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

  • Subscription
  • 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!

Navigation:
HTTP://SERVERNAME:IP/REPORTS

SHOW DETAILS:
It displays various report manager items. They are creation date, modified date, created user and modified user names, etc…

NEW FOLDER:
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

Name: DS-DB-MSBI
Description:
Data source type: Microsoft SQL Server
Connection string: Data source =.; initial catalog = DBMSB

Connect using:
?    Credentials stored securely in the report server.
User name:         Rawan administrator
Password:
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
Properties →
a) General → It displays

  • Modified date:
  • Modified by:
  • Created Date:
  • Created by:
  • Size:

Properties
Name: Party-report
Description:

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)

Ex:

 Linked report

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)

Frequently Asked MSBI Interview Questions & Answers

Working with site settings

It’s having sharable options across reports.
a) General
Properties
Name:
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:
URL:
Apply
b) Security
c) Schedules  à Click the new schedule
Schedule name: SC_SHARED
Schedule details
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..!

 With no cache

b)With cache:-

 With cache

c) With snapshot:-
It maintains history

 With snapshot

CACHES                                                                                SNAPSHOTS

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]
or
?             Shared schedule

To see the cache creation and separation:
Go to → SSMS → Database engine → report server temp _DB → Execution cache

Snapshot implementation:

Execution →
?  Render this report from a report execution snapshot

  • Use the following schedule to create report execution snapshots:

Report – specific schedule [Configure]
Click Apply
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: [100]
[apply]

Navigation:-

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.

Related Page: Action Properties Dialog Box (Report Builder And SSRS) - MSBI

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.
Requirement analysis

Navigation:-Security → New Role Assignment → specify group or user name:  [          ]

                                                    ↓
                                     (local computer n)
→ Click the options report builder → click ok

Subscriptions:

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
b) E-Mail

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.

Navigation:-

Start menu → programs → SQL server 2008 → Configuration tools → Reporting Services configuration manager
Connect:

→ E-Mail settings
→ SMTP settings
To edit, change the fields & click the apply button

Standard subscription:

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
Path: //rawanreports.
Render format: XML file with report data
Credentials used               User name:
To access the file              password:
Share:
Overwrite options:
?              Overwrite an existing file with a newer version

E-mail subscription:
Go to Report → select new subscription delivered by: e-Mail
To: ADMINISTRATOR@DWHT.COM
cc: VINAY@DWHT.COM
Bcc:
Reply to:
Subject: @Report name was executed at @execution time
Include report render format: [MHTML Archive)
Include link
Priority: [High]

Comment:                                                   Report

When the schedule run is complete  [Select Schedule]

Data-Driven subscription:

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.

 Data Driven subscription

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    awanreport     exce y(yes)  .doc   admin       vinay
party_madhu awanreport    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

Description:

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

Click next
Step-3:- Enter a query: Select * from multiuser → next
Step-4:- Filename
?  get the value from the database: [File name]
path
? Get the value form the database:  [path]                               Render format
?  Get the value from the database: Render _ Format write mode
File extension
User name
Password

Step-5:- click next
Step-6:- Notify recipients:
?  on a shared schedule
Click next.

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: