Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Create Paramater Filter by State Expand / Collapse
Author
Message
Posted Wednesday, January 11, 2012 8:28 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, July 27, 2014 6:10 AM
Points: 4,179, Visits: 4,260
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/

Post #1234036
Posted Wednesday, January 11, 2012 9:18 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, July 27, 2014 6:10 AM
Points: 4,179, Visits: 4,260
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/

Post #1234100
Posted Wednesday, January 11, 2012 9:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 11:23 AM
Points: 7, Visits: 65
Can you post code for the stored procedure you are calling to populate your State dropdown box?
Post #1234118
Posted Wednesday, January 11, 2012 9:39 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, July 27, 2014 6:10 AM
Points: 4,179, Visits: 4,260
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/

Post #1234133
Posted Wednesday, January 11, 2012 9:43 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, July 27, 2014 6:10 AM
Points: 4,179, Visits: 4,260
I deleted the parameter but the data is still filtered by State?

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/

Post #1234137
Posted Wednesday, January 11, 2012 9:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 11:23 AM
Points: 7, Visits: 65
Which version of SSRS are you using? Are you doing your testing in Business Intelligence Development Studio?
Post #1234148
Posted Wednesday, January 11, 2012 9:55 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, July 27, 2014 6:10 AM
Points: 4,179, Visits: 4,260
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/

Post #1234153
Posted Wednesday, January 11, 2012 9:59 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:28 AM
Points: 1,872, Visits: 2,998
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.
Post #1234160
Posted Wednesday, January 11, 2012 10:32 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, July 27, 2014 6:10 AM
Points: 4,179, Visits: 4,260
Thanks everyone, I deleted the parameters and Dataset, recreated and all of my problems went away.



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/

Post #1234185
Posted Wednesday, January 11, 2012 10:55 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, July 27, 2014 6:10 AM
Points: 4,179, Visits: 4,260
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/

Post #1234212
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse