Designer Questions

[LeeBrown]

I have a number of questions that I hope you can help me with...

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
Lee

 

[reisw]

Question 1.
Using an ApplySimple or other "Apply..." syntax you can bypass the MSTR engine and run the SQL against the database. There is also a case function provided in the tool as well so you don't need to know the database syntax. I'm not quite sure what you are trying to do here but I hope what I said helps.

Question 2.
It sounds like you are creating two metrics called TW and one called LW. Defining another metric that does the comparison would do the trick. When creating the TW and LW metrics you could either embed a filter in each metric that would give you the time period or you could use a transformation to calculate the weeks based on some report filter criteria. Does this help?

 

[LeeBrown]

Thanks. I've now got the case statement produced in the SQL, however when I include a metric it is not included in the group by and hence the SQL will not run. Any ideas?

All I am trying to acheive is to convert a code into a meaningful description.

I'll have a go at the comparison report and let you know.

Thanks again
Lee

 

[nlim]

for 2: I suggest you try using transformations instead of hardcoding the previous week code, just because you can easily drill around after you have the report.

create a transformation called "One period ago" on the attribute week defined by "weekid-1".
create a new metric, add this transformation into its definition.

now you can have this type of report:

weekid metric previous period metric
------ ------ ----------------------

if you have a similar situtation for last month, last year; add these transformations into the "one period ago" transformation. now you can drill from year to month to week and the "previous metric" will change from year to month to week automatically.