Thursday, March 26, 2015

Creating a multi-option parameter report for SQL Server Reporting Services

I have a reporting requirement where users want to enter report parameters by either selecting values from a dropdown list or entering To and From range values.  How can I do this in a Reporting Services report?


This tip assumes that you have experience building a simple SSRS report and T-SQL skills. In my previous tips I have explained optional query parameters and cascaded report parameters in SSRS, it is recommended to read these tips as well.

In SSRS we can't enable or disable report parameters based on other parameter values. If we have a report parameter, then we have to provide a value. SSRS doesn't provide any functionality to choose query parameters from report parameters, but we have a workaround.

Check out complete article here.

Saturday, March 7, 2015

SQL Server Reporting Services Interview questions

In this article I have tried to cover all important questions generally asked in the interview. Please give your valuable suggestions and feedback to improve this article.I will write the answers for each question in future.

1.       What is SSRS?
2.       What is the architecture of Reporting Services?
3.       How can you find which report is running slow?
4.       What is Data Driven Subscription?
5.       When to use Null Data Driven Subscription?
6.       What are the components of SQL Server Reporting Services?
7.       What is encryption key and what is its extension?
8.       What are the types of parameter in SSRS?
9.       What is the file extension of SSRS report and what is it stands for?
10.   Which language Report files made of?
11.   Can you create subscription using windows authentication?
12.   What is the report rendering?
13.   What are the different types of report rendering methods?
14.   What do you need to restore report server database on another machine?
15.   When storing the credentials of a data source in the server, are those credentials safe?
16.   What happens if you do not specify a parameter value in a subscription and the parameter does not have a default value? 
17.   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? 
18.   What caching options do you have for reports on report server?
19.   What is report caching?
20.   What is report snapshot?
21.   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? 
22.   What is report server?
23.   What is report Manager?
24.   What are the three SSRS command-line utilities and their primary functions?
25.   What permission do you need to give to users to enable them to use Report Builder?
26.   What is data source in SSRS?
27.   What is Dataset in SSRS?
28.   What are the different types of data sources in SSRS?
29.   What are the different types of Datasets?
30.   When to use shared Data Source and Shared Dataset?
31.   Why should you not overwrite a shared data source in production?
32.   What is the main benefit of using embedded code in a report?
33.   What programming language would you use to create embedded functions in SSRS? 
34.   How do you reference an embedded function in a report expression?
35.   What are different types of roles provided by SSRS?
36.   Can a user or group belong to more than one item-level or system-level role? 
37.   What is a Report Model?
38.   In which SQL Server version report builder introduced?
39.   What is the latest version of report builder?
40.   What are the issues when exporting SSRS reports into Microsoft Excel?
41.   What new data source types were added in SSRS 2014?
42.   What are the new features are introduced in SQL Server 2012 reporting services?   
43.   How do you display the Image Properties dialog box? 
44.   What are data regions?
45.   Can you use the Report Wizard to create a report with Charts?
46.   What is the difference between Logical Page and Physical Page in SSRS?
47.   Which rendering formats are affected by the PageSize properties?
48.   Can you use a stored procedure to provide data to an SSRS report?
49.   What is the main difference between a Matrix report item and a Table report item? 
50.   When report caching is not used then which is better to use parameters to filter information in the query or to use filters in the dataset?
51.   Can you edit a report that an end user created by using Report Builder in BIDS?
52.   What mechanisms do you identify to reduce the overhead of Reporting Services data sources?
53.   How can you edit the .rdl code associated with a linked report?
54.   What are sub reports and how to create them in SSRS?
55.   How to create alternate row color?
56.   How to display fixed number of row per page in SSRS?
57.   You want your report to display a hyperlink that will take users to your intranet. How do you configure such a hyperlink?
58.   Which functions are used to pass single parameter value and multi parameter value to MDX query?
59.   How to call text-box value of report table in header or footer?
60.   How will I enable the column header should repeat on each page?
61.   How to use Calendar Date Picker for MDX Based reports?
62.   How to create optional query parameter?
63.   Explain Reporting Life Cycle?
64.   How to fine tune SSRS report?
65.   Name and Describe few console utilities for SSRS?
66.   Which version of Reporting Services doesn’t support report model?
67.   Can we join two datasets in SSRS?

68.   When do we get parameter forward dependencies error?

You can find SQL Server Interview Questions here.


Monday, March 2, 2015

How to use a multi valued comma delimited input parameter for an SSRS report

You have a requirement where users want to filter report data in SQL Server Reporting Services by entering a list of comma delimited values instead of choosing from a drop down list.  In this tip I will show how this can be completed with Reporting Services.

In this tip we will show how to pass multiple values from a user input report parameter to a query parameter in SQL Server Reporting Services. I will use the AdventureWorksDW2008R2 sample database for SQL Server and SQL Server 2012 Reporting Services to demonstrate the solution.

Click here to read article.