Parameter problem

hi,
i m using following query in my dataset query string

SELECT OrderNumber, OrderDate, Name AS Client, orderstatus
FROM Orders
Where @.Param

i m sending boolean exp as value for @.param as string like " YEAR(ORderDate) = YEAR(getDate()) "

but is showing following error

An expression of non-boolean type specified in a context where a condition is expected,near '@.param'. (Microsoft Sql Server, Error:4145)

please help me,

thanks
You cannot do that unless you use dynamic Sql in a procedure, passing the string as the parameter which should be avoided.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||thanks for reply

but can u explain further because i m new to it
|||

You dont need to send the parameter string because whatever you trying to do can be achieved in plain SQL in dataset query itself:

SELECT OrderNumber, OrderDate, Name AS Client, orderstatus
FROM Orders
Where YEAR(OrderDate) = YEAR(GETDATE())

Shyam

|||Wee that depends. if you have the same query everything with carying parameters you can use something like Where YEAR(@.SomeVar) = YEAR(Getdate()), the parameter will automatically mapped to the report if you write such a query in the pane of the query editor in the report designer. but if you want to pass in a whole condition rather than just a parameter you would probably need to pass it to a stored procedure to make it execute in a dynamic SQL statement.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Parameter passing from report to SQL

Hi, I have the following SQL code in my DataSet:
(SQL Server 2005)
DECLARE @.sel1 AS varchar(20)
DECLARE @.sel2 AS varchar(20)
DECLARE @.sel3 AS varchar(20)
DECLARE @.sel4 AS varchar(20)
DECLARE @.test AS integer
set @.test = @.TimeDiff; <-- this I want to insert via URL in report
set @.sel1=convert
(char(10),DATEADD(dd,-convert(integer,@.test),getdate()),121);
set @.sel2=Replace(@.sel1,'-','');
set @.sel3=convert (char(10),DATEADD(dd,0,getdate()),121);
set @.sel4=Replace(@.sel3,'-','');
select @.sel2 AS StartTime, @.sel4 AS EndTime;
I have declared a report parameter (TimeDiff, without @.) in Report
Parameters. It is integer and hidden and has a default value 2 (non queried).
In dataset parameters I do not have any configurations (should I have).
I tried to opass the parameter to my sql code but it is not succeeded.
I got the followng error message:
"Must declare the scalar cariable "@.TimeDiff"
The final meaning is to insert this as parameter via URL and use it in the
dataset sql code, but I can not get it into my SQL .
When running the dataset itself it asks the parameter @.TimeDiff and works OK.
What is wrong in my parameter handling ?Push the ... button (the point button) right beside your dataset
(within the data display), a couple of tabs will popup and goto the
parameters tab.
Within this tab you're able to bind the sql declared parameter to your
reporting parameter.
You'll notice the way it works via your other parameters which are
bound to the sql parameters|||I'd suggest having a stored procedure for this code but as long as you don't
use a temp table (if you use a temp table you must put this in a stored
procedure) then this will work.
I would have expected RS to automatically create a TimeDiff report parameter
to match the @.TimeDiff since @.TimeDiff is not declared. Perhaps it is the
use of the set statement. Try
select @.test = @.TimeDiff
If you click on the ..., parameters tab and there is no @.TimeDiff in the
name column then try adding this by hand and then select your report
parameter that it is mapping to.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jarmopy" <jarmopy@.discussions.microsoft.com> wrote in message
news:9123A5B9-0DA9-4639-9884-D9D7A30CAA2C@.microsoft.com...
> Hi, I have the following SQL code in my DataSet:
> (SQL Server 2005)
> DECLARE @.sel1 AS varchar(20)
> DECLARE @.sel2 AS varchar(20)
> DECLARE @.sel3 AS varchar(20)
> DECLARE @.sel4 AS varchar(20)
> DECLARE @.test AS integer
> set @.test = @.TimeDiff; <-- this I want to insert via URL in report
>
> set @.sel1=convert
> (char(10),DATEADD(dd,-convert(integer,@.test),getdate()),121);
> set @.sel2=Replace(@.sel1,'-','');
> set @.sel3=convert (char(10),DATEADD(dd,0,getdate()),121);
> set @.sel4=Replace(@.sel3,'-','');
> select @.sel2 AS StartTime, @.sel4 AS EndTime;
> I have declared a report parameter (TimeDiff, without @.) in Report
> Parameters. It is integer and hidden and has a default value 2 (non
> queried).
> In dataset parameters I do not have any configurations (should I have).
> I tried to opass the parameter to my sql code but it is not succeeded.
> I got the followng error message:
> "Must declare the scalar cariable "@.TimeDiff"
> The final meaning is to insert this as parameter via URL and use it in the
> dataset sql code, but I can not get it into my SQL .
> When running the dataset itself it asks the parameter @.TimeDiff and works
> OK.
> What is wrong in my parameter handling ?
>
>
>|||Hi, thanks for the answers.
I tried to put the following parameter setting in the dataset (... button)
@.TimeDiff = Parameter!TimeDiff.Value
but now I get the following error message:
The report parameter 'LastDate' has a DefaultValue or a ValidValue that
depends on the report parameter "TimeDiff". Forward dependencies are not
allowed"
The LastDate report parameter is coming from the dataset from field
StartTime and that is calculated from TimeDiff.
LastTime parameter is Hidden and available value is from query and the
default value is from query.
Still something wrong.|||Hi,
I solved the last problem just by changing the order of the parameters in
report side.
Thanks for nicismyname and Bruce L-C|||Hi, you should be aware of the order of your parameters. If lastdate
parameter it's dataset refers to the timediff parameter you should
first prompt the timediff parameter. The prompting order is determined
by the 'report>parameters' menu. You'll notice two arrows at the
dialog popping up after selecting 'report>parameters', use the up and
down arrow to determine the prompting order.

