Is it possible to design a report with 3 date ranges?

[Tepzepi]

Hi All,

I need to design a report that has me stumped. I'll try and provide the specs in full:

The End Result

A B C D E F G H I J K L M N O
Group Client Client ACC Premium Levy Comm
Code No Name Exec

Columns E,F & G based on Date Range 01 Sept 2001 to 31 Dec 2001
Columns H,I & J Same headers as E,F & G but Date Range 01 Jan 2002 to 30 Sept 2002
Columns K,L&M Same headers as E,F & G but different filters (invoices that have not been completed)but on Date Range 01 Sept 2002 to 31 Dec 2002
Column N = H + K (Total Premium 2002)
Column O = J + M (Total Comm 2002)

Furthermore, the date range selection needs to be parameter driven in order to select on one of 2 date fields (Invoice Date or Effective Date)
There are only 2 Tables required viz Client and Policy.

I started off by dismissing bringing in the same tables again as aliases as the date ranges would be generic to the report and then thought Sub reports is the way to go.

Hence I created the primary report with date ranges for columns for E, F& G.
This report is Grouped by Branch, Department,ClientNo and CoverNo.The latter field for drill down options and most probably will need InvoiceNo at a latter stage.
Subtotals on each Group.
I then copied the primary, selected date ranges for columns H,I & J, suppressed client information,hence Sub Report A. Copied the primary again, and filtered according to requirements and date ranges for columns K,L & M, hence Sub Report B.
At this stage I have not as yet placed parameters in the report for date range selections, or any other parameters.

However, my first challenge has been run into linking issues (Sub Report A linked via ClientNo) and after searching for clues in Tek-Tips and reading the manual on Sub reports again, have realised that Sub reports will not allow me to have different date ranges on one report, as such it's fruitless exercise to expend time to resolve my linking issues.

1.)The question is, is it possible to create the report in question? (If so based on what I have done to date, my question would be how to fix my linking issue which I'm still trying to get to work, more of that later if required)
2.)If so,but not on the way I have been trying to write the report,please could you provide pointers for the best way forward to design this report.
3.)If not possible, other experienced recommendations would be appreciated.

CR8
Oracle 8i

Thanks in advance and Regards

Warren I'd rather be stupid for 5 minutes than for the rest of my life.

 

[Naith]

Warren,

This is possible, and you are barking up the right tree so far.

My interest is piqued when you say that different date ranges in sub-reports is impossible. This kind of thing is what sub-reports are for.

The two ways I think you could do this is to either:

Extract a single chunk of data based on the single minimum date of all three range parameters to the single maximum date of all three range parameters. This expends the need for subreports, but if it's likely that the report will cater for ranges which are quite far apart - like Range1 in 2000, Range2 in 2001, and Range3 in 2002 - then you're talking about extracting a lot of excess data. If you're database isn't massive at present, and if it's unlikely that you'd be carrying a lot of wasted data - then this approach would be quicker to process than subreports, as it's only hitting the database one time.

The alternative, which is the way it seems you're already researching, is to have 3 parameter ranges prompted by the main report. This results in far less, if any, wasted data. The 1st range is included in the main report selection criteria. Range 2 and 3 are prompted for, but are not included in the main report selection criteria.
The two subreports are linked to the 2nd and 3rd range respectively, so the database will be hit 3 times but for a different date range each time.

To provide you with a linking example, you'll be linking on the fields which ensure that each record is unique; GroupCode and ClientNo, for instance. You'll also need to link on the date, so:

