Multi-value parameter cannot be passed to SQL where clause (Oracle db)

Greetings!!

I have a problem with SSRS 2005 (simple rdl) on Oracle10g environment.

PROBLEM Definition

We have multi-valued list box, the selected values of which are being passed as parameters to Oracle 10g (back-end) from the report. I want to be able to pass these values as "WHERE......IN" clause in the main query in SSRS dataset window responsible for loading report. However, when I do that it throws error. I have tried all possible combinations i.e using IN operator with '@.', ':' , '&'. Now when I did the same with SQL server as back end; it worked. Can you please help with the exact syntax of executing this successfully?

Sample Query used in sample SSRS data-set when SQL server was at back end:

SELECT PKEY, PRODUCT, TIME, BRANCH, AGEGROUP, REVENUE, PROFIT, COST

FROM TEST_SSRS WHERE (PRODUCT IN (@.p))

The report worked fine in this case when the user selected certain values in multi-select list.

Thank you,

Karthik

Oracle can not support the query parameter with a name as your case "@.p". You may change your query into:

SELECT PKEY, PRODUCT, TIME, BRANCH, AGEGROUP, REVENUE, PROFIT, COST

FROM TEST_SSRS WHERE (PRODUCT IN ?)

Then set the parameter ? to one of your report parameter.

It should be work.

|||

Multi-value query parameter rewrite for Oracle only works with the "Oracle" data extension (i.e. data source type = "Oracle").
Note: Oracle uses ":" to mark named parameter - therefore the following query syntax should work:

SELECT PKEY, PRODUCT, TIME, BRANCH, AGEGROUP, REVENUE, PROFIT, COST
FROM TEST_SSRS WHERE PRODUCT IN (:p)

-- Robert

|||Excellent, the : method worked for me. However it only worked in the data view, and not the preview window. In both instances I am asked for the paramters which I specify. In the data view I have to type in the date value as a paramter but in the preview view I can select a date form the calendar. Basically I am getting results in the data view but nothing is retuned in the preview view, no error messages occur, just no data. Can anyone help?

Multi-value parameter cannot be passed to SQL where clause (Oracle db)

Greetings!!

I have a problem with SSRS 2005 (simple rdl) on Oracle10g environment.

PROBLEM Definition

We have multi-valued list box, the selected values of which are being passed as parameters to Oracle 10g (back-end) from the report. I want to be able to pass these values as "WHERE......IN" clause in the main query in SSRS dataset window responsible for loading report. However, when I do that it throws error. I have tried all possible combinations i.e using IN operator with '@.', ':' , '&'. Now when I did the same with SQL server as back end; it worked. Can you please help with the exact syntax of executing this successfully?

Sample Query used in sample SSRS data-set when SQL server was at back end:

SELECT PKEY, PRODUCT, TIME, BRANCH, AGEGROUP, REVENUE, PROFIT, COST

FROM TEST_SSRS WHERE (PRODUCT IN (@.p))

The report worked fine in this case when the user selected certain values in multi-select list.

Thank you,

Karthik

Oracle can not support the query parameter with a name as your case "@.p". You may change your query into:

SELECT PKEY, PRODUCT, TIME, BRANCH, AGEGROUP, REVENUE, PROFIT, COST

FROM TEST_SSRS WHERE (PRODUCT IN ?)

Then set the parameter ? to one of your report parameter.

It should be work.

|||

Multi-value query parameter rewrite for Oracle only works with the "Oracle" data extension (i.e. data source type = "Oracle").
Note: Oracle uses ":" to mark named parameter - therefore the following query syntax should work:

SELECT PKEY, PRODUCT, TIME, BRANCH, AGEGROUP, REVENUE, PROFIT, COST
FROM TEST_SSRS WHERE PRODUCT IN (:p)

-- Robert

|||Excellent, the : method worked for me. However it only worked in the data view, and not the preview window. In both instances I am asked for the paramters which I specify. In the data view I have to type in the date value as a paramter but in the preview view I can select a date form the calendar. Basically I am getting results in the data view but nothing is retuned in the preview view, no error messages occur, just no data. Can anyone help?

