Please Note This forum exists for community support for the Mango product family and the Radix IoT Platform. Although Radix IoT employees participate in this forum from time to time, there is no guarantee of a response to anything posted here, nor can Radix IoT, LLC guarantee the accuracy of any information expressed or conveyed. Specific project questions from customers with active support contracts are asked to send requests to support@radixiot.com.
Dynamic Excel reports?
-
Not sure if the title is exactly the right heading for this topic but it's the best way I can describe it. I'm curious if it's possible, like the dynamic dashboards (I'm about to watch some videos on that topic), to use a single Excel report template to generate a report based on the currently displayed values. Here's what I'm trying to do.
I have a client who has something like 50 commercial tenants, each one with an electric submeter. I'm going to build a quick dashboard for these tenants using the dynamic displays so that the tenants can log in and see their usage data and some stats. My client has requested that the tenants be able to download a report based on this data. The data would all be the same for each tenant, just the name and point data would differ. Maybe Excel reports aren't the way to accomplish that?
Thank you again, always saving my butt!
-
I've written a controller that enables you to download data as a CSV file. Taking into account not all people may have excel on their PC/smartphone at least CSV can be viewed by text editors as well as google sheets/excel etc.
If the data retrieved on the page is stored in an object in the page you'll be able to parse the entire object and convert it into a BLOB for downloading as a csv file.
This is all my opinion, perhaps some further info on what data needs to be parsed and formatted into a way that makes sense needs to be realised first...
-
Hmm... How about I reformulate the question to something like;
How would one solve the problem of having to generate 50+ reports if all the data is the same for each individual report. By "all the data is the same" I mean it's all hourly consumption data, same format, different numbers. -
That won't make a difference as that data will be pulled everytime a user logs in.
If you want to streamline, perhaps run a script that that will store the hourly information all in one go in a datapoint, or better yet in the JSON store as you're only updating not keeping an hourly record.Load said dashboard, pull required data, update names reflecting that of the user and bingo.
-
Hi psysak,
Not going to say this is the way to go, but there may be some inspiration in this thread for you: https://forum.infiniteautomation.com/topic/3044/can-you-relativise-an-excel-report-driven-by-watchlist-possibly
I do agree with Fox that the easiest and perhaps most straightforward would be to do it all in the frontend and let users download from the chart to get the raw data. But, I do think it'd be possible to kick off an excel report in a script and get the path to the excel file back to download it.
-
@phildunlap said in Dynamic Excel reports?:
it all in the frontend and let users download from the chart to get the raw data
Sorry not sure I said that, I meant perhaps assisting with a means to help him generate a csv file on the fly via javascript. Can always use a JSON data item/angularJS template as a means to template the format so it's not hardcoded.
The script datasource idea will certainly remove overhead on the user's side as well. -
Thanks for the replies. I was contemplating the csv option because the number of reports is starting to get a little loony on my end. The thing with the excel report is formatting, as trivial as it may sound I don't want my clients just downloading raw csv files. I need our logo, proper formatting with bolding, some equations to run etc.
I think most of my checkboxes are checked so long as I can load a single excel template and just trigger that the 80 or so times that I need to. I'll poke at the problem based on the thread @phildunlap posted.
If I were to consider the csv way, are you guys aware of anything Angular, and maybe not so much the mango itself, could do?
-
@psysak said in Dynamic Excel reports?:
The thing with the excel report is formatting, as trivial as it may sound I don't want my clients just downloading raw csv files. I need our logo, proper formatting with bolding, some equations to run etc.
Ah, why didn't you say so sooner.
Seeing as you dont need them to 'access' a raw file per se. why not just generate a pdf file to encapsulate it instead?? Do you anticipate that they will need to use the data for their own graphing/charting in excel?? If not, this is also a viable solution. Works everywhere, can generate the layout and whatnot that you need in html and make it open in a new page for them to download or just download outright. There are numerous javascript libraries available. Doing it this way will also enable you to append chart images on the page as well rather than just numbers using amcharts's export api.Let me know your thoughts.
EDIT: I have come across this library if you would still rather use excel. Whether or not it will do what you want I'm not certain.
https://github.com/SheetJS/js-xlsx however at first glance I'm not seeing anything resembling image support....