[ul][li]Ensure each subreport has one date parameter, of the same type as the main report DateRange2 and 3 parameters.[/li]
[li]Link the correct date range parameter from the main report to the actual parameter name on the subreport. This means that you do not want to link to the {?Pm-?xxx} in the bottom left linking window. You want to scroll down on the very short scrollbar (it's only about 0.5cm high) in the bottom left of the linking window until you see the parameter name as it is in the subreport.
Make sure that the selection criteria of your subreports reflects the parameter values.[/li][ul]

Repeat the process for the other subreport.

Bear in mind, that if you're going to process the subreports for each line, then this is going to take a while to run.

I hope this helps with providing some direction, but if anything is unclear, please don't hesitate to come back.

All the best,

Naith

 

[Tepzepi]

Hi Naith,

Thanks for the expeditious and lengthy reply, it really is handy when the experts try and lay on as much information for the novices like myself.

I obviously misinterpreted what I was reading when researching sub-reports, hence the erroneous conclusion.

So now I need to get a greater understanding of how to use sub reports, as the sub reports alternative appears to make more sense to me. I have diligently followed your instructions , however I cannot see the date parameter fields as a choice when trying to link the first of my sub-reports.

Let me confirm that I am looking in the right place. When Inserting a sub report, I choose an exiting, and on the Link tab, I have selected ClientNo to link from Container report then the <<Sub Report parameter field to use: >> becomes available. This shows a default of ?Pm-ClientNo. Scrolling down in this box I see Parameter Fields: but nothing else.
Have I missed a step?

While I'm on, if I may, I have another 2 questions.
I assume I have to leave the Select data in sub report based on field will be more meaningful to me once I have the data parameters available for selection, or is this where I select ClientNo in the Sub report as the Client No Link?
My report is going to display my Group 3 _ClientNo summary fields as the information the end user requires. Where am I supposed to actually supposed to insert the sub report, and via relevant section format select underlay.
Also

What I have is a From Date and a To Date parameter in each report. No other parameter in primary and sub report A, but a second from and to Date parameter (based on REN_Date) in Sub report B. That's it for now.Are these multiple parameter types going to create a problem?

Regards

Warren



I'd rather be stupid for 5 minutes than for the rest of my life.

 

[Naith]

...expeditious...erroneous... careful, old chap, you may lose me there. I'm not used to dealing with such sesquipedalian typists.

It sounds like you're certainly on the right track with your link attempts. But you need to ensure that the date parameter in the main report is exactly the same as the parameter type in the subreport. (i.e. don't try to mix dates with datetimes or discreet with ranged parameters). You only have one set of date ranges in each subreport.

Once you have correct parameters set up in the subreport, when you go into the link expert, you'll see something like:

Fields to link to:
{?Pm-?MainReportDateParameter2}
<...then scroll down to see>
{?SubReportParameter}

Link the second date range parameter from the main report to the subreport. If you want the subreport to run for each grouping or record, then you will need to link the group fields too. Then you place the subreport where you want the data to appear in the main report - most likely in the group header/footer or details section. (Remember, the more times your subreport has to process, the slower your report will be.)

As far as the selection criteria in the subreport is concerned, it should reflect the date difference, but otherwise be the same as the main report.

For example, if your main report selection criteria is:

{Field1} = &quot;A&quot; and
{Field2} > 100 and
{Date} in {?MainReportDateParameter1}

then your subreport criteria is:

{Field1} = &quot;A&quot; and
{Field2} > 100 and
{Group1} = {?Pm-Group1} and
{Group2} = {?Pm-Group2} and
{Date} in {?SubReportParameter}

Your linking criteria should pass itself accurately into the selection criteria of the subreport.

Hope this helps you out some.

Good luck,

Naith

 

[lyanch]

What Naith (and you) have suggested will indeed work, but there is a much easier method, that will allow you to easily change dates etc. and you don't have to deal with the problems that arise when a group DOESN'T have data for a particular period, passing shared variable etc...

I would create a manual cross tab for this. Grouping by &quot;group ID&quot; and only showing the group footer with the data that you want to display, supressing the group header and detail.

Howard Hammerman has a good FAQ on how to create a manual crosstab:

http://www.tek-tips.com/faqs.cfm?spid=149&sfid=1219&CFID=84529881&CFTOKEN=42403452

If the direction your heading works for you, great. If you start running into problems I would take a look at it from this direction.

Lisa

 

[Naith]

Ideally, Warren, any alternative to subreports is an alternative worthy of consideration, simply because of the performance overhead you subject yourself to when you start getting involved in subreports - particularly those that have to run many times per report, like yours will have to.

A manual cross tab is certainly an option for you, as Lisa has suggested. However, if you do decide to retain the standard report format, and if you can grab all date ranges in the main report without too much hassle, I would consider that too. Database considerations notwithstanding, for personal preference, I would most likely go with a selection criteria similar to:

({Database.Date} in {?Range1} or
{Database.Date} in {?Range2} or
{Database.Date} in {?Range3}) and...
<...the rest of your criteria...>

and eliminate the need for subreports.

Naith

 

[Tepzepi]

Naith, Lisa,

Writing crystal reports is an add-on to my actual job, and I am not a tecchie by any means (no kidding?). Your recommendations have now veered me down the river of mysterious waters and I have missed my deadline by 2 days to date. Hence I'll have to outsource this report.

Thanks for your time and assistance it's really appreciated.

Regards

Warren
I'd rather be stupid for 5 minutes than for the rest of my life.