Jump to content


Photo
- - - - -

Writing reports to Excel through XML


17 replies to this topic

#1 mikelandl

mikelandl

    Expert

  • Members
  • PipPipPipPip
  • 101 posts

Posted 15 May 2009 - 07:37 PM

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.


#2 Guest_Steve H_*

Guest_Steve H_*
  • Guests

Posted 16 May 2009 - 08:45 AM

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

#3 DARREN

DARREN

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 395 posts
  • Gender:Male
  • Location:Florida,USA

Posted 21 May 2009 - 01:21 PM

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.
Things should be made as simple as possible, but not simpler

#4 mikelandl

mikelandl

    Expert

  • Members
  • PipPipPipPip
  • 101 posts

Posted 21 May 2009 - 07:33 PM

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.

#5 mikelandl

mikelandl

    Expert

  • Members
  • PipPipPipPip
  • 101 posts

Posted 21 May 2009 - 07:48 PM

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

#6 Vol Yip

Vol Yip

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 393 posts
  • Gender:Male
  • Location:Hong Kong

Posted 22 May 2009 - 03:54 AM

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

#7 Guest_Steve H_*

Guest_Steve H_*
  • Guests

Posted 22 May 2009 - 08:31 AM

Hi All

We are running Pro-IV 5.5 using the 6.2 client with up to 150 users on Red hat Linux. Clients all run Windows XP with Excel 2003. I have done 3 test runs

Test 1 - 1,156 rows with 90 columns - time 95 seconds
Test 2 - 1,650 rows with 90 columns - time 110 seconds
Test 3 - 2,017 rows with 90 columns - time 109 seconds

I use an update function to build the report as a memory workfile, keyed on terminal id and a record count. The fields are all put into an array 40 characters long with a dimension of 120. A separate global update does the DDE's calls. I have experienced the faults you all describe in the past but the addition of the following logic made the update work 100% of the time so far!

#CTR += 1
RND(0)
#PER = (#CTR / @#COM10) * 100
IF #PER = 100 THEN
$PER = '100'
ELSE
IF #PER < 10 THEN
$PER = PIC(#PER,'9')
ELSE
$PER = PIC(#PER,'99')
ENDIF
ENDIF
IF $PER # $OLD THEN
UMSG('Building Spreadsheet - ' + $PER + '% complete ....')
$OLD = $PER
ENDIF

However I have a similar function which reads data from Excel. Using the 5.5 client it works 100% of the time but using the 6.2 client the function does not work at all!!!

Somebody described programming in Pro-IV as witchcraft - sometimes it works in strange ways.

Cheers

Steve

#8 DARREN

DARREN

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 395 posts
  • Gender:Male
  • Location:Florida,USA

Posted 22 May 2009 - 12:44 PM

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.
Things should be made as simple as possible, but not simpler

#9 mikelandl

mikelandl

    Expert

  • Members
  • PipPipPipPip
  • 101 posts

Posted 22 May 2009 - 01:02 PM

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??

#10 Chris Mackenzie

Chris Mackenzie

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 368 posts
  • Gender:Male
  • Location:Bristol, United Kingdom

Posted 22 May 2009 - 01:59 PM

...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...
The content and views expressed in this message are those
of the poster and do not represent those of any organisation.

#11 Wim Soutendijk

Wim Soutendijk

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 211 posts
  • Gender:Male
  • Location:Netherlands

Posted 26 May 2009 - 06:34 AM

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

#12 mikelandl

mikelandl

    Expert

  • Members
  • PipPipPipPip
  • 101 posts

Posted 26 May 2009 - 12:06 PM

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.

#13 Glenn Meyers

Glenn Meyers

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 221 posts
  • Gender:Male
  • Location:St. Louis, MO, United States
  • Interests:I also raise African Gray Parrots and build hot rod automobiles.

Posted 26 May 2009 - 01:23 PM

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.

#14 mikelandl

mikelandl

    Expert

  • Members
  • PipPipPipPip
  • 101 posts

Posted 26 May 2009 - 03:07 PM

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.

#15 Glenn Meyers

Glenn Meyers

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 221 posts
  • Gender:Male
  • Location:St. Louis, MO, United States
  • Interests:I also raise African Gray Parrots and build hot rod automobiles.

Posted 26 May 2009 - 05:32 PM

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.



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users