troubleshooting Question

Report Builder 3.0 - Multi-Select Parameters

Avatar of gberkeley
gberkeley asked on
Microsoft SQL Server
3 Comments1 Solution1208 ViewsLast Modified:
What am I doing wrong????  

Here are the steps taken thus far: See attached to get all the screen shots.

1. I have included the parameter in the main dataset query and then I created separate datasets to enable drop downs for selection.  I want the user to be able to choose Care Manager and Service values from a pick list.  I also want the user to be able to do a multi-select so the report can pull more than one service and/or more than one manager.

 

 
2.  I have created both parameters (Under Parameters vs wtihin their datasets) using the following conventions for configuration:

a.  Select Data Type as text and allow blank values.

Cannot use integer because the identifier is alpha-numeric.

 

b. Set available values to pull from query – the datasets created for the drop downs.

 

 
c.  Set default value to none.

 

3. The dataset for use in the drop down is configured as follows:

a. Single field for the pick list

 

b.  no configuration for options, filters, or parameters.

 

4.  Here is the query for the main dataset

SELECT
  HAR_CONSUMERS.CONSUMER_AGENCY_DES
  ,HAR_SERVICE_DELIVERIES.PROVIDER
  ,HAR_SERVICE_DELIVERIES.FUND_IDENTIFIER
  ,HAR_SERVICE_DELIVERIES.SERVICE
  ,HAR_SERVICE_DELIVERIES.UNITS AS [HAR_SERVICE_DELIVERIES UNITS]
  ,HAR_SERVICE_DELIVERIES.CREATE_DATETIME
  ,HAR_CONSUMERS.PRIMARY_CARE_MANAGER
  ,SERVICE_DELIVERY_DAILY_DETAIL.SERVICE_DELIVERY_DAILY_DETAIL_UUID
  ,SERVICE_DELIVERY_DAILY_DETAIL.UNITS AS [SERVICE_DELIVERY_DAILY_DETAIL UNITS]
  ,SERVICE_DELIVERY_DAILY_DETAIL.SERVICE_DATE
  ,SERVICE_DELIVERY_DAILY_DETAIL.SERVICE_DELIVERY_UUID
FROM
  HAR_CONSUMERS
  INNER JOIN HAR_SERVICE_DELIVERIES
    ON HAR_CONSUMERS.CONSUMER_UUID = HAR_SERVICE_DELIVERIES.CONSUMER_UUID
  INNER JOIN SERVICE_DELIVERY_DAILY_DETAIL
    ON HAR_SERVICE_DELIVERIES.SERVICE_DELIVERY_UUID = SERVICE_DELIVERY_DAILY_DETAIL.SERVICE_DELIVERY_UUID
WHERE
  HAR_SERVICE_DELIVERIES.CREATE_DATETIME >= @CREATE_DATETIME
  AND HAR_SERVICE_DELIVERIES.CREATE_DATETIME <= @CREATE_DATETIME2
  AND HAR_CONSUMERS.CONSUMER_AGENCY_DES = @CONSUMER_AGENCY_DES
  AND HAR_SERVICE_DELIVERIES.SERVICE = @SERVICE
  AND HAR_CONSUMERS.PRIMARY_CARE_MANAGER = @PRIMARY_CARE_MANAGER

5. Here is the query for the drop down data set (only showing one, both have same construction) – bonus points if you can tell me the right syntax to make this sort in alpha order on display…….

SELECT
  HAR_SERVICE_DELIVERIES.SERVICE
FROM
  HAR_SERVICE_DELIVERIES
GROUP BY
  HAR_SERVICE_DELIVERIES.SERVICE

6.  I can see in the constructed report that the parameters are being correctly rendered as available for multi-select:

 


7. But when I try to run any combination involving a multiple selection on either of the two multi-select parameters, the system bombs out and fails to execute the report.

 

 

HELP!!! How do I fix this so the user can apply a multi-select at run time?
MultiSelect-Screen-Shots.doc
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros