SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Development Tools
 Reporting Services Development
 SSRS Passing Multiple Values to Multi-Value Parame
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

johnellis
Starting Member

1 Posts

Posted - 08/14/2013 :  14:59:11  Show Profile  Reply with Quote
Hello:

I have an SSRS report with a subreport. Each report has two parameters. And, the parameters are the same. One pulls data from a work center, while the other pulls data from manufacturing orders.

If I run either the main report or the subreport, with one value for each parameter, the report(s) runs fine. But, if I choose say every value in each parameter, no data appears.

The entire project is based on SQL stored procedures that I created. I tested those stored procedures in SQL, and they display data accurately when I run the same tests that I ran in SSRS. So, the problem is definitely in SSRS itself.

I have been reading today online about how developers place the following expressions within the main dataset of the SSRS report and subreport respectively:

=join(Parameters!ParamName.Value,",")
=Split(join(Parameters!ParamName.Value,","),",")

Also, the “verbiage” online says to modify the stored procedures’ parameter “@-symbols” to contain the following: ‘,’. I have done all of this, but I still get the same result—blank data upon choosing multiple values. Also, SSRS gives off a warning shown at the end of this e-mail.

Does anyone have any suggestions?

Thanks!

John

Warning 1 [rsInvalidExpressionDataType] The Value expression used in textrun ‘currentstep1.Paragraphs[0].TextRuns[0]’ returned a data type that is not valid. F:\SSRS\Manufacturing Scheduling\Mfg Scheduling\Mfg Scheduling\Current Step Subreport.rdl 0 0

MuMu88
Aged Yak Warrior

547 Posts

Posted - 08/15/2013 :  10:46:35  Show Profile  Reply with Quote
Since join(Parameters!ParamName.Value,",")
returns a comma separated list of values, you can not use '=' operator for comparison.

Try this:
if you want to include the list in a where clause


SELECT ..... FROM .... WHERE (<column_name> IN (@parametername, ','))



if you want to display the list then you can use
= join(Parameters!ParamName.Value,",")
in your expression
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000