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

Introduction

In this tutorial, we will walk through setting up a Minus sub query. A good way to think about Minuses is - use them if you need a list of something, not including results found in another list.

In this scenario our main report will have a list of athletes that attended camps in the current year. We will then remove athletes from that list that are in the minus list (athletes from prior to the current year), leaving us with only athletes attending camps for the first time this year.

 

 

 

Create

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.

Left Side Nav

Top Right

(with Left Side Navigation enabled)

Toolbar

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.

 

 

Master Query

4. Drag and drop the Athlete ID, First Name, Last Name, and Date of Birth dimensions into the Columns list.

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

 

Minus Query

Create Minus

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

8. Click Ok to set up the query.

 

Configure Minus

9. Set the Filter on the Master Query to apply to the Athlete ID field. This is how rows to be removed from the master query will be located, matching the ID field.

10. Set the Exclude field on the Sub Query to be the Athlete ID field. This will list all Athete ID values to be removed from the master query.

11. Drag the Year field into the Sub Query Filters list. This will be used to restrict the list of Athlete IDs to remove to a certain date range.

12. Change the name of the Sub Query to Prior Years and the description to Remove all customers from previous years to create a list of new customers for the current year.

13. Click Save to complete the Minus.

 

Edit Settings

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

 

Apply Filters

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

 

Master Filter

16. Set the Master Query filter to be Year Between

17. Click the Define Value link and select the Pre-defined Period option.

18. Select the This Calendar Year option and click the Submit button.

 

Sub Query Filter

19. Navigate to the sub query filter by clicking on the Prior Years tab.

20. Set the filter to be Year Less than or equal to

21. Click the Define Value link and select the Pre-defined Period option.

22. Select the Calendar Year Start option and click the Submit button.

23. Close the Configure Filters panel.

 

Save

24. Click Report > Save to activate your report.

25. Set the Name of your report to Minus Sub Query Tutorial.

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

27. Select Tutorial as the Category.

28. Select Training as the Sub Category.

29. Click Activate to finish.

30. You now have an active Minus Sub Query report. There will be no results as there is only a small number of transactions for 2014, and no new customers among them. If you change the filters to be Master = 2013 and Minus < 2013 there will be results.