SQL Server Reporting Services (SSRS)
is a product that combines the SQL Server database engine and a front
end GUI that users and programmers can use to create reports. The latest
version of SSRS was released with SQL Server 2012, but SSRS was also
included with SQL Server 2000, 2005 and 2008. You might need to know
several versions to work in some companies, but most companies stick
with the same version of SQL Server for several years to avoid
production problems with its database. To get started in the industry,
you need to answer some technical questions regarding SSRS. Here are
some questions that you might run into when you go for your next job
interview.
1) Do you create your reports using the wizard or manually?
As with most Microsoft tools, SSRS includes a wizard that helps you create reports. The wizard is great for people who are new to the product, but it’s not good if you can’t go beyond the tools and create a report from scratch. Before you go for your interviews, make sure you can create a report from scratch without any wizards or tools to create them for you. Most companies need someone who can customize reports, and wizards leave room for very little customizations.
2) What are query parameters?
Query parameters are the parts of an SQL query that allow you to filter results. Parameters are contained in the SQL’s “where” clause. These parameters tell the SQL server which records you want to update, select, or delete. In other words, if it wasn’t for the where clause parameters, you would affect all records in your tables.
3) What is a sub-report?
Sub-reports are inserted into a main report. Just like a main report, you also pass parameters and queries to it. Think of a sub-report as an extension to your main report, but it contains a different data set. For instance, you could create a report of customers and then use a sub-report to display a list of orders for each customer.
4) What are RDL files?
RDL files are like the “language” for SSRS servers. However, RDL files are created in XML, so really the “language” of SSRS servers is XML. RDL files contain the queries and layout format for your reports.
5) What is a data set?
Data sets are the components that contain your records. You can have a blank data set, a data set with one record, or a data set with millions of records (although millions of records will probably cause performance issues for your users). You can view a data set like a customized table. The data set has the columns and rows like a table, but you determine the columns and number of rows using your SQL statements.
6) What is a data source?
A data source is the database and tables where your data set comes from. You must define the data source when you create your database connection. Every data set needs a source from which to pull the data. Your reporting server can be SQL Server 2000, 2005, 2008 and 2012.
7) What servers can be used with SSRS?
While most companies use SQL Server with SSRS, you can also integrate other database servers with your SSRS reports. SSRS is compatible with Oracle, ODBC and OLEDB connections, Hyperion, Teradata and flat XML files that contain data.
8) What is mixed mode database security?
When you install SQL Server, you have the option to allow SQL Server to integrate with Windows or require users to have a separate SQL Server user name and password. While Windows integration is convenient, it’s not considered the most secure of the two security options. Instead, it’s better to require a separate SQL Server user name and password when logging in to the database server. Your reports will need their own user name and password to run reports from SSRS.
9) Can SSRS reports cache results?
Caching reports makes it much faster for users to access and view data. If you have common data that doesn’t change often, it’s best to cache results. SSRS lets you cache reports on your reporting server. This means that it reduces the load on the SQL Server and your users can quickly access data without querying the server several times.
10) What formats can SSRS export or render to?
SSRS is compatible with several file formats. When the user runs the report, the default format prints to the web browser. You can also save or export to HTML, Excel, CSV, Image, PDF and XML formats. The SSRS reporting software has buttons at the top of each report that the user can use to export these files.
11) How do you deploy a report to a reporting server?
The SSRS reporting software includes tools to deploy directly to the SQL Server reporting server. After you’ve configured your software with the proper SQL Server, user name and password, you just need to right-click the report you want to deploy and click “Deploy.” Make sure you test the report after you deploy it to ensure there are no bugs and that the data returned is correct.
12) How do you edit a report?
Open SQL Server Management Studio and the reporting project. Expand the node that contains your reports. Right-click the report and click “Edit.” You can also save your reporting file from this view.
This is a dozen possible questions you could be asked in an SSRS interview, but it’s best to brush up on the tools and information you’ll need when you’re on the job. SSRS is not tough to learn, but the nervousness of an interview can affect your ability to answer questions. Make sure you practice before the interview.
13) What are the different kinds of SSRS Reports?
Reports can be categorized into operational and analytical reports. The distinction is based on the source of data and level of analysis facilitated by any particular report. Operational reports are based on OLTP sources and are static reports and Analytical reports are based on OLAP sources and generally facilitate drill-down and drill-through for analysis. Technically, SSRS reports can be categorized into parameterized, linked, snapshot, cached, etc...
14) What are parameterized reports? What are cascading parameters in SSRS reports?
Reports that accept parameters from users to fetch and report data conditionally, are known as parameterized reports. When you have multiple parameters in a report and values of different parameters are dependent and populated dynamically based on the value of parent parameters, it's known as a cascading parameter.
15) How would you go about developing a SSRS report?
General development methodology for a SSRS report is to start by creating a data source. Based on the data source create one or multiple datasets as needed for parameters and the body of the report. Add required controls from the toolbox which would act as a container for the fields in the dataset. Format the controls added to the report body. Verify and validate the report and finally deploy the report.
16) What is a dataset and what are the different types of datasets?
A dataset is similar to a query definition, which is executed when the report is executed. Datasets are of two types: Shared and Embedded. An embedded dataset is private to the report in which it exists and shared datasets can be shared across reports.
17) Would you store your query in a SSRS report or a Database server? State the reason why.
Storing SQL queries directly in text format in the dataset, should be avoided. Ideally it should be stored in a stored procedure in the database server. The benefit is that the SQL would be in a compiled format in a SP and brings all the benefits of using an SP compared to using an ad-hoc query from the report.
18) 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.
19) How would you access SSRS reports deployed on report server?
Reports Manager is the most straight-forward way to access SSRS reports deployed on report server. Apart from executing the reports, it is the main administration console for SSRS server to manage reports.
20) Have you used the Report Viewer control / web part? What are the limitations?
The report viewer control / web part is the basic control to integrate SSRS reports with external applications. Using these interface applications can link and display SSRS reports within the application. The parameters toolbar in these controls have a limited programmable interface to decorate and blend it with the theme of the application.
21) Which is the latest version of SSRS and what are the new enhancements?
SSRS 2008 R2 is the latest RTM version of SSRS. Rich data visualizations, better programming functions for lookup and aggregation, improved performance and better control over exported data are some of the major enhancements.
22) What is Report Builder?
Report Builder is an ad-hoc report authoring tool primarily targeted to be used by business analysts to facilitate self-service report authoring. Report Builder 3.0 is the latest version available as of date.
23) 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 RSScripter can be used for this.
24) What is drill-down and drill-through in SSRS?
Drill-down is a mechanism of decomposing summarized information to a detailed level. Drill-through is a mechanism of decomposing the problem by drilling information generally using more than one report.
1) Do you create your reports using the wizard or manually?
As with most Microsoft tools, SSRS includes a wizard that helps you create reports. The wizard is great for people who are new to the product, but it’s not good if you can’t go beyond the tools and create a report from scratch. Before you go for your interviews, make sure you can create a report from scratch without any wizards or tools to create them for you. Most companies need someone who can customize reports, and wizards leave room for very little customizations.
2) What are query parameters?
Query parameters are the parts of an SQL query that allow you to filter results. Parameters are contained in the SQL’s “where” clause. These parameters tell the SQL server which records you want to update, select, or delete. In other words, if it wasn’t for the where clause parameters, you would affect all records in your tables.
3) What is a sub-report?
Sub-reports are inserted into a main report. Just like a main report, you also pass parameters and queries to it. Think of a sub-report as an extension to your main report, but it contains a different data set. For instance, you could create a report of customers and then use a sub-report to display a list of orders for each customer.
4) What are RDL files?
RDL files are like the “language” for SSRS servers. However, RDL files are created in XML, so really the “language” of SSRS servers is XML. RDL files contain the queries and layout format for your reports.
5) What is a data set?
Data sets are the components that contain your records. You can have a blank data set, a data set with one record, or a data set with millions of records (although millions of records will probably cause performance issues for your users). You can view a data set like a customized table. The data set has the columns and rows like a table, but you determine the columns and number of rows using your SQL statements.
6) What is a data source?
A data source is the database and tables where your data set comes from. You must define the data source when you create your database connection. Every data set needs a source from which to pull the data. Your reporting server can be SQL Server 2000, 2005, 2008 and 2012.
7) What servers can be used with SSRS?
While most companies use SQL Server with SSRS, you can also integrate other database servers with your SSRS reports. SSRS is compatible with Oracle, ODBC and OLEDB connections, Hyperion, Teradata and flat XML files that contain data.
8) What is mixed mode database security?
When you install SQL Server, you have the option to allow SQL Server to integrate with Windows or require users to have a separate SQL Server user name and password. While Windows integration is convenient, it’s not considered the most secure of the two security options. Instead, it’s better to require a separate SQL Server user name and password when logging in to the database server. Your reports will need their own user name and password to run reports from SSRS.
9) Can SSRS reports cache results?
Caching reports makes it much faster for users to access and view data. If you have common data that doesn’t change often, it’s best to cache results. SSRS lets you cache reports on your reporting server. This means that it reduces the load on the SQL Server and your users can quickly access data without querying the server several times.
10) What formats can SSRS export or render to?
SSRS is compatible with several file formats. When the user runs the report, the default format prints to the web browser. You can also save or export to HTML, Excel, CSV, Image, PDF and XML formats. The SSRS reporting software has buttons at the top of each report that the user can use to export these files.
11) How do you deploy a report to a reporting server?
The SSRS reporting software includes tools to deploy directly to the SQL Server reporting server. After you’ve configured your software with the proper SQL Server, user name and password, you just need to right-click the report you want to deploy and click “Deploy.” Make sure you test the report after you deploy it to ensure there are no bugs and that the data returned is correct.
12) How do you edit a report?
Open SQL Server Management Studio and the reporting project. Expand the node that contains your reports. Right-click the report and click “Edit.” You can also save your reporting file from this view.
This is a dozen possible questions you could be asked in an SSRS interview, but it’s best to brush up on the tools and information you’ll need when you’re on the job. SSRS is not tough to learn, but the nervousness of an interview can affect your ability to answer questions. Make sure you practice before the interview.
13) What are the different kinds of SSRS Reports?
Reports can be categorized into operational and analytical reports. The distinction is based on the source of data and level of analysis facilitated by any particular report. Operational reports are based on OLTP sources and are static reports and Analytical reports are based on OLAP sources and generally facilitate drill-down and drill-through for analysis. Technically, SSRS reports can be categorized into parameterized, linked, snapshot, cached, etc...
14) What are parameterized reports? What are cascading parameters in SSRS reports?
Reports that accept parameters from users to fetch and report data conditionally, are known as parameterized reports. When you have multiple parameters in a report and values of different parameters are dependent and populated dynamically based on the value of parent parameters, it's known as a cascading parameter.
15) How would you go about developing a SSRS report?
General development methodology for a SSRS report is to start by creating a data source. Based on the data source create one or multiple datasets as needed for parameters and the body of the report. Add required controls from the toolbox which would act as a container for the fields in the dataset. Format the controls added to the report body. Verify and validate the report and finally deploy the report.
16) What is a dataset and what are the different types of datasets?
A dataset is similar to a query definition, which is executed when the report is executed. Datasets are of two types: Shared and Embedded. An embedded dataset is private to the report in which it exists and shared datasets can be shared across reports.
17) Would you store your query in a SSRS report or a Database server? State the reason why.
Storing SQL queries directly in text format in the dataset, should be avoided. Ideally it should be stored in a stored procedure in the database server. The benefit is that the SQL would be in a compiled format in a SP and brings all the benefits of using an SP compared to using an ad-hoc query from the report.
18) 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.
19) How would you access SSRS reports deployed on report server?
Reports Manager is the most straight-forward way to access SSRS reports deployed on report server. Apart from executing the reports, it is the main administration console for SSRS server to manage reports.
20) Have you used the Report Viewer control / web part? What are the limitations?
The report viewer control / web part is the basic control to integrate SSRS reports with external applications. Using these interface applications can link and display SSRS reports within the application. The parameters toolbar in these controls have a limited programmable interface to decorate and blend it with the theme of the application.
21) Which is the latest version of SSRS and what are the new enhancements?
SSRS 2008 R2 is the latest RTM version of SSRS. Rich data visualizations, better programming functions for lookup and aggregation, improved performance and better control over exported data are some of the major enhancements.
22) What is Report Builder?
Report Builder is an ad-hoc report authoring tool primarily targeted to be used by business analysts to facilitate self-service report authoring. Report Builder 3.0 is the latest version available as of date.
23) 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 RSScripter can be used for this.
24) What is drill-down and drill-through in SSRS?
Drill-down is a mechanism of decomposing summarized information to a detailed level. Drill-through is a mechanism of decomposing the problem by drilling information generally using more than one report.