CR9 Subreport Record Selection

[TimBiesiek]

Hi all,

Am using CR9, and want a subreport to look up data based on other values in my main report.

I have created 2 parameters that hold the minimum and maximum dates, and I want the sub report to pull up all data from a different, unlinkable table based on this date range.

I have tried the following in the record selection formula, but to no avail:

{dlg_AreaCodes.Area_Code} = {?Pm-dlg_AreaCodes.Area_Code} and
{arc_Round.DueDate} >= {?Pm-Min of dlgV_055_ArcRoundMinMaxRTime.RDate} 
and
{arc_Round.DueDate} <= {?Pm-Max of dlgV_055_ArcRoundMinMaxRTime.RDate}

Any help would be hugely appreciated!!!

 

[Madawc]

You haven't said what you mean by no avail. Too much data? Too little? The wrong data?

In the subreport, choose Database>Show SQL Query - that's how it would be in Crystal 10 and Crystal 9 should be similar. Post the actual SQL commands and someone may be able to figure the problem.

I'd also suggest a quick report using Crystal's 'wizards', just to check that the data you are after really is what you think it is. I've often found that data wasn't what I expected.

Madawc Williams (East Anglia, UK)

 

[TimBiesiek]

The issue is that either too much or too little data is returned. I have another link to the subreport based on AreaCode, and this works fine. If I remove the date link I get too much data, but if I add the link (as =, not a range) then I get too little data.

I want the subreport to show data based on the range of dates between the minimum and maximum...

SQL Query is:

SELECT "dlg_AreaCodes"."Area_Code", "arc_Round"."DueDate", "arc_Round"."RoundID", "arc_Round"."TargetRate", "dlgV_060_ArcRoundCounts"."Expr1"
 FROM   (("DialogCoreData"."dbo"."dlgV_060_ArcRoundCounts" "dlgV_060_ArcRoundCounts" INNER JOIN "DialogCoreData"."dbo"."arc_Round" "arc_Round" ON ("dlgV_060_ArcRoundCounts"."ArchivedSessionId"="arc_Round"."ArchivedSessionId") AND ("dlgV_060_ArcRoundCounts"."RoundID"="arc_Round"."RoundID")) INNER JOIN "DialogCoreData"."dbo"."dlg_ZoneCodes" "dlg_ZoneCodes" ON ("arc_Round"."Zone_Code"="dlg_ZoneCodes"."Zone_Code") AND ("arc_Round"."Company_Code"="dlg_ZoneCodes"."Company_Code")) INNER JOIN "DialogCoreData"."dbo"."dlg_AreaCodes" "dlg_AreaCodes" ON ("dlg_ZoneCodes"."Company_Code"="dlg_AreaCodes"."Company_Code") AND ("dlg_ZoneCodes"."Area_Code"="dlg_AreaCodes"."Area_Code")
 ORDER BY "dlg_AreaCodes"."Area_Code"

 

[synapsevampire]

Where in the main report in the subreport? And isn't area code the link between the main report?

Anyway, successful posts generally include:

Database/connectivity used
Example data
Expected output

Note that you should execute this SQL on the database to see what is returned, but I'd guess that you've set up the relationships icorrectly, rather than the record selection being incorrect.

Also note that your record selection isn't getting passed to the database, so either it's in the wrong place, or Crystal is doing all of the filtering for it, which means it's slow.

Remark out each section of the record selection until you start getting data.

-k

 

[TimBiesiek]

It's ok, I got around this one using the method described in the FAQ here...