Forum Discussion

mstarr13's avatar
mstarr13
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago
Solved

Dynamic Column generation challenge

So I am needing to build a table on the fly. First the structure then I am going to fill it. On the structure, I just want to insert a column for every year from 2007 through 2058. I was trying to do...
Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    mstarr13 based on your example data table, it is perfect,  use matrix visual, put org on rows, years on columns and # procured on value and you will get what you are looking for.

     

    Would appreciate Kudos 🙂 if my solution helped.

parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

mstarr13 so you want 52 columns(2007 - 2058)?? It is bad design thou, not sure how you are planning to use these columns.

mstarr13's avatar
mstarr13
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

yep in this case 52 columns. Once that table is built, it is going to be populated with values I have generated by reporting Procurment number by each of those years which I have summarized in another table by grouping on a Org identifier. So I get a breakdown of procurement numbers by Org for each of those years. 

 

Imagine you have a table:

Org, Year, # procured

A       2007    12

A       2007     14

A       2008     8

A       2009     6

B      2007     10

B      2007     9

B      2009     5

 

and so on for 24 orgs, for years covering 2007-2028. They want to see in their report

Org 2007  2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019

A      26       8      6       0      0       0        0       0        0      0      0        0     0 

B     19         0      5      0       0      0        0       0        0       0     0        0     0 

 

and so on. This data comes out of a D365 OData Feed, structure incoming cannot be changed and does no support the types of metrics they want so I am attempting to build the structure on the fly to report against. 

 

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    mstarr13 based on your feedback, you should be using year on rows not in columns and then you can visualize the way you want. This is better design rather than having years on columns.

    • parry2k's avatar
      parry2k
      Icon for Super User rankSuper User
      6 years ago

      mstarr13 based on your example data table, it is perfect,  use matrix visual, put org on rows, years on columns and # procured on value and you will get what you are looking for.

       

      Would appreciate Kudos 🙂 if my solution helped.