Creating last month and variance objects in Designer

[Gaye]

Can anyone help ???

We are using WebIntelligence to create our reports and therefore are creating any required objects in the universe. One of our needs is to create a report which shows the current month, prior month and variance amounts for a given account.

We have a calendar table with a field which indicates what the current reporting period is. We have managed to create a last month dimension to return the prior month date but we are having difficulty creating the value object.

Additionally, we are using SQL server. We are desperate to resolve this issue ASAP so if anyone can help, we'd be very grateful.

Gaye

 

[skrandel]

Without seeing your database it's impossible to solve this. However, could you investigate using the CASE statement to get what you want?

If you can't do that, then perhaps you could write a Store Function. Steve Krandel
BASE Consulting Group

http://www.baseconsulting.com

 

[blom0344]

If you are just interested in information from current and prior month, you could de-normalize using a database view to store information columnwise , instead of row-wise. This will only work of course for a very small set, like in your case 2 months, but it makes assigning objects to fields very easy, if you use the view as the datasource..... T. Blom
Information analyst
tbl@shimano-eu.com

 

[funkmonsteruk]

We have an NT platform and i've been using objects created at the universe level to dictate timescales.

Try creating a measure as "CURRENT DATE - 1 DAY" (This would indicate yesterday) format - This can be extended to incorporate most timescales eg "CURRENT DATE - 7 DAYS" (A WEEK AGO)
First Day of this month = CURRENT DATE - (DAY(CURRENT DATE) - 1)DAYS
last Monday = DATE(DAYS(CURRENT DATE)/7*7)

You'll find if you parse the new object that it will say you have an error - ignore this it should still work

 

[Gaye]

Thanks funkmonsteruk but can you tell me how to associate the time measure with the value for that period ??

 

[funkmonsteruk]

Once you have created the time measures they become objects within the BO Universe, it is then simply a matter of of selecting the object in your query