Building variance in Active Reports

[nikfalla]

Hello,

I have build an active report against a dimensional package. In my report, I have a data discrete slider that allows user to select a range of months. Since this is a range, I can use the min and max month to extract the value of a measure for the first and last selected month and display it accordingly. I am however running into a problem displaying the difference in the value of that measure for the first and last selected month.

Let us say the slider shows all the months of a year, say 2012 and my list has Retailers, Regions, Revenue and Month. The user selects months of Feb and July. I can show the revenue for retailer/region for Feb and July, but since they are in two different queries, I cannot subtract the values from each other and show the variance. I cannot join the two queries together, as they are dimensional and I get an error.

We are on 10.2.1 fix pack 3.

Please have a look at the sample code build against Go Sales (Analysis) and see if you can shed any light into this.

I appreciate your help!

[nikfalla]

So, I tried a couple more things and ended up solving my own problem, but I thought I will be a good citizen and share the workaround with the community.
I added one list as a column to the other list, created master-detail relationship between the two lists, and then I was able to calculate the variance.
The Solution is in the attached report.

Please note that for my requirement, the client is only interested in the variance, so I have some of the columns hidden and also the column headers in the list within the list are hidden.

I hope this helps someone...

[MFGF]

So, I tried a couple more things and ended up solving my own problem, but I thought I will be a good citizen and share the workaround with the community.
I added one list as a column to the other list, created master-detail relationship between the two lists, and then I was able to calculate the variance.
The Solution is in the attached report.

Please note that for my requirement, the client is only interested in the variance, so I have some of the columns hidden and also the column headers in the list within the list are hidden.

I hope this helps someone...

Excellent! Glad you found a solution, and thanks for sharing it! :)

MF!