Like what you see? Have a play with our trial version.

Error rendering macro 'rw-search'

null

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Table of Contents
classcontents

Overview


HTML
<iframe width="700" height="394" src="https://www.youtube.com/embed/CyUgmM4PmBI?color=white" frameborder="0" allowfullscreen></iframe>

...

 

Introduction

...

ClasstopLink

...

Styleclass
ClasstopLink

...

top

20709876

In this tutorial, we will walk through setting up an append sub query in order to compare two years worth of figures.

...

In this scenario our main report will look at invoice figures by country in 2014. The column we want to add is the invoiced figures in 2013. The filters Year = 2014 and Year = 2013 conflict, so the additional column will have to be added through the append. 


Master Query

The Master Query is going to include:

...

Note: sometimes you will have a case where you don't wish to filter the Country list by 2014, this may be because some countries have no results for 2014 yet. In this case you would have Country in the Master Query, and an Append Query for each Invoiced Amount. 


Append Query

The Append Query will include:

...

This will be joined to the Master Query using the Athlete Country field. The query will be filtered by Year = 2013, and will include a copy of the Sum Invoiced Amount field to be filtered.

 


Create


Styleclass
ClasstopLink

...

20709876


Section


Column
width30%

1.  Start the Report creation process by using one of these methods:

 


    • Left Side Nav - open the left side navigation panel, click on Create, select the Report option
 


    • Top Right Button - click on the create button, select the Report option.
 


    • Toolbar - click on the Create link in the toolbar, select the Report option.


You will now see the New Report lightbox.


Column
width70%


Left Side Nav

Top Right

(with Left Side Navigation enabled)

Toolbar



...

Section


Column
width30%

2. Click on the Ski Team view in order to use the fields defined within it for creating the report.

3. You will now be taken to the Data step of the Report Builder.


Column
width70%

...



Master Query


Styleclass
ClasstopLink

...

20709876


Section


Column
width30%

4. Drag and drop the Athelte Country dimension and Invoiced Amount metric into the Columns list.

5. Drag and drop the Year dimension into the Filters list.


Column
width70%

 



Append Query


Styleclass
ClasstopLink

...

20709876

...


Create Append

Section


Column
width30%

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 Append and Style to Basic.

8. Click Ok to set up the query.


Column
width70%

...



Join Append

Section


Column
width30%

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.


Column
width70%

...



Filters

Section


Column
width30%

12. Add the Year field to the Sub Query Filters box. This filter will be used to restrict results of the sub query only.


Column
width70%

...



Name & Save

Section


Column
width30%

13. Change the name of the Sub Query 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
width70%

...



Add Column

Section


Column
width30%

15. Drag and drop the Invoiced Amount metric into the Columns list.


Column
width70%

...



Edit Settings

Section


Column
width30%

16. If you need to make changes to your sub query setup at all, click the Edit Settings link.


Column
width70%

 



Apply Filters

Section


Column
width30%

17. Once your sub query is ready, navigate back to the Master Query by clicking on it in the sub query panel on the left of the page.


Column
width70%


...

Section


Column
width30%

18. Click on the Settings link in the Filters panel. This will allow you to configure logic and values for all filters.


Column
width70%

...



Master Filter

Section


Column
width30%

19. Set the Master Query filter to be Year Equal to 2014 by clicking on the funnel icon.


Column
width70%


...

Section


Column
width30%

20. Select 2014 and click submit


Column
width70%

 



Sub Query Filter

Section


Column
width30%

21. Navigate to the sub query filters by clicking on the Previous Year tab.


Column
width70%


...

Section


Column
width30%

22. Click on the x on the filter panel to close it. Be sure not to click on the X to close the report builder.


Column
width70%

...



Formatting


Styleclass
ClasstopLink

...

20709876


Section


Column
width30%

23. Click on the first Sum Invoiced Amount field's drop down list and select Format.


Column
width70%


...

Section


Column
width30%

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
width70%

...



Save


Styleclass
ClasstopLink

...

20709876


Section


Column
width30%

27. Click Report > Save to activate your report.


Column
width70%


...

Section


Column
width30%

33. You now have an active Append Sub Query report.


Column
width70%

 



horizontalrule

Styleclass
ClasstopLink

...

top

...

20709876