Installation Guides

Grid Builder Walkthrough

Welcome to the Grid Builder Walkthrough! In this guide you will learn about each part of the grid builder step-by-step and how each part is used.

The grid builder is basically an instructions sheet used by the dataflows that power our apps. Each column in the grid builder represents an instruction and each row represents a column from your data onto which these instructions will be applied. Let’s go through each column to find out what they do.

But before we do that, here is an example of the Grid Builder:

GridId GridName C SELECT FROM WHERE GROUP BY AS Include JoinType JoinCondition MAJIK FilterId FilterName FilterInputType FilterIsPrimary FilterIsPrimaryGroup Description
1 Scorecard kamaji_calendar(1, @DB)
101 Date KAMAJI Calendar Date 1
102 ThisYear This Fiscal Year 1 1 Date Range Single Select
103 LastYear Last Fiscal Year 1 1
104 ThisQuarter This Fiscal Quarter 1 1
105 LastQuarter Last Fiscal Quarter 1 1
106 ThisMonth This Fiscal Month 1 1
107 LastMonth Last Fiscal Month 1 1
108 FMStartDate FMStartDate
201 `Rep` Sales Scorecard – Sample Rep 1 INNER C101 = C205 2 Rep Multi Select 1
202 `Team` Team 1 3 Team Multi Select 1
203 `Region` Region 1 4 Region Multi Select 2
204 `Role` Role 1 5 Role Multi Select
205 `Date` Date
206 `Gross Sales` Gross_Sales 1
207 `Net Sales` Net_Sales 1
208 `New Clients` New_Clients 1
209 `Avg Deal Size` Avg_Deal_Size 1
210 `Quota Attainment` Quota_Attainment 1
211 `Upsell Count` Upsell_Count 1
212 `Renewal Sales` Renewal_Sales 1
213 `Product Sales` Product_Sales 1
214 `Leads` Leads 1
215 `Demos` Demos 1
216 ‘ ‘ PhotoUrl 1

 

It may seem like a lot of complicated instructions, but it gets much simpler as we break it down piece by piece.

1. The GridId and GridName columns

The grid builder takes your data and generates new tables formatted for the app that will use them. The GridId column is used to tell the grid builder when to start a new table, and the GridName column is where we name the tables that are generated. To fill out these columns, enter numbers in descending order on the first row where a new table is being created under GridId, and name your tables on the same row under the GridName column, as seen in the example below:

GridId GridName C SELECT FROM
1 opportunity 100 Amount Opportunity
CloseDate
CreatedDate
Id
IsClosed
IsWon
LastActivityDate
LastModifiedDate
Probability
StageName
OwnerId
AccountId
Name
200 Name User
Id
2 opportunity_history 300 CreatedDate Opportunity History
Id
OpportunityId
StageName

 

Notice that row 1 has a “1” under GridId to go along with “opportunity” in the next column, and row 16 has a “2” under GridId to go along with “opportunity_history” in the next column. That means that this Grid Builder will generate two tables, one called “opportunity” and the other called “opportunity_history”. The data referrenced from rows 1 to 15 will be included in the first table, and the data referrenced from row 16 to the end will be included in the second.

Protip: As long as the GridName column is filled out, you can actually leave the GridId column blank and the grid builder will automatically infer an Id number for each table.

Note: A lot of apps require specific names under the GridName field. Pay close attention to the app installation guides to know what table names need to be used in this column.

2. The "C" column

The “C” column is used to give a unique identifier to each row that the grid builder can reference. There are two different ways this column can be filled out, but keep in mind that the way you choose to fill this column out changes the way the JoinCondition column is filled out later.

Method 1: From top to bottom, starting with the number 101, increment by one and add the numbers in each following row as you go down. Ex. 101,102,103, etc. If you change source datasets in the FROM column, go to the next 100’s value and follow the same pattern. Ex. 201,202,203, etc. See the example below:

GridId GridName C SELECT FROM
1 opportunity 101 Amount Opportunity
102 CloseDate
103 CreatedDate
104 Id
105 IsClosed
106 IsWon
107 LastActivityDate
108 LastModifiedDate
109 Probability
110 StageName
111 OwnerId
112 AccountId
113 Name
201 Name User
202 Id
2 opportunity_history 301 CreatedDate Opportunity History
302 Id
303 OpportunityId
304 StageName

 

