cancel
Showing results for 
Search instead for 
Did you mean: 

How to show group in Main rpt according to sub report group

Former Member
0 Kudos

Dear all,

I have created two sub reports which calculates different total jobs, I then added both of them into my main report by using a shared var formula.

The total of both reports is working fine however all the Reason Codes are being shown instead of showing only those which are matched with both sub reports (report is Grouped by Reason Code in all three reports Main-Subreport1-sub report 2).

For example in the main report it says

Reason Code:

1

2

3

4

5

6

7......

Where as in sub report the group reason code might be only 1,4 and 7.

What I am trying to do is to show only specific reason codes in the main report which are given in both sub reports.

Is it possible to select (or use a formula) which selects only exact match reason codes and displays it?

Please note: The main report has only Reason Code table and does not have any other filtration. All the filtration for reporting happens at the sub report level.

Many thanks

Regards

Jehanzeb

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

This has been sorted out, I asked my db admin to create a table for me so that I don't have to keep on using Sub reports. He did and it has been sorted.

Former Member
0 Kudos

So it sounds like your shared vars in your subreports return the correct total on the main report.

Now, in order to get the subreport totals to match up with the main report lines, you have to include some conditional logic to check what line you're on before displaying the subreport number. In order to do that, you have to have some indication of what Reason Code matches the number in your subreport. There are many ways you could handle this, including a second array that keeps track of what Reason Code matches what total from the subreport.

Another (may be simpler) approach would be to add another shared string variable in your subreport that captures the total as well as Reason Code in one field, which you later parse on the main report. For example:

shared stringvar ReasonCode_Total;
ReasonCode_Total := {ReasonCodeField} & ":" & totext({TotalField});

In the example above, I pass in the ReasonCode and the total into the sharedVariable separated with a colon (:).

In the main report, you'd create a formula that would first check to make sure the Reason Code matched the row before printing the value...

if {ReasonCode} = left(ReasonCode_Total, instr(":") - 1) then ....

Hope that helps...

Former Member
0 Kudos

Will that is a great answer, however how would it display records accordingly into my main report which is grouped by Reason Code?

I can understand upto sub report level that it will capture only those codes which are relevant in both sub reports however how would it show both in the main report?

Can't I use some sort of condition on the group level instead of creating another shared var in my 2 sub reports.

Many thanks

Regards

Jehanzeb

Former Member
0 Kudos

Got it... Let me see if I understand this better.... You have a group on the main report and in the group header (or footer,) you have two sections holding the subreports in each - say SubReport 1 is in GH1a and SubReport 2 is in GH1b.

If that's the case, create a blank section above GH1a - so you end up with SubReport 2 in GH1c. Place a formula in the new GH1a that reads...


whileprintingrecords;
shared numbervar ReasonCodeMain := tonumber({Group on ReasonCode});

In each of the subreports, conditionally suppress the lines that don't correspond to the value in your ReasonCodeMain variable...

if {ReasonCodeField} <> ReasonCodeMain then true (for Suppress)

Former Member
0 Kudos

Yes! you got it half way, the sub reports are filtered properly because I have given conditions under report section formula.

The main report looks like this

[Main Report Code version|http://www.experts-exchange.com/images/t61651/MainReport.JPG]

Main Report Looks like this on the view

[Main Report in viewer format|http://www.experts-exchange.com/images/t61676/emptyspaces.JPG]

What I want to do is to get rid of all the reason code which are not matched with both sub reports.

Like this in the example The code range from Reason Code 2 to 15 does not have any records in both sub reports, so in theory those records should not be shown in the main report.

It should go from 1 to 16 to 25 and so on, where match show the reason code and data.

Hope this makes sense now.

Regards

Jehanzeb

Edited by: Jehanzeb Navid on Sep 22, 2008 5:33 PM

Oh and the main report should match up with Sub reports not vice versa.

Former Member
0 Kudos

Presuming I understand your situation any better....

This is not the best option from a performance stand-point - and given your situation, I would be using a SQL statement (view/stored proc/ sql command) to create this report instead. Have you considered that? In my experience when I start contemplating using subreports in unusual ways on a report, I go back and see if I can push everything back to the DB to make like easier. In this case, an equal join between the tables giving you the data in Subreports 1 & 2 with the main report data should suffice...

That being said, try the following:

Create two Report Header Sections (RHa & RHb). Place copies of the subreports in these section, one per section. Format this sections to 'Underlay Following Sections' using the Section Expert. Make sure you suppress all fields in both subreports. You will also want to remove the shared variables you had before so you don't mess up the rest of the logic in the Group Headers below. Now, create a formula with the following code:

whileprintingrecords;
shared stringvar array ReasonCodeList;
global numbervar iCounter

redim ReasonCodeList [1]; // resize array

if ({TotalField}> 0 or not isnull({TotalField})) and
{ReasonCode} not in ReasonCodeList then (
iCounter := iCounter + 1;
ReasonCodeList [iCounter] := {ReasonCode} )

You may have to edit whatever syntax errors crop up - I'm simply typing in this forum post page without having validated. The code above simply captures every single ReasonCode from the subreports that have a Total value and adds them to an array of ReasonCodes.

Finally on the main report, on the section Group Header section, conditionally suppress GH when the ReasonCode on the main report does not exist in the array ReasonCodeList

whileprintingrecords;
shared stringvar array ReasonCodeList;
if {ReasonCode} in ReasonCodeList then false else true // conditional suppress

Former Member
0 Kudos

Morning Will,

I totally agree with you that I should be using SQL command to do this however I am not sure how to do this within Crystal.

I know how to do it in Informix (our database).

I think I still don't understand where to put your formula given above (main report or sub)? Secondly there are two sub reports which {@Total_field} you want me to add?

Additionally,

I have opened a blank report, went into Database Expert and then clicked on Command and created two new commands.

One from one database and the other one from another database, and both have Reason Code in common i.e.

Lab_Credits.Reason_Code | Order_header.Reason_Code

Now I am wondering how would I be able to use Lab_Credits.Credit_date and Order_header.date_entered within these two commands because the credit date and date entered actually filter the records.

There are few questions which are in my mind and I would really appreciate if you could help me understand them

1: I have added date field into the command but not sure how that will help me filter the records in the main report?

2: How can I make one single command from two databases, so that it can only look at one table instead of looking two?

Many thanks for your help so far and I really appreciate it.

Regards

Jehanzeb