Hi All,
This is one interesting post that I wanted to share with you all. Very rarely we get a requirement to concatenate the attribute values and group the same to appear under another attribute. Though this requirement is best handled in a database view/ table, it may not be a permanent solution for all scenarios. It may vary based on the reporting filter condition, report granularity etc., Also it my get very complicated if the concatenation needs to happen based on two attributes from different tables.
So its better handled in the reporting side if you have many such concatenations at different attribute levels and different attribute concatenation.
Today we are going to see how to handle such a requirement from through MicroStrategy reports. Here is the requirement with the following base data
So the concatenation needs to happen on Product attribute based on each Customer group and the final output should appear like below.
To achieve this you need to design couple of metrics, this unfortunately can be done only through metrics, as Derived Attributes wont support ConcatAgg function.
Step 1: Create a Derived Metric for the attribute which you want to GroupConcat. Its very important to set the aggregation parameters as mentioned below to get the desired output.
Step 2: Create the ConcatAgg Metric using the newly created derived metric.
And you can customise the options to change the delimiter character and sort options as well. In case if you need to use custom sort order for the concatenated attribute elements you would need to create a new attribute with custom sorting and add it in the Sort by parameter.
That's it you are done. You just have to add the Product Concat metric alone in the report and keep the Product Attribute and Product Derived metric in the report objects.
Pros.
Main thing in this implementation is that this eliminates the dependency with the database layer if you are not handling concatenation in the database level. Since the reports may have many such combinations between different attributes where we cannot have a concat column loaded in the database.
Also any change in the logic can be derived in the report level itself.
Cons.
Since this is a metric you cant add this field in between the attributes. You may need to consider making all other attributes as metrics placed after the GroupConcat.
