Page Comparison

{anchor:top}
{toc: class=contents}
h2. Introduction
{styleclass: Class=topLink}[top|#top]{styleclass}


h3. Creating the Master Query
The Master Query is going to include:



h3. Union SubQuery
The Union Query will include:


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%}
!0101create.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%}
!0202builderType.png!

{column}
{section}

{section}
{column: width=30%}
*3.* Select *Ski Team* as the View.

*4.* Now click the !step_right_on.png!
button to proceed.
{column}
{column: width=70%}
!2a03view.png|thumbnail,border=1! 
{column}
{section}

h2. Master Query
{styleclass: Class=topLink}[top|#top]{styleclass}
{section}
{column: width=30%}
*54.* Drag and drop the !rpt_dim.png! *AthleteAthelte Country* dimension and !rpt_metric.png! *Invoiced Amount* metric into the *Column FieldsColumns* arealist. 	

{column}
{column: width=70%}
!06master.png|thumbnail,border=1!
{column}
{section}

{section}
{column: width=30%}
*6*5.* TickDrag theand checkbox next to *Sub Queries* to enable them. There should now be a *Sub Query* link at the top of the page.	 
{column}
{column: width=70%}
!03enable.png!
{column}
{section}
{section}
{column: width=30%}
*7.* Click on the *Sub Query* link. This will open the *Sub Query Type* menu.

*8.* Set the Type to *Union*.

*9.* Click *Submit*drop the !rpt_dim.png! *Year* dimension into the *Filters* list.
{column}
{column: width=70%}
!04select04fields.png|thumbnail,border=1!
{column}
{section}
{section}
{column: width=30%}
*10.* You should now have a *Master Query* Tab and a *Sub Query* Tab. Click on the *Master Query* tab to setup the query.	 
{column}
{column: width=70%}
!05tabs.png!
{column}
{section}

h3. Creating a Field Label

h2. Union Query
{styleclass: Class=topLink}[top|#top]{styleclass}
h3. Create Union
{section}
{column: width=30%}
*116.* Click on the *Add Calculated Fields+* Linkbutton atin the bottomSub ofQuery the page to begin creating a calculated field.	 
{column}
{column: width=70%}
!07calcfield.png!
{column}
{section}
{section}
{column: width=30%}
*12.* Type *Invoiced Amount* in the textbox as shown.

*13.* Click on the !calc_adobj.gif! buttonpanel to the left of the Data step to add thea text to your formulaSub Query.

*147.* *'Invoiced Amount'* should now appear in Set the large text area. This will mean that the calculated field's value will be constant, used as a label in the report.

*15.* Set the *Calculated Field Name* to *LabelType to *Union* and Style to *Basic*.

*168.* Click the {color:#357CB6}Save{color} link to finish*Ok* to set up the query.
{column}
{column: width=70%}
!08labelx.png|thumbnail,border=1!
{column}
{section}

h3. Configure Union
{section}
{column: width=30%}
*17.* Drag and drop the !rpt_dim.png! *Label* Calculated Field into the *Column Fields* area.

*18.* Click on the *Union* tab.	 
{column}
{column: width=70%}
!09master.png|thumbnail,border=1!
{column}
{section}
{section}
{column: width=30%}
*19.* Drag and drop the !rpt_dim.png! 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* dimensionfield and !rpt_metric.png! *Profit Margin* metric into the *Child Fields* area as shown. It is important to make sure the order matches upin 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 *MasterSub Query Fields* so that box in order to join it to the unionMaster worksQuery.	 
{column}
{column: width=70%}
!11subqueryx.png|thumbnail,border=1!
{column}
{section}

h3. Name & Save
{section}
{column: width=30%}
*2013.* Click onChange the *Addname Calculatedof Field*the linkSub asQuery before. This time type *Profit Margin* and click !calc_adobj.gif! to add the text.

*21.* Set the *Calculated Field Name* to *Label*..

*22.* Click the {color:#357CB6}Save{color} link to finish.	to *Previous Year*. This will later help identify the sub query, especially useful if you have multiple sub queries.

*14.* Save the Sub Query.
{column}
{column: width=70%}
!10label08nameSave.png|thumbnail,border=1!
{column}
{section}

h3. Edit Settings
{section}
{column: width=30%}
*2316.* DragIf andyou dropneed the !rpt_dim.png! *Label* Calculated Field into the *Child Fields* area as shown.

*24.* You can now click on the !step_right_on.png! at the top of the page to progress to the next step.	 
to make changes to your sub query setup at all, click the *Edit Settings* link.
{column}
{column: width=70%}
!12subqueryLabel10settings.png!
{column}
{section}


h2. CustomisationFormatting
{styleclass: Class=topLink}[top|#top]{styleclass}
{section}
{column: width=30%}
*123.* You should now have a report that looks like the one pictured here. 

*2.* To start, click on the *Label* column header and drag it so that it is moved between the *Athlete County* and *Sum Invoiced Amount* columns.	Click on the first *Sum Invoiced Amount* field's drop down list and select *Format*. 
{column}
{column: width=70%}
!13reorderx.png|thumbnail,border=1!
{column}
{section}
{section}
{column: width=30%}
*324.* YourChange reportthe should*Display* nowname appearto as*Invoiced pictured. Reordering the columns should make the information easier to read. Formatting will also help.	 2014*. It's important to do this to provide the user with some context to help differentiate between the queries.
{column}
{column: width=70%}
!14reportx.png|thumbnail,border=1!
{column}
{section}
{section}
{column: width=30%}
*425.* While Clickstill onin the *ColumnsColumn Formatting* link at the top of the page.	
{column}
{column: width=70%}
!15columns.png!
{column}
{section}
{section}
{column: width=30%}
*5.* Select the menu, select the *Sum Invoiced Amount* Columnfield from the dropdown.
{column}
{column: width=70%}
!15column.png!
{column}
{section}
{section}
{column: width=30%}
*6.* Click list on the *Display* Menuleft.

*726.* ChangeSet the *Displayname Name* to *AmountInvoiced 2013*. and *8.* Click on Close.	close the menu.
{column}
{column: width=70%}
!16displayx.png|thumbnail,border=1!
{column}
{section}

h2. Save
{styleclass: Class=topLink}[top|#top]{styleclass}
{section}
{column: width=30%}
*927.* YouClick should*Report* now> have*Save* ato reportactivate that looks like the one pictured hereyour report.

{column}
{column: width=70%}
!17reportx.png!
{column}
{section}
{section}
{column: width=30%}
*10.* You are now finished with this report. Click on the *Save Menu*.

*11.*28.* Set the *Name* of your report to *UnionAppend Sub Query Tutorial*.

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

*1330.* Select *Tutorial* as the *Category*.

*1431.* Select *Advanced ReportingTraining* as the *Sub Category*.

*1532.* Click *Activate* to finish.	
{column}
{column: width=70%}
!18savex.png|thumbnail,border=1!
{column}
{section}
{section}
bq. See [Formatting|Report Builder - Formatting] for more information.{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}