Traditional web-based reporting with SAS BI tools

Aggregate Functions in Information Map

Reply
Contributor
Posts: 22

Aggregate Functions in Information Map

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
SAS Employee
Posts: 238

Re: Aggregate Functions in Information Map

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
Contributor
Posts: 22

Re: Aggregate Functions in Information Map

Thanks Angela.

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

Regards,
Bob.
SAS Employee
Posts: 238

Re: Aggregate Functions in Information Map

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
Contributor
Posts: 22

Re: Aggregate Functions in Information Map

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.
SAS Employee
Posts: 238

Re: Aggregate Functions in Information Map

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
Ask a Question
Discussion stats
  • 5 replies
  • 389 views
  • 0 likes
  • 2 in conversation