The Dynamic List – an Introduction
ReportServer comes with several reporting engines one being the Dynamic List. While the Dynamic List certainly doesn’t have a flashy name, don’t let that fool you. In simple terms the Dynamic List is The Tool for relational reporting, empowering your users to get the data they need when they need it while at the same time taking the strain from IT as there is hardly any reporting engine out there that is easier to administrate and maintain than Dynamic Lists.
This introduction is the first part of a series of blog posts that look at the Dynamic Lists in greater detail. So far the following posts are available in this series:
The following is probably a familiar story. Bob from customer relations goes to Alice telling her that he needs a list of all the customers that have ordered a classic car model (Bob and Alice work for a small toy manufacturer) within the last two weeks. Alice takes some notes to remember what exactly Bob wants and then goes back to her current task. A week or so later Alice finds the notes and decides to start working on the report for Bob. She fires up her Report Designer and starts fiddling with all the knobs and levers. A few days later, she goes to Bob showing him a first draft of the report. Looking at the result Bob says: “thanks, but actually, what I would now need is not those customers that bought classic car models, but vintage car models. And didn’t I tell you that I only need customers from Canada? Must have forgotten, sorry.”. So Alice goes back, but of course, there are also other tasks to do and until finally Bob gets the data he needed weeks ago, a long time has passed.
Had they had ReportServer and the Dynamic List, the story would have been quite different. Let’s suppose for a moment that Bob is a new employee since, if he’d been around for a while he wouldn’t have thought about going to Alice for this report in the first place. But as he is new, and in his old company IT was doing all the reports he goes to Alice and asks her for the list of customers that have ordered a classic car model within the last two weeks. Instead of taking down the requirements, Alice would have responded with: “Hi Bob, let me show you how you can get the data yourself. Login to ReportServer and open the Dynamic List called T_AGG_ORDERS. This contains everything you would want to know about any individual order that has ever been placed with us. To get the customer data, select the following customer attributes. Now we only need to place a filter on the order item to find only those that bought classic car models, put a second filter on the order date to select the last two weeks, and .. that is it. You can also save that report for later use.”. Bob is happy, because he’d never dreamed of getting his data so quickly or that he could have even done it himself and Alice is happy, because she can concentrate on the important IT tasks again.
The Dynamic List
The Dynamic List is ReportServer’s ad-hoc reporting engine and the right choice whenever the focus is on the data and not on the pixel perfect looks. The term Ad-hoc, however, doesn’t quite give the Dynamic List justice, as it allows to create very complex reports. A more fitting description of the Dynamic List might be self-service engine focussing on the fact that users can generate their reports on their own without additional IT support.
Of course, the very first steps in the life of Dynamic Lists also need to be done by IT (i.e., a ReportServer administrator). So let us take a look at what is necessary to configure a Dynamic List. The first step to a Dynamic List is to to have a datasource, usually a datasource pointing to a relational database. When you have installed the demo data then you should find a preconfigured datasource in the datasource manager within the admin module.
The demo datasource is an H2 database with username demo and password demo and which contains demo for our imaginary toy manufacturer. The next step is to create a new Dynamic List report in the Report manager. For this we go to the Reports module within Administration and right click on the folder into which we want to place our new Dynamic List and choose insert > Dynamic List.
Now, we are almost there. To complete the configuration we should give the new report a name, for example, “Order List” and tell it to use our “Demo Data” datasource (we leave the Metadata Datasource empty for the moment and discuss this in a separate blog post). Once we have selected the datasource ReportServer will show an additional text field in which we need to write a SQL query. This query defines the report’s data universe or in simpler terms the base table the report is using. In our example, Bob asked for a list of customers that have ordered a specific item within the last two weeks. To answer this query, one would need to look at all orders from the last two weeks. The demo data (a description of the available tables is given in the Administration Guide) contains degenerate aggregate tables for the various entities of the toy manufacturer, and also one for orders called T_AGG_ORDER. (We’ll have an in-depth discussion of how to optimize your data warehouse for Dynamic Lists in a separate blog post.) For now, simply consider that T_AGG_ORDER is a huge table that contains an order on every row with all available information to that specific order as attributes. Here is an excerpt from the data (yes .. it is created with a Dynamic List).
So in order to let our Dynamic List use the T_AGG_ORDER table as its base table we simply use the following SQL Query
SELECT * FROM T_AGG_ORDERAnd that is all that needs to be configured. The final configuration of our Dynamic List looks as follows:
Using Dynamic Lists
Now that we have a Dynamic List, how do users use it? Users do usually not get access to the Administration module but access reports via the TeamSpace module. Bob, being part of the Customer Relations team thus has access to the Customer Relations TeamSpace and we assume that an IT person (or Bobs TeamSpace manager) imported the Base Dynamic List “Order List” into the TeamSpace. When Bob accesses the TeamSpace it could thus look as follows (well, usually there would be more reports, but here it is just the one).
To execute the Dynamic List, Bob double clicks the item which opens the report execution view. In case of a Dynamic List this usually opens the “Configure list” perspective which is empty as Bob opened a fresh and not further configured Dynamic List.
The first step, when configuring a Dynamic List is to configure the columns of the resulting report. For this Bob clicks on Select columns from the toolbar which opens a selection popup that on the left hand side lists all the available columns for that particular Dynamic List (i.e., all the available attributes/columns in T_AGG_ORDER). Bob is interested in customers that have bought a particular product, so he selects the attributes CUS_CUSTOMERNUMBER, CUS_CUSTOMERNAME and PRO_PRODUCTLINE. (As you can see the Default Alias and Description fields are empty and we see in a later post why that is and how to add additional information to each of the attributes.)
Once Bob confirms his selection the three columns are listed in the Configure list view and Bob can now further configure the properties of these attributes.
But first, Bob wants to get an idea of the data and thus clicks on Preview which loads the first 50 data records of the currently configured list.
Bob was interested only in those customers that have bought a particular item, namely a classic car model. To filter the list Bob goes back to the Configure list view and double clicks on the PRO_PRODUCTLINE attribute to open the filter perspective for that attribute. The filter view allows to choose between filtering by including values (or ranges) or excluding values (or ranges). Bob only wants those records that contain Classic Cars so he creates an Include filter for it.
Confirming his selection and going back to preview now shows a filtered view of the data where only those rows are present that contain Classic Cars.
The resulting report now, however, may contain a customer multiple times (in case a customer bought more than one Classic Car) and furthermore it would be nice if the list was sorted. For this, Bob goes back to the Configure list view, defines that the list should be sorted by name and further checks the distinct filter from the toolbar.
Bob now has exactly the data that he wanted.
(Ok, not quite true, since we did not yet filter the data to only contain data from the last two weeks. While simple date filters can be placed similarly to the Classic Car filter, ReportServer also supports advanced filters such as “this months, data” or “the last two weeks”. We discuss date filters in a separate post.)
The final report can now, for example, be exported to Excel, PDF or HTML. Here is how the HTML output would look like (note that the output can be themed in ReportServer Enterprise).
Now, Bob is happy since he has the data that he needs. However, he might need the same report again in a month but with up-to date data. He thus decides to store his configuration as a report variant. For this he hits the Save button from the toolbar (this button is only available if Bob has at least the “User role” within the TeamSpace) and chooses the name “Customers of Classic Cars”.
The new variant was stored in the Customer Relations TeamSpace and when Bob again opens the TeamSpace he now sees the base Dynamic List “Order List” and his variant “Customers of Classic Cars”. When selecting the variant the Details tab on the right hand side provides a detailed description of the configuration telling the viewer, for example, that the report consists of three columns and that there is a filter on the PRO_PRODUCTLINE column.
It is important to note that what was stored is not the data but only the metadata. That is, what is stored is the information about the configuration of the report (three columns, a filter, some sorting etc.) and whenever the variant is executed it greps fresh data from the underlying datasource.
The next posts
We here end our first post on Dynamic Lists but note that there is still a lot to discover and we’ll come back to Bob and the Dynamic List over the next weeks.