0

I am trying to create a report like given below with Node.js. The data is consumed from Microstrategy API.

enter image description here

Below is the JSON response I am getting from Microstrategy

{
  "id": "12DC624040860B5401F516A2341D95C8",
  "name": "Sales Report_AB.xlsx",
  "instanceId": "A7900B0A4AD840FF327F04897A9DB4E3",
  "result": {
    "definition": {
      "attributes": [
        {
          "name": "Branch Channel",
          "id": "B449BEE543721EEB57501BBC1EDD1B3D",
          "type": "Attribute",
          "forms": [
            {
              "id": "45C11FA478E745FEA08D781CEA190FE5",
              "name": "ID",
              "dataType": "Char"
            }
          ]
        },
        {
          "name": "Branch City State",
          "id": "2DF6E4204460A79BE3A86BA9F1FE9552",
          "type": "Attribute",
          "forms": [
            {
              "id": "45C11FA478E745FEA08D781CEA190FE5",
              "name": "ID",
              "dataType": "Char"
            }
          ]
        },
        {
          "name": "Client Full Name",
          "id": "61460DC345A8EFDCCBCFB9AB0D3FEFAB",
          "type": "Attribute",
          "forms": [
            {
              "id": "45C11FA478E745FEA08D781CEA190FE5",
              "name": "ID",
              "dataType": "Char"
            }
          ]
        },
        {
          "name": "Firm",
          "id": "145C2A3E45C369BEA82B9BA3C6CA68BF",
          "type": "Attribute",
          "forms": [
            {
              "id": "45C11FA478E745FEA08D781CEA190FE5",
              "name": "ID",
              "dataType": "Char"
            }
          ]
        },
        {
          "name": "Product Group",
          "id": "5429196C44F9A365F0904DB2733C0209",
          "type": "Attribute",
          "forms": [
            {
              "id": "45C11FA478E745FEA08D781CEA190FE5",
              "name": "ID",
              "dataType": "Char"
            }
          ]
        },
        {
          "name": "Regional Manager (RM) MF",
          "id": "DE85E29F45158ED9C5B35082B399A22E",
          "type": "Attribute",
          "forms": [
            {
              "id": "45C11FA478E745FEA08D781CEA190FE5",
              "name": "ID",
              "dataType": "Char"
            }
          ]
        }
      ],
      "metrics": [
        {
          "name": "Branch Rank",
          "id": "975BE0ED4174AA52C0AF97A96D64CE7B",
          "type": "Metric",
          "min": 4,
          "max": 103,
          "numberFormatting": {
            "category": 7,
            "decimalPlaces": 0,
            "formatString": "#,##0;\"(\"#,##0\")\""
          }
        },
        {
          "name": "MF & SMA Current AUM",
          "id": "33B375744FA4A91B86A87D9FE4EEE718",
          "type": "Metric",
          "min": 145865.48,
          "max": 10530391.47,
          "numberFormatting": {
            "category": 7,
            "decimalPlaces": 0,
            "formatString": "#,##0;\"(\"#,##0\")\""
          }
        },
        {
          "name": "MF & SMA Today Sales",
          "id": "82F9DD1D4F6AC5EBA77D918D0541B9AD",
          "type": "Metric",
          "min": 15.34,
          "max": 1315.91,
          "numberFormatting": {
            "category": 7,
            "decimalPlaces": 0,
            "formatString": "#,##0;\"(\"#,##0\")\""
          }
        },
        {
          "name": "MF & SMA Pr. Month Sales",
          "id": "3890BF0C4553300D5467A7A871C66F76",
          "type": "Metric",
          "min": 388.56,
          "max": 60365.82,
          "numberFormatting": {
            "category": 7,
            "decimalPlaces": 0,
            "formatString": "#,##0;\"(\"#,##0\")\""
          }
        },
        {
          "name": "RET Today Sales",
          "id": "99DC7CD344B5B5C6DD337989077BC478",
          "type": "Metric",
          "min": 15.34,
          "max": 1315.91,
          "numberFormatting": {
            "category": 7,
            "decimalPlaces": 0,
            "formatString": "#,##0;\"(\"#,##0\")\""
          }
        },
        {
          "name": "RET Current AUM",
          "id": "981FE7E34CE4D6C3543B2B8E0367ECAA",
          "type": "Metric",
          "min": 46170.04,
          "max": 3172313.93,
          "numberFormatting": {
            "category": 7,
            "decimalPlaces": 0,
            "formatString": "#,##0;\"(\"#,##0\")\""
          }
        }
      ],
      "thresholds": []
    },
    "data": {
      "paging": {
        "total": 5,
        "current": 5,
        "offset": 0,
        "limit": 1000,
        "prev": null,
        "next": null
      },
      "root": {
        "isPartial": false,
        "children": [
          {
            "depth": 0,
            "element": {
              "attributeIndex": 0,
              "formValues": {
                "ID": "Broker Dealer"
              },
              "name": "Broker Dealer",
              "id": "hBroker Dealer;B449BEE543721EEB57501BBC1EDD1B3D"
            },
            "isPartial": false,
            "children": [
              {
                "depth": 1,
                "element": {
                  "attributeIndex": 1,
                  "formValues": {
                    "ID": "BILLINGS"
                  },
                  "name": "BILLINGS",
                  "id": "hBILLINGS;2DF6E4204460A79BE3A86BA9F1FE9552"
                },
                "isPartial": false,
                "children": [
                  {
                    "depth": 2,
                    "element": {
                      "attributeIndex": 2,
                      "formValues": {
                        "ID": "TODD H VRALSTED"
                      },
                      "name": "TODD H VRALSTED",
                      "id": "hTODD H VRALSTED;61460DC345A8EFDCCBCFB9AB0D3FEFAB"
                    },
                    "isPartial": false,
                    "children": [
                      {
                        "depth": 3,
                        "element": {
                          "attributeIndex": 3,
                          "formValues": {
                            "ID": "UBS FINANCIAL SERVICES INC."
                          },
                          "name": "UBS FINANCIAL SERVICES INC.",
                          "id": "hUBS FINANCIAL SERVICES INC.;145C2A3E45C369BEA82B9BA3C6CA68BF"
                        },
                        "isPartial": false,
                        "children": [
                          {
                            "depth": 4,
                            "element": {
                              "attributeIndex": 4,
                              "formValues": {
                                "ID": "GLOBAL BOND"
                              },
                              "name": "GLOBAL BOND",
                              "id": "hGLOBAL BOND;5429196C44F9A365F0904DB2733C0209"
                            },
                            "isPartial": false,
                            "children": [
                              {
                                "depth": 5,
                                "element": {
                                  "attributeIndex": 5,
                                  "formValues": {
                                    "ID": "Doss Steve"
                                  },
                                  "name": "Doss Steve",
                                  "id": "hDoss Steve;DE85E29F45158ED9C5B35082B399A22E"
                                },
                                "metrics": {
                                  "Branch Rank": {
                                    "rv": 4,
                                    "fv": "4",
                                    "mi": 0
                                  },
                                  "MF & SMA Current AUM": {
                                    "rv": 567490.58,
                                    "fv": "567,491",
                                    "mi": 1
                                  },
                                  "MF & SMA Today Sales": {
                                    "rv": 15.34,
                                    "fv": "15",
                                    "mi": 2
                                  },
                                  "MF & SMA Pr. Month Sales": {
                                    "rv": 388.56,
                                    "fv": "389",
                                    "mi": 3
                                  },
                                  "RET Today Sales": {
                                    "rv": 15.34,
                                    "fv": "15",
                                    "mi": 4
                                  },
                                  "RET Current AUM": {
                                    "rv": 46170.04,
                                    "fv": "46,170",
                                    "mi": 5
                                  }
                                }
                              }
                            ]
                          },
                          {
                            "depth": 4,
                            "element": {
                              "attributeIndex": 4,
                              "formValues": {
                                "ID": "LARGE CAP GROWTH"
                              },
                              "name": "LARGE CAP GROWTH",
                              "id": "hLARGE CAP GROWTH;5429196C44F9A365F0904DB2733C0209"
                            },
                            "isPartial": false,
                            "children": [
                              {
                                "depth": 5,
                                "element": {
                                  "attributeIndex": 5,
                                  "formValues": {
                                    "ID": "Doss Steve"
                                  },
                                  "name": "Doss Steve",
                                  "id": "hDoss Steve;DE85E29F45158ED9C5B35082B399A22E"
                                },
                                "metrics": {
                                  "Branch Rank": {
                                    "rv": 4,
                                    "fv": "4",
                                    "mi": 0
                                  },
                                  "MF & SMA Current AUM": {
                                    "rv": 10530391.47,
                                    "fv": "10,530,391",
                                    "mi": 1
                                  },
                                  "MF & SMA Today Sales": {
                                    "rv": 763.74,
                                    "fv": "764",
                                    "mi": 2
                                  },
                                  "MF & SMA Pr. Month Sales": {
                                    "rv": 60365.82,
                                    "fv": "60,366",
                                    "mi": 3
                                  },
                                  "RET Today Sales": {
                                    "rv": 763.74,
                                    "fv": "764",
                                    "mi": 4
                                  },
                                  "RET Current AUM": {
                                    "rv": 3172313.93,
                                    "fv": "3,172,314",
                                    "mi": 5
                                  }
                                }
                              }
                            ]
                          }
                        ]
                      }
                    ]
                  }
                ]
              },
              {
                "depth": 1,
                "element": {
                  "attributeIndex": 1,
                  "formValues": {
                    "ID": "BOULDER"
                  },
                  "name": "BOULDER",
                  "id": "hBOULDER;2DF6E4204460A79BE3A86BA9F1FE9552"
                },
                "isPartial": false,
                "children": [
                  {
                    "depth": 2,
                    "element": {
                      "attributeIndex": 2,
                      "formValues": {
                        "ID": "RORY D LINDQUIST"
                      },
                      "name": "RORY D LINDQUIST",
                      "id": "hRORY D LINDQUIST;61460DC345A8EFDCCBCFB9AB0D3FEFAB"
                    },
                    "isPartial": false,
                    "children": [
                      {
                        "depth": 3,
                        "element": {
                          "attributeIndex": 3,
                          "formValues": {
                            "ID": "MORGAN STANLEY SMITH BARNEY LLC"
                          },
                          "name": "MORGAN STANLEY SMITH BARNEY LLC",
                          "id": "hMORGAN STANLEY SMITH BARNEY LLC;145C2A3E45C369BEA82B9BA3C6CA68BF"
                        },
                        "isPartial": false,
                        "children": [
                          {
                            "depth": 4,
                            "element": {
                              "attributeIndex": 4,
                              "formValues": {
                                "ID": "SMALL CAP GROWTH"
                              },
                              "name": "SMALL CAP GROWTH",
                              "id": "hSMALL CAP GROWTH;5429196C44F9A365F0904DB2733C0209"
                            },
                            "isPartial": false,
                            "children": [
                              {
                                "depth": 5,
                                "element": {
                                  "attributeIndex": 5,
                                  "formValues": {
                                    "ID": "Doss Steve"
                                  },
                                  "name": "Doss Steve",
                                  "id": "hDoss Steve;DE85E29F45158ED9C5B35082B399A22E"
                                },
                                "metrics": {
                                  "Branch Rank": {
                                    "rv": 103,
                                    "fv": "103",
                                    "mi": 0
                                  },
                                  "MF & SMA Current AUM": {
                                    "rv": 339671.74,
                                    "fv": "339,672",
                                    "mi": 1
                                  },
                                  "MF & SMA Today Sales": {
                                    "rv": 1315.91,
                                    "fv": "1,316",
                                    "mi": 2
                                  },
                                  "MF & SMA Pr. Month Sales": {
                                    "rv": 3571.37,
                                    "fv": "3,571",
                                    "mi": 3
                                  },
                                  "RET Today Sales": {
                                    "rv": 1315.91,
                                    "fv": "1,316",
                                    "mi": 4
                                  },
                                  "RET Current AUM": {
                                    "rv": 339671.74,
                                    "fv": "339,672",
                                    "mi": 5
                                  }
                                }
                              }
                            ]
                          }
                        ]
                      }
                    ]
                  }
                ]
              }
            ]
          },
          {
            "depth": 0,
            "element": {
              "attributeIndex": 0,
              "formValues": {
                "ID": "Financial Advisor"
              },
              "name": "Financial Advisor",
              "id": "hFinancial Advisor;B449BEE543721EEB57501BBC1EDD1B3D"
            },
            "isPartial": false,
            "children": [
              {
                "depth": 1,
                "element": {
                  "attributeIndex": 1,
                  "formValues": {
                    "ID": "DENVER"
                  },
                  "name": "DENVER",
                  "id": "hDENVER;2DF6E4204460A79BE3A86BA9F1FE9552"
                },
                "isPartial": false,
                "children": [
                  {
                    "depth": 2,
                    "element": {
                      "attributeIndex": 2,
                      "formValues": {
                        "ID": "CHAD J LARSEN"
                      },
                      "name": "CHAD J LARSEN",
                      "id": "hCHAD J LARSEN;61460DC345A8EFDCCBCFB9AB0D3FEFAB"
                    },
                    "isPartial": false,
                    "children": [
                      {
                        "depth": 3,
                        "element": {
                          "attributeIndex": 3,
                          "formValues": {
                            "ID": "LPL FINANCIAL LLC"
                          },
                          "name": "LPL FINANCIAL LLC",
                          "id": "hLPL FINANCIAL LLC;145C2A3E45C369BEA82B9BA3C6CA68BF"
                        },
                        "isPartial": false,
                        "children": [
                          {
                            "depth": 4,
                            "element": {
                              "attributeIndex": 4,
                              "formValues": {
                                "ID": "SMALL CAP GROWTH"
                              },
                              "name": "SMALL CAP GROWTH",
                              "id": "hSMALL CAP GROWTH;5429196C44F9A365F0904DB2733C0209"
                            },
                            "isPartial": false,
                            "children": [
                              {
                                "depth": 5,
                                "element": {
                                  "attributeIndex": 5,
                                  "formValues": {
                                    "ID": "Doss Steve"
                                  },
                                  "name": "Doss Steve",
                                  "id": "hDoss Steve;DE85E29F45158ED9C5B35082B399A22E"
                                },
                                "metrics": {
                                  "Branch Rank": {
                                    "rv": 101,
                                    "fv": "101",
                                    "mi": 0
                                  },
                                  "MF & SMA Current AUM": {
                                    "rv": 145865.48,
                                    "fv": "145,865",
                                    "mi": 1
                                  },
                                  "MF & SMA Today Sales": {
                                    "rv": 322.84,
                                    "fv": "323",
                                    "mi": 2
                                  },
                                  "MF & SMA Pr. Month Sales": {
                                    "rv": 1507.62,
                                    "fv": "1,508",
                                    "mi": 3
                                  },
                                  "RET Today Sales": {
                                    "rv": 322.84,
                                    "fv": "323",
                                    "mi": 4
                                  },
                                  "RET Current AUM": {
                                    "rv": 145865.48,
                                    "fv": "145,865",
                                    "mi": 5
                                  }
                                }
                              }
                            ]
                          }
                        ]
                      }
                    ]
                  },
                  {
                    "depth": 2,
                    "element": {
                      "attributeIndex": 2,
                      "formValues": {
                        "ID": "PATTERSON J MCKINLAY"
                      },
                      "name": "PATTERSON J MCKINLAY",
                      "id": "hPATTERSON J MCKINLAY;61460DC345A8EFDCCBCFB9AB0D3FEFAB"
                    },
                    "isPartial": false,
                    "children": [
                      {
                        "depth": 3,
                        "element": {
                          "attributeIndex": 3,
                          "formValues": {
                            "ID": "LPL FINANCIAL LLC"
                          },
                          "name": "LPL FINANCIAL LLC",
                          "id": "hLPL FINANCIAL LLC;145C2A3E45C369BEA82B9BA3C6CA68BF"
                        },
                        "isPartial": false,
                        "children": [
                          {
                            "depth": 4,
                            "element": {
                              "attributeIndex": 4,
                              "formValues": {
                                "ID": "HIGH INCOME"
                              },
                              "name": "HIGH INCOME",
                              "id": "hHIGH INCOME;5429196C44F9A365F0904DB2733C0209"
                            },
                            "isPartial": false,
                            "children": [
                              {
                                "depth": 5,
                                "element": {
                                  "attributeIndex": 5,
                                  "formValues": {
                                    "ID": "Doss Steve"
                                  },
                                  "name": "Doss Steve",
                                  "id": "hDoss Steve;DE85E29F45158ED9C5B35082B399A22E"
                                },
                                "metrics": {
                                  "Branch Rank": {
                                    "rv": 80,
                                    "fv": "80",
                                    "mi": 0
                                  },
                                  "MF & SMA Current AUM": {
                                    "rv": 1545251.85,
                                    "fv": "1,545,252",
                                    "mi": 1
                                  },
                                  "MF & SMA Today Sales": {
                                    "rv": 662.46,
                                    "fv": "662",
                                    "mi": 2
                                  },
                                  "MF & SMA Pr. Month Sales": {
                                    "rv": 50635.32,
                                    "fv": "50,635",
                                    "mi": 3
                                  },
                                  "RET Today Sales": {
                                    "rv": 662.46,
                                    "fv": "662",
                                    "mi": 4
                                  },
                                  "RET Current AUM": {
                                    "rv": 1545251.85,
                                    "fv": "1,545,252",
                                    "mi": 5
                                  }
                                }
                              }
                            ]
                          }
                        ]
                      }
                    ]
                  }
                ]
              }
            ]
          }
        ]
      }
    }
  }
}

