Solved

Crystal 11 Suppress main report section if subreport is blank

Posted on 2016-11-18
24
53 Views
Last Modified: 2016-11-28
I Have a main report with 2 groups and 1 detail section.  The subreport is in the detail section as each record on the main report might/might not have a matching subreport record.  The records I need to see are all those on the main report with a matching record in the subreport.  For example the main report is Stock in main warehouse and the sub report is Stock in bulk warehouse.  I want to see where the stock is low in main warehouse but have stock in bulk.  The link between the main and subreport is SKU ID.
It may be of use to know that if there is no stock held in the bulk warehouse the subreport will be blank so ultimately the report will show me a list of stock that needs replenishing from Bulk Warehouse.

Any assistance on this would be greatly appreciated :-)

Many thanks in advance,

AJ
0
Comment
Question by:Andrew Jay
  • 9
  • 7
  • 7
24 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 41892902
Are you using the subreport to display any other information?

What do you need suppressed based on the subreport?

You can't suppress any sections that are before the subreport or the section the subreport is in based on the subreport return.  The subreport is the last object evaluated/rendered in the section.

DO you need the group headers suppressed?

WHy are you using a subreport?

mlmcc
0
 

Author Comment

by:Andrew Jay
ID: 41892936
Hi MLMCC,

In response:
Are you using the subreport to display any other information?  The subreport confirms the SKU then gives the Qty and Bulk location code where the stock is located in Bulk

What do you need suppressed based on the subreport? I need the detail line in the main report to be surpressed if there is no info detected in subreport as I'm building a Stock Replenishment Report to get emailed to Goods In.

You can't suppress any sections that are before the subreport or the section the subreport is in based on the subreport return.  The subreport is the last object evaluated/rendered in the section. I could insert another detail line and enter the subreport into this section.

DO you need the group headers suppressed?  Its the detail line mainly so that all that is reported on is the stock where it is low/zero in Main warehouse but held in bulk warehouse

WHy are you using a subreport?  We have a rather complex database and each location has a pick face definition where we state the Min/Max of a product/Sku.  Stock info is held in a seperate tables that dont like to be linked so inserting a subreport gets the info we need, I just need to get rid of the detail lines where no stock is in Bulk as currently I have 40 odd pages and would like to reduce this so when Goods in have the finished article all they see is what they need to action.

Thank you for your response, I feel I'm not that far away but cant put my finger  on it, i have included a screen shot.Untitled.jpg
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 300 total points
ID: 41893361
Thanks for the information.

The way to do this is to add a second detail section to the main report.
Move all the main report information to the second detail section
Put the subreport in details A
You can use a shared variable to determine how many records were in the subreport

Add a formula to the main report header
WhilePrintingRecords;
Shared NumberVar subRecordCount;
''

Open in new window


Add a formula to the subreport
WhilePrintingRecords;
Shared NumberVar subRecordCount;
subRecordCount := Count({Some NON NULL Field});
''

Open in new window


If the subreport doesn't display anything when the count is 0 you can format the section to suppress a blan section
The subreport can be formatted to suppress when no records (format subreport - 4th tab)
For details B
    Suppress with the formula
WhilePrintingRecords;
Shared NumberVar subRecordCount;
subRecordCount = 0

Open in new window


mlmcc
0
 

Author Comment

by:Andrew Jay
ID: 41895755
Hi mlmcc,

Thank you for your solution, I appreciate your help as I've reached a point where I understand what I'm doing as you have suggested but the report still isn't suppressing as indicated therefore I must have missed something.  

Just to recap:
a) I have entered a second detail section, moved the main report details into details b and moved the subreport into details a.
b) I have created a formula in Main report titled SubReportCount, do i insert this into the main report header or on detail line?
Untitled.jpgc) I have then created a formula within the sub report titled SubReportCount, where if anywhere is this entered in the subreport?
Untitled1.jpgd) When I check the formula's in both reports they are null so I think its here that it falls down.  Should either of the formula's contain values?
e) Also just to add, when I attempt to suppress the blank section, do I right click on the subreport from the main report and 'format subreport' using the forth tab, there isnt anywhere to enter the suppress formula?
Untitled2.jpg
I'm certain this will work, I just need to understand where I'm going wrong :0-)

This is what the report looks like with 2 detail lines, the formulas have been created in both main and sub reports and the next step is to insert the suppress formula:
Untitled3.jpg
Many, many thanks in advance,

AJ
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 41895948
>>b) I have created a formula in Main report titled SubReportCount, do i insert this into the main report header or on detail line?
Put in the main report Report Header

