shared variables from main to subreport and back to main

[JEnright1]

I am using an external MS Access translation table that maps a code to many departments and many accounts used in each department to the exiting ERP system. For example,
the code is 6020-7010. The 6020 maps to more than one department and the 7010 maps to more than one account. I want to send the value 6020 to the subreport, find all departments and all accounts within these departments and send all of these values back to the main report so that I can display running totals. The code 6020 does not exist anywhere in the ERP system. I would like to send back a string of all the distinct departments and distinct accounts so that I can combine different cobinations of these in the main report.

For Example: send to SUB '6020', return Departments (65282,65283) and
Accounts (602000,602100,602200,602300,602400,604000,604100)for 7010.
I also need to keep the 7010 attached to the accounts returned. There is also a 6020-7020, 6020-7050, etc. which may use the same accounts as 7010.

 

[lbass]

If I'm following you correctly, try the following:

Link the sub to the main on {table.code}. Allow it to link to some string field in the sub, and then go into the sub record selection formula and change it to the following:

{table.deptcode} = split({?pm-table.code},"-")[1] and
{table.acctcode} = split({?pm-table.code},"-")[2]

...where deptcode and acctcode are the linking fields in the translation table. Then create formulas in the sub to add to the detail section:

whileprintingrecords;
shared stringvar dept := dept + {table.dept}+",";
shared stringvar acct := acct + {table.acct}+",";

In the main report, in the section below the one in which the sub is located, use formulas like these to reference the variables:

whileprintingrecords;
shared stringvar dept;
left(dept,len(dept)-1);

whileprintingrecords;
shared stringvar acct;
split({table.code},"-")[2]+": "+left(acct,len(acct)-1);

You should also have a reset formula in a section below this section or before the section containing the sub:

whileprintingrecords;
shared stringvar dept := "";
shared stringvar acct := "";

-LB

 

[JEnright1]

This was very helpful. I need to use the returned value in running totals in the main report but it is not available in the (use a formula) of the running total. How can I make it available to use? Thank You.

 

[lbass]

How would you use them in running totals, since these are strings? Please explain.

-LB

 

[JEnright1]

The sub will return a string. The string is a concatanation of deparments. For example the department string returned is 11111,22222,33333. The running total needs to use a formula like
department in [11111,22222,33333].

 

[lbass]

Create a manual running total like this:

whileprintingrecords;
shared stringvar dept;
numbervar amt;
if {table.dept} in dept then
amt := amt + {table.amt};

Then reference the result in the footer:

whileprintingrecords;
numbervar amt;

If you want the result at a group level, then add a reset formula in the group header:

whileprintingrecords;
numbervar amt;
if not inrepeatedgroupheader then
amt := 0;

-LB