Making two calls to the same view in a report

[KidFix]

Hi all

is there a way I can return two sets of data from the same view within one report? I need to pass in a sf where clause to a report and make a calculation based on one set of data only filtering on the date range passed in by the sf whilst displaying data filtered on the entire sf where clause.

Sounds a bit improbable to me, maybe there's a way of using a sub report? If I have a subreport in a view, are both the main report and sub report effected by the sf where thats passed in or is there a way of filtering the subreport independantly?

Many thanks

 

[lbass]

You could add the view in twice. But if you only need to do a calculation on some of the records within the view, you might be able to do that without adding in the view again or without adding a subreport, but we would need more information on what you are trying to do and maybe some sample data.

-LB

 

[KidFix]

Thanks for replying

I'll try and explain the situation further. The report is for a timesheets application. In my view are three fields (there are more but for simplicity we'll only consider three) PeriodHrs, DayDate and ProjectType.

Say this is the data contained in the view:

PeriodHrs DayDate ProjectType
7.5 01/01/01 Managed
7.5 02/01/01 Other

if a user filters on the date range 01/02/01 to 02/01/01 the report needs to calculate the total working hours in this date range using PeriodHrs. Not a problem in this example, but if a user filters on the same date range but also where ProjectType = 'Managed' the total period hours should be the same as its over the same period of time, however only 1 record will be returned by the report so my calculation will only return 7.5 rather than 15.

Now you might say I should just multiply the number of days in whatever date range is filtered on by 7.5 but its not a straight forward as that as the PeriodHrs field takes into account for weekends and public holidays and such.

Thanks

 

[Reebo99]

Rather than filtering the data that comes down, conditionally suppress the detail section. Suppressed areas still form part of a SUM() field.

I'm assuming you're filtering based on a parameter, so remove this selection from the record selection formula, then goto the conditional suppress of the Detail section
(right click detail section label in design, section expert, x2 by suppress) and enter something like :
{Table.ProjectType} <> {?ProjectParam}
replacing with your actual field names.

If you now right click on the PeriodHrs field > insert > summary > sum . This will be the total of all records in the date range, but only the selected project item will be displayed.

Reebo
UK

 

[KidFix]

thanks reebo

the report is being filtered not by a parameter but by an sf where (i.e. reportname.rpt?sf={fieldname}=val AND {fieldname2}= etc.)

this isn't really a suitable solution for me as it'd take a lot of work to implement, there can be anything up to seven fields being filtered on in the where clause passed to the report, there are in excess of 20 formulae diong various calculations as the report is genereated, each of these would have to be amended to ignore the surpressed records, and on top of that I'd have to add seven parameters to the report to receive each filter paramter and I'd have to set something up allowing for null values passed to a parameter.

I'm not slurring your solution, its a good way of doing it, its just not practicle for my situation

Thanks