Multi-Value Parameter

Hi
I have created a very simple report in SSRS, which counts the number of
Supplier transactions.
I have added a Multi-Value parameter with two values, (I or C) to filter
either Invoices or Credits or both.
If I query Invoice only, I get the correct result.
If I query Credit only, I get the correct result.
If I select all, the result is the same as Invoice. i.e. it is not adding
Invoice and Credit together.
Can anyone please help?
Thanks
ShodmanSo how you have given in your query to "select all" ?
Amarnath
"Shodman" wrote:
> Hi
> I have created a very simple report in SSRS, which counts the number of
> Supplier transactions.
> I have added a Multi-Value parameter with two values, (I or C) to filter
> either Invoices or Credits or both.
> If I query Invoice only, I get the correct result.
> If I query Credit only, I get the correct result.
> If I select all, the result is the same as Invoice. i.e. it is not adding
> Invoice and Credit together.
> Can anyone please help?
> Thanks
> Shodman|||Please note that I am not using any syntax or trying to write code - I am not
that clever!!
From the layout tab, I am adding a parameter through Report > Report
Parameters, and selecting Multi-Value. Values = I and C, (non-queried).
When I Preview the report, the parameter shows "select all" as on option.
Thanks for your time
Shodman
"Amarnath" wrote:
> So how you have given in your query to "select all" ?
> Amarnath
> "Shodman" wrote:
> > Hi
> >
> > I have created a very simple report in SSRS, which counts the number of
> > Supplier transactions.
> >
> > I have added a Multi-Value parameter with two values, (I or C) to filter
> > either Invoices or Credits or both.
> >
> > If I query Invoice only, I get the correct result.
> > If I query Credit only, I get the correct result.
> >
> > If I select all, the result is the same as Invoice. i.e. it is not adding
> > Invoice and Credit together.
> >
> > Can anyone please help?
> >
> > Thanks
> > Shodman|||Hey I am asking whether you have given any if condition in your data tab,
otherwise how you intend to display and select the correct query to display
your results. In otherwords you need to tell RS that when you select "Select
All" what should be done to display the correct results.
Amarnath
"Shodman" wrote:
> Please note that I am not using any syntax or trying to write code - I am not
> that clever!!
> From the layout tab, I am adding a parameter through Report > Report
> Parameters, and selecting Multi-Value. Values = I and C, (non-queried).
> When I Preview the report, the parameter shows "select all" as on option.
> Thanks for your time
> Shodman
>
> "Amarnath" wrote:
> > So how you have given in your query to "select all" ?
> >
> > Amarnath
> >
> > "Shodman" wrote:
> >
> > > Hi
> > >
> > > I have created a very simple report in SSRS, which counts the number of
> > > Supplier transactions.
> > >
> > > I have added a Multi-Value parameter with two values, (I or C) to filter
> > > either Invoices or Credits or both.
> > >
> > > If I query Invoice only, I get the correct result.
> > > If I query Credit only, I get the correct result.
> > >
> > > If I select all, the result is the same as Invoice. i.e. it is not adding
> > > Invoice and Credit together.
> > >
> > > Can anyone please help?
> > >
> > > Thanks
> > > Shodman|||I believe that Amarnath is saying that you have to provide some code in your
query to tell the database what to do when the "Select All" option is
selected.
So for example:
Select c.customer_name, p.product_type
From customer c, product p
Where c.customer_id = p.customer_id
and (p.product_type IN(@.param_product_type))
So in your report parameters you check the Multi-Value check box for the
"param_product_type" parameter. So in the above code, if you select 1 type,
the SQL query will return the correct type and if you select ALL, then the
query will return ALL product types.
Hope this helps.
Rob Cuscaden
"Amarnath" wrote:
> Hey I am asking whether you have given any if condition in your data tab,
> otherwise how you intend to display and select the correct query to display
> your results. In otherwords you need to tell RS that when you select "Select
> All" what should be done to display the correct results.
> Amarnath
> "Shodman" wrote:
> > Please note that I am not using any syntax or trying to write code - I am not
> > that clever!!
> >
> > From the layout tab, I am adding a parameter through Report > Report
> > Parameters, and selecting Multi-Value. Values = I and C, (non-queried).
> >
> > When I Preview the report, the parameter shows "select all" as on option.
> >
> > Thanks for your time
> > Shodman
> >
> >
> > "Amarnath" wrote:
> >
> > > So how you have given in your query to "select all" ?
> > >
> > > Amarnath
> > >
> > > "Shodman" wrote:
> > >
> > > > Hi
> > > >
> > > > I have created a very simple report in SSRS, which counts the number of
> > > > Supplier transactions.
> > > >
> > > > I have added a Multi-Value parameter with two values, (I or C) to filter
> > > > either Invoices or Credits or both.
> > > >
> > > > If I query Invoice only, I get the correct result.
> > > > If I query Credit only, I get the correct result.
> > > >
> > > > If I select all, the result is the same as Invoice. i.e. it is not adding
> > > > Invoice and Credit together.
> > > >
> > > > Can anyone please help?
> > > >
> > > > Thanks
> > > > Shodman|||You are right, I am asking exactly the same.
Amarnath
"Rob" wrote:
> I believe that Amarnath is saying that you have to provide some code in your
> query to tell the database what to do when the "Select All" option is
> selected.
> So for example:
> Select c.customer_name, p.product_type
> From customer c, product p
> Where c.customer_id = p.customer_id
> and (p.product_type IN(@.param_product_type))
> So in your report parameters you check the Multi-Value check box for the
> "param_product_type" parameter. So in the above code, if you select 1 type,
> the SQL query will return the correct type and if you select ALL, then the
> query will return ALL product types.
> Hope this helps.
> Rob Cuscaden
>
> "Amarnath" wrote:
> > Hey I am asking whether you have given any if condition in your data tab,
> > otherwise how you intend to display and select the correct query to display
> > your results. In otherwords you need to tell RS that when you select "Select
> > All" what should be done to display the correct results.
> >
> > Amarnath
> >
> > "Shodman" wrote:
> >
> > > Please note that I am not using any syntax or trying to write code - I am not
> > > that clever!!
> > >
> > > From the layout tab, I am adding a parameter through Report > Report
> > > Parameters, and selecting Multi-Value. Values = I and C, (non-queried).
> > >
> > > When I Preview the report, the parameter shows "select all" as on option.
> > >
> > > Thanks for your time
> > > Shodman
> > >
> > >
> > > "Amarnath" wrote:
> > >
> > > > So how you have given in your query to "select all" ?
> > > >
> > > > Amarnath
> > > >
> > > > "Shodman" wrote:
> > > >
> > > > > Hi
> > > > >
> > > > > I have created a very simple report in SSRS, which counts the number of
> > > > > Supplier transactions.
> > > > >
> > > > > I have added a Multi-Value parameter with two values, (I or C) to filter
> > > > > either Invoices or Credits or both.
> > > > >
> > > > > If I query Invoice only, I get the correct result.
> > > > > If I query Credit only, I get the correct result.
> > > > >
> > > > > If I select all, the result is the same as Invoice. i.e. it is not adding
> > > > > Invoice and Credit together.
> > > > >
> > > > > Can anyone please help?
> > > > >
> > > > > Thanks
> > > > > Shodman

