Aggregate Functions in Information Map

[databobjr]

Hi

I need generate an operational report which will look like the following:

date ; Sales Person ; Today's Revenue ; sum of Last 7 Days Revenue.

The source data table will provide data for date, sales person and today's revenue. Sum of last 7 days revenue need to be calculated for each sales person each day.

Since this is a very simple report and I was trying not to use an OLAP cube. I wanted to use Information Map and create a data item to calculate the last 7 days' revenue. Since I am not using a cube, I can not use MDX function such as LastPreriods() (am I right on this?). Is there a function or work around to create a data item for this kind of calculation?

Thanks,
Bob.

Message was edited by: databobjr Message was edited by: databobjr

[AngelaHall]

You would use the MDX lag function for this.

Something like:

Sum (
([Date].CurrentMember.Lag(11):[Date].CurrentMember),
[Measures].[Revenue]
)


~ Angela
http://sas-bi.blogspot.com

[databobjr]

Thanks Angela.

But I am not using a cube, therefore, there is no time dimension (date). right?

Regards,
Bob.

[AngelaHall]

Sorry your first post was confusing.
There is a lag function in SAS as well but it is only available in data step. Information Maps use SQL and when attempting a SQL lag function, I get a message stating that its only available in the data step.

So your other options:
1. create the running total using data step lag function during data building time
2. utilize a stored process with your information map that computes the running total (using the data step lag function)
3. moving to an olap cube

~ Angela
http://sas-bi.blogspot.com

Reference
SAS Documentation on LAG Function:
http://support.sas.com/documentation/cdl/en/lrdict/63026/HTML/default/viewer.htm#a000212547.htm

[databobjr]

Thanks Angela.

I created a cube and it worked with the mdx function. However, I need to produce a summary reports which the user can select a date from the filter, and the report shows something like:

date select 12/20/2010

sale person, last 7 day Revenue, last 15 days revenue, last 30 days revenue

If I did not include the time dimension on the reports, the MDX did not work. I used web report studio.

Thanks,
Bob.

[AngelaHall]

The Time Dimension needs to be included in the MDX query for the calculated measure to be generated. In Web Report Studio, the query is generated automatically by what is used/included in the report. Therefore what you are seeing is expected.

If you would prefer to not include the dimension in the result, an alternative is to utilize a SAS Stored Process within your information map that queries the OLAP cube and generates the data result for Web Report Studio to then display.
Bypassing the Information Map query is discussed in a webinar I presented last month.

http://support.sas.com/documentation/onlinedoc/guide/webreportstudioperformancewebinar.htm

~ Angela
http://sas-bi.blogspot.com