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.
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 -
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.
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
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.
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 (MAXRECURSION200)
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 –
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-
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.
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:
We cannot use RunningValue for the filter or
sort expression.
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.
Scope parameter cannot be an expression.
Expression parameter can contain calls to nested
aggregate functions with the following exceptions and conditions:
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.
Scope for nested aggregates cannot be the name
of a dataset.
Expression must not contain First, Last,
Previous, or RunningValue functions.
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 -