17 replies to this topic

[mikelandl]

Hi All,

We've just recently started investigating outputting reports to Excel through XML (instead of using DDE - giving us too many issues) and have come across a few issues. If anyone has any suggestions, tips or words of warning it would be greatly appreciated. We're currently developing in 6.2.
Is it possible to dynamically set the "XML Tag" of a field? We have some reports where we would like to dynamically set the headings (i.e. month, year, etc.. based on user input).

Is it possible to output the page header (found in the statics tab of VIP/Developer) to XML?
I'm sure I had more questions but can't think of them at the moment.

Thanks.

Edited by mikelandl, 15 May 2009 - 07:37 PM.

[DARREN]

Hi All,

We've just recently started investigating outputting reports to Excel through XML (instead of using DDE - giving us too many issues) and have come across a few issues. If anyone has any suggestions, tips or words of warning it would be greatly appreciated. We're currently developing in 6.2.
Is it possible to dynamically set the "XML Tag" of a field? We have some reports where we would like to dynamically set the headings (i.e. month, year, etc.. based on user input).

Is it possible to output the page header (found in the statics tab of VIP/Developer) to XML?
I'm sure I had more questions but can't think of them at the moment.

Thanks.

Mike

We have used XML reporting with great success, although not in conjunction with spreadsheets. The answers to your questions are

Yes, it is possible to set the tag of a field dynamically. This, however, can only be achieved in version 6.2 (and above). There is a post on this board that discusses this topic in greater detail.

No, formats and control breaks are not output in the XML stream. In my experience, however, this is not an issue as they can easily be defined as dynamics. This also makes for succinct code, as the structure of the document is all defined in one place.

[mikelandl]

Hi Mike

I am a very big user of DDE's to produce reports in Excel and would be interested to hear about your problems. What version of Pro-IV and Excel do you use?

Regards

Steve

Hi Steve,

We're running on version 6.2, using Excel 2003 and 2007 (both on Windows XP and Windows Vista machines). We have 3 main areas of concern.

1. Occasionally the output will become garbled or complete rows of data will be missed.
2. On one of our Windows Vista machines, when the report completes, the function hangs (does not return to the menu). You have to ctrl + break to get back to the menu.
3. Speed. Perhaps we are not going about things as efficiently as possible but to output a 50 column report with 2000 rows of data takes atleast 2 minutes.

[mikelandl]

Hi All,

We've just recently started investigating outputting reports to Excel through XML (instead of using DDE - giving us too many issues) and have come across a few issues. If anyone has any suggestions, tips or words of warning it would be greatly appreciated. We're currently developing in 6.2.
Is it possible to dynamically set the "XML Tag" of a field? We have some reports where we would like to dynamically set the headings (i.e. month, year, etc.. based on user input).

Is it possible to output the page header (found in the statics tab of VIP/Developer) to XML?
I'm sure I had more questions but can't think of them at the moment.

Thanks.

Mike

We have used XML reporting with great success, although not in conjunction with spreadsheets. The answers to your questions are

Yes, it is possible to set the tag of a field dynamically. This, however, can only be achieved in version 6.2 (and above). There is a post on this board that discusses this topic in greater detail.

No, formats and control breaks are not output in the XML stream. In my experience, however, this is not an issue as they can easily be defined as dynamics. This also makes for succinct code, as the structure of the document is all defined in one place.

Hi Darren,

Would you happen to know which post mentions setting the tag dynamically? I did a search but didn't have any luck. We're running on 6.2.

Thanks

[Vol Yip]

Hi Mike

I am a very big user of DDE's to produce reports in Excel and would be interested to hear about your problems. What version of Pro-IV and Excel do you use?

Regards

Steve

Hi Steve,

We're running on version 6.2, using Excel 2003 and 2007 (both on Windows XP and Windows Vista machines). We have 3 main areas of concern.

1. Occasionally the output will become garbled or complete rows of data will be missed.
2. On one of our Windows Vista machines, when the report completes, the function hangs (does not return to the menu). You have to ctrl + break to get back to the menu.
3. Speed. Perhaps we are not going about things as efficiently as possible but to output a 50 column report with 2000 rows of data takes atleast 2 minutes.

Hi all,

I have been writting a few DDE's to produce Excel too. (I am on PROIV4.6 and using Excel 2003). I am having the same concern on the speed too.

