Working with Report Manager
1) It is a client tool to work with client reports and management reports.
2) It does various operations such as
- Creating and manipulating folders.
- Creating snapshots, caches, maintaining histories
- Creating and working with linked reports etc.
Learn how to use SSRS, from beginner basics to advanced techniques, with online video tutorials taught by industry experts. Enroll for Free SSRS Training Demo!
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 → toolbox Properties → table → drag on to the layout
→ While working with site settings etc. click the new data source
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 the 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:
Updating Report Definition
This option is helpful to directly update the definition of the 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 a 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 the main report. Now connect and see the required parameter content from the report click create linked report à 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 the new schedule
Schedule name: SC_SHARED
Define a schedule that runs on an 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 the 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
Subscribe to our youtube channel to get new updates..!
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 on the 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:00 am
we fire any no of queries it gets an old number
NO cache:-If the table data is changing frequently we can not choose cache or snapshot because we need to get the 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 a start time but no end time.
a) Cache a temporary copy of the report expire copy of the report after a number of minutes:30
b) Cache a temporary copy of the report expire copy of the 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 the start time to move to snap short
- We can limit the no of snapshots in history to the 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 a 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 its own role and responsibilities creation.
USER GROUP VINAY/DEV-TEAM SIVA/TEST-TEAM
Role Developer Tester
Responsibilities coding unit testing System testing, defect raising.
Navigation:-Security → New Role Assignment → specify group or user name: [ ]
(local computer n)
→ Click the options report builder → click ok
Delivering the report to the corresponding persons in the corresponding format at a stipulated time is called a 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-mail 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 rendering 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
Filename: party – Report – today
Render format: XML file with report data
Credentials used User name:
To access the file password:
? 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)
When the schedule run is complete [Select Schedule]
Here we 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|
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 * from multiuser → next
? 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 the report cache is enabled then the result of the subscript will end –up here.
List of Related Microsoft Certification Courses:
|SCCM||SQL Server DBA|
|Team Foundation Server||BizTalk Server Administrator|