I have a report that contains a group header, a bunch of detail sections consisting of subreports, and a group footer. In the group footer is a formula field named "Balance". The value of this field depends on the results of the subreports contained in the detail sections. What I need to do is to suppress the entire iteration (ie the group header, details, and footer) if this number is 0. The problem that I've encountered is that the balance is not calculated until after the subreports have run. As such, I figure the only way that I will be able to do this is to create a formula field in a group footer section below the balance field with an instruction in VB that conditionally hides the above group footer, detail sections, and the group header.
In short, how do you use VB code to conditionally suppress a section based on the value of a formula field?
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Put a copy of the subreport in the group header, remove all the print from the copy subreport, and leave only the calculations, paint it really small, pass the value out from this copy, not the other one, suppress based on value.
Alternatively, is there no way of writing an additional query/view on the database that gives the answer at 'group' level, so you can avoid selecting the records entirely? This will undoubtedly make the report run faster.
Unfortunatly, I don't think the subreport copies will work because of the database layout. This is a buy sheet for purchasing needed materials based on a number of complex criteria that are calculated from the database on a row by row basis (ie in the detail sections). If I put the subreport copies in the group header (grouped by part number) then I will not have access to all the information in the various rows. It's too bad you can't put detail sections in the group header somehow... The only other way I can come up with to get around this is to somehow buffer the information and make the suppression calculation based on the buffered value and then display the buffered info - is this possible using arrays or strings???
I have five subreports all pulling data from different tables based on the value being input to that subreport. The issue is that the driving table has four columns: purchase order, sales order, job number, receipt number. Only one of these fields will be populated for any given row, and which one is populated directly effects how that row contributes to the total balance hence the requirement of them being in the details section. So after determining which one is populated, the subreports pull data from the other connecting tables and make calculations that contribute to the overall balance.
Ideas???
So you can't left outer join to the other tables from each column and then use whichever data turns up in the row to do the calculation?
Can you give brief details of the attached tables and the relationships between them. It seems to me that you can probably do without subreports at all, which is always easier to handle on totals and suppressions.
Your best bet will probably be if you can create a query in your db that gathers at least some of the required information before it gets to your report, and use that as the report's datasource, so that you don't have to try to do everything within the report. For example, if you could create a stored procedure or view that includes the figures from the subreports that are used in the Balance formula.
Otherwise, it might be possible to do what you want in your report using your current data, but even if it's possible, I suspect that it won't be very practical.
James
Thanks for all the comments. This was for use with a Progress DB which is totally closed, so no creation of views/stored procedures was possible. I was able to get this working by making use of more subreports. What I did was place a subreport for each of the demand types in the group header and return the overall balance result from each of the subreports by way of a shared variable. Thus, by the time the actual data was due to get displayed, Crystal already knew of the overall balance and so conditionally suppressing the rest of the group work fine.
So, you know, to at least some degree, what to expect from the detail records, so you were able to put subreports in the group header that essentially duplicated the subreports in the detail sections? That was fortunate. In most reports you probably wouldn't know how many detail records there would be in a group, much less what was in them, so something like that probably wouldn't be possible. Trying to duplicate those detail section subreports somewhere else, like the group header, was probably the biggest stumbling block. I'm glad you were able to work it out, and thanks for letting us know.
James
Business Accounts
Answer for Membership
by: mlmccPosted on 2009-08-20 at 16:13:05ID: 25147925
I don't think you can. Are you calculating the total in the VB program?
mlmcc