How can I capture the number of pages that makes subreport 1

[biandinfosec]

Crystal Version 8.
environment:
Main report with 2 large subreports
Question: How can I capture the number of pages that makes subreport. Of course the subreports are generated dynamically. I have combed through the forum but havent seen anything like that. Plainly, what I am looking to get is:

If the entire report have 30 pages, and subreport 1 have only 5 pages, subreport 2 have 24 pages, and 1 page is for the main report.

How can I calculate and extract the numbers of pages that makes up subreport 1 and 2. The two reports are in the details section.

Any help will be appreciated.

-b

 

[lbass]

How would you plan to use these numbers and where?

-LB

 

[biandinfosec]

The numbers will be used for populating certain sections within the subreport. I will also use the numbers to populate certain sections of main report.
Each of the subreports has several sections that need to print based on certain conditions - one of which is last page for sub report. I want to be able to capture the first and last pages for these subreports, then populate with appropriate information. The information for the first and last page for each subreports are different.

Has this explanation clarify things for you? if not I will attempt to give specifics.

-b

 

[lbass]

I think you should explain more specifically what you are trying to do. You might have to take a different approach than using page numbers, e.g., based on the beginning of a certain report section.

-LB

 

[biandinfosec]

Let me try to give as much information as possible here.

Scenario: Main report(MR) with 2 complex subreports (SR) - R1 and R2.
R1 formating before merge to MR (Page Header -PH, Page Footer -PF)
[ul]
[li]PHa - print once on entire report[/li]
[li]PHb - print from page 2 to end of report[/li]
[li]PFa - print from page 2 to end of report[/li]
[li]PFb - print from page 1 to end of report[/li]
[/ul]

