Create Paramater Filter by State

Question

Post reply

Create Paramater Filter by State

Welsh Corgi

SSC Guru

Points: 116520
More actions
January 11, 2012 at 8:28 am

#262694

I'm trying to create a parameter to filter a Report by State.
Any help would be greatly appreciated.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

So far I have created a Stored Procedure with a State Parameter.

I changed the Data Source to the Stored Procedure.

I have a Drop Down Box and it displays the States but they are repeated for every record on my Stored Procedure which is based on Companies.

I selected a State 'TX' and clicked on View Report and it filters the Report.

I click on the State List box and it and it only lists 'TX', the other States are not displayed.

I click to refresh the Data but I sill on get TX.

I do not want the States to be distinct.

How can I accomplish that & get rid of the filter problem.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

matt.jonas Valued Member Points: 59 More actions · Answer 2

Can you post code for the stored procedure you are calling to populate your State dropdown box?

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

matt.jonas (1/11/2012)
Can you post code for the stored procedure you are calling to populate your State dropdown box?

I have made some progress.

I created another Data Set to poulate the list box and that works.

SELECT StateAbbr

FROM States

I Select a State 'AK', click on View Report and it Filters the records.

If I select another State from the list 'TX' and Click on View and it still displays 'AK'

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

I deleted the parameter but the data is still filtered by State?:laugh:

Caching?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

matt.jonas Valued Member Points: 59 More actions · Answer 5

Which version of SSRS are you using? Are you doing your testing in Business Intelligence Development Studio?

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

matt.jonas (1/11/2012)
Which version of SSRS are you using? Are you doing your testing in Business Intelligence Development Studio?

SSRS 2005 & I'm testing in BIDS.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gazareth One Orange Chip Points: 27737 More actions · Answer 7

IIRC BIDS caches results locally. There is a way to make it run a fresh set but I forget how!

You can force it by deleting the cached data from the file system - it'll have the same name & path as the rdl file.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

Thanks everyone, I deleted the parameters and Dataset, recreated and all of my problems went away.:crazy:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

I spoke to soon, modified the SP to Accept a NULL value and the report defaults to the last State that I selected in the Drop Down Box.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gazareth One Orange Chip Points: 27737 More actions · Answer 10

I think that's BIDS caching again -I think you need to exit the report, resave, and then try running it again.

You shouldn't see that behaviour in a deployed report 🙂

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 11

Thanks. The problem that I'm dealing with is that my Stored Procedure allows nulls but I must Select a State or I get an error.:hehe:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 12

Meaning that you want to be able to pick all states? (by making no selection) if so, then create your statement dynamically and only drop in the state if selected. If that's not what you want, please clarify.

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 13

jcrawf02 (1/11/2012)
Meaning that you want to be able to pick all states? (by making no selection) if so, then create your statement dynamically and only drop in the state if selected. If that's not what you want, please clarify.

Excuse me but I have written a lot of reports in many different tools but that is not the case with SSRS.

I did not know that you had the option to select all states until I checked the multi-select check box.

So please disregard that statement.:-)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jason Wolfkill SSCrazy Eights Points: 9832 More actions · Answer 14

Welsh Corgi (1/11/2012)
matt.jonas (1/11/2012)
Can you post code for the stored procedure you are calling to populate your State dropdown box?
I have made some progress.
I created another Data Set to poulate the list box and that works.
SELECT StateAbbr
FROM States
I Select a State 'AK', click on View Report and it Filters the records.
If I select another State from the list 'TX' and Click on View and it still displays 'AK'

As others have mentioned, this is most likely caused by the fact that BIDS caches the dataset the first time you generate a report previews and uses the cached data every time you re-run the report preview. There's a "Refresh" button on the Preview tab toolbar - click it to make BIDS retrieve new data from the data source to generate the report preview.

Jason Wolfkill