1) It is a client tool to work with client reports and management reports.
2) It does various operations such as
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
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
View → It displays the report
Properties →
a) General → It displays
Properties
Name: Party-report
Description:
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
Create a linked report when you want to use different securities and parameters with the
report (then it is recommendable)
Ex:
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
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
b)With cache:-
c) With snapshot:-
It maintains history
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
Report – specific schedule [Configure]
Click Apply
History → Here the settings related to history available (SHM) like manual History
maintenance, schedule history maintenance… etc
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:
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
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.
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.
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:
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
SSIS | Power BI |
SSAS | SQL Server |
SCCM | SQL Server DBA |
SharePoint | BizTalk Server |
Team Foundation Server | BizTalk Server Administrator |
Our work-support plans provide precise options as per your project tasks. Whether you are a newbie or an experienced professional seeking assistance in completing project tasks, we are here with the following plans to meet your custom needs:
Name | Dates | |
---|---|---|
MSBI Training | Dec 07 to Dec 22 | View Details |
MSBI Training | Dec 10 to Dec 25 | View Details |
MSBI Training | Dec 14 to Dec 29 | View Details |
MSBI Training | Dec 17 to Jan 01 | View Details |
Ravindra Savaram is a Technical Lead at Mindmajix.com. His passion lies in writing articles on the most popular IT platforms including Machine learning, DevOps, Data Science, Artificial Intelligence, RPA, Deep Learning, and so on. You can stay up to date on all these technologies by following him on LinkedIn and Twitter.