Method 2: On the first row where each source dataset is listed in the FROM column, insert a multiple of 100 as you go down. Ex. 100 for the first dataset referenced, 200 for the second, etc. See the example below:

GridId GridName C SELECT FROM
1 opportunity 100 Amount Opportunity
CloseDate
CreatedDate
Id
IsClosed
IsWon
LastActivityDate
LastModifiedDate
Probability
StageName
OwnerId
AccountId
Name
200 Name User
Id
2 opportunity_history 300 CreatedDate Opportunity History
Id
OpportunityId
StageName

 

We do this so that when we need to reference a certain row in other places in the grid builder (like the JoinCondition column) we can do so by referencing the “C” column value so that the grid builder knows what to look for. Ex. C101 = C201, or C100.Date = C200.Date etc. More on the JoinConditon column below.

Lastly, when we need to do dynamic date filtering in an app, as is the case with the Scorecard app family, the very first row of the “C” column is where we place a function call to a function called kamaji_calendar. We place the function call in the first row right after the filled-out GridId and GridName columns, but the rest of the cells on this row are left blank. See the example below:

GridId GridName C SELECT FROM WHERE GROUP BY AS
1 Scorecard kamaji_calendar(1, @DB)
101 Date KAMAJI Calendar Date
102 ThisYear This Fiscal Year
103 LastYear Last Fiscal Year
104 ThisQuarter This Fiscal Quarter

 

Calling the kamaji_calendar function generates a calendar with different date filter options that we can pull into the app. The function takes two parameters, one of which you can change to get more options. The calling syntax is as follows: kamaji_calendar(1, @DB). Do not change the second parameter (@DB). This parameter references the database schema of the dataflow and the dataflow will not function if it is changed. The first parameter, “1” in this case, can be changed as follows to generate different date ranges for the calendar:

Kamaji Calendar includes every day of the current year plus the number of trailing years specified in the first parameter. If the argument is an integer less than 6, the function looks back that number of years. If the argument is an integer greater than or equal to six, the function looks back using months instead of years. If the argument is a date or date time, the function includes all dates between the argument date and the last day of the current year. If the argument is the number 445, 454 or 544, the function generates a retail calendar of that type looking back two fiscal years.
Examples: kamaji_calendar([INTEGER, DATE, DATETIME, OR (445, 454 OR 544)], [DATABASE SCHEMA]);
kamaji_calendar(2, @DB);
kamaji_calendar(8, @DB));
kamaji_calendar(‘2011-11-12’, @DB));
kamaji_calendar(‘445’, @DB));

Check the index at the end of this guide to see what date filter options are generated with the calendar. These options can be referenced in the SELECT column when using dynamic date filtering as with the Scorecard app family.

3. The MYSQL columns (SELECT, FROM, WHERE, GROUP BY, AS)

These 5 columns are the central part of the Grid Builder and they behave just like they would when writing a MYSQL statement.

