May 18, 2011 at 9:55 am
Hi,
I have a report with 4 parameters. The first two parameters select a year. The third parameter selects an agency id. This parameter is populated by a query. The query has no null value. I need to be able to have null so I can run the report for all agencies in the query. I have tried making the parameter a multi value parameter but when I select "All" the agencies and try to run the report, I get the following error
Cannot add multi value query parameter '?' for dataset 'FatalSevrityFinal' because it is not supported by the data extension.
. Is there a way to add a null value to the query so I can run it for all agencies?
May 18, 2011 at 10:04 am
Can't you have this multi-select parameter nullable in SSRS (never tried)?
Either that or select all regions by default.
May 18, 2011 at 10:10 am
I tried making the multi-select parameter null, but it wouldn't allow that.
May 18, 2011 at 10:16 am
I tried selecting all regions by default as well and that didn't work. I keep getting the original message.
May 18, 2011 at 10:31 am
I've never had that error. Google wasn't helpful?
May 18, 2011 at 2:28 pm
I have a report where the main dataset has a location parameter in the where clause like this:
AND location IN(@location)
I then created a second dataset that queries all of the possible locations. The location parameter gets its available and default values from this second dataset and I checked the "allow multiple values" box. When the report runs it has the "select all" checkbox selected and I get all my locations by default. Would this not work for you? Sorry if I did not understand the question but I'm not sure why you would need a null value.:blink:
November 29, 2011 at 1:55 pm
You can either have multi-select parameter or null, not both.
if using multi-select parameter and any agency_name and agency_id is null,
it wont display the record, so your best bet is to use null.
---
main dataset:
select agency_name, agency_id, agency_branch, city, country
from mytable
where agency_name in (@agency)
---
To cater for nulls -
agency parameter dataset:
select agency_name as label, agency_id as value from mytable
union
select 'ALL' as label, NULL as value
order by label
right-click @agency parameter, select properties, select null checkbox.
select second tab on left from top (below general),
select agency (dataset), select value for Value, select label for Label
when running report, agency parameter will display option ALL (by default as first option).
use ALL for agency parameter.
---
To cater for multi-select -
agency parameter dataset:
select agency_name as label, agency_id as value from mytable order by agency_name
right-click @agency parameter, select properties, select multi-value checkbox.
select second tab on left from top (below general),
select agency (dataset), select value for Value, select label for Label
when running report, agency parameter will display checkboxes for every agency,
select those agencies you require (say you see 10 agencies in the list, you
can choose <Select all> to get report info for all 10, else select say only 5 and report will
return info for only those 5 selected).
Remember, multi-select won't display info for NULL's, so if one of the agencies has a null value
for agency_name and agency_id, that agency won't de displayed in the parameter drop down box,
so obviously it's info won't be returned by report.
November 29, 2011 at 2:42 pm
What is the data source for the agencyID? Can you show the code you are using to populate the parameter? Also please show the code where you are trying to use the multiple values.
December 1, 2011 at 12:31 pm
Hi Daniel Bowlin, I just updated my previous post.
Regards.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply