SaveAs on Composite DW?
Travis Hettenbach
tried an Excel file and all he got was an 'a' in the A1 cell of Excel...
Is there a way to Save a Composite DW as an external file?
--
Travis H.
Software Developer Analyst
hett...@kochind.com
Travis Hettenbach
format...
If anyone has any other suggestions on how I might get it to Excel, I'll be
happy to listen. I'm in PB6.5 btw.
Travis Hettenbach <hett...@kochind.com> wrote in article
<01bf681c$8b746fa0$2a1ed192@kii-205592>...
DawnBrownEyes[TeamSybase]
I wrote the following article to export crosstab datawindows that are
stored inside composites and nested crosstabs.
You would use similar code except eliminate the staticmode, crosstabdata...
use the data property & the file functions: ls_string =
string(dw_1.object.dwc_1.object.dwc_1.object.datawindow.data)
This is the article:
How do you export a crosstab datawindow and have it be WYSIWYG?
The easiest way to export a crosstab datawindow is to save it in html
format. The newer Excel, Word97 and 2000 bring it up and it looks like the
crosstab. You are able to see the headings and totals.
To export using script, put the datawindow in static mode after you
retrieve the data. While the dynamic crosstab is in static mode, you can
manipulate the properties of individual columns. When you are finished
exporting set the static mode back to No, in order for the SaveAs function
to work.
When you use Save As to export a crosstab, notice that the column headings
have val_1, val_2 and so on, in the heading, instead of the dates or other
column values. To get the values of the columns, use the crosstab data
property. This property is a string containing a tab-separated list of the
expressions used to calculate the values of columns in a crosstab
DataWindow.
Sometimes the crosstab is nested within a composite. To get the properties,
use dot notation. This has the format of
dw_1.object.childdatawindowname.object.property or
dw_1.object.childdatawindowname.object.anotherchilddatawindowname.property.
Sometimes the crosstab is nested inside a non-composite datawindow. This
may happen if you have a crosstab with retrieval arguments and it has to be
nested in a non-composite datawindow. The error message, "Crosstab nested
report d_reportname has retrieval arguments and can be used only in
composites", message will appear if you nest a composite report with
retrieval arguments in a non-composite datawindow. For this type of
datawindow there may be many rows therefore the rownumber must be specified
for the nested childdatawindows. Example code is included at the end. An
example of this format is:
dw_1.object.dwc_3[ll_row].object.dwc_1.object.datawindow.crosstab.staticmode
='Yes'
These values can be written to a text file using the FileOpen, FileWrite,
and FileClose functions. Then use Excel or Word to read the text file.
When you create the file, create it using the StreamMode option.
StreamMode will write the file in 32K chunks instead of one line at a time.
Use the fileexists function to check if a directory exists.
A '~t' is need for each field specified for the row. Otherwise the column
heading will start above the row descriptions. In this example ls_string =
'~t' + string( return value from crosstabdata )
Using FileWrite to send char (10)+char (13) sends a carriage control +
linefeed and there will be blank rows before and after the headings.
In the following example a composite datawindow contains two crosstabs.
The customer wants to export it to a file and use it in Excel. The totals
are not needed in the exported text file.
integer li_FileNum
string ls_string, ls_dir, ls_ext
if fileexists(is_dir+'\.') then ls_ext = is_dir+'\' else ls_ext = 'c:\'
//check if directory exists
li_FileNum = FileOpen( ls_ext + "emp.txt", streamMode!, Write!, Shared!,
Replace!)
dw_1.object.dwc_1.object.dwc_1.object.datawindow.crosstab.staticmode='Yes'
ls_string =
'~t'+string(dw_1.object.dwc_1.object.dwc_1.object.datawindow.table.crosstabd
ata)
FileWrite(li_FileNum, ls_string)
FileWrite(li_filenum, char(10)+char(13))
ls_string = string(dw_1.object.dwc_1.object.dwc_1.object.datawindow.data)
FileWrite(li_FileNum, ls_string)
FileWrite(li_filenum, char(10)+char(13))
dw_1.object.dwc_1.object.dwc_1.object.datawindow.crosstab.staticmode='No'
dw_1.object.dwc_2.object.dwc_1.object.datawindow.crosstab.staticmode='Yes'
ls_string =
'~t'+string(dw_1.object.dwc_2.object.dwc_1.object.datawindow.table.crosstabd
ata)
FileWrite(li_FileNum, ls_string)
FileWrite(li_filenum, char(10)+char(13))
ls_string = string(dw_1.object.dwc_2.object.dwc_1.object.datawindow.data)
FileWrite(li_FileNum, ls_string)
FileWrite(li_filenum, char(10)+char(13))
dw_1.object.dwc_2.object.dwc_1.object.datawindow.crosstab.staticmode='No'
FileClose(li_filenum)
The following is an example of a crosstab datawindow nested inside a
non-composite datawindow. In this datawindow there are two nested
crosstabs. The main report retrieves profit center information and for
each profit center there is data summarized in crosstab datawindows. This
code scrolls through each row and exports the data out to a text file.
Make sure you set the staticmode back to No.
ll_max = dw_1.rowcount()
for ll_row = 1 to ll_max
dw_1.object.dwc_2[ll_row].object.dwc_1.object.datawindow.crosstab.staticmode
='Yes'
ls_columns =
'~t~t'+string(dw_1.object.dwc_2[ll_row].object.dwc_1.object.datawindow.table
crosstabdata)
ls_data =
string(dw_1.object.dwc_2[ll_row].object.dwc_1.object.datawindow.data)
FileWrite(li_FileNum, ls_columns)
FileWrite(li_filenum, char(10)+char(13))
FileWrite(li_FileNum, ls_data)
FileWrite(li_filenum, char(10)+char(13))
dw_1.object.dwc_3[ll_row].object.dwc_1.object.datawindow.crosstab.staticmode
='Yes'
ls_columns =
'~t'+string(dw_1.object.dwc_3[ll_row].object.dwc_1.object.datawindow.table.c
rosstabdata)
ls_data =
string(dw_1.object.dwc_3[ll_row].object.dwc_1.object.datawindow.data)
FileWrite(li_FileNum, ls_columns)
FileWrite(li_filenum, char(10)+char(13))
FileWrite(li_FileNum, ls_data)
FileWrite(li_filenum, char(10)+char(13))
dw_1.object.dwc_2[ll_row].object.dwc_1.object.datawindow.crosstab.staticmode
='No'
dw_1.object.dwc_3[ll_row].object.dwc_1.object.datawindow.crosstab.staticmode
='No'
next
--
Dawn T Brown Eyes (TeamSybase)
Travis Hettenbach <hett...@kochind.com> wrote in article
<01bf681c$8b746fa0$2a1ed192@kii-205592>...
> I'm trying to allow my user to save their reports as Excel files... He
> tried an Excel file and all he got was an 'a' in the A1 cell of Excel...
> Is there a way to Save a Composite DW as an external file?