Hi All,
I'm working on a report which shows quarterly revenue growth. The report is a list. Each quarter and the respective revenue for that quarter are in rows. I have a calculation that shows the % increase or decrease in revenue over the previous quarter.
Users run the report by selecting a year (the report is not multi-select). The report output is as follows:
| Quarter | RevenueÃ, Ã, | % Increase or Decrease |
| Q1 2006 | Ã, $1,000 | |
| Q2 2006 | Ã, $10,000 | 900% |
| Q3 2006 | Ã, $100,000 | 100% |
| Q4 2006 | Ã, $1,000,000 | 50% |
Notice how Q1 2006 doesn't have a % increase or decrease number. I went ahead and made the prompt pull up ?Year? and ?Year?-1. The report runs fine, but shows all of the previous year's quarters.
I'd like to figure out how to not display last year's quarters, but still have that Q1 % increase/decrease number show up.
I tried pulling the data items up into a parent query, but Q1 2006's % increase/decrease number started calculating off of Q1 2005's number instead of the previous quarter (which would have been Q4 2005).
Any thoughts?
Hi,
Sounds like a job for conditional formatting.
Set up a boolean variable that checks for the prompted year in the data, then conditionally hide or show the list columns based on whether the variable evaluates to true or false. You can hide the columns by setting their 'Box type' property to a value of 'None'.
Hope that helps,
MF.
Giv the filter condition as % Increase or Decrease is not null.
Quote from: mrcool on 28 Mar 2007 11:03:15 PM
Giv the filter condition as % Increase or Decrease is not null.
This would work if I didn't want to display the first quarter's row at all -- but in my circumstance, I do want it to show with the value from the difference between Q1 2006 and Q4 2005. Thanks for the reply, regardless :)
Quote from: MFGF on 26 Mar 2007 04:44:06 AM
Hi,
Sounds like a job for conditional formatting.
Set up a boolean variable that checks for the prompted year in the data, then conditionally hide or show the list columns based on whether the variable evaluates to true or false. You can hide the columns by setting their 'Box type' property to a value of 'None'.
Hope that helps,
MF.
This might work for me, actually - I'll need to figure out how to get last year's values into a separate column (they are in rows, so Q1 2005 through Q4 2006 are each in their own row) and maybe try it that way. Great suggestion! I will let you know if it works for me :)
Appreciate the help!
Kat
Hi,
I know it sounds odd, but you don't need to get last year's values in a separate column. Based on the output below, the conditional formatting will hide the column values for the previous year (ie hide any rows with 2005 as the year) , but show the column values for the current year (ie show rows with 2006 as the year).
If you're struggling to set this up, drop us a line and we will point you in the right direction.
Best regards,
MF.
Wow! It works! You're right... it seemed odd :)
Thanks very much for the help in pointing me in the right direction. Much appreciated :)
You're welcome :) Glad you managed to get it working.
MF.