×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Designer Questions

Designer Questions

Designer Questions

(OP)
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

RE: Designer Questions

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?

RE: Designer Questions

(OP)
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

RE: Designer Questions

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close