How to do this Impromptu query?

[dideb]

Hi all,
i have a table for the sessions of the law cases which are stored in another table (every case has several sessions).
In the report i need to get info about a prompted case and also info about the last session made for this case.
i used max() to get the last session date (one of the P.K. of the session table) but when i get other session data like the comments, it gets comments of another session (probably the of the first record)

so where is the problem
thanks in advance

 

[griffindm]

Diaa,

I would probably tackles this in Impromtpu as follows:

Create a report on that returns just the case number and a concatenation of the case number and the session date for the last session for each case. This would contain just the two data elements, and would be grouped on case number and have a summary filter of session date = Max(session date). Save this report. It will not be a direct data source for your final report, but will be used as part of that report's filter.

Now, create a list frame report where the main query contains the information on the last case number. Filter this report by having a text concatenation of the case number and date being IN the DATASET of the report created first, using the same concatenation there. This restricts the data to only the last session for each case.

Next, create a sub-report, placed in an enlarged page footer of the report. This will show the information for the session just prior to the last session, if it exists. Use the same data sources as the Main query and group on the case number, but make the filter as follows. In the summary filter, have it equal to the Max( session date), but in the detail filter, have the same concatenation column of case number and session date be NOT IN the same DATASET as the main query. This will restrict the sub report to the newest session before the last session. Additionally, make the case number in the sub-report equal to the case number from the main query.

This should give you the results you want.

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

 

[nagrajm]

Instead of using MAX(SESSIONS_DATE)in the data definition of the report (which is what you are probably doing), Use the following statement in the summary filter

SESSIONS_DATE = MAX(SESSIONS_DATE)

Make sure your report is grouped by CASE_NO.

Otherwise,

SESSIONS_DATE = MAX(SESSIONS_DATE) FOR CASE_NO

That should resolve your problem.