Break 1 string of multiple IDs into single IDs for subreport retrieval

[thementornetwork]

Crystal XI

Goal:
Bring back service details (amount used, date, duration etc.) on screen for any services covered under the contract.
The report gives the user a chance to compare total used vs total allowed.

Facts:
- Each contract covers 1 or many services
- Each contract covers up to a certain amount
- Each group of service is stored as a long string like:
"200&260&2270&301&333&389&430&466"

Current Contract table structure looks like:
____________________________________________
Contract|Cust_ID |Svc_Code | Max_Dollar |
________|________|____________|____________|
100 | C16 | 200&260&370| $1000 |
170 | C29 | 102&235 | $ 800 |
________|________|____________|____________|

Challenge:
The IDs I can link the Contract Table to the Services Details table would be:
Cust ID
Service Code

How can I break the Service Code string and retrieve the Service details corresponding
to each of the Service Code?
I am almost sure you need to create an array to store those codes but I do not know
how to proceed nor where to start?





Ideal look of the report:

---------------------------------------------------------------
Group H:|Contract 100 Max Dollars allowed: $1000 Cust# C16
---------------------------------------------------------------
Details |Service Date: Service: Charges:
|
|09/10/2007 200 - Laundry pick up $100
|09/17/2007 260 - Lawn mowing $50
|09/25/2007 200 - Laundry pick up $100
|10/04/2007 370 - Heater repair $250
|10/16/2007 200 - Laundry pick up $100
---------------------------------------------------------------
Group F:| Subtotal used: $600
---------------------------------------------------------------
Group F:| Remaining: $400
---------------------------------------------------------------
Group H:| Contract 100 Max Dollars allowed: $ 800 Cust# C19
---------------------------------------------------------------
Details |Service Date: Service: Charges:
|
|08/31/2007 235 - Groceries delivery $200
|09/14/2007 235 - Groceries delivery $350
|09/28/2007 102 - Dog walk $100
|10/14/2007 102 - Dog walk $100
|10/30/2007 235 - Groceries delivery $250
---------------------------------------------------------------
Group F:| Subtotal used: $1000
---------------------------------------------------------------
Group F:| Remaining: $(200)

 

[lbass]

The simplest solution might be to insert a subreport in a group header_a section that is linked to the main report on contract, customer and svc code. Then in the subreport, you can go into the selection formula and change the svc code line to read:

{svccode.svccode} in split({?pm-contract.svccode},"&")

You can show the subreport subtotal in a formula in the sub report footer:

whileprintingrecords;
shared currencyvar sumchg := sum({svccode.chg});

Then in the main report, write a formula for the balance to be placed in GF:

whileprintingrecords;
shared currencyvar sumchg;
{contract.max_dollars}-sumchg

You would probably need a reset formula in GHa:
whileprintingrecords;
shared currencyvar sumchg;
if not inrepeatedgroupheader then
sumchg := 0;

-LB

 

[thementornetwork]

Hi lbass,

Contract Code is not available in the Service Details table so I will only be able to link on:

svccode.CustID = Contract.CustID
svccode.SvcCode in split({?pm-contract.svccode},"&")

Do I make the subreport Record Selection look like this?
svccode.CustID = {?pm-contract.svccode.PATID}
and
svccode.SvcCode in split({?pm-contract.svccode},"&")

Unfortunately, it will only bring back details if the Contract covers only 1 Service. It does not seem to bring back anything when there are multiple Service Codes per contract...

 

[lbass]

I just tested this and it worked perfectly with multiple codes. I'm not sure what your field names are, but in your last post you set CustID = PatID.

{svccode.CustID} = {?pm-contract.svccode.CustID} and
{svccode.SvcCode} in split({?pm-contract.svccode},"&")

-LB

 

[thementornetwork]

Hi lbass, I need to do further testing on a wider Sample of data and let you.

The CustID = PatID was a typo. Sorry.

I will write back on Monday. Thanks.