Morning everyone... I had a question from a customer few weeks ago asking can they export more reports from Madics/Ashell? I said sure I can change the report to create a csv or like majority of the time I convert the report into XTree and they can export it from the data-grid them self using the right click export to CSV, Just let me know what report... They replied back with over 30+ of them, yikes... This made me think could PRTXLS help, is there any [90x,x] examples? remember im looking for the magic bullet one tweak/thing that work with 99% of the reports.
I created a LPT88.INI with COMMAND=SBX:PRTXLS but nothing unless I missed the obvious shows to export to xsl? (see screen shot) Or a licencing issues? mine shows: ISMA ATE ATS PDFX APEX AXL ASQL EFS - Testing under Ashell/Windows but would be for Ashell/Linux/ATE.
Lot of report were accounting ones with multi-line so not sure how clever it needs to be..
Thoughts, suggestion?
Code
RUN DATE: 13-JUN-22 TIME: 09:11 HRS Madics 4.2 (Multi Div) (STEVE) PAGE 1
PLIP02 4.2(501) PURCHASE LEDGER-TRANSACTION POSTING JOURNAL - BATCH: TI0220 PERIOD: 6, 2021
====================================================================================================================================
1 - INVOICE, 2 - CREDIT NOTE, 3 - DEBIT NOTE, 4 - JOURNAL, 5 - CONTRA
====================================================================================================================================
-----DOCUMENT----- -----------------SUPPLIER----------------- APPLY DUE-DATE
NO TP DATE NO NAME AMOUNT VAT TOTAL NON-DISC TO STL-DISC CURRNCY
12345678 1 30/06/21 STEVE Steve test Supplier Ltd 100.00 20.00 120.00 0.00 28/07/21 GBP
000861 REF: ABCDEF NOTE: 75.00 6230-400 3.00
25.00 6960-500
TOTAL INVOICES 100.00 20.00 120.00
TOTAL CREDIT NOTES 0.00 0.00 0.00
TOTAL DEBIT NOTES 0.00 0.00 0.00
TOTAL JOURNALS 0.00 0.00 0.00
TOTAL CONTRAS 0.00 0.00 0.00
TOTAL GBP 100.00 20.00 120.00
It seems we have managed to bury the necessary details about APEX configuration in the documentation where it isn't easily found. But the missing link is the APEX extensions configuration file, ASHCFG:APEX.CFG, covered under Operations > APEX > APEX Extensions. For the PRTXLS extension, you also need PRTXLS.INI and PRTXLS.SBX, both of which are included in the ATE and A-Shell install packages, and can also be retrieved via UPDCUR.
The $64 question though is how successful PRTXLS will be in translating your existing reports into Excel spreadsheets. The primary requirement is that the reports contained "well behaved" columns, and the primary consideration there is that there be at least 2 blank spaces between each column, or, if there is a set of underlines or dashes separating the column headers from the data, there is at least one blank separating each column. In the example below, we have both (a separator between the column headers and the data with at least one space between each column), and the column data has at least 2 spaces between them. It also helps if there are several rows with consistent column data (which allows PRTXLS to properly set up the Excel column format options.)
In your example, you only have one real data row, and those secondary (NOTE) lines are problematic in that there is only one space between the AMOUNT and VAT columns (and also the first 3 columns). You may be above to overcome some of that just by adding a separator row of dashes below the column headers (reducing the column separation requirement from 2 to 1 space), but you're likely to still have problems with mixture of line types.
Typically, the path from printed reports to spreadsheets consists of:
Experiment, i.e. try printing your existing reports to PRTXLS
To the extent that the results aren't good enough, determine whether minor systematic adjustments (such as adding a separator line between the headers and data) will suffice.
If that's not going to be good enough, then what I do is add a secondary CSV output to the existing report (taking advantage of the CSV2XL features). You can then create a wrapper for the PRTXLS extension to switch from the print file being previewed to the corresponding CSV file for Excel output.
That last option is obviously a potentially large and open-ended project, as the give-a-mouse-a-cooking effect will soon creep in as the users get a sense of how much more exciting their reports can become when turned into customized multi-sheet spreadsheets with all kinds of bells and whistles. But that's not necessarily a terrible path to go down. (While it may involve a considerable amount of programming, that may still be more cost effective for the customer in the long run than having their employees individually and repetitively doing the same kinds of things themselves.) In my experience the natural progression ends up with the most-commonly-used reports being output automatically to spreadsheets that are stored in a common network folder, possibly with an email being sent to the most-interested users, so that they all share one set of auto-generated spreadsheets instead of each user creating and maintaining their own spreadsheet kingdom.
Feel free to email me a couple of typical report samples, as it is sometimes the case that a minor tweak to the logic can make PRTXLS work a lot better for a given style of report.
After transferring that £34,000,000.67 to Ty's Swiss bank account I thought it would be magic do everything with a press of a button?
You asked all the questions i pretty well asked myself, and really not sure what even the user expects for multi line data and secondary row... In the end I think its going have be looking at them report by report. But thanks for links and tips and I'll have a play with one report and may just send you an example once i get stuck
Under Windows/Ashell I created a ERSATZ of ASHCFG: pointing it to DSK0:[1,4] and created the following APEX.CFG:
Code
;APEX customization
;BUTTON=prog,icon,tip{,msgno,ini}
;If msgno>0, is expected to be msg 002,### in inifile.lng
BUTTON=SBX:PRTXLS,ashico1::document_out,Export to Excel,38,BAS:PRTXLS.INI
BUTTON=SBX:PXLCFG,ashico1::preferences,Configuration Options For Export,0,BAS:PRTXLS.INI
I already had a PRTXLS.INI and PRTXLS.SBX Version 2.1(217).
So now getting the special Export APEX buttons Jorge has paid his life saving for.
Few tests and the one line by line reports looks well converted. As we expected the multi list reports do supress certain lines (but what could it doe with them any way to make any real sense)
To set up this for ATE can the INI's and SBX be on the server or do they have be on each ATE clients/local ashell?
You could put the SBX and INI on the server and add them to your ATSYNC list to sync to the client on startup.
As for the pricing, although we may have wanted to allow selection of the option for individual users, our lawyers have advised us that it would run afoul if not of the equal protection clause of the 14th Amendment to the US Constitution, then probably some more general human rights anti-discrimination principles. Not to mention the negative effect on workplace morale if only certain favored users are given this life-enhancing tool.
Perhaps you can apply for some kind of Brexit framework exemption, but you'd have to take that up with Ty. (And as a practical matter, would require individual licensing of each ATE client rather than server-side site licensing.)
I've now changed our login program to transfer the 3 files from the Unix server to the same folders on the local ATE. (I delete the local apex.cfg if I dont have it enabled in a new INI setting so I can turn on/off the feature)
Code
17/06/22 SE 4.2(596) Now Sync's the APEX/Excel export files from the Linux Server
to the the Local ATE (if enabled)
Set: APEX ENABLED=Y in MADSYS.INI under [SYSTEM]
SYNC APEX TO ATE=Y
This transfers the following files:
DSK0:APEX.CFG[1,7]
DSK0:PRTXLS.SBX[7,6]
DSK0:PRTXLS.INI[7,6]
FUNC.SUB was also changed adding: FN'SYNC'APEX'TO'ATE()
I now have APEX popup and show on printing by setting the following new LPT80.INI printer def on the UNIX server::
Is there a way to remove the Apex: Exit without printing prompt? and set DEVICE=null as my idea is if they choose this printer its solely use is APEX and Export to Excel and then exit so no warning prompt required as it just confuse people.
Actually more testing when I print to LPT80 (config settings above) the APEX does not come up, is there way forcing this no mater what if printed to a certain printer def. From a program using xcall spool and to LPT80 it does not show APEX but if I print to the LPT80 from the dot prompt it does.
Yes been very noisy here and that just me working along at home! Yes not sure what I did (or did not do) but now printing to the LPT80 that I created brings up the APEX all ready for the Export button to be pressed. LPT80.INI
Are those settings stored in the HKCU (current user) hive, or the HKLM (all users) hive? If the former, it seems excessive to block the program from updating the settings. After all, that's how the vast majority of the entries in the HKCU hive get created in the first place. But if you have your configurations spanning all users, then it makes sense to require admin privileges. (In which case I would suggest migrating or recreating them for each user, even if that means proliferation of registry entries that could otherwise be shared.)
Steve - FYI, I just posted a 2.1(218) version of the PRTXLS.SBX module. It won't help the Registry obstacle (above) but does have some improved logic for figuring out the column separation in reports (like the examples you sent me), and also closes a memory corruption loophole. For now, the updated module can be downloaded via the UPDCUR utility (and then ATSYNC'd to the ATE clients). I'll add it to the next builds after a little more field testing.
![[Linked Image]](https://www.microsabio.net/ForumImages/taxprt.png)
