Crystal Reports - Hide Footer based on Criteria

Posted on 2014-01-28
Last Modified: 2014-01-28

I am using Crystal Reports XI R2 and MS-SQL 2008

I have information in a Sub Report that I would like to pass back a variable to either suppress or display a group footer.

If I save the value to a variable, the value is only populated on the next group, so the current footer is not able to evaluate the correct variable.

I have created a Group Footer 1a and 1b so that it displays in 1b, but I am unable to hide 1a.

The reason for running a sub report is that I need to evaluate information from another Database which selects its information based on Data passed from the main report.

The Answer would be to run a SQL Expression Query,  but there does not seem to be a way to pass variables or selection information to a SQL Expression
Question by:JacquesSmith
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
LVL 35

Expert Comment

ID: 39814664
I'm not sure about the SQL Expression part, because I never used them.

 As for the subreport variable, you have to put the subreport in a main report section that's before the one where you want to use the value in that variable.

 If the subreport is only producing a couple of values on the report, you could save the values in variables, have the subreport not produce any output at all, and use the variables to show the values on the main report.  Then you could have the subreport run "silently" in a new GF1a, and use the variables to display values and do your suppression in group footer sections below that.

 If the subreport is producing a lot of output, then your only option using subreports may be to have two copies of the subreport.  You would have one copy in a new GF1a section that would run without producing any output and just set your suppression variable.  Then your original subreport would be in a section after that, to produce the required output.

 If neither of those options works for you, then another option may be to create a query (eg. a stored procedure on the server, or a CR command) and use that as the datasource for the report.  The query would gather the data required for the main report, and the value that you're trying to get from the subreport to control the suppression, so that the main report doesn't have to use a subreport to get that value.

 Or, as you suggested, a SQL Expression may be an option.  Maybe someone more familiar with those will be able to help with that.


Author Comment

ID: 39814833

Thanks for replying

The Sub report is only evaluated in the footer section, as the variables needed for the sub report are derived from there, so it cannot be run before the footer. Hence trying to run a footer 1a and 1b and trying to hide 1a, but it seems if you hide 1a, it hides the whole of footer 1.

The Sub report only produces 1 value for the report which is a total of values from a separate database,  I tried to use a variable, but the correct variable only displays on the second footer line, and I obviously only want 1 line in the footer.

I thought about using a query, but have no way of pushing the selection criteria to the query.

If I use a SQL expression with hard coded selection criteria, then it works, but every line has a different selection criteria, so I need to somehow see if I can send the selection criteria to the SQL Expression
LVL 35

Assisted Solution

James0628 earned 50 total points
ID: 39814889
If you simply suppress the section that a subreport is in, the subreport won't be run.  You can:
 Suppress everything in the subreport, so that it doesn't produce any output.
 Go to the subreport format and on the Subreport tab, check the "Suppress blank subreport" option.
 Go to the Section Expert for that section and check the "Suppress blank section" option.

 Of course you also need to make sure that no other fields are displayed in that section, so that when the subreport is suppressed, the section is blank, and then the whole section will be suppressed.

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.


Author Comment

ID: 39814923

I am trying to suppress the group footer of the report, not the sub report. The sub report gives information that I must evaluate to enable the Group footer to print. The Footer contains a lot of other information, If the sub report's supplied criteria meets certain expectations, I want the group footer to print, else the footer must be suppressed.

The sub reports information displays correctly on the Group Footer line, the variable that is stored in the sub report, though only appears on the next group footer, or in line 1b.

The report is only displaying the footers (So I have 14 000 lines of footers for the report) as the detail lines are hidden because the footer calculates from the detail lines.
LVL 100

Expert Comment

ID: 39814981
James tried to explain the issue.

Crystal uses a multipass method for rendering the report.

In a section the last thing rendered or evaluated is the subreport.  This is done after any formulas including the section suppression formulas, thus the values from the subreport are NOT available when the formulas in the main report are evaluated if the subreport is in that section

Example as you probably have the report set up.
MR Report Header
MR Group Header
MR Details
MR Group Footer  - Shared values are not available from this pass of the subreport.
      Subreport  - evaluated to produce values for the shared values
