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

conditional style logic

Started by CogUsrUSA22, 04 Jan 2021 11:03:35 AM

Previous topic - Next topic

CogUsrUSA22

Hello

A simple list report has running totals for Col_C based on a Column (Col_A)

Col_A  Col_B Col_C  Col_D (Running_total_of_ColC_based_On_ColA)
A1        B1      10           10
A1        B2       20          30
A2        B3       15          15
A3        B4       25           25
A3         B5      30           55
I need to hide the first row (Col_D)   And 4 th row (Col_D)  because these are not the last in the group . I tried a few things in conditional style logic expression but cannot figure out how to do it . Shown as below


Col_A  Col_B Col_C  Col_D (Running_total_of_ColC_based_On_ColA)
A1        B1      10         
A1        B2       20           30
A2        B3       15           15
A3        B4       25           
A3         B5      30           55
Any inputs are appreciated

BigChris

You might be able to do something using running-count(ColB for ColA)...you might need to play with it to get the right format etc. but that might give you a starter at least...

CogUsrUSA22

#2
Thank you Chris.
I tried using running counts (by using the logic that if col d /col c =  1 and running count col c for column a =1 ) then show else hide  the col d (as conditional style expression ) but it just doesn't take it (errors out), i tried using count as well but it doesn't like it . Please note that the running totals are working fine in the column the current challenge is to just hide all the running totals except the last occurrence (unless the Col A appears only once in that case we do need to show up the totals (running total) which are both same )


BigChris

Have you included the calculation in your output? You can hide it with a render variable, or set the box type to none, but from memory it needs to be on the page for you to use it in the conditional formatting.

CogUsrUSA22

The running total calculation is for col D but the same column values needs to be hidden depending on if Col A is repeating itself (if yes then show the last running total else hide ) - So to handle that I need some kind of value that counts the number of occurrences of col A (like a running count value) and neither the conditional style or Render variable allows such functions in expression , so most likely I am failing to come up with a logic that is acceptable in expression for render variable

BigChris

I'm a bit swamped today so I can't do any testing for you, but can you create a calculation in your query for something like
case running-count([Col B] for [Col A])
  when 1 then 'No'
  else 'Yes'
end

If that works it should be simple enough to build a conditional format using that field.

MFGF

Quote from: CogUsrUSA22 on 05 Jan 2021 08:33:04 AM
The running total calculation is for col D but the same column values needs to be hidden depending on if Col A is repeating itself (if yes then show the last running total else hide ) - So to handle that I need some kind of value that counts the number of occurrences of col A (like a running count value) and neither the conditional style or Render variable allows such functions in expression , so most likely I am failing to come up with a logic that is acceptable in expression for render variable

Hi,

I just mocked this up locally and it works for me:
1. Add a new calculation called Row_Count with the expression running-count ([Col_B] for [Col_A])
2. Add a second calculation called ToBeHidden with the expression if ([Row_Count] < maximum([Row_Count] for [Col_A])) then ('Y') else ('N')
3. Select the Col_D list column and in Properties add a new Conditional Style. Choose the Advanced Conditional Style option, and add a new condition - [ToBeHidden] = 'Y'. For the style for this condition, go to the Advanced tab and set the Visible option to No.

Cheers!

MF.
Meep!

BigChris


CogUsrUSA22

Thank you

So I tried MF's logic but at 2 (as below)
"2. Add a second calculation called ToBeHidden with the expression if ([Row_Count] < maximum([Row_Count] for [Col_A])) then ('Y') else ('N')"

I  got all flags as 'Y' , so I created another calculation just to see what value  maximum([Row_Count] for [Col_A])) is giving me and I found that it giving me 3 in first occurrence and then 6 in second occurrence . I tested all conditions , records that are coming once , records that are coming twice  , thrice etc - They all seem to have random (or following some logic ) maximum counts .
When you tested did the maximum work the way it should ?


MFGF

Quote from: CogUsrUSA22 on 06 Jan 2021 10:12:10 AM
Thank you

So I tried MF's logic but at 2 (as below)
"2. Add a second calculation called ToBeHidden with the expression if ([Row_Count] < maximum([Row_Count] for [Col_A])) then ('Y') else ('N')"

I  got all flags as 'Y' , so I created another calculation just to see what value  maximum([Row_Count] for [Col_A])) is giving me and I found that it giving me 3 in first occurrence and then 6 in second occurrence . I tested all conditions , records that are coming once , records that are coming twice  , thrice etc - They all seem to have random (or following some logic ) maximum counts .
When you tested did the maximum work the way it should ?

Hi,

Is this a list report? What do you see if you include the first calculation in the list? Does the running count work as you expect?
If this works, what do you then see if you add the second calculation to the list? Do you get the Y and N values displayed as expected?
Assuming both of these work as expected when displaying them in the list, try removing them from the list again and adding them in to the Properties property of the list.

Cheers!

MF.
Meep!

CogUsrUSA22

Yes , this is a list report 
When i include first calculation in the report - The Row_Counts work exactly as they should .
Then I added the "To be Hidden" and it gave me an CRX-API-0005 error . That is when i added this field to properties as you mentioned .
Then I ran and saw all values Y (where some values were expected to be N ) - That is when i checked what is being calculated for maximum and I most of the maximum's did not make any sense  (for example  - in my example row 1 and row 2 gave me row counts of 1 and 2 respectively (which is correct ) but maximums of 3 and 6 (when they both should be 2 and 2 ) .

The conditional style logic was exactly working the way it should because it was based on flags 'Y' or 'N'.

MFGF

Quote from: CogUsrUSA22 on 06 Jan 2021 11:27:27 AM
Yes , this is a list report 
When i include first calculation in the report - The Row_Counts work exactly as they should .
Then I added the "To be Hidden" and it gave me an CRX-API-0005 error . That is when i added this field to properties as you mentioned .
Then I ran and saw all values Y (where some values were expected to be N ) - That is when i checked what is being calculated for maximum and I most of the maximum's did not make any sense  (for example  - in my example row 1 and row 2 gave me row counts of 1 and 2 respectively (which is correct ) but maximums of 3 and 6 (when they both should be 2 and 2 ) .

The conditional style logic was exactly working the way it should because it was based on flags 'Y' or 'N'.

Hi,

So you added the "To Be Hidden" item to your list as a list column and at that point you got a CRX-API-0005 error? That doesn't sound right. Do you have old conditional expressions still hanging around that are causing issues? Try either removing the conditional expressions already in place or creating a new list report to test this. Also check the aggregation type of your Row_count item in properties - what is it set to?

This is what my mock-up looks like and what you should expect to see:



Cheers!

MF.
Meep!

CogUsrUSA22

Thanks a lot  .
The results you show is what is the desired output . I will try creating a fresh list report .

CogUsrUSA22

There was something wrong with my original list , I recreated the list and followed your logic . It works . Thank you all .