How to Access Report Manager in SSRS 2012
Working with Report Manager
1) It is a client tool to work with client reports and manger reports.
2) It does various operations such as
- Creating and manipulating folders.
- Creating snap shots, caches, maintaining histories
- Creating and working with linked reports etc.
It displays various report manager items. They are creation date, modified date, created user and modified user names etc…
It creates a new folder in the report Manager
Click New Folder → Name → Ok
a) NEW Data source → Data Source credentials should be stored permanently in the Report server in the below situation
- creating caches, snapshots, history
- while creating subscriptions
CREATING A REPORT FROM EXCEL SHEET
Create a Driver for excel sheet
Start → settings → control panel → Administrative tools → databases → rt Click → add → ODBC Ms Excel setup → Data source name → DS Excel → Select work book → desktop → folder → ok → ok → ok
→ View → report data → New Data source → General → Name → DST EXCEL → Embedded connection → type (odbc) → EDIT → click option string connection
→ Build → Machine data source driver name: DST_EXCEL → Ok → Ok → Ok
→ New data set → Name → dst Excel → click query builder → query designer → OK → OK
→ VIEW MENU → report data → tool box Properties → table → drag on to the layout
→ While working with site settings etc. click new data source
Name : DS-DB-MSBI
Data source type: Microsoft SQL Server
Connection string: Data source =.; initial catalog = DBMSB
ʘ Credentials stored securely in the report server.
User name: Rawan \administrator
Use the windows authentication
Click → apply
Upload file:- It uploads files to report manager.
Upload file menu → Click ok click → Click ok file to upload [ ] [Browse]
→ Click ok
Working with individual properties of a report:
View → It displays the report
a) General → It displays
- Modified date:
- Modified by:
- Created Date:
- Created by:
Name : Party-report
Updating Report Definition
This option is helpful to directly update the definition of report without deploying from its position
Report definition → update → file to upload: [ ] [Browse]
→ specify the rdl file → click ok → Click apply
Move:- Moving the report to specific folder
Delete:- Deleting the report
Creating and working with linked report
Create a linked report when you want to use different securities and parameters with the report (then it is recommendable)
From the above example saving & current accounts having their links to main report. Now connect and see the required parameter content from the report click create linked re port à name à change location à OK GO TO the corresponding folders & observe the links.
b) Data Sources:- Same as above like data source creation. In addition it displays the existing data source also (like page no 54)
Working with site settings
It’s having sharable options across reports.
Select the default settings for report history:
ʘ Keep an unlimited no of snapshots in report history
○ limit the copies of report history:
Report timeout report execution
○ Do not timeout report execution
ʘ Limited report execution to the following no of seconds:
Custom Report builder launch URL:
c) Schedules à Click new schedule
Schedule name: SC_SHARED
Define a schedule that runs on hourly, daily, weekly, monthly or one time basis
Note:- The above security, schedule, general options searchable across all reports.
ð Upload file:- It is used to upload any files to report manager
a) with no cache → If data is changing frequently.
b) With cache → If data changing for 30 to 40 minutes.
c) With snapshot → If data changing after hrs (or) day, we go for snapshots.
a) With no cache:-
(Frequently task data changes) Database
c) With snapshot:-
It maintains history
1) No start time but end time is available There is start time but no end time
2) Created for the first request of the user Created at the specified time
3) No history is maintained History is maintained
4) For small data reporting and frequently For long running queries, for huge data we
changed data (every 30 min, 40 min) go for snapshots
it is recommendable
5) Ex:- changing mobile no in internet it Ex:- Sensex data is not changing evening 3:50
takes 30 min to update during the time Pm to next day morning 9:00am
we fire any no of queries it gets old number
NO cache:-If the table data is changing frequently we can not choose cache or snapshot because we need to get latest data every time
Properties → Execution →
ʘ Always run this report with the most recent data
ʘDo not cache temporary copies of this report
Cache Implementation:- For cache there is start time but no end time.
a) Cache a temporary copy of the report expire copy of report after a number of minutes:30
b) Cache a temporary copy of the report expire copy of report on the following schedule
ʘ Report specific schedule [Configure]
ʘ Shared schedule
To see the cache creation and separation:
Go to → SSMS → Database engine → report server temp _DB → Execution cache
ʘ Render this report from a report execution snapshot
- Use the following schedule to create report execution snapshots:
Report – specific schedule [Configure]
History → Here the settings related to history available (SHM) like manual History
maintenance, schedule history maintenance… etc
- IN times of SHM we need to specify start time to move snap short
- We can limit the no of snapshots in a history to history
- Allow the report history to be created manually
- Store all report execution snapshots in history
- Use the following schedule to add snapshots to report history.
Report – specific schedule [configure]
At 9:02 PM every mon of every week, starting 9/21/20ʘ
Select the no fo snapshots to keep:
Use default settings
Limit the copies of report history: 
Database engine → report server database → snapshot data → monitor → snapshot information database engine → report server database → history.
Security:- RS supports Role based history the default roles available are:
- Browser → May view folders, reports and subscribe to reports.
- Content Manager
- My Reports
- Publisher → May publish reports & linked reports to the report server.
- Report Builder → May view report definitions
a) Every role will have set of responsibilities.
b) Users (or) groups assigned to those default roles 30 that they perform the underlined responsibilities of the role.
Note: In 2005 we can create our own role and we can assign required responsibilities. But 2008 doesn’t support own role and responsibilities creation.
USERGROUP VINAY/DEV-TEAM SIVA/TEST-TEAM
Role Developer Tester
Responsibilities coding unit testing System testing, defect raising.
Security → New Role Assignment → specify group or user name: [ ]
(local computer n)
→ Click the option report builder → click ok
Delivering the report to the corresponding persons in the corresponding format at a stipulated time is called subscription.
These are of two ways to delivery the reports they are :-
a) File share
Note:- We can deliver the report through customized extensions also
Two types of subscriptions
→ Standard type
→ data driver type
To deliver the reports through E-mail we must perform (or) we must set E-mial settings in the report server configuration manager.
Start menu → programs → SQL server 2008 → Configuration tools → Reporting Services configuration manager
→ E-Mail settings
→ SMTP settings
To edit, change the fields & click the apply button
Here to the specified user, the report is delivered and it is event driven (schedule).
Here parameter values and vendering format can be set along with the report.
Generally small scale reports delivered through this.
Go to the individual reports → click subscription → click new subscription
Report delivery options:
Specify options for report delivery
Delivered by: windows file share
File name: party – Report – today
Render format: XML file with report data
Credentials used User name:
To access the file password:
Over write options:
ʘ Overwrite an existing file with a newer version
Go to Report → select new subscription delivered by : e-Mail
Subject: @Report name was executed at @execution time
- Include report render format: [MHTML Archive)
- Include link
When the schedule run is complete [Select Schedule]
Data Driven subscription:
Here we are deliver to any no of users with their required parameters (according to the format what they require reports with generally large parameters we go for this.
1) Create a table in the database with the below fields
File name path Render-Format Write mode File Extn. user Name pwd
party_report \\rawan\report excel y(yes) .doc admin vinay
party_madhu \\rawan\report excel y(yes) .xls Vinay Madix
2) Go to the reports → subscriptions → new data driven subscription →
Step-1:- create a data –driven subscription: party-report
Specify how recipients’ are notified: [Windows File share]
ʘ Specify for subscription only à click next
step-2:- Data source type: MS SQL Server
Connection String: Dataset = Rawan ; initial catlog= ‘MSBI_DB”,
Credentials used to Username:
Access the file share Password:
- Use as windows credentials
Step-3 :- Enter a query: Select * form muluser → next
Step-4:- File name
ʘ get the value from the database: [File name]
ʘ Get the value form the database: [path] Render format
ʘ Get the value from the database: Render _ Format write mode
Step-5:- click next
Step-6:- Notify recipients:
ʘ on a shared schedule
Null delivery provider:
Here recipients are notified without any report
- Null delivery doesn’t have any settings
- If report cache is enable then the result of subscript will end –up here.
Enroll for Live Instructor Led Online MSBI Training