Make it easier to do single row excel exports.
This request is based on experiences with MooD 15 v116, but i see it as still valid for MooD 16. My apologies if this has already been resolved in MooD 16 v45
In brief:
When providing an overview of matrix data in MooD to customers and their regulators, there is a strong preference for data extracts where the data for each starting element comes in a single excel row. However to do so using the current options available in MooD requires a lot of hacks, and workarounds, ultimately to resolve this for complex data sets can take 100+ hours of effort. As such it would be fantastic if it was possible to configure relationships and pick lists to output with a seperator, and likewise for relationshps and pick lists in sub-columns.
Purpose:
When single row excel exports we currently have a couple of workarounds we use to deliver this to customers.
- We use the smart column functionality to execute a custom SQL script that returns the relationship or picklist values with our chosen seperator. However this workaround has the limitation that the smartcolumn string is limited to 250 characters, so we risk the data being cut off at 250 characters for elements with many relationships or with relationships with long names.
- We use a combination of the field modifiers available in the import synchronizer such as Array join string, special container elements with html fields created for the purpose, and relationships to these elements to output the data, however this can get very complicated to implement, is prone to error, and data for relationships on relationships can be tricky to import.
- We export the MooD data to excel and run it through an SSIS package where we transform the data into a single excel row, however this method takes quite a bit of coding, and is very sensitive to changes in the underlying meta data structure.
The first workaround has known issues so we run the risk of the data exported being invalid, and we have spent quite a bit of time in the past implementing the other workarounds. As such it would be really great to have better options available to provide customers with more easily readable data outputs from MooD.
Possible implementation:
The ideal fix would be some form of option to configure concatenation settings for the excel export synchronizer, with options like:
- [True/False] Concatenate pick lists and relationship elements
- If above Concatenate is True, Choose separator for concatination [Dropdown with values similar to Excel's options for Delimiter in the "text to columns" feature]
- if above Concatenate is True, [True/False] include name of parent column in concatenated sub-columns
- if above is True, specify format for inclusion of parent column {parent} in excel output of sub-column {sub-column}, example: "-----" +{parent}+"-----"+Environment.NewLine+{sub-column"
A temporary fix would be to increase the nvarchar limitation for smart column strings so that we can use the first workaround listed with less concerns about data being cut off.
This is the SQL we currently use to accomplish workaround 1:
"function (
(
select {2} + MooDPrimaryElement.name from MooDElementRelationship, MooDPrimaryElement
where MooDElementRelationship.subjectId = {0}
and MooDElementRelationship.aliasDefFieldId = (
select id from MooDAliasDefField
where aliasId = (
select aliasId from MooDPrimaryElement
where id = {0}
)
and defTypeId = (
select id from MooDAliasDefRelationshipType
where aliasId = {1}
)
)
and MooDElementRelationship.relatedElementId = MooDPrimaryElement.id
for xml path(''), type
Test:
Data structures to take into consideration would be something like this:
System A
System A relates to Vendor A
Vendor A is in Country A
relationship on a relationship - Vendor A is in Country A with Business function Support
relationship on a relationship - Vendor A is in Country A with Business function Data Processing
Vendor A is in Country B
relationship on a relationship - Vendor A is in Country B with Business function IT Development
System A relates to Vendor B
Vendor B is in Country A
relationship on a relationship - Vendor B is in Country A with Business function IT Development
relationship on a relationship - Vendor B is in Country A with Business function Finance
Vendor B is in Country B
relationship on a relationship - Vendor B is in Country B with Business function Support
System A relates to Vendor C
Vendor C is in Country C
System B......
So a test case for the Smart Column workaround fix would be
- Define a relationship in MooD between Type A and Type B
- Add elements to the relationship so that the count of characters for all element names of all elements in the relationship exceeds 250
- Configure the smart column with the SQL provided, confirm that the smart column contains the full name of all elements involved.
- Add the smart column to a matrix and export the matrix.
- Confirm that the excel export contains the full contents of the relationship.
Hope you find this relevant, and that it makes sense, please reach out if you have any questions or would like any further details on some of the workarounds we currently have running.
Br
Rune
Staun & Stender.
-
Hi, nice post.
MooD 16 (build 18 onwards) has increased the string Smart Column limit to 2000 characters. You could also consider adding conditional formatting so that LEN > 2000 appears in red?
I like the Excel export options (not very clear on the last one though).
I wonder whether the first 3 options should be on the matrix as a whole rather than just on the export?
Matt.
Below is an extract from the Sprint Review slides:
-
Rune: Now I understand, thanks. The excel export option would be easier to implement because we wouldn't have to worry about editing values in BA/MAE.
Jason: Don't think you could do this because the only way to write custom SQL is via an Aggregation / Smart Column and the Fact reading code has a maximum of 2000 characters.
-
Hi Matthew
That's really great to hear, looking forward to upgrading the customer to MooD 16 then.
If you can integrate the options into the matrix setup that would be really great as well. With regards to the last one, the intention was just to have a very flexible option to control how sub-column multi rows show up for multi row columns.
So for instance the example i put ""-----" +{parent}+"-----"+Environment.NewLine+{sub-column}" would output as
"----- Vendor A -----
Country A, Country B
----- Vendor B -----
Country A, Country B
"
It could also just be a dropdown list with some standard options for how to prefix a delimited list with the parent.
Br
Rune
Please sign in to leave a comment.
Comments
4 comments