Inconsistent value in SQL Expression

[RenaG]

I am using Crystal 10.

I am working on a report that uses a view. The view lists all the custom reports in our system showing the date/time it was run, who ran it, the job status, what server it was run on, the report path, etc. I am trying to create a report that is grouped by the report name and sorted by date in descending order and just shows the last time the report was run. But I also want a count of how many times the report was run. This is how I set it up:

I created a SQL Expression Field called max_CreateDt:

(
select max("Create_DtTm")
from vw_RptTracker A
where "A"."Rpt_Name"="vw_RptTracker"."Rpt_Name"
)

Then on the Detail | Section Expert on the Suppress formula I put:

{vw_RptTracker.Create_DtTm} <> {%max_CreateDt}

The odd thing is that throughout most of the report this works perfectly. The group header shows the name of the report and a count to show how many time it was run. For example:

[b]ChargeAudit.rpt                              Run 20 times[/b]
   5/14/2009 10:06:19am  Smith, Larry  PT Dept  Job Completed

So each report should have one header and one detail line that just shows the last time it was run. What I see happening, occasionally, is that there is just a header with the count and no detail.

I took the conditional suppress off the detail line and put the SQL Expression in the group footer to check the value. On the ones that don't work correctly, the date/time value shown in the SQL Expression is incorrect. But it is correct most of the time so I don't get it. Does anybody have any suggestions of what causes it to ocassionally hickup and not have the correct value in the SQL Expression?

TIA!
~RLG

 

[RenaG]

I got it to work!

I created a Formula field called Hld_CreateDt:

{vw_RptTracker.Create_DtTm}

Then, in the Section Expert | Detail conditional suppress:

{@Hld_CreateDt} <> maximum({@Hld_CreateDt},{vw_RptTracker.Rpt_Name})

BUT - if anybody has any suggestions for why the other way didn't work, I would certainly like to hear them.

~RLG

 

[lbass]

When you are using a SQL expression, in most cases you must build in the same selection criteria as in the main report, since it accesses the database directly. I'm guessing the SQL expression was picking up values excluded in the main
report selection formula.

-LB

 

[RenaG]

Good morning Ms Bass,

That could be. I do exclude some records in the Record Selection Formula. Thank you for taking the time to explain this concept! There are so many things in Crystal that I know work but don't know how. Just like the Select Expert code I listed above. I don't know why it works just that it does!

Thanks again!
~RLG