How to do this?
Hi,
Our financial department submit a very challenging request.
We have a claim management system. One requirement of our system is to
print cheque. Every cheque will have an EOB attached to it which explains
which claim have been paid and how the paid amounts are determined.
So it is a master-detail-detail-detail report. The master is the payment
data, then the claim data, then the claim item data, then the adjudication
message (if any) to explain how the paid amount of each claim item is
determined (in the case the amount is reduced for reasons like coverage
percentage less than 100%, deductible is taken off, or maximum is
reached,etc, or in the case the claim item is rejected for reason like the
service has bee performed in the last six months and the plan only allow
once in six months). Actually, the report layout is more complicated than
this.
Because usually the adjudication message will repeat itself in every claim
item (for example, coverage percentage or reaching maximum), the messages
are numbered and all by itself, then the claim item will post-fix with the
number of the messages (a lot of time a claim item will have more than one
message). So the layout is like
Payment - claim - claim item
- adjudication message
So one master, two details, one detail (claim) with sub-detail (claim item),
and one detail (adjudication message) without sub-detail.
In the past, the cheque was printed from preprinted cheque paper. The EOB
starts right after the cheque (the blank portion of the proprinted cheque
paper under the cheque). If the EOB does not all fit in the preprinted
cheque paper, then it will continue on plain paper from another tray. When
it finishes the first cheque, it switch to the first tray to print the next
cheque and switch to the second tray if there is more EOB.
I have managed to make this work. However, our financial department lately
told me that before the end of 2007, all cheque will be at the bottom of the
cheque paper (a new standard by some authority in Canada). So the report
has to print the detail and sub-detail in the blank portion of the
preprinted cheque paper above the cheque portion, then print the cheque in
the botton of the preprinted cheque paper, and continues to print any
unfinished EOB on plain paper from another tray. Which mean it has to print
the first detail (claim) and sub-detail (claim item) before printing the
master. And depends on the amount of claim and claim items, it may have to
continue the first detail and sub-detail in the subsequent plain paper. It
also means the report may have to print none, part, or all of the second
detail (adjudication messages) in the preprinted cheque paper.
With my limited knowledge and experience with RB, I have no idea how to do
this, or it is even possible? If it is doable, how?
Thanks,
Ping Kam
Our financial department submit a very challenging request.
We have a claim management system. One requirement of our system is to
print cheque. Every cheque will have an EOB attached to it which explains
which claim have been paid and how the paid amounts are determined.
So it is a master-detail-detail-detail report. The master is the payment
data, then the claim data, then the claim item data, then the adjudication
message (if any) to explain how the paid amount of each claim item is
determined (in the case the amount is reduced for reasons like coverage
percentage less than 100%, deductible is taken off, or maximum is
reached,etc, or in the case the claim item is rejected for reason like the
service has bee performed in the last six months and the plan only allow
once in six months). Actually, the report layout is more complicated than
this.
Because usually the adjudication message will repeat itself in every claim
item (for example, coverage percentage or reaching maximum), the messages
are numbered and all by itself, then the claim item will post-fix with the
number of the messages (a lot of time a claim item will have more than one
message). So the layout is like
Payment - claim - claim item
- adjudication message
So one master, two details, one detail (claim) with sub-detail (claim item),
and one detail (adjudication message) without sub-detail.
In the past, the cheque was printed from preprinted cheque paper. The EOB
starts right after the cheque (the blank portion of the proprinted cheque
paper under the cheque). If the EOB does not all fit in the preprinted
cheque paper, then it will continue on plain paper from another tray. When
it finishes the first cheque, it switch to the first tray to print the next
cheque and switch to the second tray if there is more EOB.
I have managed to make this work. However, our financial department lately
told me that before the end of 2007, all cheque will be at the bottom of the
cheque paper (a new standard by some authority in Canada). So the report
has to print the detail and sub-detail in the blank portion of the
preprinted cheque paper above the cheque portion, then print the cheque in
the botton of the preprinted cheque paper, and continues to print any
unfinished EOB on plain paper from another tray. Which mean it has to print
the first detail (claim) and sub-detail (claim item) before printing the
master. And depends on the amount of claim and claim items, it may have to
continue the first detail and sub-detail in the subsequent plain paper. It
also means the report may have to print none, part, or all of the second
detail (adjudication messages) in the preprinted cheque paper.
With my limited knowledge and experience with RB, I have no idea how to do
this, or it is even possible? If it is doable, how?
Thanks,
Ping Kam
This discussion has been closed.
Comments
I believe this would be possible with ReportBuilder, however it is going to
take some time and research on your part to complete all your requirements
below.
1. I would suggest taking care of all data processing in SQL code before
involving ReportBuilder. A number of queries could handle the requrements
for this part of the task.
2. Moving the check to the bottom of the page should not be an issue. You
may be able to place two fixed style subreports in a static size detail band
and populate the top one with the data and the bottom one with the check
information, setting the top one to continue on the next page if needed.
Printing from a different tray however is a little tricky in ReportBuilder
and requires access to the DEVMODE structure in the Windows API. It sounds
like you have this working which is great because this can be very tricky
depending on the printer you are using. (See article below).
-------------------------------------------------
Tech Tip: Configuring Printer Specific Options
-------------------------------------------------
The Report.PrinterSetup properties can be used to set the properties that
are common to all printers. Internally RB applies these to the Windows
DevMode structure.
Report.PrinterSetup <----> Windows DevMode <-----> Printer Driver <--->
Printer
Printers often contains additional features, such as output bins, media
types, etc. The Windows DevMode structure can contain a private area that is
used the printer driver to store printer specific options. These can be
configured by using the Printer Driver's built-in properties dialog. These
options can theoritically be set programmatically, the trick is that you
have to know where in the structure to store the option and what values are
valid.
The following example shows how to display the printer properties dialog and
save the devmode for use within RB...
http://www.digital-metaphors.com/tips/SavePrinterDevMode.zip
--
Regards,
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
Thanks for your reply.
Agreed. Data preparation is not the scope of the report.
You
band
It doesn't seem to be that easy. I have tried your suggestion but failed.
I add a master, a detail, and a sub-detail query (I leave out the other
detail table for now to make it simpler), 3 datasource, 1 report and 3
dbpipeline. I link them up accordingly and the report reference the master
pipeline. I start the report designer, remove the header and footer, add
two subreports to the detail band. Link the first subreport to the master
pipeline and place it at the bottom of the detail band and set its
printbehavior to pbFixed, and add a few control to the detail band of this
subreport. Link the other subreport to the detail pipeline and place it to
the top of the detail band, also set its printbehavior to pbFixed. Then I
add a few control and another subreport to the detail band of this detail
subreport. Then I add a few control to the detail band of the subdetail
subreport.
I tried various combination of the the printbehavior and these are the
result (none of them works):
Master: pbFixed
Detail: pbFixed
SubDetail: pbChild
The master subreport will only print the first master record on every page.
The detail and subdetail will change to the next master on subsequent page,
i.e., it will stop printing the detail and subdetail on subsequent pages
before all the detail and sub-detail are finished (I select the data so the
detail and subdetail will fill up several pages).
Master: pbFixed
Detail: pbChild
SubDetail: pbChild
The difference is this configuration is the detail and subdetail will
continue on subsequent pages until finish. But in the first page, it will
continue to print over the master subreport. Also the master only print the
first record on every occasion of the master subreport.
Master: pbChild
Detail: pbChild
SubDetail: pbChild
The detail and subdetail behaves the same as before. But the master
subreport will print all master records on every master subreport occasion.
What did I do wrong? What need to be done to accomplish this?
sounds
I didn't expect to access the DEVMODE. I am thinking of presenting two
comboboxes, one for the preprinted cheque paper, the other for the plain
paper, and populate the drop down with PrinterSetup.GetBinNames. So the
user can choose the tray before the print job. To make it easier for the
user user friendly, I can save this info in an ini file. Simply read it
before the print job and popup a dialog to prompt the user if any of the
bins does not exist.
To change the bin, I think I can set the PrinterSetup.BinName in the
OnStartPage event.
The harest part is to determine the time to switch the tray, I think this
can be done via the dataset.OnScroll event.
Ping Kam
I find the best way to approach a complicated problem such as this one is to
break it down into simpler pieces and work from there. Let's start with
just getting the data on the top of the report to show up correctly. We'll
add the check later. You will probably want your master linked to the main
report and the detail linked to the subreport. If you are in need of more
detail data, you may need to add another subreport to the existing one and
link it to the other dataset. See if you can play around with it and get
the proper output.
I'm sorry, I thought you were printing to output bins. You are correct in
that you can control which bin paper is taken from using the
Report.PrinterSetup.BinName property.
--
Regards,
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
to
We'll
main
I ignore the subreport for the master (I leave it there but make all the
labels invisible) and focus on the top subreport as you suggested.
Now if I set the printbehavior of the top subreport for the detail data to
pbChild, it shows all the data correctly. But it will not stop and continue
to print to the bottom of the page in the preprinted paper.
If I set the printbehavior to pbFixed, then it will stop just above the
cheque portion, but it will only print one page per master. The second page
will be the first detail of the next master record so the rest of the detail
and subdetail data of the master record in subsequent pages are not printed.
I have tried to set the BinName in the OnStartPage event but it doesn't seem
to print from the assigned bin. Is the OnStartPage too late to switch the
bin? I also try to use the master Dataset.OnScroll event, the master
pipeline.OnRecordPositionChange, the detail
pipeline.OnMasterRecordPositionChange to control the bin, but they all get
fired more than once even there is no change to the master record. Is there
any event that I can use to determine that the master record changes?
Thanks,
Ping Kam
After several hours of trying this and that, I have solve some of the
problem.
Put the master data (the cheque portion) in the page footer, then turn on or
off the visible porperty of the page footer in the BeforePrint event of the
PageHeader band (not the page footer). If the Page footer is visible, the
detail subreport will stop above the page footer and continues on the next
page and if the page footer is invisible, it will continue till the bottom
of the page.
I have also managed to switch bin using engine.page.printer.setup.bin to
change the bin in the OnStartPage event.
I was planning to use group and start new page and reset page no to
determine when to switch bin and print the cheque (only print cheque on Page
1). I add a group on the master's primary key. However, in the OnStartPage
event, except for the first master record, the page no always starts from 2
(the page no will be correct in the OnEndPage event though). The Page No is
correct too in the second pass. Is this a bug? If I can, I don't want to
use two passes because it is a multi-tier app and data fetching will be
relatively slow.
Then I run into the problem with one master and two details (not master -
detail - subdetail). See my other post. II think I am all set to go if I
can get the Page No and one master multiple details to work.
Thanks,
Ping Kam
Great news. I'm glad you are making progress.
Try using the AbsolutePageNo property instead of the PageNo when using
groups. We can continue our conversation about creating a master with
multiple details in your new thread.
--
Regards,
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
From what you are saying it looks as though the OnStartPage fires to early
to use it for this purpose. You might try the GroupHeaderBand.BeforePrint
event or something similar that fires after the page breaks.
--
Regards,
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
Is there any reason why the page no was not set properly in the OnStartPage
event on the first pass? It is set correctly in the second pass. I think
the page no should be set properly even in the OnPageStart event, like the
behavior in the second pass. And I think should be fixed as it introduces
inconsistent behavior between the two passes.
I don't know if it is too late to set the bin in the OnPrint event of the
PageHeader band. Your sample code do that in the OnStartPage event which
makes more sense to me. I will give it a try just in case RB decides not to
change this.
Thanks,
Ping Kam
You are probably right in that the BeforePrint event is too late to set the
bin property. As I mentioned before this is a timing issue. The
OnStartPage is firing before the group has a chance to reset the page
number. The reason this is correct on the second pass is that during the
first pass of the report all pagination has been calculated.
As a workaround you will need to create a two pass report or we will need to
find another way to determine which bin the page is to be printed from.
--
Regards,
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
I have tried the PageHeader's BeforePrint event and it seems to work.
Thanks,
Ping Kam