Crystal Reports Suppressing Sub-Report

Technical Questions

Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions

Topic: Suppressing Sub-Report

Author	Message
ostaro Newbie Joined: 20 Jul 2009 Location: United States Online Status: Offline Posts: 3	Topic: Suppressing Sub-Report Posted: 20 Jul 2009 at 7:45am
	I have a sub report that I would like to suppress if any row in the detail fails. For example, if I have 3 detail records in which the 2nd detail record fails a given test, I would like to suppress the whole sub report. Currently if the first row fails the subsequent detail records are also suppressed, but the sub-report headers print. But, if detail record one is good and two is bad, one prints while 2 and subsequent does not. I am counting the number of fails (variable) and then using that variable to test if the sub report should be suppressed. Currently, i am doing the same for each detail record. i have tried a global boolean var that sets to true if any record fails, but this doesn;t work either. Any ideas?
	IP Logged


DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 20 Jul 2009 at 8:05am
	in the sub report create a formula to make a 1 on fail and 0 on pass. Use the Insert Summary as do a SUM on that formula field. Conditionally suppress all sections when that SUM>0. Suppress the sub report in the main report when blank.
	IP Logged

ostaro Newbie Joined: 20 Jul 2009 Location: United States Online Status: Offline Posts: 3	Posted: 20 Jul 2009 at 12:06pm
	Not sure if that will work or I am not understanding how that would work, but here is a data flow. (Details of a sub-report) Detail rec 1 - MetalType 1 2 3 4 5 6 7 8 Detail rec 2 - MetalType 1 2 3 4 5 6 7 8 Now the above records are good and are brought to Crystal as a stored procedure. Now, if any of the fields in a row are null or blank, that would be considered 'bad' and thus the whole sub-report needs to be suppressed. Now if row 1 is bad, I can get rid of both, row 1 is good, it prints and I cannot get rid of row 1 if row 2 is bad. Hope this helps.
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 20 Jul 2009 at 12:30pm
	Let me explain further. I assume your example rows of: *Detail rec 1 - MetalType 1 2 3 4 5 6 7 8* *Detail rec 2 - MetalType 1 2 3 4 5 6 7 8* that 1,2,3,4 etc. are all different data columns. Here you create a formula field as "Check" or whatever you want (although I would add this as the stored proc if possible) to look at all of those columns per row for your 'bad conditions' : if isnull(table.column1) or table.column1=0 or if isnull(table.column2) or table.column2=0 or ... if isnull(table.column8) or table.column8=0 then 1 else 0 Now you have a flag per row that indicates GOOD (0) or BAD (1) data. Create a SUM of this formula field as SUM({@Check}) placed on the report footer. now you are checking the entire subreport for Good (Sum=0) or Bad (Sum>0). So now you can suppress the all subreport sections conditionally if SUM(0">{@Check})>0 the SUM function in this case will allow you to check earlier sections. If you used a Running Total or a variable you would not be able to do that. In your main report you can suppress the subreport when it is blank. If you choose to alter your stored proc you just change the last part to Summing your stored proc field instead of the formula field I created above. Make sense?
	IP Logged

ostaro Newbie Joined: 20 Jul 2009 Location: United States Online Status: Offline Posts: 3	Posted: 21 Jul 2009 at 8:44am
	Yes, it makes sense and I got that to work, per your info, thanks. I couldn't wrap my head around what you said as I left out one important detail (sorry about that). the wrinkle is this - for each row, there has to be 2 or more columns that fail to make the whole record fail. So, I created a count for each row on the number of columns that fail and tried to do the same, but i cannot create a summary on a shared variable nor can I now create a group. Anywork around to this as you may see fit? I have not looked into trying to build a SQL statement around this, but of course, any help will be appreciated.
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 21 Jul 2009 at 9:45am
	Change your "CHECK" formula to: if ( (if isnull(table.column1) or table.column1=0 then 1 else 0) + (if isnull(table.column2) or table.column2=0 then 1 else 0) + (if isnull(table.column3) or table.column3=0 then 1 else 0) + ... (if isnull(table.column8) or table.column8=0 then 1 else 0) ) > 1 then 1 else 0
	IP Logged

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum