How To: Create Views Based On A Current Date?

SUMMARY
Sometimes, it is necessary to have a view always sliced (or displayed) on a current unit of date (usually Day, Month or Quarter)
SOLUTION

In order to determine a current date in view, a MDX query should be performed.

Assume that the Time dimension has the hierarchy of [Year].[Month].[Day].

The following MDX will return a current date:

strtomember("[time].["+cstr(datepart("yyyy",now()))+"].[Q"+cstr(datepart("q",now()))+"].["+cstr(datepart("m",now()))+"]")

This query can be used as it was a regular member. For example, it is possible to display a set of the current and previous quarters:

{strtomember("[time].["+cstr(datepart("yyyy",now()))+"].[Q"+cstr(datepart("q",now()))+"]"),strtomember("[time].["+cstr(datepart("yyyy",now()))+"].[Q"+cstr(datepart("q",now()))+"]").lag(1)}

 

Panorama Implementation:

There are two possible ways to represent a current period at Panorama:

1. Define a required formula to be a default member of Time dimension at Analysis Services. Then enable the option to “Always load view with dimension’s default member” at Panorama View.

Note: This will affect on all cubes which use that dimension.

2. Define a “Named set” at Analysis Services using the required formula. Then define that set as a default on a time dimension at Panorama View.
VERSION
  • Not Supported (provided as is). Suitable for Analysis Services 2000
 
Free business joomla templates