Home » BI Tools

Web Report Studio: Adding Drill-Down Filter Based on a Date

Submitted by on May 21, 2012 – 8:04 am

When I was creating the Summary and Detailed reports for the SAS Global Forum paper, I was demonstrating how to link from the weekly chart to the detailed report about the week.  On my first try with my Week Filter based on the date value – it just would not work.  Eeek!  To fix the problem I made a new data item that was a character value.  This posts talks about my strategy.  [You can read our paper "Get Your Fast Pass to Building Business Intelligence with SAS and Google Analytics".  The presentation goes into more detail, but here's some past articles about Google Analytics. ]

What Should Happen

When you click on the bar that represents the week – you actually drill-down to the Weekly Detailed report. You cannot use a date value as this filter – you must use a character based filter for this prompt.  If you need help setting up the filter or creating the link – just refer to the  ”Get Your Fast Pass to Building Business Intelligence with SAS and Google Analytics” paper.

Creating the Proper Filter in the Information Map

First you need to create a data item based on the date variable.  I used a CASE statement to test if the week was less than 10.  If so I added the zero to string so all of dates would look nice and sort the way I wanted.  I used the Concatenate function (CATS) along with the YEAR() and WEEK() functions to create the new data item.  You’ll see the CASE statement – the code first tests if the week is great then 10.  The Week function returns the week number starting on Sunday.  So 01JAN2012 is Week 1, 08Jan2012 is Week 2, and so on.  

If the week is less than 10, then the code creates a new value.  CATS function places the values together while remove all extra white space. For this character value I want the end result to be 2012WK03.  To get that value I need to extract the year from the date using the Year function. Add the character values – which is “WK0″ or “WK” and then extract the week number.

If the week is less than wk 10 I add a 0 to the value – so it will look like this:  2012WK07.  If it’s after week 10, then it produces this value: 2012WK22.  I want all the values to look the same so they line up neatly. 

 

Then you can create your filter and associated prompt based on the Week data item – as shown in the following figure.  

Looking into Web Report Studio

Here’s my data item in Web Report Studio .  I like for the numbers to line up – I think it looks more professional.  Plus the best part is that it works now!

 

Learn all about SAS BI

You can learn more about SAS Business Intelligence from the "SAS BI Bible." Take a peek inside the Building Business Intelligience with SAS book.

The following two tabs change content below.
Tricia Aanderud is a SAS Business Intelligence and Visual Analytics consultant based in Raleigh, NC. She has written several books about SAS, presented papers at many SAS conferences, and has over 10 years of SAS programming experience. Contact her for assistance with your next project.

Tags: , , , ,