Hi,

I have a requirement where in i need to display some of the columns in my report in the below format(EXCEL) :

Rx #
723456784503,
723456784503,
xxxxxxxxxxxxx and so on.

hence i first print it in HTMTABLE format and then reset the it to XLS.But i need to display the excelsheet in the following name "Recall Report".Hence i tried to use TITLETEXT but did not work.
Please see the code snippet below :

  
TABLE FILE XXX
PRINT 
COMPUTE RXFLD/A1000V = IF PTNT_I NE LAST PTNT_I THEN ASGN_RX_I ELSE RXFLD || (',<br> ' | ASGN_RX_I);
COMPUTE DRUGFLD/A1000V = IF PTNT_I NE LAST PTNT_I THEN PHARM_PROD_N ELSE DRUGFLD || (',<br> ' | PHARM_PROD_N);
COMPUTE NDCFLD/A1000V = IF PTNT_I NE LAST PTNT_I THEN PROD_I ELSE NDCFLD || (',<br> ' | PROD_I);
COMPUTE STRFLD/A1000V = IF PTNT_I NE LAST PTNT_I THEN STR_I ELSE STRFLD || (',<br> ' | STR_I);
COMPUTE LFDFLD/A1000V = IF PTNT_I NE LAST PTNT_I THEN HCNVRT(fill, '(HMDYY)', 20, 'A20') ELSE LFDFLD || (',<br> ' | HCNVRT(fill, '(HMDYY)', 20, 'A20'));
COMPUTE LOTFLD/A1000V = IF PTNT_I NE LAST PTNT_I THEN LOT ELSE LOTFLD || (',<br> ' | LOT);
ON TABLE HOLD AS YYY
END

AFter this i convert it into HTMTABLE and then reset it to XLS.

  
TABLE FILE YYY
PRINT
RX AS 'Rx #'
DRUGFLD1 AS 'Drug Name'
NDC1 AS 'NDC'
LFDFLD1 AS '<BR>Latest <BR>Fill Date'
STRFLD1 AS 'Store'
LOTFLD1 AS '<BR>Lot<BR>Number'
DSPFLD AS '# of<BR> Dispensed <BR>Events'
ON TABLE SET PAGE-NUM OFF
ON TABLE HOLD AS REPORT FORMAT HTMTABLE
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
TYPE=TITLE,CLASS=title_valign,$
TYPE=REPORT,COLUMN=N2,WRAP=0.500,TITLETEXT='Pharmacy Recall Report',$
ENDSTYLE
END
-GOTO PRINTRPT
-NODATA
-SET &&RPT_NAME='Pharmacy Recall Report';
-SET &&ERR_MSG='No Data Found!';
-INCLUDE rrxcoerr
-RUN
-PRINTRPT
SET HTMLFORMTYPE=XLS
-RUN

Hence while resetting it i tried to do the styling for the report and applied TITLETEXT .Although it worked all the formatting went off like for example wherever i use
, it displays
rather than separating it out in another line .

Lastly tried code snippet

  
TABLE FILE YYY
PRINT
RX AS 'Rx #'
DRUGFLD1 AS 'Drug Name'
NDC1 AS 'NDC'
LFDFLD1 AS '<BR>Latest <BR>Fill Date'
STRFLD1 AS 'Store'
LOTFLD1 AS '<BR>Lot<BR>Number'
DSPFLD AS '# of<BR> Dispensed <BR>Events'
ON TABLE SET PAGE-NUM OFF
ON TABLE HOLD AS REPORT FORMAT HTMTABLE
ON TABLE SET HTMLCSS ON
END
-GOTO PRINTRPT
-NODATA
-SET &&RPT_NAME='Pharmacy Recall Report';
-SET &&ERR_MSG='No Data Found!';
-INCLUDE rrxcoerr
-RUN
-PRINTRPT
TABLE FILE REPORT
PRINT
*
ON TABLE PCHOLD AS REPORT FORMAT EXL2K
ON TABLE SET STYLE *
TYPE=TITLE,CLASS=title_valign,$
TYPE=REPORT,COLUMN=N2,WRAP=0.500,TITLETEXT='Pharmacy Recall Report',$
ENDSTYLE
END
-RUN

Infact it does not display the required data,displays some non-related data REPORTID
REPORTNUM
BASETABLENAME
APPNAME
RUNTYPE .

Appreciate your help !!!!


Thanks!
Janani

This message has been edited. Last edited by: Kerry,

sigh, where to start.
look, you're saving an HTML table, and then pretending you can read it, TABLE FILE against it as tho it were a datafile.
so you're getting gibberish.
which is exactly what you should be getting.

Hi,

is there a way to get the line breaks in EXCEL format like below other than using
in HTMTABLE format.

Rx # ->column heading
723456784503,
723456784503,
xxxxxxxxxxxxx and so on.

Appreciate your help.


Thanks!
Janani

Replace the <BR>'s with commmas for get the line breaks in the titles:

LFDFLD1 AS '<BR>Latest <BR>Fill Date'

Could be:

LFDFLD1 AS ',Latest,Fill Date'

Or, set the column titles in Dev Studio as desired.

The blank lines can be a PRINT of a DEFINEd field, where that DEFINEd field is a single space or a null.

hi,

Doug , thanks for the help
But the line breaks that am trying is done in the below code :

 

