Solved

SSRS 2005 to 2008 Join Parameters Issue

Posted on 2016-10-04
10
17 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:ldcallahan
  • 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:ldcallahan
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 13

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
 
LVL 13

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:ldcallahan
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:
ldcallahan 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:ldcallahan
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 13

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Steps to solve SSRS SQL 2008 R2 User Access Control (UAC) Permission Error With the introduction of SQL Server 2008 R2 and Vista (Windows 7 as well) came new enhanced security features. One of the features included was User Access Control (UAC) t…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now