Last week I had blogged about how to export SQL Server data to multiple sheets of an Excel file using SSIS
http://visakhm.blogspot.in/2013/09/exporting-sqlserver-data-to-multiple.html
One of my readers asked me whether we could achieve this using any other method. There's a much easier method using SSRS if you've a reporting server instance available. We will see the steps to achieve the requirement using SSRS in this post.
Open a report server project in Business Intelligence Development Studio (BIDS)/ SSDT. Add a data source to the server containing the table(s) with required data.
For this illustration I'm using the same table I used for earlier post (Marklist) which is in my local server instance.
Once this is done, the next step is to create the dataset to pull the data from your required table. For this click on datasets folder on the left window and choose Add Dataset. Make an embedded dataset which points to the datasource created earlier as per below
Once you create the dataset and click refresh fields the fields from the table/query will be populated inside dataset which can be dragged and dropped within report designer inside various containers like table, matrix etc.
We'll use a table container in this case to display the data. Drag and drop a table container from tollbox to design window and fill the inside with the fields from dataset by clicking on them and dragging them onto table cells.
Now add a grouping on Subject field to make it break the data based on Subject value and set a page break property to appear after each instance of group.
http://visakhm.blogspot.in/2013/09/exporting-sqlserver-data-to-multiple.html
One of my readers asked me whether we could achieve this using any other method. There's a much easier method using SSRS if you've a reporting server instance available. We will see the steps to achieve the requirement using SSRS in this post.
Open a report server project in Business Intelligence Development Studio (BIDS)/ SSDT. Add a data source to the server containing the table(s) with required data.
For this illustration I'm using the same table I used for earlier post (Marklist) which is in my local server instance.
Once this is done, the next step is to create the dataset to pull the data from your required table. For this click on datasets folder on the left window and choose Add Dataset. Make an embedded dataset which points to the datasource created earlier as per below
We'll use a table container in this case to display the data. Drag and drop a table container from tollbox to design window and fill the inside with the fields from dataset by clicking on them and dragging them onto table cells.
Go to the group properties by selecting on group and clicking on properties icom on top and in the properties window which appears on right, expand Group -> PageBreak and set PageName property to Subject fields. This property will make sure the sheets in Excel are named based on Subject field values.
Now deploy the report to the local report server by setting report server url property value in project properties
Set report server name correctly as shown in red. This can found out from Report Server Configuration Manager by looking at web service Virtual Directory value.
Once this is done , click on the deploy button on the solution explorer in BIDS/SSDT to deploy the report to the server.Once the report gets deployed to the server, invoke report using weblink after passing the format value as Excel (rs:Format).
The weblink will be of the form
http://machinename/webservicevirtualdirectory/Pages/ReportViewer.aspx?Fullreportprojectpath/ReportName&rs:Format=Excel
ie in my case
http://localhost/ReportServer_SQLSERVER2008R2/Pages/ReportViewer.aspx?%2fReport+Project1%2fPagedreport&rs:Format=Excel
This will popup a window asking you to open the Excel or save it and on selecting the required option you'll get the Excel file opened or saved as below.
This will clearly have data displayed as tabs broken based on the subject value as group.
This is exactly similar to what we got using SSIS as in previous blog.
You can automate this by creating a subscription for this report in the report server and choosing the delivery option as email or file share depending on whether you need the report to be saved to a folder or sent as an email attachment.
No comments:
Post a Comment