Show only results with the most recent date

[hsandwick]

Crystal 2008
Oracle 9
Business Objects 3.1

In a subreport, I am retrieving TopN = 5 results for each Division (a group), based on the total count of all unique employee ids within each group that exist within a date range.

So, results look like this

Group Employee Total Count
A 500
B 400
C 300
D 200
E 100
Other 10

However, sometimes unique employees have a result in more than one group, e.g., Employee "a" is counted in the total for Group A, and is separately counted in the total for Group C. This is because two separate events occurred for that employee within the date range of the subreport.

In this instance, we wish to retrieve only the most recent date event for our subtotal employee counts, and suppress the earlier event date.

I've been testing maximum(table.date), but so far haven't resolved this one.

Thanks in advance for your suggestions!

-hms

 

[lbass]

You need to create a SQL expression {%maxdate} like this:

(
select max("eventdate")
from table A
where A."EmployeeID" = table."EmployeeID"
)

Then in the record selection formlua use:

{table.date} = {%maxdate} and
{table.date} = {?range}

Then the employee will only appear in one group (assuming they can't be in the same group on the same date).

-LB

 

[hsandwick]

Thanks, lbass. I created the sql expression, and now I'm receiving the error message,

"Error Compiling SQL Expression : Failed to retrieve data from the database ORA 00942 table or view does not exist"

But the error is only relevant to the SQL Expression. The table exists and all results compile in the main and other subreports.

Note: the groups A/B/C/D/E/Other are the TopN resulting "groups" under the Division Group, hope I was clear about that.

 

[lbass]

Please show a copy of the SQL expression as you created it.

-LB

 

[hsandwick]

(select max("PDRMF_ALL"."EVENT_EFF_DTE")from "PDRMF_ALL" where "PDRMF_ALL"."SSN" = "PDRMF_ALL"."SSN")

 

[FireGeek21]

hsandwick,

Using LBass's SQL as an example:
(
select max("eventdate")
from THIStable A
where A."EmployeeID" = REPORT_table."EmployeeID"
)

REPORT_table should be a table in your report. Essentially the where A."EmployeeID" = REPORT_table."EmployeeID" statement is linking the table in this SQL statement to the table in your report.

Hope that explanation helps.


FireGeek
(currently using Crystal Reports XI with Lawson 8.03)

 

[lbass]

You should be able to copy the following into your SQL expression editor:

(
select max("EVENT_EFF_DTE")
from "PDRMF_ALL" A
where A."SSN" = "PDRMF_ALL"."SSN")
)

-LB

 

[hsandwick]

Thanks, lbass and FireGeek21 - it'll be early next week before I can test this, will try it out and let you know.

-H

 

[hsandwick]

Copied the sql, still received errors (table or view does not exist). Ran tests in TOAD, this works below, where the schema is included:

(select max("EVENT_EFF_DTE")from "HISTDBA"."PDRMF_ALL" A where A."SSN" = "SSN")

Running the report with this sql expression, it kills the already slow performance, and the results are " ".

It seems to be looking for the max date in the entire subreport. But what we're really trying to achieve is the max date for a ssn where more than one date exists for that ssn within the Division Group | results for the Top 5 subgroups within the Division. So, say, the ssn exists separately in two TopN subgroups, but only exists as one count in each TopN subgroup. In this case, we want to only retrieve the one instance in whichever TopN subgroup shows the ssn with the most recent date.

I'll keep testing ...

Thanks,
-H

 

[lbass]

You didn't copy it correctly. Please try again.

-LB

 

[hsandwick]

Hello, lbass,

I did copy the code as suggested:

(select max("EVENT_EFF_DTE")from "PDRMF_ALL" Awhere A."SSN" = "PDRMF_ALL"."SSN") )

(copied directly from above once more just now), but it didn't work. So, then I tested it but added the schema name, also, and while this collected results in TOAD, there were contentions in Crystal.

Ran into additional issues.

Thank you,
-hsandwick

 

[lbass]

Then I think this is how it should look:

(
select max("EVENT_EFF_DTE")
from "HISTDBA"."PDRMF_ALL" A
where A."SSN" = "PDRMF_ALL"."SSN"
)

You do need to have the table PDRMF_ALL in the main report though for this to work.

-LB