Multi-Select Parameters in SSRS 2005 - HELP

Hi all â'
Having a problem with SQL 2005 Reporting Services and Multi-Select
parameters. Itâ's seems as though Multi-Select and Allow Nulls are mutually
exclusive. This is a problem as we have some reports that are very complex â'
using very complex stored procedures that allow for this scenario. Now â' put
you hand over your ears for a second â' this works fine in Cryatsl â' the sproc
is set up to accept a null or a list.
WHERE (@.parm is NULL or element = @.parm)
There must be a way around this in SSRS
ThanksTry doing this...
where (@.parm is null or element in (@.parm))
Cheers|||My bad - I mistated the problem - the problem is not in the sproc - it is in
SRSS Designer. It will not let you set both Multi-Select and Allow Nulls on a
parameter - they are mutually exclusive. If you try to set both - the
designer gives an error and will not save your changes. It will on the other
hand - let you set one or the other individually.
Ths sproc workd just fine as we have sent it a list and a NULL value and it
works both ways as intended.
Thanks...
"sullins602" wrote:
> Try doing this...
> where (@.parm is null or element in (@.parm))
> Cheers
>|||Did you try returning a value of null to the dataset that populates the
list for the parameter?
select
null as 'val',
' - All - ' as 'label'
union
select
realval as 'val',
reallabel as 'lable'
from
mytable|||I just ran into the same problem today with a multi-value and null
parameter. I also thought to add null to the dataset, but when I added
the null to the parameter dataset it did not show up in the report
drop-down box.
Could it be possible this is being filtered out?
One way that does work is to use a actual value instead of a null
(assuming int).
SELECT -1 AS Level1Value, ' - None -' AS Level1Label
UNION ALL
<rest of query>
But I would rather have the option to not require a value to be
selected.
Any other ideas on making this work?|||I could try that - the only drawback that I see would be that All would then
be part of the mustiselect list - then what would happen if they select all
plus some of the other values - would they not get a sting like
'NULL','Val1','Val2'....
"sullins602" wrote:
> Did you try returning a value of null to the dataset that populates the
> list for the parameter?
> select
> null as 'val',
> ' - All - ' as 'label'
> union
> select
> realval as 'val',
> reallabel as 'lable'
> from
> mytable
>

