selecting monthly data : dat ais at date level

building semantic layer
1

hi,
the data base is having data at date level.
we need to show the monthly sales in a report. How do I achieve this.

I need to create new objects in the universe, but iam unable to get an idea on how to implement it.

The database is Oracle.

Please help .

Thanks

P.S.: Iam not sure but strangely iam unable to post reply to my earlier post,hence creating the new post. Apologies if this is violation of rules here.

BO student :india: (BOB member since 2004-10-03)

2

Hi,

If you have your facts and Dimensions correctly set, you must be having a Calendar Dimesnion table which will be having month in it for each date.
If not, you will need to modify your Calendar dimension table to include the month field. Then create an Object MONTH.
When you are creating your report, do not include the date object in your report,just include MONTH object. That will Aggregate your data at monthly Level on the report. Make sure to set your Measure property to SUM, in the measure definitions.

Other option is to create the aggregate tables/ View at the monthly level and use @ aggregateaware to access them on the need basis.

Thanks

siddhartha.dubey :india: (BOB member since 2006-09-13)

3

Or just have an object for sales date, an object for sales invoice number and a measure object to sum sales amounts.

Then in your report, drag all three objects in. This will show the sum at date/invoice level. Remove the sales invoice object and it should change to show sales at date level.

Create report variables for month(invoice date) and year(invoice date). Use them instead of the actual date, order by year and then month and you should have monthly totals.

Debbie

Debbie :uk: (BOB member since 2005-03-01)

4

Please do not cross-post. It leads to fragmented discussions and is against our forum rules. This topic is now locked. Please continue discussion here.

MichaelWelter :vatican_city: (BOB member since 2002-08-08)