KB13932: How to create a report that displays a row for each of the months in a year up to a month specified in a report filter qualifications in MicroStrategy Developer 9.4.x - 10.x

Salesforce Solutions Architect • Strategy

This technical note describes the steps to create a report that displays a row for each of the months in a year up to a month specified in a report filter qualifications in MicroStrategy Developer 9.4.x - 10.x

Many-to-many transformations may be used in Strategy Developer to produce transformations such as month to date, in which metric values for every day in the month up until the day displayed on the template will be aggregated into a single value, as shown below:

Users may also want the reverse behavior; namely, they want the ability to filter on a given day but display a distinct row for each day in the month up to the day chosen in the filter.

This behavior is related to transformations, in that a filtering condition must be transformed into different values for display, but it bears an important distinction. If the source attribute is defined as the project attribute on which the transformation is based and the transformation attribute as retrieving the attribute elements over which the transformation metric is actually calculated, a Strategy report may display only the source attribute and only this attribute may be used to set the aggregation level of the calculation. The transformation attribute elements are used strictly internally for calculation, but the results are always aggregated to the level of the source attribute. Thus it is not possible, using a transformation, to expand a single attribute element in a filter into multiple rows.

It is possible, however, to model the relationship between the filtering attribute and the template display attribute in the project schema to achieve the desired result. To do this, perform the following steps:

Create a table to relate filtering dates to the dates in the month up to and including the filtering date. This may be a physical table or view in the warehouse, or it may be a logical view defined in Strategy. The example below uses a logical view:

Create a 'Month to Date' attribute whose lookup table is the newly-created view. Its primary ID should be XformDate, and it should be mapped using heterogeneous column mapping to any fact tables that will be used against the new attribute. In this illustration, DAY_DATE comes from the DAY_CTR_SLS table, and ORDER_DATE comes from ORDER_DETAIL and ORDER_FACT:

This attribute must also have a description form, defined on LU_DAY_MTD.
The attribute must be related to Day as a child, so the Day attribute must include a new mapping to the SourceDate column in the month to date lookup table, as shown below:

With the new mapping, Month to Date may be set as a child of Day, with LU_DAY_MTD as the relationship table, as shown below:

Update schema.

The report may now be defined with 'Month to Date on the template, Day in the filter (with or without a prompt), and any metrics.

NOTE: Before executing the report, it is necessary to edit the very large database (VLDB) property 'WHERE Clause Driving Table' to use the lookup table rather than the fact table. If the where clause driving table is the fact table, the Day filter will be applied directly to the fact table and only results for that day will be returned. Changing this VLDB property means that the filter will be applied against the Month to Date lookup table, correctly expressing the relationship between the filter and the template in SQL.

This VLDB property is located in the Query Optimizations folder, as shown below:

Also, it is very important that the 'Month to Date' attribute have a description form. Without a description form, the SQL Generation Engine will optimize the join path and remove LU_DAY_MTD from the query, returning incorrect results. If a description form is not required, it may be possible to evade this requirement by enabling the engine attribute role recognition option, located in the VLDB properties for the warehouse database instance under Query Optimizations -> Engine Attribute Role Option. Be aware that the setting may be enabled only for the project as a whole, which may have an impact on other reports.

Report results and SQL are shown below:

select   a12.XformDate   XformDate,
   max(a12.XformDate_DESC)   XformDate_DESC,
   sum(a11.TOT_DOLLAR_SALES)   WJXBFS1
from DAY_CTR_SLS a11
   join   (select Source.DAY_DATE SourceDate,
   MTD.DAY_DATE XformDate,
   MTD.DAY_DATE XformDate_DESC,
   MTD.MONTH_ID Month_ID
from   LU_DAY Source,
   LU_DAY MTD
where   (Source.MONTH_ID = MTD.MONTH_ID)
and (MTD.DAY_DATE <= Source.DAY_DATE)
) a12
      on (a11.DAY_DATE = a12.XformDate)
where a12.SourceDate in (CONVERT(datetime, '2003-01-05 00:00:00', 120))
group by a12.XformDate

Comment

0 comments

Details

Knowledge Article

Published:

April 26, 2017

Last Updated:

April 26, 2017