Building a Basic Query

!Prerequisites:
1. Connecting TAD to the Server

The core structure of every Table and Report is a SQL Statement. When building a Table or Report there are two requirements:

1. Always start on the From part so you can select your main table. This must be first because the other parts are populated based on the main table.
2. You must have Selected Columns under the Select part in order to have Available Columns under the Group By part.

Other than that, you have the ability to change the following parts of the SQL Statement:

This example will walk through select your main table, selecting columns and adding a data filter.

1. Start by clicking the Add Report button on the ribbon.

From Part

2. That will open the TAD Query Editor form starting on the From view showing the available tables.

*Reports can also download data from stored procedures. Please follow the Add Report Using Stored Procedure topic if you’re interested in that.

3. In the Search Tables box, type “TAD Sandbox” to filter the list.

4. Select the table and the Save Changes and Cancel Changes buttons will appear. Click the Save Changes button.

*Other ways to select the table:
1. Double-clicking on the table will save it and take you to the Query view bypassing the Save Changes button.
2. Selecting the table and pressing your Enter key will save the table to the TAD Tab and start the Download action.

5. You will now be on the Query view where you will see the Tab Name and basic SQL Statement. Every time you adjust a part the SQL Statement will be updated.

Select Part

6. First, we will choose what columns we see on the report. Click the Select button on the left side.

7. You should see the Select view below. The left list contains the Available Columns and the right list contains the Selected Columns which is what you see when downloading. Use the Alias field to rename columns and the Format field to specify how the column will be formatted after download.

To get columns from the Available to Selected lists you have a few options:

• Select a column and then click the single blue arrow right button.
• Double-click on a column and it will be added to the bottom of the right side list.
• Select a column and drag and drop it to the right side list.

8. Click the double blue arrows left to clear the Selected Columns.

9. Select the tadvSandbox.Co column and then the single blue arrow right button to add it to the right side.

10. Double-click on the tadvSandbox.Description column and it should be added to the right side.

11. Select the tadvSandbox.State column and drag and drop it to the bottom of the right side.

*You can also drag and drop columns on the right side list to reorganize them.

12. You can rename columns using the Alias field. Select the tadvSandbox.BudgetAmt column and enter Budget Amount in the Alias field before clicking the Add button.

13. With all columns added, click the Save Changes button to go back to the Query view.

14. The SQL Statement has been updated to now only include the columns you selected.

Where Part

15. Next, we will choose how we want to filter the data. Click the Where button on the left side.

16. You should see the Where view below.

Adding a Where condition requires three things.

• Select an Available Column to filter
• Select an Operator button
• Enter a Value or select the button to generate the parameter name

17. Select the tadvSandbox.BudgetAmt Column, the Operator and enter $50,000 for the value. Click the Add button.

18. You should see the new condition in the Where Clause box.

19. With all conditions added, click the Save Changes button to go back to the Query view.

20. The SQL Statement has been updated to include the new data filter.

21. Click the Download button to download the Report.

22. The TAD Parameters form should now appear showing your Tab Name, the added data filter and the (Companies) parameter.

*Parameters surrounded by parentheses, such as (Companies), can accept multiple values separated by commas. For example, entering “1,2,3” for (Companies) would download data for all three.

23. Most parameters have an associated lookup. To access the lookup either double-click in the parameter field or click in the field and press your F4 key.

24. The TAD Lookup form appears providing a list of Companies.

*Month and Date parameters will display the TAD Calendar form instead of TAD Lookup form.

25. You can filter the list by using the Column and Search fields. Entering “0” into the Search field filters the lookup list.

26. Select Company 0 for the TAD Sandbox Company and click the Save button. You can also double-cliick on values to select them.

*You can also use your Ctrl key to select multiple values for any parameters that are surrounded by parentheses, such as (Companies).

27. That will populate the (Companies) parameter with the value of 0.

28. Click the Download button to download the Table.

29. The TAD Progress form should now appear showing you the status of the Download action. When the download finishes the form will show you if it was successful or failed.

30. You should now be on the My Sandbox tab and it should be green.

*When an Action finishes the tab will be green if successful and red if it failed.

31. The My Sandbox Report tab should look like the image below. This represents the Data layout which contains no formatting.

32. To download the table again, you have two options:

Option 1 – Using Download Button on Ribbon

Option 2 – Using Download Action Button

When you use this button you will be prompted with the message below. Click Yes to download the Table again with the same parameters or No to enter them again.