When I started developing with Reporting Services I was confused when I would like to make Tablix header repeating on each page. I tried to manage it through Tablix properties and Row headers or Column headers section. But as states on MSDN in case of static groups in Tablix you should use another approach then in case of non-static members. Since I was working mainly with Tablix component, I worked with static members. So, I had to apply approach described in this post. For this scenario I will use report from post Grouping Tablix.
Picture 1 Grouped Sales Report
Open the report and in design mode select Tablix, right click and select Tablix Properties from popup menu. In the dialog box you can see menu saying Repeat header rows on each page, Keep header visible while scrolling and the same menu for columns. When you check this checked-boxes in Row Headers or Columns Headers section and run the report again nothing happened. This setting would work in case of Matrix component. Because there is no need to have static groups. In case of Tablix report, headers are static groups. So, let’s close properties window and click on the Tablix again.
Picture 2 Tablix Properties
At the bottom of designer or studio surface, in the Grouping pane, there is an arrow, on the right side and by clicking on it you can enable Advanced Mode (picture below). After enabling this feature you can see static groups in Grouping pane. When you click for example on first static group Row Groups panel you can see that Last Name text box on the left-top corner of the Tablix is selected.
Picture 3 Grouping Pane
Having Static Last Name group selected, you can see its properties in the Property pane (picture below). We need to set FixedData, KeepWithGroup and RepeatOnNewPage attribute as on picture below. FixedData = True, KeepWithGroup = After, RepeatOnNewPage=True.
Picture 4 Properties Pane
Run report again and you can see header repeating on each page and staying on top when scrolling the report page. And that’s it. In next posts I will try on repeating groups in more details.
In this post I will explain how to easily set grouping in report Tablix. We will extend our report from post Edit report with Report Builder. If you would like to download the report go to the post page. We will add region name and province name groups to our report, so it will look like on picture bellow.
Short overview
Extend dataset – add new attributes grouping the data
Add parent group – English Country Region Name
Add child group – State province Name
Picture 1 Sales report
Let’s modify report dataset first. Go to Report data pane, under Datasets item right click on dataset you would like to modify and select Dataset Properties from popup menu.
Picture 2 Report data – Datasets
Add attributes from DimGeography dimension. City, StateProvinceName and EnglishCountryRegionName. You can edit query directly in Query text area or using Query Designer.
Picture 3 Query
After editing query, by click on Refresh Fields button, we can check new attributes in report dataset.
Picture 4 Dataset
There are two possibilities how to add new group to the report Tablix.
Through Grouping Pane
Directly in Tablix design
At first, we will add new group through Grouping Pane. Let’s check if we Group Pane is visible in design mode bellow. On the top of the menu click on View and check if Grouping checkbox is checked.
Picture 5 View menu
In Grouping Pane -> Row Groups you can see current report Tablix group. We will group our report by EnglishCountryRegoinName
attribute we added by extending our dataset. Right click on LastName group in Row Groups panel and Add Group from popup menu. Because we would like to group our detail data, we select Parent Group option.
Picture 6 Grouping Pane
New dialog box appears, we select EnglishCountryRegionName attribute. After Ok button, new column is added on the left side of the Tablix. Grouping direction is from left to->right, which means parent-> child relationship. That’s the reason why new column is added to the left.
Picture 7 Tablix group
Picture bellow shows the second approach, adding group directly from Tablix area. Right click on textbox in Tablix -> Add Group -> Parent Group from popup menu.
Picture 8 Add group
Let’s look at the report now. We can see that data are grouped by region name. But it would be useful to have running totals on the group level, to see how much Sales amount per region was profited.
Picture 9 Sales report
When we return to step when we select grouping attribute, there is a possibility to add group header or group footer. In our case we would like to have totals above detail data, so we check Add group header. In case we would like to have data summarized below grouped data, we choose Add group footer.
Picture 10 Tablix group
Bellow we can see final report matrix design.
Picture 11 Sales report
There is another approach how to add Group Totals. We can do it directly from Tablix area. Let’s go back when we have added new group by Group Totals is missing. Right click on Tablix Row -> Insert row from popup menu and select Outside Group – above (if we would like to have totals above grouped data). Before we invoke Popup menu, it is important to choose the inner group area we would like to summarize in Tablix. In our case it is detail group, Lastname group. We achieve it by clicking on Last Name column since it is column inside LastName group. In case we choose Inside Group option we would extend current group and add rows to detail data.
Picture 12 Tablix, Insert Row
Look on picture bellow how the grouping looks from the design perspective.
Picture 13 Tablix
Let’s add another group based on StateProvinceName attribute. We would like to place this new group between EnglishCountryRegionName group and Lastname group to create group hierarchy: region name -> province name -> data.
In Grouping Pane -> Row Groups, we can see our new parent group EnglishCountryRegionName. Reporting services set the group name based on attribute name by default. If we would like to change the name, we can manage it trough Group Properties.
Right click on EnglishCountryRegionName -> Add Group -> Child Group, do not forget to focus mouse cursor on parent group. Other option is to select LastName group and add new group as Parent Group.
Picture 14 Add Group
On picture bellow we see Tablix with new group in design mode.
Picture 15 Tablix
We would like to have subtotals in this group too, so let’s insert new row outside the group. Follow the same steps like with EnglishCountryRegionName group.
Picture 16 Insert Row
.And that’s it. Picture bellow shows final group hierarchy in group panel.
Picture 17 Grouping Pane, Row Groups
Picture bellow shows report matrix in design mode.
Picture 18 Tablix, Grouping regions
Here is the final report matrix design with grouping.
Picture 19 Sales report, grouping
If you follow steps in this post, you should have similar report. You can download it ReportSalesGrouping.
I write about Reporting Services which was installed in native mode. The second one, SharePoint mode will be mentioned in future posts. When you perform simple installation as local instance on your serve or on your computer, you should be aware of 2 importing things to start your work with Reporting Services.
Is my reporting instance running?
On which URL address can I access published reports?
There is other stuff to check, but I think these two are the most important. There is configuration tool named Reporting Services Configuration Manager, where you find basic info regarding Reporting Services configuration such name and destination of Reporting Services database, service account under which is reporting service running, very useful info regarding report manager and report server URL (I never can’t remember) etc.
Let’s check that Reporting Service is already running
Click on Reporting Services Configuration Manager, you find it obviously in location: Start Menu\Programs\Microsoft SQL Server\Configuration Tools. When Reporting Services Configuration Manager appears, there are menu items on the left side, linked to correspondent sections you need to configure. There is Server instance item at the top of the menu, by click on it you get Report Server status and basic information. Name and ID of the instance, information regarding edition and product version. Name of configured Report Server Database, weather instance is running in native or SharePoint mode and finally report service status.
Here is the point you can easily check if your report server is running or not. You can start or stop it from this window too. Other approach to handle Reporting Services is from \Programs\Administrative Tools\Services.
Picture 1 report server status
Where can I find my published reports
In Report Manager URL, there is an URL address to Report Manager web application defined. Do not mix Report manager URL with Report Server URL. Report Manager is web application through which you can visually manage reports, data sources, subscriptions, users’ access, etc. Report Manager is visual web interface for report publishing management. In comparation with Report Server URL, this application has user interface you can manage mentioned operations. The URL is obviously in following shape: <Server Name>/<Reports>. You can set URL through installation or you can change it later in the Configuration Manager. You can also click on Advanced option to change port of Report Manger service, 80 is set by default.
Picture 2 Report Manager URL
Under which service is my reporting services running
In the Service Account section, you can configure account under which will Report Server service run. You can select whether to use Local, Network, System or Domain account. Since 2017 you can also use Virtual account which is recommended if you don’t have Domain user account. Primary it is recommended to use domain account instead of the Network service account from security reasons. Local accounts have some limitations you have to considered when you are configuring report server. Generally, you should consider whether your Report server connects to remote Report Server database or you will use subscription, or you will access files as data sources placed somewhere on network, etc.
Picture 3 Service Account
Report Server
Report Server is the core of Reporting Services. It consists of several components handling processing, rendering and publishing of reports and possibility to implement extensions in authentication, data processing, report rendering and publishing. It does not have user-interface, but it allows you to access mentioned components programmatically. You can list the report repository with your internet explorer. The default URL of Report Server is follows: <Server name>/<ReportServer>, 80 port by default. If you would like to access web service methods of Report Server you can get them by its web service end-points. You can connect it through web-service URL in shape: <Server name>/<ReportServer/ReportServiceXXXX.asmx (Server/ReportServer/ReportService2005.asmx). In next posts I will write regarding this feature in more detail since it could automate work for administrators.
Picture 4 Web Service URL
Report Server Database
Report Server Database consists of reports metadata, report repository data, report objects, log data regarding report processing, users access etc. There are two databases installed, one for temporary data and primary one as the main data storage. Due to installation you can leave default settings of Report Server databases which are installed locally, or you can choose manual installation and select different Report Server databases destination, for example on remote machine. Here in the Configuration Manager you can change the database after Reporting Services installation. There is also a possibility to set credentials used by Report Server to connect to the report server database.
Picture 5 Report Server Database
Scale-out Deployment
This Configuration Manager option will be described in very short. I will try to come to describe them in later posts since they are not so important for the basic installation.
Scale-out Deployment gives you possibility to share one Report Server Database with more Report Servers or to implement solution with load balancer. The detail info is described on the Microsoft website: https://bit.ly/30eFHTY
Picture 6 Scale-out Deployment
Encryption Keys management
Report Server Database stores sensitive data which are encrypted by a symmetric key. You should create and backup encryption key immediately after installation of Reporting Services is done as it is important part in case you would reuse Report Server Database. In one of my future post I will go through Report Server Database recovery and this functionality will be described in more detail. There are other scenarios such creating new installation of Reporting Services with existing Report Server Database, moving instances of Reporting Services, renaming the instance where hosting Report Server, resetting password for account Reporting Services is running etc. You can find more info on website https://bit.ly/2w6EJeJ.
Picture 7 Encryption Keys
E-mail Settings
Reporting Services supports report distribution by email. It can be configured in two ways. In the Report Server configuration file RSReportServer.config you find in your installation directory of Reporting Services. This will be described in one of my future posts. In this file you can set advanced email delivery parameters. The fundamental step to make this feature functional is to go to Configuration Manager, Email Settings section, set your SMPT server address and email account sending email from the SMTP server. As on Microsoft website states The Report Server service account must have permission on the SMTP server to send mail. You can find More info on https://bit.ly/2OO0iZl. I will pay attention to Report Server subscription using email delivery in separate post.
