When you open the template workbook, you will notice four tabs listed - Initialization, Triggered, UDTF, and Calculator. What follows is a description of each tab and of the fields in each of them.
Initialization Tab
- Runs when you hit the Execute button in the FlowObjects SQL Service section.
- An ordered list of actions
- Directs the database to load specific information into specific cells in sheets in the workbook
- Fields:
- Order: the order the queries will be ran in. Each is completed before the next starts
- Query Name: Name according to what the query is doing
- Result Destination: Lists the tab and the cells that the data in the query will be loaded to. See the Results Destination Calculator section below.
- Clear Range Before pasting: field is going away (True or false?)
- Max Rows to Retrieve: If left blank, will bring in all the data in the query
- Column Headers: True if you want them, False if not. Often people will leave false so they can add more descriptive headers than what is in the table on the DB
- Query Enabled: allows the user to turn the query on and off incase you have a query you are working on and don’t want it foul up things when running the other queries (True or false?)
- Shows Sums of Column(s): used if the column has numbered data that the user would want to see the sum of
- SQL Query: where the SQL Query is loaded. There are a few MS Excel caveats based on some formatting requirements. Is able to use UDTFs to parameterize queries sent to the database.
- Last Run Query Status: “Success” if it ran
- Last Run Time: DTG for the last time the query ran.
- The power of this tab: can take the results of one query and use them to construct a another query. Allows data driven cascading SQL that mutates based on the data that you get back
- Example: often people are looking at hot spots, something that changes often. Say you wanted to pull the top 10, something that probably changes every day. You may want to see some details that are product centric based on top ten returns. This would make the products change every day also.
- So you could have the top ten returns grouped together by what is being returned
- This could go into the next query that pulls back a list of products, etc
Triggers Tab
- Is for dynamic use
- Enables building very elaborate analysis in Excel through the use of triggers based on a cell’s value
- If that cell gets triggered, the query executes
- You can write a group of queries that are fairly complex and are all tied to the same trigger.
- You can make them all run in a cascading order to pull back fetch data
- You can work with very complex data sets, but because you can ask for them in discrete pieces, you can simplify and navigate more easily and all in MS Excel.
- Fields
- Group Order: the order the queries will run
- Trigger Query Name: based on what the trigger does
- Trigger Source: the tab and cell that the trigger is located
- State: The current state of the trigger (normally 0 or 1). It is copied from the cell where the trigger is located. Do not change this
- Result Destination: where the data the trigger trips will land.
- Clear Range Before Posing: field is going away
- Max Rows to Retrieve: If left blank, will bring in all the data in the query
- Column Headers: True if you want them, False if not. Often people will leave false so they can add more descriptive headers than what is in the table on the DB
- Query Enabled: allows the user to turn the query on and off in case you have a query you are working on and don’t want it foul up things when running the other queries
- Shows Sums of Column(s): used if the column has numbered data that the user would want to see the sum of
- SQL Query: where the SQL Query is loaded. There are a few MS Excel caveats based on some formatting requirements. Is able to use UDTFs to parameterize queries sent to the database.
- Last Run Query Status: “Success” if it ran
- Last Run Time: DTG for the last time the query ran.
User Defined Table Functions (UDTF) Tab
- Used to accept parameters
- A UDTF key in the database is a view with parameters that is packaged up into a function
- Just like in MS Excel, when you call a function or command, you have the name of the command and in parentheses you put the parameters.
- Fields:
- NDTF Name: typically made up using the DB, the schema and the function
- Parameter Name: list of the parameters of the function
- Value: These come from the dropdown in the list
- Code: automatically generated (do not overwrite)
- These parameters us used to generate the code in the yellow box at the bottom which can be used in the SQL field in the Initialization and Triggered tabs. This code does analysis based on the parameters selected.
Calculator tab (for Results Destination Field)
- This field is used to determine the value that will go in the Initialization Tab, Results Destination field.
- To generate the value, follow the instructions at the top of the tab