Interactive Sorting for a SQL Server Reporting Services Report

You created a report for your client and the report default sorting is in descending order which was applied at the dataset level. But what if the client wants to see the data in ascending order? Or what if the client wants to be able to switch back and forth as needed? In this tip we look at how to make the sorting option interactive, so after the report has been rendered the user can changed the sort order.

Check out complete article here.

Dynamically Refresh SQL Server Reporting Services Report Date Parameters

You created a report in SQL Server Reporting Services that has four parameters Year, Month and two Calendar Date Picker parameters StartDate and EndDate. The default values for StartDate and EndDate are determined by theYear and Month parameters. So when a new Month or Year is selected the value for StartDate should be the first day of the Month and the EndDate should be the last day of the Month. When you set default values for StartDateand EndDate the parameter values are correct the first time, but if you change the Month or Year the values forStartDate and EndDate don't change. In this tip we show how to solve this problem.

Check out complete article here.https://www.mssqltips.com/sqlservertip/3560/dynamically-refresh-sql-server-reporting-services-report-date-parameters/

SSRS Report Subscription Important Points to remember.

There are two option to create report subscription in Report Manager.

1- New Subscription
2- Data Driven Subscription


New Subscription 

-Report Subscription will not work with Window Authentication.
-Report Subscription will work with Store Database Credentials.
-SQL Server Agent must be running to create Report Subscription.
-Once Report Subscription is created, a SQL Server Agent will be created to run the report on scheduled time.
-Path to share report will be \\MachineName\Users\UserName\Documents
-Once subscription is created, you can find it in MY Subscription list in Report Manager.
-You can create Subscription Schedule for Once or Monthly, Weekly, Day, Hourly basis.
-If subscription is deleted from Report Manager, SQL Server Agent job to run the report will also be deleted.
-If SMTP connection connection is not created, then you will not get option to send report through email.
-If report has parameter and parameter default value is not set then you can't create subscription. You have to define it at the subscription creation time.


Data Driven Subscription

-Report can be shared via email and windows file sahre.
-Null Delivery Provider, will be used for report caching. In this mode, report will not be rendered but will be stored in the report server temporary database.
-Steps to create Data Driven Subscription
Step 1 - Specify how recipients are notified:, Specify a data source that contains recipient information:
Step 2 - Create connection to source which holds data driven subscription information.
Step 3 - Write SQL query to return FILENAME, PATH, RENDER_FORMAT, WRITEMODE, FILEEXTN, USERNAME, PASSWORD
Step 4 - Specify delivery extension settings for Report Server FileShare, assign values for FILENAME, PATH, RENDER_FORMAT, WRITEMODE, FILEEXTN, USERNAME, PASSWORD
Step 5 - Specify Report Parameters default value
Step 6 - Specify when subscription will be processed

Handle Excel exceeds maximum 65,536 rows in SSRS 2008R2

You have created report in SSRS2008R2 and your report contains more than 65,536 records. When you export the report into Excel you get Export Error message “Exceeding 65,536 rows limit”. How can we resolve this issue?

SQL Server Reporting Services allows you to export data into multiple formats (i.e. Excel, PDF, XML, etc.) and you can find all the supported formats here. SSRS 2008R2 supports the .xls file extension for Excel, but these Excel files support a maximum of 65,536 records per sheet. When you try to export a SSRS report which has more than 65,536 records it will fail to export the report into Excel.

In this tip I will explain the problem with an example and later demonstrate the solution. This tip assumes that you have previous real world work experience building a simple SSRS Report. To demonstrate the solution, I will use theAdventureworks2008R2 sample database for SQL Server and SQL Server 2008R2 Reporting Service.

Read complete article here.

Freeze Excel Column Header for SQL Server Reporting Services Report

You created a SQL Server Reporting Services report and the requirement is to output to Excel format. As you may know, SQL Server Reporting Services (SSRS) provides functionality to export a SSRS report into Excel, but for long reports the column headers disappear in Excel when you scroll through the data. In this tip we look at how to freeze the column headers in Excel when the report is created using SSRS.

