Suppression and things... 2

[fattyfatpants]

Hello all I've got a few questions

Using: CR 9 Standard with SQL Server 2000 using Stored Procedures

I work for a CPA firm and have been given the task to build a very large and complex hours analysis report. I am building it in sections, obviously, so what I have so far is the following structure:

Main Report - Hours Analysis for Current Month
RH - Suppressed
PH - Company Name, Report Name, Field Headings
GH1 - Grouped by Department
GH2 - Grouped by Timekeeper Title (i.e. Auditor, Staff, etc.)
D - Necessary Fields (Name, Hours worked, chargeable hours, etc.)
GF2 - Summary of Hours worked for each timekeeper by title
GF1 - Summary of Hours worked for each title in the department
RFa - Firm totals
RFb - Subreport

Subreport - Hours Analysis Year To Date
Exact same structure as main report except it spans the current fiscal year up to the current month

What I would like is to suppress the main report header when the subreport is shown. I have tried to use the onlastrecord function but it doesn't work because of the firm totals in the RF. Any ideas??

A few more things. From the looks of it I have about 5 more reports that will need to be created to build the final report. A few of these reports will need to share information with the others in order to do more calculations; Should I remove the subreport above and place both it and its parent report in a large main report instead of having subreports within subreports like this:

Large Main Report
--SubReport
--SubReport
--SubReport
----SubReport
--SubReport

Or:

Large Main Report
--SubReport
--SubReport
--SubReport
--SubReport
--SubReport

I also need to be able to calculate the number of work days in the current month, prior year current month, year to date, and prior year to date. I have looked at synapsevampire's FAQ of creating a table to hold all of the dates and holidays, this would work if we didn't have floating holidays that change from year to year. I suppose that until I create the firms intranet to give the ability to go in and designate the floating holidays I will need to manually do so. Any suggestions??

Thanks in advance for any advice!!!

 

[lbass]

If the subreport is less than one page long and you are willing to have it on its own page, then format RH_b with new page before and then format the page header section to suppress with:

pagenumber = totalpagecount

You cannot have subreports within subreports, but you can share values between subreports, depending on the order in which they execute (shared variables from a subreport will only work in main report sections or in other subreports that are in sections below that in which the subreport is located.

-LB

 

[fattyfatpants]

thanks for your response, unfortunately the subreport is about 10 pages in length so that won't work. On the main report that will house all 7 of my subreports, does it matter where the subreports go?? Should I just create PHa, PHb, PHc,...PHg?? The only thing I want to pass from the main report to the subreports would be the date parameter that I want the reports to report on. Thanks.

 

[lbass]

To eliminate the page header on the report footer subreport pages, create two formulas in the main report:

//{@true} to be placed in the report header:
whileprintingrecords;
booleanvar flag := true;

//{@false} to be placed in the report footer_b (where the subreport is located):
whileprintingrecords;
booleanvar flag := false;

Then go to the section expert (format->section)->page header->suppress->x+2 and enter:
whileprintingrecords;
booleanvar flag = false;

The placement of your subreports does matter. First, you don't want to place them in a page header or footer since they would either repeat for each page or you would get a message that the object was too large for the space. You could place them in the report header section or footer sections, but earlier you said that you wanted to share information between subreports for calculations, so you would have to ensure that your subreports that were passing shared values were positioned above the section in which you want to perform/display the calculations.

If your main report has a date parameter and you want your subreports to use the same date parameter, then in each subreport create the same date parameter and in the subreport selection formula use it to limit records. Then in the main report, go to edit->subreport links and choose the main report {?date} to move to the right, and in the bottom left corner, use the drop down to choose {?date} (NOT {?PM-?date}) for the subreport field. Repeat for each subreport.

-LB

 

[fattyfatpants]

Awesome, thanks for that information. Now that I have completed a large portion of my report I am looking at it and can't help but think that there has to be a better way of doing it. As it stands right now I will have 9 subreports that make up the sought after report. The one that I am concerned with is the Hours Analysis Breakdown report that is basically a summary page for the number of hours worked for each department over the current month, previous year current month, current year to date, prior year to date, and two prior years to date.

# On Staff Current Month
Current Yr Prior Yr Dept Total Hrs Charge Hours
36.00 36.00 Audit 6,798 4,797
25.00 22.00 Tax 4,097 2,385
.
.
.
22.00 18.00 Admin 3,451 314

Total 18,827 10,221

FullTime Equivalence
35.00 33.75 Audit
23.00 20.00 Tax
.
.
.
10.50 8.50 Admin

The top portion is grouped on Department and so is the bottom portion. How can I have two groups at different places in the same report?? I was thinking about making the top portion one report and the bottom portion another report and then use Underlay Following Sections but I can't use subreports within subreports...any ideas?? I hope I have explained this good enough.

 

[lbass]

Why not just place the Fulltime Equivalence columns next to the #On Staff and Current Month columns?

Otherwise, if you must have this layout, you could do the top portion and the bottom portion as separate subreports. Or, you could create these summaries using running totals where you use the evaluation section to limit records to a particular department. Then place the running totals in the report footer. A lot of work though, and not feasible if there are many departments.

-LB

 

[fattyfatpants]

lbass,

the partners at my firm want it laid out exactly like above because they feel it is easier to look at (rolls eyes). Anyway, I thought of doing the subreport thing but like I said above I will already have 9 subreports at this point and from what you tell me it is not possible to have subreports within subreports (correct??) so I am thinking either
a) have both portions on their own reports which would look very horrible
b) tell the partners they are out of luck and it can't be done the way they want

