Setting up a boolean variable

[ARustad1645]

I've only been using Cognos for a few months and we're still in the setup process, but I have a follow up question to a response I received a few days ago. I'd asked how to hide lines containing all zeros on a financial report.

In the response I received (pasted below) I was told to "set up a boolean variable that checks if all the fields are zero." Can someone explain to me how I do that?

Thanks!



jem122974 said:
"By using conditional formatting and the box type property for the list body.

First setup a boolean variable that checks if all the fiels are zero.

Then select the lists column body style using the ancestor button. There assign the style variable to the variable you created.

Finally in the condition explorer select the yes value (all fields are zero) of the variable and set the box type property to none.

That will hide rows where everything is zero in that row."

 

[ARustad1645]

Maybe I should add that I'm trying to evaluate:

If [Actual] = 0 and [budget] = 0 then I want it to hide the line. I understand how to set up the rest of the instructions I received, other than the boolean variable.

 

[blom0344]

You need to open the variable GUI and drag a new boolean variable into the panel. Then define the expression as:

[Actual] = 0 and [budget] = 0

You'll notice that Cognos adds only two fixed values to this statement Yes/No

The GUI for variables is a bit of a hassle, you need to navigate properly as it 'pops up' on the screen.

Ties Blom

 

[ARustad1645]

It looks like I was trying to make the syntax too complicated before. Now my report runs without error messages (I was getting errors before) but it doesn't seem to work. I'm still getting lines that have all zeros. Does it matter if I have the fact cells formatted to show a '-' rather than a 0, when the value is 0? I wouldn't think it would. Any other suggestions?

 

[blom0344]

Did you look at the raw data (tabular data) and check whether the zero's are really zero? (like 0.0000000000)

I also suspect that you may have null values (instead of 0)
which in that case the evaluation of [Actual] = 0 and [budget] = 0 would evaluate to false.

Suggestion (no idea if it does what I expect):

coalesce([Actual],0) = 0 and coalesce([budget],0) = 0

Ties Blom

 

[ARustad1645]

I have not looked at the raw data, but based on the information in our ERP system I believe it is in dollar format (0.00).

When I attempt to validate the expression I get the following error message:

RSV-VAL-002 Invalid expression coalesce([Actual],0) = 0 and coalesce([Budget],0) = 0. CRX-API-0011 Function name was not found on or around the position '8' in expression: "coalesce([Actual],0) = 0 and coalesce([Budget],0) = 0".

Amanda

 

[blom0344]

Coalesce is SQL related, so will no parse when using a cube as datasource. So ,I suspect you use a cube..

Ties Blom

 

[blom0344]

The following article on KB has your solution:

1038905.1

Current functionality of Report Studio does not have an option to suppress zeros like Analysis Studio, and the use of expression
filter(descendants([Parent], 1), [Measure1 > 0)
only works for one measure item.
Although specifying the following expression will pass without errors your report will still suppress the first zero item and ignore the rest
filter(descendants([Parent], 1), [Measure1] > 0 and )[Measure2] > 0 and [Measure3] > 0 )

The following technique will allow users to author reports where zero suppression can be applied on multiple measures.

Solution:
Create a calculation that checks if all items are = 0 and then it will set itself to 0 otherwise it will set itself to 1.
Example
[Calc]
if ([Measure1]= 0 and [Measure2]= 0 and [Measure3]= 0) then (0) else (1)

Then use the filter(descendants...) expression on this calculation in the following manner
Example
filter(descendants([Parent], 1), [Calc]> 0)

Ties Blom

 

[ARustad1645]

Yes, you are correct, I am using a cube.

I'll give that a try, thanks! I just received a log in for KB, I didn't even think to look over there.

Thanks!