Avatar of Lisa Callahan
Lisa Callahan
Flag 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

Avatar of undefined
Last Comment
Megan Brooks

8/22/2022 - Mon
Arifhusen Ansari

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.

Lisa Callahan

The value is passed to a stored procedure.

Thank you for the suggestion, I will try it anyway - you never know :)
Megan Brooks

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Megan Brooks

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.
Lisa Callahan

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 :)
Arifhusen Ansari

Hi What is the value expression for the parameter available value in below screen shot.

is this parameter in Child or Parent ??

Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Lisa Callahan

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Lisa Callahan

I chose my own solution since my question had nothing to do with the answer :)

But thank you to the experts who posted!

-- Lisa
Megan Brooks

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.