cancel
Showing results for 
Search instead for 
Did you mean: 

Preventing duplicate results when returning Maximum

Former Member
0 Kudos

Hi,

I am building a report in CR XI.  Initially the report was built as follows:

Action ID | Meeting Date | Due Date | Completed Date

___________

(SUBREPORT)

Action Date

Action Note

This report would provide details on action items that came out of committee meetings.  There were often multiple notes for each Action ID.

The report was running fine as it was.  If I ran it for January, it would return 10 results.

I was asked to add a heading between Due Date and Completed Date to display the last Action Date.  I have created a Group based on Action ID and then created a formula to find the following:

maximum({tblActionSheetNote.actionDate},{@ActionID})

I then placed the formula field into the report between Due Date and Completed Date.  The formula returns the last date for each Action ID, but now when I run the report for January it returns 18 results.  When I look at the data in the report, it is displaying multiple results where there are multiple notes for an action (ID 123 has 3 notes so appears 3 times in the report).

Has anyone got any suggestion about how I can provide the action date without ending up with multiple Action ID results?

Thanks

Simon

Accepted Solutions (1)

Accepted Solutions (1)

ido_millet
Active Contributor
0 Kudos

Using {tblActionSheetNote.actionDate} in the report forces a join to that table, which causes record inflation.
Some of the options to solve this are:
a) use a linked subreport

b) use a correlated SQL expression
c) use a View, SP, or Command as the data source for the report

Former Member
0 Kudos

Thanks Ido,

In the end I went with a linked sub-report.  Apart from the issue mentioned above, the user wanted to have the option of sorting on different fields that were in the group so I had to get rid of the group to allow that to be possible.

Thanks

Simon

Answers (0)