Sunday, August 23, 2015

SQL Server

1.       Difference b/w SSAS and AS 2000.
2.       Fact table and dimension table
3.       Data Sources & Data Source Views -
4.       Cube Dimensions
a.       Standard Dimension
b.       Time Dimension
c.       Parent-Child Dimension
5.       Measures
a.       Adding Measure Group
b.       Measures
c.       Semi-Additive Measures
d.       Distinct Count
e.       Calculations
6.       Aggregations
7.       Partitions
8.       Hierarchies - User Hierarchy, parent child hierarchy. What are user hierarchies and attribute hierarchies?
9.       Dimension Relationships – Dimension Usage Tab
10.   How do u reference an attribute in VBScript – [$Student].[Personnel ID]
11.   Key Performance Indicators (KPIs)
12.   Actions
13.   Perspectives
14.   Cube Browser View
15.   Proactive Caching
16.   Deploying/Processing of Cube.
17.   Storage Methods – MOLAP, ROLAP & HOLAP
18.   Error handling/configurations
19.   NULL Processing/Unknown Member
20.   Translations
21.   What type of objects can u have in a DSV?
22.   What is the difference between Star schema and Snowflake schema?
23.   What are the basic things that you consider before designing a cube?
24.   How do you improve performance of a SSAS OLAP DB?
25.   How do you implement security in SSAS?
26.   What are slowly changing dimensions (SCD)? When do you use them?
27.   What are the different relationships used in dimension usage?
28.   How do you process a single partition without processing other dimensions and measures in production?
29.    What is fact-less-fact dimension? When do you use such dimension?

30.   Explain about calculated members and drill through actions.

Friday, August 21, 2015

SQL Server Reporting Services Interview Questions and Answers - Part4

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.
Posted by Ravi at 9:48 PM 0 comments
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))
Posted by Ravi at 1:49 AM 0 comments
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..
Posted by Ravi at 11:57 PM 0 comments
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.
Posted by Ravi at 4:56 AM 0 comments
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
Description: https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgVYACZJlIuhDSIuSzqKpxqzr293nc8SDhqLEiE0SFDBQhYG0oYZEj-jVIBSlsw4cnS1jlozijt76ENQxI-s0Lw7CAN4jKH-AJQ7ZW9OrusCBPrxivtGoua4WfQUM0rjCgr3umIdy4eLQM/s400/Untitled.jpg
Step3: Now Add Group for product by right clicking on Product Column as shown bellow
Description: https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhD9J1V2CvPfFhrS428VmzHMZP0_TVn0bDtBKiG6kkSd_J9KPVDkx-etPjdWsTciO8x37pw7OSOv9wszh8THFvGamQT0JfoGAN9nv1-KM8wwbn2C6_9C5-VtqqIfyubbyzBWKDuaBKnCqw/s400/Untitled.jpg
Select Add Parent group
Description: https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhc1oi_WjOJh9IBgyDIb1D1zw7zVO9ENU5eYtksU0TkysgfPeqtJH7TQlxDfyEwBqvBkQ0HqUix9xHxbOPpMZIwGuVrO4B-b64S7LkKnHq1Q_dxbGtsKJ-i_HAjacgdjwCKyRR8yuP36ro/s400/Untitled.jpg
Selet Product from Group By DropDown and check Add Group Header, then click OK

The report table looks like this
Description: https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhgxE2ZknmFZd9LkXJOJKmZ0Xwct0M2TqKurRR_74gzbYi6IvGOM54QRmwW8Sjhqm5Im_7vReDNNZcaQAyJCId9rOdW6hURfNjEei2fiIZcX5oGViiv3Oq0HVApbLh4If7li7uAFRUP7fU/s400/Untitled.jpg
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
Description: https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjA1M6Y-D2LL_TXRFYnyhFKpEks2yRht8tftTGruANtLkICt04B88FkEzui-zyiOL6JGqF-UvnUzD16_qUwnv_g2VjBI_3_2qJpm3H7A-NlGE8gavGkMM8yNA4OpYVlSP5Epg1OOWLLB9U/s400/Untitled.jpg
Then the  preview the report
Description: https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjBwb8ZRlpa8VJiWw7gV3Ywjx5c93GGtfNSc9MtAk7sWxe1c4xL2GWRjorRUXWEXNwPy1A7Z42CQ4w7gNFnkcwcaJYXruoo9l1BJMj2vOwr3SWDiuxM_wzJqqOrdUexdj4GH79QV8vL1FE/s400/Untitled.jpg
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
Posted by Ravi at 1:13 AM 0 comments
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)
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.
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.
Description: https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgIrcK6LMNKEN4PT6oZWgUnfU8h6QEQJx2l2pz4gwn5aMWYnYFJLl15ytPWHJ6hImdLvu4lDXxOFzYyCT_QiDmFQYIXGvQ8UteK4LFsg_bid1c5jxlfnvPp2ZJiUSz5WX21s3fljt2cw2g/s400/Navigation+with+in+report.jpg


 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.
