SSRS – How to Add Fixed Row Column Header in Report

In this SSRS Tutorial, You will be learning How to Fixed Column header in every new page in SSRS to organise and summarise your data in a better manner. We know that Page Break and Fixed header provides a way of managing large amounts of report. 

After adding Page Break functionality in our report, we will observer that column header is not repeating in every new page. To prevent row and column labels from scrolling out of view after rendering a report, we can freeze the row or column headings.

To fix your row header or column headings in every new page of the report, we need to do some additional settings where we need to go Column Group and choose Advanced Mode as given below:

 After choosing the Advanced Mode, the Row Groups pane displays the hierarchical static and dynamic members for the row groups hierarchy, and the Column groups pane shows a similar display for the column groups hierarchy in the design mode as given below:

Now, we need to click on the top most group and will do some settings in their properties.  

In the properties section, we need to set KeepTogether as True and ReportOnNewPage as True which were set as false by default as given below:

After doing the above setting in the report, preview of the report and the static tablix members remain in view as given below:

Now go to the next page of the report where you see the report table header is visible and also visible in every new page as given below:

So, it is another easy setting in SSRS report to repeat your columns header in every new page with page break functionality. SSRS gives us ability to control whether to repeat row and column headers on every page of a Reporting Services paginated report for a tablix data region (a table, matrix, or list) that spans multiple pages.

To see the video demo, you can visit us at - 

SSRS – How to Add Page Break in Report

In the report, Page Break functionality gives a control on the amount of information on each page and can improve the performance of published reports because only the items on each page have to be processed as we view the report. If we try to show all the information on the same page, it will take a huge amount of time because SSRS engine must need to be processed all items before we can view the report.

To understand this functionality, we are using an example by assuming that we have Regional Sales report and we need to add page break functionality on the report in the starting of the every new Sales Region group as given below:

In the Report Design mode, we have two levels of group as Row Groups and Column Groups as given below:

We need to go in the Row Groups section, we need to right click on the top most group on the report and choose group properties as given below:

It will open Group Properties window where we need to go in the Page Breaks Section and select Between each instance of a group to add a page break between each instance of a group in the table. Optionally, select Also at the start of a group or Also at the end of a group to specify that a page break be added when a group starts or ends in the table.

In this section, we are not choosing any optional options (Also at the start of a group or Also at the end of a group) and just click on the OK button. After doing this, preview of the report as given below:

Now, we can see that page break functionality is working on the report. This is very easy to implement this functionality in SSRS. To see the live demo, you can visit us at -

SSRS - File Share Delivery Subscription

We know that SSRS supports both on-demand (pull) and event-based (push) delivery of reports where event based delivery of reports is known as Report Subscription. We can easily configure the automatic delivery of specified reports by e-mail or to a file share at scheduled intervals. In this tutorial, we will learn how to subscribe a report at shared file location or directory.

Pre-requisite for File Share Subscription

• SQL Server Agent Service should run.
• Service account that is using for file share subscription should have read/write access to shared folder.
• To create the report subscription, we should have "Manage individual subscriptions" or "View a report" permissions.
• Report must have stored the credential security and used embedded connection instead of shared data source reference.
• Users who will use these reports must have read only access to the shared folder.

Steps to implement file share subscription in SSRS 2016

