Search This Blog

Wednesday, 24 September 2014

Create SSRS Report using SSAS as a Data source

I am assuming that you are familiar with creating SSRS report using T-SQL as Data source. In order to create a report using SSAS as a data source you have to perform the following steps.
     1.  Create a Data source using SSAS  
     2.  Just follow the same steps as you use to create the TSQL DataSource , But while selecting source select  “Microsoft SQL Analysis Services “ See image below .Rest of the steps are same as you use to perform in T-SQL report DataSource

Now just go to the report and create a Dataset same you use to create in T-SQL report

In function window paste your query like below image

SELECT                {
                                            [Measures].[Internet Order Quantity],
                                            [Measures].[Internet Tax Amount]
                                } on COLUMNS ,
NON EMPTY ([Product].[Product].[Product] ) on 1 from
[Adventure Works]

Now Click on “Field” TAB check your filed data source see below image

At the above stage, you need to manually change the Field Names and give appropriate names. So that it would be easy for us to use these names at report coding. Refer following details.
 Copy Field source XMLA and paste it somewhere like below
<?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="" xmlns:xsd="" xsi:type="Level" UniqueName="[Product].[Product].[Product]" />
See unique name according to your unique name provide the name of your field see image above
Note: If type =”level” it means this your dimension/level field and if it is type =”Measure" then it is Measure
After performing all the steps your dataset is ready with fields 

Now you can Preview report.