The Summary Group Options screen which you access from the Query Wizard allows you to set specific behaviour for each group field you have added to the Group by box in the Query Wizard.

 

Setting these options is useful for a number of different things.

 

Firstly it allows you to transform you date fields from the original source date into a year, month, week or quarter. This can be useful for allow you to get summary totals by year, month, week or quarter.

 

Secondly it allows you to use Top N options to focus in on areas that are of most interest. Top N allows you to select the top (or bottom) number of records from the summary totals that have been calculated. So for example your best customers or products or your worst customers or products.

 

Thirdly it allows to identify trends in your data. So for example you might want to see which customers have seen the greatest growth in revenue over a particular period or which products have seen the greatest variability in volume month to month over the last few years

 

The screen will be displayed with the Field you selected in the Query Wizard in the Group by list displayed at the top along with it's data type.

 

The screen displays differently depending on the data Types for the select group column.

 

For more details on what you can set of each data type please refer to the individual topics for Group Options or Date Options.

 

Once you have finished your changes you can click the OK button to apply the changes or click the Cancel button to discard them.


Summary - Group Options


The Group Options tab allows you to set Top N settings for the column you have selected in the Group by list on the Query Wizard.

 

clip564

 

The first thing you need to do in this screen is to select what you want to create the group for. These options are listed below.

 

Option

Description

All

Select All if you want to have no Top N options set on this Group Column. This will be the default for the Column and this can be used to remove a Top N option if you no longer require it on the Column.

Top N

Setting this option limits the data to just the Top N records for this group based on the Summary Column value selected in the Based On drop down list and the value entered in the Where N is box. So, for example, if you entered 5 for the value in the Where N is box, the group column is Country and the Based On column selected is Sum of Extended Price then you will get results from your query for that group of the top 5 counties based on the Sum of the Extended Price.

Bottom N

Setting this option limits the data to just the Bottom N records for this group based on the Summary Column value selected in the Based On drop down list and the value entered in the Where N is box. So, for example, if you entered 5 for the value in the Where N is box, the group column is Country and the Based On column selected is Sum of Extended Price then you will get results from your query for that group of the bottom 5 counties based on the Sum of the Extended Price.

Top Percentage

Setting this option limits the data to just the Top N % of records for this group based on the Summary Column value selected in the Based On drop down list and the value entered in the Where N is box. So, for example, if you entered 10 for the value in the Where N is box, the group column is Country and the  Based On column selected is Sum of Extended Price then you will get results from your query for that group of the top 10% of counties based on the Sum of the Extended Price.

Bottom Percentage

Setting this option limits the data to just the Bottom N % records for this group based on the Summary Column value selected in the Based On drop down list and the value entered in the Where N is box. So, for example, if you entered 10 for the value in the Where N is box, the group column is Country and the  Based On column selected is Sum of Extended Price then you will get results from your query for that group of the bottom 10 % counties based on the Sum of the Extended Price.

 

 

clip566

 

Option

Description

Include Others

The Include Others option allows you to decide whether you want to include data for the records which do not get included in the Top N that you have set. If you check the Include Others box then an additional record will be added to your data which has the label you specify in the Other Label box. The value of that record will be the total of Column selected in the Based On drop down down list for all the records which do not get included in the specified Top N.

Include Ties

The Include Ties option allows you to decide if you want to include records whose values are the same in your results. So for example if you set a Top N to give you just the Top 10 Countries and the Country in position10 and the Country in position 11 have the same value then setting the Include Ties will mean you see both the Country in position 10 and the country in position 11 in your results. If you set Include Ties to be off then you would only see the Country in position 10 in your results.

 

Once you have set your Group Options click one of the other tab available (Date Options) or you can click OK to apply your changes or Cancel to discard them.

 


Summary - Date Options


The Date Options tab will only be displayed if the column you have selected in the Group by list on the Query Wizard is a date based column. That is if it's source data type is date, datetime or datetime-tz.

 

Date Columns

 

For a source column that had a date data type you will have a smaller number of options than for the datetime or datetime-tz data types that will be discussed later in this topic.

 

clip562

 

The options you have available for a date column are:

 

Group Date

Description

Year

This transforms the date column into a Year value. How this will be calculated will depend on the Starting Month selected. This will default to January so by default the Year will just be the year portion of the date from the field. However setting the Starting Month is useful if you would like the Year to be calculated so that it mirrors your financial year rather than just the normal calendar.

Month

This transforms the date column into a Month value. How this will be calculated will depend on the Starting Month selected. This will default to January so by default the Month will just be the month portion of the date from the column. However setting the Starting Month is useful if you would like the Month to be calculated so that it mirrors your financial months rather than just the normal calendar.

Year/Month

This transforms the date column into a Year and Month value. The format of this will be YYYY.MM. How this will be calculated will depend on the Starting Month selected. This will default to January so by default the Year and Month will just be the year portion of the date from the field followed by the month portion of the date from the field. However setting the Starting Month is useful if you would like the Year and Month to be calculated so that it mirrors your financial year rather than just the normal calendar. This Year and Month option is particularly useful when looking for trends in data month to month over a period of a number of years.

Quarter

This transforms the date column into a Quarter value. How this will be calculated will depend on the Starting Month selected. This will default to January so the Quarters by default will be calculated as Q1 Jan - Mar, Q2 Apr - Jun, Q3 Jul - Sep, Q4 Oct - Dec. However setting the Starting Month is useful if you would like the Quarters to be calculated so they mirror your own financial quarters rather than just the normal calendar.

Week

This transforms the date column into a Week value. How this will be calculated will depend on the Starting Week and Starting Weekday selected. This will default to week 1 and to Sunday. The Starting Week should be the week number on the calendar of the first week of your financial year. The Starting Weekday should be the day on which each of your financial weeks begins. Setting the Starting Week and Starting Weekday is useful if you would like the Week to be calculated so that it mirrors your financial weeks rather than just the normal calendar weeks.

 

Date Time and Date Time TZ Column

 

For a datetime or datetime-tz column you have the same options as listed above for a date column but have a number of additional options available too.

 

The options you have available for a datetime or datetime-tz column are:

 

Group Date

Description

Day

This transforms the datetime or datetime-tz column into a date value. This is useful if you want to report by Day but your source column is a datetime or datetime-tz field.

Hour

This transforms the datetime or datetime-tz column into a datetime value that contains the date and the just the hour. The minute, second and millisecond are discarded. This is useful if you want to group your data by hour throughout a day or a number of days.

Minute

This transforms the datetime or datetime-tz column into a datetime value that contains the date, the hour and the minute. The second and millisecond are discarded. This is useful if you want to group your data by hour and minute throughout a day or a number of days.

Second

This transforms the datetime or datetime-tz column into a datetime value that contains the date, the hour, the minute and the second. The millisecond is discarded. This is useful if you want to group your  data by hour, minute and second throughout a day or a number of days.

 

Once you have set your Date Options click one of the other tab available (Group Options ) or you can click OK to apply your changes or Cancel to discard them.