Go to Report Manager URL e.g. (http://<local server>/reports ). Open the report folder or directory and right click on that report which we want to subscribe as given below:

After clicking on the Manage, it will open the Edit <Report Name> page as given below:

To create the subscription, click on the subscriptions on the page as given below:

Now, we can see that there is no subscription on the report. To create a new subscription, click on the +New subscription to lunch the subscription options. Fill the description of the subscription and choose Standard subscription from the Type of subscription as given below:

Scroll down the page and choose Report-specific schedule from Schedule section and click on Edit schedule link button as given below:

After clicking on the Edit schedule, we need to fill the schedule details. In our case, we are using daily report subscription. We are subscribing this report from the start date (begin running this schedule on) to end date (Stop this schedule on) as given below:

After clicking on the Apply button, we need to set the destination. In this section, we are choosing as “Window File Share” as given below:

In the Destination section, for Window File Share, we have to set the shared file path, render format of the report as PDF as given below:

To set the credentials, we are using window user credentials and overwrite options as Overwrite an existing file with a newer version  as given below:

Scroll down the report subscription page to set the report parameters. In this section, we need to set the static parameters to the report as given below:

After doing all the steps, just click on “Create subscription” button and you have created File Share Delivery subscription in SSRS 2016 as given below:

Note: The report is delivered as a static file. If the report includes interactive features (for example, links to additional rows and columns), those features are not available.

SSRS - Report Subscriptions

SQL Server Reporting Services comes with many features to visualize your business data. In term of Report delivery, SSRS supports both on-demand (pull) and event-based (push) delivery of reports where users can view reports in a Web-based format or in e-mail. The event based delivery of reports is known as Report Subscriptions in SQL Reporting Services which provides a way to us to configure the automatic delivery of specified reports by e-mail or to a file share at scheduled intervals.

A report subscription is a report snapshot generated on some defined scheduled time and delivered to the intended audience through the report server. We have two type of subscription in SQL Server Reporting Services as given below-

1. Standard Subscription - This subscription is based on the static values for the parameters and could be created by any user having "Manage individual subscriptions" or "View a report" permissions.

2. Data-driven Subscription - This subscription is based on the dynamic values for the report parameters means report parameters are filled from a query (or a database) at the time of report execution. This subscription requires recipient list, delivery method and parameter values needed for each recipient's report execution. To create this subscription, user must have permissions as "Manage all subscriptions” or Content Manager.

SSRS provides the facility to create multiple subscriptions for a single report to vary the subscription options; for example, we can specify different parameter values to produce three versions of a report, such as a Western region sales report, Eastern region sales, and all sales.

Report delivery is an extensible part of Reporting Services architecture. These subscriptions are processed on the report server and are distributed through delivery extensions that are deployed on the server. 

By default, we can create subscriptions that send reports to a shared folder or to an e-mail address.

For each subscription, we need to configure delivery options and the available options are determined by the delivery extension we choose. A delivery extension is a module that supports some manner of distribution. 

Parts of a subscription

For each standard subscription, there is exactly one set of report presentation options, delivery options, and report parameters. A subscription definition consists of the following parts:

• A pointer to a report that can run unattended (that is, a report that uses stored credentials or no credentials).
• A delivery method (for example, e-mail) and settings for the mode of delivery (such as an e-mail address).
• A rendering extension to present the report in a specific format.
• Conditions for processing the subscription, which is expressed as an event.
• Usually, the conditions for running a report are time-based. For example, you may want to run a particular report every Tuesday at 3:00 P.M. UTC. However, if the report runs as a snapshot, you can specify that the subscription runs whenever the snapshot is refreshed.
• Parameters used when running the report.

In the report subscription, report parameters are optional and are specified only for reports that accept parameter values. This is because a subscription is typically user-owned, the parameter values that are specified vary from subscription to subscription. 

For example, sales managers for different divisions will use parameters that return data for their division. All parameters must have a value explicitly defined, or have a valid default value.
File Share Delivery Subscription in SSRS 2016

References: https://msdn.microsoft.com/en-us/library/ms159762.aspx

SSRS - Subscription can't be created or edited

A Reporting Services subscription is a configuration that delivers a report at a specific time or in response to an event, and in a file format that we specify. Subscriptions can be used to schedule and automate the delivery of a report and with a specific set of report parameter values. 

"Subscriptions cannot be created because the credentials used to run the report are not stored or if a linked report, the link is no longer valid" is the very generic error in SQL Server Reporting Services. This error occurs because SSRS requires a Login to connect to the data source to process the report whenever subscription will be occurring at its scheduled time.

If you are creating your report with the shared datasource and your datasource is built up on the Use Windows Authentication (Integrated security) as given below image then you will not able to create subscription on that report and will face the above error.

Shared Data Source is not allowed in Report Subscription and before creating subscriptions, scheduled report needs to know which credentials we want to run with, when producing the reports.

If you want to create report subscription on the report then I would recommend you to store the credential security into report level and use embedded connection instead of shared data source reference. To sort out this issue, we are doing the following settings step by steps.

1.In development studio, right click on the Data Sources and choose Data Source Properties from report data as given below:

2.In Data Source Properties, checked Embedded connections, Type Microsoft SQL Server and click on Edit button as given below:

3.After click on Edit button, it will open Connection Properties window where you need to fill Server name, checked Use SQL Server Authentication and fill user credentials, select or enter a database name and click on test connection. If Test result alert shows succeeded then click on OK button as given below:

4.Click on OK button on Connection Properties window. In Data Source Properties window’s Credentials tab, you can see that your report connection is set on Use this user name and password as given below:

5.Click on OK button and deploy your report on the report server again and open the data source of the report and set custom data source as given below:

 

6. After testing the connection string, click on Save button. Choose the Subscription, where you can see the enabled New Subscription.

After following the all the above steps, you are able to create "New subscription" or "New Data-driven Subscription" with the help of report manager.

What is MAXRECURSION hint in Recursive CTE?

Common Table Expression (CTE) provides a mechanism to write easy to understand, more readable and maintainable recursive queries. We can reference them as a temporary result set which is defined within the execution scope of a single SQL statement. If you are working on recursive queries with CTE feature of SQL Server then you will get the following error if you are not using MAXRECURSION query hint at the end of the query–

Msg 530, Level 16, State 1, Line 1 The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

If the MAXRECURSION query hint is not specified, the default limit is 100. When the specified or default number for MAXRECURSION limit is reached during query execution, the query is ended and an error is returned.

What is MAXRECURSION hint in Recursive CTE?

MAXRECURSION hint allows us to specify how often the CTE can recur before generating an error. MAXRECURSION hint is very helpful in a scenario where poorly written CTE is resulting in an infinite recursion level; in such cases MAXRECURSION terminates the CTE once the defined recursion crosses. MAXRECURSION 0 allows infinite recursion.
While creating VIEW do not use this clause but whenever you are executing the VIEW use it there instead.

Just understand MAXRECURSION hint

To understand the worth of MAXRECURSION hint, we are taking a recursive example for employee data which is just a sample to avoid max recursion error. In this query, we have to use hint (MAXRECURSION 200) or hint (MAXRECURSION 0) and see the behavior of the CTEs queries.

A)   Hint (MAXRECURSION 200)