Description: https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg87Q8x3Zl_pStz-94D2XrTF_uHJZO-s7mRlQKtx1oF9y_7ZuFBXyhU3ZpC4oHsL4Z_uQG7X90qLCjMcNrgJp0XA_-VXihIN4Yefyd2WwjBu3j7Iwr-J8vI3KxCXqD2M5U1DVSbEWttYeM/s400/NavigationtoReport.jpg

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.
Description: https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhtf-Ytr1s97eExiZcMciOH8XN5oEFS56IySPutBe7FGiDeang_NVhoSJVkRQodHsZCl1S7Dohg4De3PpfieqYGBjIidE89yXbibeRVrhWmyXJEHOE1jfsxioHLTGjpGNpFA8J7F0uWLdQ/s400/Navigation+to+URL.jpg
Click on "Fx" to enter custom Web URL like http://www.google.co.in/ then click on ok.
Description: https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQeqsRY94BgMT1uf3-H7GcmV8klb946UR8B64OQ2uJkfu4V17DGmVaIYxuh7JnQvsi2rnGuqgC-IPC36aUexNbrnIrGzx7GO8TL9z7HCWiIgWd6GFYaSthNWpWDoulv2JyNrLjvQUXTRc/s400/Navigation+to+URL1.jpg
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?


I need to deploy a report using Reporting services but i'm getting this error, using visual studio to deploy the report
The permissions granted to user 'Domain\user' are insufficient for performing this operation
Right now i'm the administrator of the machine, so why i'm getting i don't hace enough permissions?
Note:
The scenario is the following:
Developing and deploying on a windows7 The report server is in the same machine. In conclusion, running all local.
In spanish:
Necesito hacer un deploy de un reporte en Reporting Services. Cuando corro el visual studio obtengo el siguiente error. Los permisos otorgados al usuario son insuficientes para realizar esta operación
Actualmente soy el administrador de la máquina, entonces no entiendo porque aparece el error que no tengo suficientes permisos.
Notas:
Este es el escenario Desarrollando y haciendo el deploy en una máquina con windows7. El Report Server y el Visual Studio están en la misma máquina. En conclusión, todo lo corro localmente.
Actualmente soy el administrador de la máquina, entonces no entiendo porque aparece el error que no tengo suficientes permisos.
This problem took me a whole day to figure it out. That's why i'm posting the answer.
To resolve this problem you should follow this steps.
1.    Run Internet explorer -> Internet Options -> Trust Sites -> Add localhost -> Uncheck the last textbox
2.    Go to the Reporting Services Configuration Manager -> Report Manager URL -> Copy the URL
3.    Run Internet Explorer as an Administrator
4.    Copy the URL, hit Enter
5.    On the site -> Site Configuration -> Security -> Add your account as System Administrator permission
6.    Home -> Properties -> New Role Assigment -> Add your account as Content Manager
7.    Run Visual Studio and deploy, see if it worked.


    1.  

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.
  1. 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.
  2. 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.
  3. What are data regions?
    Data regions are report items that display repeated rows of summarized information from datasets.
  4. 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.
  5. 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.
  6. 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.
  7. 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.
  8. 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.
  9. 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.
  10. 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.
  11. 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.
  12. 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.
  13. 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.
  14. 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.
  15. How do you configure a running aggregate in SSRS?
    You can use the RunningValue function to configure a running aggregate.
  16. 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.
  17. What is the main purpose of a query parameter?
    The main purpose of a query parameter is to filter data in the data source.
  18. 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.
  19. 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.
  20. What programming language would you use to create embedded functions in SSRS?
    An SSRS report supports only visual Basic .nET embedded code.
  21. 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.
  22. 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.
  23. 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.
  24. 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.
  25. 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.
  26. 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.
  27. 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.
  28. 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.
  29. 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.
  30. 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.
  31. 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.
  32. 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.
  33. 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.
  34. 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.
  35. 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.
  36. 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.
  37. What is the file name extension for an encryption key backup?
    Encryption key backups have an .snk file name extension.
  38. 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.
  39. 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.
• 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.
• 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.
• 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.
• 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
• 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.
• 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.
• 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.
• 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.
• 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.
• 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.
• 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.
• Which language rdl files made of?
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.
• 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.
• 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
• 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.
• 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.
• 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.
• 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.
• 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.
• 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.
• 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.
• 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.
• 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.
• 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.
• What is Datasets in SSRS?
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
  1. Report designer
  2. Report server
  3. Report manager
  4. 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 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.
• 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.
• 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
• 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.

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.
  1. 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.
  2. 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.
  3. What are data regions?
    Data regions are report items that display repeated rows of summarized information from datasets.
  4. 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.
  5. 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.
  6. 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.
  7. 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.
  8. 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.
  9. 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.
  10. 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.
  11. 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.
  12. 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.
  13. 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.
  14. 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.
  15. How do you configure a running aggregate in SSRS?
    You can use the RunningValue function to configure a running aggregate.
  16. 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.
  17. What is the main purpose of a query parameter?
    The main purpose of a query parameter is to filter data in the data source.
  18. 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.
  19. 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.
  20. What programming language would you use to create embedded functions in SSRS?
    An SSRS report supports only visual Basic .nET embedded code.
  21. 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.
  22. 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.
  23. 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.
  24. 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.
  25. 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.
  26. 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.
  27. 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.
  28. 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.
  29. 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.
  30. 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.
  31. 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.
  32. 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.
  33. 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.
  34. 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.
  35. 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.
  36. 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.
  37. What is the file name extension for an encryption key backup?
    Encryption key backups have an .snk file name extension.
  38. 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.
  39. 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.