R2 formating before merge to MR - general information about R2 (Has about 7 groups. Only one group (GR#1)is used for breaking page whenever the group formula changes, other groups depends on this change to populate their values. Data that makes up these groups is not known, depends on how many records in the db that relates to these 7 groups, and all associated details sections (DS) - 12 sections - DSa to DSl.)
[ul]
[li]PHa, PHb, PHc, PHd, PHe, PHf - print on first page of group records- print on first page. If there are more records to span more than 1 page, then PHc, PHd, PHe will not print on subsequent pages for that group[/li]
[li]On change of group, PHa, PHb, PHc, PHd, PHe, PHf will print on first group page. Again, if there are more records to span more than 1 page, then PHc, PHd, PHe will not print on subsequent pages for that group[/li]
[li]PFa and PFb - print all all pages[/li]
[/ul]

I would like the report to continue to work in that fashion after being merged as a SR to MR. As we are all aware, all the PH's and PF's are gone, and replaced by report headers and footers, and the logic described above cannot continue to work.

I have tried to create fake page headers, but have not been able to make it work well. Also some of the pages after creating fake headers in R1 continue to print to the end of the reports. I cannot create fake page footer, to force those items in PF to print. I cannot determine where the page numbers for each sub report begin and where it ends.

One last word, the report says that, R1 MUST always print before R2, and lastly R3 ( did I mention R3? yes, there is a R3, but I am not concerned about this, because the information on R3 is static, only print once for entire report)

Hope this will help to speed up providing help.

-b

 

[lbass]

I think the page headers could be handled within each subreport. I'm assuming that you have created a fake page header by inserting a group on a formula like:

whilereadingrecords;
1

... and that you have formatted this to "repeat group header on each page". You would then insert additional group header sections and use:

not inrepeatedgroupheader

...to suppress any page headers that you don't want on the first subreport page. You could use:

inrepeatedgroupheader

...to suppress any page headers that you don't want to print after the first page.

For the page footers, it will be much simpler if you use the main report page footers. I'm assuming here that your two subreports are placed in separate report footer sections a and b, with a 3rd in _c. In the main report, insert four page footer sections, two for each report. Then create five formulas:

//{@false1} to be placed in the report header and in report footer_b:
whileprintingrecords;
booleanvar flag1 := false;

//{@true1} to be placed in the report footer_a section:
whileprintingrecords;
booleanvar flag1 := true;

//{@false2} to be placed in the report header and in report footer_c:
whileprintingrecords;
booleanvar flag2 := false;

//{@true2} to be placed in report footer_b:
whileprintingrecords;
booleanvar flag2 := true;

//{@firstpage} to be placed in the page footer_a section:
whileprintingrecords;
numbervar counter;
booleanvar flag1;

if flag1 = true then
counter := counter + 1;

Then go to the section expert->select the particular page footer section you want suppressed and use a formula like:

whileprintingrecords;
booleanvar flag1;
flag1 = false//note no colon

This will suppress the footer except where the subreport is running. Use flag1 for the first subreport, and flag2 for the second.

In the case where you want the page footer to show only on the first page, you would use a formula like:

whileprintingrecords;
booleanvar flag1;
numbervar counter;

flag = false or
(
flag = true and
counter > 1
)

-LB

 

[biandinfosec]

LB,
You are probably wondering why I haven't responded.
I have been trying your solution. For what ever reason, it's not working for me. Both the repeating on page header and page footer. I have done all the things that you have mentioned here, I do not have the desired result especially the page header is very critital for me to work.

I think I can get around the footer by adding it to the fake groupfooter sections. I actually prefer this solution because, page footer on MR leave empty spaces on the report even when suppressed, this is causing the static information in SR3 to wrap, causing undesirable results.

I can post the report definition file , if that will be helpful. Please let me know.

To recap, the 2 subreports are embeded in the MR - report footer.
With the solution, fake header is only suppress on the first page and is not repeating on subsequent pages on the subreport. Even when I remove the any suppression condition, it only print on first page. Nothing print on second page and subsequent pages.

I still think in addition to this solution, if I can capture the number of pages generated by the 2 subreports, I will be able to use that so print or suppress some of the sections on the subreport.

I am not sure if I am doing anything wrong here. But I have followed the steps judiciously and dont seem to get headway.

Please help!!!

-b

 

[lbass]

It sounds like you don't have the fake group header set to "repeat group header on each page". Go to report->change group expert->options and check it there.

I just recreated your report (for the second time), and it works as I understand your requirements. Place the formulas where I indicated in the main report and then go to the section expert in the main report and create these settings:

//page footer_a -> suppress->x+2 (sub1 pf_a info here):
whileprintingrecords;
booleanvar flag1;
numbervar counter;
flag1 = false or
(
flag1 = true and
counter = 1
)

//page footer_b->suppress->x+2(sub1 pf_b info here):
whileprintingrecords;
booleanvar flag1;
flag1 = false;

//page footer_c->suppress->x+2(sub2 pf_a info here):
whileprintingrecords;
booleanvar flag2;
flag2 = false;

//page footer_d->suppress->x+2(sub2 pf_b info here):
whileprintingrecords;
booleanvar flag2;
flag2 = false;

Also format each page footer section to "suppress blank section".

Report footer (higher order)->new page before.
Report footer_a->new page after.
Report footer_b->new page after.

Then go into sub1->section expert and create these settings:

GH#1a->suppress->x+2 and enter: inrepeatedgroupheader
GH#1b->suppress->x+2 and enter: not inrepeatedgroupheader

In sub2, it is my understanding that you want the first three page headers to appear on all pages, but the last three only on pages where there is a new group. I'm assuming you have set new page after on this group (which will be Group #2, since Group #1 must be the fake page header. Go to the section expert and create these settings:
GH#1d->suppress->x+2 and enter:

{table.group#2field} = previous({table.group#2field}) and
inrepeatedgroupheader

Repeat this formula in the suppression area for GH#1e and GH#1f.

This tests out for me. I understand your concern about the last page of the main report. Perhaps you can gain space my suppressing the page header at least on this page, using:

pagenumber = totalpagecount

The problem with using pagenumbers within the subreports, is that I don't think there's a way of getting the totalpagecount within the subreport. You can use page numbers within subreports, but I haven't been able to get the totalpagecount. You could use pagenumbers within the subreport to hardcode the suppression, e.g., pagenumber > 1, but that won't be dynamic.

-LB

 

[biandinfosec]

I am giving another try with this expanded information. I was careful to make sure the "repeat group header on each page" was set, and reaffirm several times that its checked.

will post an update if the solution has solved the problem.

Thanks LB

-b

 

[lbass]

Also, make sure you do NOT have "Reserve minimum page footer" checked for the page footer higher order section.

-LB

 

[biandinfosec]

I have spent some time on sub1. Honestly, Header thing does not seems to work. I tried to not suppress anything, and it will only print on fist page. I tried moving the the group_header_1 fields to report header since I only this once on this particular report, and not suppressing anything on group headers. no suppression formula at all, except "repeat group header on each page", still does not work. I am wondering if it has anything to do with my CR environment. If you have tried 2 times on yours and it works, I know what else to do.

Good news, the footer works ok, so far. Only have problem with blank space that are being reserved, this is distorting information on the last report. However I am not worried about the footer section. I plan to tackle the section after the header is completed. Header is the most important part to me right now.

LB do you think this may have something to do with my CR environment. My version information below:
Product 8.0.1.0 CRW32 8.0.0.393

Thanks again.

-b

 

[lbass]

No, I use 8.0 also. Please verify that you are using a formula like:

whilereadingrecords;
1

...to insert a group on, and that this is your highest order group, and that when you set it up you checked "repeat group header on each page". Then verify that all page headers are appearing in the subreports on every page before adding the conditional suppression criteria.

-LB

 

[biandinfosec]

lbass. I have implemented the solution you suggested with mior modification and that seem to work ok, right now. I will post the modification after the my project deadline on thursday. I think this should be writen as an FAQ.

However, there is one more problem that I just run into right now. One of the reports that I have just combined has 3 subreports in it, and its difficult to just adjust the sql to make it work. I am considering using a UNION for the 3 subreport SQL's to the main report - SR2 in this case.

Is there any other suggestion you can give me on how to link these 3 subreports to SR2? I know that crystal does not support subreport within a subreport. What can I do? Very pressed on time right , only until morrow to turn this in. Please chime in if you have solution suggestion. Below is the 3 sql for the 3 subreports and how they are currently joined to SR2 before SR becoming a Sub report. Following thread.

 

[biandinfosec]

Main Report SQL
select RepHeader.CUSTOMER_ID,
RepHeader.TELCO_ID,
RepHeader.REP_NAME,
RepHeader.REP_ID,
RepHeader.REP_PHONE,
RepHeader.REP_OFFICE_CODE,
RepHeader.REP_UNIT,
RepHeader.MAINE_IND,
RepHeader.STATUS,
RepHeader.CONTACT,
RepHeader.MARKET_NO,
RepHeader.POC_STREET_ADDRESS,
RepHeader.POC_COMMUNITY,
RepHeader.POC_STATE_ZIP,
RepHeader.CONTACT_NAME,
RepHeader.CONTACT_TN,
RepHeader.CONTACT_TITLE,
RepHeader.CUST_OPEN_DATE_TIME,
RepHeader.BILLING_NAME,
RepHeader.BILLING_COMMUNITY,
RepHeader.BILLING_ATTENTION,
RepHeader.BILLING_STREET_ADDRESS,
RepHeader.BILLING_STATE_ZIP,
RepHeader.BILLING_TN,
RepHeader.QC_ATTENTION,
RepHeader.QC_STREET_ADDRESS,
RepHeader.QC_COMMUNITY,
RepHeader.QC_STATE_ZIP,
RepHeader.MAIN_LISTED_TN,
RepHeader.MAIN_LISTED_NAME,
RepHeader.MAIN_LISTED_COMMUNITY,
RepHeader.MAIN_LISTED_STREET_ADDRESS,
RepHeader.MAIN_LISTED_STATE_ZIP,
RepHeader.SOHO_IND,
RepHeader.OMIT_ADDRESS_IND,
RepCntrctProdBill.DIRECTORY_CODE,
RepCntrctProdBill.DIRECTORY_ISSUE_NUM,
RepCntrctProdBill.ACCOUNT_ID,
RepCntrctProdBill.DIRECTORY_NAME,
RepCntrctProdBill.DIRECTORY_ISSUE_DATE,
RepCntrctProdBill.STATE_ABBREVIATION,
RepCntrctProdBill.BILLING_BASIS,
RepCntrctProdBill.REP_ID as C_ReP_ID,
RepCntrctProdBill.UNIT_ID,
RepCntrctProdBill.DIV_ID,
RepCntrctProdBill.CURR_REP_ID,
RepCntrctProdBill.CURR_REP_UNIT,
RepCntrctProdBill.CURR_REP_OFFICE_CODE,
RepCntrctProdBill.RETIREMENT_STATUS,
RepCntrctItem.DIRECTORY_CODE as I_DIRECTORY_CODE,
RepCntrctItem.DIRECTORY_ISSUE_NUMBER,
RepCntrctItem.ITEM_ID,
RepCntrctItem.ACCOUNT_ID as I_ACCOUNT_ID,
RepCntrctItem.BILLING_SUFFIX,
RepCntrctItem.HEADING_NAME,
RepCntrctItem.MAIN_ITEM_ID,
RepCntrctItem.UDAC_CODE,
RepCntrctItem.ITEM_ACTION_CODE,
RepCntrctItem.ITEM_PI_AMT,
RepCntrctItem.ITEM_NISD_AMT,
RepCntrctItem.TEXT_POINTER,
RepCntrctItem.TRADE_CAPTION_LEVEL_1,
RepCntrctItem.TRADE_CAPTION_LEVEL_2,
RepCntrctItem.TRADE_CAPTION_LEVEL_3,
RepCntrctItem.COOP_NISD_AMT,
RepCntrctItem.RELATED_TYPE,
RepCntrctItem.brand_name,
RepCntrctItem.SECTION_CODE,
RepCntrctItem.SPANISH_HEADING_NAME,
RepCntrctItem.CO_OP_NI_STATUS,
RepCntrctItem.FORCE_PRICE_IND,
RepCntrctItem.DISCOUNT_CODE,
RepCntrctItem.LANGUAGE_TYPE,
RepCntrctItem.HEADING_CODE,
RepCntrctItem.SUB1_HEADING_NAME,
RepCntrctItem.SUB2_HEADING_NAME,
RepCntrctItem.CPY_SHT_STS,
RepCntrctItem.DISC_NAME,
RepCntrctItem.IN_OUT_DATE,
RepCntrctItem.COOP_PLAN_NAME,
RepCntrctItem.OPEN_DATE,
RepCntrctItem.BARTERED_SEEDED_IND,
CANVASS.CANVASS_CODE,CANVASS.CANVASS_NAME,
DIRECTORY_SECTION.SECTION_CODE as D_SECTION_CODE,
DIRECTORY_SECTION.SECTION_FULL_NAME,
DIRECTORY_ISSUE.DIRECTORY_CODE as DI_DIRECTORY_CODE,
DIRECTORY_ISSUE.INFO_TEXT_LINE_1,
DIRECTORY_ISSUE.INFO_TEXT_LINE_2,
DIRECTORY_ISSUE.INFO_TEXT_LINE_3,
DIRECTORY_ISSUE.INFO_TEXT_LINE_4,
RepCntrctListing.LISTING_ID,
RepCntrctListing.LISTED_TN,
RepCntrctListing.LISTING_ALI_CODE,
RepCntrctListing.LISTED_NAME,
RepCntrctListing.LNI,
RepCntrctListing.SCOPING_TYPE,
RepCntrctListing.WIRELESS_IND,
UDAC.rate_ind
from
DBA.RepHeader as RepHeader,DBA.RepCntrctProdBill as RepCntrctProdBill left outer join DBA.DBA.RepCntrctItem as RepCntrctItem on RepCntrctProdBill.CUSTOMER_ID = RepCntrctItem.CUSTOMER_ID and RepCntrctProdBill.DIRECTORY_CODE = RepCntrctItem.DIRECTORY_CODE and RepCntrctProdBill.DIRECTORY_ISSUE_NUM = RepCntrctItem.DIRECTORY_ISSUE_NUMBER and RepCntrctProdBill.MAIN_LISTING_ID = RepCntrctItem.MAIN_LISTING_ID and RepCntrctProdBill.ACCOUNT_ID = RepCntrctItem.ACCOUNT_ID,DBA.RepCntrctProdBill as RepCntrctProdBill left outer join DBA.DBA.Canvass as Canvass on RepCntrctProdBill.Canvass_Code = Canvass.Canvass_Code and RepCntrctProdBill.Canvass_Issue_Number = Canvass.Canvass_Issue_Number,DBA.RepCntrctItem as RepCntrctItem left outer join DBA.RepCntrctListing as RepCntrctListing on RepCntrctItem.CUSTOMER_ID = RepCntrctListing.CUSTOMER_ID and RepCntrctItem.LISTING_ID = RepCntrctListing.LISTING_ID,DBA.RepCntrctItem as RepCntrctItem left outer join DBA.UDAC as UDAC on RepCntrctItem.UDAC_CODE = UDAC.UDAC_CODE,DBA.RepCntrctItem as RepCntrctItem left outer join DBA.DIRECTORY_SECTION as DIRECTORY_SECTION on directory_section.effective_date = (select Max(ds.effective_date) from DBA.DIRECTORY_SECTION as ds join DBA.RepCntrctItem as rci on ds.DIRECTORY_CODE = rci.DIRECTORY_CODE and ds.section_code = rci.SECTION_CODE and ds.effective_date <= now(*)) and RepCntrctItem.SECTION_CODE = DIRECTORY_SECTION.section_code and RepCntrctItem.DIRECTORY_CODE = DIRECTORY_SECTION.DIRECTORY_CODE,DBA.RepCntrctProdBill as RepCntrctProdBill left outer join DBA.DIRECTORY_ISSUE as DIRECTORY_ISSUE on RepCntrctProdBill.DIRECTORY_CODE = DIRECTORY_ISSUE.DIRECTORY_CODE and RepCntrctProdBill.DIRECTORY_ISSUE_NUM = DIRECTORY_ISSUE.DIRECTORY_ISSUE_NUMBER,DBA.RepCntrctItem as RepCntrctItem left outer join DBA.DISCOUNT_PERCENTAGE as DISCOUNT_PERCENTAGE on RepCntrctItem.DISCOUNT_CODE = DISCOUNT_PERCENTAGE.DISCOUNT_CODE and RepCntrctItem.DISCOUNT_PHASE = DISCOUNT_PERCENTAGE.NUMBER_OF_PHASES where
RepHeader.CUSTOMER_ID = RepCntrctProdBill.CUSTOMER_ID

the 3 subreports are linked to this main report sql with the following sql
sr1 sql

SELECT
RepCntrctListing."LISTING_ID", RepCntrctListing."LISTED_TN", RepCntrctListing."LISTING_ALI_CODE", RepCntrctListing."LISTED_NAME", RepCntrctListing."LNI",
RepCntrctItem."CUSTOMER_ID"
FROM
{ oj "DBA"."RepCntrctListing" RepCntrctListing LEFT OUTER JOIN "DBA"."RepCntrctItem" RepCntrctItem ON
RepCntrctListing."CUSTOMER_ID" = RepCntrctItem."CUSTOMER_ID" AND
RepCntrctListing."LISTING_ID" = RepCntrctItem."LISTING_ID"}

and is linked to main with the following parameters - none. subreport is in the group footer section

subreport 2 sql

SELECT
MEMO."MEMO_ID", MEMO."CUSTOMER_ID", MEMO."MEMO_OPEN_REASON", MEMO."LISTING_ID", MEMO."PRODUCT_CODE", MEMO."MEMO_NOTES"
FROM
"DBA"."MEMO" MEMO left outer join "DBA"."Product_billing_ATN" p2 on MEMO.product_code = p2.product_code and memo.listing_id = p2.atn_listing_id and memo.version = p2.version
WHERE
MEMO.MEMO_OPEN_REASON = 26 and MEMO.VERSION=1

sr2 is link with the following parameters = {MEMO.LISTING_ID} = {?Pm-RepCntrctListing.LISTING_ID}
and
{MEMO.PRODUCT_CODE} = {?Pm-DIRECTORY_ISSUE.DIRECTORY_CODE}
. It is in the group footer section

sub report 3 sql and how its linked to main report

select RepCntrctText."TEXT_DATA" as TEXT_DATA from
dba.RepCntrctText RepCntrctText left outer join dba.RepCntrctItem repcntrctitem1 on
RepCntrctText.text_pointer=RepCntrctItem1.TEXT_POINTER
AND RepCntrctText.item_id=RepCntrctItem1.ITEM_ID
AND RepCntrctText.directory_CODE=RepCntrctItem1.DIRECTORY_CODE
AND RepCntrctText.directory_ISSUE_NUMber=RepCntrctItem1.DIRECTORY_ISSUE_NUMBER

This sr3 is linked to the main with the following parameter = {RepCntrctText.TEXT_POINTER} = {?Pm-RepCntrctItem.TEXT_POINTER}
and
{RepCntrctText.ITEM_ID} = {?Pm-RepCntrctItem.ITEM_ID}
and
{RepCntrctText.DIRECTORY_ISSUE_NUMBER} = {?Pm-RepCntrctItem.DIRECTORY_ISSUE_NUMBER}
and
{RepCntrctText.DIRECTORY_CODE} = {?Pm-RepCntrctItem.DIRECTORY_CODE}
and its in the details section.

Requirement: Remove the 3 subreports from the main, and merge the 3 subreports SQL's to the main sql.

Thank you all for your help.

 

[lbass]

These do not appear to me to be good candidates for a union statement. Try adding the tables from the subreports to the main report, linking based on the same fields that were used to link the subreport to the main, but using left joins. Then use conditional suppression to suppress unwanted records.

-LB

 

[biandinfosec]

That is what I tried doing initially, but the repeatition is so much.

Do you know NVL oracle function equivalent in Sybase? i have searched everywhere for something similar in sybase without success.

Maybe I can capture these information in an array and pass the values to the report. Remember, the report that we have been discussing here. Its got main and the 3 sub. So I am thinking perhaps, these 3 sub reports on the main report as well, then capture their results in a multi array to pass to the appropriate subreport sections, in this case SR2 is the report that has these additional 3 subreports.

Any thoughts on that? Thanks.

 

[lbass]

Well, yes, you could potentially add these 3 subreports directly to the main report, and then use shared variables to pass data to SR2. Using one of the subreports, can you explain how the info would be displayed in SR2? Please show some sample data, indicating where the subreport value would appear.

-LB

 

[biandinfosec]

The 3 subreport information should will be populated in SR2 only. sub report 3 sql pull data is populated as part of details section in SR2. The problem is that the pulled data MUST appear as one block on information. Example:

DA - Paving Equipment
DB - {All of DB section info currently comes from third subreport} Data is not always available, if null, DB is suppressed. These values are Text_data from the SQL above.
Line 1 text
Line 2 text
Line 3 Text
""
""
Line n text

GF#2a - Data from subreport - sr1 sql above (block)
Line 1 text
Line 2 text
Line 3 Text
""
""
Line n text

GF#2d - data from subreport - sr2 sql above (block also)
Line 1 text
Line 2 text
Line 3 Text
""
""
Line n text

All the information must appear in one block as indicated above.

I could put these subreports on the main report, I wondering the best way to capture these information and make them appear in block. i guess a lot of shared variable would need to be writen, and arranged at the appropriate sections, suppressed when null.

let me know if you have idea for me. thanks lbass and others following up on this thread.

 

[lbass]

I don't think I can offer much help with the shared array approach, as it would be difficult to do in the abstract and without a lot more information. You could share the info in blocks by accumulating the records in the subreport and adding a return after the end of each line.

-LB

 

[biandinfosec]

lbass and others, I have to change my strategy at the last minute due to the problem of being able to get those other subreport information to work. And I have a deadline of EOB day today.

What I have decided now to do is to use SR2 from my original post as the main report, and embed SR1 and SR3 into it as subreport. In this way I will be able to maintain the functionalities of those 4 sub reports in SR2.

The only challenge now is getting the SR1 to integrate well with SR. The reason is because SR1 must always print on top or SR2 or SR3. For this reason, I have to include SR1 in Report Header (RH)of SR2. Make the same changes for headers and all that.

I have never used subreports in RH before. So I am not sure how the whole thing will integrate. Right away I can see the problem of blank pages, and suppressing them. I am going to try to use the solutions that we had discuss earlier.

But generally, is there a way that I can put this subreport somewhere else and still have it printed as first part of the report?