Changing filters using custom code

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Reporting Services » Reporting Services » Changing filters using custom code

Changing filters using custom code

Rate Topic

Display Mode

Topic Options

Author

Message

ByronOne

Posted Tuesday, October 02, 2012 4:08 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 11, 2013 2:33 PM
Points: 125, Visits: 325

Hi

I have a report which contains around 20 separate matrix tables. The tables all contain fllters one of which specifies the month of sales we are interetsed in presenting. At the moment I have to change all the group filters individually in order to present different months of data ie if I want September sales I need to change the filter to "Sep 12". Is it possible to write custom code which all the tables could reference and which would only need to be changed the once?

Thanks in advance.

BO

Post #1366933

Daniel Bowlin

Posted Tuesday, October 02, 2012 6:54 AM

SSCrazy

Group: General Forum Members
Last Login: Friday, May 17, 2013 11:53 AM
Points: 2,672, Visits: 2,416

Rather than use filters, try using parameters in your dataset query.

Post #1366998

ByronOne

Posted Tuesday, October 02, 2012 7:06 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 11, 2013 2:33 PM
Points: 125, Visits: 325

I actually simplified my problem a little.

Each of the tables in the report looks at different periods of sales and a few of them look at all sales for the year so parameters won't really work in this scenario.

Post #1367012

Daniel Bowlin

Posted Tuesday, October 02, 2012 11:30 AM

SSCrazy

Group: General Forum Members
Last Login: Friday, May 17, 2013 11:53 AM
Points: 2,672, Visits: 2,416

So probably your best approach is to make the filter for each table the same relative to the current (or requested) date. For example if you run the report today you want one table to have data consisting of last month. So your filter would be something like
BETWEEN DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,'1900-01-01',GetDate()),'1900-01-01')) AND DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,'1900-01-01',GetDate()),'1900-01-01'))

Two months ago would be
BETWEEN DATEADD(mm,-2,DATEADD(mm,DATEDIFF(mm,'1900-01-01',GetDate()),'1900-01-01')) AND DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,'1900-01-01',GetDate())-1,'1900-01-01'))

and so on

Post #1367197

ByronOne

Posted Tuesday, October 02, 2012 12:39 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 11, 2013 2:33 PM
Points: 125, Visits: 325

Cheers Daniel

Looks good.

Thanks for your help.

Post #1367232

« Prev Topic | Next Topic »

Permissions