Solved

Crystal Reports - Hide Footer based on Criteria

Posted on 2014-01-28
12
1,472 Views
Last Modified: 2014-01-28
Hi

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
0
Comment
Question by:JacquesSmith
  • 6
  • 4
  • 2
12 Comments
 
LVL 34

Expert Comment

by:James0628
Comment Utility
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.

 James
0
 

Author Comment

by:JacquesSmith
Comment Utility
James

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
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 50 total points
Comment Utility
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.

 James
0
 

Author Comment

by:JacquesSmith
Comment Utility
James

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.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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.

mlmcc
0
 

Author Comment

by:JacquesSmith
Comment Utility
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


Comment:
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
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 100

Accepted Solution

by:
mlmcc earned 450 total points
Comment Utility
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

GroupFooter
    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

mlmcc
0
 

Author Closing Comment

by:JacquesSmith
Comment Utility
mlmcc

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.

James
Thanks for trying to assist
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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

mlmcc
0
 

Author Comment

by:JacquesSmith
Comment Utility
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
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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.

mlmcc
0
 

Author Comment

by:JacquesSmith
Comment Utility
I will do, thanks for that
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 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

11 Experts available now in Live!

Get 1:1 Help Now