Archive for January, 2012

My Date dimension in the Cube  has 2 hierarchies. One is called Monthly with the following levels: Year -> Month -> Date. Another one is called Calendar with the following levels: Year -> Month -> Week -> Date.  

There is a fact table called fact_Injuries that stores the number of injuries (i.e. [Measures].[Injury Count] ) that can be sliced based on date, body parts, workgroups etc. There is also date calculations in my Cube, generated from the “Add Business Intelligence” wizard. One of the date calculations is called Twelve Months To Date with the following MDX:

// Twelve Months to Date

// Twelve months to date enables you to see data in an accumulated view from the same period in the previous year to the selected period.


      [Date].[Calendar Dates Calculations].[Twelve Months to Date],






             { [Date].[Calendar Dates Calculations].[Current Dates] }







               ) : [Date].[Calendar].CurrentMember



Unfortunately, this Twelve Months To Date calculation does not work.

When the number of injuries is drilled down to the Date level, the count for some dates are incorrect (see screenshot below). I think this is a Microsoft bug. Take a look at below.. the number goes up from 3 to 19. The value should be 4 not 19.

CREATE MEMBER CURRENTCUBE.[Measures].[Twelve Months To Date Incidents]
 AS Sum([Date].[Calendar Dates Calculations].[Twelve Months to Date], [Measures].[Events Count]),
VISIBLE = 1 ,  DISPLAY_FOLDER = ‘Twelve Months To Date’ ,  ASSOCIATED_MEASURE_GROUP = ‘Fact Events’; 


To resolve this, I have had to manually calculate this. The following MDX works 🙂 In here, I have had to get the SUM of a date range, instead of using LastPeriods() MDX function.  The LastPeriods(12, [Date].[Monthly].CurrentMember  ) function will get the sum of the last 12 months to the month selected (NOT the date selected).

 CREATE MEMBER CURRENTCUBE.[Measures].[Twelve Months To Date Injuries]
 AS Sum( { OpeningPeriod([Date].[Monthly].[Date]
               , LastPeriods(12, [Date].[Monthly].CurrentMember.Parent).Item(0))
       : [Date].[Monthly].CurrentMember }
,  [Measures].[Injury Count] ),
VISIBLE = 1 ,  DISPLAY_FOLDER = ‘Twelve Months To Date’ ,  ASSOCIATED_MEASURE_GROUP = ‘Fact Injuries’  ;

To calculate the number of injuries 12 months prior to the twelve months to date injuries:

CREATE MEMBER CURRENTCUBE.[Measures].[Twelve Months Prior Twelve Months To Date Injuries]
 AS ( ParallelPeriod([Date].[Monthly].[Year], 1 , [Date].[Monthly].CurrentMember )
  , [Measures].[Twelve Months To Date Injuries]),
VISIBLE = 1 ,  DISPLAY_FOLDER = ‘Twelve Months Prior’ ,  ASSOCIATED_MEASURE_GROUP = ‘Fact Injuries’;

Read Full Post »

Recently, I used the same dtsx packages to build the data warehouse in TEST environment for a client. I have got a main dtsx package that runs other packages (Execute Package Task) subsequently to build the dimensions first then the fact tables. To my surprise, each Execute Package Task fails with the same error: Accesss denied or Cannot open the database. I first checked if the user has the required permission, which is a yes. The second error stumbled me the most as the connection managers in each package have been set up correctly but somehow when the package is run, it remembers the previously set connection managers. How odd!

I decided to find out more about this such as finding a way to clear the cache within the dtsx package. This led me to nowhere in Google. Further googling led me to a few websites where some users suggest setting the “Delay Validation” property to True for all the packages. So I did that and it works like a charm! 😀

Read Full Post »

I have a main report that contains 2 subreports i.e. subreport A and B. The main report has 2 parameters i.e. @Username and @PersonGUID. Subreport A calls a stored procedure that needs @PersonGUID, whereas subreport B calls another stored procedure that needs @Username. The main report only has to display one parameter, which the Person LookupName.

So I have quite a bit of dilemma here, either add @PersonGUID into the stored procedure used in the subreport B or write a query to lookup the username for the @PersonGUID selected. Furthermore, these stored procedures are maintained by R&D.

Luckily, there is a VB function called Lookup() that is available within SSRS.  😀


Lookup(source_expression, destination_expression, result_expression, dataset)

  • source_expression – specifies the value in the current scope that you want to use as the lookup; e.g. EmployeeKey in the SalesQuotaDataset
  • destination_expression – specifies the value that you want to match in the destination dataset; e.g. EmployeeKey in the EmployeeDataset
  • result_expression – is what you want the lookup function to return; e.g. EmployeeName from the EmployeeDataset.
  • dataset – is the name of the destination dataset; e.g. EmployeeDataset

The actual Lookup expression in my report is:

=Lookup(Parameters!PersonGUID.Value, Fields!PersonGUID.Value, Fields!Username.Value, “GetPersonList”)

Read Full Post »

I have a Reporting Services report that calls a stored procedure, which runs instanly in SQL Server Management Studio. However, the report takes ages to run on the client production  server. It runs quick on the development server.

Googling got me to the following link:


The resolution is to add the following syntax to the end of the stored procedure. I have no idea why this is required but it does resolve the issue.


My report now runs as quick as the SP execution time 🙂


Read Full Post »

My report has one date parameter and it has to display the fiscal year from the first day of the fiscal year to the selected reporting date. For example. if the date parameter is set to 2011-06-10 then the fiscal year date range would be 2010-07-01 – 2011-06-10.

The date dimension has the Fiscal hierarchy members as follows:

Fiscal Year > Fiscal Quarter > Month > Date

To get the first day of the Fiscal Year for 2011-06-10, use the MDX navigation functions of Parent and FirstSibling.

SET [FirstDayOfFiscalQuarter] AS  OpeningPeriod([Date].[Fiscal].[Date], STRTOMEMBER(@DateDate, CONSTRAINED).Parent.Parent.FirstSibling  )
        ,  [FirstDayOfFiscalQuarter] ON ROWS
FROM [Cube]



Read Full Post »

%d bloggers like this: