Create goto reports in SSRS with cube as source.

So, I have found some information on the web about this but mostly people tend to use regular SQL, I really can´t understand why. Anyhow here is one way of creating the connection between reports in SSRS with parameters.

I start with the “subreport” ie. that´s the report that´s going to be called by the main report. I create a dataset with a datasource of type “Microsoft SQL Server Analys Services”

 

tut1_createdatasource

 

Before creating the dataset I would recommend in this case to create the parameters that needs to be added to the report. One of those will be used during creation of the dataset.

Click create new parameter, up to the left under “New” in the “Report Data” window.

Then choose names and make it hidden, that is, if you don´t want anybody to see it. In this case this parameter will come from the main report so there is no use of showing it. We will show it to the user on the report pane instead.

 

 

tut1_createParametersProperties    After:     tut1_createparameters

 

Then I create my Dataset, more information how to exactly create dataset: http://msdn.microsoft.com/en-us/library/ms160345(v=sql.100).aspx

tut1_createdataset

 

Then click on “fx” to create the MDX-query, which is good to test before in SSMS. There is no help for getting the right syntax here.

=”WITH MEMBER [Measures].[Adress] AS [Kunder].[Kund].currentmember.name SELECT ({[Measures].[Belopp], [Measures].[Adress]}) ON COLUMNS, NONEMPTY({” + Parameters!UniqueName_Name.Value + “},{[Measures].[Belopp]}) ON ROWS from [Transactions]”

The part “Parameters!UniqueName_Name.Value” comes from the parameter created before.

After this you have to create your own fields because of the custom made expression:

These need to look like this:

See picture below to see where to write this.

tut1_createdatasetfields

 

 

Then I create a simple table with the detailed information I want to display. The “@Name_Name” is just a textbox with the expression set to Parameter!Name_Name.Value which will be what the report get from it´s parameter.

tut1_createTable

If you try to preview the report now you will get: tut1_previewSubReport

This because the parameter will come from the main report as mentioned before.

Now it´s time to create the main report and create the datasource and dataset. Then go to query designer inside dataset properties. You need to create two calculated fields which get the unique name and the name, they could look like this:

  • [Kunder].[Namn].CURRENTMEMBER.Name
  • [Kunder].[Namn].CURRENTMEMBER.UniqueName

tut1_createDataSetQueryDesignerReportMain

Then create a table and right-click on the column you want to be able to click on in the report to go drillthrough to the first created report:

tut1_designViewReportMain

Here you need to set “Go to report”, select the report you created before and add the two parameters which exists in the subreport.

tut1_setParametersToSubReport

 

Then just preview the main report and click one rows and you should get the drilldown effect and see the subreport.

Advertisements
Tagged with: , , , ,
Posted in Report Building

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Archive
Categories

Blogstatistics
  • 3,870 hits
Boyan Penev on Microsoft BI

This is my journey towards greater knowledge with Microsofts Business Intelligence Solutions.

Sherry's BI Corner

All about Microsoft’s BI Platform (SQL Server, Reporting Services, Analysis Services, Integration Services)

Sherry's BI Corner

This is my journey towards greater knowledge with Microsofts Business Intelligence Solutions.

Comments for CALUMO

This is my journey towards greater knowledge with Microsofts Business Intelligence Solutions.

True Tiger Recordings

This is my journey towards greater knowledge with Microsofts Business Intelligence Solutions.

Data Inspirations

This is my journey towards greater knowledge with Microsofts Business Intelligence Solutions.

%d bloggers like this: