Displaying Rows under conditions

[dylsing]

Hi,

is it possible for me to write an expression for a query calculation such that this query will only returnÃ,  column A values only if column B = 0, otherwise the whole row will not be displayed.

In this row, there could be other columns containing the description of column A.

It will be great to have an example, as when I try writing it with IF ELSE statements, there are errors but I can't figure out how to resolve it using the debug help.

Thank you.

[MFGF]

Is there a reason you are trying to do this with a query calculation rather than a filter - if you want the whole row to be hidden if B <> 0, why not just add a filter to do it?

Regards,

MF.

[CArnold40]

I'm with dylsing on this one. I am not sure how you would use a filter to display a column under specific conditions.

I know that I can use a filter to either display a value, or a dfault value -- such as:

If A =! B Then [Column.field] = NULL 

or I can even do:

If A =! B The [Column.field] = 'N/A'

But it is still going to DISPLAY the column on the report. But if I want to display (or NOT display) the column based on the value of A, I wouldn't know how to use a filter as such.

CArnold40

[dylsing]

Yup, CArnold40 said roughly what I had in mind though I have no idea how to do it. I did realise what I mentioned sounded as if I should use a filter.

I am doing some stuff that accesses Chart of Accounts. I have two columns, one that contains Revenue and the other Profit.
Both figures have different codes to identify them but the actual figures come from the same column, eg 1111 for Revenue and 2222 for Profit.
- So in one column, I only want to extract anything that has code 1111 and the other column anything to do with 2222.
- If I use a filter and maybe select a condition that selects both 1111 and 2222, I will have figures coming from the same column lumping both Revenue and Profit into one which is not what I want.

I am sure this is a common problem, can you shed any light on this? Thanks

[CArnold40]

I think I can help with this:

1) Drag the desired column (I'll call the column 'overall_income') into your query on the page.
2) Drag the column into the same query again, right next to the previous one; COGNOS will, by default, name it 'overall_income1'.
3) This should give you overall_income & overall_income1 right next to each other. Maybe rename the List Column Titles to : '
Ã,  Ã, overall_income = 'Revenue'Ã,  andÃ,  overall_income1 = 'Profit'.
4) Click on overall_income on your page; In the Properties section for overall_income, look for Expression (it will be under the Data Item subsection of the properties).
5) Modify the expression to be something like:

Ã,  IF ( overall_income = '1111') THEN
Ã,  Ã,  Ã,  Ã, ( overall_income)
Ã,  ELSE
Ã,  Ã,  ( NULL )

6) Click on overall_income1 on your page; In the Properties section for overall_income1, look for Expression (it will be under the Data Item subsection of the properties).
7) Modify the expression to be something like:

Ã,  IF ( overall_income = '2222') THEN
Ã,  Ã,  Ã,  Ã, ( overall_income)
Ã,  ELSE
Ã,  Ã,  ( NULL )

I THINK that should do what you are describing in your previous post. Give it a try and let me know if it works out for you.

CArnold40

[dylsing]

Hey CArnold,

I actually tried exactly what you said last Friday and it worked really well.

Thank you for your detailed suggestion though, it just helped me confirm that I was doing it the right way. Else I would be guessing if it is the best method. Thanks again.