Solved

SSRS 2005 to 2008 Join Parameters Issue

Posted on 2016-10-04
10
25 Views
Last Modified: 2016-10-08
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
Comment
Question by:Lisa Callahan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
10 Comments
 
LVL 12

Expert Comment

by:Arifhusen Ansari
ID: 41828396
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
 

Author Comment

by:Lisa Callahan
ID: 41828468
The value is passed to a stored procedure.

Thank you for the suggestion, I will try it anyway - you never know :)
0
 
LVL 14

Expert Comment

by:Megan Brooks
ID: 41828501
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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 14

Expert Comment

by:Megan Brooks
ID: 41828558
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
 

Author Comment

by:Lisa Callahan
ID: 41828653
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
 
LVL 12

Expert Comment

by:Arifhusen Ansari
ID: 41829659
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
 

Accepted Solution

by:
Lisa Callahan earned 0 total points
ID: 41829937
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
 

Author Comment

by:Lisa Callahan
ID: 41830319
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
 
LVL 14

Expert Comment

by:Megan Brooks
ID: 41830490
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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
Written by Valentino Vranken. Introduction: In a previous article (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Reporting-On-Data-From-Stored-Procedures-part-1.html) I announced that I would writ…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question