The Metadata Datasource for Dynamic Lists
In this episode of the Dynamic List series we discuss how IT can enhance the user experience by providing proper descriptions and default aliases for attributes.
This is the third post in the Dynamic List series. So far the following posts are available in this series.
- Dynamic List – Part 1 – Introduction
- Stars, Spreadsheets and the Dynamic Liste
- The Metadata Datasource for Dynamic Lists
The Metadata Datasource
Remember how Alice created a Dynamic List “Order List” which contains any information about purchase orders of the imaginary toy manufacturer 1:87 (see the first installment of this series on the Dynamic List). While Bob is happy that he can now simply get all the data he needs, when he needs it, Alice noticed that she did not fully configure the Dynamic List. Alice created the Dynamic List as follows:
That is, she configured the datasource as
SELECT * FROM T_AGG_ORDERbut she did not configure a Metadata Datasource. This is, of course, perfectly fine, but without the Metadata Datasource users only see the attributes’ technical names but no further description. When opening the Dynamic List and selecting all the attributes we get the following result
Each attribute has a technical name that luckily for the employees of 1:87 quite well describes the content. Nevertheless an additional textual description and a default alias might make the experience for users even better. When configuring Dynamic Lists, users can define aliases for attributes which are then used as heading for that column in the final report. If no alias is specified ReportServer checks if there is a default alias specified and if this is also not the case then it uses the attribute’s technical name. The “Order List” contains fields such as
- CUS_ADDRESSLINE1
- CUS_ADDRESSLINE2
- CUS_CITY
- CUS_COUNTRY
- …
The Metadata datasource allows IT to specify default aliases as well as textual descriptions for all the attributes of a Dynamic List. For this, one must define a datasource that returns a table consisting of three columns: COLUMN_NAME, DEFAULT_ALIAS, and DESCRIPTION. For example, consider that the datasource is configured such that it returns the following table:
| COLUMN_NAME | DEFAULT_ALIAS | DESCRIPTION |
|---|---|---|
| CUS_ADDRESSLINE1 | NULL | Customer Address: First line |
| CUS_ADDRESSLINE2 | NULL | Customer Address: Second line |
| CUS_CITY | City | Customer’s city of residence |
| CUS_COUNTRY | Country | Customer’s country of residence |
Now, if we open the corresponding Dynamic List and select the above four columns we get:
As you can see, the configuration is now preset to contain the default aliases (if present) and the description. While the attribute description does not appear in the final report it makes finding the correct attributes easier as when selecting the attributes the description is displayed and the search (on top) also searches through the descriptions:
CSV Datasources
A natural question to ask is: “why is this information defined via a datasource”. The answer is, of course, it is the most flexible way to define metadata. In some warehouses you might already have metadata directly available in your warehouse somewhere that you can extract here. Even if not, storing the metadata in a database provides you with a single point where to change the metadata.
On the other hand, in some cases it might be a bit cumbersome to create an extra database table and manage the metadata for your report there. One easy way to the rescue are ReportServer’s CSV datasources. A CSV datasource processes a comma separated value (CSV) file and makes the content available as a datasource that can then be used, for example, to power a Dynamic List, or to power the metadata datasource of a Dynamic List.
When defining a CSV datasource you need to define a connector which describes where the datasource gets its CSV file from. Currently you can choose between a text connector, a url connector and an argument connector.
The text connector allows to specify the data directly at the datasource while the URL connector allows you to specify a URL from which the CSV file is loaded. Finally, the argument connector provides you with a neat way to easily enter static data, for example, for datasource parameters or the metadata datasource. If we create a CSV datasource with an argument connector, name it CSV Argument Source and choose it as the datasource for our metadata datasource we are provided with a text field into which we can directly type in the information:
Note that the first line of a CSV datafile must always provide header information (i.e., the column names).
With this we are at the end of this blog post.
Happy Reporting