Apparently, one of my report produce 25 columns and 2000-3000 thousands row of data, it just takes age to finish (when compare to 2 minutes as Mkie mentioned. I can see the client screen, dumping a row at a time, and it takes hours to finishing dumping 3000 rows.

Do you guys having the problem? Or is that something I am doing wrong?

Regards,

Vol

[DARREN]

Hi All,

We've just recently started investigating outputting reports to Excel through XML (instead of using DDE - giving us too many issues) and have come across a few issues. If anyone has any suggestions, tips or words of warning it would be greatly appreciated. We're currently developing in 6.2.
Is it possible to dynamically set the "XML Tag" of a field? We have some reports where we would like to dynamically set the headings (i.e. month, year, etc.. based on user input).

Is it possible to output the page header (found in the statics tab of VIP/Developer) to XML?
I'm sure I had more questions but can't think of them at the moment.

Thanks.

Mike

We have used XML reporting with great success, although not in conjunction with spreadsheets. The answers to your questions are

Yes, it is possible to set the tag of a field dynamically. This, however, can only be achieved in version 6.2 (and above). There is a post on this board that discusses this topic in greater detail.

No, formats and control breaks are not output in the XML stream. In my experience, however, this is not an issue as they can easily be defined as dynamics. This also makes for succinct code, as the structure of the document is all defined in one place.

Hi Darren,

Would you happen to know which post mentions setting the tag dynamically? I did a search but didn't have any luck. We're running on 6.2.

Thanks

My bad. The thread that I was thinking of describes how to set the attributes for the XML objects dynamically. It is in the topic located at

http://www.proivrc.c...c...hl=xml&st=0

...but thinking about it, why would you want to change the tag name of an xml object. When is the client id not the client id. One of the great features of an xml document is that you can publish as much information as you want. The process that parses the xml document can then act accorindly based on values being defined or absent.

If it is a realy neccesity to conditionally print xml fields you can always jump to/jump over fields, as it is just a PROIV report at the end of the day.

[mikelandl]

Hi All,

We've just recently started investigating outputting reports to Excel through XML (instead of using DDE - giving us too many issues) and have come across a few issues. If anyone has any suggestions, tips or words of warning it would be greatly appreciated. We're currently developing in 6.2.
Is it possible to dynamically set the "XML Tag" of a field? We have some reports where we would like to dynamically set the headings (i.e. month, year, etc.. based on user input).

Is it possible to output the page header (found in the statics tab of VIP/Developer) to XML?
I'm sure I had more questions but can't think of them at the moment.

Thanks.

Mike

We have used XML reporting with great success, although not in conjunction with spreadsheets. The answers to your questions are

Yes, it is possible to set the tag of a field dynamically. This, however, can only be achieved in version 6.2 (and above). There is a post on this board that discusses this topic in greater detail.

No, formats and control breaks are not output in the XML stream. In my experience, however, this is not an issue as they can easily be defined as dynamics. This also makes for succinct code, as the structure of the document is all defined in one place.

Hi Darren,

Would you happen to know which post mentions setting the tag dynamically? I did a search but didn't have any luck. We're running on 6.2.

Thanks

My bad. The thread that I was thinking of describes how to set the attributes for the XML objects dynamically. It is in the topic located at

http://www.proivrc.c...c...hl=xml&st=0

...but thinking about it, why would you want to change the tag name of an xml object. When is the client id not the client id. One of the great features of an xml document is that you can publish as much information as you want. The process that parses the xml document can then act accorindly based on values being defined or absent.

If it is a realy neccesity to conditionally print xml fields you can always jump to/jump over fields, as it is just a PROIV report at the end of the day.

Hi Darren,

The only reason we want/need to dynamically change the xml tag is because, from my limited experience, it appears as though the xml tag is what is used to create the column headings when the xml document is opened in Excel. For the majority of our reports this would be fine but we do have a few reports where the headings are not known until run time (i.e. names of months and/or years) based on user input. Perhaps there is another way to go about defining the headings for Excel but I haven't found it yet. I should also note that for testing purposes I am currently using the copy.xsl transform file found in the virtual_machine/xsl folder which comes standard with a ProIV 6.2 install. I have absolutely no experience with xml transform files (I plan on reading up on them in the near future) so maybe there's something that could be done with them to solve our problem??

[Chris Mackenzie]

...but thinking about it, why would you want to change the tag name of an xml object.

When pro-iv truncates the tag name at 32 chars but your destination xml parser expects a longer tag name...
I had to do file search and replace on the xml file to work around it - ever done that on Windows? What fun...

[Wim Soutendijk]

Be aware of memory usage when you use xml transform files to excel sheets.
I converted some DDE reports which took hours and converted them into xml/xsl conversion. For a small date range, everything works ok (and fast), but for a larger date range (resulting in more than 20.000 rows) the conversion runs into a memory problem.
Without any error messages, the report just ends and doesn't produce the output excel sheet.

Wim

[mikelandl]

Be aware of memory usage when you use xml transform files to excel sheets.
I converted some DDE reports which took hours and converted them into xml/xsl conversion. For a small date range, everything works ok (and fast), but for a larger date range (resulting in more than 20.000 rows) the conversion runs into a memory problem.
Without any error messages, the report just ends and doesn't produce the output excel sheet.

Wim

Thanks for the warning Wim. I've yet to see any of our reports go much past 5000 rows but I'll certainly keep that in mind.

[Glenn Meyers]

We have found it considerably faster to write the data to a file and export the file to the users desk, then start excel and opening the file.
If you generate all of the Excel commands you will be needing and code them into a permenant file, you can call them in a lookup screen in your function by name or format, etc.
As a result, we are exporting spreadsheets into Exccel with formatting, bolds, cell text and background colors, etc at a fraction of the speed we experienced using DDE commands.

[mikelandl]

We have found it considerably faster to write the data to a file and export the file to the users desk, then start excel and opening the file.
If you generate all of the Excel commands you will be needing and code them into a permenant file, you can call them in a lookup screen in your function by name or format, etc.
As a result, we are exporting spreadsheets into Exccel with formatting, bolds, cell text and background colors, etc at a fraction of the speed we experienced using DDE commands.

Hi Glenn,

Are you outputting the data to a csv file? Are you opening the file with an Excel template? I've contemplated doing something like that in the past was but was always worried about the users deleting the template files.

[Glenn Meyers]

We write to a XML file format then command Excel to load it in that way.
3 steps in 3 callable functions:
Prep file using stored data cell formats on local machine.
Export and Write file to users computer
Open excel and import XML file.

Totally transparent to user. They get Excel popped up in their screen WITH the data in in.