Page Comparison

{anchor:top}
{toc: class=contents}
h2. Introduction
{styleclass: Class=topLink}[top|#top]{styleclass}
In this tutorial, we will walk through setting up a *Union* sub query. A good way to this about *Unions* is - use them if you need to combine multiple fields into the one column, either from the same or different views or sources.

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.

h3. Master Query
The Master Query is going to include:
# Camp Region
# Calculated Field: "Invoiced" text label
# Sum Invoiced Amount


h3. 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)


h2. Initialise
{styleclass: Class=topLink}[top|#top]{styleclass}
{section}
{column: width=30%}
*1.* Click on the *Create* link and select *Report* to begin building your report.
{column}
{column: width=70%}
!01create.png!
{column}
{section}

{section}
{column: width=30%}
*2.* You should now be on the Initialise Report page. Select the *Drag and Drop Builder* as the build tool.
{column}
{column: width=70%}
!02builderType.png!
{column}
{section}

{section}
{column: width=30%}
*3.* Select *Ski Team* as the View.
{column}
{column: width=70%}
!03view.png|thumbnail,border=1!
{column}
{section}

h2. Master Query
{styleclass: Class=topLink}[top|#top]{styleclass}
{section}
{column: width=30%}
*4.* Drag and drop the !rpt_dim.png! *AthelteCamp CountryRegion* dimension and !rpt_metric.png! *Invoiced Amount* metric into the *Columns* list. 
{column}
{column: width=70%}
!04masterFields.png|thumbnail,border=1!
{column}
{section}

h3. Label Field
{section}
{column: width=30%}
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".

*5.* Click on the *+* to create a calculated field, in the bottom of the View Fields List panel. 
{column}
{column: width=70%}
!05addCalc.png!
{column}
{section}
{section}
{column: width=30%}
*6.* Set the *Calculated Field Name* to *Label* 
{color:#CC0000}*Note:*{color} the name for this field can be anything, it will not change the way the union works if it's called something else.

*7.* Type *Invoiced* Drag and drop the !rpt_dim.png! *Year* dimension into the *Filters* list into the text box under the main calculation panel, as shown here, and click *+ Add* to add it to the calculation.
{column}
{column: width=70%}
!06typeLabel.png|thumbnail,border=1!
{column}
{section}
{section}
{column: width=30%}
*8.* Click the *Validate* button to ensure your calculation is built correctly. 

*9.* Click the *Save* button to complete your calculation.
{column}
{column: width=70%}
!07saveLabel.png|thumbnail,border=1!
{column}
{section}
{section}
{column: width=30%}
*10.* Drag the *Label* calculation to between the *Camp Region* and *Sum Invoiced Amount* fields in either the *Columns* list or *Table Preview*.
{column}
{column: width=70%}
!x08addLabel.png|thumbnail,border=1!
{column}
{section}


h2. Union Query
{styleclass: Class=topLink}[top|#top]{styleclass}
h3. Create Union
{section}
{column: width=30%}
*6.* Click on the *+* button in the Sub Query panel to the left of the Data step to add a Sub Query.

*7.* Set the Type to *Union* and Style to *Basic*.

*8.* Click *Ok* to set up the query.
{column}
{column: width=70%}
!x09addUnion.png|thumbnail,border=1!
{column}
{section}

h3. 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.

h4. Configure Union Label
{section}
{column: width=30%}
*5.* Click on the *+* to create a calculated field, in the bottom of the View Fields List panel. 
{column}
{column: width=70%}
!05addCalc.png!
{column}
{section}
{section}
{column: width=30%}
The first thing you will need to define, when setting up a sub query, is the join type, and join fields.

*9.* Ensure the join type is *Left Outer Join*.

*10.* Select the *Athlete Country* field in the *Master Query Fields* drop down list. This is the field you wish to join the Sub Query to.

*11.* Drag the *Athlete Country field in to the *Sub Query Fields* box in order to join it to the Master Query*6.* Set the *Calculated Field Name* to *Label* 
{color:#CC0000}*Note:*{color} the name for this field can be anything, it will not change the way the union works if it's called something else.

*7.* Type *Cost* into the text box under the main calculation panel, as shown here, and click *+ Add* to add it to the calculation.

*8.* Click the *Validate* button to ensure your calculation is built correctly. 

*9.* Click the *Save* button to complete your calculation.
{column}
{column: width=70%}
!10labelCost.png|thumbnail,border=1!
{column}
{section}

h4. Cost
{section}
{column: width=30%}
*5.* Click on the *+* to create a calculated field, in the bottom of the View Fields List panel.

*6.* Set the *Calculated Field Name* to *Cost* 
{color:#CC0000}*Note:*{color} the name for this field can be anything, it will not change the way the union works if it's called something else.

*7.* Click on the *∑* button. 

*x.* Select the *Camp Cost* field from the *--Select Field--* drop down, and click *+ Add*.

*x.* Close the bracket by clicking *)*.

*x.* Click the *\** button to multiply the field by a value.

*x.* Type *-1* into the text entry field, and click *+ Add* to put in your calculation.

*8.* Click the *Validate* button to ensure your calculation is built correctly. 

*9.* Click the *Save* button to complete your calculation.
{column}
{column: width=70%}
!x11costCalc.png|thumbnail,border=1!
{column}
{section}

h3. NameConfigure & SaveUnion
{section}
{column: width=30%}
*1310.* ChangeDrag the name of*Camp Region* field into the *Sub Query Fields* list, directly next to the *Camp Region* field *Previous Year*. This will later help identify the sub query, especially useful if you have multiple sub queries.

*14.* Save the Sub Queryin the *Master Query Fields* list.

*10.* Drag the *Label* calculated field into the *Sub Query Fields* list, directly next to the *Label* field in the *Master Query Fields* list.

*10.* Drag the *Cost* calculated field into the *Sub Query Fields* list, directly next to the *Sum Invoiced Amount* field in the *Master Query Fields* list.

** Change the name of the Sub Query to *Cost* and click *Save* to complete the *Union*.
{column}
{column: width=70%}
!x12configureUnion.png|thumbnail,border=1!
{column}
{section}

h3. Edit Settings
{section}
{column: width=30%}
*16.* If you need to make changes to your sub query setup at all, click the *Edit Settings* link.
{column}
{column: width=70%}
!10settings.png!
{column}
{section}


h2. Formatting
{styleclass: Class=topLink}[top|#top]{styleclass}
{section}
{column: width=30%}
*23.* Click on the first *Sum Invoiced Amount* field's drop down list and select *Format*. 
{column}
{column: width=70%}
!x.png|thumbnail,border=1!
{column}
{section}
{section}
{column: width=30%}
*24.* Change the *Display* name to *Invoiced 2014*. It's important to do this to provide the user with some context to help differentiate between the queries.
{column}
{column: width=70%}
!x.png|thumbnail,border=1!
{column}
{section}
{section}
{column: width=30%}
*25.* While still in the *Column Formatting* menu, select the *Sum Invoiced Amount* field from the list on the left.

*26.* Set the name to *Invoiced 2013* and close the menu.
{column}
{column: width=70%}
!x.png|thumbnail,border=1!
{column}
{section}

h2. Save
{styleclass: Class=topLink}[top|#top]{styleclass}
{section}
{column: width=30%}
*27.* Click *Report* > *Save* to activate your report.
{column}
{column: width=70%}
!x.png!
{column}
{section}
{section}
{column: width=30%}
*28.* Set the *Name* of your report to *Append Sub Query Tutorial*.

*29.* Set the *Description* to *This report was written using the Sub Query Tutorial*.

*30.* Select *Tutorial* as the *Category*.

*31.* Select *Training* as the *Sub Category*.

*32.* Click *Activate* to finish.
{column}
{column: width=70%}
!x.png|thumbnail,border=1!
{column}
{section}
{section}
{column: width=30%}
*33.* You now have an active Append Sub Query report.
{column}
{column: width=70%}
!x.png!
{column}
{section}

\\
\\
{horizontalrule}
{styleclass: Class=topLink}[top|#top]{styleclass}