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.

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:

Order List

That is, she configured the datasource as

SELECT * FROM T_AGG_ORDER

but 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

Dynamic List without Metadata

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:

Dynamic List with Metadata

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:

Column Selection with Metadata

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.

CSV 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:

CSV as Metadata

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

404