Create Report Subscription in SSRS – Part 2

3
1717

This article is further to my previous post onCreating 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.

Shared Data source and Shared Datasets in SSRS

  • 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.

Data Region in SSRS

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 –

Report manager url in SSRS

  • In the above wizard, click on Data Sources. Once you click on Data sources, following window will appear –

Data Sources in Report Server in SSRS

  • 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 –

Credentials for Report Subscription in Report Server

  • 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.

Home area in SSRS Report Server

  • In the above window just click on “Report project1”. Once you click on this, following window will appear.

Click on Report server1 in home area

  • 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.

Click on Subscribe option from drop down in SSRS

  • Once we click on “Subscribe”, the following window will appear on our screen.

Subscription in SSRS

  • 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.

Subscription in SSRS

  • 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.

Share ssrs report with specific people in windows file share

  • 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.

File sharing Wizard in SSRS

  • Just copy and paste the above path “\\AVINASHREDDY\subscription” at path place in subscription window.

Subscription folder using windows  file share in SSRS

  • 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.

Schedule Details for SSRS report Subscribtion

  • 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.

Choose Hourly Schedule details for report subscription

  • Click OK button consequently two times. Once we complete all the settings, window will look like this –

Final windows file share wizard in SSRS Report Subscription

  • 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.

SQL Server agent in SSRS

  • 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.

Empty folder before scheduled time in SSRS

  • 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 –

Scheduled reports in SSRS

  • See after 2 minutes again –

Scheduled reports in SSRS after 2 minutes

  • 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 –

Scheduled reports in SSRS after 18 minutes

  • Finally open up one excel file to see what data it contains actually.

Values in Excel file generated by Report subscription in ssrs

Summary :-

  • 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.

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 10.0/10 (9 votes cast)
VN:F [1.9.22_1171]
Rating: +10 (from 10 votes)
Create Report Subscription in SSRS - Part 2, 10.0 out of 10 based on 9 ratings
  • Saurab

    This has to be the most easy and straightforward tutorial on subscription for SSRS – #MSBI #PhpRing!

  • Chrisitine Vo

    Hi Avinash,
    Thank you very much for a detailed instruction on SSRS subscription. Under Step 4: Executing Subscribed Report in SSRS, we need to ensure SQL Server agent is in ON mode; however, when I right click on the server, the START, STOP…option gray out. When I scheduled the subscription, it had an error message “…The network path was not found. ” Do you have any idea what went wrong in my steps? Thank you so much in advance for your help!