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

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! *Athlete ID*, !rpt_dim.png! *First Name*, !rpt_dim.png! *Last Name*, and !rpt_dim.png! *Date of Birth* dimensions into the *Columns* list.

*5.* Drag and drop the !rpt_dim.png! *Year* dimension into the *Filters* list.
{column}
{column: width=70%}
!04master.png|thumbnail,border=1! 
{column}
{section}


h2. Minus Query
{styleclass: Class=topLink}[top|#top]{styleclass}
h3. Create Minus
{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 *Minus* and Style to *Basic*.

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

h3. Configure Minus
{section}
{column: width=30%}
*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*.
{column}
{column: width=70%}
!06configureMinus.png|thumbnail,border=1!
{column}
{section}

h3. Edit Settings
{section}
{column: width=30%}
*14.* 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. Apply Filters
{styleclass: Class=topLink}[top|#top]{styleclass}
{section}
{column: width=30%}
*15.* Click on the *Settings* link in the *Filters* panel. This will allow you to configure logic and values for both filters.
{column}
{column: width=70%}
!07filterSettings.png!
{column}
{section}

h3. Master Filter
{section}
{column: width=30%}
*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.
{column}
{column: width=70%}
!08masterFilter.png|thumbnail,border=1!
{column}
{section}

h3. Sub Query Filter
{section}
{column: width=30%}
*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.
{column}
{column: width=70%}
!09minusFilter.png|thumbnail,border=1!
{column}
{section}

h2. Save
{styleclass: Class=topLink}[top|#top]{styleclass}
{section}
{column: width=30%}
*24.* Click *Report* > *Save* to activate your report.
{column}
{column: width=70%}
!20save.png!
{column}
{section}
{section}
{column: width=30%}
*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.
{column}
{column: width=70%}
!10activate|thumbnail,border=1!
{column}
{section}
{section}
{column: width=30%}
*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.
{column}
{column: width=70%}
!x.png!
{column}
{section}

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