Crystal Reports - Hide Footer based on Criteria
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
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
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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
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
ASKER
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
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
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
mlmcc
ASKER
I will do, thanks for that
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