TABLE FILE XXX
PRINT 
COMPUTE RXFLD/A1000V = IF PTNT_I NE LAST PTNT_I THEN ASGN_RX_I ELSE RXFLD || (',<br> ' | ASGN_RX_I);
COMPUTE DRUGFLD/A1000V = IF PTNT_I NE LAST PTNT_I THEN PHARM_PROD_N ELSE DRUGFLD || (',<br> ' | PHARM_PROD_N);
COMPUTE NDCFLD/A1000V = IF PTNT_I NE LAST PTNT_I THEN PROD_I ELSE NDCFLD || (',<br> ' | PROD_I);
COMPUTE STRFLD/A1000V = IF PTNT_I NE LAST PTNT_I THEN STR_I ELSE STRFLD || (',<br> ' | STR_I);
COMPUTE LFDFLD/A1000V = IF PTNT_I NE LAST PTNT_I THEN HCNVRT(fill, '(HMDYY)', 20, 'A20') ELSE LFDFLD || (',<br> ' | HCNVRT(fill, '(HMDYY)', 20, 'A20'));
COMPUTE LOTFLD/A1000V = IF PTNT_I NE LAST PTNT_I THEN LOT ELSE LOTFLD || (',<br> ' | LOT);
ON TABLE HOLD AS YYY
END
 

Example:

|| (',
' | ASGN_RX_I);

i concatentae it here . Hence while trying the EXL2K format
does not work , instead it appears like

123456,789000 (in teh same line )

Appreciate your help!

Thanks!
Janani

sigh, where to start continue from... I'm not sure what you're looking for? Did you find a solution? If not, please elaborate on your question...

Hi,

I did not find a solution.Let me elaborate on what i need.Below are the details :

1)Need to display report in EXL2K format.
2)Need to display certain columns in the below format
RX#
------
122444,
764764,
218782

Hence in order to bring the report in the above formats, we used HTMTABLE and the reset it to XLS.
Please see the code snippet below :

In the below file (XXX),teh concatenation of the commas and line breaks are done.

  
TABLE FILE XXX
PRINT 
COMPUTE RXFLD/A1000V = IF PTNT_I NE LAST PTNT_I THEN ASGN_RX_I ELSE RXFLD || (',<br> ' | ASGN_RX_I);
COMPUTE DRUGFLD/A1000V = IF PTNT_I NE LAST PTNT_I THEN PHARM_PROD_N ELSE DRUGFLD || (',<br> ' | PHARM_PROD_N);
COMPUTE NDCFLD/A1000V = IF PTNT_I NE LAST PTNT_I THEN PROD_I ELSE NDCFLD || (',<br> ' | PROD_I);
COMPUTE STRFLD/A1000V = IF PTNT_I NE LAST PTNT_I THEN STR_I ELSE STRFLD || (',<br> ' | STR_I);
COMPUTE LFDFLD/A1000V = IF PTNT_I NE LAST PTNT_I THEN HCNVRT(fill, '(HMDYY)', 20, 'A20') ELSE LFDFLD || (',<br> ' | HCNVRT(fill, '(HMDYY)', 20, 'A20'));
COMPUTE LOTFLD/A1000V = IF PTNT_I NE LAST PTNT_I THEN LOT ELSE LOTFLD || (',<br> ' | LOT);
ON TABLE HOLD AS YYY
END

After this i convert it into HTMTABLE and then reset it to XLS.

  

TABLE FILE YYY
PRINT
RX AS 'Rx #'
DRUGFLD1 AS 'Drug Name'
NDC1 AS 'NDC'
LFDFLD1 AS '<BR>Latest <BR>Fill Date'
STRFLD1 AS 'Store'
LOTFLD1 AS '<BR>Lot<BR>Number'
DSPFLD AS '# of<BR> Dispensed <BR>Events'
ON TABLE SET PAGE-NUM OFF
ON TABLE HOLD AS REPORT FORMAT HTMTABLE
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
TYPE=TITLE,CLASS=title_valign,$
TYPE=REPORT,COLUMN=N2,WRAP=0.500,$
ENDSTYLE
END
-GOTO PRINTRPT
-NODATA
-SET &&RPT_NAME='Pharmacy Recall Report';
-SET &&ERR_MSG='No Data Found!';
-INCLUDE rrxcoerr
-RUN
-PRINTRPT
SET HTMLFORMTYPE=XLS
-RUN

Hence till now everything works fine. But i have a new requirement where in the worsheet name needs to be changed from "WFServlet" to "Pharmacy Recall report". Am unable to do this with the above code.Hence i need a way to bring in the line breaks in EXL2K format itself so that i can set the TITLETEXT.
Hope this gives a clear understanding of the problem. Also the solution that you had provided was applicable to the file (YYY). but i need the line breaks at the file (XXX).

Appreciate your help!

Thanks!
Janani

Hi,

Just a quick update.I was trying to find a solution with the code i posted previously.

When i change the format as EXL97, my code works fine that is line break worked properly(without any change is the prev code other than EXL97). But then TITLETEXT does not work.


Thanks!
Janani

HOLD FORMAT EXCEL does not support the setting of the title of the worksheet.
EXL2K does.
The following is a totally useless piece of code, but it does show the line breaks in columns as well as data while still setting the worksheet name for excel 2K. Hope it can help you in some way.

TABLE FILE CAR
PRINT RCOST AS'<BR>This is<br>the retail price'
COUNTRY AS '<BR>Country <BR>of origin'
CAR AS '<br>Brand of<br>automobile'
MODEL AS '<BR>Which<BR>Model'
COMPUTE NDCFLD/A1000V = IF CAR NE LAST CAR THEN '<BR>' || BODYTYPE ELSE NDCFLD || (',<br> ' | BODYTYPE); AS '<br>All<br>bodytypes'

ON TABLE SET PAGE-NUM OFF
ON TABLE PCHOLD AS REPORT FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
TYPE=TITLE,CLASS=title_valign,$
TYPE=REPORT,COLUMN=N2,WRAP=0.500,TITLETEXT='Pharmacy Recall Report',$
ENDSTYLE
END

hi,

Thanks a lot!! This did help me resolve the issue.