Parameter not refreshing default data selection

Here is the senerio:

Parameter 1: Company - multi-value

Parameter 2: Employee - multi-value

Available values: Query - Dataset: EmpList

Default values: Query - Dataset: EmpList

Parameter 2 is dependant on Parameter 1.

When a Company is selected, it does update the list of employees in parameter 2. But what it isn't doing is updating my default values.

I select Company A. It populates the Employee list with all of Company A's employees and marks them all as selected (check in the checkbox) and the "Select All" checkbox is also checked.

I then decide I want to include Company B. It populates the Employee list with all of Company B's employees but it does NOT select the employee's from Company B. It is like it ignored the default values query.

I would also think that if "Select All" is checked all new values coming in would be checked.

Is there a way around this problem?

I need to know if this is a bug or if there is something I can do to fix this.

parameter modification within mdx

Is it possible to modify a parameter value within a dataset using mdx?

lets say the param values of @.bucket = 6

how would you go about writing @.bucket + 1

so @.bucket now would equal 7

is this possible?

Your options are:

1. Call an SSAS stored procedure from the generated MDX statement, e.g.

SELECT NON EMPTY { [Measures].[Profit] } ON COLUMNS, NON EMPTY { ([Product].[Product].[Product].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(MyAssemblyName.MyStoredProcedureName(@.ProductProduct), CONSTRAINED) ) ON COLUMNS FROM [RPM]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

2. If you want to use an SSRS code-behind function, you need to use an expression-based query, e.g.:

="SELECT NON EMPTY { [Measures].[Profit] } ON COLUMNS, NON EMPTY { ([Product].[Product].[Product].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(" & Code.SomeProcedure(Parameters!Product.Value & "), CONSTRAINED) ) ON COLUMNS FROM [RPM]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"

parameter is missing a value

I have a SQL Report in SQL 2005 that has 2 datasets. The second
dataset queries a value that is passed as a parameter to the first
dataset. It works well as the main report but when I use it as a
linked report and put a default on parameter field and hide the
prompt, it gives me "xxx parameter is missing a value". It seems that
it's not reading the parameter at all.
Now if I have the second dataset as Non-queried and embed the value
and label fields on the report itself, it works well even as a linked
report. However, I dont want to do this because these values and
labels change. I cannot embed it on each report (around 50 of them)
and change each report one by one if there is a change.
I appreciate your help in advance.
Ben
%$^*&I finally got the report to work. The first dataset didn't recognize
the 3-character default value because it expected a 10 character
string (as declared in the second dataset). I just added seven
trailing spaces without quotes and it worked fine.
Ben
On Nov 15, 11:57 am, benito <lochinvar...@.gmail.com> wrote:
> I have a SQL Report in SQL 2005 that has 2 datasets. Theseconddatasetqueriesavaluethat is passed as a parameter to the firstdataset. It works well as the main report but when I use it as a
> linked report and put a default on parameter field and hide the
> prompt, it gives me "xxx parameter is missing avalue". It seems that
> it's not reading the parameter at all.
> Now if I have theseconddatasetas Non-queried and embed thevalue
> and label fields on the report itself, it works well even as a linked
> report. However, I dont want to do this because these values and
> labels change. I cannot embed it on each report (around 50 of them)
> and change each report one by one if there is a change.
> I appreciate your help in advance.
> Ben
> %$^*&

Parameter dropdown width

How do I adjust the reporting parameter dropdown width? the dropdown is
populated from a dataset and the width of the values exceed the width of the
drop down
thanks
--
Message posted via http://www.sqlmonster.comI am also looking for the similar solution. Please respond ,if it is possible
to adjust the drop down parameter list width. Would it be possible after SP2.
I hope somebody from Reporting Services team would help us here.
Regards
Vijay
"Angie via SQLMonster.com" wrote:
> How do I adjust the reporting parameter dropdown width? the dropdown is
> populated from a dataset and the width of the values exceed the width of the
> drop down
> thanks
>
> --
> Message posted via http://www.sqlmonster.com
>|||There is no way to do this that I know of. That sort of fine tuning of the
Report Manager UI is not possible. I don't know about RS 2005 but I for RS
2000 SP2 this is not possible. You would need to create your own web page
and then integrate via either URL or webservices.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Vijay Tripathi" <VijayTripathi@.discussions.microsoft.com> wrote in message
news:49AAE0A4-A32E-41CC-A821-F81BBEDDD501@.microsoft.com...
>I am also looking for the similar solution. Please respond ,if it is
>possible
> to adjust the drop down parameter list width. Would it be possible after
> SP2.
> I hope somebody from Reporting Services team would help us here.
> Regards
> Vijay
> "Angie via SQLMonster.com" wrote:
>> How do I adjust the reporting parameter dropdown width? the dropdown is
>> populated from a dataset and the width of the values exceed the width of
>> the
>> drop down
>> thanks
>>
>> --
>> Message posted via http://www.sqlmonster.com

Parameter Does Not Work When Deployed

This is my first attempt at using a second dataset to fill a drop-down
parameter. This works fine inside of the designer but when I deploy to the
report server, there are no values in the drop down.
I thought perhaps it was because the data source name on the server is
different. In the past I would just update it on the server after I deploy
and it worked fine. After the deploy and after chaning the DS name, I edited
the server RDL and found that not all of the DS names had been updated. I
then edited the names in the RDL and uploaded manually thinking this was the
problem but the DD list still does not work.
Thanks for any help!Are you using a shared data source? This could be a credentials problem. I
always use a shared datasource. Personally, I don't see much of a reason to
ever not use a shared data source.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:OJhRIuNjFHA.3256@.TK2MSFTNGP12.phx.gbl...
> This is my first attempt at using a second dataset to fill a drop-down
> parameter. This works fine inside of the designer but when I deploy to the
> report server, there are no values in the drop down.
> I thought perhaps it was because the data source name on the server is
> different. In the past I would just update it on the server after I deploy
> and it worked fine. After the deploy and after chaning the DS name, I
> edited the server RDL and found that not all of the DS names had been
> updated. I then edited the names in the RDL and uploaded manually thinking
> this was the problem but the DD list still does not work.
> Thanks for any help!
>|||Bruce
Thanks for your reply
Yes, I have deployed other reports to the same folder, reset them to the
same data source and it always has worked fine. What is different is this is
the first time I used a second dataset in a report (to drive the lookup drop
down). It may not be the datasource, that was just a guess on my part. Are
there othe issues that could affect a query driven lookup such that it works
in the designer but not when deployed?
Thanks
Mike
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23oQhumSjFHA.576@.TK2MSFTNGP15.phx.gbl...
> Are you using a shared data source? This could be a credentials problem. I
> always use a shared datasource. Personally, I don't see much of a reason
> to ever not use a shared data source.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
> news:OJhRIuNjFHA.3256@.TK2MSFTNGP12.phx.gbl...
>> This is my first attempt at using a second dataset to fill a drop-down
>> parameter. This works fine inside of the designer but when I deploy to
>> the report server, there are no values in the drop down.
>> I thought perhaps it was because the data source name on the server is
>> different. In the past I would just update it on the server after I
>> deploy and it worked fine. After the deploy and after chaning the DS
>> name, I edited the server RDL and found that not all of the DS names had
>> been updated. I then edited the names in the RDL and uploaded manually
>> thinking this was the problem but the DD list still does not work.
>> Thanks for any help!
>|||It could be a security issue on the database side of things. I've been
burned by that. I use one ID in development and then production is using
another one and the table/store procedure does not have the correct rights
for the production credentials. Take a look on the database at the table the
query is going against and see what the security access rights are set to.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:ulgIyGijFHA.3012@.TK2MSFTNGP12.phx.gbl...
> Bruce
> Thanks for your reply
> Yes, I have deployed other reports to the same folder, reset them to the
> same data source and it always has worked fine. What is different is this
> is the first time I used a second dataset in a report (to drive the lookup
> drop down). It may not be the datasource, that was just a guess on my
> part. Are there othe issues that could affect a query driven lookup such
> that it works in the designer but not when deployed?
> Thanks
> Mike
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23oQhumSjFHA.576@.TK2MSFTNGP15.phx.gbl...
>> Are you using a shared data source? This could be a credentials problem.
>> I always use a shared datasource. Personally, I don't see much of a
>> reason to ever not use a shared data source.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
>> news:OJhRIuNjFHA.3256@.TK2MSFTNGP12.phx.gbl...
>> This is my first attempt at using a second dataset to fill a drop-down
>> parameter. This works fine inside of the designer but when I deploy to
>> the report server, there are no values in the drop down.
>> I thought perhaps it was because the data source name on the server is
>> different. In the past I would just update it on the server after I
>> deploy and it worked fine. After the deploy and after chaning the DS
>> name, I edited the server RDL and found that not all of the DS names had
>> been updated. I then edited the names in the RDL and uploaded manually
>> thinking this was the problem but the DD list still does not work.
>> Thanks for any help!
>>
>|||Bruce
The security seems ok and the main query for dataset-1 works fine. The same
data source is used for dataset-1 and dataset-2 which drives the param
drop-down list. Maybe it is not the data source since I am not getting any
erorrs. This is what happens: the the drop down list is horizontally
truncated and there are no values in displayed when you select it. It just
displays 'Select Value'.
Thanks
Mike
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23fTmvMijFHA.3608@.TK2MSFTNGP12.phx.gbl...
> It could be a security issue on the database side of things. I've been
> burned by that. I use one ID in development and then production is using
> another one and the table/store procedure does not have the correct rights
> for the production credentials. Take a look on the database at the table
> the query is going against and see what the security access rights are set
> to.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
> news:ulgIyGijFHA.3012@.TK2MSFTNGP12.phx.gbl...
>> Bruce
>> Thanks for your reply
>> Yes, I have deployed other reports to the same folder, reset them to the
>> same data source and it always has worked fine. What is different is this
>> is the first time I used a second dataset in a report (to drive the
>> lookup drop down). It may not be the datasource, that was just a guess on
>> my part. Are there othe issues that could affect a query driven lookup
>> such that it works in the designer but not when deployed?
>> Thanks
>> Mike
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:%23oQhumSjFHA.576@.TK2MSFTNGP15.phx.gbl...
>> Are you using a shared data source? This could be a credentials problem.
>> I always use a shared datasource. Personally, I don't see much of a
>> reason to ever not use a shared data source.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
>> news:OJhRIuNjFHA.3256@.TK2MSFTNGP12.phx.gbl...
>> This is my first attempt at using a second dataset to fill a drop-down
>> parameter. This works fine inside of the designer but when I deploy to
>> the report server, there are no values in the drop down.
>> I thought perhaps it was because the data source name on the server is
>> different. In the past I would just update it on the server after I
>> deploy and it worked fine. After the deploy and after chaning the DS
>> name, I edited the server RDL and found that not all of the DS names
>> had been updated. I then edited the names in the RDL and uploaded
>> manually thinking this was the problem but the DD list still does not
>> work.
>> Thanks for any help!
>>
>>
>

Parameter Default value

Since the Select all feature was removed from drop downs I have created my
own value to represent that and union it to the dataset for one of my
parameters. I'm trying to make that the default value, but it won't let me
reference the top of the dataset because the parameter uses the same
dataset.
Hmm... that sound confusing, I'll try again but leave that in case someone
understands it.
Basically I have a parameter of "Owners" which is just a string collection
of names. This box is populated by a dataset named the same. I fill the
Value and the Label of the parameter with Owners.ownername. That dataset is
just a simple table field but I union the string "(SELECT ALL)" to the top
of it so that it's the first record returned and appears at the top of the
parameter drop down selection.
I then try to set the default value of the Parameter. If I use a
non-queried value I can use an expression and selection the first of the
field. The problem is that non-queried can not use fields. If I try to
use a queried value it selects everything.
Is there a way to just fill that first record?> The problem is that non-queried can not use fields. If I try to
> use a queried value it selects everything.
> Is there a way to just fill that first record?
Why is it a problem that you can not use fields. If you use the non-
queried default value "(SELECT ALL)" then this is what should be
selected by default.
Rowen|||Ofcourse you can use it in the expression of non-queried with hard coded
"select All" so whenever the first record or the parameter is "select All"
then it should be "Select All" string.
Amarnath
"Ryan" wrote:
> Since the Select all feature was removed from drop downs I have created my
> own value to represent that and union it to the dataset for one of my
> parameters. I'm trying to make that the default value, but it won't let me
> reference the top of the dataset because the parameter uses the same
> dataset.
> Hmm... that sound confusing, I'll try again but leave that in case someone
> understands it.
> Basically I have a parameter of "Owners" which is just a string collection
> of names. This box is populated by a dataset named the same. I fill the
> Value and the Label of the parameter with Owners.ownername. That dataset is
> just a simple table field but I union the string "(SELECT ALL)" to the top
> of it so that it's the first record returned and appears at the top of the
> parameter drop down selection.
> I then try to set the default value of the Parameter. If I use a
> non-queried value I can use an expression and selection the first of the
> field. The problem is that non-queried can not use fields. If I try to
> use a queried value it selects everything.
> Is there a way to just fill that first record?
>
>

parameter cannot set the command text for dataset

Hello

I get the following error in a very quick test system i created:

An error occurred during local report processing.

An error has occurred during report processing.

Cannot set the command text for data set 'test'.

Error during processing of the CommandText expression of dataset 'test'.

My sql is:

= "select T.testid, T.test from test T " & Iif(Parameters!test1.Value = 1, "", "Where T.testid = " & Parameters!test1.Value)

There is nothing obviously wrong in the code as far as i can see

The parameter 'test1' is of type 'string'

The database 'test' has 2 columns of type 'smallint' and 'Name:nvarchar(50)'

I am at a loss, as this query is really simple, and is similar to the example query set up my microsoft which works fine

Thanks

t.test from test?

You tried running this in sql?

|||

Yes, it works fine in sql if you strip out the bits that obviously wont work in query analyser

this works:

= "select T.testid, T.test from test T " & Iif(1 = 1, "", "Where T.testid = 1" )

this does not:

= "select T.testid, T.test from test T " & Iif(1 = 1, "", "Where T.testid = " & Parameters!test1.Value)

why?

there is a parameter called test1

|||

Found the answer!

You have to have another table with a different name into which the parameter links (in the section 'available values' / 'from query')

I created another table called test2, et voila! it works

parameter and Oracle DB

I have a dataset that queries an Oracle DB. I am getting an error when I try
to set a parameter in the query. Basically I have the following:
WHERE x.system_num = s.system_num
and na.service_id = x.service_id
and na.location_num = sl.location_num
and sd.device_mux_id = dm2.device_mux_id
and (x.auth_date >= @.param_start)
ORDER BY m.mso_name, sl.system_name, system_type, d.device_address
The error I get is the following:
ORA-00936: missing expression
(System.Data.OracleClient)
When I remove the "and (x.auth_date >= @.param_start)" the error goes away.
The missing expression sounds like I have a comma at the end of the SELECT or
FROM line; but when the param line is removed the error goes away. So I
don't think that is it.
Could it be in how the parameter is set up? Just throwing out ideas here.
Thank you for any help offered.
Rob CuscadenFYI
After several HOURS of silly errors someone found the solution to this
problem. I wanted to post it here so that others can gain from my hours of
frustration. I will not say that it will work in ALL cases; but that it
works in mine and it is something for others to try if they are stuck.
Problem, using Reporting Services 2005 and accessing an Oracle database with
parameters.
In Reporting Services queries of a SQL DB, I have seen the @.param_startdate
used in the query and then the parameter is defined in the "Report
Parameters" option on the "Reports" top menu option.
So, for example
Select *
From sometable
Where sometable.datefield >= @.param_startdate
Then you go modify the Report Parameters option for the project.
If you try this with an ORACLE DB you will get several errors (ORA-00936:
missing expression is one of them).
SOLUTION: To solve this for an ORACLE DB, use the following:
SELECT *
FROM sometable
WHERE sometable.datefield = :param_startdate
-----
Now I am sure that this little ( : ) is documented somewhere in a document
out there somewhere. BUT for crying out loud... such a simple solution that
took 6 hours to find.
Please feel my frustration and I hope I prevent this from happening elsewhere.
Rob Cuscaden
"Rob" wrote:
> I have a dataset that queries an Oracle DB. I am getting an error when I try
> to set a parameter in the query. Basically I have the following:
> WHERE x.system_num = s.system_num
> and na.service_id = x.service_id
> and na.location_num = sl.location_num
> and sd.device_mux_id = dm2.device_mux_id
> and (x.auth_date >= @.param_start)
> ORDER BY m.mso_name, sl.system_name, system_type, d.device_address
> The error I get is the following:
> ORA-00936: missing expression
> (System.Data.OracleClient)
> When I remove the "and (x.auth_date >= @.param_start)" the error goes away.
> The missing expression sounds like I have a comma at the end of the SELECT or
> FROM line; but when the param line is removed the error goes away. So I
> don't think that is it.
> Could it be in how the parameter is set up? Just throwing out ideas here.
> Thank you for any help offered.
> Rob Cuscaden
>|||Rob,
I have been having the same problem and my queries look up an Oracle DB
also. I ended up getting so frustrated at mine, that our Oracle DB
Administrator told me to use a program called Toad for Oracle. Used that
which the SQL module in that program works very similar as the Microsoft
Query tool (Data, Import External Data,
New Database Query).
Anyway, I looked at the SQL Statement after building the Query (joining
tables, adding fields, adding criteria filters, etc). And I saw the same
Where table.field LIKE :whatever
Well, I tried using that in the Microsoft Query (SQL Statement) and I end up
getting some other error which I can't duplicate right now. I tried it again
and now it just completely bails me out of excel with the ever famous Debug,
Send Error Report, etc.
Anyway, wasn't sure if you ran into anything more you could share that might
help in my situation.
I also posted this issue somewhere else on here explaining more in detail
about how originally I created the query with one line of criteria where 3 of
my criteria having 5 "or" conditions. When I refresh or return data to Excel
it works fine.
Problem starts occurring if I ever have to go back in to the query to edit
and then try and refresh or return the data again. That's where I get the
ORA-00936 Missing expression error message.
I'm so stuck on this and really need to resolve somehow, because the data
that's returned from this query is used for charts, graphs, pivot tables,
that are all part of a giant VBA I wrote to automate dashboards for a bunch
of engineers.
Anwyay, any suggestions you or anyone might have on this would be greatly
appreciated.
"Rob" wrote:
> FYI
> After several HOURS of silly errors someone found the solution to this
> problem. I wanted to post it here so that others can gain from my hours of
> frustration. I will not say that it will work in ALL cases; but that it
> works in mine and it is something for others to try if they are stuck.
> Problem, using Reporting Services 2005 and accessing an Oracle database with
> parameters.
> In Reporting Services queries of a SQL DB, I have seen the @.param_startdate
> used in the query and then the parameter is defined in the "Report
> Parameters" option on the "Reports" top menu option.
> So, for example
> Select *
> From sometable
> Where sometable.datefield >= @.param_startdate
> Then you go modify the Report Parameters option for the project.
> If you try this with an ORACLE DB you will get several errors (ORA-00936:
> missing expression is one of them).
> SOLUTION: To solve this for an ORACLE DB, use the following:
> SELECT *
> FROM sometable
> WHERE sometable.datefield = :param_startdate
> -----
> Now I am sure that this little ( : ) is documented somewhere in a document
> out there somewhere. BUT for crying out loud... such a simple solution that
> took 6 hours to find.
> Please feel my frustration and I hope I prevent this from happening elsewhere.
> Rob Cuscaden
> "Rob" wrote:
> > I have a dataset that queries an Oracle DB. I am getting an error when I try
> > to set a parameter in the query. Basically I have the following:
> >
> > WHERE x.system_num = s.system_num
> > and na.service_id = x.service_id
> > and na.location_num = sl.location_num
> > and sd.device_mux_id = dm2.device_mux_id
> > and (x.auth_date >= @.param_start)
> >
> > ORDER BY m.mso_name, sl.system_name, system_type, d.device_address
> >
> > The error I get is the following:
> >
> > ORA-00936: missing expression
> > (System.Data.OracleClient)
> >
> > When I remove the "and (x.auth_date >= @.param_start)" the error goes away.
> > The missing expression sounds like I have a comma at the end of the SELECT or
> > FROM line; but when the param line is removed the error goes away. So I
> > don't think that is it.
> >
> > Could it be in how the parameter is set up? Just throwing out ideas here.
> >
> > Thank you for any help offered.
> > Rob Cuscaden
> >sql

param prob

hi ,
I have created 2 datasets. my first dataset quary parameter is BU. query is like following.

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SET [FilteredBUList] AS strtoset(@.BU)

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[FilteredBUList] on rows

FROM Profitability

my second dataset query is like following,

SET [FilteredBUList] AS strtoset(@.BU)

SET [customtimeset] AS.....

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
intersect( [FilteredBUList],...) on rows

FROM Profitability

i need to map previous query BU parameter to the second dataset parameter. pls tel m the proper way.
cant access report parameter in this way from different dataset.
WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SET [FilteredBUList] AS strtotuple(@.BU)

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[FilteredBUList] on rows

FROM Profitability