Named Sets

Year to Date (YTD) named sets are created by Stratum.Connector during the processing of the Analysis Services database for your Stratum.Viewer environment. There will be YTD named sets created for each single level, absolute time dimension. The named sets will consist of the time members for a year up to the current period -- for example, days in the year up to the current day, weeks up to the current week, months up to the current month, and quarters up to the current quarter.

This table shows examples of types of Stratum.Viewer dimensions that would and wouldn't have named sets.

Named sets can be used in Stratum.Viewer for period based analysis and filtering levels from single level, absolute time dimensions. The named sets also can be used for setting up calculated measure items. More details:

• Named Sets Created for Single Level, Absolute TimeNamed Sets Dimensions

• Accessing Named Sets and Using them in Views

Named Sets Created for Single Level Time Dimensions

Here are examples of named sets and their members that are created by Stratum.Connector for single level, absolute time dimensions. Factors that determine the named sets are measure groups and their related Stratum.Server Structure Codes, the Based Periodic ViewSets in ViewGroups associated with the Structure Codes (excluding Based Year ViewSets), Categories in the Structure Codes, and Current Period information in the Stratum.Server database. For each Category in a Structure Code, a Named Set for each type of Based Periodic ViewSet is created.

Additionally, the Structure Codes contain these Categories.

Given this set up, Stratum.Connector will create the following named sets. Their names consist of the Category name, a space, the text "YTD" (for year to date), another space, and the name of the Absolute Periodic ViewSet on which the Based Periodic ViewSet in the ViewGroup was based.

More Information:

Named sets are comprised of members that make up the YTD Periods for the related Absolute Periodic ViewSet. Current Period information determines the member list for each named set. For example, if the current month in the Stratum.Server storage database is defined as April, the YTD Periods for the Months ViewSet would be January, February, March, and April. Given this, named sets such as Sales YTD Months and Forecast YTD Months would be comprised of the members January, February, March, and April.

Given the example named sets shown above, if the Current Period was September 2014, then:

• The list of members for Sales YTD Quarters and other YTD Quarters named sets would be Q1, Q2, and Q3.

• The list of Members for Sales YTD Months and other YTD Months named sets would be January, February, March, April, May, June, July, August, and September.

• The list of Members for Sales YTD Weeks and other YTD Weeks named sets would be Week 1 through Week 38.

Accessing Named Sets and Using Them in Views

Named sets are accessed from the Select Named Set Filter window, which in turn is accessed from the Select Filter Method window when you are working with filters or working with expressions. Examples of using named sets follow. Remember, to use named sets you must be working with a single level, absolute time dimension such as Weeks, Months, Quarters, or Periods. When you are choosing a named set for a filter or expression, be sure that the set is relevant to the measure items in the view or expression. For example, if the measure item in a view is Actual Sales and you are filtering a level by a named set, choose one that pertains to Actual Sales rather than one pertaining to Budget.

Named Set Used for a Filter

The following view includes the Months level, which is from the Months time hierarchy and time dimension. Because the Months dimension is a single level, absolute time dimension, you can use the named sets for the Months level when working with the view. The Budget YTD Months named set was used to filter the level because Budget measure items are in the view. More information follows this image.

To set up the filter, the Months level was right-clicked and Filter then Edit was selected from the pop-up menu that displayed. In the Select Members window that displayed by default, the More button was clicked then Change Filter Type was selected to open the Select Filter Method window. In that window, Named Set was selected and OK was clicked.

Note: Another option in the following window would have been to select Named Set, enter the name of the named set in the quick filter field, and click OK. In that case, the filter would be applied without accessing the Select Named Set Filter window.

The Select Named Set Filter window displayed, and the Budget YTD Months named set was selected. The window displayed the members in the named set. Then, OK was clicked to apply the filter.

Named Set Used for a Calculated Measure Item

Here is an example of named sets used for calculated measure items. Both YTD calculated measure items use a named set in their expression. The calculated measure items take into account data from all months up to and including the current month for the last year and current year. More information follows this image.

Note: You can create a similar view using measure items with time ranges. The view would need to be one in which the Time Range property is enabled. See also Time Ranges vs. Time Hierarchies for YTD, Trending, and Other Time Analysis.

The expression for the first measure item is:

IIF([Measures].[Data5 (Actual Sales Sales Units)] = null, null, Sum(CrossJoin({[Time].[Year Based Months Based].[Year Based].[Current Year]},{[Act Sales YTD Months]}),[Measures].[Data5 (Actual Sales Sales Units)]))

This expression uses the Act Sales YTD Months named set and the Actual Sales Sales Units measure item, which is part of the view definition but hidden from display. The expression has been optimized to check whether or not sales units data exists. The expression will be executed only for cases where there is data. Running the expression without this check could be time consuming.

When building such expressions, you can enter the name of the relevant named set directly into the Expression window. Or, navigate to the appropriate level in the window and click its Members sub-folder to access the Select Filter Method window. For this measure item's expression, the Members sub-folder of the Months level was clicked to access the Select Filter Method window. Named Set was selected, and OK was clicked.

The Act Sales YTD Months named set was selected since the measure item in the expression is Actual Sales Sales Units.