How to
Configure the Report Server Database Connection
1.Start the Reporting Services Configuration tool and open a connection to the report server.
2.On the Database page, click Change Database. Click Next.
3.Click Choose an existing report server database. Click Next.
4.Select the SQL Server that now hosts the report server database and click Test Connection. Click Next.
5.In Database Name, select the report server database that you want to use. Click Next.
6.In Credentials, specify the credentials that the report server will use to connect to the report server database. Click Next.
7.Click Next and then Finish.
Note
A Reporting Services installation requires that the SQL Server Database Engine instance include the RSExecRole role. Role creation, login registration, and role assignments occur when you set the report server database connection through the Reporting Services Configuration tool. If you use alternate approaches (specifically, if you use the rsconfig.exe command prompt utility) to configure the connection, the report server will not be in a working state. You might have to write WMI code to make the report server available. For more information, see Reporting Services WMI Provider.
1.Start the Reporting Services Configuration tool and open a connection to the report server.
2.On the Database page, click Change Database. Click Next.
3.Click Choose an existing report server database. Click Next.
4.Select the SQL Server that now hosts the report server database and click Test Connection. Click Next.
5.In Database Name, select the report server database that you want to use. Click Next.
6.In Credentials, specify the credentials that the report server will use to connect to the report server database. Click Next.
7.Click Next and then Finish.
Note
A Reporting Services installation requires that the SQL Server Database Engine instance include the RSExecRole role. Role creation, login registration, and role assignments occur when you set the report server database connection through the Reporting Services Configuration tool. If you use alternate approaches (specifically, if you use the rsconfig.exe command prompt utility) to configure the connection, the report server will not be in a working state. You might have to write WMI code to make the report server available. For more information, see Reporting Services WMI Provider.
Monday,
December 20, 2010
Dates are
fun. See, by default most dates come out like 5/6/2008. But computers, and
programs like them formatted as 05/06/2008. That way, all the dates, no matter
what month or day, are all the same length, cool huh?
Well, in Reporting Services, if you have a date field coming back in a dataset, and you want to format it as a 10 digit string, there are about 50 different ways to do it. You can use old VBA Left and Mid etc, or you can use String.Format like..
=String.Format(“{0:MM}/{0:dd}/{0:yyyy}”,CDate(Fields!CalendarDate.Value))
Well, in Reporting Services, if you have a date field coming back in a dataset, and you want to format it as a 10 digit string, there are about 50 different ways to do it. You can use old VBA Left and Mid etc, or you can use String.Format like..
=String.Format(“{0:MM}/{0:dd}/{0:yyyy}”,CDate(Fields!CalendarDate.Value))
Monday,
September 27, 2010
Problem
We have many SQL Server Reporting Services (SSRS) reports that use our OLTP systems as data sources. We are always adding new reports and the number of users running these reports is also increasing rapidly. We would like to look at leveraging report snapshots as a way to reduce some of the load on our database servers. Can you provide us with an example of how to do this?
Solution
A report snapshot provides the ability to run a report at a scheduled time or on demand and save the report in the ReportServer database. When a user requests the report, they automatically get the snapshot version stored in the ReportServer database rather than executing the SQL queries to retrieve the data from the underlying data source. You can schedule report snapshots to be run during off-peak hours; this can reduce the load on your database servers. You can also create snapshots manually and select which snapshot version of a report that you would like to run. You can save as many snapshots as you like or you can have Reporting Services limit the number of snapshots that are saved. Besides reducing the load on your database servers, you can use report snapshots to archive reports at a particular point in time for later viewing; e.g. month-end reports, year-end reports, etc. Depending on how many report snapshots you keep, you could use a significant amount of space in the ReportServer database. Read more..
We have many SQL Server Reporting Services (SSRS) reports that use our OLTP systems as data sources. We are always adding new reports and the number of users running these reports is also increasing rapidly. We would like to look at leveraging report snapshots as a way to reduce some of the load on our database servers. Can you provide us with an example of how to do this?
Solution
A report snapshot provides the ability to run a report at a scheduled time or on demand and save the report in the ReportServer database. When a user requests the report, they automatically get the snapshot version stored in the ReportServer database rather than executing the SQL queries to retrieve the data from the underlying data source. You can schedule report snapshots to be run during off-peak hours; this can reduce the load on your database servers. You can also create snapshots manually and select which snapshot version of a report that you would like to run. You can save as many snapshots as you like or you can have Reporting Services limit the number of snapshots that are saved. Besides reducing the load on your database servers, you can use report snapshots to archive reports at a particular point in time for later viewing; e.g. month-end reports, year-end reports, etc. Depending on how many report snapshots you keep, you could use a significant amount of space in the ReportServer database. Read more..
You can
create subscriptions for reports that you access from a SharePoint Web
application that is integrated with a report server that runs in SharePoint
integrated mode.
To create a subscription, the report must use stored credentials and you must have permission to view the report and create alerts. When you create a subscription, there are three ways to specify its delivery:
You can create a subscription that delivers a document based on the original report to a library within the same SharePoint site as the original report. You cannot deliver the document to a library on another server or another site within the same site collection. To deliver the document, you must have Add Items permission on the library to which the report is delivered.
You can deliver a document based on the original report to a shared folder on the file system. You must select an existing folder that is accessible over a network connection.
If the report server is configured to use the Report Server E-mail delivery extension, you can create a subscription that sends a report or an exported report file (saved in an output format) to your in-box. To receive just the notification without the report or report URL, clear the Include a link to this report and the Show report inside message checkboxes.
When you create a subscription, you can select a file format that creates the report as a certain document type. Not every report works well in every format. Before you select a format in a subscription, open the report and export it to different formats to verify that it appears as expected.
Important:
A subscription that delivers a report to a library or to a shared folder creates a new, static file that is based on the original report, but it is not a true report definition that runs in the Report Viewer Web Part. If the original report has interactive features (such as drillthrough links) or dynamic content, those features will not be available in the static file that is delivered to the target location. If you select a "Web Page" you can preserve some interactivity, but because the document is not an .rdl file that runs in the Report Viewer, clicking through a report creates new pages in the browser session that you must scroll through to return to the site.
You cannot rename the file name extension of an exported report to .rdl and have it run in the Report Viewer Web Part. If you want to create a subscription that provides an actual report, use the Report Server E-mail delivery extension and set options to include a link to the report.
Version settings on the library that contains the delivered document determine whether a new version of the document is created with each delivery. By default, version settings are enabled for each library. Unless you specifically choose No versioning, a new major version of the document will be created upon delivery. Only major versions of the document are created; minor versions are never created as a result of subscription delivery, even if you select a versioning option that allows minor versions. If you limit the number of major versions that are retained, older deliveries will be replaced by newer ones when the maximum limit is reached.
Output formats that you select for a subscription are based on rendering extensions that are installed on the report server. You can only select output formats that are supported by the rendering extensions on the report server. For descriptions of the output formats, see Exporting Reports.
To deliver a report to a SharePoint library
Point to the report in a library.
Click the down arrow next to the report, and select Manage Subscriptions.
Click Add Subscription.
In Delivery type, select SharePoint Document Library.
In Document Library, select a library within the same site.
In File Options, specify the file name and title for the document that will be created by the subscription.
In Output Format, select the application format.
Web archive (MHTML) is the default because it produces a self-contained HTML file, but it will not preserve interactive report features that might be in the original report.
In Overwrite Options, specify an option that determines whether subsequent deliveries overwrite a file. If you want to preserve previous deliveries, you can select Create a file with a unique name. A number will be appended to new files to create a unique file name.
In Delivery Event, specify a schedule or event that causes the subscription to run. You can create a custom schedule, select a shared schedule if one is available, or run the subscription whenever the data is refreshed for a report that runs with snapshot data. For more information about schedules and data processing, see How to: Set Processing Options (Reporting Services in SharePoint Integrated Mode) and How to: Schedule Report and Subscription Processing (Reporting Services in SharePoint Integrated Mode).
In Parameters, if you are creating a subscription to a parameterized report, specify the values that you want to use with the report when the subscription is processed. For more information about parameters, see How to: Set Parameters on a Published Report (Reporting Services in SharePoint Integrated Mode).
To create a subscription for shared folder delivery
Point to the report in a library.
Click the down arrow next to the report, and select Manage Subscriptions.
Click Add Subscription.
In Delivery type, select Windows File Share.
In File Name, enter the name of the file that will be created in the shared folder.
In Path, enter a folder path in Uniform Naming Convention (UNC) format that includes the computer's network name. Do not include trailing backslashes in the folder path. An example path might be \\ComputerName01\Public\MyReports, where Public and MyReports are shared folders.
In Render Format, select the application format for the report.
In Write Mode, choose between None, Autoincrement, or Overwrite. These options determine whether subsequent deliveries overwrite a file. If you want to preserve previous deliveries, you can choose Autoincrement. A number will be appended to new files to create a unique file name. If you choose None, no delivery will occur if a file of the same name already exists in the target location.
In File Extension, choose True to add a file name extension that corresponds to the application file format, or False to create the file without an extension.
In User Name and Password, enter credentials that have write permissions on the shared folder.
In Delivery Event, specify a schedule or event that causes the subscription to run. You can create a custom schedule, select a shared schedule if one is available, or run the subscription whenever the data is refreshed for a report that runs with snapshot data. For more information about schedules and data processing, see How to: Set Processing Options (Reporting Services in SharePoint Integrated Mode) and How to: Schedule Report and Subscription Processing (Reporting Services in SharePoint Integrated Mode).
In Parameters, if you are creating a subscription to a parameterized report, specify the values that you want to use with the report when the subscription is processed. For more information about parameters, see How to: Set Parameters on a Published Report (Reporting Services in SharePoint Integrated Mode).
To create a subscription for report server e-mail delivery
Point to the report in a library.
Click the down arrow next to the report, and select Manage Subscriptions.
Click Add Subscription.
In Delivery type, select E-mail.
In Delivery options, specify an e-mail address to send the report to.
Optionally, you can modify the Subject line. The Subject line uses built-in parameters that capture the report name and time when it was processed. These are the only built-in parameters that can be used. The parameters are placeholders that customize the text that appears in the Subject line, but you can replace it with static text.
Choose Include a link to this report if you want to embed a report URL in the body of the message.
In Report Contents, specify whether you want to embed the actual report in the body of the message.
The rendering format and browser determine whether the report is embedded or attached. If your browser supports HTML 4.0 and MHTML, and you select the Web archive rendering format, the report is embedded as part of the message. All other rendering formats (CSV, PDF, and so on) deliver reports as attachments. Reporting Services does not check the size of the attachment or message before sending the report. If the attachment or message exceeds the maximum limit allowed by your mail server, the report will not be delivered. Choose one of the other delivery options (such as URL or notification) for large reports.
In Delivery Event, specify a schedule or event that causes the subscription to run. You can create a custom schedule, select a shared schedule if one is available, or run the subscription whenever the data is refreshed for a report that runs with snapshot data. For more information about schedules and data processing, see How to: Set Processing Options (Reporting Services in SharePoint Integrated Mode) and How to: Schedule Report and Subscription Processing (Reporting Services in SharePoint Integrated Mode).
In Parameters, if you are creating a subscription to a parameterized report, specify the values that you want to use with the report when the subscription is processed. For more information about parameters, see How to: Set Parameters on a Published Report (Reporting Services in SharePoint Integrated Mode).
To view or modify a subscription
Point to the report.
Click the down arrow, and then click Manage Subscriptions.
Each subscription is identified by the type of delivery. Click the subscription type to view and change the existing properties.
To delete a subscription
Point to the report.
Click the down arrow, and then click Manage Subscriptions.
Click the checkbox next to the subscription, and click Delete.
To create a subscription, the report must use stored credentials and you must have permission to view the report and create alerts. When you create a subscription, there are three ways to specify its delivery:
You can create a subscription that delivers a document based on the original report to a library within the same SharePoint site as the original report. You cannot deliver the document to a library on another server or another site within the same site collection. To deliver the document, you must have Add Items permission on the library to which the report is delivered.
You can deliver a document based on the original report to a shared folder on the file system. You must select an existing folder that is accessible over a network connection.
If the report server is configured to use the Report Server E-mail delivery extension, you can create a subscription that sends a report or an exported report file (saved in an output format) to your in-box. To receive just the notification without the report or report URL, clear the Include a link to this report and the Show report inside message checkboxes.
When you create a subscription, you can select a file format that creates the report as a certain document type. Not every report works well in every format. Before you select a format in a subscription, open the report and export it to different formats to verify that it appears as expected.
Important:
A subscription that delivers a report to a library or to a shared folder creates a new, static file that is based on the original report, but it is not a true report definition that runs in the Report Viewer Web Part. If the original report has interactive features (such as drillthrough links) or dynamic content, those features will not be available in the static file that is delivered to the target location. If you select a "Web Page" you can preserve some interactivity, but because the document is not an .rdl file that runs in the Report Viewer, clicking through a report creates new pages in the browser session that you must scroll through to return to the site.
You cannot rename the file name extension of an exported report to .rdl and have it run in the Report Viewer Web Part. If you want to create a subscription that provides an actual report, use the Report Server E-mail delivery extension and set options to include a link to the report.
Version settings on the library that contains the delivered document determine whether a new version of the document is created with each delivery. By default, version settings are enabled for each library. Unless you specifically choose No versioning, a new major version of the document will be created upon delivery. Only major versions of the document are created; minor versions are never created as a result of subscription delivery, even if you select a versioning option that allows minor versions. If you limit the number of major versions that are retained, older deliveries will be replaced by newer ones when the maximum limit is reached.
Output formats that you select for a subscription are based on rendering extensions that are installed on the report server. You can only select output formats that are supported by the rendering extensions on the report server. For descriptions of the output formats, see Exporting Reports.
To deliver a report to a SharePoint library
Point to the report in a library.
Click the down arrow next to the report, and select Manage Subscriptions.
Click Add Subscription.
In Delivery type, select SharePoint Document Library.
In Document Library, select a library within the same site.
In File Options, specify the file name and title for the document that will be created by the subscription.
In Output Format, select the application format.
Web archive (MHTML) is the default because it produces a self-contained HTML file, but it will not preserve interactive report features that might be in the original report.
In Overwrite Options, specify an option that determines whether subsequent deliveries overwrite a file. If you want to preserve previous deliveries, you can select Create a file with a unique name. A number will be appended to new files to create a unique file name.
In Delivery Event, specify a schedule or event that causes the subscription to run. You can create a custom schedule, select a shared schedule if one is available, or run the subscription whenever the data is refreshed for a report that runs with snapshot data. For more information about schedules and data processing, see How to: Set Processing Options (Reporting Services in SharePoint Integrated Mode) and How to: Schedule Report and Subscription Processing (Reporting Services in SharePoint Integrated Mode).
In Parameters, if you are creating a subscription to a parameterized report, specify the values that you want to use with the report when the subscription is processed. For more information about parameters, see How to: Set Parameters on a Published Report (Reporting Services in SharePoint Integrated Mode).
To create a subscription for shared folder delivery
Point to the report in a library.
Click the down arrow next to the report, and select Manage Subscriptions.
Click Add Subscription.
In Delivery type, select Windows File Share.
In File Name, enter the name of the file that will be created in the shared folder.
In Path, enter a folder path in Uniform Naming Convention (UNC) format that includes the computer's network name. Do not include trailing backslashes in the folder path. An example path might be \\ComputerName01\Public\MyReports, where Public and MyReports are shared folders.
In Render Format, select the application format for the report.
In Write Mode, choose between None, Autoincrement, or Overwrite. These options determine whether subsequent deliveries overwrite a file. If you want to preserve previous deliveries, you can choose Autoincrement. A number will be appended to new files to create a unique file name. If you choose None, no delivery will occur if a file of the same name already exists in the target location.
In File Extension, choose True to add a file name extension that corresponds to the application file format, or False to create the file without an extension.
In User Name and Password, enter credentials that have write permissions on the shared folder.
In Delivery Event, specify a schedule or event that causes the subscription to run. You can create a custom schedule, select a shared schedule if one is available, or run the subscription whenever the data is refreshed for a report that runs with snapshot data. For more information about schedules and data processing, see How to: Set Processing Options (Reporting Services in SharePoint Integrated Mode) and How to: Schedule Report and Subscription Processing (Reporting Services in SharePoint Integrated Mode).
In Parameters, if you are creating a subscription to a parameterized report, specify the values that you want to use with the report when the subscription is processed. For more information about parameters, see How to: Set Parameters on a Published Report (Reporting Services in SharePoint Integrated Mode).
To create a subscription for report server e-mail delivery
Point to the report in a library.
Click the down arrow next to the report, and select Manage Subscriptions.
Click Add Subscription.
In Delivery type, select E-mail.
In Delivery options, specify an e-mail address to send the report to.
Optionally, you can modify the Subject line. The Subject line uses built-in parameters that capture the report name and time when it was processed. These are the only built-in parameters that can be used. The parameters are placeholders that customize the text that appears in the Subject line, but you can replace it with static text.
Choose Include a link to this report if you want to embed a report URL in the body of the message.
In Report Contents, specify whether you want to embed the actual report in the body of the message.
The rendering format and browser determine whether the report is embedded or attached. If your browser supports HTML 4.0 and MHTML, and you select the Web archive rendering format, the report is embedded as part of the message. All other rendering formats (CSV, PDF, and so on) deliver reports as attachments. Reporting Services does not check the size of the attachment or message before sending the report. If the attachment or message exceeds the maximum limit allowed by your mail server, the report will not be delivered. Choose one of the other delivery options (such as URL or notification) for large reports.
In Delivery Event, specify a schedule or event that causes the subscription to run. You can create a custom schedule, select a shared schedule if one is available, or run the subscription whenever the data is refreshed for a report that runs with snapshot data. For more information about schedules and data processing, see How to: Set Processing Options (Reporting Services in SharePoint Integrated Mode) and How to: Schedule Report and Subscription Processing (Reporting Services in SharePoint Integrated Mode).
In Parameters, if you are creating a subscription to a parameterized report, specify the values that you want to use with the report when the subscription is processed. For more information about parameters, see How to: Set Parameters on a Published Report (Reporting Services in SharePoint Integrated Mode).
To view or modify a subscription
Point to the report.
Click the down arrow, and then click Manage Subscriptions.
Each subscription is identified by the type of delivery. Click the subscription type to view and change the existing properties.
To delete a subscription
Point to the report.
Click the down arrow, and then click Manage Subscriptions.
Click the checkbox next to the subscription, and click Delete.
Thursday,
September 9, 2010
In SSRS we
can generate drill down reports in very easy way. To create Drill down reports,
please follow the following steps.
Step1: Create New Report Project with one Shared DataSource with data query for generating report.
Step1: Create report by taking Table type report as shown bellow
Step1: Create New Report Project with one Shared DataSource with data query for generating report.
Step1: Create report by taking Table type report as shown bellow
Step3: Now Add
Group for product by right clicking on Product Column as shown bellow
Select Add Parent group
Selet Product
from Group By DropDown and check Add Group Header, then click OK
The report table looks like this
The report table looks like this
Step 4: From
Row groups > select Product group details (group Details and not group
header) > go to Group Properties > select Visibility tab > select Hide
> click on Display can be toggled by this group item and select the name of
the group then click on OK
Then
the preview the report
Step 5:We can
also change the drill down and make it reverse by setting ‘InitialToggleState’
to True and by selecting the group properties (header), visibility tab and
click Show
Monday,
April 26, 2010
We can make
three types navigations in SQL Server Reporting Services
- Navigating in same report(Book Marking)
- Navigating to another Report(Jump to Report)
- Navigation Website(Jum to URL)
Any text box or image item can be used for intrareport
or interreport navigation, for navigation to external resources like web pages
and documents, and also to send e-mail. All of these features are enabled by
using navigation properties that can be specified in the Textbox Properties or
Image Properties dialog.
1.Navigating in same report(Book Marking)
1.Navigating in same report(Book Marking)
This can be achieve with Book Marking in Reports.
Abookmark is a text box or image in a report that can be used as a navigational link. If you want to allow the user to click an item and navigate to another item, assign a bookmark value to each of the target items.
First, open the Textbox Properties dialog by right-clicking the text box and selecting Properties from the pop-out menu. In the Textbox Properties dialog, then switch to the Action tab in left panel.
Abookmark is a text box or image in a report that can be used as a navigational link. If you want to allow the user to click an item and navigate to another item, assign a bookmark value to each of the target items.
First, open the Textbox Properties dialog by right-clicking the text box and selecting Properties from the pop-out menu. In the Textbox Properties dialog, then switch to the Action tab in left panel.
To enable Hyperlink to a bookmark, set the Go to
bookmark property to the target bookmark.
Using bookmarks to navigate within a report is very easy to
do. Each report item has a BookMark property that may be assigned a unique
value.
After adding
bookmarks to any target items, use the Go to Bookmark Selection
list to select the bookmark in the Properties for the Source item. This allows
the user to navigate to items within the same report.
2.Navigating
to another Report(Jump to Report)
This powerful feature enables a text box or image to be used as a link to another report by passing parameter values to the target report. The target report can consist of a specific record or multiple records, depending on the parameters passed to the target report.
The following example uses a Products by Category report.That means the Products report shows the list of all Products and the Category Report shows the information about perticular Product. Name text box is used to link to a report that will display the details of a single product record. The Product Details report is very simple. This report accepts a ProductID parameter to filter the records and narrow down to the record requested.
First, open the Textbox Properties dialog by right-clicking the text box and selecting Properties from the pop-out menu. In the Textbox Properties dialog, then switch to the Action tab.
Then select the Go to report radio button, and select the target report from the drop-down list. And Add the perameters for the targetting report by clicking on Add button.
This powerful feature enables a text box or image to be used as a link to another report by passing parameter values to the target report. The target report can consist of a specific record or multiple records, depending on the parameters passed to the target report.
The following example uses a Products by Category report.That means the Products report shows the list of all Products and the Category Report shows the information about perticular Product. Name text box is used to link to a report that will display the details of a single product record. The Product Details report is very simple. This report accepts a ProductID parameter to filter the records and narrow down to the record requested.
First, open the Textbox Properties dialog by right-clicking the text box and selecting Properties from the pop-out menu. In the Textbox Properties dialog, then switch to the Action tab.
Then select the Go to report radio button, and select the target report from the drop-down list. And Add the perameters for the targetting report by clicking on Add button.
When a
product name is clicked on the main report, the viewer redirects to the
detailed report for the specific product by passing the ProductID parameter
value.
3.Navigation Website(Jum to URL)
The Go to URL option can be used to navigate to practically any report or document content on your report server, and files, folders, and applications in your intranet environment or on the World Wide Web. It can also be set to an expression that uses links stored in a database, custom code, or any other values. It’s more accurate to say that any URI (Uniform Resource Identifier) can be used since a web request is not limited only to a web page or document. With some creative programming, queries, and expressions, your reports could be designed to navigate to a web page, document, e-mail address, Web service request, or a custom web application, directed by data or custom expressions.
3.Navigation Website(Jum to URL)
The Go to URL option can be used to navigate to practically any report or document content on your report server, and files, folders, and applications in your intranet environment or on the World Wide Web. It can also be set to an expression that uses links stored in a database, custom code, or any other values. It’s more accurate to say that any URI (Uniform Resource Identifier) can be used since a web request is not limited only to a web page or document. With some creative programming, queries, and expressions, your reports could be designed to navigate to a web page, document, e-mail address, Web service request, or a custom web application, directed by data or custom expressions.
Once you run the Report and click on
the column which you have set "Go to URL", then you will be
redirected to that web site.
How to deploy a report on a report
server installed on a Windows7 machine?
1.
What
is difference b/w matrix, tabular and list ?
Ans:
Table: - Has fixed Number of Columns,
Variable number of rows.
We can group and aggregate rows
only.
Matrix: - has variable number of
columns and rows.
we can group and aggregate rows
and columns .
List: -Displays set of report
items that are repeated for each group or row data.
2.
What
is shared datasource and embedded datasource?
Ans:
Shared data source, we can use for different reports for that project and if
the database is on LAN, different developers can use.
Embedded data source can use if
database is already saved in server.
3.
What
are the connection types available in SSRS?
1.Sql server
2.ODBC
3.XML
4. SQL Analysis server
5. SQL Server Report
Model
6. Oracle…..
4.
What
is the purpose of table property?
Ans:
I think There is no table property (Ask your friends also). If it is Tablix
property, we use for applying filters,
sorting and drilldowns
5.
What
is use of Expression and what are all available expressions?
Ans:
- we can use expressions for writing our custom expressions. We can write
expressions by using variables, built-in fields (Execution time, Page Number,
Report Folder, Report name), parameters, fields, datasets, variables
(Arithmetic, comparison, concatenation…) and Common function (Text, date time,
math ….)
6.
What
is difference b/w iif and switch?
And:
- IIF: - Returns one of two objects, depending on the evaluation of an
expression.
Ex:
-=IIf(Fields!YearlyIncome.Value >= 60000,"High","Low")
CASE:
- Evaluates a list of expressions and returns an Object value corresponding to
the first expression in the list that is True.
Ex:
-=Switch(Fields!State.Value =
"OR","Oregon",Fields!State.Value =
"WA","Washington")
=Switch(Fields!FirstName.Value
= "Sue","Susan",Fields!FirstName.Value =
"Bob","Robert")
7.
What
is Running Value?
Returns a running
value of all non-null numericvalues specified by the expression, evaluated for
the given scope.
Runningvalues(expression,function,
scope)
The function takes 3 parametrs
Ex: -=running
value(Fields!Dailysales.value, sum,”table1_details group”) (last one is scope)
8.
What
are the variables available in SSRS?
9.
What
are the global variables and local variables?
10. How will you do
alternate row color change?
Ans:-
By writing expression in IIF clause
=IIf(Rownumber(Nothing)
MOD 2!=0,”Grey”,”Transparent”)
11. What is drilldown
report and difference b/w drilldown& drill through?
Ans:
- Drilldown: Drilldowns are applied for the report, by selecting toggled item
property. If we give value, based on that value report is generated ( with ‘+’
symbols for that value)
Drill
Through: - Drill throughs are like sub-reports only. For this we have to take
two reports and we have to pass the value from second report to first report.
So that, if we click on the first report parameter, it will go to second report
based on that value.
12. What is interactive
sorting?
Interactive
sorting is useful for end-user, to view data either ascending or descending
format. We can apply interactive more than one column also.
13. How to create multiple
groups and multiple totals in each group?
First
we have to create parent group under row groups ----> details (Right click),
and then we can add child row groups.
Multiple
totals can be applied for each group based on sum (Fields! (Column
Name).value).
14. How do you pass multi
param pass through query?
15. What is the cascading
parameter?
Child
parameters values depend on Parent variable. We can create upto 32 levels.
Child parameter value wont enable until we enter value in parent parameter.
16. What is difference b/w
multi value and multi param?
17. What is difference b/w
hidden and internal?
18. How will you do the
dynamic sorting and dynamic grouping?
Dynamic
grouping can be applied by passing value at runtime.
19. What are the sections
available in charts?
3
sections
1.
Category
field( x-axis)
2.
Data
field (y-axis)
3.
Series
field
20. What are the charts
available in reports?
They
are 8 different charts are available
They
are
1.
column
2.
Line
3.
Shape
4.
Bar
5.
Area
6.
Range
7.
Scatter
8.
polar
21. What are the gauges
available in reports?
Radial
gauges
Linear
guages
22. How many ways to rendering the reports?
23. How to deploy the
reports on Report Server?
We
can deploy reports in 2 ways
1.
BIDS:
- go to solution explorer right click on project and enter deploy server url
(Http://localhost:8080/ReportServer)
2.
Command
prompt (RSutility)
24. How many ways to
deploy in report server?
Above
answer
25. What is difference b/w
report manager and report server?
Ans:
Report Server stores the data and time and of the report is created. It will
display in List format.
Report manager displays list of
available reports and data sources.
26. What is the linked
report?
Ans:
Types of Reports:
·
Parameterized reports
A parameterized report
uses input values to complete the report. With the parameterized reports ,
report can be varied depending upon the input variable we assign. Parameterized
paramters used in Drill through Reports.
·
Linked Reports
A linked
report is a report server item that provides an access point to an existing
report.
A linked
report is derived from an existing report and retains the original report
definition.
·
Snapshot reports
A Report
snapshot is a report that contains layout information and query results that
were retrieved at a specific point of time.
·
Cached Reports
A cached
report is a saved copy of a processed report. Cached reports are used to
improve performance by reducing the number of processing requests.
·
Adhoc Reports
·
Click through Reports
·
Drill Down reports
·
Drill Through Reports
·
Sub Reports
27. What are all the ways
we can provide security for report?
We
can provide 3 ways for the report
1.
Database
level
2.
Project
level
3.
Report
Level
28. How will provide
report level security?
Go
to (Http://localhost:8080/Reports) select one report
and go to Properties there you find security option, in that we can provide.
29. How will provide the
data level security?
Go
to above link select project and select security, there you can assign
security.
30. How do you provide
data source security?
Ans:
go to reports in the reports select particular report and click on properties
there we can find datasources , select that one and provide credentials.
31. What is difference b/w
local server and server url?
Local
server means we can store our reports in our own (local) system only.
Server
Url:- if we want to store our reports in centralized server , at that time we
will give that server URL.
32. How do you pass the
url with parameter?or what is url command on report server?
33. How do you pass the
command with url?
34. How do you integrate
dot net code or VB Script code in Reports?
35. How will integrate dot
net dll into reports?
Ans:
open the Report properties dialog box from the the visual studio designer and
select refernces tab.
Browse
and select the custom code .dll file and preview it , we will get error. Take
the .dll file and copy in Microsoft visual studio
9.0/Common7/IDE/PrivateAssemblies
36. What is default size
memory on report server?
Ans:
Report and model size is based on the size of report definition and report
model files(.smdl). however the report server doesnot limit the size or model
of the report that we publish in report server. But ASP.NET imposes the size of
the model or report that posted to the reportsever. By default server size 4MB.
37. What is subscription
and what are all available subscriptions on reports?
Ans:
Delivering report to the user on daily/ weekly/monthly based.
These
are two types:
·
Standard
subscriptions
·
Data-Driven
Subscriptions
38. What is data driven
subscriptions?
Ans:
Reporting Services supports 2 types of subscriptions
1.
Standard
subscriptions: created and managed by
individual user. It consists of static data
2.
Data-driven
Subscriptions: data can be change at run time and if want to deliver to the
particular members present in the database, by writing query from the database.
These are
two types
·
E-mail
subscriptions
·
File
share subscriptions
39. What is report
builder?
Ans:
Report Builder provides individuals with a quick and easy way to create
attractive and ad-hoc reports.
40. What report server
architecture?
41. What is difference b/w
2005&2008 SSRS?
Mainly
1.
there
is no IIS
2.
Rendering
format MS-word as introduced.
3.
We
can format custom textbox text in bold and
italic .In 2005 we cannot format text in
textbox.
4.
Used
HTTP.sys
5.
More
enhancements in rendering formats of excel, pdf and CSV….
42. What is typical
situation faced in your project?
43. What is RS- Report
configuration file?
44. How will you do the
SMTP configuration?
We
can configure our SMTP settings by Going into Reportservices configuration
manger----->Email settings----> SMTP Settings.
45. While configuration
how will you do the configuration setting in report server?
46. How many ways to
deliver the reports?
47. What is purpose of sub
report?
Sub-reports
are useful, when we want to find out list of values based on one parameter,
then we pass that parameter to that second report.
Ex:-
consider I have two reports with names sales, sellerId. If I linked sellerid
column in the second report to sellerid column in the first report , by giving
click on the first report sellerid, it goes to the second report and gives
information about how many sales done by him on datewise.
48. How will you move the
reports from difference environments
49. What is report server
utility?
50. What is documentation
map?
ANS:
Documentation map provides set of navigation links to report items in a
rendered report.
51. Wht is report server
utilities?
52. How to add comments to
report dynamically?
53. Wht is report viewer
controller?
Ans: with the report viewer we can see list of
available reports in list formats. If we click on single report it will open
from report server.
- 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 code. - 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 <ExtensionName="RenderDevice"/> tag for the specific device.
1. What are the main
components of reporting services?
Report designer, report server, report
manager, report user.
2. Where can u
publish the report?
By using report designer or publish reports on
report server.
3. What are the
necessIty things for creating matrix report?
Page, column, row, details
4. for generating
reports which is used like RDBMS OR CUBE?
Depends on data
5. What is assembly
code in SSRS?
a)
6. What is Rdl file?
Rdl is a Report DefinItion
Language. Every report is saving wIth Rdl Extension.
7. How can u create a
job?
Job is a group of reports.
The reports can be processed eIther sequentially or
parallel.
SSRS
Interview Questions and Answers
What is Query parameter in
SSRS?
Query parameters is mentioned in the query of the datasources that are to be included into the SQL script’s WHERE clause of the SQL that can accept parameters. Query parameters begin with the symbol @.The name should not contain spaces and can not begin with numeral. For clarity, we use only letters.
Query parameters is mentioned in the query of the datasources that are to be included into the SQL script’s WHERE clause of the SQL that can accept parameters. Query parameters begin with the symbol @.The name should not contain spaces and can not begin with numeral. For clarity, we use only letters.
• What are the Reporting
Service Components in SSRS?
Report Designer: A place where we can create report. Report Server: Provides services for implementation and delivery of reports. Report Manager: A Web-based administration tool for managing the Report Server.
Report Designer: A place where we can create report. Report Server: Provides services for implementation and delivery of reports. Report Manager: A Web-based administration tool for managing the Report Server.
• What is a matrix in SSRS?
A matrix is a data region linked to a report set. Matrix allows us to create crosstab reports with the report variables displaying on rows and columns. It allows us to drag and drop fields into it.
A matrix is a data region linked to a report set. Matrix allows us to create crosstab reports with the report variables displaying on rows and columns. It allows us to drag and drop fields into it.
• What are sub reports and how
to create them?
A sub report is like any other reports which can be called in main report and can be generate
through main report. Parameters can be passed from main report to sub report and basis of
that report can be generated.
A sub report is like any other reports which can be called in main report and can be generate
through main report. Parameters can be passed from main report to sub report and basis of
that report can be generated.
• What is the report model
project?
Report model project is for creating Adhoc reporting. You can create the adhoc reports
through report builder. Report model project can be created on bids or report server. This
model can have simple view. And using
Report model project is for creating Adhoc reporting. You can create the adhoc reports
through report builder. Report model project can be created on bids or report server. This
model can have simple view. And using
• What is report server
project?
Report Server Project contains the RDL file and it need to be deployed on report server to
view the report files to application and user.
It a solution where we design our reports. You can add it by going into BIDS clicking on
new item and then selecting reports server project. Once the solution is created you can start
creating reports.
Report Server Project contains the RDL file and it need to be deployed on report server to
view the report files to application and user.
It a solution where we design our reports. You can add it by going into BIDS clicking on
new item and then selecting reports server project. Once the solution is created you can start
creating reports.
• What is the report builder?
Report builder is used to create small reports and it a define interface. You can’t change the
report interface in report builder it pre designed. You can just drag columns in the report.
Report builder creates reports on database objects available with report model project.
Report builder is used to create small reports and it a define interface. You can’t change the
report interface in report builder it pre designed. You can just drag columns in the report.
Report builder creates reports on database objects available with report model project.
• In which SQL Server version
report builder introduced?
Report builder introduced in SQL Server 2005. While creating or deploying report model
project on report server you can get error or it might not get created. For this you need to
check whether the service pack 22 is installed or not.
Report builder introduced in SQL Server 2005. While creating or deploying report model
project on report server you can get error or it might not get created. For this you need to
check whether the service pack 22 is installed or not.
• How to deploy the Report?
Report can be deployed in three ways.
1. Using visual studio: In visual studio you can directly deploy the report through
solution explorer by providing the report server URL in project properties at Target
Server URL. This will deploy entire project or single report as per you selection.
2. Using report server: Can directly go to the report server and deploy the report by
browsing the report from the disk location on server.
3. Creating the utility: SQL server provides the utility using that which can be used to
create a customize utility for your report deployment in bulk.
Report can be deployed in three ways.
1. Using visual studio: In visual studio you can directly deploy the report through
solution explorer by providing the report server URL in project properties at Target
Server URL. This will deploy entire project or single report as per you selection.
2. Using report server: Can directly go to the report server and deploy the report by
browsing the report from the disk location on server.
3. Creating the utility: SQL server provides the utility using that which can be used to
create a customize utility for your report deployment in bulk.
• What is RS.exe utility?
Rs.exe utility is used for deploying the report on report server. It comes with the report
server and can be customize accordingly.
Rs.exe utility is used for deploying the report on report server. It comes with the report
server and can be customize accordingly.
• What is the name of reporting
services config file and what’s it’s used for?
Reporting service config file is used for report configuration details. It contains the report
format and also the report import types. Report service config reside at ISS.
Reporting service config file is used for report configuration details. It contains the report
format and also the report import types. Report service config reside at ISS.
• What are the three different
part of RDL file explain them?
In visual studio RDL files has three parts.
1. Data: It contains the dataset on which we write the query. Data set is connected with
data source.
2. Design: In design you can design report. Can create tables and matrix reports. Drag
columns values from source.
3. Preview: to check the preview after the report run.
In visual studio RDL files has three parts.
1. Data: It contains the dataset on which we write the query. Data set is connected with
data source.
2. Design: In design you can design report. Can create tables and matrix reports. Drag
columns values from source.
3. Preview: to check the preview after the report run.
• Which language rdl files made
of?
RDL files are written in XML.
RDL files are written in XML.
• What is the chart in report?
Chart reports are for graphical representation. You can get pie charts columns harts and
various other options.
3d charts are also available in reporting services.
Chart reports are for graphical representation. You can get pie charts columns harts and
various other options.
3d charts are also available in reporting services.
• What is Data Set in report?
Data set are the set of data which we want to show in report. Data creates on data source.
Data source is the source of data from where we are getting this data i.e. database server and
database name connection string.
Data set are the set of data which we want to show in report. Data creates on data source.
Data source is the source of data from where we are getting this data i.e. database server and
database name connection string.
• What are the different types
of data sources in SSRS?
SSRS use different data source. Some of them are listed below.
1. Microsoft SQL Server
2. OLEDB
3. Oracle
4. ODBC
5. SQL Server Analysis Service
6. Report Server Model
7. SAP Net weaver BI
8. Hyperion
9. Teradata
10. XML
SSRS use different data source. Some of them are listed below.
1. Microsoft SQL Server
2. OLEDB
3. Oracle
4. ODBC
5. SQL Server Analysis Service
6. Report Server Model
7. SAP Net weaver BI
8. Hyperion
9. Teradata
10. XML
• What is the web service used
for reporting services?
Reporting Service Web Service used in SSRS. By accessing this web service you can access all
report server component and also get the report deployed on report server.
Reporting Service Web Service used in SSRS. By accessing this web service you can access all
report server component and also get the report deployed on report server.
• How to add the custom code in
Report?
To add the custom codes in report go to report tab on top then properties and there you will
find the options for custom code.
To add the custom codes in report go to report tab on top then properties and there you will
find the options for custom code.
• What is a cache in SSRS?
Report server can lay up a copy of processed report in a memory and return the copy when a user opens the report. This server memory is known as cache and the process is called caching.
Report server can lay up a copy of processed report in a memory and return the copy when a user opens the report. This server memory is known as cache and the process is called caching.
• What is report snapshot in
SSRS?
Report snapshot is a report which contains layout information and a dataset that is extracted at a particular point of time. When the new snapshot is created the previous report snapshot will be overwritten.
Report snapshot is a report which contains layout information and a dataset that is extracted at a particular point of time. When the new snapshot is created the previous report snapshot will be overwritten.
• What is bookmark link in
SSRS?
Bookmark is a link which a person clicks to shift to a different area or page in a report. We can insert bookmarks links only to textboxes and images.
Bookmark is a link which a person clicks to shift to a different area or page in a report. We can insert bookmarks links only to textboxes and images.
• What is Command parameter in
SSRS?
A Command parameter is used to forward protocol to the Report Server regarding the item that has been retrieved. Command parameter is also used for rearranging a user’s session information.
A Command parameter is used to forward protocol to the Report Server regarding the item that has been retrieved. Command parameter is also used for rearranging a user’s session information.
• What is Format parameter in
SSRS?
Format parameter is used to control report output. Every delivering format on Report Server has to pass through this parameter.
Format parameter is used to control report output. Every delivering format on Report Server has to pass through this parameter.
• What is Snapshot parameter in
SSRS?
When a report is saved in a snapshot history, it is allocated a time or date to uniquely identify that report. Snapshot parameter is used to get back these historical reports by passing this time or date to get proper report.
When a report is saved in a snapshot history, it is allocated a time or date to uniquely identify that report. Snapshot parameter is used to get back these historical reports by passing this time or date to get proper report.
• What are the rendering
extensions of SSRS?
Rendering extensions manage the category of document produced when a report is processed. Rendering Extensions are: HTML, MHTML, EXCEL, CSV, IMAGE, PDF, and XML.
Rendering extensions manage the category of document produced when a report is processed. Rendering Extensions are: HTML, MHTML, EXCEL, CSV, IMAGE, PDF, and XML.
• What is a ReportItem in SSRS?
A ReportItem is one of the subsequent types of items: Rectangle, Textbox, Image, Subreport, Line, CustomReportItem, List, Table, Matrix or Chart.
A ReportItem is one of the subsequent types of items: Rectangle, Textbox, Image, Subreport, Line, CustomReportItem, List, Table, Matrix or Chart.
• What is Datasets in SSRS?
Dataset elements have information about the sets of data recovered as a part of the report.
Dataset elements have information about the sets of data recovered as a part of the report.
• What are the main components
of reporting services?
The main components of Reporting Services are
The main components of Reporting Services are
- Report designer
- Report server
- Report manager
- Report user.
• What is Report Designer?
Report Designer is a collection of graphical query and design tools that are hosted within the Microsoft Visual Studio environment.
Report Designer is a collection of graphical query and design tools that are hosted within the Microsoft Visual Studio environment.
Report Designer provides
a Report Data pane to organize data used in your report, and tabbed views for
Design and Preview so that you can design a report interactively.
Report Designer also provides query designers to help specify data to retrieve from data sources and the Expression dialog to specify report data to use in the report layout.
When the report is complete, you can use Report Designer to preview the report and publish it directly to the report server.
Report Designer also provides query designers to help specify data to retrieve from data sources and the Expression dialog to specify report data to use in the report layout.
When the report is complete, you can use Report Designer to preview the report and publish it directly to the report server.
• 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 browse the report server folders or search for specific reports. You can view a report, its general properties, and past copies of the report that are captured in report history.
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 browse the report server folders or search for specific reports. You can view a report, its general properties, and past copies of the report that are captured in report history.
• What is Report Builder?
Web-based tool to create reports
Come free with SQL Server 2005
Launch Report Builder 1.0 to create ad hoc reports that you can save and run on the report server.
Users build desired reports themselves; can export to Word, Excel, PDF, and more
Web-based tool to create reports
Come free with SQL Server 2005
Launch Report Builder 1.0 to create ad hoc reports that you can save and run on the report server.
Users build desired reports themselves; can export to Word, Excel, PDF, and more
• What is Report Server?
The report server is the central component of a Reporting Services installation. It consists of a pair of core processors plus a collection of special-purpose extensions that handle authentication, data processing, rendering, and delivery operations.
The report server is the central component of a Reporting Services installation. It consists of a pair of core processors plus a collection of special-purpose extensions that handle authentication, data processing, rendering, and delivery operations.
SSRS-Interview Questions
54. What is difference b/w
matrix, tabular and list ?
Ans:
Table: - Has fixed Number of Columns,
Variable number of rows.
We can group and aggregate rows
only.
Matrix: - has variable number of
columns and rows.
we can group and aggregate rows
and columns .
List: -Displays set of report
items that are repeated for each group or row data.
55. What is shared
datasource and embedded datasource?
Ans:
Shared data source, we can use for different reports for that project and if the
database is on LAN, different developers can use.
Embedded data source can use if
database is already saved in server.
56. What are the
connection types available in SSRS?
1.Sql server
2.ODBC
3.XML
4. SQL Analysis server
5. SQL Server Report
Model
6. Oracle…..
57. What is the purpose of
table property?
Ans:
I think There is no table property (Ask your friends also). If it is Tablix
property, we use for applying filters,
sorting and drilldowns
58. What is use of
Expression and what are all available expressions?
Ans:
- we can use expressions for writing our custom expressions. We can write
expressions by using variables, built-in fields (Execution time, Page Number,
Report Folder, Report name), parameters, fields, datasets, variables
(Arithmetic, comparison, concatenation…) and Common function (Text, date time,
math ….)
59. What is difference b/w
iif and switch?
And:
- IIF: - Returns one of two objects, depending on the evaluation of an
expression.
Ex:
-=IIf(Fields!YearlyIncome.Value >= 60000,"High","Low")
CASE:
- Evaluates a list of expressions and returns an Object value corresponding to
the first expression in the list that is True.
Ex:
-=Switch(Fields!State.Value =
"OR","Oregon",Fields!State.Value =
"WA","Washington")
=Switch(Fields!FirstName.Value
= "Sue","Susan",Fields!FirstName.Value =
"Bob","Robert")
60. What is Running Value?
Returns a running
value of all non-null numericvalues specified by the expression, evaluated for
the given scope.
Runningvalues(expression,function,
scope)
The function takes 3 parametrs
Ex: -=running
value(Fields!Dailysales.value, sum,”table1_details group”) (last one is scope)
61. What are the variables
available in SSRS?
62. What are the global
variables and local variables?
63. How will you do
alternate row color change?
Ans:-
By writing expression in IIF clause
=IIf(Rownumber(Nothing)
MOD 2!=0,”Grey”,”Transparent”)
64. What is drilldown
report and difference b/w drilldown& drill through?
Ans:
- Drilldown: Drilldowns are applied for the report, by selecting toggled item
property. If we give value, based on that value report is generated ( with ‘+’
symbols for that value)
Drill
Through: - Drill throughs are like sub-reports only. For this we have to take
two reports and we have to pass the value from second report to first report.
So that, if we click on the first report parameter, it will go to second report
based on that value.
65. What is interactive
sorting?
Interactive
sorting is useful for end-user, to view data either ascending or descending
format. We can apply interactive more than one column also.
66. How to create multiple
groups and multiple totals in each group?
First
we have to create parent group under row groups ----> details (Right click),
and then we can add child row groups.
Multiple
totals can be applied for each group based on sum (Fields! (Column
Name).value).
67. How do you pass multi
param pass through query?
68. What is the cascading
parameter?
Child
parameters values depend on Parent variable. We can create upto 32 levels.
Child parameter value wont enable until we enter value in parent parameter.
69. What is difference b/w
multi value and multi param?
70. What is difference b/w
hidden and internal?
71. How will you do the
dynamic sorting and dynamic grouping?
Dynamic
grouping can be applied by passing value at runtime.
72. What are the sections
available in charts?
3
sections
4.
Category
field( x-axis)
5.
Data
field (y-axis)
6.
Series
field
73. What are the charts
available in reports?
They
are 8 different charts are available
They
are
9.
column
10. Line
11. Shape
12. Bar
13. Area
14. Range
15. Scatter
16. polar
74. What are the gauges
available in reports?
Radial
gauges
Linear
guages
75. How many ways to rendering the reports?
76. How to deploy the
reports on Report Server?
We
can deploy reports in 2 ways
3.
BIDS:
- go to solution explorer right click on project and enter deploy server url
(Http://localhost:8080/ReportServer)
4.
Command
prompt (RSutility)
77. How many ways to
deploy in report server?
Above
answer
78. What is difference b/w
report manager and report server?
Ans:
Report Server stores the data and time and of the report is created. It will
display in List format.
Report manager displays list of
available reports and data sources.
79. What is the linked
report?
Ans:
Types of Reports:
·
Parameterized reports
A parameterized report
uses input values to complete the report. With the parameterized reports ,
report can be varied depending upon the input variable we assign. Parameterized
paramters used in Drill through Reports.
·
Linked Reports
A linked
report is a report server item that provides an access point to an existing
report.
A linked
report is derived from an existing report and retains the original report
definition.
·
Snapshot reports
A Report
snapshot is a report that contains layout information and query results that
were retrieved at a specific point of time.
·
Cached Reports
A cached
report is a saved copy of a processed report. Cached reports are used to
improve performance by reducing the number of processing requests.
·
Adhoc Reports
·
Click through Reports
·
Drill Down reports
·
Drill Through Reports
·
Sub Reports
80. What are all the ways
we can provide security for report?
We
can provide 3 ways for the report
4.
Database
level
5.
Project
level
6.
Report
Level
81. How will provide
report level security?
Go
to (Http://localhost:8080/Reports) select one report
and go to Properties there you find security option, in that we can provide.
82. How will provide the
data level security?
Go
to above link select project and select security, there you can assign
security.
83. How do you provide
data source security?
Ans:
go to reports in the reports select particular report and click on properties
there we can find datasources , select that one and provide credentials.
84. What is difference b/w
local server and server url?
Local
server means we can store our reports in our own (local) system only.
Server
Url:- if we want to store our reports in centralized server , at that time we
will give that server URL.
85. How do you pass the
url with parameter?or what is url command on report server?
86. How do you pass the
command with url?
87. How do you integrate
dot net code or VB Script code in Reports?
88. How will integrate dot
net dll into reports?
Ans:
open the Report properties dialog box from the the visual studio designer and
select refernces tab.
Browse
and select the custom code .dll file and preview it , we will get error. Take
the .dll file and copy in Microsoft visual studio
9.0/Common7/IDE/PrivateAssemblies
89. What is default size
memory on report server?
Ans:
Report and model size is based on the size of report definition and report
model files(.smdl). however the report server doesnot limit the size or model
of the report that we publish in report server. But ASP.NET imposes the size of
the model or report that posted to the reportsever. By default server size 4MB.
90. What is subscription
and what are all available subscriptions on reports?
Ans:
Delivering report to the user on daily/ weekly/monthly based.
These
are two types:
·
Standard
subscriptions
·
Data-Driven
Subscriptions
91. What is data driven
subscriptions?
Ans:
Reporting Services supports 2 types of subscriptions
3.
Standard
subscriptions: created and managed by
individual user. It consists of static data
4.
Data-driven
Subscriptions: data can be change at run time and if want to deliver to the
particular members present in the database, by writing query from the database.
These are
two types
·
E-mail
subscriptions
·
File
share subscriptions
92. What is report
builder?
Ans:
Report Builder provides individuals with a quick and easy way to create
attractive and ad-hoc reports.
93. What report server
architecture?
94. What is difference b/w
2005&2008 SSRS?
Mainly
6.
there
is no IIS
7.
Rendering
format MS-word as introduced.
8.
We
can format custom textbox text in bold and
italic .In 2005 we cannot format text in
textbox.
9.
Used
HTTP.sys
10. More enhancements in
rendering formats of excel, pdf and CSV….
95. What is typical
situation faced in your project?
96. What is RS- Report
configuration file?
97. How will you do the
SMTP configuration?
We
can configure our SMTP settings by Going into Reportservices configuration
manger----->Email settings----> SMTP Settings.
98. While configuration
how will you do the configuration setting in report server?
99. How many ways to
deliver the reports?
100.
What
is purpose of sub report?
Sub-reports
are useful, when we want to find out list of values based on one parameter,
then we pass that parameter to that second report.
Ex:-
consider I have two reports with names sales, sellerId. If I linked sellerid
column in the second report to sellerid column in the first report , by giving
click on the first report sellerid, it goes to the second report and gives
information about how many sales done by him on datewise.
101.
How
will you move the reports from difference environments
102.
What
is report server utility?
103.
What
is documentation map?
ANS:
Documentation map provides set of navigation links to report items in a
rendered report.
104.
Wht
is report server utilities?
105.
How
to add comments to report dynamically?
106.
Wht
is report viewer controller?
Ans: with the report viewer we can see list of
available reports in list formats. If we click on single report it will open from
report server.
- 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 code. - 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 <ExtensionName="RenderDevice"/> tag for the specific device.
1: What are sub reports and how to create them?
A sub report is like any other reports which can be called
in main report and can be generate through main report. Parameters can be
passed from main report to sub report and basis of that report can be
generated.
2: What is the report model project?
Report model project is for creating Adhoc reporting. You
can create the adhoc reports through report builder. Report model project can
be created on bids or report server. This model can have simple view. And using
3: What is report server project?
Report Server Project contains the RDL file and it need to
be deployed on report server to view the report files to application and user.
It a solution where we design our reports. You can add it by
going into BIDS clicking on new item and then selecting reports server project.
Once the solution is created you can start creating reports.
4: What is the report builder?
Report builder is used to create small reports and it a
define interface. You can’t change the report interface in report builder it
pre designed. You can just drag columns in the report. Report builder creates
reports on database objects available with report model project.
5: In which SQL Server version report builder introduced?
Report builder introduced in SQL Server 2005. While creating
or deploying report model project on report server you can get error or it might
not get created. For this you need to Check whether the service pack 22 is
installed or not.
6: How to deploy the Report?
Report can be deployed in three ways.
1. Using visual studio: In visual studio you can directly
deploy the report through Solution explorer by providing the report server URL
in project properties at Target Server URL. This will deploy entire project or
single report as per you selection.
2. Using report server: Can directly go to the report server
and deploy the report by Browsing the report from the disk location on server.
3. Creating the utility: SQL server provides the utility
using that which can be used to Create a customize utility for your report
deployment in bulk.
7: What is RS.exe utility?
Rs.exe utility is used for deploying the report on report
server. It comes with the report server and can be customize accordingly.
8: What is the name of reporting services config file and
what’s it’s used for?
Reporting service config file is used for report configuration
details. It contains the report Format and also the report import types. Report
service config reside at ISS.
9: What are the three different part of RDL file explain
them?
In visual studio RDL files has three parts.
1. Data: It contains the dataset on which we write the
query. Data set is connected with
Data source.
2. Design: In design you can design report. Can create
tables and matrix reports. Drag
Columns values from source.
3. Preview: to check the preview after the report run.
10: Which language rdl files made of?
RDL files are written in XML.
11: What is the chart in report?
Chart reports are for graphical representation. You can get
pie charts columns harts and
Various other options.
3d charts are also available in reporting services.
12: What is Data Set in report?
Data set are the set of data which we want to show in
report. Data creates on data source.
Data source is the source of data from where we are getting
this data i.e. database server and
database name connection string.
13: What are the different types of data sources in SSRS?
SSRS use different data source. Some of them are listed
below.
1. Microsoft SQL Server
2. OLEDB
3. Oracle
4. ODBC
5. SQL Server Analysis Service
6. Report Server Model
7. SAP Net weaver BI
8. Hyperion
9. Teradata
10. XML
14: What is the web service used for reporting services?
Reporting Service Web Service used in SSRS. By accessing
this web service you can access all report server component and also get the
report deployed on report server.
15: How to add the custom code in Report?
To add the custom codes in report go to report tab on top
then properties and there you will
Find the options for custom code.
16: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.
17: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.
18:What are data regions?
Data regions are report items that display repeated rows of
summarized information from datasets.
19: 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.
20: 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.
21: 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.
22: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.
23: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.
24: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.
25: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.
26: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.
27: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.
28: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.
29: 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.
30: How do you configure a running aggregate in SSRS?
You can use the Running Value function to configure a
running aggregate.
31: 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.
32: What is the main purpose of a query parameter?
The main purpose of a
query parameter is to filter data in the data source.
33: 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.
34: 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.
35: What programming language would you use to create
embedded functions in SSRS?
An SSRS report
supports only visual Basic .nET embedded code.
36: 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.
37: 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.
38: 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.
39: 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.
40: 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.
41: 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.
42: 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.
43: 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.
44: 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 Report Server SQL Server
database.
45: 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.
46: 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 Report Server database to execute a report
on a schedule.
47: 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.
48: 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.
49: 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.
50: 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.
51: 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.
52: What is the file name extension for an encryption key
backup?
Encryption key
backups have an .snk file name extension.
53: 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.
54: 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
<ExtensionName=”RenderDevice”/>
tag for the specific device.
55: How to make
conditional sum in SSRS?
IIF condition true then SUM else nothing. Visit our tutorial for more
details
56: How to find a
value in another dataset based on current dataset field (SSRS 2008 R2)?
Use lookup function.
57: How to change parameter value inside the report?
Set action. “Jump to itself” and pass different value for the parameter.
58: How to identify current user in SSRS Report?
User!UserID
59: How to create “dependant” parameter “Make, Model,
Year”
They need to be in correct order, and previous parameter should filter
next parameter dataset. For instance Model dataset should be filtered using
Make parameter
60: How to create “alternate row colour”?
use RowNumber and Mod function OR visit our tutorial.
61: How to create percentile function? Custom
code is required. Visit our tutorial for more details.
62: How to create median function?
Custom code is required. Visit our tutorial for more details.
63: 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.
64: 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.
65: What are data
regions?
Data regions are
report items that display repeated rows of summarized information from
datasets.
65: 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.
66: 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.
67: 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.
68: 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.
69: 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.
70: 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.
71: 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.
72: 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.
73: 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.
74: 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.
75: 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.
76: How do you
configure a running aggregate in SSRS?
You can use the
RunningValue function to configure a running aggregate.
77: 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.
78: What is the main
purpose of a query parameter?
The main purpose of a
query parameter is to filter data in the data source.
79: 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.
80: 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.
81: What programming
language would you use to create embedded functions in SSRS?
An SSRS report
supports only visual Basic .nET embedded code.
82: 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.
83: What are different option to deploy report from Dev to
Prod.
Ø From Solution
Explorer by giving Target Sever URL
Ø Uploading the .rdl from Report
manager.
84: Describe the role of Encrpytion key in Report Server
Configuration manager. To
encrypt credentials, connection strings. Can be taken backup and restore when
required from configuration manager.
85: Name and Describe few console utilities for SSRS.
RSConfig.exe Configuration of connection properties between
the Report Server to the repository database.
RSKeyMgmt.exe Management of encryption keys via command-line
RS.exe Scripting of report deployment
86: How can you access the Code in You report. Describe the
Static and intance based method with example.
Static methods within a custom assembly are available globally within
the report. You can access static methods in expressions by namespace, class,
and method name
The following example calls the method ToGBP, which converts the
StandardCost field value from dollar to pounds sterling:=CurrencyConversion.DollarCurrencyConversion.ToGBP(Fields!StandardCost.Value)Instance-based
methods are available through a globally defined Code member. You access these
by referring to the Code member, and then the instance and method name. The following
example calls the instance method ToEUR which converts the StandardCost field
value from dollar to
euro:=Code.m_myDollarCoversion.ToEUR(Fields!StandardCost.Value)
87: how to add custom Assemblies to Report. 1. On the Report menu, click Report
Properties
2. On the References tab, do
the following:
a. In References, click the add (…) button
and then select or browse to the assembly from the Add Reference dialog box.
b. In Classes, type name of the class and
provide an instance name to use within the report.
88: 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.
89: What is SQL Server Reporting Services?
Reporting Services helps business to Transform raw data (table data)
into graphical like chart, histogram etc. to add meaning to raw data.
90: What is reporting lifecycle ?
Reporting Services has three mainly three phases
1.Development of Reports (Developer) – First of all a report
needs to be design which is primarily done by report developer
2.Management of Reports (DBA) – Once the Report is being
developed, DBA need to ensureReport Delivery (DBA+Developer) – Once the report
is being developed and executed now the report should be reached to final
recipients (business users) who are going to understand / analyze report data.
if any changes, we again go back to development stage. 1.Security – Only
authorized user should access the report
2.Execution – How the report will be executed to optimize
data sources performance
3.Scheduling of reports – so that report are executed on
scheduled timings
3.
91: What can we do with SQL Server Reporting Services ?
Reporting Services can help you in building and deploying
fully interactive scorecards, dashboards, and enterprise reports.
SQL Server Reporting Services Feature
?Design ad-hoc dashboards and reports quickly
?Data is interactive and available everywhere even on your
IPAD / IPHONE.
?This is bundle product with SQL Server database services,
so there is additional cost for this service.
?.Net integrated for rapid development for report even in
few clicks by using wizards. By using Reporting Services, we can build up a
solution which is kind of Google maps, where we can list our all business
locations
92: What are the New Features of SQL Server 2008 R2
Reporting Services ?
Lot of new features were added in SQL Server 2008 R2, like
?New Report Types – Table, Matrix, List, Chart, Sub report
?New Tools added to report designer Toolbox
?Report Data Panel -
built in page numbers
?Report Builder 3.0
93: What are the new features are introduced in SQL Server
2012 reporting services? SQL Server 2012
has introduced a couple of new features like
?Power View - interactive data exploration
?SQL Server 2012 is fully integrated with SharePoint
?Introduction to Data Alerts, data alerts are a data-driven
alerting solution that informs you about changes in report data that are of
interest to you, and at a relevant time
?SQL Server Data tool
?new rendering extensions supports MS Office 2010
?Project Crescent is being introduced
94: What is Reporting Services Architecture ? Reporting
Services is a .NET framework-based platform that includes a comprehensive tools
that we can use to integrate reporting solution into any centrally-managed
environment.
95: What are the tasks which are mainly performed by a
production DBA on a SQL Server Reporting Services?
Operational support DBA are mainly responsible for
?Install and Configure reporting services
?Backing up Reporting Services (including databases, config
files and encryption keys)
?Deploying new reports
?create and manage shared data sources
?Optimizing reports – Enable caching of reports
?Authorize users for reports
?Create and deploy security roles.
?Check Reporting Services Error Logs for errors
?Maintain response time of reporting services
96: Can we run Reporting Services with SQL Server Express
Edition, which is a free version of SQL Server ?
Yes, SQL Server Express Edition with Advanced Services
support Reporting Services. we can use SQL Server Express Edition for deploying
reporting Services.
97: What are the limitations in Reporting Services on SQL
Server Express Edition ?
Microsoft offers reporting services free as part of SQL
Server Express with Advance Services edition but following restrictions will be
applicable to this edition.
?SQL Server Management Studio Express cannot be used to
administer report server
?Report Models will not be available
?Other features like Caching, History and Delivery of Report
is not available.
?Report Builder is not available
?Local SQL Server is a only option, which can be used as
Report Data Source.
?We cannot store the report server database on a remote
server, it has to be local only.
?Reports can be rendered only in Excel, PDF, Image formats
only
?No SQL Server agent is available, so no scheduling is
possible.
?Reporting Services will not be able to use more than 1 GB
of RAM
?No Subscriptions (Standard and Data Driven) can be made
?Can not be integrated with Share Point
?Only named instances is supported
?Scale-out Report Servers will not be available
?Can not implement Role based security.
98: What are the parameters which should be considered for
Reporting Services Disk storage requirement ?
There are various factors to be considers as a primary I would consider
1.Number of reports to be hosted server
2.Report size and frequency of reports
3.Number of snapshots to be saved
99: What is Report Server and ReportServerTempDB ?
Reporting Services uses two SQL Server databases for storage
by default, the databases are named Report Server and ReportServerTempdb.
Report Server is a main database, which store all internal
configuration and report meta data whereas ReportServerTempdb is used to store
temporary data, session information, and cached reports.
100: How to backup SQL Server Reporting Services ?
There are mainly three things, which should be backed up as
part of reporting services backup
1.Report Server Databases, which can be backed up by SQL
server backup and restore method
2.SQL Server Reporting Services Configuration, SQL Server
Reporting Services Configuration is saved in config files, which can be copied
as part of backup. look for other to know config files and there location.
3.Encryption Keys backup , use SQL Server Reporting Services
Configuration tool to backup symmetric keys.
101: What is encryption key ?
Encryption keys are used by the report server so that items
such as connection strings are maintained securely. These keys are required in
case you want to perform restoration of report server databases.
102: How to backup encryption key ? Encryption
Keys backup , use SQL Server Reporting Services Configuration tool to backup
symmetric keys.
103: Can we install a 32-bit version of SQL Server Reporting
Services on a computer that is running a 64-bit version of Windows?
YES, we can install SQL Server 32 bit on Windows 64 bit version. SQL
Server Reporting services are part of SQL Server.
104: Where the SQL Server Reporting Configuration is saved,
is that in registry ? NO, all SQL
Server configuration is saved in configuration files (.xml files)
105: What are the key configuration files for SQL Server
Reporting Services ?
Mostly all Configuration files located at Install
Directory\Microsoft SQL Server
\<SQL Server
Instance>\Reporting Services\ReportServer and ReportManager
?RSReportServer.config stores configuration settings for
feature areas of the Report Server service: ReportManager, the Report Server
Web service, and background processing.
?RSSrvPolicy.config stores code access security policies for
the server extensions.
?RSMgrPolicy.config stores code access security policies for
Report Manager
?ReportingServicesService.exe.config stores configuration
settings that specify the trace levels and logging options for the Report
Server service.
106: What are the mostly used PERFMON Counters for
monitoring SQL Server Reporting Services?
This is bit tricky and everyone has his own answer, but I
personally choose the following at least
?ReportServer: Service Performance Object \ Active
Connections
?ReportServer: Service Performance Object \ Report Executed
per second
?ReportServer: Service Performance Object \ Total Cache Hits
?ReportServer: Service Performance Object \ Total Requests
?Web Service \ Deliveries per second
?Web Service \ Total processing failures
107: What all should be backed as part of reporting Services
?
1.Report server databases (ReportServer and
ReporterverTempdb)
2.Encryption keys and
3.Configuration Files (xml files) 1.RSReportServer.config,
2.RSSrvPolicy.config,
3.RSMgrPolicy.config,
4.ReportingServicesService.exe.config,
5.RSReportDesigner.config and
6.RSPreviewPolicy.config
108: Can I disable | restrict SQL Server Reporting Services
Export Formats (Rendering Formats) ?
YES, we can restrict Reporting services export formats.
SQL Server Reporting Services (SSRS) supports export to different
formats like XML, CSV, HTML, PDF, etc., when you view the report. This export option is available at report
viewer Export drop-down list.
109: How to migrate SQL Server 2008 Reporting Services to
another computer?
If you are trying to migrate only SQL Server reporting
services to new server, then we have an option to install reporting services on
new server and while configuring report server, we can point report server
services to old report database database.
Or incase, you are trying to migrate report server databases
and reporting service together, the install reporting services on new instance
and restore configuration files and
restore report server databases and reconfigure the report server services.
110: How to check how frequent report is being called ?
Check Report history either from SSMS or Report manager. In
report manager, go to report and select history tab.
111: What is Report Caching ?
Report caching is a performance enhancing technique in
Reporting Services that saves copies of reports for faster viewing. Cached
instances have an expiration time set to force a refresh of the data set used
for the report.
112: In case you have filters in your report, when filters
will be applied in Cached Report instance ?
Filters are applied when a report is rendered, Filters will
not create a new cached instance on the Report Server.
113: What is report snapshot ?
Snapshot means a instance of a report for future reference,
that means a copy of report (data is freezed) will be saved on a report server
for future reference.
114: What is Parameterized Reports in SSRS ?
You often need more advanced reporting such as drop down
lists and the ability to use parameters when working in Sql Server Reporting
Services.
?How to use variables that have not been declared in SSRS vs
TSQL
?How to create report with auto-Parameterization
?How to View Report Parameters
?How to create a drop-down list
115: What are different types of reports are available ?
?There is as such not any type of of reports, but we can
categories them as
?Tabular Report – where we represent data in rows and
columns format. (Now this is non as Tablix which is mixture of table and a
matrix)
?CHART REPORTS – where we represent the data in graphical
format mainly in charts?
116: What sort of query we can write in SSRS, is that just
SQL / TSQL or is there anything else too?
?MDX – for OLAP / CUBE data sources
?DMX – for data mining
117: What is the RDL file?
?RDL stands for Report Definition Language, when we design a
report using any tool like BIDS and when we save the report, it’s saved as .rdl
file, RDL file is an kind of xml code, which stores the design of the report.
?This RDL file is being used
for deploying report to report server.
118: What are the product alternatives to SQL Server
Reporting Services ?
Market of full of tools and here are few of them
Actuate Hyperion (BRIO) SIEBEL-CRM
BusinessObjects Oracle Express OLAP Qlikview
Cognos Informatica Power Analyzer Proclarity
IntelliView Dundas Chart for .NET MS-Excel
SAS MicroStrategies Pentaho
119: How many instances of Reporting Services can be managed
by a single DBA ?
This is pretty tricky questions, and there is not any single
formula to decide this. This all depends on what sort of monitoring tools you
have and what sort of automation you have achieved.
But as per my understanding and calculations a single 3-7
year experience DBA without any third party tool can easily deploy 25-35 report
in a day, which is one time activity for most of the organizations and DBA can
easily monitor / provide production support to 25-40 instances of SQL Server,
which included daily
?Monitor Event Viewers
?Check SQL Server Errors
?Check Reporting Services Logs
?Monitor Perfmon counters
?Backup all databases
?check schedule sql jobs etc.
but I believe, without any tool (just using manual / Script)
we can assume 1 DBA for every 25 servers is ideal number to focus more on
preventive actions rather then corrective actions.
120: Is my data is safe, What all we are backing up for SQL
Server Reporting Services ?
There are mainly three things, which should be backed up as
part of reporting services backup
1.Report Server Databases, which can be backed up by SQL
server backup and restore method. SQL Server Reporting Services Configuration,
SQL Server Reporting Services Configuration is saved in config files, which can
be copied as part of backup. look for other to know config files and there
location.
2.Encryption Keys backup , use SQL Server Reporting Services
Configuration tool to backup symmetric keys.
121: What is the licensing cost for SQL Server Report
Server?
SQL Server Reporting Services is bundled with SQL Server. So
in case you have already purchased SQL Server Standard edition for your
business then there is no additional cost for using this SQL Server Reporting
Services.
122: Do I need to buy separate licenses for users who view
reports ?
NO, there is no separate licenses for report delivery or
viewing report on demand, i’’s all bundled in SQL Server with once license
cost.
123: How to pass cascaded parameters in report?
Cascaded parameters are the parameters which are depended on
other parameters. These parameters can be created in parameter section in upper
window while creating the report.
124: How to implement group by in report?
Group by can be implemented by adding a group on row in
table data part. Adding group will two group rows for this row on above and
below of this particular row.
Below row can be used for subtotal of this group which is
specified in above row. By repeating the same operation on group row can
created a group on group.
125: Why can’t we use page total in the report body?
The built in field [&pagenumber] and [&pagetotal]
cannot be used in report body as these are applicable in report header or
report footer.
These built in field can be added in footer or header for
better representation of the report.
126: What is the web service used for reporting services?
Reporting Service Web Service used in SSRS. By accessing
this web service you can access all report server component and also get the
report deployed on report server.
127: Add alternating row color to SQL Server Reporting
services report Expression: = IIf(RowNumber(Nothing) Mod 2 = 0, “Blue”,
“Green”) This trick can be applied to
many areas of the report. (oR)
=
If(RowNumber(Nothing) Mod 2 = 0, “Silver”, “Transparent”)
128: Hide/Show Items Dependent On Export Format (SSRS):
=IIF(Globals!RenderFormat.Name = “EXCEL”, True, False)
129: What is Tablix?
A Tablix can be seen as a control with combined capabilities
of a table and a matrix, which facilitates asymmetric and flexible, row and
column level, static and dynamic groupings.
130: How would you deploy SSRS Reports using out-of-box
functionality and how can you automate SSRS report deployment?
Business Intelligence Development Studio is generally used to deploy
SSRS reports. There is no out-of-box support in SSRS to automate reports
deployment, but free third-party products like RS Scripter can be used for
this.
131: It is possible to display multiple distinct fields in
one cell (textbox).
To display a date range “12:45 AM – 3:34 PM” Set the Value Property to
=FORMAT(Fields!StartDate.Value, “t”) & ” –
” & FORMAT(Fields!EndDate.Value,”t”)
If you provide formatting instructions as part of the Value
Property, you don’t need to set the Format Property
132: It is possible to use the value of one textbox as part
of a calculation in another. To
refer to another TextBox use the prefix “ReportItems!” collection, this lets
you refer to its value.
eg: If TextBox3 was
“YOY Delta” with Value: “= Fields!2008Sales.Value – Fields!2007Sales.Value”
then rather than Textbox4 “YOY Change” being “= (Fields!2008Sales.Value –
Fields!2007Sales.Value) / Fields!2007Sales.Value”.
You can reuse the calculation like:
“=ReportItems!textbox3.Value / Fields!Sales2007.Value”
133: Avoid “Divide by Zero Errors”, but only check the
denominator.
In the above example “=ReportItems!textbox3.Value /
Fields!Sales2007.Value”, if I had zero sales in 2007, I’ll see “NaN” (Not a
Number) or “Infinity”. This expression
would be better written to include a test to ensure I do not attempt to divide
by zero ie: =iif(Fields!Sales2007.Value <> 0, ReportItems!textbox88.Value
/ Fields!Sales2007.Value, “”). I often see some very creative formulas that
attempt to create the divide by zero error in the test & then provide a
different result. eg: =iif( iserror(value/0), “-”, value/0) this is a very
sub-optimal approach & quite unnecessary.
134: It is possible to use the value of one textbox to
control the color of another. Option 1: Nested IF statements
=iif(
(Fields!Discount.Value <= 0.12), “Green”, ( iif(Fields!Discount.Value >
0.2, “Red”, iif(Fields!Qty.Value mod 7 = 0, “Yellow”, “Red”) ) ) )
Option 2: Switch Statement
=Switch( (Fields!Discount.Value <= 0.12), “Green”,
Fields!Discount.Value > 0.2, “Red”, Fields!Qty.Value mod 7 = 0, “Yellow”,
true, “Red” )
The configuration settings of Report Manager and the Report
Server Web service are stored in a single configuration file
(rsreportserver.config).
You can also use “Reporting Services Configuration Manager”
to configure settings for Report Server and Report Manager.
Difference between standard subscription and data-driven
subscriptions
Standard Subscriptions
1.Standard Subscriptions are available in Sql Server
Standard version.
2.Report users can subscribe for the subscriptions.
3.The subscription is static in the sense that the list of
subscribers is not derived from a query or some Active Directory search. The
subscribers request(subscribe) for the report delivery or get an information
that the report has run and is ready for viewing.
Data-Driven
Subscriptions
1.Data-Driven subscriptions are available in Enterprise
Edition of Sql Server
2.The DBAs/SSRS administrators are the ones who configure
the subscriptions. The reports can be subscribed with different parameters.
3.The subscribers list can be dynamic and can be the result
of a query.
