Cognos Analytics

Have a report where a filter is set for a Maximum date return. 

Unfortunately, the field the filter is referencing, has multiple entries for the same date.

I.e., in the program, we are pulling the data from there are two entries with the same date.

• 06.01.21- blah blah blah
• 06.01.21- naa naa naa

Because of the Maximum date filter, the report populates both different types of data in the report. 

Unfortunately, I only need one of the data types in the report. It doesn't matter which since they were made in the same day, but I only need one to return back to the report. 

I've already tried setting the Maximum Rows Retrieved in the Query Hints to 1 but it still populates both. This is a convoluted report with multiple different queries that are then joined to obtain a final report.

Thinking that a Count SQL would work but need assistance with the SQL to make sure it works properly.

Any help would be appreciated.

------------------------------
wesley Fornero
------------------------------

#CognosAnalyticswithWatson

Hi Wesley,

I'm asking myself: why do you have the second column in your query, when you just need the max(date)? Cognos will always populate used columns in the query or - if used for joins - into the sql inside of the with-clause.

Would it suit your needs if you ust have the date column in the query to get the max value? Otherwise I would suggest to apply a "row_number()=1 filter in addition."

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen
------------------------------

Original Message:
Sent: Wed July 28, 2021 11:15 AM
From: wesley Fornero
Subject: How to have a Maximum date filter Return one item of data

Have a report where a filter is set for a Maximum date return. 

Unfortunately, the field the filter is referencing, has multiple entries for the same date.

I.e., in the program, we are pulling the data from there are two entries with the same date.

• 06.01.21- blah blah blah
• 06.01.21- naa naa naa

Because of the Maximum date filter, the report populates both different types of data in the report. 

Unfortunately, I only need one of the data types in the report. It doesn't matter which since they were made in the same day, but I only need one to return back to the report. 

I've already tried setting the Maximum Rows Retrieved in the Query Hints to 1 but it still populates both. This is a convoluted report with multiple different queries that are then joined to obtain a final report.

Thinking that a Count SQL would work but need assistance with the SQL to make sure it works properly.

Any help would be appreciated.

------------------------------
wesley Fornero
------------------------------

#CognosAnalyticswithWatson

Hi,

Change the Detail Aggregation for string field to Maximum too.

------------------------------
JEAM COELHO
Cognos Solution Architect

LinkedIn: https://www.linkedin.com/in/jeamcoelho/
------------------------------

Original Message:
Sent: Wed July 28, 2021 11:15 AM
From: wesley Fornero
Subject: How to have a Maximum date filter Return one item of data

Have a report where a filter is set for a Maximum date return. 

Unfortunately, the field the filter is referencing, has multiple entries for the same date.

I.e., in the program, we are pulling the data from there are two entries with the same date.

• 06.01.21- blah blah blah
• 06.01.21- naa naa naa

Because of the Maximum date filter, the report populates both different types of data in the report. 

Unfortunately, I only need one of the data types in the report. It doesn't matter which since they were made in the same day, but I only need one to return back to the report. 

I've already tried setting the Maximum Rows Retrieved in the Query Hints to 1 but it still populates both. This is a convoluted report with multiple different queries that are then joined to obtain a final report.

Thinking that a Count SQL would work but need assistance with the SQL to make sure it works properly.

Any help would be appreciated.

------------------------------
wesley Fornero
------------------------------

#CognosAnalyticswithWatson

Hello Wesley,

I had a similar issue. 

Try creating a calculated field and code something like this: rank([Date] for [Field Name])

then Filter something like: Calculated Rank Field = 1

It worked for me

------------------------------
Stephune Waites
------------------------------

Original Message:
Sent: Wed July 28, 2021 11:15 AM
From: wesley Fornero
Subject: How to have a Maximum date filter Return one item of data

Have a report where a filter is set for a Maximum date return. 

Unfortunately, the field the filter is referencing, has multiple entries for the same date.

I.e., in the program, we are pulling the data from there are two entries with the same date.

• 06.01.21- blah blah blah
• 06.01.21- naa naa naa

Because of the Maximum date filter, the report populates both different types of data in the report. 

Unfortunately, I only need one of the data types in the report. It doesn't matter which since they were made in the same day, but I only need one to return back to the report. 

I've already tried setting the Maximum Rows Retrieved in the Query Hints to 1 but it still populates both. This is a convoluted report with multiple different queries that are then joined to obtain a final report.

Thinking that a Count SQL would work but need assistance with the SQL to make sure it works properly.

Any help would be appreciated.

------------------------------
wesley Fornero
------------------------------

#CognosAnalyticswithWatson