MR Report footer - Shared values are available here

As suggested by James
MR Report Header
MR Group Header
MR Details
MR Group Footer1A  - Shared values are not available from this pass of the subreport.
      Subreport  - evaluated to produce values for the shared values
MR Group Footer1B - Shared values are available here can can be used to suppress this section
MR Report footer - Shared values are available here

If the subreport doesn't produce any output it can be suppressed through the formatting and the section can be suppressed throught the section expert.

If you are using the subreport to display other information then you have 2 options
1.  Create another subreport that evaluates the data to get the shared values then use them to suppress the following Group Footer subsection and its full subreport
2.  Use the values in the subreport to suppress the subreport output.


Author Comment

ID: 39815066
Hi mlmcc

The Sub report always has an output, if I suppress it due to it meeting the criteria then I have a blank space on the Line, so I would always display it - (unless I am missing your point)

I understand what James is saying, this is what I am trying to do, but just not being successful

My report is as follows

MR Report Header
MR Page Header
MR Group Header1 (Hidden)
MR Details (Hidden)     Detail-A    Detail-B    Detail-C   Detail-D    Detail-E     Detail-F
MR Group Footer1A  - Detail-A  Detail-B  Detail-C Detail-D <SubReport> Detail-E Detail-F

Detail-D/E/F are sums and formulas based on the details in MR Details

This has worked fine, until now.  I now need to suppress/Hide Footer1A based on value of Sub Report, so as not to have a line at all.  

I thus created a shared variable called SV1 and put it at the end of Group Footer 1A, I also put it in Group Footer1B.

In Group Footer 1A it shows the value from the previous "Run" and Group Footer 1B shows the value correctly. I can also use the variable in Group Footer 1B to hide/suppress Group Footer 1. (That works)

The Problem is that now when I display, I am displaying 2 lines instead of one, I would like the output to only have one line, and hide GF1A, but you don't seem to be able to do this as there is only a suppress, and if you suppress or hide GF1A, GF1B is hidden as well, you don't seem to be able to only hide one of them.

This is where I tried the SQL Expression Route, which unfortunately one does not seem to be able to pass any values too.

I hope this all makes sense
LVL 100

Accepted Solution

mlmcc earned 450 total points
ID: 39815174
Hide is for the entire section and can't be applied to a subsection.

You can suppress individual subsections.

You need to get it setup as

    1A - New subsection added - Subreport to calculate values for suppressing 1B
              Subreport should only calculate and not display anything
              To suppress a blank subreport
                     right click the subreport
                     CLick FORMAT SUBREPORT
                      Borders Tab - Set borders to NONE
                      Last Tab - Set option to suppress empty/blank subreport
                      Click OK
                Suppress blank section
                      Right click section in left margin
                       CLick SECTION EXPERT
                       Set option to SUPPRESS BLANK SECTION
    1B - (old 1A) - Suppress using the shared variables


Author Closing Comment

ID: 39815441

Thanks for the assistance, once I realised that the Sub report section also had to be hidden, then everything fell into place.

Is there any way to your knowledge to pass criteria to a SQL Expression Query.

Thanks for trying to assist
LVL 100

Expert Comment

ID: 39815459
What do you mean by criteria?

What database?

I f I understand what you mean by criteria (values from a record in the report) you can't pass them if you are using Oracle


Author Comment

ID: 39815487
I am using MS SQL 2008R2.

Yes you are correct, I am trying to pass values from a record in the report to the SQL Expression Query.

The aim was to try and eliminate the use of a sub report to speed up the report
LVL 100

Expert Comment

ID: 39815513
You probably should open a new question on that.  The best experts on SQL expressions didn't participate in this question and probably aren't monitoring it.


Author Comment

ID: 39815521
I will do, thanks for that

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Error "Could not load mobile report" in SQL SSRS 2016 after SP1 CU2 Update 2 47
Begin Transaction 12 26
how to use ROW_NUMBER() correctly 8 44
learning MS SSIS 13 27
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

710 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