Using SSRS to Export SQLServer data to multiple Excel Sheets

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.