30日間の無料評価版をお試しいただけます。

Versions Compared

Old Version 2

changes.mady.by.user Yellowfin Admin

Saved on

compared with

New Version 3

changes.mady.by.user Yellowfin Admin

Saved on

Key

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

Anchor
top
top

Creating the Union Sub Query

Table of Contents
classcontents

Introduction

Styleclass
ClasstopLink

top

Master Query

The Master Query is going to include:

Union Query

The Union Query will include:

Initialise

Styleclass
ClasstopLink

top

Section
Column
width30%

1. Click on the Create link and select Report to begin building your report.

Column
width70%

Image Removed Image Added

Section
Column
width30%

2. You should now be on the Initialise Report page. Select the Drag and Drop Builder as the build tool.

Column
width70%

Image Removed Image Added

Section
Column
width30%

3. Select Ski Team as the View.4. Now click the Image Removed button to proceed.

Image Removed
Column
width70%

Image Added

Master Query

Styleclass
ClasstopLink

top

section
Section
Column
width30%

54. Drag and drop the Athlete Athelte Country dimension and Invoiced Amount metric into the Column Fields areathe Columns list.

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

Column
width70%

Image Modified

Union Query

columnwidth
Styleclass
Class
30%

6. Tick the checkbox next to Sub Queries to enable them. There should now be a Sub Query link at the top of the page.

Column
width70%

Image Removed

topLink

top

Create Union

Section
Column
width30%
Column
width30%

76. Click on the + button in the Sub Query link. This will open the Sub Query Type menu.panel to the left of the Data step to add a Sub Query.

78. Set the Type to Union and Style to Basic.

9. Click Submit.

Column
width70%

Image Removed

Section

10. You should now have a Master Query Tab and a Sub Query Tab. Click on the Master Query tab to setup 8. Click Ok to set up the query.

Column
width70%

Image Modified

...

Configure Union

Section
Column
width30%

11. Click on the Add Calculated Fields Link at the bottom of the page to begin creating a calculated field.

Column
width70%

Image Removed

Section
Column
width30%

12. Type Invoiced Amount in the textbox as shown.

13. Click on the Image Removed button to add the text to your formula.

14. 'Invoiced Amount' should now appear in 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 Label.

16. Click the Save link to finish.

Column
width70%

Image Removed

Section
Column
width30%

17. Drag and drop the Image Removed Label Calculated Field into the Column Fields area.

18. Click on the Union tab.

Column
width70%

Image Removed

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

Section
Column
width30%

19. Drag and drop the Image Removed Athlete Country dimension and Image Removed Profit Margin metric into the Child Fields area as shown. It is important to make sure the order matches up to the Master Fields so that the union works.

Column
width70%

Image Modified

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.

20. Click on the Add Calculated Field link as before. This time type Profit Margin and click Image Removed to add the text.

21. Set the Calculated Field Name to Label..

22. Click the Save link to finish.

Column
width70%

Image Modified

Edit Settings

Section
Column
width30%

23. Drag and drop the Image Removed Label Calculated Field into the Child Fields area as shown.

24. You can now click on the Image Removed at the top of the page to progress to the next step16. If you need to make changes to your sub query setup at all, click the Edit Settings link.

Column
width70%

Image RemovedImage Added

...

Formatting

Styleclass
ClasstopLink

top

Section
Column
width30%

1. 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 columns23. Click on the first Sum Invoiced Amount field's drop down list and select Format.

Column
width70%

Image Modified

Section
Column
width30%

3. Your report should now appear as pictured. Reordering the columns should make the information easier to read. Formatting will also help24. 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
width70%

Image Modified

section
Section
Column
width30%

4. Click on the Columns link at the top of the page.

Column
width70%

Image Removed

Section

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

5. Select the Sum Invoiced Amount Column from the dropdown.

Column
width70%

Image Modified

Save

columnwidth
Styleclass
Class
30%

6. Click on the Display Menu.

7. Change the Display Name to Amount.

8. Click on Close.

Column
width70%

Image Removed

topLink

top

Section
Column
width30%

9. You should now have a report that looks like the one pictured here27. Click Report > Save to activate your report.

Column
width70%

Image Modified

Section
Column
width30%

10. You are now finished with this report. Click on the Save Menu.

1128. Set the Name of your report to Union Append 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 Reporting Training as the Sub Category.

1532. Click Activate to finish.

Column
width70%

Image Modified

...

Section
Column
width30%

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

Column
width70%

Image Added



horizontalrule
Styleclass
ClasstopLink

top