Re: Export Form Data to Excel on Button Action (UNCLASSIFIED)
Hi Gordon, is there a way to do this on the web not having the User tool. I mean maybe there is a way using OLE and php or perl on a webpage? Is it? Serouche Bezhenar, Dmitry wrote: Hello Frank, Thank you for your useful answer. I have a question reg. item 7 in your list. How is it possible to tie a report, which exports data to application, to an Open Window action? There are only 3 options - screen, file and printer. Kind Regards / C ? Dmitry Bezhenar -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of FRANK, GORDON CTR DISA JSSC Sent: Monday, November 19, 2007 4:54 PM To: arslist@ARSLIST.ORG Subject: Re: Export Form Data to Excel on Button Action (UNCLASSIFIED) Classification: UNCLASSIFIED Caveats: NONE This can be done in the following way: 1. Create a DDE.ini file in your Home directory 2. Copy the following into the DDE.ini text file [excelrecord] Path=c:\excel\excel.exe Application=excel Topic=system Format=TAB XFRDATA=Clipboard Command1=[NEW(1)][PASTE()][SAVE.AS(,0)] 3. Enable Report to Application option is selected in the Options dialog box 4. Build and save a report in the Remedy User tool 5. Choose Report Export to Application from the Report menu bar 6. Your query and created report will be sent to Excel 7. Tie this report to an Open Window action in an Active Link with a Button (Permissions: Public) Below is a web link with explains this in more detail: http://service-it-remedy.web.cern.ch/service-it-remedy/clients/aruser/Re ports15.html A description is also available in the Remedy User Tool help. You can also execute Visual Basic code. To do this you will need to substitute VB for the Command1=[NEW(1)][PASTE()][SAVE.AS(,0)] see VB Help for more detail. You will have to work with relative addressing in Visual Basic (a recorded macro in VB will give you static addressing). Gordon M. Frank DISA\Version FNS -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Anesh A Kurian Sent: Monday, November 19, 2007 7:30 AM To: arslist@ARSLIST.ORG Subject: Export Form Data to Excel on Button Action ** Hi Listers, I require to perform a data export of a regular form to an excel sheet on click of a button. And also that it shouldnt require the user to interact other than clicking the button. I tried by using the Open window action to run a report but here again it requires the user to select a format to which the data has to be saved as. i require that the workflow automatically opens an excel sheet and displays the exported data on to the excel sheet. i would appreciate if some one could shed some light on this or if anyone has done the same to share the idea. thanks and regards Anesh __20060125___This posting was submitted with HTML in it___ Classification: UNCLASSIFIED Caveats: NONE ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Export Form Data to Excel on Button Action (UNCLASSIFIED)
Hello Frank, Thank you for your useful answer. I have a question reg. item 7 in your list. How is it possible to tie a report, which exports data to application, to an Open Window action? There are only 3 options - screen, file and printer. Kind Regards / C ? Dmitry Bezhenar -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of FRANK, GORDON CTR DISA JSSC Sent: Monday, November 19, 2007 4:54 PM To: arslist@ARSLIST.ORG Subject: Re: Export Form Data to Excel on Button Action (UNCLASSIFIED) Classification: UNCLASSIFIED Caveats: NONE This can be done in the following way: 1. Create a DDE.ini file in your Home directory 2. Copy the following into the DDE.ini text file [excelrecord] Path=c:\excel\excel.exe Application=excel Topic=system Format=TAB XFRDATA=Clipboard Command1=[NEW(1)][PASTE()][SAVE.AS(,0)] 3. Enable Report to Application option is selected in the Options dialog box 4. Build and save a report in the Remedy User tool 5. Choose Report Export to Application from the Report menu bar 6. Your query and created report will be sent to Excel 7. Tie this report to an Open Window action in an Active Link with a Button (Permissions: Public) Below is a web link with explains this in more detail: http://service-it-remedy.web.cern.ch/service-it-remedy/clients/aruser/Re ports15.html A description is also available in the Remedy User Tool help. You can also execute Visual Basic code. To do this you will need to substitute VB for the Command1=[NEW(1)][PASTE()][SAVE.AS(,0)] see VB Help for more detail. You will have to work with relative addressing in Visual Basic (a recorded macro in VB will give you static addressing). Gordon M. Frank DISA\Version FNS -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Anesh A Kurian Sent: Monday, November 19, 2007 7:30 AM To: arslist@ARSLIST.ORG Subject: Export Form Data to Excel on Button Action ** Hi Listers, I require to perform a data export of a regular form to an excel sheet on click of a button. And also that it shouldnt require the user to interact other than clicking the button. I tried by using the Open window action to run a report but here again it requires the user to select a format to which the data has to be saved as. i require that the workflow automatically opens an excel sheet and displays the exported data on to the excel sheet. i would appreciate if some one could shed some light on this or if anyone has done the same to share the idea. thanks and regards Anesh __20060125___This posting was submitted with HTML in it___ Classification: UNCLASSIFIED Caveats: NONE ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Export Form Data to Excel on Button Action
Hi listers, Thank you to all for their prompt reply, to my earlier posting on how to export data to excel. A key point I forgot to mention is that the application runs on web so many it leaves me with lesser options. I was exploring on DDE and OLE to my disappointment I found later that these are not supported over web. Then I switched to running a report using the Open Window action on an Active Link. But here too I face a problem that the user has to rename the file from filename.rep to filename.xls and do a save. Is there any way I can force it always to save with a .xls. I have seen a same posting on the same requirement. Hope to see some ideas. Thanks and regards, Anesh A Kurian __20060125___This posting was submitted with HTML in it___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Export Form Data to Excel on Button Action
Anesh, I can answer this indirectly... In the last paragraph in my previous email I forgot to mention that when you create the Excel sheet on the server it is then also available on the web. Since the Excel sheet will end up in an attachment field on some form, your client (web or Windows) can simply do a Set Fields to pull it to the client. I use this here so web users can generate the same reports as the WUT users. Stephen Remedy Skilled Professional From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Anesh A Kurian Sent: Tuesday, November 20, 2007 11:04 AM To: arslist@ARSLIST.ORG Subject: Re: Export Form Data to Excel on Button Action Hi listers, Thank you to all for their prompt reply, to my earlier posting on how to export data to excel. A key point I forgot to mention is that the application runs on web so many it leaves me with lesser options. I was exploring on DDE and OLE to my disappointment I found later that these are not supported over web. Then I switched to running a report using the Open Window action on an Active Link. But here too I face a problem that the user has to rename the file from filename.rep to filename.xls and do a save. Is there any way I can force it always to save with a .xls. I have seen a same posting on the same requirement. Hope to see some ideas. Thanks and regards, Anesh A Kurian __20060125___This posting was submitted with HTML in it___ __20060125___This posting was submitted with HTML in it___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Export Form Data to Excel on Button Action (UNCLASSIFIED)
Classification: UNCLASSIFIED Caveats: NONE You could save a field which always has .xls in it and append it to the name Sort of like below $PATH$ + $filename$ + $excel extension$ C:\test\ myfilename.xls Becomes C:\test\myfilename.xls Just a thought Gordon M. Frank DISA\Version FNS -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Anesh A Kurian Sent: Tuesday, November 20, 2007 11:04 AM To: arslist@ARSLIST.ORG Subject: Re: Export Form Data to Excel on Button Action ** Hi listers, Thank you to all for their prompt reply, to my earlier posting on how to export data to excel. A key point I forgot to mention is that the application runs on web so many it leaves me with lesser options. I was exploring on DDE and OLE to my disappointment I found later that these are not supported over web. Then I switched to running a report using the Open Window action on an Active Link. But here too I face a problem that the user has to rename the file from filename.rep to filename.xls and do a save. Is there any way I can force it always to save with a .xls. I have seen a same posting on the same requirement. Hope to see some ideas. Thanks and regards, Anesh A Kurian __20060125___This posting was submitted with HTML in it___ __20060125___This posting was submitted with HTML in it___ Classification: UNCLASSIFIED Caveats: NONE ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Export Form Data to Excel on Button Action
Hi Anesh, I did this by creating a hidden view field and a button for generating excel. When you click the button, it sets the view field to a php page that uses a simple ODBC call to pull the data back from Remedy and generate Microsoft excel xml output. The following php calls will popup the Open/Save/Cancel dialog for the excel file. snippet from php that will open the form on the button click # This line will stream the file to the user rather than spray it across the screen header(Content-type: application/octet-stream); # replace excelfile.xls with whatever you want the filename to default to header(Content-Disposition: attachment; filename=\my_excel_report_$today.xls\); header(Pragma: no-cache); header(Expires: 0); If you haven't done excel xml, all you need to do is create an excel file manually. Add colors, text, formatting, etc. Save it as xml, and then view it in an editor like notepad. That will show you what you need to print using php. Hope that helps, Chris. Anesh A Kurian [EMAIL PROTECTED] Sent by: Action Request System discussion list(ARSList) arslist@ARSLIST.ORG 11/19/2007 07:31 AM Please respond to arslist@ARSLIST.ORG To arslist@ARSLIST.ORG cc Subject Export Form Data to Excel on Button Action ** Hi Listers, I require to perform a data export of a regular form to an excel sheet on click of a button. And also that it shouldnt require the user to interact other than clicking the button. I tried by using the Open window action to run a report but here again it requires the user to select a format to which the data has to be saved as. i require that the workflow automatically opens an excel sheet and displays the exported data on to the excel sheet. i would appreciate if some one could shed some light on this or if anyone has done the same to share the idea. thanks and regards Anesh __20060125___This posting was submitted with HTML in it___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Export Form Data to Excel on Button Action
Anesh, I have a lot of reports that are generated in Excel format by clicking a button. There are different approaches to accomplishing this. Here are several: The first is the easiest but requires the user to copy/paste the results into a new Excel sheet. In an active link loop through the records you want to include in the report and concatenate the results into a 0-length display-only temp character field. In between each column insert a Tab character. You can get a tab character by using a single Set Fields SQL of SELECT CHAR(9) and storing the result into a temp field. The temp field can then be referenced in your Set Fields to your 0-length temp field while looping. The user would then open Excel and copy/paste the contents of the 0-length temp field into a new sheet. Because it is tab-delimited each field in the temp field will be placed in a separate column in the sheet. Note that as you are looping through the records you will need to replace any tab or return characters in the Remedy fields - because Excel will interpret these characters as column separators or new line characters. Another approach to this is to call a SQL function (that you create) which processes the records and returns a varchar value that contains the entire report. Calling a function has limitations. In SQL Server the varchar data type is limited to 8000 characters. If this is too limited then you could call a stored procedure (that you create) which processes the records in the format you want but instead of returning the text directly to your workflow it adds records to a temp SQL table which is used exclusively for reports. After this table is populated your workflow would then query this table (by a unique Id). In order for multiple users to create reports simultaneously a unique Id (ie. GUID) would need to be passed into your stored procedure (along with any report parameters (ie. data range, Category, Type, Item, Requester, etc.) These approaches will generate the text formatted for Excel. Now, to get the system to automatically create an Excel sheet with the report contents... Somehow you need to create a file that contains the report contents with the file extension of .txt. This is necessary otherwise Excel will try to convert numbers to numeric values and remove any leading zeros. You could create this file either from the client (active links) or at the server (filters). On the client you could loop through each line of the report and ECHO the results to a text file on the client's computer. I prefer the server approach for several reasons whereby, after populating the temp SQL table with report data, I run the bcp.exe (SQL Server) command line utility to query the table and create a text file on the server. The next command in the filter is to run a $PROCESS$ PERFORM-ACTION-ADD-ATTACHMENT to attach the text file to an attachment field. In order to get the client to request the report I PUSH a new record to a command form. The next command in the active link is a Set Fields using $LASTID$ to retrieve the attachment from the new record. Now we have the report in a text file on the client. The next step is to load Excel with this file... At some point before you generate reports (I do this when the main control panel is opened when the user logs in) you will need to set two Global character fields. The first is the full path to the %TMP% or %TEMP% environment variables. This folder is where the Remedy Windows User Tool stores the attachments when you Set Fields to an attachment field. The other Global field will store the full path to Excel.exe. There are multiple ways to obtain this value. *One option is via a free utility on ARS Wiki (dotnetutil) named WksInfo. With these two values set a temp field ($zTmp Char1$) to the value of $Global - Excel$ + + $Global - TMP$ + \ + $zTmp Attachment$ Then, use a run process command using the $zTmp Char1$ field. Note that if there are spaces in the folder names you will need to surround them with double-quotes. I prefer to use the 8.3 names for the folders because you don't need the additional double-quotes. That should create the file you want and the user will see a new Excel sheet just by clicking a button. To take this one step further, you could create the Excel file on the server and then use PERFORM-ACTION-ADD-ATTACHMENT to add the Excel sheet directly. This approach has the advantage of being able to email the report to the user, if you want. It also provides the ability to schedule these reports so that they are auto-generated every Monday morning (for example) and emailed to various users. HTH Stephen Remedy Skilled Professional From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Anesh A Kurian Sent: Monday, November 19, 2007 7:30 AM To: arslist@ARSLIST.ORG Subject: Export Form
Re: Export Form Data to Excel on Button Action (UNCLASSIFIED)
Classification: UNCLASSIFIED Caveats: NONE This can be done in the following way: 1. Create a DDE.ini file in your Home directory 2. Copy the following into the DDE.ini text file [excelrecord] Path=c:\excel\excel.exe Application=excel Topic=system Format=TAB XFRDATA=Clipboard Command1=[NEW(1)][PASTE()][SAVE.AS(,0)] 3. Enable Report to Application option is selected in the Options dialog box 4. Build and save a report in the Remedy User tool 5. Choose Report Export to Application from the Report menu bar 6. Your query and created report will be sent to Excel 7. Tie this report to an Open Window action in an Active Link with a Button (Permissions: Public) Below is a web link with explains this in more detail: http://service-it-remedy.web.cern.ch/service-it-remedy/clients/aruser/Re ports15.html A description is also available in the Remedy User Tool help. You can also execute Visual Basic code. To do this you will need to substitute VB for the Command1=[NEW(1)][PASTE()][SAVE.AS(,0)] see VB Help for more detail. You will have to work with relative addressing in Visual Basic (a recorded macro in VB will give you static addressing). Gordon M. Frank DISA\Version FNS -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Anesh A Kurian Sent: Monday, November 19, 2007 7:30 AM To: arslist@ARSLIST.ORG Subject: Export Form Data to Excel on Button Action ** Hi Listers, I require to perform a data export of a regular form to an excel sheet on click of a button. And also that it shouldnt require the user to interact other than clicking the button. I tried by using the Open window action to run a report but here again it requires the user to select a format to which the data has to be saved as. i require that the workflow automatically opens an excel sheet and displays the exported data on to the excel sheet. i would appreciate if some one could shed some light on this or if anyone has done the same to share the idea. thanks and regards Anesh __20060125___This posting was submitted with HTML in it___ Classification: UNCLASSIFIED Caveats: NONE ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Export Form Data to Excel on Button Action
Hi Chris Thanks for the prompt reply I shall try and get back to you on the same. Anesh A Kurian From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Chris Ingalls Sent: Monday, November 19, 2007 6:56 PM To: arslist@ARSLIST.ORG Subject: Re: Export Form Data to Excel on Button Action ** Hi Anesh, I did this by creating a hidden view field and a button for generating excel. When you click the button, it sets the view field to a php page that uses a simple ODBC call to pull the data back from Remedy and generate Microsoft excel xml output. The following php calls will popup the Open/Save/Cancel dialog for the excel file. snippet from php that will open the form on the button click # This line will stream the file to the user rather than spray it across the screen header(Content-type: application/octet-stream); # replace excelfile.xls with whatever you want the filename to default to header(Content-Disposition: attachment; filename=\my_excel_report_$today.xls\); header(Pragma: no-cache); header(Expires: 0); If you haven't done excel xml, all you need to do is create an excel file manually. Add colors, text, formatting, etc. Save it as xml, and then view it in an editor like notepad. That will show you what you need to print using php. Hope that helps, Chris. Anesh A Kurian [EMAIL PROTECTED] Sent by: Action Request System discussion list(ARSList) arslist@ARSLIST.ORG 11/19/2007 07:31 AM Please respond to arslist@ARSLIST.ORG To arslist@ARSLIST.ORG cc Subject Export Form Data to Excel on Button Action ** Hi Listers, I require to perform a data export of a regular form to an excel sheet on click of a button. And also that it shouldnt require the user to interact other than clicking the button. I tried by using the Open window action to run a report but here again it requires the user to select a format to which the data has to be saved as. i require that the workflow automatically opens an excel sheet and displays the exported data on to the excel sheet. i would appreciate if some one could shed some light on this or if anyone has done the same to share the idea. thanks and regards Anesh __20060125___This posting was submitted with HTML in it___ __20060125___This posting was submitted with HTML in it___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Export Form Data to Excel on Button Action
I love seeing the various methods used to get things. I have seen Jasper Reports used in conjunction with iReport (GUI Front end) to generate actual XLS files. iReport provides a GUI front end similar to Crystal Reports to create reports, and can be run on the command line on the server to generate the report. At this shop the report was put into a directory available on a web server for retrieval by the customer. _ From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Heider, Stephen Sent: Monday, November 19, 2007 6:34 AM To: arslist@ARSLIST.ORG Subject: Re: Export Form Data to Excel on Button Action ** Anesh, I have a lot of reports that are generated in Excel format by clicking a button. There are different approaches to accomplishing this. Here are several: The first is the easiest but requires the user to copy/paste the results into a new Excel sheet. In an active link loop through the records you want to include in the report and concatenate the results into a 0-length display-only temp character field. In between each column insert a Tab character. You can get a tab character by using a single Set Fields SQL of SELECT CHAR(9) and storing the result into a temp field. The temp field can then be referenced in your Set Fields to your 0-length temp field while looping. The user would then open Excel and copy/paste the contents of the 0-length temp field into a new sheet. Because it is tab-delimited each field in the temp field will be placed in a separate column in the sheet. Note that as you are looping through the records you will need to replace any tab or return characters in the Remedy fields - because Excel will interpret these characters as column separators or new line characters. Another approach to this is to call a SQL function (that you create) which processes the records and returns a varchar value that contains the entire report. Calling a function has limitations. In SQL Server the varchar data type is limited to 8000 characters. If this is too limited then you could call a stored procedure (that you create) which processes the records in the format you want but instead of returning the text directly to your workflow it adds records to a temp SQL table which is used exclusively for reports. After this table is populated your workflow would then query this table (by a unique Id). In order for multiple users to create reports simultaneously a unique Id (ie. GUID) would need to be passed into your stored procedure (along with any report parameters (ie. data range, Category, Type, Item, Requester, etc.) These approaches will generate the text formatted for Excel. Now, to get the system to automatically create an Excel sheet with the report contents. Somehow you need to create a file that contains the report contents with the file extension of .txt. This is necessary otherwise Excel will try to convert numbers to numeric values and remove any leading zeros. You could create this file either from the client (active links) or at the server (filters). On the client you could loop through each line of the report and ECHO the results to a text file on the client's computer. I prefer the server approach for several reasons whereby, after populating the temp SQL table with report data, I run the bcp.exe (SQL Server) command line utility to query the table and create a text file on the server. The next command in the filter is to run a $PROCESS$ PERFORM-ACTION-ADD-ATTACHMENT to attach the text file to an attachment field. In order to get the client to request the report I PUSH a new record to a command form. The next command in the active link is a Set Fields using $LASTID$ to retrieve the attachment from the new record. Now we have the report in a text file on the client. The next step is to load Excel with this file. At some point before you generate reports (I do this when the main control panel is opened when the user logs in) you will need to set two Global character fields. The first is the full path to the %TMP% or %TEMP% environment variables. This folder is where the Remedy Windows User Tool stores the attachments when you Set Fields to an attachment field. The other Global field will store the full path to Excel.exe. There are multiple ways to obtain this value. *One option is via a free utility on ARS Wiki (dotnetutil) named WksInfo. With these two values set a temp field ($zTmp Char1$) to the value of $Global - Excel$ + + $Global - TMP$ + \ + $zTmp Attachment$ Then, use a run process command using the $zTmp Char1$ field. Note that if there are spaces in the folder names you will need to surround them with double-quotes. I prefer to use the 8.3 names for the folders because you don't need the additional double-quotes. That should create the file you want and the user will see a new Excel sheet just by clicking a button. To take
