• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1032
  • Last Modified:

Select All in Multivalue Parameters does not return any values

Utilizing SQL Visual Studio 2005 Create Matrix:


STEP 1:
SQL Query for Dataset1
Select SALESREP, CUSTOMER, SUM(AMOUNT) AS AMOUNT
FROM SALESDATA
WHERE SALESREP=@SALESREP
GROUP BY SALESREP, STATE, CUSTOMER

Step 2
Setup Dataset SALESREP
SELECT DISTINCT SALESREP
FROM CUSTOMER

Step3
Setup Report Parameter
Name SALESREP
Data Type Text
Prompt Sales Rep
Select Multi-Value
From Query:
Data Set SALESREP
Value Field SALESREP
Label Field SALESREP
Default None

Step 4
Attach Filter to Report
From Layout, right-click, select Properties
Filter tab
Expression: =Field!SALESREP.Value
Operator: In
Expression: =JOIN(Parameters!SALESREP.Value,",")

Step 5
Attach Filter to Dataset1
From Data, Dataset Properties
Filter tab
Expression: =Field!SALESREP.Value
Operator: In
Expression: =JOIN(Parameters!SALESREP.Value,",")

Preview Report
Select All from SALESREP Drop Down, Preview Report
No information returned.
Select one value in Drop Down, Preview Report
Report reflects information for selected SALESREP.

I am not a programmer, so would like to avoid writing any custom functions.  If a custom function is the only please lead me to basic instructions for writing custom functions.
Thank You
0
tberardi
Asked:
tberardi
  • 4
  • 2
1 Solution
 
PFrogCommented:
In your filters, you need to remove the join clause. SSRS deals with multi valued parameters internally, so you can just use
    =Parameters!SALESREP.Value
as your filter
0
 
tberardiAuthor Commented:
I originally used the =Parameters!SALESREP.Value as filter, but received same results, when Select All, no information returned.  Since I'm new to SSRS, I've been trying many options to resolve.  If a custom function is the solution, I'd appreciate a SSRS step-by-step resource to resolve.
Thanks for responding in my desparation!
0
 
PFrogCommented:
You definately don't need a custom function for this.

Leaving the filter as
    =Parameters!SALESREP.Value
what happens when you just select two values?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
PFrogCommented:
Hold on, just noticed....

change this
  SQL Query for Dataset1
  Select SALESREP, CUSTOMER, SUM(AMOUNT) AS AMOUNT
  FROM SALESDATA
  WHERE SALESREP=@SALESREP
  GROUP BY SALESREP, STATE, CUSTOMER

To
  WHERE SALESREP IN (@SALESREP)
0
 
PFrogCommented:
In which case, you don't need to specify any filters for the dataset, as you are doing it within the query.
0
 
tberardiAuthor Commented:
To complete the steps, I had to add the following to the query to accomodate for null values:

Select SALESREP, CUSTOMER, SUM(AMOUNT) AS AMOUNT
  FROM SALESDATA
  WHERE (SALESREP IN (@SALESREP) OR 'NONE' IN (@SALESREP))
  GROUP BY SALESREP, STATE, CUSTOMER

0
 
daveswbCommented:
Question that would work on a dataset with the query in the report. What if you want to do it against a stored proc? I have data comming back from a proc I want to filter after the fact. Is that possile?
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now