Post reply

Changing filters using custom code

  • October 2, 2012 at 4:08 am

    #262483

    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

  • October 2, 2012 at 6:54 am

    #1544341

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

  • October 2, 2012 at 7:06 am

    #1544354

    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.

  • October 2, 2012 at 11:30 am

    #1544516

    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

  • October 2, 2012 at 12:39 pm

    #1544546

    Cheers Daniel

    Looks good.

    Thanks for your help.

Viewing 5 posts - 1 through 4 (of 4 total)

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