This article is further to my previous post on “Creating Report subscription in SSRS”. To see the 1st part of this article, Visit here – Report Subscription in SSRS – Part 1. In this post, i have shown how good is this reporting tool (SSRS).
Previously we created a user named TEST with Admin access and provided SQL Server roles to that user. In this article, we will start with creating an SSRS project. Then, we will create Shared Data source and Shared Dataset. After this, we will Deploy that report to the Report server. Once the Deployment is successfully completed, we will create Report subscription and schedule it as per the business requirement. After scheduling, we will start SQL Server agent to see the scheduled Reports being generated.
Step 1 :- Report Creation
- Open BIDS (Business Intelligence Development Studio). Then, Create a SSRS project.
- Once we create a New project then Report designer will appear. Under the Report project name we have two options – Shared Data source and Shared Dataset.
- To Create Shared Data source – Click on Shared datasource. Now, Right click and choose ‘Add New Datasource’. At the time of creating Shared datasource, give Server name as ‘localhost’ and Database name as ‘PHPRING’.
- To Create Shared Dataset – Click on Shared dataset. Right click and choose ‘Add new Dataset’. Then write the following query in that dataset :- Select * from emp. This “emp” table exists in our PHPRING database. I am just going to call it in Dataset.
- Once Shared Data source and Shared Dataset creation is over, Go to “Reports”. Right click on it and under Add, Choose “New Item”. Follow the below screen shot if you all have any doubts regarding Shared datasource and Shared dataset.
- Once you create a Report, go to Report data pane. Click on Data source. Add Datasource and then choose used shared Datasource reference. Choose Shared Datasource name (i.e. Datasource1).
- Now, To create a new Dataset. Click on new dataset. Choose use a Shared dataset option. Select “Dataset1”. This is our dataset name. Finally click OK button.
- Go to Data region. Choose Region as table to display our data. Then Add column fields in that table just like below Screen shot.
Step 2 :- Deploy report to Report Server in SSRS
- Once you are done with report creation, again open BIDS with Admin mode to deploy you reports to “Report server”. Firstly, Click on Report server project name. Then right click and choose Deploy option.
- Open IE (internet explorer in Admin mode) with following url (http://localhost/reports).
Note :- The above url is the Report manager url. We will get this at the time of installing Report configuration wizard. The IE appears like this –
- In the above wizard, click on Data Sources. Once you click on Data sources, following window will appear –
- In the above screen shot we can see “DataSource1”. This is our shared datasource name. Simply click on this datasource1.The following window will appear –
- In the above window check “Credential stores securely in the report server”. After that give user name and password as – Username=test ; Password=test.
- Now, Click on Test connection and click Apply button.
Step 3 :- Report Subscription in SSRS
- Click on Home area in Report manager. Once we click on home the following window appear.
- In the above window just click on “Report project1”. Once you click on this, following window will appear.
- Select the Report SAMPLE (This is our report created in BIDS for this demonstration) and then click on Subscribe. Follow the below screen shot if you have any doubt.
- Once we click on “Subscribe”, the following window will appear on our screen.
- By observing above screenshot, we have only one option to choose in Delivered by section i.e. Choose Windows File Share.
Note :- If we have SMTP connection, one more option would have come into picture, but here we have only one. Because I do not have any “SMTP” connection right now. That’s why I “choose Windows File Share”.
- Once we choose “Windows file share “option, below window will appear.
- In the above screen shot we have different options we can go through each one of them now. Give file name =sample and For path name I can go to my ‘E’ drive and create one folder name as ‘Subscription’.
- This is an empty folder. After this we can share this folder with TEST user. For doing this just right click on folder ‘Subscription’ and go to Share with and click specific people.
- Once we click on specific people the following wizard will appear. Just click on user TEST and click Add. Finally click on share button. The following message will appear.
- Just copy and paste the above path “\\AVINASHREDDY\subscription” at path place in subscription window.
- Provide other details as Render format = Excel; Give credentials of TEST user; Username=test; Password=test.
- Click on select schedule. Once we click on schedule the following window will appear.
- In the above window, check Day as “MON” and Time as 6:30 PM. Now, click on “HOUR”. Once we click on hour the following wizard will appear. Give Run schedule every 2 minutes and start time 6:30 PM.
- Click OK button consequently two times. Once we complete all the settings, window will look like this –
- In the above window we can see all the settings. Follow these settings and click OK button.
Step 4 :- Executing Subscribed Report in SSRS
- The above schedule will run by SQL server agent .Make sure that this service is ON.
- Open SSMS and look whether SQL Server agent is in ON mode or OFF mode. If it is in OFF mode make it to ON mode.
- Follow the below screen shot, In my SSMS it is in off mode so am going to start now.
- Once we start the SQL server agent it looks green in color. Before we have seen RED mark but now we can see GREEN mark.
- Finally wait for schedule time. In our case it is 6:30 pm. This SQL server agent will run our subscription for every 2 minutes.
- Open our folder path and see the files generated by SQL server agent in the form of Excel.
- In the following picture we can clearly observe that we don’t have any files Before 6:30 PM. You can also see the time it displayed in the bottom of this screen shot,it shows 6:27 pm.
- At 6:30 pm, we will receive scheduled report –
- See after 2 minutes again –
- By seeing above screen shots, we can conclude that our subscription is working according to our schedule. In our schedule we gave 2 min time. So for every two min one file will be incremented automatically.This is the beauty of Subscriptions.
- After 18 Minutes, we will have in total 10 subscribed reports in our folder –
- Finally open up one excel file to see what data it contains actually.
- Part 1 : – Report Subscription in SSRS – Part 1
- Created SSRS project and then created shared datasource and shared dataset.
- Deployed that Report to Report server.
- Opened IE with admin access.
- Cretated Subscription and Scheduled the report with some time.
- Started SQL server agent.
- Finally saw the reports that are generated according to our scheduled time (for this Open shared folder path and see for every 2 min, we got one excel file).
This is the end of Report Subscription in SSRS article. This reporting tool (SSRS) provides many other features which we will post I hope you all enjoyed both the parts. If anybody still have doubt regarding this then please drop them as comments.