How can i add filter to only 1 column on a report?

[mlov83]

Hello everyone I apologize if this sounds like a stupid question however I'm a noob to cognos. but what im trying to accomplish with cognos would seem to me very doable. I Have a report that runs base on a calculation filter using the datediff function. It basically calculates the Difference of two dates and it the date diff is above 30 days it shows the result otherwise it does not.
My Problem is that i dont want to add another list i would just like to add a column to my report that say 31-50 days and show the amounts on that column. The problem that im having is that when i put a filter of 30 days and on the same list a filter of 50 days i get nothing. I assume it problablys has to do with the fact that you can only have 1 filter to a list? and maybe it cannot meet both datediff categorys?... Im totally confused on this one. I have been able to do it but have had to add another list to my report and it just looks sloppy in my opinion.
Is there anyway not to show the report headers when it runs?
Thank you eveyrone for the help in advance.

 

[toddl]

Write a case statement instead of using a filter. That way the column will just be blank when it's not in that range.

Todd

 

[mlov83]

Yeah but how can I put a case statement based on just the column? I thought they worked like filters.

 

[blom0344]

A filter will end up in the where clause of the SQL generated and effect ALL rowss.
With a case statement you can change the value of a data-item like:

CASE
WHEN
[SOME_DATAITEM] 'matches' <some_criteria>
THEN [someother_dataitem]
ELSE null
END

In your case [SOME_DATAITEM] is the DATEDIFF expression
and <some_criteria> is 30,
[someother_dataitem] is amount

Ties Blom

 

[mlov83]

thanks for the response i guess my question is should i be puttiing this case statement on the expression of the column? Thats where im having a hard time understanding. Where do i put case statements in cognos besides filters?... Sorry if i'm having a hell of a time understanding here. and thank you in advance.

 

[mlov83]

I dont think i have been very clear here is the datediff function I use to calculate the difference between the invoice date and the current date.
I have a column called approved amount. What i would like to do is just show the amount when the column meets the criteria below.
(_days_between(current_date,[Invoice Payable].[Invoice].[INVOICE_DATE])) between 0 and 30

Then im going to add another approve column and put the criteria below but i should only show me the amounts when this criteria is met.
(_days_between(current_date,[Invoice Payable].[Invoice].[INVOICE_DATE])) between 31 and 50


I try using the case statement but i cant figuer out how to use it because it would look something like this...


CASE
When[(_days_between(current_date,[Invoice Payable].[Invoice].[INVOICE_DATE]))]= '<30'
THEN <what?>
ELSE [null ]
END

 

[mlov83]

I finally Figuered this one out!!! BOY im i excited ;D here is what i had to do.
I created a union then i added some data items in addition to a little case statement

CASE
WHEN (_days_between({sysdate},[INVOICE_DATE])) between 0 and 30
Then ([Union2].[APPROVED_AMOUNT])
ELSE (NULL)
END

Hope this helps someone else down the line

 

[blom0344]

mlov,

The union comes out of nowhere, but perhaps your situation is a little bit more complex than you described.
Using calculated dataitems is pretty much standard fare , you will use this time and again

Ties Blom