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

).value('.', 'nvarchar(max)'), 1, 1, '')"

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.

Rune

Staun & Stender.

Matthew Dodd

March 06, 2019 13:55

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 Kjerulff

March 07, 2019 11:01

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

It could also just be a dropdown list with some standard options for how to prefix a delimited list with the parent.

Jason Rose

March 07, 2019 16:36

If anything's over 2000 characters is it possible @Matt to write custom SQL that would work in a datasheet or would it have a limitation there too? Long shot but might be useful for a couple of people as a workaround.

March 08, 2019 11:12

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.

Make it easier to do single row excel exports.

Comments

Didn't find what you were looking for?