If you are unable to create a new account, please email support@bspsoftware.com

 

[SOLVED] Trying to not display rows based on a criteria - tough one

Started by cognoskat, 22 Mar 2007 03:17:36 PM

Previous topic - Next topic

cognoskat

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?

MFGF

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.
Meep!

mrcool

Giv the filter condition as % Increase or Decrease is not null.

cognoskat

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 :)

cognoskat

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

MFGF

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.
Meep!

cognoskat

Wow! It works! You're right... it seemed odd :)

Thanks very much for the help in pointing me in the right direction. Much appreciated :)

MFGF

You're welcome  :)  Glad you managed to get it working.

MF.
Meep!