Monthly Archives: December 2007
Open SSRS report in a new Window
There isn’t yet a built-in facility within Reporting Services to open a report link (i.e. a drillthrough report) in a new browser window or tab, however it isn’t too tricky to get this to work using a bit of javascript.
javascript:void(window.open(Destination,’_blank’))
My preferred method is to wrap this up into a custom code function such as
Function OpenURL(ByVal URL As String) As String
Return “javascript:void(window.open(‘” & URL & “‘,’_blank’))”
End Function
In your report, you can then just set the navigation property to jump to a URL of a website:
=Code.OpenURL(“https://www.purplefrogsystems.com“)
Or to the path of a the drillthrough report:
=Code.OpenURL(“http://[Your Server]/ReportServer?/Folder/ReportName&rs:Command=Render”)
If you have parameters that you need to pass in, you can add these into the URL either fixed or dynamic:
=Code.OpenURL(“http://[Your Server]/ReportServer?/Folder/ReportName&rs:Command=Render &MyFirstParam=Value1&MySecondParam=” & Fields!MyField.Value)
Please note that this will not work in the BIDS development environment, it will only work once you have deployed the report to the Report Server.
Excel Addin for Analysis Services
For any users of Analysis Services, if you haven’t already downloaded the Excel (2002/2003) addin you’re missing out.
It’s a free download from Microsoft which significantly expands Excel’s cube querying ability. Well recommended!
Convert MDX fields to SQL
A number of our customers have reporting systems that use both MDX and SQL, retrieving data from both OLAP and SQL Server databases. This generates the problem of converting an MDX field ([Dimension].[Hierarchy].&[Attribute]) into SQL Server field value (Attribute). The following code is a Reporting Services custom code section that will rip off the MDX and leave you with the value.
Public Function MDXParamToSQL(Parameter As String, All As String) As String
Dim Val As String
Val = Parameter
If Val.Contains(“[“) Then
If Val.ToLower().Contains(“].[all]”) Then
Return All
Else
Val = Val.Substring(1, Val.LastIndexOf(“]”) – 1)
Val = Val.Substring(Val.LastIndexOf(“[“) + 1)
Return Val
End If
Else
Return Val
End If
End Function
Lets say that you have a report using an MDX dataset, if you want to call a drillthrough report based on SQL Server you will need to pass at least one attribute through as a parameter to filter the second report. If you add the code above to the custom code section, you can set the parameter value of the second report to
=Code.MDXParamToSQL(Fields!MyField.Value,”%”)
The second report will then just receive the member name, not the full MDX unique identifier.
Chris Hays Reporting Services Sleazy Hacks
It really wouldn’t be fair to kick off the Frog-Blog without a shout out to Chris Hays and his superb ‘Sleazy Hacks’ site. If you want to push SQL Server Reporting Services further than anyone else, then Chris will definately have something of use to you. He designed the RDL report language – the guy knows what he’s talking about.
The Frog-Blog is open for business!
Well, what can I say – the very first Frog-Blog post.
Keep checking back often for new and (hopefully) useful info…
Alex
I'm Alex Whittles.
I specialise in designing and implementing SQL Server business intelligence solutions,
and this is my blog! Just a collection of thoughts, techniques and ramblings on SQL Server, Cubes, Data Warehouses, MDX, DAX and whatever else comes to mind.
