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
,HAR_SERVICE_DELIVERIES.UNITS AS [HAR_SERVICE_DELIVERIES UNITS]
,SERVICE_DELIVERY_DAILY_DETAIL.UNITS AS [SERVICE_DELIVERY_DAILY_DETAIL UNITS]
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
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…….
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.
”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
With your subscription - you'll gain access to our exclusive IT community of thousands of IT pros. You'll also be able to connect with highly specified Experts to get personalized solutions to your troubleshooting & research questions. It’s like crowd-sourced consulting.
We can't always guarantee that the perfect solution to your specific problem will be waiting for you. If you ask your own question - our Certified Experts will team up with you to help you get the answers you need.
Our certified Experts are CTOs, CISOs, and Technical Architects who answer questions, write articles, and produce videos on Experts Exchange. 99% of them have full time tech jobs - they volunteer their time to help other people in the technology industry learn and succeed.
We can't guarantee quick solutions - Experts Exchange isn't a help desk. We're a community of IT professionals committed to sharing knowledge. Our experts volunteer their time to help other people in the technology industry learn and succeed.