Connecting SSRS 2008 Data Source

4/27/2010 4:51:49 PM

In this blog post you will learn how to connect to a Data Source and create your first Data Set. You can then use this to extract data from any OLAP cubes and compile a report that you can then export in PDF, XLS or XLSX format.

Follow the instructions below in order to get some data from the Adventure Works 2008 Data Warehouse Cubes.

  1. Create a new Data Source by clicking on New Data Source in the Report Data section of SSRS. If this window is not shown you can view it by going to View Report Data.
  2. This should open a Data Source Properties window.
  3. Inside the Data Source Properties window give your Data Source a name
  4. Change its type to Microsoft SQL Server Analysis Services.
  5. Press Edit beside connection string and point to your source of the cubes.
  6. Once you have sucessfully tested your connection you can press OK.
  7. Data Source Screen Shot
  8. You can now use that Data Source to create a Data Set and bring back some data for your report.
  9. Right click on the new Data Source and select "Add Data Set"
  10. Give your new Data Set an appropriate name.
  11. Select your Data Source from the drop down box.
  12. Click on Query Designer to create a query that will be used to extract data.
  13. Here you can drag whatetver measures and dimensions you want. For me I will drag out the Internet Gross Profit Measure on Internet Sales and the Product Category and Prodcut Sub Category.
  14. Query Designer With Data
  15. If you press OK now you will see that your Data Set (in the Report Data window) now contains all of the dimensions you need in order to make a report.
  16. You can now drag a chart onto your page now and drag thosefields onto the report.