Link to home
Start Free TrialLog in
Avatar of Lisa Callahan
Lisa CallahanFlag for United States of America

asked on

SSRS 2005 to 2008 Join Parameters Issue

Hello experts -

We are in the process of upgrading from SSRS 2005 to SSRS 2008 and I am seeing a discrepancy in a grouping issue - what works in 2005 does not work in 2008. Basically a stored procedure passes a string of values which will eventually end up being the group by fields. The original programmer used the Join(Parameter!Field.Value,",") statements and all worked great in 2005. However, 2008 is not happy. At first I thought it might be a comma vs. a pipe delimiter but I tested that theory and neither one works in 2008. I have attached some screen shots with an attempt to explain the issue.

If anybody has any experience or can at least point me in a direction to trouble-shoot, I would be very grateful.

Thank you.

-- Lisa
SSRS-2005-to-2008-Upgrade.docx
Avatar of Arifhusen Ansari
Arifhusen Ansari
Flag of India image

In child  report do you in line query or you are passing the parameter value to stored procedure.

If you have inline query in the data set for second report.

Please try below expression before passing the parameter.

=split(join(Parameters!GroupSubSetOUT.Value,","),",")
Avatar of Lisa Callahan

ASKER

The value is passed to a stored procedure.

Thank you for the suggestion, I will try it anyway - you never know :)
A few years ago I inherited a large collection of reports running under SSRS 2008 that used a common subreport with multiple parameters. At times, the parameters just didn't work right, and I am certain this was due to bugs in SSRS 2008. I ultimately solved the problem using a C# library that converted multiple parameters, some of them lists, into one XML string, passed that string to the subreport, and then converted the XML string back into whatever the subreport needed, within the subreport.

I hope there is a simpler solution here. There was one particular issue with subreport parameter passing that stood out: default values for subreport parameters didn't always work. I therefore made it a rule never to depend on default values and to always pass a value for every subreport parameter, even if it had no meaning in the current context. That made a mess at the calling end but it did make things work better, generally. It didn't, however, solve all of the problems.

I have looked through your attachment and haven't noticed any use of default parameter values so far. I will stare at it a bit longer.
I'm taking this one step at a time -- I haven't looked closely at the text box actions yet, but I notice that the screen shot for the JOIN list shows the parameter's available values list properties. RS 2008 had a serious problem with available value lists that led me to not use them.

Including available values in subreport parameter definitions sometimes triggered what we came to call the "autocorrupt" bug where you would save your report in Report Designer (and check into source control), come back later to load it, and the RDL (XML) schema validation would fail because something had decided to add data type attributes that were not part of the schema to certain XML elements. It was painful, although we eventually got used to editing the XML directly and searching for and removing the junk, until we figured out that it was an SSRS bug involving available value lists that I had added to the subreport parameters.

What I am trying to say with these posts is that you can run into serious internal bugs in SSRS 2008 with parameters passed to subreports, and there are no fixes that I know of. You have to work around them. The only reason I had added the available value lists was to provide automatic translation between ID values and text translations of those values. We had to accomplish that a different way after we removed the available value lists.

So why is the subreport using available values? It looks like there is only one value, so it seems like it should not be needed.

I may answer my own question as I look at the next part of your attachment.
Ugh. I really hope that is not the answer. We have ALOT of reports that use a list of available values. It's sort of a stored procedure dynamic SQL nested nightmare that passes values stored as strings into the various and sundry RDL files and then continues passing to subreports.

Please post if you do find anything else that I might be able to try.

Thank you for your insight :)
Hi What is the value expression for the parameter available value in below screen shot.

is this parameter in Child or Parent ??

User generated image
ASKER CERTIFIED SOLUTION
Avatar of Lisa Callahan
Lisa Callahan
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I chose my own solution since my question had nothing to do with the answer :)

But thank you to the experts who posted!

-- Lisa
This is why, when reviewing someone else's report, it is a good idea to view the RDL file directly as XML. The Report Designer hides all sorts of things you would never imagine someone would do, not to mention obsolete coding that doesn't work any more.