Page Comparison

{anchor:top}
{toc: class=contents}
h2. Introduction
{styleclass: Class=topLink}[top|#top]{styleclass}
In this tutorial, we will walk through setting up an Intersect sub query. A good way to think about *Intersects* is - use them if you need a list of something that appears in two seperate lists.

In this scenario our main report will have a list of athletes that attended camps in the current year. We will then create a list of athletes from the previous year, and only return a list of athletes that are in both. This will effectively give us a list of athletes that are return customers from last 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. Intersect Query
{styleclass: Class=topLink}[top|#top]{styleclass}
h3. Create Intersect
{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 *Intersect* and Style to *Basic*.

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

h3. Configure Intersect
{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 used from the master query will be located, matching the ID field.

*10.* Set the *To Match* field on the *Sub Query* to be the *Athlete ID* field. This will list all Athete ID values to be matched to 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 a certain date range.

*12.* Change the name of the Sub Query to *Last Year*.

*13.* Click *Save* to complete the *Intersect*.
{column}
{column: width=70%}
!06configureIntersect.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 filter to be Year *Between*

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

*22.* Select the *This Calendar Year* option and click the *Submit* button.

*23.* Close the Configure Filters panel.
{column}
{column: width=70%}
!09intersectFilter.png|thumbnail,border=1!
{column}
{section}

h2. Save
{styleclass: Class=topLink}[top|#top]{styleclass}
{section}
{column: width=30%}
*x24.* Click *Report* > *Save* to activate your report.
{column}
{column: width=70%}
!xsave20save.png!
{column}
{section}
{section}
{column: width=30%}
*x25.* Set the *Name* of your report to *Intersect Sub Query Tutorial*.

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

*x27.* Select *Tutorial* as the *Category*.

*x28.* Select *Training* as the *Sub Category*.

*x29.* Click *Activate* to finish.
{column}
{column: width=70%}
!x10activate.png|thumbnail,border=1!
{column}
{section}
{section}
{section}
{column: width=30%}
*x30.* You now have an active Intersect Sub Query report. 
{column}
{column: width=70%}
!x11active.png|thumbnail,border=1!
{column}
{section}

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