The data is kind of a tree structure and I can vary from report to report, i.e. the number of sub-nodes and subcategories can change. So I need a dynamic code to build the same. I have tried something from my end and I am not able to build it in the way I want. Below is my code

var result = { "records": [] };
var res = test(records.result.data.root.children, result,[], 0);

console.log(res);

function test(records, result,items, index) {
    _.forEach(records, function (value, key) {        

        items.push({"depth":value.depth,"name":value.element.name});
        result.records[index] = items;

        if(typeof value.children != "undefined") {
            if(value.children.lenth > 1) {
                index++;
            }     
            test(value.children,result, items,index);
        } else {
            if(typeof value.metrics !== "undefined") {
                _.forEach(value.metrics, function (v, k) {
                    items.push({mkey:k,mval:v.fv});
                    result.records[index] = items;
                });
            }
        }
        index++;
        items = [];
    });

    return result;
}

The above code is using Lodash for array operations.

Improve this question
4
  • what do you need to do? your code snippet does not look like creating/building tree-like structure. actually it looks like you want to flatten existing tree-structured data. Commented Sep 14, 2018 at 5:22
  • I just want to flatten the json I have provided. The flattened array should look like the image provided. Hope you are able to understand Commented Sep 14, 2018 at 5:39
  • The result array I have flattened contains only the first row, fourth row, and some columns in the 5th row. I just want to now what modifications are needed in the existing code for it to give me every record Commented Sep 14, 2018 at 5:41
  • @skyboyer are u able to understand? Commented Sep 14, 2018 at 5:46

1 Answer 1

Reset to default
2

I'm not sure what exactly you want to collect to a flat array so I'm going to show you a general way how you can recursively traverse a node tree and collect all the nodes from it to an array.

Later you can do .filter, .map, etc. on the result to get any data you want.

function collectNodes(rootNode) {
    const nodes = []

    function visitNode(node) {
        nodes.push(node)

        if (node.children) {
            node.children.forEach(visitNode)
        }
    }

    visitNode(rootNode)

    return nodes
}

const nodes = collectNodes(json.result.data.root)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.