This tip assumes that you have previous real world work experience building a simple SQL Server Reporting Services (SSRS) report.

In this tip I will describe how to freeze the Tablix column headings in SSRS, so that when viewing the data in Excel the column names can be seen even after scrolling down. SQL Server Reporting Services doesn't provide any out of box functionality to freeze the column headings when exporting to Excel, so we will show a workaround to do this.

Click here to read complete article.

Execute a SQL Server Reporting Services report from Integration Services Package

You have a requirement where a user does not want to use the SQL Server Reporting Services (SSRS) report subscription service, but wants to execute the SSRS report from a SQL Server Integration Services Package. In this case, whenever the user executes the package, a particular SSRS report will be executed and exported into Excel.  The exported Excel file will be saved in a shared folder. In this tip I will demonstrate how to solve this problem.

This tip assumes that you have previous real world work experience building a simple SSRS Report and SSIS package. I will use AdventureworksDW2008R2 sample database and SQL Server 2012 to demonstrate the problem and solution.

I have divided this tip in two parts.

Part 1: I will create a sample SSRS report and deploy it to the Report Server.
Part 2: I will create a SSIS Package which will execute the SSRS report created in Part1.

Read complete article here.

Display a fixed number of rows per page for an SSRS report

You have a requirement where you need to display a fixed number of rows on each page of an SQL Server Reporting Services (SSRS) report, but there is no out of the box option to limit the number of records displayed per page. How can you limit the numbers of records per page for an SSRS report?

This tip assumes that you have previous experience building a simple SSRS Report.

In this article we will demonstrate how to display a fixed number of rows on each page of an SSRS report using a Tablix data region. I will use the AdventureWorksDW2008R2 sample database and SQL Server 2012 Reporting Services to demonstrate the solution.

I have already created a data source connection to the AdventureWorksDW2008R2 database, now let's create a dataset for the demo report. The below dataset returns Productkey and EnglishProductName.

Read complete article here.

Creating a Tabbed Report in SSRS

Have you ever struggled with creating tabs for a SQL Server Reporting Services report? If yes, then you will most likely know that SQL Server Reporting Services does not provide any built in feature to accomplish this task. Finding a suitable solution can be very tricky. So how can we create a tabbed report in SSRS?

In this tip I will give a demo on how to create a tabbed report in SSRS? To demonstrate the solution, I will use the AdventureworksDW2008R2 sample database for SQL Server and SQL Server 2012 Reporting Service.

This tip assumes that you have previous real world work experience building a simple SSRS Report.

Read complete article here.

SQL Server Reporting Services Text Box Orientation

If a SQL Server Reporting Services report has many columns with text descriptions in a horizontal direction then those headings will need significant space in the report. There are situations where report data will not fit in a single page and the users will have to scroll horizontally to see the data. Unfortunately, this is very irritating and users may ask you to modify the report in such a way that report data can fit in a single page. How can we modify the report and what approach you should follow in this case?

Proper orientation of a Textbox saves a lot of space in a report layout; if a report has 10 columns with text descriptions in a horizontal direction then it will take more space in the report as compared to the same report having those columns in a vertical direction.

Read complete article here.

Export SQL Server Reporting Services Report Data into Multiple Excel with Worksheets

Problem

Sometimes SQL Server Reporting Services users want to export SSRS reports into Excel with multiple sheets, but when the SSRS report data region (Tablix/Matrix) doesn't have any grouping then all the data will be exported into a single sheet. How can we export a SSRS report into Excel with multiple sheets?

Solution

In this tip we will first review the problem and then demonstrate the solution. To demonstrate, I will use theAdventureworksDW2008R2 sample database and SQL Server 2012 Reporting Service.

This tip assumes that you have previous real world work experience building a simple SSRS Report.

Read complete article here.

Display a fixed number of rows per page for an SSRS report

You have a requirement where you need to display a fixed number of rows on each page of an SQL Server Reporting Services (SSRS) report, but there is no out of the box option to limit the number of records displayed per page. How can you limit the numbers of records per page for an SSRS report?

