Records Based on Value of a Subreport

Using Crystal XI

I have a report which has two subreports on the detail line.  I need the detail line to be ignored when the values in the subreports are zero.

To that end, I created shared variables in each subreport as such:
WhilePrintingRecords;
Shared numberVar Invoiced_Total := {TABLE2.Amount} + {TABLE2.Adjustment};

Open in new window

The second subreport has a similar shared vairable, but named "GL_Total"

Then, in my main report, in the Section Expert/Details/Suppress code behine, I put the following code:
whileprintingrecords;
Shared numberVar GL_Total;
Shared numberVar Invoiced_Total;
(GL_Total + Invoiced_Total) > 0

Open in new window


This does not supress any lines.

Furthermore, while testing to make sure values were coming through, I created a test formula that I placed on the detail line showing the results.  The results were there, but on the line below where they were supposed to be.

Finally, and I'm fairly certain I'll have to do it the hard way, even if the detail line get's suppressed, it won't affect calculations such as counting the number of rows (suppressed rows are still counted).

So, my main question is, what am I doing wrong in trying to supress a line based on the value(s) of subreports within that line.

A second question, if you care to tackle it, is how can one not just suppress a line, but ignore it completely.  (An answer to this question will garner a second question post with another 500 points.)

TIA
LVL 10
ClifAsked:
Who is Participating?
 
mlmccCommented:
In my experience, subreports seem to be evaluated after the suppression formula so the values of the shared variables havent been calculated.

I assume this is the detail section
Insert a second detail section
Put the subreports in details A
Make sure they don't display anything and you have the SUPPRESS BLANK SUBREPORT option set
You can the use the SUPPRESS BLANK SECTION option in the section expert to suppress it
Details B would get all the remaining fields from the current details
You should then be able to suppress as you ahve it.

One other thing,
Add a formula to the report header that declares the shared variables
whileprintingrecords;
Shared numberVar GL_Total;
Shared numberVar Invoiced_Total;
""

mlmcc
0
 
ClifAuthor Commented:
Oops,
Oops, the code I posted above in the Section Expert/Details/Suppress code behind was wrong:

This is the actual code behind I'm using:
whileprintingrecords;
Shared numberVar GL_Total;
Shared numberVar Invoiced_Total;
(GL_Total + Invoiced_Total) = 0

Open in new window

0
 
ClifAuthor Commented:
Yes, this is in the detail section.

The subreport is supposed top display results.  I need to hide the line if there are no results to display (and show the line when there is data in the subreport).  If I follow your recpommendation, I would need to create four subreports instead of just two.  The original two to display the results when they are there and two more to put in the details A.

Just out of curiosity, why do I need a separate formula?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mlmccCommented:
If there is no data do the subreports display something?

Is the a drawing line as a separator?
You could just put it in the second section and suppress as necessary

Crystal likes to have the shared variables declared in the main report before they are used in the subreport.  I am not sure why that is but it seems to be the way to do it.

mlmcc
0
 
ClifAuthor Commented:
No, they don't display of there is no values.

But I will need to test (tomorrow) if they are returning a null.  That may be the problem.
0
 
ClifAuthor Commented:
It wasn't the problem, but this may be a dead end I'm heading down.
0
 
James0628Commented:
As mlmcc said, CR seems to evaluate the suppression formula for a section before it runs any subreports in that section.  I don't know if this was the intent, but it is potentially more efficient that way.  A subreport can add a lot to the work required to generate a report, for example if it's reading a lot of records, or being executed frequently (eg. in a detail section).  The section suppression formula is evaluated first and if the section is suppressed, the subreport is not executed, potentially making the report run that much faster.

 If the _only_ data on those lines comes from the subreports, you could set up the subreports so that they don't produce any output at all when there are no records or whatever, and then use the "Suppress blank subreport" option in the format for those subreports, and the "Suppress when blank" option for the section.

 If the lines have a combination of data from the main report and the output from the subreports, there are a few options:

 1) Put copies of the subreports in a section above the current section and have those just set your variables, but produce no output.  Then use the variables to suppress the section below that, which has your original subreports.

 2) If the subreports aren't producing too many values in a line (eg. just a few fields), you could move the subreports to a section above the current section and have them save those values in variables and not produce any output, then use those variables to output the values in the section below that in the main report.  The difference from #1 is that you only have one copy of each subreport.

 3) Basically, the opposite of #2.  If the section of the main report isn't producing too many values, you could pass those values to the subreports and have them output the values along with the data that they're reading.  Then the subreports could suppress those fields if necessary.  You could use shared variables to pass the values from the main report to the subreports, but I'd use parameters.  Create a parameter for each value in each subreport and link the fields in the main report to the corresponding subreport parameters.


 As for your question, "how can one not just suppress a line, but ignore it completely", the only way to really do that in CR is with record selection, which isn't an option if the selection is based on values from a subreport.  The most efficient way to do this would be to write a manual query that duplicates the tests done in the subreports and uses that to restrict the results of the query.

 James
0
 
ClifAuthor Commented:
I have a few too many fields in the main report to be able to use option 3.

I initially tried option 1, but it would appear you can't Copy/Paste subreports, so I would have to completely recreate them.

I tried #2, as it was the most reasonable (read: easiest).    It does work, however there is the problem of the section above (Detail a) where the subreport is.  If I suppress it, it seems to turn off the subreports and I get no data at all (Detail b gets suppressed for every row).
0
 
ClifAuthor Commented:
An issue has cropped up with the option I chose.  There are grouping levels and I need subtotals per group.  I can't summarize the fields that I'm copying out of the subreport.
0
 
mlmccCommented:
You can SAVE a subreport to an rpt file then import it into a new report.
Select the subreport
CLick FILE --> SAVE SUBREPORT AS

The added subreport in the detail section only needs to determine if there are records.

mlmcc

0
 
ClifAuthor Commented:
I missed the comment by mlmcc to check "Suppress when blank".  That solved that issue.

Since it took both of you (mlmcc and James0628) to get me to understand I awarded points to both.

Now onto my other problem with this.  Subtotalling based on a group.  I'll open another question, though.  

Thanks
0
 
James0628Commented:
You're welcome.

 FWIW ...

 As you found, you can't copy a subreport, but just to confirm what mlmcc said, you can save it as a separate report, and then import that to create a second copy of the subreport.

 As for suppressing a subreport, I know that you've got it working, but just to explain ...

 It's a bit tricky.  You can't just suppress the section, because then the subreport won't be run.  So, you have to:
 1) Have the subreport produce no output.
 2) Use the "Suppress Blank Subreport" option in the subreport format to suppress the subreport when it doesn't produce any output.
 3) Use the "Suppress Blank Section" option in the section format for the section that the subreport is in.

 That way, the subreport is run, but produces no output, so the subreport is suppressed, so the section is blank, so the section is suppressed.  But the subreport is run first.

 James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.