where statement in Impromptu

[srf]

We are extracting sales information for a number of periods and wish to create a columnar report for the sales codes WHERE the periods = specific values.

We are using a grouped list format for this report as a cross-tab does not suit our needs.

Does a WHERE statement exist in Cognos or can we use another function do do this ?????

An IF then Else statement does not work for what we are trying to do ?

 

[hindsjl]

Go to Report, Query, Filter.

The expressions you put in the Filter window are essentially the Where clause, minus the word "Where".

E.g.,

Month = 8 and Region = 'NE'

"If then else" constructs can be used in calculating the values of data items, but they don't work in the main Filter. You just work with And, Or, and parentheses.

John

 

[srf]

John

We have already filtered the data for the periods required using the IN command in the Filter for Periods 31,32,33 and 34.

We now want to create columns for each Period without having to create a cross-tab report.

When Period = 31 the Sum all Sales data for Account Code in Period 31 and shown in Column 1.

I hope this helps with the explanation

 

[CognosProfessional]

When in your impromptu report, click insert calculation and use an expression in each to Sum all Sales data for specific Account Code for each Period using a new calculation for each to separate columns.

CP

 

[hindsjl]

OK, so you do want the If Then Else.

Create a calculated column named Period_31, defined as
total ( if (Period = 31) then (Amount) else (0) )

Then create similar calculated columns for the other periods. Sorry I misinterpreted the question.

John