Page History
Anchor | ||||
---|---|---|---|---|
|
Table of Contents | ||
---|---|---|
|
Overview
Styleclass | ||
---|---|---|
| ||
HTML |
---|
<iframe width="700" height="394" src="https://www.youtube.com/embed/1O7ogaEZ4p0?color=white" frameborder="0" allowfullscreen></iframe> |
Introduction
Styleclass | ||
---|---|---|
| ||
In this scenario our main report will have a list of invoicing figures by region, we will then use the Union query to display negative cost figures in the same column, and then add sub totals to see the total income for each region.
Master Query
The Master Query is going to include:
- Camp Region
- Calculated Field: "Invoiced" text label
- Sum Invoiced Amount
Union Query
The Union Query will include:
- Camp Region
- Calculated Field: "Cost" text label
- Calculated Field: Sum Cost * -1 (to convert the Cost figures to a negative value)
Create
Styleclass | ||
---|---|---|
| ||
Section | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
Section | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
Master Query
Styleclass | ||
---|---|---|
| ||
Section | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
Label Field
To complete the master query, a label field is required. This will be used to identify the rows from the master query as "Invoiced" rows. There will be a similar calculation in the union query to identify rows as "Cost".
Section | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
Section | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
Section | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
Section | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
Union Query
Styleclass | ||
---|---|---|
| ||
Create Union
Section | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
Calculations
To complete the union query, two calculations will be required;
- Label: this will be used to identify the rows from the union query as "Cost" rows. There will be a similar calculation in the master query to identify rows as "Invoiced".
- Cost: this will be used to convert the cost figure to a negative value. This will mean that when a sub total is added to the report, the cost amount will be subtracted from the invoiced amount.
Label
Section | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
Section | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
Cost
Section | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
Configure Union
Section | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
Edit Settings
Section | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
Formatting
Styleclass | ||
---|---|---|
| ||
Section | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
Section | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
Section | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
Section | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
Section | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
Save
Styleclass | ||
---|---|---|
| ||
Section | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
Section | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
Section | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
horizontalrule |
---|
Styleclass | ||
---|---|---|
| ||