How to open SSRS reports in a new tab?

I created a launch page that contains 4 images that behave like buttons. Each image opens a report (by setting the Go to URL property instead of using Go to Report). If you set the Action of the image to use Go to Report, the report will be displayed on the same browser window as its parent. If you set the Action of the image to use Go to URL, the report will be displayed in a new browser window.

In order to have the report displayed in a new tab, I have had to use javascript and set the browser setting to open pop-ups in tabs.

For IE8, it opens pop-ups in new windows by default.

For Firefox, it opens pop-ups in new tabs by default.

To set IE8 to open pop-ups in new tabs:

Go to Tools > Internet Options > General > Tabs

An example:

Management Dashboard:

=”javascript:void(window.open(‘” + Globals!ReportServerUrl + “/Pages/ReportViewer.aspx?/InControl%2f” +
“Management Dashboard” +
“&DateDate=’+escape(‘”+
Parameters!DateDate.Value
+”‘)+’&ClassValue=’+escape(‘”+
Parameters!ClassValue.Value
+”‘) + ”,”, ‘width=1100,height=800,screenX=0,left=0,screenY=0,top=0,menubar=0,resizable=1,status=1,scrollbars=1,toolbar=0,location=1’), ‘_blank’)”

To pass MDX parameter value to the URL, you have to translate it first:

[ = %5B
] = %5D
space = %20
& = %26

For example:

=”[Date].[Monthly].[Year].&[” & Year(CDate(Parameters!DateDate.Value)) & “]”

& “%5BDate%5D.%5BMonthly%5D.%5BYear%5D.%26%5B” & Year(CDate(Parameters!DateDate.Value)) & “%5D” &

or add the System.Web assembly ( System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a ) into the report then use the following custom code to translate the MDX value automatically:

Public Function URLEncode (ByVal inURL As String) As String

Dim outURL As String

outURL = System.Web.HttpUtility.UrlEncode(inURL).ToString

Return outURL

End Function

Example:

=Globals!ReportServerUrl  & “/Pages/ReportViewer.aspx?/InControl/Management Dashboard&DateDate=” & Parameters!DateDate.Value & “&ClassValue=” & Code.URLEncode(Parameters!ClassValue.Value) & “&rc:NoHeader=True&rs:Command=Render”

Read Full Post »

SSIS package failed with CPackage load from xml Error (SSIS 2008R2)

I took a copy of SSIS package solution from a client server then copied it to my laptop. After making all the necessary connection string changes, when I tried to run the packages, i was prompted with an error below. I could not find the CPackage::LoadFromXML function in the dtsx package.

The package failed to load due to error 0xC0010014 “One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.”. This occurs when CPackage::LoadFromXML fails.

Google returns with an information about setting the Project Property > Debugging > of Run64BitRunTime to False. I did that and the error went away. However, I noticed that this error could be caused by having one package calling into another package, which contains old connection strings. After sorting out all these connection strings in all packages (by updating the global configuration dtsconfig file and Config.[SSIS Configurations) , I then set the Run64BitRunTime to True and the packages ran successfully with no problems. The above mentioned error is gone. Hmmm…. weird!

Read Full Post »

MDX to obtain a non aggregrated target value based on Financial Year (Australian) but with the @Month parameter set in the Calendar Year

I have a report written in MDX with @DateYear and @DateMonth as the parameters. Both parameters have datasets from [Date].[Monthly] hierarchy, which is based on the Calendar Year (i.e. 1 Jan to 31 Dec). However, the target value must be based on the Financial Year (Australian) i.e. 1 Jul to 30 Jun. This financial year has [Date].[Fiscal] hierarchy. When I put this together in SSRS, I received an error about having member and level from a different hierarchy.

To resolve this, I have had to play quite bit with Navigation built-in MDX functions such as FirstSibling, LastSibling and Parent and have had to use Format function. I have spent at least 5 hours on this.

The followng MDX returns the Financial Year target value for Feb 2012 for each injury type (i.e. 1 July 2011 to 30 June 2012)

WITH

//To get the first day of the calendar month from the @Month parameter selected

//e.g. For Feb 2012, the first day would be in this format [Date].[Monthly].[Date].&[2012-02-01T00:00:00]

SET [FirstDayOfCalendarMonth] AS OPENINGPERIOD([Date].[Monthly].[Date], STRTOMEMBER(“[Date].[Monthly].[Month].&[2012]&[2]”, CONSTRAINED) )

//Convert this to [Date].[Fiscal].[Date] format

//e.g. For Feb 2012, the first day would be in this format [Date].[Fiscal].[Date].&[2012-02-01T00:00:00]

SET [FirstDayOfFiscalMonth] AsSTRTOMEMBER(“[Date].[Fiscal].[Date].&[” + Format( [FirstDayOfCalendarMonth].Item(0).MemberValue, “yyyy-MM-dd”) + “T00:00:00]”).FirstSibling

//Get the Fiscal Month from the FirstDayOfFiscalMonth

//e.g. For Feb 2012, the Fiscal Month would be in this format [Date].[Fiscal].[Month].&[2012]&[2]

SET [FiscalMonth] AS STRTOMEMBER(“[Date].[Fiscal].[Month].&[” + Format( [FirstDayOfFiscalMonth].Item(0).MemberValue, “yyyy”) + “]&[” + Format( [FirstDayOfFiscalMonth].Item(0).MemberValue, “MM”) + “]” )

//Get the Fiscal Year range e.g. For Feb 2012, the range would be 1 July 2011 – 30 June 2012

SET [FirstDayOfFiscalYear]  AS OpeningPeriod( [Date].[Fiscal].[Date], [FiscalMonth].Item(0).Parent.FirstSibling)

SET [LastDayOfFiscalYear]  AS ClosingPeriod( [Date].[Fiscal].[Date], [FiscalMonth].Item(0).Parent.LastSibling)

MEMBER [Measures].[This Year Target Value]  AS SUM( { [FirstDayOfFiscalYear].Item(0) : [LastDayOfFiscalYear].Item(0) } , [Measures].[Target Value] )

 

SELECT { [Measures].[This Year Target Value] } ON COLUMNS

, [Injury Types].[Injury Type Description].[Injury Type Description] ON ROWS

FROM Cube

The screenshot below shows 2012-06-30 as the Last Day of the Financial Year for Feb 2012.

Have fun! 😀

Read Full Post »