>>c) I have then created a formula within the sub report titled SubReportCount, where if anywhere is this entered in the subreport?
The formula in the subreport goes where you are setting the count.

>>d) When I check the formula's in both reports they are null so I think its here that it falls down.  Should either of the formula's contain values?
The formula in the main report won't show a value because the formula returns a blank

>>e) Also just to add, when I attempt to suppress the blank section, do I right click on the subreport from the main report and 'format subreport' using the forth tab, there isnt anywhere to enter the suppress formula?
The blank section is suppressed in the Section Expert

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 41896370
It looks like you're getting close to a solution, but there may be one issue with the count.  In my experience, if a subreport doesn't read any records (the record selection formula excludes everything), then the formulas in the subreport aren't evaluated, even if they're in the subreport report header.  So, if there is no stock in the bulk warehouse, your count variable may not get set in the subreport (leaving it with the count for the last SKU ID in the main report that had something in the bulk warehouse).

 If that's an issue, you can handle it by adding a new main report detail section above the section that contains the subreport, and setting the variable to 0 there.  That would just reset the count to 0 for each new item, and then if the subreport finds some records, it will update the count.

 Just create a formula like the following in the main report, and put it in the new detail section (above the subreport section):

WhilePrintingRecords;
Shared NumberVar subRecordCount;
subRecordCount := 0;
''

 mlmcc sort of covered this, but, in case it still isn't clear, the quotes at the end are just so the formula doesn't produce any visible output on the report.  You can suppress the new detail section, or set it to "suppress when blank".

 James
0
 

Author Comment

by:Andrew Jay
ID: 41897146
Thanks James for your input, I have followed both mlmcc and your advice (Shared Variables are something new for me after all these years) to the letter and in some respects the report is functioning however i think there is a slight  error somewhere as some records are showing even though the subreport is blank.

Below is a shot of the first record the report has detected where stock is in the bulk warehouse, there are lots of records before and after that in theory i shouldn't be seeing.  This then continues right to the end and the report is 30 pages long.

Untitled3.jpg
My aim is to schedule these to be delivered to Goods In every 4 hours so that it saves time using the client replenishment report which takes time to populate. My end vision is for the report to only show the record in the main report when it detects stock in bulk sub report, so they will get a email containing a list with mainly red records and be limited to a few pages of action by Goods In.

I really do appreciate all comments as I always enjoy finding and learning something new in Crystal  :-)

I have shown below how the design of the report looks, I believe I have set it up correctly but if you can spot anything from this do let me know :-)

Untitled6.jpg
0
 
LVL 34

Expert Comment

by:James0628
ID: 41897639
It would probably help if you could post the report (.RPT) file.  There's only so much that we can tell from screenshots.

 James
0
 

Author Comment

by:Andrew Jay
ID: 41897696
Hi James,

If you pick up on anything I may have missed, please could you let me know so I can understand where I went wrong.  Thank you for all the input received so far, its greatly appreciated :-)  

I've requested to the powers that be that i would probably benefit from more training.  I went on a beginner course 2004, self taught the rest with some help from forums :-)  If you could advise as to any worthwhile training or literature that would be the icing on the cake :-)

Many thanks,

AJ
Pick-Face-definition-Report-2017edit.rpt
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 41897767
I think you will find that most of us had limited initial training and learned most of it through this site or other avenues on the web.
I had no formal training.

I do recommend a book by George Peck - The Complete Crystal Reports xx Reference

The SAP/Crystal site has an excellent knowledge base though it is a bit difficult to use and search.

mlmcc
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 200 total points
ID: 41897944
I see 2 problems in the report.

 1) You want to suppress Details C when there are no records in the subreport, correct?  You didn't enter a suppression formula for Details C, as mlmcc described earlier.  Right-click on the label for Details C, select Section Expert, click on the formula (X+2) button beside Suppress, and enter the following formula (which mlmcc posted earlier):

WhilePrintingRecords;
Shared NumberVar subRecordCount;
subRecordCount = 0

 That seems to eliminate a lot of the details.

 2) After I make that change, I still see what I assume are extra details after a subreport that has records.  I think that's the problem that I described, where the count is not updated when there are no records in the subreport.  You put the subRecordCount formula in Details A, but that formula just declares the variable.  It doesn't set it to 0.

 Change subRecordCount to:

WhilePrintingRecords;
Shared NumberVar subRecordCount;
subRecordCount := 0;
''

 Note that this formula uses := 0, which sets subRecordCount to 0, while the previous formula uses =, which compares subRecordCount to 0 (people have gotten := and = confused before).

 I think that change will get rid of the extra details that I was seeing, but I wasn't able to test that, because when I made that change, CR wanted to connect to your db, which, of course, it couldn't, so I couldn't see the results.


 Also, the subRecordCount formula should also be in the report header in the main report (as mlmcc mentioned earlier), just to declare the variable at the beginning of the report.  Honestly, that doesn't really seem to be necessary with shared variables, but it won't do any harm, so might as well play it safe.  So, that formula should be in both the report header and in Details A.


 FWIW, like mlmcc, I had no formal training.  I was shown some of the basics and given reports to modify/create, and went from there.  I picked up a lot from this site over the years, just reading other people's questions, and trying to help out when I could.

 James
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Andrew Jay
ID: 41898734
Thank you both James and mlmcc for your contribution, James' final response has hit the nail right on the head and has certainly give me a positive start to the morning.  It seems the general theme with crystal in that people just learn as they go.  

I like it when I find something new within Crystal and your help has certainly done that, I think I'll be ordering me a new book real soon!

Thank you once again and if you gain any satisfaction in helping solve crystal problems then I hope your currently feeling that in abundance :-)

[embed=file 1129317]

Thanks, thanks, thanks!!!
Untitled.jpg
0
 
LVL 34

Expert Comment

by:James0628
ID: 41899427
mlmcc should have gotten most of the points.  He gave you most of the solution in his second post.  I just added the part about resetting the count to 0 before the subreport, and pointed out one part of mlmcc's solution that you were missing.

 You can ask to have the question reopened, and then split the points between us.

 James
0
 

Author Comment

by:Andrew Jay
ID: 41899441
Sorry, the whole points thing is new to me although I agree in that mlmcc's initial response did indeed form most of the solution and can only apologize I didn't do exactly what you suggested.  I'll see if I can get the question re-opened and get the points split :-)  Just out of curiosity, what are the points for?

Many thanks James & mlmcc :-)
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 41899496
Points are relatively meaningless in reality.  They are the way we get "rewarded" for helping you with  a question.  Points get translated into a level number.  You can see the number under our Avatar.  The higher the number the more points we have earned.

To reopen the question click the REQUEST ATTENTIOIN link in the question body.  It is under to 3 dot menu

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 41899623
I guess the points can be used when you ask a question.  You get points for answering questions, which you can then use when asking questions of your own.  I think.  And, as I recall, if you earned more than X number of points per month, you could ask an unlimited number of questions or something like that.  I'm not really sure, since I never ask questions here.  :-)  And, even if I'm right, the site may not work like that anymore.  FWIW, the level number thing is a relatively recent addition.

 For me, it's mostly just about giving credit where it's due.  But another factor is thinking of the site as a resource.  If someone with a similar problem searches the site and finds this question, you'd want to have the correct posts marked as the solution.

 James
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 41900260
James - the Q&A part of experts exchange is now completely free.  There are some parts that require either a paid account or earning 10000 points per month.

mlmcc
0
 

Author Comment

by:Andrew Jay
ID: 41900408
Hi,

Does anyone know how to choose 2 posts as best solution?
0
 
LVL 34

Expert Comment

by:James0628
ID: 41900716
mlmcc,

 Thanks.  I got the impression that things had changed, but wasn't sure.


 Andrew,

 I'm sure that I've seen it mentioned, but I can't remember the details.  mlmcc will know, and I'm sure that he'll give you an answer when he gets back to this question.  (I know that this isn't all that helpful, but I didn't want you to think that I was ignoring you :-)

 James
0
 

Author Comment

by:Andrew Jay
ID: 41904018
Is there anyway of distributing the points evenly if there were 2 solutions that contributed to the end result as that was the case.  I could assign the best points to mlmcc and assisted points to James but I'd rather share the points if thats possible ?

Many thanks,

AJ
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 41904217
When you select the comments the points will be split evenly among all the comments.  You can adjust if you feel one of the comments deserves more credit than another

mlmcc
0
 

Author Closing Comment

by:Andrew Jay
ID: 41904231
Thank you everyone for your help, book ordered, experience gained and happy that I now have a Replenishment Report :-)  Thank you, thank you and thank you once again :-)
0
 
LVL 34

Expert Comment

by:James0628
ID: 41904903
You're welcome.  Glad I could help.  And thanks for taking the time to reclose the question.  Not everyone does when something like that comes up.

 James
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now