Returning all or no records in a group

[jbarbato]

Hi there! I am using CR XI and an Oracle DB.

I am grouping by package, and each package has several dates as details...

Package A
2/19/2005
3/12/2005
4/5/2005
5/10/2005
Package B
4/10/2005
6/12/2005
8/15/2005
Package C
1/2/2005
1/23/2005
2/4/2005
3/12/2005

I have two parameters: Start_Date, and End_Date. If any date detail of a package is between these two parameters, I want all details to show. Example:

Parameters:
Start_Date = 4/1/2005
End_Date = 4/15/2005

Result:
Package A
2/19/2005
3/12/2005
4/5/2005
5/10/2005
Package B
4/10/2005
6/12/2005
8/15/2005

 

[synapsevampire]

Insert a Group (under Insert) by the package, then place the date in the Details section. Suppress the group footer.

Go to the Report->Edit selection formula->Record and place something like:

(
{table.date} >= {?start_date}
and
{table.date} <= {?end_date}
)

This will limit the data returned to the report, and the grouping will provide the look you want.

-k

 

[jbarbato]

That would return
Package A
4/5/2005
Package B
4/10/2005

Which is not what I want... Thank you though

 

[synapsevampire]

Ahhh, I get it now.

I think that you'll need a 2 pass system for this, or do it in a Stored Procedure on the database.

Create a 3 formula system:

Group Header:

whileprintingrecords;
booleanvar DontShowme:= True;

Details:
whileprintingrecords;
booleanvar DontShowme;
If {table.date} in {?start_date} to {?end_date} then
DontShowme := False;

Place a subreport in the group footer to display the details, linking by the package field, and in the suppress for the group footer use:

whileprintingrecords;
booleanvar DontShowme

Should work.

-k

 

[vidru]

You could also try using a Command object, the SQL for which would look something like[red]*[/red]:
[tt]
SELECT Fields
FROM Table T
JOIN (SELECT DISTINCT Package
FROM Table
WHERE DateField BETWEEN {?Start_Date} AND {?End_Date}) T1 ON T.Package = T1.Package
[/tt]
That should return all package records for any package that has a Date in your range.

The Start_Date and End_Date parameters would have to be defined in the Command.

[red]*[/red] The SQL used is fairly standard, but I'm not an Oracle user.

-dave

 

[lbass]

Or, you could group on {table.package} and then create a formula {@inrange}:

if {table.date} in {?start} to {?end} then 1

Place this in the detail section. Then go to report->selection formula->GROUP and enter:

sum({@inrange},{table.package}) > 0

-LB