In this SSRS Tutorial I will show you how to Create Dataset in SSRS 2012.
In this Tutorial I will show you how to add Dataset to your Report.To set up Dataset we have two ways :
1. First one - is to set up Shared Dataset, which allows us to use the same Dataset for more than just one report however we use that option generally for generic drop down boxes only otherwise it creates dependecies and increase risks and testing effort.
2. Second one - is to add Dataset in the report we are working in - which doesn't allow sharing it there there are no dependencies.
I will include more details about this when we will talk about Shared Dataset and provide exception scenarios when we will use it.
To Embed Data Set on a Report I will go to the left side and in the Report Data pane I go down to Datasets folder and right click on it - you can see option below.
Now you can see Dataset Properties window where we have two options:
1. Use a shared Dataset
2. Use a dataset embedded in my report
>>> I will choose the second option.
Next step is to type the Name for this Dataset - in my case it will be CustomerProductSales.
In Data source option click New and you can see Data Source properties window you can see that at the bottom I have option - use shared data source reference. I select this option and from the drop down box I choose AdventureWorksDW.
After clicking OK you have to come back to the first Dataset properties window and click Query Designer.
In the Query Designer you can type code. In my case I will paste my code and execute it (!). Look at the pictures below which will show you this steps.
|SELECT fis.CustomerKey, c.FirstName, c.LastName, c.AddressLine1, c.AddressLine2, c.EmailAddress, fis.TotalProductCost
FROM FactInternetSales AS fis INNER JOIN DimCustomer AS c
ON fis.CustomerKey = c.CustomerKey
Now you can see that there is a lot of rules. Normal in this case I would create parameter which allows me to filter my data but in this case I just click OK and one more time OK.
I will get error message said that I have a space in my Name. I click OK and next Remove my space from the Field Name in Dataset Properties.
In the Report Data pane you can see that new Datasets - CustomerProductSales was created.
I hope this SSRS Tutorial was helpful.
Katie & Emil