Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Records Based on Value of a Subreport

Posted on 2011-05-03
12
Medium Priority
?
534 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:Clif
  • 7
  • 3
  • 2
12 Comments
 
LVL 10

Author Comment

by:Clif
ID: 35513823
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
 
LVL 101

Accepted Solution

by:
mlmcc earned 1000 total points
ID: 35513919
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
 
LVL 10

Author Comment

by:Clif
ID: 35514682
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 
LVL 101

Expert Comment

by:mlmcc
ID: 35515095
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
 
LVL 10

Author Comment

by:Clif
ID: 35516944
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
 
LVL 10

Author Comment

by:Clif
ID: 35689877
It wasn't the problem, but this may be a dead end I'm heading down.
0
 
LVL 35

Assisted Solution

by:James0628
James0628 earned 1000 total points
ID: 35690273
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
 
LVL 10

Author Comment

by:Clif
ID: 35691005
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
 
LVL 10

Author Comment

by:Clif
ID: 35691148
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 35691242
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
 
LVL 10

Author Closing Comment

by:Clif
ID: 35692874
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
 
LVL 35

Expert Comment

by:James0628
ID: 35696873
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses
Course of the Month11 days, 4 hours left to enroll

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question