Default Value for a Queried Parameter

[plarson13]

Hi All,
I have created a report parameter with a query for the list of available values. This works well and you are able to see the values and select the one you want and the report displays the correct records. When I try to assign a non-queried value to the default value the report seems to ignore the default value and comes up in the preview window as if there wasn't any default value and you have to select a parameter. I have made sure the the default value was correct. Any ideas?

 

[Catadmin]

I just tested one of my reports and I come up with the same issue if the default value I put in is not one of the values in the queried list of values. If I make sure the default value is exactly the same as one of the queried values (exact spelling AND case), then it fills in properly.

What is the query you're using to populate the default to begin with? You might be able to use it to force the default value at the top of the list (which is what I did).




Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[plarson13]

I am only using a query to populate the available values not the default values. This is the query:

SELECT CLASSID, CLASDSCR
FROM RM00201
UNION
SELECT 'ALL', '-ALL CLASSES'

If I use the value of ALL for a default value it works but the default value does not work for any of the other values from the query.

 

[Catadmin]

Unfortunately, it doesn't work that way. Try to force the "All" in the queried list like this.

Create Procedure ddsp_AllTableNames

AS
-- TempTable for List population w/Identity for sorting
Declare @tbllist Table (id int identity(1,1), TableName VarChar(50))

-- Forcing an "All" into the top of the list
Insert into @tbllist(TableName) Values('All Tables')


-- Pulling all other query values into TempTable
Insert into @tbllist 
Select vw.TableName from (Select Distinct Top 100 Percent TableName 
from dbo.vwApacsBaseTableList with(nolock)
order by 1) vw

-- Returning all values from TempTable by Identity value
Select TableName from @tbllist
order by id

GO

I used the above for a data dictionary, but the principle works. The view in the code was just to further filter the data before I called it into the report. You can pull straight from a table.

Once you have your dataset correctly coded, then just set the non-queried default value of your parameter to exactly what you chose as your ALL value, case and all. SO, if you chose ALL, then set the value as ALL. If you chose All Values, set the default as All Values, and if you chose ALL Values, set the default as ALL Values.

Make sense?

Let me know if I need to explain it more. My dataset, BTW, was "exec ddsp_AllTableNames" in the Text command type.




Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[plarson13]

I created the following query for the parameter available values.

DECLARE @classlist TABLE (id int IDENTITY (1, 1), classid Char(15), clasdscr Char(31))
INSERT INTO @classlist (Classid, ClasDscr) VALUES ('All', 'All Classes')
INSERT INTO @classlist SELECT classid, clasdscr FROM dbo.rm00201
SELECT classid, clasdscr FROM @classlist ORDER BY id

This works for the parameter list to choose from, but does not allow me to specify a default value for the parameter.

 

[Catadmin]

Which of those two values, Classid or clasdscr, did you set the Paramater to equal in the Values section?



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[plarson13]

classid

 

[Catadmin]

And you are using "All" (minus the quotes) as the default non-queried value? Exact case too?

If it's still not working, I'd advise cutting this dataset down to 2 columns. 1 as the identity value to sort on and the other with the values you want to populate on the drop down list.

You can always use this param in another dataset/SP to call the other column's worth of details later on in the report.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[plarson13]

I have been trying other reports using different parameters and found that if I use a query for the available values and not use the "All" addition I still can't select a default value. I think the problem is something other than the table variable we created. And yes I was using the exact "All" (minus the quotes).

 

[Catadmin]

That's really strange. With the code I listed above, I was able to force a non-queried default value into my Parameter as long as it was part of the list. What SP does your Reporting Services have? I'm working off of SP2.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[plarson13]

sp1, I will try sp2.

 

[plarson13]

SP2 didn't help, I still can't get a default value to work

 

[plarson13]

If I change the available values to non-queried and place several there, then the default value works just fine.

 

[Catadmin]

Something strange is going on with your machine or software. It shouldn't have been that hard for you to do this. Maybe you should contact Microsoft Support and see if they have an idea why you couldn't get it to work.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[plarson13]

I tried it on another machine and got the same results.

 

[Catadmin]

Did you try this with dropping the third column in the dataset, only pulling the identity and the required values list?



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[plarson13]

Yes