1. On my Time Dimension table I have a column called 'Relative Week Code' containing numbers relating to the business week e.g. 0 is Current Week, -1 is Last Week, -2 is Previous Week, etc etc. I would like to create an object to use in reports that translates these codes into the textual descriptions. As a BusinessObjects user I would create an object using a CASE statement - How do I replicate this in MS7?
As a test I created a report using a filter of 'Relative Week Code = 0' and included a custom group object that I thought would answer my text conversion question, however the SQL produced was a number of seperate statements (4 in total, one for each group) that ANDed two Relative Week Code objects e.g. 0 and 0, 0 and -1, 0 and -2 etc. This is not really what I imagined I would get, and not particularly efficient either.
2. Using my 'Relative Week Code' I would also like to create a comparison report i.e. TW vs LW. I have created two filters, one as 'Relative Week Code = 0' and another '...= -1', however I am struggling to see how to incorporate both and still bring back data! In BObj I would simply create a union query and change the filter.
Thanks in advance