1. Under the SELECT column, in each row list the column headers from your source data as they appear in your data. Include any columns from your data that contain data you want to display in the app, columns you may want to filter by, and any columns you may need to use to join to other tables. If your column name contains spaces or symbols, enclose the column names in backticks (`).

2. In the FROM column, in only the first row in each set of column names from a single data source, include the name of the source data table.

3. In the WHERE column, you can add any MYSQL conditional statements to filter your data. Ex. `Region` != ‘Ohio’. This would filter out all rows where ‘Ohio’ was the value in a `Region` column. Only include your conditional statements in the same row where you have filled out the FROM column for each table. If you need two different filtered sets from the same dataset, you can treat the second filtered set as a new table and then join it back to the original using the Join columns.

4. In the GROUP BY column, you can add any MYSQL Group By statements. Here, simply list the columns you would like to group by separated by commas. Ex. date, rep, region. If you need two different groupings from the same dataset, you can treat the second grouping as a new table and then join it back to the original using the Join columns. Again, only include your group by statements in the same row where you have filled out the FROM column for each table.

5. In the AS column, enter the name that you would like to call the metric listed in the SELECT column of the same row. We advise avoiding the use of spaces and using either camel-case or underscores in your metric names. Also avoid numerics, (ex. Metric 1, Last 2 Years etc.), spelling out numbers instead. Both of these things have caused unexpected errors in our past experiences with the grid builder.

Here is an example of these columns filled out correctly:

C SELECT FROM WHERE GROUP BY AS Include
100 Amount Opportunity StageName = ‘StageTwo’ OwnerId Amount 1
CloseDate CloseDate 1
CreatedDate CreatedDate 1
Id Id 1
IsClosed IsClosed 1
IsWon IsWon 1
LastActivityDate LastActivityDate 1
LastModifiedDate LastModifiedDate 1
Probability Probability 1
StageName StageName 1
OwnerId OwnerId 0
AccountId AccountId 0
Name AccountName 1

 

Protip: You can use CASE statements in the SELECT column to produce some awesome results. For example, you could use a case statement to create your own custom date range: CASE WHEN `Date` >= (CURDATE() – INTERVAL 30 DAY) and `Date` <= CURDATE() THEN 1 ELSE 0 END – This would create a last thirty days date range.

Note: A majority of apps have certain fields that are required in order to function. In these cases, the metric names under the AS column need to be named something very specific. Pay close attention to the app installation guides to see which fields are required and what they need to be named.

4. The Include column

The Include column is used to signify which columns you would like to show in the final data. A value of “1” means to include and “0” will exclude a column. Generally, you will want to include most of your columns. Exceptions are when you only use columns to join and don’t want them to show in the app, or if you join and want to include the column, you would include one and not include the other so you don’t get duplicate data.

Protip: If you leave the whole Include column blank, all entries will default to “1”.

5. The JoinType and JoinCondition columns

These two columns are where we give the grid builder instructions on how to do the joining of multiple tables. Fill out these two columns on the same row where you start a new table that needs to be joined to one you’ve already included. The first table you list in the grid builder will not use these columns and should be used as the base table that other tables are joined onto.

The JoinType column supports all types of MYSQL joins. Ex. INNER, LEFT, RIGHT, CROSS, etc. Generally when joining to a calendar, like the kamaji_calendar for example, you will want to use INNER, and then use LEFT for when you are joining additional tables. Omit the JOIN keyword in this column. The joins behave just like MYSQL joins so if you need a special join, feel free to play around!

The JoinCondition column behaves just like the ON part of a MYSQL join statement, but to reference the columns we want to join we have to use the “C” column to reference the columns. For example if you used method 1 for the “C” column above, you might enter C101 = C201. If you used method 2, you might enter C100.Date = C200.Date. See the example below:

C SELECT FROM WHERE GROUP BY AS Include JoinType JoinCondition MAJIK
kamaji_calendar(1, @DB)
101 Date KAMAJI Calendar Date 1
102 ThisYear This Fiscal Year 1
103 LastYear Last Fiscal Year 1
104 ThisQuarter This Fiscal Quarter 1
105 LastQuarter Last Fiscal Quarter 1
106 ThisMonth This Fiscal Month 1
107 LastMonth Last Fiscal Month 1
108 FMStartDate FMStartDate
201 `Rep` Sales Scorecard – Sample Rep 1 INNER C101 = C205
202 `Team` Team 1
203 `Region` Region 1
204 `Role` Role 1
205 `Date` Date
206 `Gross Sales` Gross_Sales 1

 

Note: If the “C” column was set up using the other method, the JoinCondition value above would be “C100.Date = C200.Date”.

6. The MAJIK column

The MAJIK column has been depreciated and doesn’t currently serve any use for the apps, but I’m sure it was really majikal at one point. The dataflow procedures still look for it though, so keep the column in the grid builder, but leave all rows underneath it blank.

7. The Filter columns (FilterId, FilterName, FilterinputType, FilterIsPrimary, FilterIsPrimaryGroup)

The filter columns are used to tell the grid builder which columns you want to use as filters, what type of filter you want to use, and whether the filter is going to be displayed as a primary group in certain apps. Fill out these fields on the rows where you have selected the columns from your source data that you want to use a filters.

1. The FilterId column is used to assign a unique Id to each filter. On each row that you want to use as a filter, assign a number to each filter from top to bottom incrementing from 1 until each filter has an Id. Ex: 1,2,3 etc.
Note: Single Select filters use multiple rows in the grid builder. Use the same Id on all rows used by a Single Select filter.

2. The FilterName column is where you can choose the name of the filter that will display in the app. For Single Select filters, only include the name on the first row included in the filter rows.

3. The FilterInputType column accepts two options: “Single Select” or “Multi Select”. Single select means that only one filter option can be chosen at a time, and multi select means that multiple filter values can be chosen simultaneously. Here is how to configure each type:

Multi Select: Filling out the FilterId, FilterName, and entering “Multi Select” under FilterInputType on a single row will make that row a filter for the app. The values that can be chosen in the filter are the individual values of the column referenced in the source data.

Single Select: Single Select filters require multiple columns in the source data. The columns that a Single Select filter references can only contain a 0 or 1 for their values in each row (Boolean), 0 meaning that the row in the source data is not included in the filter view and 1 meaning that the row is included.

For example, lets say we wanted to make a custom region-grouping filter and there were the following regions in the data: regionA, regionB, regionC, regionD, and regionE. Say we wanted three filter options: 1. regionA and regionB, 2. regionC and regionD, 3. regionE and regionA. We could use a MYSQL CASE statement beforehand to generate three new columns in the source data for these filter options. Ex. CASE WHEN `Region` IN (‘regionA’,’regionB’) THEN 1 ELSE 0 END as ‘GroupOne’. (Then two more like this for ‘GroupTwo’ and ‘GroupThree’)

We would then add these three new columns under the SELECT column in the grid builder, one row in the grid builder for each new column. We would then use the same Id for all three rows under the FilterId column, choose a name for the filter under the FilterName column (only on the topmost row of the three rows) and enter “Single Select” (again only on the topmost row of the three rows). This would create a single select filter where only one of the values, “GroupOne”, “GroupTwo”, or “GroupThree” could be chosen at a time. The data would then be filtered according to which rows had 1 values in the filter view option that was selected.

See the filter set-up example below:

AS Include JoinType JoinCondition MAJIK FilterId FilterName FilterInputType
Date 1
This Fiscal Year 1 1 Date Range Single Select
Last Fiscal Year 1 1
This Fiscal Quarter 1 1
Last Fiscal Quarter 1 1
This Fiscal Month 1 1
Last Fiscal Month 1 1
FMStartDate
Rep 1 INNER C101 = C205 2 Rep Multi Select
Team 1 3 Team Multi Select
Region 1 4 Region Multi Select
Role 1 5 Role Multi Select

 

Note: Only certain apps support the single select option. As of writing, the apps include the Scorecard family apps and the Google Maps Pinboard.

4. The FilterIsPrimary column is only used on certain apps where the primary view can be chosen from any column in the source data. As of the time of this writing, that is only the apps in the Scorecard family. To use this column place a “1” in this column in the row that represents the column that will be your primary view in the app. Ex. Sales Representative or Store etc.

5. Similarly to the previous column, the FilterIsPrimaryGroup column is only used in apps in the Scorecard family currently. This column is used when, in addition to the primary view, you want additional primary view options in the app. For example, your primary view is by Store but you want to view your data by district and region as well. To add those as main view options, add a number in this column starting with 1 and incrementing as you go from top to bottom on each row that you want to add as an additional primary view. Do not include the row you chose under the FilterIsPrimary column here.

Here is an example of using these two columns:

FilterId FilterName FilterInputType FilterIsPrimary FilterIsPrimaryGroup
1 Date Range Single Select
1
1
1
1
1
2 Rep Multi Select 1
3 Team Multi Select 1
4 Region Multi Select 2
5 Role Multi Select

 

Note: In the example above, Rep would be the primary filter view and Team and Region could be selected as optional primary views. All filters that have a number under FilterId will still display as standard filters, even if they are used as a primary group.

8. The Description column

The description column is included for documentation purposes. Many of the installation guides give descriptions here of what app-specific metrics they are looking for. You may fill them out with whatever information you would like to. The grid builder dataflow ignores this column.

Protip: if multiple people will be looking at and changing the grid builder, this is a great place to leave change notes, metric descriptions, etc.

That's It!

Those are the different columns of the grid builder and how to use them! If you have any questions or suggestions for this guide, feel free to reach out to the Domo AppLabs team at DomoAppDeployment@domo.com. Thanks for Reading!

INDEX

Kamaji_calendar date options

Below is a list of all of the date filter options available in the kamaji calendar. These option names would be included in the SELECT column where columns are being selected from kamaji_calendar. This is used in apps from the Scorecard family as well as any app that uses dynamic date filtering.

Here are the values:

KEY:
C stands for Calendar
F stands for Fiscal
D stands for Day
W stands for Week
M stands for Month
Q stands for Quarter
Y stands for Year
O stands for “Of”

This field is used to join datasets by date, includes all dates in the range:
Date

These are numeric values representing their value. For example: if the year of `Date` is 2017 then CY = 2017. If the month of `Date` is January then CM = 1:
FY
CY
FQ
CQ
FM
CM
FWOY (ie. Fiscal week of Year 1-52)
CWOY
FWOQ
CWOQ
FWOM
CWOM
FDOY
CDOY
FDOQ
CDOQ
FDOM
CDOM
FDOW
CDOW

These can be included if you need the specific date:
FYStartDate
FYEndDate
CYStartDate
CYEndDate
FQStartDate
FQEndDate
CQStartDate
CQEndDate
FMStartDate
FMEndDate
CMStartDate
CMEndDate
FWStartDate
FWEndDate
CWStartDate
CWEndDate

These can be included as date range filter options. They are all boolean columns where 1 means the date is included in the range and 0 means that it is not:
CurrentFY
PreviousFY
2FYAgo
CurrentAndPreviousFY
CurrentAndPrevious2FY
CurrentCY
PreviousCY
2CYAgo
CurrentAndPreviousCY
CurrentAndPrevious2CY
CurrentFQ
PreviousFQ
2FQAgo
CurrentAndPreviousFQ
CurrentAndPrevious2FQ
CurrentCQ
PreviousCQ
2CQAgo
CurrentAndPreviousCQ
CurrentAndPrevious2CQ
CurrentFM
PreviousFM
2FMAgo
CurrentAndPreviousFM
CurrentAndPrevious2FM
CurrentCM
PreviousCM
2CMAgo
CurrentAndPreviousCM
CurrentAndPrevious2CM
CurrentFW
PreviousFW
2FWAgo
CurrentAndPreviousFW
CurrentAndPrevious2FW
CurrentCW
PreviousCW
2CWAgo
CurrentAndPreviousCW
CurrentAndPrevious2CW
ThisYear
LastYear
ThisQuarter
LastQuarter
ThisMonth
LastMonth
ThisWeek
LastWeek
ThisQuarterLastYear
ThisMonthLastYear
ThisWeekLastYear
Today
Yesterday
Last7Days
Last30Days
Last90Days
Last120Days

And here is an example of where to include them in the app:

C SELECT FROM WHERE GROUP BY AS Include JoinType JoinCondition MAJIK FilterId FilterName FilterInputType
kamaji_calendar(1, @DB)
101 Date KAMAJI Calendar Date 1
102 ThisYear This Fiscal Year 1 1 Date Range Single Select
103 LastYear Last Fiscal Year 1 1
104 ThisQuarter This Fiscal Quarter 1 1
105 LastQuarter Last Fiscal Quarter 1 1
106 ThisMonth This Fiscal Month 1 1
107 LastMonth Last Fiscal Month 1 1
108 Last30Days Last Thirty Days 1 1
109 CurrentAndPreviousFQ Current And Previous FQ 1 1
110 CurrentAndPreviousCY Current And Previous CY 1 1
111 2FWAgo Two Fiscal Weeks Ago 1 1
112 FMStartDate FMStartDate