Hi Team!
I need advice how to customize Insight reports.
I need an inventory report enhanced with several custom properties.
I can't understand if it's possible to join equipment dataset (racks for example) and custom properties dataset.
Both have different structure - Racks have "horizontal" when you have one row per device and Custom properties have "vertical" structure when each row contain one custom property and many lines per device.
I need to "pivot" Custom properties dataset, but can't find how...
Please advice how to do it.
Thanks in advance!
Solved! Go to Solution.
Hello Valentin.
Ok, I admit your question made me think about how to do what you're asking. So I did a little research, I pasted a section of the Insight view of the Inventory report below. You can certainly create a joined dataset with the existing inventorytags dataset and a new custom properties dataset to join rows to custom properties.
But as I think you already stated in this ticket this means you'd get a series of addition rows in your output, one for each custom property assigned to the device (right?).
So, I think you need to create a group in each section... the group would be based on name and location perhaps? Then you'd want to move the data fields in the above detail rows to the group footer row, then change your new custom property field to an string field which you aggregate the custom property value from each returned row into a list. So the grouping will produce one row of output for each inventory item and location, then your list of custom properties would only be outputted on the footer.
Make sense? I think this would work.
GregS
Hello Valentin
Have you consulted the ACS team regarding this item or is there a requirement you complete this process yourself?
Regards
Greg Sterling
Hello Valentin.
Ok, I admit your question made me think about how to do what you're asking. So I did a little research, I pasted a section of the Insight view of the Inventory report below. You can certainly create a joined dataset with the existing inventorytags dataset and a new custom properties dataset to join rows to custom properties.
But as I think you already stated in this ticket this means you'd get a series of addition rows in your output, one for each custom property assigned to the device (right?).
So, I think you need to create a group in each section... the group would be based on name and location perhaps? Then you'd want to move the data fields in the above detail rows to the group footer row, then change your new custom property field to an string field which you aggregate the custom property value from each returned row into a list. So the grouping will produce one row of output for each inventory item and location, then your list of custom properties would only be outputted on the footer.
Make sense? I think this would work.
GregS
Hello Greg!
Thank you very much for your input!
Really appreciate your help!
You describe all right. I need to know how it works so I need to do it myself.
And I found similar solution yesterday 🙂
I add grouping on asset key to the table and made aggregation field for each custom property. One difference that I place data field to the group header, not footer.
it works pretty slow, but works! 🙂
Discuss challenges in energy and automation with 30,000+ experts and peers.
Find answers in 10,000+ support articles to help solve your product and business challenges.
Find peer based solutions to your questions. Provide answers for fellow community members!