In the below example, MAXRECURSION hint value is 200 which terminates the CTE execution once it reaches the recursion level of 200 and terminates the statement with the error –

;WITH CTE AS (  SELECT EMPID=101, EMPNAME='ABC_101'  UNION ALL  SELECT EMPID=EMPID+1, EMPNAME='ABC_'+CAST(EMPID+1 AS VARCHAR(3))  FROM CTE  WHERE EMPID<1000 ) SELECT * FROM CTE OPTION (MAXRECURSION 200);

B)    Hint (MAXRECURSION 0)

If we have to  come across a scenario, where we need to have recursion level till the end of the recursive condition then we can achieve this by specifying MAXRECURSION value as 0 means no limit to the recursion level as given below- 

;WITH CTE AS (  SELECT EMPID=101, EMPNAME='ABC_101'  UNION ALL  SELECT EMPID=EMPID+1, EMPNAME='ABC_'+CAST(EMPID+1 AS VARCHAR(3))  FROM CTE  WHERE EMPID<1000 ) SELECT * FROM CTE OPTION (MAXRECURSION 0);

Conclusion

The MAXRECURSION query hint specifies the maximum number of recursions allowed for a query. The number of recursions is a non-negative integer between 0 and 32,767. When 0 is specified, no limit is applied.

SSRS - RunningValue Function

RunningValue function is very useful feature in SSRS report which is used to pull a running aggregate of all non-null numeric values specified by the expression, evaluated for the given scope. This function takes three input parameters as given below:

Syntax RunningValue(expression, function, scope)  This function based on three parameters such as Expression - The expression on which to perform the aggregation, for example, [Quantity]. Function - (Enum) The name of the aggregate function to apply to the expression, for example, Sum. This function cannot be RunningValue, RowNumber, or Aggregate. Scope - (String) A string constant that is the name of a dataset, data region, or group, or null (Nothing in Visual Basic), that specifies the context in which to evaluate the aggregation. Nothing specifies the outermost context, usually the report dataset. Return Type - Determined by the aggregate function that is specified in the function parameter.

The most interesting thing is that the value for RunningValue resets to 0 for each new instance of the scope. 

If a group is specified, the running value is reset when the group expression changes.

If a data region is specified, the running value is reset for each new instance of the data region. 

If a dataset is specified, the running value is not reset throughout the entire dataset.

Limitation of RunningValue function

There is few limitation of this function which is given below:

1. We cannot use RunningValue for the filter or sort expression.
2. The set of data for which the running value is calculated must have the same data type. To convert data that has multiple numeric data types to the same data type, use conversion functions like CInt, CDbl or CDec.
3. Scope parameter cannot be an expression.
4. Expression parameter can contain calls to nested aggregate functions with the following exceptions and conditions:
5. Scope for nested aggregates must be the same as, or contained by, the scope of the outer aggregate. For all distinct scopes in the expression, one scope must be in a child relationship to all other scopes.
6. Scope for nested aggregates cannot be the name of a dataset.
7. Expression must not contain First, Last, Previous, or RunningValue functions.
8. Expression must not contain nested aggregates that specify recursive.

How does RunningValue Function Work?

To understand this function, we are taking an example of Employee Salary where we should need to calculate the cumulative salary or running salary across the report. First of all connect your report with your data source and create a dataset as given below:

Select 101 as EmpId, 'Ryan Arjun' as EmpName, 25000 as Salary UNION Select 102 as EmpId, 'Kimmy Wang' as EmpName, 15000 as Salary UNION Select 103 as EmpId, 'Black Smith' as EmpName, 22000 as Salary UNION Select 104 as EmpId, 'Rosy Gray' as EmpName, 12000 as Salary EmpId EmpName Salary 101 Ryan Arjun 25000 102 Kimmy Wang 15000 103 Black Smith 22000 104 Rosy Gray 12000

In the report, we have the report dataset called dsEmployee as given below:

Now just set the DataSetName as dsEmployee to your report as given below:

After adding the dataset your report and setting the column names to your report, just preview your report which will be look like as given below:

Now, we need to add the running salary to the report. For this, just add a new column to your report and named as Running Salary as given below:

After adding new column your report, just right click in the detail column and choose expression as given below:

After choosing the Textbox Expression, it will launch the Expression window. In the Expression window, we must add the RunningValue function with the requested parameters such as Expression, function type and scope of the function as given below:

After doing the needful settings, click OK button and preview the report as given below:

We can add the average running salary in the report as given below:

= RunningValue(Fields!Salary.Value, Avg, Nothing)

Conclusion

This function is very useful to calculate on the fly calculation such running sales with the month, within the year or within the product specific. It gives us to facilitate to use the aggregate functions to calculate the different type running aggregation values. It has some limitation such as sorting and filtering data. 
For live demo - please visit us at -