Large Complicated Report 1

[zemp]

Using: CR 10.
Database: MS SQL Servcer 2000 SP4.
Connection: likely ODBC but open to suggestion.
Called from a VB app.

I am trying to design a large (20+ pages) report for an insurance company and I am not sure of the best way to do it. I am looking for your suggestions and comments.

Here goes...

The report is to detail a client and the employee benefits that a client has. There are 20+ benefit packages available and a client can choose from the 20+. This means that the report will have the client information and information on the selected benefits (1 - 20+).

I am thinking of having each benefit as a separate subreport within the main report. I am hoping that this will allow me to suppress the specific benefit if it is not selected and print each one starting on a new page.

Page numbering is an issue as it must by consecutive page numbers regardless of the number of benefits (each benefit is 1-5 pages). I am hoping that the subreports will allow the page numbering (footers) to be consistent with the main report.

My Questions are as follows.

1. Will this report design work? If not why?

2. If it will work, how and where is it best to set up the subreports?

3. Is there a better way to design the report?

4. Are there any pitfalls that I should watch for?

If you would like more clarification or detail then please ask.

Thanks for taking the time to read and consider this post. All comments and suggstions are welcome.

zemp

 

[synapsevampire]

My Questions are as follows.

1. Will this report design work? If not why?

Perhaps, but why use a subreport if you don't need to?

2. If it will work, how and where is it best to set up the subreports?

Group by the client and they'd exist at that level.

3. Is there a better way to design the report?

I think so, but we need technical information.

4. Are there any pitfalls that I should watch for?

Subreports are slower as they execute for each section that they exist in.

In essence your question asks us to understand the underlying data and speculate on architecture, without you having posted anything about how the data is stored.

You did a nice job of stating your environment, now we need table/relatioship information.

From what you've posted, I would next group by the Benefit, and create parameters for the benefits they wat to see, then group by the benefit, and use the group option of a sepecific order for the benefits (if I undertstood your requirement correctly), and list them in the order you need them in the report.

This should avoid subreports.

If possible, have a View created to return the data grouped by employee, then benefit, that should simplify things a bit if the table structure is complex.

-k

 

[zemp]

ok, let me go further.

Each report will only be for ONE (1) client at a time. This will be selected from a client table via a PK (clID).

The selected benefits are stored in a clientBenefit table which has a CPK of the clID (FK to client) and the BenID (FK to Benefit). Benefit names are stored in a spearate table. This clientBenefit is sort of a look up table to a clientBenefitDetail table which has a CPK of clID, BenID and the BenDetID(FK to benefitDetails table).

The benefits table will be less than 50 records. Then benefitDetails is already 1500 records and the clientBenefitDetails table will be in the tens of thousands and growing.

Examples of benefits are Prescription Drugs, Dental, Vision, etc.

Examples of Benefit details are waiting period, Maximum coverage, # of prescription refills, Crowns included, contacts, new frames, etc.

The clientBenefitDetail information will need to be in the details section of the subreports.

What I am thinking is to place one subreport in the details section of the main report and pass it the benefit ID for each of the clients selected benefits.

Speed is not critical and they have a server (not overworked, yet...) running a LAN of about 15 computers (one building).

Hope that helps. Thanks again for your time.

zemp

 

[synapsevampire]

If you don't want to consider options, then I can't really help you.

"The clientBenefitDetail information will need to be in the details section of the subreports.". This isn't true, you've just decided to use subreports, which is ALWAYS my last choice unless I'm designing a dashboard report using disparate data sources.

I'd set up the joins in the main report, then group by Employee and then by benefit. This allows for a single query to be issued to the database.

The fact that you don't care about optimizing has little to do with it, and of course if the database grows, or server load increases, you've used a kludgy method that will need a rewrite.

If there's an overiding reason why you think it wise to use subreports, then do so.

My approach is to build out database objects anyway, such as Views or SPs. Embedding business rules in clients is rarely a good idea in any system.

-k

 

[LLowrance]

I agree with synapsevampire, use subreports as a LAST resort. I've had to embed business rules in a report in the past because I had to. It's never a good idea. They are a nightmare to maintain much less try to remember which reports need updating as things change. I work in the insurance arena also and from the information you have shared you do not need a subreport. Grouping is a better way to go. Good luck.

-LLL

 

[zemp]

Sorry to sound as if I am absolutly going to use subreports. I was just trying to let you know where my thought process was at the moment (right or wrong). I am open to options. That's why I am asking.

I like your idea of building and using database objects (views and SP's). I have never used a SP in a Crystal Reports before.

Keeping the business rules out of the report also makes good sense. Something I didn't even consider (not sure why).

Slowly I am learning and I am listening.

Also I am not sure if it is simple grouping. I assume that the clientBenefit will be the main group (each report will deal with just one client) and the ClientBenefitDetails will be in the details section.

---Client (Main Report page header)
------ClientBenefit (Grp Header)
---------ClientBenefitDetails (Details)

I am more familiar with using a view, so I am leaning that way.

One other thing that I am not sure of is the use of footnotes. some of the Benefit details have footnotes that need to show up on the same page as the benefit detail itself. I believe that the numbering of the footnotes can be handled by a variable but which report section should the footnotes be placed in and how to ensure that they are on the same page.



zemp

 

[synapsevampire]

I've no idea what these footnotes are, you speak of your report layout and data as if we can see it, and know what the expected ouput is, you need to state the source of the data, and where you want them as opposed to asking us where you want them.

I guess my answer is place them wherever you'd like, if the footnotes take up more than a page, than it can't all fit...master of the obvious here

Footnotes are usually displayed at the bottom of the page, so the page footer might make sense, but what are they? If they're a field, place them in the details as well.

-k

 

[zemp]

That's probably too general a question for this stage. I'll ask it again when I have more details. I am sure that I will have other questions as well.

Thanks so much for your help. I am going to do it with grouping and use either a view or a stored procedure. I will see which is easier/better to pass one parameter (the Client ID).

Thanks again.





zemp