This tip assumes that you have previous experience building a simple SSRS Report.

In this article we will demonstrate how to display a fixed number of rows on each page of an SSRS report using a Tablix data region. I will use the AdventureWorksDW2008R2 sample database and SQL Server 2012 Reporting Services to demonstrate the solution.

I have already created a data source connection to the AdventureWorksDW2008R2 database, now let's create a dataset for the demo report. The below dataset returns Productkey and EnglishProductName.

Click here to read complete article.

Include Report Parameter Selection Values in Report Output for SQL Server Reporting Services

In your SQL Sever Reporting Services report, if you don’t display the parameter values which are used to filter the report data then it will be very difficult to the end users to find the parameter values against which the report was ran. This is especially the case when a report is exported to another format i.e. Excel or PDF. So it is always a good idea to display the parameter values in the report. This tip will demonstrate how to display a single value and multi valued report parameter selection values in SQL Server Reporting Services.

This tip assumes that you have previous real world work experience building a simple SQL Server Reporting Services (SSRS) Report with parameters. I have prepared this article in such a way that an SSRS beginner can also understand the problem and implement the solution. To demonstrate the solution, I will use AdventureworksDW2008R2 sample database for SQL Server and SQL Server 2012 Reporting Services.

Click here to read complete article.

Set Select All as Default for Multi-Value Report Parameters in SQL Server Reporting Services

Most SQL Server Reporting Services (SSRS) reports contain multi-value report parameters and sometimes there be a requirement to set "Select All" as the default. There is not a simple way to set "Select All" as the default for an SSRS report, but in this tip I will demonstrate how to set "Select All" as the default parameter value.

Setting "Select All" as the default parameter value is really helpful when users want to preview the report for all parameter values. Users can preview the report without manually selecting the "Select All" parameter value for each parameter.

This tip assumes that you have previous real world work experience building a simple SQL Server Reporting Services (SSRS) report with parameters. I have prepared this tip in such a way that a SSRS beginner can also understand the problem and implement the solution. To demonstrate the solution, I will use AdventureworksDW2008R2 sample database for SQL Server and SQL Server 2012 Reporting Services.

Click here to read complete article.

Allow NULL value in Multi Value Report Parameter in SQL Server Reporting Services

Your SQL Server Reporting Services (SSRS) report has a multi value parameter, but it doesn't show NULL in the parameter drop down along with the other parameter values. In SSRS a multi-value parameter cannot include a NULLvalue, so users can't filter the data for NULL values. Your requirements state a need to be able to filter the data forNULL values, so in this tip I will demonstrate how to allow NULL values in a multi value SSRS report parameter.

Click here to read complete article.

Alternate Row Background Color in SQL Server Reporting Services Tablix and Matrix

You want to make your SQL Server Reporting Services (SSRS) Tablix's and Matrix's more attractive by configuring an alternative row background color. Unfortunately, you cannot find any out of box properties in SSRS to do this. How do you configure alternative row background colors in a Tablix or Matrix?

The alternative row background color improves the readability of data in your SQL Server Reporting Services (SSRS) Reports. In this tip I will show you how to configure alternative row background colours in a Tablix and Matrix each with one simple example.

Click here to read complete article.

How to change Rendering Extensions in SQL Server Reporting Services ?

SQL Server Reporting Services provides many different rendering extensions. Our users don't want to see the list of all rendering extensions, they only want to see Excel and PDF rendering extensions. How can you enable or disable Rendering Extensions in SQL Server Reporting Services Report Manager?

Check complete article here

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.

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.

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. 

Cascaded Parameters in SQL Server Reporting Services

You have a reporting requirement where a user has two or more parameters for a single report, but the values for one parameter depend on the values chosen in a previous parameter.  

Let's understand with a simple example; suppose you have two parameters: ProductCategory and Product. When you choose the ProductCategory then the Product parameter lists only those Products which belong to the selected ProductCategory. 

How can we create cascaded parameters in SQL Server Reporting Services?

Read complete article here!