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:
Then, in my main report, in the Section Expert/Details/Suppress code behine, I put the following code:
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
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};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) > 0This 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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
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
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
ASKER
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.
But I will need to test (tomorrow) if they are returning a null. That may be the problem.
ASKER
It wasn't the problem, but this may be a dead end I'm heading down.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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).
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).
ASKER
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.
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
Select the subreport
CLick FILE --> SAVE SUBREPORT AS
The added subreport in the detail section only needs to determine if there are records.
mlmcc
ASKER
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
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
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
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
ASKER
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:
Open in new window