You can create interactive, tabular, graphical, or free-form reports from relational, multidimensional, or XML-based data sources.
You can publish reports, schedule report processing, or access reports on-demand.
You can create ad hoc reports based on predefined models and interactively explore data within the model.
You can select from a variety of viewing formats, export reports to other applications, and subscribe to published reports.
You can view the reports created over a Web-based connection or as part of Windows application.
Mention what is SSRS?
SSRS or SQL Server Reporting Services is a server-based reporting platform that gives detailed reporting functionality for a variety of data sources. Reporting services include a complete set of tools to manage, create and deliver reports and APIs that allows developers to synchronize data and report process in custom application.
How to unzip a File in SSIS?
One can use the Execute process task available under Control Flow task to unzip a file in SSIS. Steps to do so:
Drag and drop Execute Process Task to the control flow.
In Execute process, provide the path of the executable (Executable), provide arguments to extract the zip files (Arguments), and provide working directory for processing (Working Directory) configurations.
Mention what are the three command line utilities and what are their primary functions?
The three command line utilities include
RsConfig.exe: It is used to determine the connection properties from the SSRS instance to the Report Server database
RsKeyMgmet.exe: It executes scale out deployment set-up and encryption key operations
Rs.exe: It executes Report server Script files which can perform management operations and report deployment
Explain the architecture of reporting services.
Reporting Services runs as a middle-tier server as part of the existing server architecture.
SQL Server 2000 should be installed for the database server, and Internet Information Services 6.0 as a Web server.
The report server engine takes in report definitions, locates the corresponding data, and produces the reports.
Interaction with the engine can be done through the Web-based Report Manager, which also lets you manage refresh schedules and notifications.
End users view the report in a Web browser, and can export it to PDF, XML, or Excel.
Explain what data regions are and what are the different data regions?
Data regions are report items that show repeated rows of summarized information from datasets.
Different data regions include
Lookup's are a key component in SQL Server Integration Services (SSIS). Explain its purpose
SSIS 2008 has the ability of cache lookup data in to a local file. Lookup data thus needs to be retrieved once, and then cached and reused. Lookups are a way to provide a range of values for a particular column’s value. It is very useful while transforming data and cleaning it as well. Lookup enables transforming of data from one table to another and merging tables as well through a range of values. This can clear and clean a lot of misspelled data. Example: A table might have flrida stored as city instead of florida. If the transformation is made through a lookup consisting of Florida, then flrida will be converted to Florida, providing a clean transformation.
Explain how you can deploy an SSRS report?
SSRS report can be deployed in three ways
By Visual Studio: You can directly deploy the report in Visual Studios through solution explorer, by declaring the report server URL in project properties at Target Server URL.
By Report Server: By browsing the report from the disk location of the server you can deploy the report to report server
By creating the Utility: Another option is to create customized utility to deploy the report
Describe SQL Server Reporting Services lifecycle.
The phases of the Reporting Life Cycle involve:
This stage involves creation of reports that are published using the Report Definition language. RDL is an XML based industry standard for defining reports.
This involves managing the published reports as a part of the webservice. The reports are chched for consistency and performance. They can be executed whenever demanded or can be scheduled and executed.
In short Report Management includes:
Organizing reports and data sources,
Scheduling report execution and delivery, and
Tracking reporting history.
Reports can be delivered to the consumers either on their demand or based on an event. Then they can view them is a web-based format.
It is important to protect reports as well as the report resources. Therefore, Reporting Services implement a flexible, role-based security model.
Explain what are the different stages of Report Processing?
Different stages of Report Processing includes
Compile: It analyze expressions in the report definitions and save the compiled intermediate format internally on the server
Process: It run dataset queries and combine intermediate format with data and layout
Render: It sends processed report to a rendering extension to tell how much information fits on each page and create the page report
Export: It exports the reports to a different file format
How to Generate an Auto Incremental Number in a SSIS Package?
Auto incremental numbers in a SSIS package can be provided using script components. The script component should be dragged and dropped to the data flow and Transformation should be the component type. The input column that needs to be set and passed to the script component should be selected by double clicking the script component. Using the input and output tab, an integer column data type can be added. Now, the script to write the coding logic can be written in which the starting and incremented by value can be set.
Explain what is the difference between Tabular and Matrix report?
Tabular Report: Tabular report is the most basic type of report. Each column relates to a column chosen from the database
Matrix Report: A matrix report is a cross-tabulation of four groups of data.
What are the ways to tune Reporting Services?
Following are a few ways you can tune up Reporting Services:
You can expand your current Server or avail the reporting service database on another server. Depending on report characteristics and application logic, it is sometimes better to have a copy of data separate to improve the performance.
You can also use continuous replication for reporting. With this there wouldn’t be any interference with the OLTP environment.
The locking problems can be solved by using ‘nolock’ and the query performance can be improved using ‘dirty read’ when a copy of the data is not available. This can be accomplished only if the database design and application logic permit dirty reads.
Mention what are the new features in SSRS?
New features in SSRS includes
Excel File Export: The files can be exported into Excel file formats, earlier only XLS files were only exported
Data Alerts: The new data alerts allow to create alert threshold which are evaluated on a user defined schedule, also there is data alert manager for alerting administrators
Power View: With the new RDLX file format, power view is a new interactive Business Intelligence feature
These are the new features included, apart from these, if you are using SSR on SharePoint it gives additional benefits like drag and drop ad hoc reporting and sending e-mails when data changes.
Explain how would you store your query in an SSRS report or a Database server?
Storing SQL queries directly in text format in the data should be avoided. Instead, it should be stored in a stored procedure in the database server. The advantage is that the SQL would be in a compiled format in an SP and gives all the benefits of SP compared to using an ad-hoc query from the report.
Explain what is a sub-report?
Sub-report are inserted into the main part, just like a main report, you also pass queries and parameters to it. In other words, a sub-report can be considered as an extension to your main report, but it consists of a different data set. For example, you can prepare a report of customers and then use a sub-report to show a list of orders for each customer.
Mention what are the core components of SSRS?
The core components of SSRS includes
A set of tool to View, Create and Manage report
A report server component that hosts and processes reports in a different formats like PDF, HTML, Excel, CSV,
An API, which enables developers to integrate with custom applications or to create custom tools to manage or build reports
Explain can SSRS reports Cache results?
Caching reports enables users to access and view reports much faster. SSRS does allow Cache reports on reporting server.
Name some of the open source software that you can use in alternative to SSR?
Some of the open source software alternative are
BIRT (Business Intelligence Reporting Tool)
DataVision and so on
Mention what is the main function of a query parameter?
The main function of a query parameter is to filter data in the data source.
Explain can you implement data mining in SSRS?
Yes, it is possible to implement data mining in SSRS by using DMX designer to create data mining queries for SSRS reports.
Explain how you can configure a running aggregate in SSRS?
To configure a running aggregate in SSRS, you can use the Running Value function.