|
Return to Newsletter Contents
Using Shared Variables in Crystal Reports XI
by: Kristen St. Jean, Crystal Instructor, BI Specialist
Subreports are commonly used in Crystal Reports.
A subreport is a report inside of a report that can be related or
unrelated to the main report.
When using subreports, quite often we find that we need to perform calculations
or use values from the subreport in a formula in the main report.
The only way that this can be accomplished is through the use of shared
variables.
Variables provide us with the power to share values between
formulas in a report. We are going
to create a variable to share information from a subreport to a main report
using the following scenario.
We have a main report which contains information about
orders. It also contains information
about returned orders which is from a different database.
The returned orders information is an unlinked subreport which displays
the returned order information in the report footer.
At the end of the report, a Net Sales formula is needed which is defined
as the Total Order Amount – Total Return Order Amount.
Since the returned order information is a subreport, and the order
information is in the main report, a variable will need to be created to bring
the total from the subreport into the main report in order for it to be used in
the calculation.
Variables are contained inside of formulas, so when
creating a variable, you will have to create a new formula first.
Once you are in the formula editor, there are three steps to remember:
Declare,
Define, and
Reference.
A variable declaration consists of its scope, data type,
and name. There are three scopes in
Crystal Reports: local, global, shared.
A local variable can only be
used in the formula in which it is declared.
A global variable can be used
in multiple formulas in one report.
A shared variable can be shared
between a main report and a subreport. After
deciding on a scope, the data type that the variable will contain must be
declared, and finally, the variable must have a name.
Once a variable has been declared, it must be assigned a
value. This value can be a constant,
a database field, a calculation, a parameter, or some type of function.
When assigning variables, the assignment operator, which is a colon
equals (:=), is used.
Each of the above statements needs to be followed by a
semicolon in order for Crystal Reports to evaluate them correctly.
Once this has been completed, it needs to be referenced or used in the
formula.
In our example, the main report contains the Order Amount,
so the Return Order Amount value needs to be shared with the main report to
perform the Net Sales calculation.
For this reason, we need to create a formula in the subreport that captures the
Return Order Amount.
The formula in the subreport to capture the Total Return
Order Amount should read as follows:
//Declare the variable: Scope, Data type, Name
Shared NumberVar vTotalReturns;
//Assign the variable a value
vTotalReturns :=
Sum({Orders_Return.return_order_amount});
//Return the variable as the result of the formula
vTotalReturns
Remember to place the formula on the subreport so it can be
calculated. It is not enough to just
create it, you need to actually place in on the subreport so the value will be
calculated by Crystal Reports.
Once the formula in the subreport has been created, we can
move to the main report and create a formula to calculate the Net Sales.
In the formula, we have to tell Crystal Reports to find the value of the
variable, so we need to declare the variable in the second formula as well.
The Net Sales formula should read as follows:
//Declare the variable so Crystal Reports finds the
value
Shared NumberVar vTotalReturns;
//Use the variable to calculate the Total Net Sales
Sum({Orders.order_amount}) - vTotalReturns
The Net Sales formula should be place in Report Footer b so
it will display at the end of the report.
Variables are a powerful tool in Crystal Reports that allow
us to share values between not only formulas on one report, but between formulas
in a main report and a subreport.
Go to Top |
Return to Newsletter Contents
|
