Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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

549 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  
 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.02 seconds. Powered By: Snitz Forums 2000