Lorem ipsum dolor sit amet:

24h / 365days

We offer support for our customers

Mon - Fri 8:00am - 5:00pm (GMT +1)

Get in touch

Cybersteel Inc.
376-293 City Road, Suite 600
San Francisco, CA 94102

Have any questions?
+44 1234 567 890

Drop us a line

About us

Lorem ipsum dolor sit amet, consectetuer adipiscing elit.

Aenean commodo ligula eget dolor. Aenean massa. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Donec quam felis, ultricies nec.

In this post we give a short introduction into the new Pivot capabilities of ReportServer’s dynamic list which we added with ReportServer 2.2.

1With ReportServer 2.2 the dynamic list got a powerful new feature: a dynamic list can be transformed into a Pivot table. This gives completely new possibilities for ad-hoc reporting and adds the capability of easily generating beautiful charts, which can then, for example, be pushed to the dashboard. In this blog post I will walk you through the generation of a simple Pivot table and accompanying chart. We want to emphasize that we use the fantastic open source project Saiku which we also included in ReportServer for the use with OLAP (Mondrian) reports. Thanks for the great work team Saiku.

Pivot mode of Dynamic Lists

For this tutorial we are using the ReportServer demo data and the predefined report T_AGG_CUSTOMER containing customer information of an imaginary toy manufacturer. Our goal is to create a report that gives an overview of the volume of sales generated by the company’s employees grouped by office, city and country. If you have the demo data installed, you will find the report in the administration module in the folder “dynamic lists”. If you do not have the demo data installed, the report can easily be created by hand. Simply create a dynamic list with the demo data source as datasource and use the following query:


For more information on how to install the demo data have a look here or at the configuration guide.

Next, we are going to generate the report step by step. After opening the empty dynamic list, the first step is to activate the Pivot mode. For this, use the Pivot button from the toolbar.

ReportServer will ask you to confirm the switch to the Pivot mode which should trigger a change in presentation. You should still see the tabs computed columns and prefilter but now have the additional tabs dimensions/measures and pivot/preview. Before you can use the Pivot functionality you need to specify which of the attributes you want to use as dimensions and which attributes you want to use as measures. For this, click on “Select dimension/measures” from the toolbar in the dimensions/measures tab and select the attributes

The underlying table contains one row for each customer and in particular contains information on the volume of the customer. That is, we have information on the volume of sales generated by that customer, wich employee is assigned to the customer and the employee’s office location (city and country). We will use the attributes OFF_COUNTRY, OFF_CITY and EMP_LASTNAME as a combined dimension. For this we assign all three attributes the same dimension which we call Employee. The attribute VOLUME is used as a measure and we assign the aggregator “Sum” as we want to display the summed volume of sales an employee is responsible for.

Having defined dimensions and measures we can switch to the Pivot/Preview tab. Note that, if you make changes to dimensions or measures, you need to reload the Pivot view. For this you can use the green circled arrow from the Pivot view toolbar.
The pivot view is structured into three parts. On the left you have your dimensions and measures and on the right is the display area. We want to display employees by their generated volume of sales. For this we drag the All part from the Employee’s dimension on the rows field and the single measure Volume on the Columns field. The view should update to show you a single value: The total volume of sales by all employees.
You can now drill down to display more information. For this, click on “All Employees” and, for example, select Show Children from the context menu. This results in one extra level of detail, that is, the volume is now grouped by country. In the following screenshot I have drilled down to the level of employees within the Paris office but kept to a level of City for all other offices.
This report can now be exported to, for example, Excel or PDF or saved as variant and scheduled similarly to any other report within ReportServer.

Creating Charts

One cool thing about Pivot reports is the ability to generate beautiful charts. In the following example we want to display the volume of sales by country. We thus keep the Volume measure on columns and only put the Office Country dimension onto Rows.
Now, if you look to the right of the report, you see several icons. The top right icon switches the view into chart view and you can choose from a number of different chart types. In this case I have chosen a simple donut representation.
Charts such as the above can also be easily integrated into ReportServer dashboards. For this, store the above report as a variant. I used the name “Volume_by_Country”. To integrate it into a dashboard, simply create a new report dadget.
All that remains is to configure the dadget to point to the just created report variant, in my case “Volume_by_Country” and select a chart type as presentation type.



InfoFabrik GmbH

Wir wollen, dass alle Unternehmen, Institutionen und Organisationen, die Daten auswerten, selbständig und zeitnah genau die Informationen erhalten, die sie für ein erfolgreiches Arbeiten benötigen.

InfoFabrik GmbH
Klingholzstr. 7
65189 Wiesbaden

+49 (0) 611 580 66 25

Kontaktieren Sie uns

Bitte addieren Sie 2 und 5.
Copyright 2007 - 2024 InfoFabrik GmbH. All Rights Reserved.

Auf unserer Website setzen wir Cookies und andere Technologien ein. Während einige davon essenziell sind, dienen andere dazu, die Website zu verbessern und den Erfolg unserer Kampagnen zu bewerten. Bei der Nutzung unserer Website werden Daten verarbeitet, um Anzeigen und Inhalte zu messen. Weitere Informationen dazu finden Sie in unserer Datenschutzerklärung. Sie haben jederzeit die Möglichkeit, Ihre Einstellungen anzupassen oder zu widerrufen.

Datenschutzerklärung Impressum
You are using an outdated browser. The website may not be displayed correctly. Close