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

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Counting only visible data...

Started by Chris_, 24 Aug 2010 07:56:47 AM

Previous topic - Next topic

Chris_

Hi,

I currently have a crosstab that has a specific row filtered out using conditional formatting.  I'd like to total up the rows on the crosstab but when I'm doing this it's including the sata in the row filtered out.

Is it possible to create a total/calculation based on only the visible data?

Many thanks, Chris.

tupac_rd

I don't know the backaground here, but can you also create a calculation which has similar logic as you used for conditionally displaying the rows, and use that for the total....

Chris_

What I'm doing is creating a Retirement Profile.  Using a crosstab I'm looking to display how many employees are within a certain age range (Over 58) and then give a total for that and then create a calculation to display how much of a % that number is of the overall business headcount.



What I've managed to achive so far, is to create an age calculation that calculates each employees age any out with the range (Over 58) are classed as Under 58.  I've then used conditional formatting to hide the "Under 58" column from the crosstab.

The problem I have now, is that if I add a total to the crosstab it includes the Under 58 figures.  I'm looking to create a calculation that will only create a sum of the data visible on the report?

Hopefully this makes sense, I'm a novice user and learning more as the days go on.

Thanks

Lynn

Hi Chris,
The expression for your total column is probably something like:
total(currentMeasure within detail [AgeLabel])

and the currentMeasure is probably something like:
[AgeCount]

You can try dragging a new data item (call it [Over58Count]) into your query that contains only the people over 58:
if ([AgeLabel] <> 'Under 58')
then ([AgeCount])
else (0)

Then edit the total expression to replace "currentMeasure" with your new item "Over58Count".

Hope this helps!
Lynn