Archive for August, 2012

I have a web application that has a URL link that takes users to a dashboard written in SSRS. The web application is hosted on IIS on server A but the Database Server and Report Server on server B. Even though both servers belong to the same domain, Windows treats it as two different sites.   Users get annoyed when prompted with the second login when clicking on the dashboard link.

To resolve this, IIS and Report Server must sit on the same server. This would then mean that a SQL Server licence is required in order to move the Report Server to the server A (i.e. another $580 per month). Or move the web application to server B, but the performance may be an issue. The Report Server configuration file (rsreportserver.config) must have RSWindowsNegotiate and RSWindowsNTLM enabled.



Read Full Post »

DataReader destination writes records to memory as it does not require configuration nor create a temporary table for storage.  However, I noticed that my SSIS package would hang for a while before completing even there is 1 record to be processed only. The following blog explains it all. The resolution would be to decrease the timeout of the DataReader destination task in this case 1 millisecond.


Read Full Post »

I have an MDX report to display a list of Health & Safety projects and these projects have not had their Project Risk Assessment prepared or their Project Risk Assessment have not been updated in the previous 3 months. The report has @DateYear and @DateMonth parameters.

As an example, I have the following projects (i.e. class values) for March 2012 report. It lists events till March 2012.

There are 4 distinct projects and only 5B701 project has had their project risk assessment done on 24 Nov 2011. Hence the last review date for this project is 24 Nov 2011 and it should be displayed for the March 2012 report because Nov 2011 is 4 months away.

The other 3 projects have not had any event raised as Health & Safety Risk Assessment event type, therefore these projects have not have their Project Risk Assessment prepared yet and their last review date should be blank.

My final report for March 2012  should look like below:

However, when the report is run for Jan 2012, 5B701 project should not be displayed because its last review date is 2 months away (see below):

Several attempts were made without having to modify the data warehouse and cube structure by using UNION() function but could not achieve the above. 

My source view to populate the data into the data warehouse was modified to add a new column called LastRiskAssessmentReviewDate. This column identifies if a project has had their risk review done and if so when it was done. So my dataset looks like below:

I then populated this new field into fact Events table in the data warehouse and cube and if the field is null set it to 1900-01-01.  My MDX code should return with the following dataset:

SELECT NON EMPTY  { [Measures].[Events Count] } ON COLUMNS

 ,NON EMPTY [Class Values].[Class Value].[Class Value]
                                       * [Risk Assessment Review Date].[Date].[Date]
                               ON ROWS
FROM ( SELECT – { [Event Status].[Event Status].&[Cancelled], [Event Status].[Event Status].&[Closed] } ON COLUMNS
FROM [Cube]  ) )
WHERE [Event Types].[Event Type Category].&[Health & Safety]

Now we need to display distinct projects on the report. Notice that 5B701 has 2 dates hence it is necessary to retrieve the latest date, i.e. 24 Nov 2011.  Afterwards, we need to set the date to blank when it is 1900-01-01.   This requirement can be met by using a row group (i.e. group the rows by classvalue) on the tablix in SSRS and using the expression below to get the max date and to set it to blank if it is of 1900 year.

=IIF(Year(CDate(Max(Fields!Date.Value))) = 1900, “”, Format(CDate(Max(Fields!Date.Value)), “dd/MM/yyyy”))

It is also required to hide projects if its last review date is less than three months away from when the report is run. This can be achieved by setting a filter on the Row Group Properties. Set the filter using the following expression

CDate(Max(Fields!Date.Value)) < CDate(Parameters!FirstDayOfPrevious3Months.Value)

where the FirstDayOfPrevious3Months value is obtained from below:

WITH SET [FirstDateOfLast3Months] AS OpeningPeriod([Date].[Monthly].[Date], ClosingPeriod([Date].[Monthly].[Month], STRTOMEMBER(@DateMonth).Lag(3)) )

         ,  [FirstDateOfLast3Months] ON ROWS
FROM  Cube

Read Full Post »

I added a new column into my database view as the source for my data warehouse.  I received the above error when trying to refresh the OLEDB Source.  The error says what it is.  In the Advance Settings  of the OLEDB Source, I could see that the new column does not have the corresponding error output column. This is very odd. I was not able to add an output column nor delete the existing unused one(s).

A few blogs advised to recreate the OLDEDB Source which I tried but did not work. 

I knew somehow I needed to refresh the column mapping. So I then went to the Columns Mapping tab in the Advanced Editor, then remapped the new column and to my surprise it worked! 🙂 What a relief! Is this a bug ?!


Read Full Post »



The answer would be to use ABS() function. It is not an MDX function but is supported by the VB function library that is included ith Analsysis Servcies and linked by default.

The following code is to get the day difference between the due date of the actions and the last day of the month. If the value is negative then make it positive by using the ABS() function.

SET [LastDayOfMonth] AS CLOSINGPERIOD([Date].[Monthly].[Date], STRTOMEMBER(@DateMonth, CONSTRAINED)  )
MEMBER [Measures].[DateDiff] AS ‘ABS(DATEDIFF(“d”, [Actions].[Due Date].CurrentMember.Name, [LastDayOfMonth].Item(0).MemberValue ) )’



Read Full Post »

%d bloggers like this: