SQL ERROR : Unable to Uninstall – Index was Outside the Bounds of the Array YOU WILL GET THE ABOVE ERROR AT TIMES WHEN TRYING TO UNINSTALL SQL RESOLUTION : IN COMMAND PROMPT TYPE THE BELOW C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\SQL2017 FOR THE ABOVE PATH
SETUP.EXE /ACTION=UNINTSALL. NOW SELECT THE CONFIGURATION AND PROBLEM SOLVED. THANKS
SSRS INTERVIEW QUESTIONS AND ANSWERS : ALL BASIC QUESTIONS ON SQL SERVER INTEGRATION SERVICES
SSRS Interview questions with Answers
SSRS BASIC TO ADVANCED Questions
AND ANSWERS
1).
What is SSRS (Sql Server Reporting Services) ?
Reporting
Services is a server-based reporting platform that provides comprehensive
reporting functionality for a variety of data sources.Which allow you to create
tabular, matrix, rectangle, sub-report,,charts, Gauge, indicator reports in
managed environment. Like
a) Development Tool
b) Configuration Tool
c) Administration Tool
d) Report Viewing Tool
2)What
is report Manager?
Report Manager is a Web-based report access and
management tool that you use to administer a single report server instance from
a remote location over an HTTP connection. You can also use Report Manager for
its report viewer and navigation features. You can use Report Manager to
perform the following tasks:
·View, search,
print, and subscribe to reports.
·Create, secure, and
maintain the folder hierarchy to organize items on the server.
·Configure
role-based security that determines access to items and operations.
·Configure report
execution properties, report history, and report parameters.
·Create report models
that connect to and retrieve data from a Microsoft SQL
Server Analysis Services data source or from a SQL Server relational data
source.
·Set model item
security to allow access to specific entities in the model, or map entities to
predefined click through reports that you create in advance.
·Create shared
schedules and shared data sources to make schedules and data source connections
more manageable.
·Create data-driven
subscriptions that roll out reports to a large recipient list.
·Create linked
reports to reuse and repurpose an existing report in different ways.
·Launch Report
Builder to create reports that you can save and run on the report server.
3) Different Types of Roles provided by SSRS
?Ans:
1) Browsers
2) Content Manager
3) My Reports
4) Publishers
5) Report Builder
4) Describe the different processing mode by
SSRS ?Ans: Two types:
1.Local Processing Mode :- Process
Reports In The Client Machine.
2.Remote Processing Mode :- Render Server
Report That Are Processed On SSRS Report Server.
5) Name the few command utilities in SSRS 2008 ?
Ans:
1.RSConfig.exe- Configuration of connection properties between report
server to repository database.
2.RSkeyMgmt.exe- Management of encryption key via cmd line
3.RS.exe- Scripting of Report deployment
6) Name the few Endpoints exposed by SSRS 2008 ?
Ans:
1.Management Endpoint
2.Execution Endpoint
3.SharePoint Proxy Endpoint
7) What is the role of encryption key in report
server configuration Manager?
Ans: To encrypt the credentials, connection string can be taken backup and
restore when required from configuration manager.
8) Explain Reporting Lifecycle ?
Ans:
1.Report Designing- It is done in
V.S Report Designer.
2.Report Processing- Bringing the
report definition with data from the report datasource. It performs
all grouping, sorting & filtering calculation.
3.Report Rendering- Report
rendering start by passing the report instance to a specific rendering
extension (HTML or pdf formats) As a final step, the report is rendered
to a specific output document.
9) Ways to Tune Reporting Services?
Ans:
1.Expand the server or utilizing the report service of the another
database.
2.Replication of data continuously: -
Using no-lock, the issue of locking can will be resolved and the
performance of the query can be improved. This can be done by using dirty read
at the time of duplicating the data is unavailable.
10) What is the extension of SSRS reports?
Ans:
rdl(RDL stands for Report Definition language). This is an XML file
which stores query information, data source information, etc. which are
required to generate report.
11) Explain the Architecture of SSRS ?
Ans:
It is a multi tired, included with
application , server, data layers. This Architecture is scalable and
modular. A single installation can be used accross multiple computers .
It include following components -
1) Report Manager
2) Report Designer
3) browsers type supported by Reporting Services
4) Report Server
5) Report Server and Command line utilities
6) Report Server Database
7) Data Sources
SSRS
is an optional package which you can select to install while installing SQL
Server. SSRS in turn is made up of number of components.
when you install
SSRS it creates Report Server Databases in your SQL Server Instance.
These databases are
ReportServerDB and ReportServerTempDB which are used to store report
configurations and other things including Caching, Session, etc. that improvise
the overall performance. You have an option of installing other components like
Report Manager and Report Server on the same machine where SQL Server instance
is running or you can install them on a different server (typical enterprise setup).
An important thing to note here is if you opt for latter you would end up
paying for 2 or more SQL Server licenses.
As it turns out there are 3 distinguish
components of SQL Server Reporting Services:
1) Report Server: It’s an overloaded term. Largely used to
indicate a set of components that allow interaction with Report Server
database. SSRS provides Web Services (.asmx) which allows LOB applications to
interact with Report Server database directly
(http://computername/ReportServer/reportservicexxxx.asmx where xxxx is version
of SSRS). SSRS 2005 created virtual directories for Report Server & Report
Manager (discussed next), but SSRS 2008 leverages the OS level HTTP listener
making SSRS independent of IIS. This allows bundling of Report Server &
Report Manager within a windows Service ReportingServicesService.exe. The name
of this Windows Service is ReportServer. ReportingServicesService.exe
functionality also includes report processing, scheduling (auto generated
reports), subscriptions (mailers), etc.
2) Report Manager: An ASP.NET web based application
(http://computername/Reports) that in turn interacts with Report Server Web
Services. As the name indicates Report Manager allows you to manage reports in
terms configuring security access, organizing them into folders (non of these
folders map to physical directories but are stored as details in Report Server
Database), subscribing to them, etc. One can also create reports (see next
point as to how) and deploy them to Report Server Database using Report
Manager. This is handy for some restricted user / production scenarios, though
most developers prefer to do the deploy reports from BI studio. As discussed
earlier with SSRS 2008 this component is bundled with ReportServer windows
service.
3) Report Designer: There would be few guys in your team whom you
may want to designate as Report Designers. Report Designers can design reports
using VS.NET Business Intelligence projects (Report Server Project). Report
Designers create data sources (normally a shared data source (.rds) that’s used
across a set of reports), create the dataset (using queries / stored procedures
on top of data source), define relevant report parameters (mapped to datasets
for value retrieval via Report data window), field formats (using properties
window with pervasive VB expressions – e.g. formatting a textbox to display
currency decimals) and create layouts (e.g. Grouping). Once they are done with
designing their reports (.rdl files – described later) they can test (preview)
them and publish them via Report Server (this is done by providing the Report
Server URL in project properties and SSRS there creates a specific folder for
your project). Once published these reports are available for end user
consumption. Advance scenarios like interacting with Excel may require a third
party product like OfficeWriter.
There are few other important aspects of SSRS
which one should be familiar with.
Report Builder is a another tool which is
targeted at business users who want to generate custom reports on fly. Report
Builder is a ClickOnce application, intuitive and easy to use but doesn’t
support all the options available with VS.NET. It’s also possible to install
ReportBuilder as a standalone application.
Report Model is the base for report creation
with Report Builder. It’s a simplified view of relational database targeted at
business users for ad hoc report creation. Report models are created using BI
Development Studio (Report Model Project – .smdl files). A report model is
built on top of a Data Source View (.dsv) that defines a logical model based on
one or more data sources. Models generated mainly consist of entities
(relational tables), fields (attributes of a relational table) and roles
(entity relationships – 1-1, 1-*, *-1). Models also contain other attributes
like aggregate values that would help ease the reporting for end users. Post
creation report model has to be deployed in similar way as reports. You can
also use Report Model as a data source for
generating reports via Report Designer. While it’s easy to deploy Report model
from BI development studio, to deploy report model manually e.g. in production
requires you to merge the
.smdl and .dsv files.
RDL – this
is another term you would run into while talking about SSRS. RDL stands for
Report Definition language. This is an XML file which stores query information,
data source information, etc. which are required to generate report. There is
another type of report definition – RDLC (Report Definition Language
Client-side) which don’t store any of above configurations. RDLC is a client
side component (VS.NET Application Projects) to which you can pass data (e.g.
via DataSet) coming from any of data sources. RDLC can be useful for scenarios
like implementing custom pagination (SSRS 2005 pagination by default is client
side pagination).
SSRS Security is primarily windows based. When a user accesses
the Report Manager Application or ASMX Web Services he has to authenticate with
a valid domain username / password. On successful login SSRS determines the
role of the user (custom or built-in ones like Browser / Content Manager, etc.)
and displays only those reports / folders to which user has access.
Drill Down Report- Means navigate the summary level to detail level
in the same report.
Drill Through Report- Navigation from one report to another
report.
Linked Report- It is a report server item that provides an access
point to an existing report.Conceptually it is similar to a program shortcut
that you can use to run a program or open file.
Q1. WHAT is SQL
Server Reporting Services(SSRS)?
SQL Server Reporting Services is a server-based
reporting platform that you can use to create and manage tabular, matrix,
graphical, and free-form reports that contain data from relational and
multidimensional data sources. The reports that you create can be viewed and
managed over a World Wide Web-based connection
Q2. Architecture of SSRS:
-Admin
Q3. What are the three stages of Enterprise
Reporting Life Cycle ?
a. Authoring
b. Management
c. Access and Delivery
Q4. What are the components included in SSRS?
1. A Complete set of Tools that can be used to create, manage and view
reports
2. A Report Server component that hosts and processes reports in a variety of
formats. Output formats include HTML, PDF, TIFF, Excel, CSV, and more.
3.An API that allows developers to integrate or extend data and report
processing in custom applications, or create custom tools to build and manage
reports.
Q5. What is the benefit of using embedded
code in a report?
1. Reuseability of Code: function created in
embedded code to perform a logic can be then used in multiple expressions
2. Centralized code: helps in better manageability
of code.
Q6. Which programming language can be used to
code embedded functions in SSRS?
Visual Basic .NET Code.
Q7. Important terms used in the reporting
services?
1. Report definition: The blueprint for a report before the report is
processed or rendered. A report definition contains information about the query
and layout for the report.
2. Report snapshot: A report that contains data captured at a specific
point in time. A report snapshot is actually a report definition that contains
a dataset instead of query instructions.
3. Rendered report: A fully processed report that contains both data
and layout information, in a format suitable for viewing (such as HTML).
4. Parameterized report: A published report that accepts input values
through parameters.
5. Shared data source: A predefined, standalone item that contains data
source connection information.
6. Shared schedule: A predefined, standalone item that contains
schedule information.
7. Report-specific data source: Data source information that is defined within a
report definition.
8. Report model: A semantic description of business data, used
for ac hoc reports created in Report Builder.
9. Linked report: A report that derives its definition through a
link to another report.
10. Report server administrator: This term is used in the documentation to
describe a user with elevated privileges who can access all settings and
content of a report server. If you are using the default roles, a report server
administrator is typically a user who is assigned to both the Content Manager
role and the System Administrator role. Local administrators can have elevated
permission even if role assignments are not defined for them.
11. Folder hierarchy: A bounded namespace that uniquely identifies all
reports, folders, report models, shared data source items, and resources that
are stored in and managed by a report server.
12. Report Server: Describes the Report Server component, which
provides data and report processing, and report delivery. The Report Server
component includes several subcomponents that perform specific functions.
13. Report Manager: Describes the Web application tool used to
access and manage the contents of a report server database.
14. Report Builder: Report authoring tool used to create ad hoc
reports.
15. Report Designer: Report creation tool included with Reporting
Services.
16. Model Designer: Report model creation tool used to build models
for ad hoc reporting.
17. Report Server Command Prompt Utilities: Command line utilities that you can use to
administer a report server.
a) RsConfig.exe, b) RsKeymgmt.exe, c) Rs.exe
Q8. what are the Command Line Utilities
available In Reporting Services?
· Rsconfig Utility (Rsconfig.exe): encrypts and stores connection and account
values in the RSReportServer.config file. Encrypted values include report
server database connection information and account values used for unattended
report processing
· RsKeymgmt Utility: Extracts, restores, creates, and deletes the
symmetric key used to protect sensitive report server data against unauthorized
access
· RS Utility: this utility is mainly used to automate report server
deployment and administration tasks.Processes script you provide in an input
file.
Q. How to know Report Execution History?
ExecutionLog table in ReportServer database
store all the logs from last two months.
SELECT * FROM
ReportServer.dbo.ExecutionLog
-Development
Q. What is difference between Tablular and
Matrix report?
OR What are the different styles of reports?
Tablular report: A tabular report is the most basic type of
report. Each column corresponds to a column selected from the database.
Matrix report: A matrix (cross-product) report is a
cross-tabulation of four groups of data:
a. One group of data is
displayed across the page.
b. One group of data is
displayed down the page.
c. One group of data is the
cross-product, which determines all possible locations where the across and
down data relate and places a cell in those locations.
d. One group of data is
displayed as the "filler" of the cells.
Martix reports can be considered more of a
Pivot table.
Q. How to create Drill-through reports?
Using Navigation property of a cell and setting
child report and its parameters in it.
Q. How to create Drill-Down reports?
To cut the story short:
- By grouping data on required fields
-Then
toggle visibility based on the grouped filed
Question:
How do you normally create reports (wizard/manually)?
Comment: If wizard in most cases then you got rather inexperienced person, if
manually than it is usually good answer. The best answer is using a template.
Generally developers create reports from scratch which is not bad but it is not
very efficient either.
Question: What languages have you used
to query data for SSRS Reports?
Comment: Most answers will probably be SQL (TSQL). But T-SQL is not the only
query language. If someone build reports based on cubes before than they will
say MDX. You can also query data from other sources (not recommended) or use
data mining expressions (DMX = advanced).
Question: Gives examples where you used parameters?
Comment: Typically you use parameters to filter data in datasets (or data on
reports) but you can also use them to restrict values like the well-known
make->model->year example. You can also have hidden and internal
parameters which get very handy for more advanced stuff.
Question: What types of graphs do you
normally use and what effects do you apply to them?
Comment: Good graph are bar, line, scatter, bullet graphs. Bad graphs are pie
charts, area graphs, gauges (apart from bullet graph which classified as gauge
in SSRS). Effects should be limited to minimum. Developers should avoid 3D
effects, "glass" effect, shadows etc
SQL Server Reporting Service is one of the server-based
software systems that generate reports developed by Microsoft. It is used for
preparing and delivering interactive and variety of printed reports. It is
administered through an interface that is web based. Reporting services
utilizes a web service interface for supporting and developing of customized
reporting applicatons. It can be competed with Crystal Reports and other
business intelligent tools.
Reporting services architecture is comprises of integrated
components. It is multi-tiered, included with application, server and data
layers. This architecture is scalable and modular. A single installation can be
used across multiple computers. It includes the following components:
- Report Manager, Reporting Designer, Browser Types Supported by Reporting
services, Report server, Report server command line utilities, Report Server
Database, Reporting Services Extensibility, Data sources that is supported by
Reporting Services.
- Report designing – The designing is done in Visual Studio Report Designer. It
generates a class which embodies the Report Definition.
- Report processing – The processing includes binging the report definition
with data from the report data source. It performs on all grouping, sorting and
filtering calculations. The expressions are evaluated except the page header,
footer and section items. Later it fires the Binding event and Bound event. As
a result of the processing, it produces Report Instance. Report instance may be
persisted and stored which can be rendered at a later point of time.
- Report Rendering: Report rendering starts by passing the Report Instance to a
specific rendering extension (HTML or PDF formats). The instance of reports is
paged if paging supported by output format. The expressions of items are
evaluated in the page header and footer sections for every page. As a final
step, the report is rendered to the specific output document.
To tune-up the Reporting Services, follow the below
mentioned ways:
- Expand the Server or utilizing the reporting services of another database
server. For better embedding of report contents, report application’s logic and
characteristics can have a duplicate copy of data.
- Replication of data continuously. Using nolock, the issues of locking can
well be resolved and the performance of the query can be improved. This can be
done by using dirty read at the time of duplicating the data is unavailable.
SQL Server Management Studio is used for taking backup of
SQL Server Analysis Services database. SSAS backup option can not perform the
SSAS database backup operation. To perform this task, create SQL Server Agent
Job and schedule that job as per custom requirement. The following is the
process:
► Right click the job folder of the SQL Agent node in SSMS.
► Select the option New job
► Specify the Job name for identification purpose.
► Go to Steps Page and click the New button.
► Select SQL Server Analysis Services command as the type of job after entering
the name of the step and enter the server name.
► Enter the following command.
AdventureWorksDW
AdventureWorksDW.abf
true
password
► Click on OK
► Goto the Schedule page for creating schedule as per the requirements.
SSRS - SQL Server Reporting Services
Q: What is SSRS?
SQL Server Reporting Service is one of the server-based software systems that
generate reports developed by Microsoft. It is used for preparing and
delivering interactive and variety of printed reports. It is administered
through an interface that is web based. Reporting services utilizes a web
service interface for supporting and developing of customized reporting
applicatons. It can be competed with Crystal Reports and other business
intelligent tools.
Q: Explain SSRS Architecture?
Reporting services architecture is comprises of integrated components. It is
multi-tiered, included with application, server and data layers. This
architecture is scalable and modular. A single installation can be used across
multiple computers. It includes the following components: - Report Manager,
Reporting Designer, Browser Types Supported by Reporting services, Report
server, Report server command line utilities, Report Server Database, Reporting
Services Extensibility, Data sources that is supported by Reporting Services.
Q: Explain Reporting Life Cycye?
The Reporting Lifecycle includes - Report designing – The designing is done in
Visual Studio Report Designer. It generates a class which embodies the Report
Definition. - Report processing – The processing includes binging the report
definition with data from the report data source. It performs on all grouping,
sorting and filtering calculations. The expressions are evaluated except the
page header, footer and section items. Later it fires the Binding event and
Bound event. As a result of the processing, it produces Report Instance. Report
instance may be persisted and stored which can be rendered at a later point of
time. - Report Rendering: Report rendering starts by passing the Report
Instance to a specific rendering extension (HTML or PDF formats). The instance
of reports is paged if paging supported by output format. The expressions of
items are evaluated in the page header and footer sections for every page. As a
final step, the report is rendered to the specific output document.
Q: How to finetune Reports?
To tune-up the Reporting Services, follow the below mentioned ways: - Expand
the Server or utilizing the reporting services of another database server. For
better embedding of report contents, report application’s logic and
characteristics can have a duplicate copy of data. - Replication of data
continuously. Using nolock, the issues of locking can well be resolved and the
performance of the query can be improved. This can be done by using dirty read
at the time of duplicating the data is unavailable.
Q: What are Data Driven Subscriptions?
Reporting Services provides data-driven subscriptions so that you can customize
the distribution of a report based on dynamic subscriber data. Data-driven
subscriptions are intended for the following kinds of scenarios: Distributing
reports to a large recipient pool whose membership may change from one
distribution to the next. For example distribute a monthly report to all
current customers. Distributing reports to a specific group of recipients based
on predefined criteria. For example send a sales performance report to the top
ten sales managers in an organization.
Q: What is Linked Report?
Q: What are different types of roles
provided by SSRS?
Q: Difference between Logical Page an
Physical Page in SSRS.
Logical page breaks are page breaks that you insert before or after report
items or groups. Page breaks help to determine how the content is fitted to a
report page for optimal viewing when rendering or exporting the report. The
following rules apply when rendering logical page breaks: Logical page breaks
are ignored for report items that are constantly hidden and for report items
where the visibility is controlled by clicking another report item. Logical
page breaks are applied on conditionally visible items if they are currently
visible at the time the report is rendered. Space is preserved between the
report item with the logical page break and its peer report items. Logical page
breaks that are inserted before a report item push the report item down to the
next page. The report item is rendered at the top of the next page. Logical
page breaks defined on items in table or matrix cells are not kept. This does
not apply to items in lists.
Q: User want only to display only pdf
as export option in report Manager. How to acheive this?
Q: Name and Describe few console
utilities for SSRS?
Q: Name few Endpoints exposed by SSRS
2012?
Q: Describe different Processing Modes
offered by SSRS?
Q: When to Use Null Data driven
Subscription?
Create a data-driven subscription that uses the Null Delivery Provider. When
you specify the Null Delivery Provider as the method of delivery in the
subscription, the report server targets the report server database as the
delivery destination and uses a specialized rendering extension called the null
rendering extension. In contrast with other delivery extensions, the Null
Delivery Provider does not have delivery settings that you can configure
through a subscription definition.
Q: How can you monitor the report Usage?
Q: How does the report manager work in
SSRS?
Report manager is a web application. In SSRS it is accessed by a URL. The
interface of this Report manager depends on the permissions of the user. This
means to access any functionality or perform any task, the user must be
assigned a role. A user with a role of full permissions can entire all the
features and menus of the report. To configure the report manager, a URL needs
to be defined.
Q: What are the Reporting Services
components?
Reporting services components assist in development. These processing
components include some tools that are used to create, manage and view reports.
A report designer is used to create the reports. a report sever is used to
execute and distribute reports. a report manager is used to manage the report
server.
Q:SQL Server Reporting Services vs
Crystal Reports.
Crystal reports are processed by IIS while SSRS have a report server. Caching
in Crystal reports is available through cache server. On the other hand,
caching in SSRS is available for Report history snapshots. Crystal reports have
standards and user defined field labels. SSRS allows only user defined field
labels.
Q: What is Report Builder?
Report Builder is a business-user, ad-hoc report design client that allows
users to design reports based on the business terms (Report Builder model) they
are familiar with, but without needing to understand database schemas or how to
write SQL or MDX queries. Report Builder works with both SQL Server and
Analysis Services data sources.
Q: How does Report Builder support
Analysis Services cubes?
Report Builder supports relational SQL and Analysis Services data sources in
SQL Server 2005. To create a model for Analysis Services cube, go to Report
Manager or Management Studio, create a data source for your Analysis Services
database, and then select the Generate Model option to create the model.
Q: How do users use Report Builder with
SQL Server data sources?
While models that provide access to SQL Server Analysis Services are
automatically generated on the report server, the Report Builder Model Designer
can be used to generate or modify the models that are built on top of SQL
Server relational databases. These model-building projects are a new type of
project within a Visual Studio–based development shell.
Q: How do I get Report Builder to
generate a parameter that can be set by users viewing the report?
In the filter dialog box, click the name of the criteria that you would like to
prompt the user for when viewing the report. For example, for the criteria
Order Year=2000, click Order Year. Select the Prompt option in the drop-down
list.
Q: What new data source types were
added in SSRS 2012?
In addition to the data source types available in SSRS 2005 (SQL Server,
Oracle, ODBC, OLE DB), the following have been added in SSRS 2008: SQL Server
2005 Analysis Services SQL Server 2005 Integration Services SQL Server 2005
Report Builder Models XML (through URL and Web services) SAP
Q: How can I add Reporting Services
reports to my application?
Visual Studio 2005 (Standard and Enterprise editions) contains a set of freely
redistributable Report Viewer controls that make it easy to embed Reporting
Services functionality into custom applications. Two versions of the Report
Viewer exist, one for rich Windows client applications and one for ASP.NET
applications.
Q: Do I need a report server to run
reports in my application?
In addition to publishing reports to a report server, you can build reports
using the Report Designer that is directly integrated with Visual Studio
language projects. You can embed reports directly in any Windows Forms or
ASP.NET Web application without access to a report server. The data access in
embedded reports is a natural extension of the Visual Studio data facilities.
Not only can you use traditional databases as a source of data for your
reports, you can use object collections as well.
Q: Can you import Microsoft Excel data
to SSRS?
Reporting Services does not import data. It only queries data in whatever
format it is stored in their native storage system. I will assume that you're
asking whether you can create reports and use Excel spreadsheets as data
sources. The answer is Yes, Reporting Services supports a wide variety of data
sources, including Excel files. You'll get the best performance with the
built-in native .NET providers but you should be able to connect to any ODBC or
OLE-DB data source, whether it comes from Microsoft or a third-party company.
Q: Can we deploy SSRS reports on our
personal website?
Your reports can only be deployed on a reporting services site. Your only
option for viewing them from other sites is an HTTP link. Some tools, like
SharePoint offer controls allowing you to view reports in the context of the
other websites, but the report is still deployed to and hosted from reporting
services.
Q: Can we use datagrids for our report
in SSRS?
I've got an ASP.NET project that populates a datagrid. I'd like to use the
datagrid as my datasource for my report using SQL Server Reporting Services. Is
this possible? The simple answer is no. However, nothing's ever simple. A set
of reporting controls was added in Visual Studio 2010 allowing you to report in
a dataset, on data that was supplied by you. So, if you retrieved your data
into a dataset, bound the datagrid to the dataset so it had data to display,
you could then use that dataset as the datasource for the reporting controls.
These are then client-side reports, not server reports though.
Q: What are the drawbacks of reporting
in SSRS?
For many years, Microsoft had no direct solution for reporting with the SQL
Server besides Crystal Reports. Now, they have SQL Server Reporting Services,
but it does have several drawbacks. It is still complex to understand the
complete functionality and structure of this new component, and many users are
still relying on the reporting application they are more familiar with, which
is Crystal Reports. Also, components in SSRS like Report Builder and Report
Designer are meant for different users for different aspects of the report
process, yet complete understanding and exposure to both is important to
utilize both functions fully and extensively. There are also issues when
exporting very large reports to Microsoft Excel, as it can lead to a loss of
data.
Q: Will running SSRS on Windows XP
limit the number of users?
Yes, but not because of SSRS. The Internet Information Services (IIS) component
of Windows XP only allows a small number of users to connect to the website at
once. As SSRS runs via IIS, this would prevent more than a few people from
using SSRS at once. Also, the only edition of SSRS that will install on Windows
XP is the Developer Edition. This edition can not be used for production use.
You need Standard or Enterprise Edition for production use, which requires a
Server OS to install on (Windows 2003 Standard, Windows 2008 Standard, etc).
Q: Are there issues when exporting SSRS
reports into Microsoft Excel?
When my users are trying to export a SSRS report into Microsoft Excel, one or
two columns in the report appear to merge together. Why might this be? Exporting
from SSRS is not always perfect, even if you stay within the Microsoft range of
products. If you have extra resources, you could splurge for an add-on that
offers much better control over exporting to Excel, such as OfficeWriter. From
my experience, though, it is usually headers or footers that cause exporting
issues. If any of these headers or footers overlap with data columns in your
report, you will find that the exported version of the report has merged cells.
Also, check columns next to each other to make sure that there is no overlap,
as well.
Q: How to send a SSRS report from SSIS?
Often there is a requirement to be able to send a SSRS report in Excel, PDF or
another format to different users from a SSIS package one it has finished
performing a data load. In order to do this, first you need to create a
subscription to the report. You can create a SSRS report subscription from
Report Manager. At the report subscription you can mention the report format
and the email address of the recipient. When you create a schedule for the SSRS
report, a SQL Server Agent Job will be created. From the SSIS, by using
sp_start_job and passing the relevant job name you can execute the SSRS report
subscription.
=iif(Fields!product.value = 'bikas',false,true) for
a field to make it visible or hidden
You want to include an image in a report. How do
you display the Image Properties dialog box?
When you drag an image item from the Toolbox window to the Report Designer, the
Image Properties dialog box automatically opens.
You want to configure an amount to display the
value in a currency format. Which property do you use?
To configure an amount to display a value in a currency format, select the
report item, and then set the format property to C or c.
What are data regions? Data regions are report items that
display repeated rows of summarized information from datasets.
You want to generate a report that is formatted as
a chart. Can you use the Report Wizard to create such a report?
No, the Report Wizard lets you create only tabular and matrix reports. you must
create the chart report directly by using the Report Designer.
You want to use BIDS to deploy a report to a
different server than the one you chose in the Report Wizard. How can you
change the server URL?
You can right-click the project in Solution Explorer and then change the
Target-Server URL property.
Which rendering formats are affected by the
PageSize properties? Because only the Adobe PDf file, Word,
and Image rendering extensions use physical page breaks, they are the only
formats that are affected by the PageSize properties.
Can you use a stored procedure to provide data to
an SSRS report? Yes, you can use a stored procedure
to provide data to an SSRS report by configuring the dataset to use a stored
procedure command type. However, your stored procedure should return only a
single result set. If it returns multiple result sets, only the first one is
used for the report dataset.
You want to use a perspective in an MDX query. How
do you select the perspective? Use the Cube Selector in the MDX
Query Designer to select a perspective.
Can you use data mining models in SSRS? Yes, you can use the DMX Designer to
create data mining queries for SSRS reports. However, do not forget to flatten
the result set returned by the DMX query.
You want your report to display a hyperlink that
will take users to your intranet. How do you configure such a hyperlink? Create a text box item, set the
action to Go To URL, and then configure the URL.
You want a report to display Sales by Category,
SubCategory, and Product. You want users to see only summarized information
initially but to be able to display the details as necessary. How would you
create the report? Group the Sales information by
Category, SubCategory, and Product. Hide the SubCategory group and set the
visibility to toggle based on the Category item. Hide the Product category
group and set the visibility to toggle based on the SubCategory item.
You want to create an Excel interactive report from
SSRS. In SSRS, can you create the same interactive experience in Excel that you
would have on the Web? No, you cannot create the same
experience with SSRS. you can, however, use Excel to create such an experience.
What is the main difference between a Matrix report
item and a Table report item? The main difference between a Matrix
and a Table report item is in the initial template. Actually, both report items
are just templates for the Tablix data region.
When you do not use report caching, is it better to
use parameters to filter information in the query or to use filters in the
dataset? From a performance perspective, it is
better to use parameters because they let SSRS pull filtered data from the data
source. In contrast, when you use filters, the queries retrieve all data and
then filter the information in an additional step.
How do you configure a running aggregate in SSRS? You can use the RunningValue function
to configure a running aggregate.
What is the main purpose of a report parameter?
The main purpose of a report parameter is to add interactivity to your reports,
letting users change the report behavior based on options they select.
What is the main purpose of a query parameter?
The main purpose of a query parameter is to filter data in the data source.
You want your users to select a parameter from a
list of values in a list box. How should you configure the parameter?
You should create a data source that contains the possible values and then bind
the data source to the parameter.
What is the main benefit of using embedded code in
a report? The main benefit of using embedded
code in a report is that the code you write at the report level can be reused
in any expression in the report.
What programming language would you use to create
embedded functions in SSRS?
An SSRS report supports only visual Basic .nET embedded cod
How do you reference an embedded function in a
report expression? Use the Code prefix and the name of
the function to reference an embedded function in a report expression.
Which of the following are valid options for
deploying a report? (Choose all that apply.) a. With BIDS
b. With the Computer Management console
c. With the .nET START command
d. With SSMS
e. With Report Manager
The correct answers are a and e, BIDS and Report Manager.
Why should you not overwrite a shared data source
in production? You should not overwrite a
production-shared data source because the administrator has probably already
changed some connection string properties.
Can you edit a report that an end user created by
using Report Builder in BIDS?
Yes, if an end user created a report by using Report Builder in BIDS and saved
the report definition file, you can open the file in BIDS and edit it.
How can you manage reports from your application if
the report server is deployed in SharePoint integrated mode?
Use the ReportService2006 endpoint of the SSRS Web service if your report
server is deployed in SharePoint integrated mode.
In which processing mode of a report viewer control
can you use the full functionality of your report server? You should use the remote processing
mode to use the full functionality of your report server.
What types of roles are available in SSRS 2008, and
what are their purposes?
Item-level roles and system-level roles are the two types of roles available in
SSRS 2008. An item-level role is a collection of tasks related to operations on
an object of the report object hierarchy of SSRS 2008. A system-level role is a
collection of tasks related to operations on server objects outside the report
object hierarchy of SSRS 2008.
Can a user or group belong to more than one
item-level or system-level role?
Yes, in SSRS 2008, a user or group can have more than one association to a
system-level or an item-level role.
When storing the credentials of a data source in
the server, are those credentials safe? Yes, the data source credentials are
safe because Reporting Services encrypts them and stores them in the
ReportServer SQL Server database.
What happens if you do not specify a parameter
value in a subscription and the parameter does not have a default value?
If you do not specify a parameter value in a subscription and the parameter
does not have a default value, the execution of the report will fail.
You want to create a subscription to a report.
However, when you right-click the Subscription subfolder of the report, you
notice that the new Subscription option is dimmed. What is wrong? When the new Subscription option is
dimmed, the report probably does not use stored credentials for accessing the
data. SSRS needs these credentials stored in its own ReportServer database to
execute a report on a schedule.
What can you do if your query with properties for a
data-driven subscription does not provide values for all of the subscription
properties? If your query with properties for a
data-driven subscription does not provide values for all of the subscription
properties, you can use text and default values instead. These values are then
used for parameters of all subscriptions you get from the query.
What mechanisms do you identify to reduce the
overhead of Reporting Services data sources?
Snapshots and cached reports can help reduce the processing pressure on data
sources and improve report response time.
Can you always create a cache of a report? No, you can create a cache of a
report only when certain requirements, such as having credentials stored in the
Report Server, are met.
Can you edit the .rdl code associated with a linked
report? No, because a linked report has no
.rdl code of its own. It refers to the .rdl code of the base report.
Which of the tools and utilities described in this
lesson can change or create the virtual directories for the report server and
Report Manager after installation? Only Reporting Services Configuration
Manager can enable and name the virtual directories for the report server and
Report Manager.
What is the file name extension for an encryption
key backup?
Encryption key backups have an .snk file name extension.
What are the three SSRS command-line utilities and
their primary functions? RSConfig.exe is used to define the
connection properties from the SSRS instance to the Report Server database;
RSKeyMgmt.exe performs encryption key operations and scale-out deployment setup;
RS.exe runs Report Server Script files that can perform report deployment and
management operations.
If you want to disable one of the rendering options
in the Save As drop-down list when viewing a report through Report Manager,
where do you do that? The visibility property for any of
the rendering devices can be changed by modifying the RSReportServer.config
file and locating the tag
for the specific device.