Standardize Your MDX Parameter Queries

By Dan Meyers

One thing that I have found useful when writing a lot of Reporting Services reports that have parameters and use MDX is to create some standard calculated members in my cube for the Label and Value properties of the parameters. Just like any other calculated measures that get built into the cube script, you get the advantage of reusing them instead of doing them over and over again in the WITH clause of your query.  The code below is dynamic and is not specific to a particular dimension or anything so it will work with whatever you put on ROWS in your data set query for your report parameters.

Below is the MDX for the calculated members and a sample query.

Insert this code into your cube script (at the bottom)

CREATE MEMBER CURRENTCUBE.[Measures].[ParameterValue] AS
    Axis(1).Item(0).Item(0).Dimension.CurrentMember.UNIQUE_NAME,
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Measures].[ParameterCaption] AS 
    String(Axis(1).Item(0).Item(0).Dimension.CurrentMember.Level.Ordinal * 1 , ' ' ) + Axis(1).Item(0).Item(0).Dimension.CurrentMember.Member_Caption,
VISIBLE = 1;

Sample Query
SELECT
      {[Measures].[ParameterCaption], [Measures].[ParameterValue]} ON 0,
      {[Date].[Calendar].ALLMEMBERS} ON 1
FROM
      [Adventure Works]

.

URL Encoding in Reporting Services

Handling Special Characters Using HttpUtility.URLEncode()

By Dan Meyers

Reporting Services allows you to create hyperlinks in your reports using the Go To URL action. This is a very useful feature that provides you with the ability to make almost anything on the report a hyperlink. When creating links to other reports, this action type provides many advantages over using the Go To Report action type. For example, you can embed some javascript into the link so that the report opens up in a new window that is a specific size in a specific location on the screen. You cannot do this using the Go To Report action type.

Of course there are disadvantages too. When using the Go To URL action type you have to manually handle any special characters in you URL string. This is something that the Go To Report action type handles for you when it comes to the parameter values passed to the report. In my opinion, the advantages outweigh the disadvantages. So I always use the Go To URL action type and manually handle the special characters using the HttpUtility.URLEncode() function.

Before we can use the the HttpUtility.URLEncode() function we have to first add a reference to System.Web assembly.

Go to Report –> Properties –> References. Click [Add].

Scroll down a select ‘System.Web’ in the list and click [OK].

You should now see that the reference has been added.

Now that we have added the reference to System.Web, we now have to create a function in the report’s Code window that uses the URLEncode() function. Click on ‘Code’ in the left panel and enter the code below.

Public Function URLEncode (ByVal inURL As String) As String
Dim outURL As String
outURL = System.Web.HttpUtility.UrlEncode(inURL).ToString
Return outURL
End Function

We can now use our new report function in an expression anywhere in our report.

As you can see in the screenshot below, the function will convert any special charters and encode the URL. In this example, I am passing in a string for a MDX member for Barnes & Noble. You can see the before and after values below.

A Common Mistake
One common mistake that many make when taking this approach is that they try to use the HttpUtility.URLEncode() function directly from an expression. The expression will basically ignore the reference and not work. You have to use the function in the report’s Code window and then reference the report’s function in the expression for it to work.

Analysis Services 2008 Metadata Report Pack

Reporting on Analysis Services Metadata using DMV Schema Rowsets

By Dan Meyers


Analysis Services 2008 Metadata Report Pack Download

I often get asked by clients about the best way to get metadata about the various SQL Server Analysis Services (SSAS) objects on a particular server. As usual, there are a few ways to go about getting this information. Unfortunately for DBAs and report writers, most of them are a headache to implement and often have you writing code rather than queries. In the recent versions of SSAS we have a somewhat straightforward way to accomplish this. SSAS exposes a lot of good information via built-inschema rowsets for OLAP and data mining objects. This means that all you have to do is write some queries and slap the results in a Reporting Services (SSRS) report and you are done.

If you think this sounds too good to be true, you are half correct. Although we finally have a nice simple solutions for reporting on this information, we are left wanting more. Not everything about the objects is exposed in these rowsets. If you want every detail about your objects then the best approaches are: to buy a 3rd party tool, or to put your programming hat on since you will be using AMOMD.NET to get all of the stuff that SSAS rowsets leave out.

In a previous blog post, I discussed a method where you can use a linked server to run MDX queries against your cubes. For these reports, I am going to use the same approach to execute DMX queries against SSAS 2008 using a linked server and the OLE DP provider for Analysis Services to retrieve metadata about our cubes, dimensions, measures, etc... from the rowsets.

This download provides you with a relatively comprehensive set of pre-built reports that you can easily deploy to your Reporting Services 2008 instance.

Before the reports will run, you have to create a linked server in SQL Server that points to your SSAS 2008 instance. Below is the SQL code needed to create a linked server named [SSAS_Metadata].


EXEC master.dbo.sp_addlinkedserver
@server=N'SSAS_METADATA',
@srvproduct=N'MSOLAP',
@provider=N'MSOLAP',
@datasrc=N'localhost'


EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'SSAS_METADATA',
@useself=N'False',
@locallogin=NULL,
@rmtuser=NULL,
@rmtpassword=NULL


The download contains the entire reporting project pre-=configured to use the localhost as the data source for the SQL Server, Reporting Services, and Analysis Services instances.

The project contains the following 26 reports:
- Cube Details
- Cube Dimension Details
- Cube Dimension Relationships
- Cube Dimensions
- Cube KPIs
- Cube Measures
- Cube Perspectives
- Cubes
- Data Source Details
- Data Sources
- Database
- Dimension Attribute Hierarchies
- Dimension Attribute Hierarchy Details
- Dimension Details
- Dimension User Hierarchies
- Dimension User Hierarchy Details
- Dimensions
- KPI Details
- Measure Details
- Mining Model Column Details
- Mining Model Details
- Mining Models
- Mining Structure Column Details
- Mining Structure Details
- Mining Structures
- Perspective Details

The reports in the report pack were designed and modeled after the output provided by my favorite 3rd party documentation tool for SQL Server BI, BI Documenter. All of the reports contain many navigational links in the header as well as click-through navigation of the objects listed in the reports that you can use to drill into child objects for more detail.

As you can see by the names of the reports listed above, the reports touch all of the major objects and should satisfy the majority of the inquiries about them. One report that I find to be quite useful in the Cube Dimension Relationships report. It is an attempt to reproduce the grid on the Dimension Usage tab of the cube designer in BIDS that tells you at what granularity the dimensions relate to the measure groups.





Hopefully this report pack will save you some time and provide you with some good samples that you can use to create your own customized reports that might work better for you.