Data Shaping

summarization to obtain numeric values that should be displayed within a dashboard item, dashboard calculates a summary function against the specified measure this is applied at the element levle see docid\ ub1khl1 2m24pch1oz9uh for calculating at the data model levle summary function types the following summary functions are available count the number of values (excluding null and dbnull values) this is the only summary type that can be calculated against non numeric data count distinct the number of distinct values sum the sum of the values min the smallest value max the largest value average the average of the values stddev an estimate of the standard deviation of a population, where the sample is a subset of the entire population stddevp the standard deviation of a population, where the population is the entire data to be summarized var an estimate of the variance of a population, where the sample is a subset of the entire population varp the variance of a population, where the population is the entire data to be summarized median the median of the values (excluding null and dbnull values) a median is the number separating the higher half of a value range from the lower half changing summary type by default, dashboard calculates sum for numeric measures and count for measures that contain another type of data you can change the summary function type for numeric measures to do this in the designer, invoke the data item menu and select the desired summary type less common summary types are organized in the more submenu grouping the dashboard designer allows you to group dimension values and display summaries for entire groups rather than individual values you can arrange dimension values in groups of different sizes by specifying the appropriate group interval for instance, date time values can be grouped by years, months, quarters, etc this topic lists the supported text and date time group intervals, and describes how to change the group interval text group intervals string values support the following grouping intervals no grouping each value is displayed "as is" alphabetical values are grouped alphabetically (e g , a, b, c, z) date time group intervals date time values support the following group intervals group interval description examples year values are grouped by the year 2010, 2011, 2012 quarter values are grouped by the quarter q1, q2, q3, q4 month values are grouped by the month january, february, march, december day values are grouped by the day of the month 1, 2, 3, 31 hour values are grouped by the hour 0, 1, 2, 23 minute values are grouped by the minute 0, 1, 2, 59 second values are grouped by the second 0, 1, 2, 59 day of the year values are grouped by the day of the year 1, 2, 3, 365 day of the week values are grouped by the day of the week sunday, monday, tuesday, saturday week of the year values are grouped by the week of the year 1, 2, 3, 52 week of the month values are grouped by the week of the month 1, 2, 3, 4, 5 week year values are grouped by the date of the first day of the week (uses culture settings) 7/1/2018, 7/8/2018, 7/15/2018, 11/4/2018, 11/11/2018, 11/18/2018, month year values are grouped by the year and month january 2012, february 2012, december 2012, january 2013, quarter year values are grouped by the year and quarter q3 2012, q4 2012, q1 2013, q2 2013, day month year values are grouped by date 3/4/2012, 3/5/2012, 3/6/2012, date hour values are grouped by date with the hour value 3/4/2012 0 00 am, 3/4/2012 1 00 am, 3/4/2012 2 00 am, date hour minute values are grouped by date with the hour and minute values 3/4/2012 0 00 am, 3/4/2012 0 01 am, 3/4/2012 0 02 am, date hour minute second values are grouped by date with the hour, minute and second values 3/4/2012 0 00 00 am, 3/4/2012 0 00 01 am, 3/4/2012 0 00 02 am, exact date each value is displayed "as is" 2009, q2 2009, 6/15/2009 1 45 30 pm, changing group interval to specify the group interval in the designer, invoke the data item menu and select the desired group interval less common group intervals are organized in the more submenus sorting changing sort order the sort order of dimension values is indicated with an arrow to change the sort order in the designer, click the data item you can also toggle sorting from the data item menu sorting by measure values dashboard allows you to sort dimension values by summary values calculated for a specific measure to enable sorting by measure in the designer, use the sort by submenu in the dimension's menu filtering the dashboard designer allows you to filter data in the dashboard items or apply filters to a specific measure dashboard item filter filters that apply to a docid 6vxaacvnlindkdav9ddvo affect only this item to add filters, select the target dashboard item and do one of the following click the edit filter button in the ribbon menu's data tab right click a dashboard item and select edit filter from its context menu this invokes the filter editor dialog that allows you to build complex filter criteria to clear the applied filter, select clear from the dashboard item's context menu or click the clear button in the data ribbon tab measure filter you can apply filters to individua measures if you create multiple measures that only differ in applied filters, you can compare values calculated over different date time periods or against different categories select a dashboard item and right click a measure to be filtered select edit filter from its context menu this invokes the filter editor dialog where you can specify filter criteria the filtered measure has the filter icon to clear the applied filter, select clear from the measure's context menu \[!note] the measure filter is technically an expression that uses the filter(summaryexpression, filtercriteria) function, where summaryexpression is the measure to be filtered and filtercriteria is the filter visible data filter you can specify a visible data filter to limit displayed data this filter type does not filter underlying data used in calculations or intermediate level aggregations click the set visible data filter button in the ribbon menu's data tab to invoke the visible data filter editor, where you can set a filter condition for example, a grid dashboard item has 35 rows and displays sales percentages the image below shows the difference between filters (the filter condition is the same) dashboard item filter sales percentages are recalculated based on the visible data visible data filter sales percentages remain the same because this filter type does not affect calculations pass parameter values you can use the filter editor to filter a dashboard item according to the current parameter value see the following topic for details top n the top n feature allows you to display only a limited number of values that correspond to the highest or lowest values of a particular measure to display the top values in a dimension, select top n from the data item menu this invokes the top n values dialog in this dialog, check the enabled check box and specify the following settings setting description mode specifies whether top or bottom values should be displayed count the number of values to be displayed measure the parameter that will determine the top or bottom value show "others" value if enabled, all values that are not the top/bottom values are consolidated in the "others" value formatting data dashboard allows you to customize various data format settings for numeric and date time values formatting numeric values to specify a format for numeric values, select format from the data item menu this invokes the numeric format window in the format type field, select the required format type format type description auto format settings are automatically determined based on the data type general converts a number to the most compact of either fixed point or scientific notation, depending on the type of the number number converts a number to a string of the " d,ddd,ddd ddd…" form where " " indicates a negative number symbol (if required), "d" indicates a digit (0 9), "," indicates a group separator, and " " indicates a decimal point symbol currency converts a number to a string that represents a currency amount to learn about currency formatting specifics, scientific converts a number to a string of the " d ddd…e+ddd" or " d ddd…e+ddd" form where each "d" indicates a digit (0 9) percent multiplies a number by 100 and converts it to a percentage string other format settings are in effect for only specific format types setting description format types unit the unit to which values should be converted number, currency precision the number of fractional digits that should be displayed scientific, percent currency defines the currency sign and format settings that should be used to display currency values to learn about currency formatting specifics, see the section of this document currency currency culture for currencies used in a region with several cultures, specifies the culture that defines format settings currency include group separator specifies whether or not separators should be inserted between digit groups number, currency, percent formatting date time values to specify a format for date time values, use the format submenu in the data item menu \[!note] specific group intervals do not have format options this means that corresponding values can only be presented in a single manner the format submenu is not displayed for such group intervals the following list shows format types by group interval year full the full year pattern (example 6/15/2017 1 45 30 pm > 2017 (en us)) abbreviated the year from 00 to 99 (example 6/15/2017 1 45 30 pm > 17 (en us)) quarter full the full quarter pattern (example 6/15/2017 1 45 30 pm > q2 (en us)) numeric the quarter from 1 through 4 (example 6/15/2017 1 45 30 pm > 2 (en us)) month full the full name of the month (example 6/15/2017 1 45 30 pm > june (en us)) abbreviated the abbreviated name of the month (example 6/15/2017 1 45 30 pm > jun (en us)) numeric the month from 1 through 12 (example 6/15/2017 1 45 30 pm > 6 (en us)) hour long long hour pattern, 12 hour format (example 6/15/2017 1 45 30 pm > 1 00 pm) short short hour pattern, 24 hour format (example 6/15/2017 1 45 30 pm > 13) day of week full the full name of the day of the week (example 6/15/2017 1 45 30 pm > monday (en us)) abbreviated the abbreviated name of the day of the week (example 6/15/2017 1 45 30 pm > mon (en us)) numeric the day of the week from 1 through 7 (example 6/15/2017 1 45 30 pm > 2 (en us)) day month year long long date pattern (example 6/15/2017 1 45 30 pm > monday, june 15, 2017 (en us)) short short date pattern (example 6/15/2017 1 45 30 pm > 6/15/2017 (en us)) date hour long long date pattern, long hour pattern (example 6/15/2017 1 45 30 pm > monday, june 15, 2017 1 00 pm (en us)) short short date pattern, long hour pattern (example 6/15/2017 1 45 30 pm > 6/15/2017 1 00 pm (en us)) time only long hour pattern (example 6/15/2017 1 45 30 pm > 1 00 pm (en us)) date hour minute long long date pattern, long time pattern (example 6/15/2017 1 45 30 pm > monday, june 15, 2017 1 45 pm (en us)) short short date pattern, long time pattern (example 6/15/2017 1 45 30 pm > 6/15/2017 1 45 pm (en us)) time only long time pattern (example 6/15/2017 1 45 30 pm > 1 45 pm (en us)) date hour minute second long long date pattern, long time pattern (example 6/15/2017 1 45 30 pm > monday, june 15, 2017 1 45 30 pm (en us)) short short date pattern, long time pattern (example 6/15/2017 1 45 30 pm > 6/15/2017 1 45 30 pm (en us)) time only long time pattern (example 6/15/2017 1 45 30 pm > 1 45 30 pm (en us)) the list below illustrates format types related to the exact date group interval year full the full year pattern (example 6/15/2017 1 45 30 pm > 2017 (en us)) abbreviated the year from 00 to 99 (example 6/15/2017 1 45 30 pm > 17 (en us)) quarter n/a the default year and full quarter pattern (example 6/15/2017 1 45 30 pm > q2 2017 (en us)) month n/a the default year pattern and the full name of the month (example 6/15/2017 1 45 30 pm > june, 2017 (en us)) day long long date pattern (example 6/15/2017 1 45 30 pm > monday, june 15, 2017 (en us)) short short date pattern (example 6/15/2017 1 45 30 pm > 6/15/2017 (en us)) hour long long date pattern, long time pattern (example 6/15/2017 1 45 30 pm > monday, june 15, 2017 1 00 pm (en us)) short short date pattern, long time pattern (example 6/15/2017 1 45 30 pm > 6/15/2017 1 00 pm (en us)) time only long time pattern (example 6/15/2017 1 45 30 pm > 1 00 pm (en us)) minute long long date pattern, long time pattern (example 6/15/2017 1 45 30 pm > monday, june 15, 2017 1 45 pm (en us)) short short date pattern, long time pattern (example 6/15/2017 1 45 30 pm > 6/15/2017 1 45 pm (en us)) time only long time pattern (example 6/15/2017 1 45 30 pm > 1 45 pm (en us)) second long long date pattern, long time pattern (example 6/15/2017 1 45 30 pm > monday, june 15, 2017 1 45 30 pm (en us)) short short date pattern, long time pattern (example 6/15/2017 1 45 30 pm > 6/15/2017 1 45 30 pm (en us)) time only long time pattern (example 6/15/2017 1 45 30 pm > 1 45 30 pm (en us)) currency formatting specifics the dashboard allows you to specify a currency format at two levels for the entire dashboard and for individual data items data item currency to specify which currency to use for a particular data item, select format from the data item menu in the numeric format dialog, select currency in the format type field and use the currency combo box to select the required currency \[!note] this option only affects the way values are displayed the dashboard does not convert monetary amounts from one currency to another for regions with several cultures, you can also select the culture that will be used to format currency values you can also apply the default dashboard currency by selecting use dashboard settings in the currency field dashboard currency you can also specify the default currency for the dashboard this setting will be applied to dashboard items that have no currency defined to set the dashboard currency, click the currency button in the ribbon this invokes the dashboard currency window in this window, select the required currency using the currency combo box \[!note] this option only affects the way values are displayed the dashboard does not convert monetary amounts from one currency to another for regions with several cultures, you can also select the culture that will be used to format currency values additionally, you can specify the client culture that should be used for the dashboard by selecting the use client system settings item in this instance, the current system culture will be used in winforms applications, and the client culture will be used in web applications