November 19, 2010 at 7:48 am
I've built a simple report which has a parameter in it, users choose parameters from a drop down list. I have specified the values for the dropdown list using a query where the ID field maps to the value field and the varchar field maps to the label.
I'd like this dropdown to have a default value, and not one which is part of the above dropdown list.
Within my query I'd like the default value to be zero, and if this maps through to a varchar just some text like 'Nothing selected'
I've tried specifying a default value of 0 and one of 'Nothing Selected' neither work, the dropdown list still gets stuck on 'select values'
Does anyone know what I'm doing wrong?!
Thanks!
November 19, 2010 at 1:49 pm
rob, is this a multiselect parameter or just a single value parameter?
If multiselect it gets slightly harder as you have the select all bit to worry about, but if it's single select, just alter your query a bit like so...
Select ID as value, text as label
From ...
UNION ALL
Select 0 as value, 'Nothing Selected' as label
That way the parameter will be valid when you make a value of 0 your default.
You just need to handle in your dataset (query/stored procedure) what to do in the case that some actually tries to run the report with a value of 0.
-Luke.
November 22, 2010 at 4:00 am
Thanks Luke. That makes perfect sense and seems to be working as I expected it 🙂
Rob.
November 22, 2010 at 6:35 am
November 22, 2010 at 8:02 am
Incidentally, what does seem very odd is I have 6 identical parameters, all have been configured in the same way, even looking at the code in the report they are the same:
<ReportParameters>
<ReportParameter Name="Book1">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>0</Value>
</Values>
</DefaultValue>
<Prompt>Book1</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>DataSetProducts</DataSetName>
<ValueField>product_id</ValueField>
<LabelField>product_description</LabelField>
</DataSetReference>
</ValidValues>
<UsedInQuery>True</UsedInQuery>
</ReportParameter>
vs
<ReportParameter Name="Book3">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>0</Value>
</Values>
</DefaultValue>
<Prompt>Book3</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>DataSetProducts</DataSetName>
<ValueField>product_id</ValueField>
<LabelField>product_description</LabelField>
</DataSetReference>
</ValidValues>
<UsedInQuery>True</UsedInQuery>
</ReportParameter>
Yet parameters 1 and 2 have the correct default values when deployed and viewed via Report Manager and 3-6 have incorrect values <Select a Value>
I've deployed the report again and tried logging into Report Manager via IE and Firefox after, to no avail.
I do fear I'm missing something obvious here!
Thanks in advance...
November 22, 2010 at 8:10 am
Not that I Think this makes any difference whatsoever, but from time to time ?I've had issues with SSRS updating a portion of a report when I redeploy it. (mostly this is from changes in the description not something like a parameter) The simplest fix for me when this happens is to just delete the report and redeploy it from Visual Studio.
Additionally, I might think about the datatype you are using. If you're passing an integer (0 or some other number) I'd set up the parameters as integers. That way there would be less need of conversions and such.
again this is something that really shouldn't make any difference, but...
Also, you can try to download the .rdl that SSRS is using, from within Report Manager. Under the properties tab, click Edit, then save it and open it with Bids.
Also, be sure no one is overiding your defaults from within Report manager as this can be a problem as well.
-Luke.
November 22, 2010 at 8:36 am
Thanks again Luke! - Copy/paste code to a new report and deploy seemed to work fine.
I'll bear in mind the other points you mentioned, right now I need to get this out ASAP, I could potentially re-jig it a little later on though 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply