SSRS–Creating reports using Custom MDX or DAX to query SQL Server Analysis Services (SSAS) Cube both OLAP and Tabular (Not using the Wizard)
By default in SSRS you can query an SSAS Cube, but this is by using the drag and drop functionality. The issue with this is that it has quite a few limitations. One of the biggest limitations that I found is that you cannot use your own Custom MDX to create your data set.
NOTE: You can use the same details below to connect to the Tabular Version of SSAS, and then use DAX
Following on later from this blog, I will also explain and show you how to pass parameters to your MDX Query so that you can dynamically change your MDX query based on the users parameter selection.
You can get the SSAS and data warehouse files from the following location below, which is from Microsoft:
· This is the Data warehouse files which are used in your Multidimensional Project in order to get the data for your SSAS Cube:
· This is the Multidimensional Project which you can use to deploy to your SSAS Instance:
What we are going to achieve is to create an SSRS Report, which will get its data from an SSAS Cube, using our Custom MDX Query. And we will show the Internet Sales, by Date and Country
1. First thing you will need to do is have created your SSRS Project.
2. Next is to right click on Shared Data Sources and select the following:
a. 
3. This will then open the Shared Data Source Properties Window
a. Where it says Name, change this from the default to a meaningful name which will describe the type of data source as well as where you are connecting go.
b. In our example I will give it the name of SSAS_AdventureWorks
c. Where it says Type, click on the drop down and select OLE DB
i. NOTE: The reason why we are doing this, is because the OLE DB provider is the only provider which we can then use to Query the SSAS Cube.
d. Then click on Edit
i. Where it says OLE DB Provider click on the drop down and select the following:
1. 
2. NOTE: The reason I have Microsoft OLE DB Provider for Analysis Services 11.0 is because I am running SQL Server 2012, you might have version 10.0 if you are running SQL Server 2008 R2
ii. Where it says Enter a Server or File name, put in the connection to your SSAS instance.
1. In our example our server was called ServerName
iii. Then were it has the Log onto the Server, select Use Windows NT Integrated Security
1. NOTE: This is the only option that you can select, because it is currently the only means of authentication to SSAS
iv. Then were it says Initial Catalog, click on the drop down and select your SSAS Database.
1. In our example we selected AventureWorksDW2012
2. NOTE: By clicking on the drop down, you will also have to authenticate which is a good test to ensure that you can connect to your SSAS instance.
v. ClickOk
vi. This is what it will look like below once completed.
vii. 
e. Click Ok again.
f. And this is what it will look like when you have finished creating your Shared Data Source
g. 
4. Now you will see the following under your Shared Data Sources
a. 
5. Now add a report to your Project.
1. Next we are going to create our data source and our data set.
2. Right click on Data Source and select Add Data Source.
a. We then configured our Data Source as shown below:
i. 
b. NOTE: We always give our Data Source Name the same name as our Shared Data Source Reference.
i. We do this so when we need to troubleshoot we know which Shared Data source to look at.
c. Click Ok
3. Next right click on Datasets and select Add Dataset, which will open the Dataset Properties Window
a. Under Name, once again give it a meaningful name, so that later when you need to troubleshoot you know which query to look at and potentially what data is has.
b. For our Example we have it the following name:
i. InternetSales_Date_Country
c. Then select Use a dataset embedded in my report
d. Click on the drop down next to Data Source and select the one we created above:
i. SSAS_AdventureWorks
e. Now where it says Query Type, leave the default which is Text.
f. NOTE: You should have your MDX Query ready and tested.
i. I had done this already by writing and testing my MDX query in SQL Server Management Studio (SSMS)
g. Now in the Query Text box paste in your query
i. As per our example we pasted the following:
Selectnonempty
{
[Measures].[Internet Sales Amount]
} on 0
,nonempty
{(
[Ship Date].[Date].Children
,[Sales Territory].[Sales Territory Country].Children
)} on 1
from [Adventure Works]
h. Next click on the Fields in the left hand Window
i. 
ii. NOTE: The reason that we do this is because by doing this, it has to run the query to get the Field Names and Field Source from your SSAS Instance.
iii. I then change the Field Name to be shorter and more meaningful as shown below:
iv. 
i. If you click back onto Query you will see the following configured:
i. 
4. You will now see your dataset created in SSRS
a. 
5. Now the final part is to create your SSRS Report, as you normally would using your Datasets.
6. I created a quick Sample report based on the above data below:
7. 