Suppress Sub report header

[sumeet22]

using Crystal Version 10.

Here is the typical scenario:

Main report contains a sub report. Main passes multiple ids to process the sub report.

Sub report uses stored proc to get data from SQL Server database. Stored Proc gets the data in following format from the database for ID 101:

ID FName LName City State Zip
101 Bob Builder New York NY 34567
101 Bob Builder Long Island NY 34567

Now, I need to compare column values among rows to see if anything got changed. In the above case, City name changed from New York to Long Island. My sub report then should display 'City changed from 'New York' to 'Long Island' as follows:

CUSTOMER RECORDS [Note:- this is the sub report header]
City name changed from 'New York' to 'Long Island'
--------------------------------------------------------

It is not over yet, in the next iteration, main report passes id 102 into sub report and the data looks like follows:

ID FName LName City State Zip
102 Sarah Miller Omaha NE 34567
102 Sarah Miller New York NY 34567

This time sub report should display the following:

'City changed from Omaha to New York'
'State changed from NE to NY'
-------------------------------------------------------
Problem is I don't want the header to get displayed again Since it is already being displayed for ID 101.

Also, if nothing changed on both above IDs, the header should be completely suppressed.

Please help, how to do this.

Thanks.
Sum.

 

[Madawc]

You could suppress the unwanted report headers, using a parameter passed to the subreport. Right-click on the detail section and choose Format Section. Then choose the formula icon (x+2 and a pencil) for suppression. Enter a suitable formula.

Or you could pass a parameter to the subreport, that would tell it

Alternatively, go into the subreport and do a 'subreport save'. Then bring it back as a similar but non-identical subreport, which could have a different header. I'm not clear quite what your 'interations' are, but you could have two sections with two different subreports. Suppress one or the other, depending on your controls.

Madawc Williams (East Anglia, UK)

 

[sumeet22]

Thanks for the response, however passing a parameter into sub report will not help. As I mentioned in my previous post, the only thing is passed into sub report is the id from main report. In the sub report, I do the comparison. I have to decide whether or not to suppress the header only after I am done comparing each column items row by row.

Using the same sub report twice is not a option unfortunately, as there are 15 sub reports that need to apply the same logic. If I duplicate all those, the response time will be hit hard.

I am running out of ideas.

Any further suggestions appreciated.
Sum.

 

[Naith]

If nothing changes, what happens to your subreport? It's not clear whether you want help with handling that too, or whether you already have that part tapped.

As for the rest, use a shared variable.

In the report header, insert a formula doing something like:

WhilePrintingRecords;
Shared BooleanVar FirstRun := True;

Then in the footer of the subreport, insert a formula saying:

WhilePrintingRecords;
Shared BooleanVar FirstRun := False;

Your subreport header is conditionally suppressed on

Shared BooleanVar FirstRun = False;

If you do have other issues regarding what you're trying to do, let us know.

Naith

 

[lbass]

I'm not clear on what the problem is. Specifically, if the ID 101 subreport header says:

City name changed from 'New York' to 'Long Island'

And the ID 102 header says:

'City changed from Omaha to New York'
'State changed from NE to NY'

...what is it that you expect to be suppressed and why? The header for 102 is not the same as for 101.

Also not clear whether you need help with how to get these statements in the subreport header.

-LB

 

[sumeet22]

Sorry for the confusion, let me try to be more descriptive.

Main Report:

PH - ABC Company
---------------------------------------------
GH1 [Sub Report 1] - grouped by IDs (101, 102, 103 etc)
---------------------------------------------
D -- suppressed
--------------------------------------------
GF1 -- suppressed
--------------------------------------------

Sub Report: for group id 101

RH - CUSTOMER RECORDS
--------------------------------
Detail section fields
@FName
@LName
@City
@State
@Zip
---------------------------------
RF - suppressed

Formula field @FName has following code:

if {table.FName} <> previous({table.FName}) then
"First Name changed from " + previous(table.FName) + " to " +
table.FName

same logic has been applied to all fields.
Now if none of the fields changed, just suppress the entire row. No problem here. Let's say the entire detail section is suppressed when no changes were found, what about the header section ? CUSTOMER RECORDS ? It still shows up there.

To be even more descriptive the records returned from stroed proc looks like the following:

CustID AddID FName LName City State Zip
101 01 Sarah Miller Omaha NE 34567
101 01 Sarah Miller New York NY 34567
101 04 Bob Builder York NE 34256
101 04 Bob Builder Jersey NE 34256

In the above scenario, the result in the sub report prints out like this:

RH - CUSTOMER RECORDS
--------------------
D - 01. City changed from Omaha to New York
State changed from NE to NY
----------------------------------------
D - 04. City changed from York to Jersey
------------------------

For id 102,103 (and so on): same logic and the process will be applied. However, I don't want the sub report header CUSTOMER RECORDS appear again, as it is already there for id 101.

Did I confuse even more ?

Help !
Sum.

 

[lbass]

Instead of using the subreport report header, you could insert another group section in the main report GH#1_b and place your subreports in that, and then use GH#1_a for the header. You could then go to the section expert->GH#1_a->suppress->x+2 and enter:

groupnumber <> 1

-LB

 

[sumeet22]

Thanks for the response lbass, really appreciate it. I already tried that. It works for only those records that have changed i.e. when the sub report returns the changed values.

Will put it in a format as follows:

Main Report:
----------------------
PH - ABC Company
----------------------
GH1a Customer Records [sub report header as you mentioned]
----------------------
GH1b Sub Report 1 [sub report 1]
----------------------
D - suppressed
----------------------
PF - suppressed

Given the above scenario, if the sub report doesn't return anything, I am suppressing it (which is what it should be). But how about the Sub report header that we just added above the sub report on GH1a section ?. It will still hang in there. I am trying to find a way to hide/suppress that header when the sub report doesn't return anything.

I tried so many things, and think this is probably not possible, is it ?.

Thanks.
Sum.

 

[lbass]

What is the content of the header? I thought it was text that was the same for all subreports. Then it doesn't matter whether the first subreport is suppressed or not, as long as at least one subreport appears.

-LB

 

[sumeet22]

Thanks again for the response. There are more than 10 sub reports embedded into the main report. Each of which has different heading. The one example I gave was CUSTOMER RECORDS. There are other ones like 'PRODUCT RECORDS', 'PAYMENT RECORDS' etc.

Thanks.
Sum

 

[lbass]

I think we need to understand where these other subreports are and how they are linked to the main report. Are they all linked to the ID field? Do you only have one group in the main report? If "Customer Records" is the correct heading for IDs 101,102,103, etc., then where do these other headings fit in? Are they related to different clusters of IDs?

-LB

 

[sumeet22]

Main report uses stored proc that returns data as follows:

Cust_ID Activity_ID
------ --------
101 200
102 200
103 200
104 200

GH1 in main report is grouped by Cust_ID and the id is linked to Sub Reprot 1

GH2 is grouped by Activity_ID and the id is linked to Sub Report 2

At this point I don't even worry about the Sub report 2.

Inside the sub report 1:

Sub report has stored proc as a data srouce that returns data for Customer id 101 as follows:

Order_ID Cust_ID Qty Unit Price
------- ------ --- ---------
1101 101 25 100
1101 101 20 100
1102 101 30 200
1102 101 30 300

Now in the sub report I am doing the comparison among rows and columns.

RH - ORDER DETAILS
------------------
D - 1101 - Qty changed from 25 to 20
D - 1102 - Unit Price changed from 200 to 300
-----------------------------
RF - suppressed

The above was processed for customer id 101

Same thing will be done for Customer id 102, 103, and 104 as well

My problem: Since The sub report header ORDER DETAILS has already appeared after processing Customer id 101, I don't want to show the Sub report header for Customer Id 102, or 103 or 104.

Let's say if sub report 1 didn't have any data for Customer id 101, suppress the whole section. Process Customer id 102, if the data has changed, display the Sub Report header. For Customer ID 103, even if something has changed, do not display the Header as it is already displayed for id 102. Got the idea ?

Any help appreciated.
Thanks.
Sum.

 

[lbass]

I still think my original suggestion would work--again, as long as at least one instance of the subreport has data under that heading. If you tried it, please explain very specifically in what way it didn't work.

-LB

 

[sumeet22]

if the stored proc in sub report doesn't return any data, sub report is suppressed along with its header by checking if isnull({table.id}) then supress. However, if the stored proc in sub report does return data and I compare the columns and rows for any changes, if nothing is found, I have a formula in detail section to suppress those records as follows:

if booleavar changed=false then
suppress=true

the above works to suppress the unchanged data in the detail section in the sub report

But the header still hangs in there. I have no means to tell the header section that 'these records didn't change hence hide/suppress the header.

Sorry if this is too confusing of a problem
Thanks.
Sum.

 

[lbass]

My suggestion was to use a main report group header section, instead of the subreport report header section.

-LB