Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 36
  • Last Modified:

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
0
Lisa Callahan
Asked:
Lisa Callahan
  • 4
  • 3
  • 2
1 Solution
 
Arifhusen AnsariBusiness Intelligence AnalystCommented:
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,","),",")
0
 
Lisa CallahanBusiness Intelligence AnalystAuthor Commented:
The value is passed to a stored procedure.

Thank you for the suggestion, I will try it anyway - you never know :)
0
 
Megan BrooksSQL Server ConsultantCommented:
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.
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
Megan BrooksSQL Server ConsultantCommented:
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.
0
 
Lisa CallahanBusiness Intelligence AnalystAuthor Commented:
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 :)
0
 
Arifhusen AnsariBusiness Intelligence AnalystCommented:
Hi What is the value expression for the parameter available value in below screen shot.

is this parameter in Child or Parent ??

Test.png
0
 
Lisa CallahanBusiness Intelligence AnalystAuthor Commented:
Thank you all for your comments and suggestions. I am happy (and frustrated at the same time) to say that the issue is NOT with the Join parameters in either report. In 2005 there was a hidden group in the matrix - I am not totally sure why but it has something to do with the report designer's code to color every other row. My fix was to simply show the group and then remove all references to the coloring scheme. And voila!

So thank you for you assistance.
0
 
Lisa CallahanBusiness Intelligence AnalystAuthor Commented:
I chose my own solution since my question had nothing to do with the answer :)

But thank you to the experts who posted!

-- Lisa
0
 
Megan BrooksSQL Server ConsultantCommented:
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.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now