Multiple tables in the dataset

Hi,
I have a stored proc which returns multiple result sets. I think SSRS picks
up the first one by design. What's the best way forward, a data extension, or
is there a simpler way?
Thanks in advance,
Regards,
DattaYou need to split each result set into a uniqe dataset in RS. It can't
handle multiple result sets. Can you create new stored procedures from the
queries in the original one?
Kaisa M. Lindahl Lervik
"Datta" <Datta@.discussions.microsoft.com> wrote in message
news:F228B40A-B4B2-40A5-A243-F2573CFEBFC8@.microsoft.com...
> Hi,
> I have a stored proc which returns multiple result sets. I think SSRS
> picks
> up the first one by design. What's the best way forward, a data extension,
> or
> is there a simpler way?
> Thanks in advance,
> Regards,
> Datta|||Thanks, I suspected so.
I can split the procedures, but I think a custom data extension is the right
way to go, if there is no in-built support.
"Kaisa M. Lindahl Lervik" wrote:
> You need to split each result set into a uniqe dataset in RS. It can't
> handle multiple result sets. Can you create new stored procedures from the
> queries in the original one?
> Kaisa M. Lindahl Lervik
> "Datta" <Datta@.discussions.microsoft.com> wrote in message
> news:F228B40A-B4B2-40A5-A243-F2573CFEBFC8@.microsoft.com...
> > Hi,
> >
> > I have a stored proc which returns multiple result sets. I think SSRS
> > picks
> > up the first one by design. What's the best way forward, a data extension,
> > or
> > is there a simpler way?
> >
> > Thanks in advance,
> > Regards,
> > Datta
>
>

Multiple tables from within a stored procedure - how?

Hi,
I'm currently analyzing SSRS as a potential replacement for Crystal Reports
(and yes, it's definitely going to replace it). I do have one issue,
however... I'm attempting to point SSRS to a stored procedure that returns
four separate tables, but only the first one seems to be returned.
Is there a way to have access to each of the tables within the sproc?
Regards,
ScottNo there is not and this has not changed in RS 2008.
The most you can do is to have a parameter that selects which one you want
and then when calling the stored procedure set that parameter to a constant
value. If used in a report and all four are desired you have to call it four
times.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott McNair" <smcnair@.beachexpress.takethispartout.com> wrote in message
news:Xns9A218ECAC3E7Adonquixote235gmailco@.207.46.248.16...
> Hi,
> I'm currently analyzing SSRS as a potential replacement for Crystal
> Reports
> (and yes, it's definitely going to replace it). I do have one issue,
> however... I'm attempting to point SSRS to a stored procedure that returns
> four separate tables, but only the first one seems to be returned.
> Is there a way to have access to each of the tables within the sproc?
> Regards,
> Scott