I work with from what I can tell is a relatively old version of Actuate with only the minimum of licensing (more or less). There are a number of functions we have come to make from scratch in part due to these limitations, as I understand there are additional licenses that may provide these natively, but as we only have Actuate as part of a third party that handles our primary software, obtaining the additional licenses are not in my capabilities at this time.
That being said, most of my work involves developing workarounds and wheel reinventions of sorts. Our client demands revolve largely around developing data exports into some form of flat file or other such that allows for easy filtering/sorting/datamining/etc. Our major workaround for these were to simply export a given query to a CSV file with an xls extension so it would open up in Excel for the common end user to view and otherwise process. There are a few flaws in this, such as Excel misinterpreting data into being a format it should not be, but otherwise this has worked well for us.
Time has passed, and client demands are requiring more and more sophisticated file formats. Gone are the days where a flat CSV file disguised as an Excel file or clients having import processes over simple formats, instead leading to better graphical interfaces such as Excel (both the old XLS format and the newer XLSX), which is where we've had to get a little trickier as we have gone along, but our methods so far have proven to be awkward at best and still requiring some touch-up in the file within the native program instead of making a file designed for this purpose.
Our setup is designed to build the files and save them without any added user input other than to say "Go". Our problem is with what access we do have to Actuate tools means that at best, we have to manually save the file from Actuate's GUI rather than have the process automated, which to cover more modern formats have led us to cheat a little and build an XML file that opens in Excel well enough to go forward. This allows us a little more sophistication, such as multiple spreadsheets within one file. However, it is still fairly archaic and full of holes, not to mention does not allow for anything but simple spreadsheets.
Our most recent request has now brought me to where I have come here for some insight regarding how to go forward. This request has basically put us in a place where the only way I can fathom to fulfill is by building the file from scratch in a native Excel format. However, our limitations force us to basically build either a text file or a series of them in some manner, which does give us the ability to make XLSX files (we can run a script to zip them up and rename the file accordingly). My research regarding this has shown me that the task I'm attempting will be VERY onerous and likely full of holes to build a process to do so. If need be, I shall do it, but it is something I'm trying to avoid, which is why I am here.
With the limitations of Actuate, I'm basically forced into a VB3 environment. This is probably the biggest limiter. My current process in Actuate is a few stored procedures that have the static XML for the simple XML Spreadsheet format, which has to be redundantly stored for every variation, which has given me some rather complicated code in terms of sheer volume of variable storage. This will not do both in the rapidly increasing amount of redundancy as more requests are made as well as the fact that I still can't do some of the more advanced functions of Excel this way. I can theoretically build the structure of the XLSX files and zip them, but this process may take a long time to build and I may be entirely reinventing the wheel to do so.
To make a long story short, I need to be able to export my data that I obtain via SQL into either XLS or XLSX, all of which need to have chart building support. This needs to be fully automated so that the end user can say "build these for me", and it will be built (charts included) without a lot of user input other than the query parameters from the SQL. My current plan is to build the XML files needed for the XLSX format from scratch at runtime through the VB3 code of ERDPro followed by a shell script to zip them and rename the zip with an XLSX extension, which on looking into has shown to be a great deal of effort to do, at least from scratch. My question is, is there a simpler way that I'm not seeing?
