| This post was kindly contributed by Business Intelligence Notes for SAS® BI Users - go there to comment and to read the full post. |
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!
You can learn about about SAS Business Intelligence from the new “SAS BI Bible.”
Take a peek inside the Building Business Intelligience with SAS book.
| This post was kindly contributed by Business Intelligence Notes for SAS® BI Users - go there to comment and to read the full post. |