COGNOS Query and Oracle MAX() function

[sstengler]

I'm needing to duplicate the use of the Oracle MAX() function in my Cognos Query data sets.

We have two tables, EMP and ELECTIONS, where EMP contains the demographic data for a person, and ELECTIONS contains thier benefit elections over time (so, there could be several rows where the person elected MEDICAL benefits, but one might be "employee-only", the next "employee plus spouse", etc.. Each row would have a date-stamp of when the election was made).

There is always a need to join the EMP and ELECTIONS tables to pull the employees and thier latest set of benefit elections. In PL-SQL, it's easy, gouping on the user id and the benefit, and selecting the MAX(elec_eff_date).

But, how do I duplicate this in Query?

 

[DoubleD]

Define your grouping levels, then enter the following in the advanced filter:

"elec_eff_date" = Max("elec_eff_date")

Pain is stress leaving the body

DoubleD

 

[sstengler]

Ok, tried that, but it kept giving me a syntax error near the MAX function....this was in the Filter expression builder. I don't see anything in Query that indicates "Advanced filter".

 

[DoubleD]

1. Group on elec_eff_date
2. Create a calculated column called Max Date.
Maximum("elec_eff_date")
3. In your Summary Filter, put the statement:
"elec_eff_date" = "Max Date"

* You are only able to create a summary filter if you are grouping and you have a calculated summary column.

Pain is stress leaving the body

DoubleD

 

[sstengler]

Appreciate the feedback, DoubleD, unfortunately, the summary filter as given has no effect on the query. With the filter applied or not, it still displays all of the elec_eff_date's, instead of just the latest (maximum) one. It looks like the filter is not selecting the maximun date for the group, only the one for each row, which is useless in this case.

In SQL, this would be handled by a sub-query in the where clause to pull the max elec_eff_date for each SSN and BENEFIT group, and then have the main query pull the row where elec_eff_date is equal to that sub-select's result.

 

[griffindm]

sstengler,

I would create an aggregation view of the election data and use that in the catalog. Depending on your database platform, the usual SQL syntax is:

CREATE VIEW CURR_ELECTION AS SELECT EMPL_ID,MAX(EFFECT_DT) GROUP BY EMPL_ID

Then join this to the election table on empl_id and effect_dt to filter down to the currently effective row for each employee.

The major advantage to doing it this way is you do not have to rearrange your groupings in the report just to get the correct effective date for each employee.

Regards,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[DoubleD]

griffindm,
Good idea, but Cognos Query doesn't use catalogs. This idea will apply if you're using Cognos Architect to generate your Cognos Query package. Create a view in your database, or write a SQL view in your Architect Model, then include that in your Query Package.

Pain is stress leaving the body.

DoubleD