What do you think??

 

[lbass]

I'm having a little trouble visualizing where these two components are located. If you are displaying results for the inner timekeeper group, then to get the display you show above, you are forced to use either running totals or subreports in a report footer section.

I guess I would insert a subreport and create the first section as a manual crosstab grouped by department. Then I would save the subreport and import it for placement in a separate report footer section, keeping the same criteria and group structure, but changing the column fields. Multiple running totals can slow reports just as subreports can, and I think subreports are probably simpler in this instance.

-LB

 

[biobrian]

lbass your suggestion below:

To eliminate the page header on the report footer subreport pages, create two formulas in the main report:

//{@true} to be placed in the report header:
whileprintingrecords;
booleanvar flag := true;

//{@false} to be placed in the report footer_b (where the subreport is located):
whileprintingrecords;
booleanvar flag := false;

Then go to the section expert (format->section)->page header->suppress->x+2 and enter:
whileprintingrecords;
booleanvar flag = false;


I'm a little confused on where to put those formulas

 

[lbass]

{@true} goes in the main report report header. {@false} goes in the main report footer (the section where your subreport is located.) Then you suppress the main report page header conditionally by going into the section expert (format->section)->page header->x+2 and entering:

whileprintingrecords;
booleanvar flag = false;

I'm not sure how to be clearer about this. Maybe you should say explicitly what you're not sure about.

-LB

 

[biobrian]

i got it thanks, small brain fart.

It worked nicely, except now by report header is missing.
Also my sub-report goes onto a second page, with no headings attached to it.

thanks again

-bh

 

[lbass]

This would not affect your report header. Go to the section expert and make sure it is not suppressed.

To create page headings in a subreport (which has no page header section), first create a formula in the subreport that will be true for all records, as in the following {@all}:

{table.ID} > 0

Then insert a group on {@all} and format it to "Repeat group headings on each page". Then drag your headings from the subreport report header into the group header.

-LB

 

[fattyfatpants]

lbass, I have gotten my report to output the information accurately and now I am proceeding with cosmetic fix ups...One of my problems is figuring out how to make the report flow correctly. I have a total of 7 subreports that are now making up the single larger report and due to the way the data has to flow in order to return the correct results I have to place the subreports in reverse order that they need to be viewed (i.e. the 7th subreport should be the first viewed and the 1st subreport should not be viewed at all). Do you know of any tricks that can be done to do what I am talking about?? The other problem that I am having is that I have two subreports that shouldn't be visible at all (they are the top two)...I have tried to insert them and then resize them to the smallest possible size but there are blank pages being printed instead. Is there anything I can do about this? Thanks.

 

[lbass]

You should be starting new threads when you introduce new topics. To answer your last question first, from what I've read, in CR 9.0 I believe you can go to format subreport->subreport->and check "Suppress blank subreport." Also, in many cases, you can suppress all sections within a subreport and then resize the subreport to make it tiny within the main report.

In terms of order of subreports, if you must have the subreports in a certain order so that shared variables are returned correctly, sometimes you must add a subreport twice--once early enough to create the correct shared variable, and later for when you want it viewed. If your subreport #7 is on the receiving end of shared variables, then all subreports contributing to the shared variable must be placed ahead of it.

-LB

 

[biobrian]

I am still having an issue with the page header showing on the report footer subreport pages.

I have the two formulas named //{@true} and //{@false} with the correct formulas with each, placed in the correct spots on the report.

In the page header section I have "suppress(no drill down) selected with the correct formula for section visibility.

When the report is run I have the word "true" in the report header and "false" in the report footer.

Any idea what I am doing wrong??

 

[lbass]

Please check the suppression formula and make sure that it does NOT have a colon in it. It should look like:

whileprintingrecords;
booleanvar flag = false;

It is not setting the flag to false, it is saying suppress IF it is false.

-LB

 

[biobrian]

That was it, thanks for your help.

Cut and paste got me good.