Calculating Prior Quarter & Variance in Active Reports

[ab.kash]

Hello Everyone,

I need your help asap.

I am building a financial report in Active Report. What I am looking to achieve is comparing two quarters (Current year quarter and Prior year quarter or the option to select any two quarter) and the calculate the variance. It also then goes on to include budget and so on but I will leave that for now.

The method I have used:

1.Use Data Check box group for Year.
2.Use two separate Data Check Box Group for Current Quarter and Prior Quarter (each filtered by Year above, so both give the option to select any quarter for the selected years)
3. In my Crosstab I have two columns for Current Qtr and Prior Qtr and the Crosstab is getting filtered by both the Data Check box above.

In theory it should work but this is not working. I think what is happening is one of the Data Check box filter for Quarter is getting active and preventing the other one to display. Attached is a snapshot of what I have created.

Is there a way to get around this. I need your help asap. Its quite urgent!

Thanks

[gpollock]

Hi Ab.kash, welcome to Cognoise.

It sounds like you're trying to compare quarter X current year vs quarter X prior year.  I don't have all the details of your report source, and how you want the report output to look, so I can't give you specific advice.  What I can do is give what I found while working on this myself.  Hopefully you can even find a better solution!

In our cube, we have two time dimensions; one proper calendar dimension with all the relative time measures, and a second calendar dimension that is Quarter->Month->Week->Day #.  In this second dimension, the level for quarter X contains quarter X from every year.  Then you use a tuple/crosstab node for current year and prior year for your comparison.  You can then filter/select to determine which quarter is chosen.

Another option you can do is stick with the basic calendar dimension, but I found the hard way that relative time members aren't considered part of the normal hierarchy, so you have limitations on them.  I had to do some tricks filtering the hierarchy level to grab weeks/quarters etc, but then I could use parallelPeriods to get comparisons for the prior year.

Sorry I don't know a simpler way, but if you can expand on what you're asking, one of us can help more.