Cognos Programming Logic Question

[Sheldon]

To simplify what I'm trying to do - here's my

input layout
| Student Id | Sport |

2 input records
123 | Baseball |
123 | Basketball |

Output record layout is:

| Student Id | Baseball Y/N | Football Y/N |

What I'd like to see:
123 | Y | N |

Question is how to "remember" that student 123 played baseball and not basketball while reading the two input records and retain that information so the (one) output record can be written correctly.

Thanks, Sheldon

[CognosAdmn]

Hi Sheldon,

Do you have any other fields in the table that uniquely identify each row?

Also, based on your sample data, a student may be in multiple sports. But you only need all Students who/ or are not in Baseball and Football? So we could drop Basketball?

If I understood you correctly and if what I think is what you are looking for, then couldnt you filter all student ids in Baseball and set "Yes" for them and filter in another query for all students who are not in baseball and put a "No"? And the same for football.

Please let me know if I misunderstood your question.

Shaam

[Sheldon]

Thanks Shaam, I appreciate your reply/suggestions.  My real world example is more complicated but I'll look to see if I can incorporate some filters. 

Sheldon

[blom0344]

Unless you incorporate a database procedure, you have to deal with set-based logic to write reports with Cognos (and as such with any other BI tool) This means that the 'remembering' you mention is just not available. There is no programming involved where you can store intermediate values in a cursor. (or loop through data) You can sort,rank, filter , use set operators like union & except etc to get proper results.

A typical example would be fetching the latest purchase date for every customer. Simply going for the latest date is not good enough. A customer may have zero , 1 , or n purchases with respective dates. You need to combine ranking with filtering to get the desired set, possibly even using a union to fetch additional customers without orders..

[Sheldon]

Thank you for that information.  It will save me a lot of time of looking for what does not exist and give me other avenues to pursue.

Sheldon

[MFGF]

Hi,

Off the top of my head, you could try the following approach:

1. Define calculations for each sport - eg a calculation called Baseball with the expression if ([Sport] = 'Baseball') then ('Y') else ('N')
2. Group on Student ID
3. In the group footer for each Student ID, add a calculation that aggregates each sport calculation above using the maximum() function.

I don't have time to try this currently, but remember using this type of technique years ago in Quiz...

MF.

[naveen10046]

Remove the dataitem 'Sport' from your report.
go to the query page add the same dataitem here.
go back to your report page.
Now create a query calculation name it Baseball Y/N ,write expression
If([Query1].[Sport]='baseball')Then('Y')Else('N').
Create another query calculation name it football Y/N ,write expression
If([Query1].[Sport]='football')Then('Y')Else('N').

With Regards
Naveen ;D