Time Intelligence in MDX: last N days

Standard

it’s a common requirement to create a report that shows last N days of a business metric – so I thought I’ll post a template here for SQL server analysis server’s MDX query:


WITH
  MEMBER [Measures].[Sales_last_15_days] AS
    Sum
    (
      {
          [Calendar].[Date YYYYMMDD].CurrentMember.Lag(14)
        :
          [Calendar].[Date YYYYMMDD].CurrentMember
      }
     ,[Measures].[Sales]
    )

   MEMBER [Measures].[CurrDate] as
      "[Calendar].[Date YYYYMMDD].[" + Cstr(Year(Now())*10000+month(now())*100+day(now()))  +"]"

SELECT
  {
     [Measures].[Sales_last_15_days]
  } ON COLUMNS
FROM 
[CubeName]
WHERE
STRTOMEMBER([Measures].[CurrDate])

Here are things that you’ll need to adjust to make it work for your scenario:

1. Date Dimension Attribute & it’s format. The example shows yyyymmdd but you could have different format of the date.

2. Measure name. Instead of [Measures].[Sales] you’ll have to replace it with your business metric. Also, make sure you are using the right aggregate function, in the example above I have used SUM but you’ll have to change this based on your requirement.

3. Create a parameter and use it in index for the Lag function.

4. change [cubename] to your cube name.

I hope this gives you a good starting point to create last N days for your business metric.

Advertisements

How to set SSRS date parameter default value to previous day?

Standard

Setting the default value for parameters like today’s date & current month is great because that way consumer do not need to manually input the parameter value. I was working on a requirement to pass previous day to the date parameter.

So here’s the quick tip to set the default value of the date parameter to previous day.

=Dateadd("d",-1,Now())

Screenshot:
SSRS date time parameter previous day

New Journal Article Published – Title: “Building an Ideal Tabular Model for Power View reports”

Standard

Part 2/2 of the series on building Ideal Models for Power View reports is live!

Summary: “In this article, we will first compare the PowerPivot and Tabular models, which will help you choose between these two models for your scenarios, and then we will study the reporting properties in a Tabular Model that you can configure to build an Ideal model for Power View reports.”

Read here: SolidQ Journal: Building an Ideal Tabular Model for Power View reports by Paras Doshi

Blog-Post about Part 1 is here: SolidQ Journal: Building Ideal PowerPivot Model for Power View reports

 

If you have any feedback or comments, please drop a comment or contact me.

SolidQ Journal: Building Ideal PowerPivot Model for Power View reports

Standard

My journal article titled “Building Ideal PowerPivot Model for Power View reports” got published in SolidQ Journal. In this article, I talk about reporting properties in PowerPivot Model that you can set which will enhance the Power View report creation experience of your end-users. Here are the five main topics discussed in the article:

– Hide from Client Tools
– ImageURL
– Default Field Set
– Table Behavior
– Calculate Columns and Calculate measures

In the Part 2 of this series, we will discuss the reporting properties in Tabular Model to help you build an ideal Model for Power View reports. I’ll let you know when that’s published Part 2 is Published: http://parasdoshi.com/2012/09/25/new-journal-article-published-title-building-an-ideal-tabular-model-for-power-view-reports/

I would also like to thank the SolidQ Journal Team and Ruben Lopez who was the Technical reviewer of the article.

And if you have any feedback, please drop a comment or contact me. Thank you