I retrieve data for a 2 year period but a Crostab groups Measure 3 & 4 by year. Am I able to show the Measure 3 & 4 as above?
If I use two data providers, one for each year and then try to link the providers in a single table, I get duplicate rows and some of the values from the 2nd provider are not shown. I seem unable to include the Measure Objects in the link between providers. The data in Measures 1 & 2 are static values related to Dimension 1 so they are the same from both data providers.
I retrieve data for a 2 year period but a Crostab groups Measure 3 & 4 by year. Am I able to show the Measure 3 & 4 as above?
Hi, Garth…
I did a presentation at a BusinessObjects conference several years ago that covered this exact problem. It’s called “Variables and More Variables!”, from 1997. You can download the file from:
If you don’t want to download the entire problem, here’s the solution in a nutshell:
Create a new variable called Sales This Year 2. Formula = Where ( = “2000”) 3. Create a new variable called Sales Last Year 4. Formula = Where ( = “1999”) 5. Create a new variable called Sales Variance 6. Formula = -
Incidentally, you can only use “=” in the Where… clause. You must have either a constant or another variable, you cannot use a calculation.
BTW, you can solve this problem with a few mouse clicks in v5; there is a new feature called Variance that is designed for just this issue. Interestingly enough, this feature simply uses the same trick I presented in my paper! They have just made it available as a toolbar button.