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: [email protected] 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 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: [email protected] 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"
