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
Solved

SSRS multiple values parameter not working

Posted on 2014-09-26
10
1,138 Views
Last Modified: 2014-10-03
I have a report showing Sales Order information, basically a Header section and a Sub-report showing the lines. The report has a parameter defined for the order number. Everything works great when showing a single order.

However, if select more than 1 order in the parameter drop-down, then the report is blank.
I'm using 3 data sets: Header Detail, Line Detail & a 3rd for the Order Number query.

The parameter value(s) for the header and lines datasets are set to use the values returned from this 3rd dataset.

This piece is working as I do get a correct list of Order Numbers to select in the drop-down, it just won't work if I choose more than 1 (or All).

What am I missing??
0
Comment
Question by:BrianFord
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40345892
I don't have an immediate answer for you, but SQL expert and MVP Valentino Vranken has an article out on Reporting On Data From Stored Procedures (part 2), where the top section is Creating A Multi-Value Report Parameter.

Good luck.
Jim
0
 

Author Comment

by:BrianFord
ID: 40345931
Thanks,

I'm not using a stored Procedure but my report is setup just like this in that it uses the same method of joining the parameter, i.e.

 (=Join(Parameters!DepartmentList.Label, ", ")"

but it just doesn't show multiple records :(
0
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 40345969
Hi Brian,

Are you using a split function to separate your parameter in separate values? When using a multi value parameter SSRS passes this as a string value to the dataset. You have to split them before they can be used in an "IN" statement.
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 37

Expert Comment

by:ValentinoV
ID: 40345971
If you're not using a stored proc then you don't need to use Join() to pass the multi-valued parameter into the dataset.  Just specify your parameter as if it were single-valued: Parameters!OrderNumber.Value

And use IN() in the WHERE clause: WHERE YourOrderNumberField IN ( @OrderNumber )
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 40345974
Koen: the join/split is only needed when using stored procs.  If the query code resides in the dataset then somehow SSRS knows how to deal with the array of values.
0
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 40345984
Ah, ok. Thanks for that piece of info Valentino. I've only ever worked with procedures and assumed it was always required.
My apologies Brian.
0
 

Author Comment

by:BrianFord
ID: 40346234
Thanks guys.  I'll check this out later today and let you know how I get on
0
 

Author Comment

by:BrianFord
ID: 40346667
OK, getting closer :)

The report now runs and show information, however it shows ALL the lines for ALL the orders against just the one order header.

Here's what I have:
A Parameter called OrderNumber
An OrderNumber dataset (a query which returns a list of order numbers) that the above parameter uses
A header dataset -  which uses the above parameter (=Parameters!OrderNumber.Value)
A Lines Dataset - which again uses the above parameter (=Parameters!OrderNumber.Value)

Pretty sure I'm missing something that splits the report by header record?
0
 
LVL 13

Accepted Solution

by:
Koen Van Wielink earned 500 total points
ID: 40347476
Sounds like there's something amiss in your SQL query, but hard to tell without actually seeing it.
Also, is there a particular reason you have separate datasets for the headers and lines? Can't you use a single dataset and use groups to separate the two? This would prevent your report from having to run over your orders twice, most likely improving performance.
0
 

Author Closing Comment

by:BrianFord
ID: 40360367
thanks
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

Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

860 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