Balance brought forward problem

[alie123]

Hi All

This is going to be alittle bit difficult to explain, but essentially I'm using CR 8.5 to build a report for an accounts system. The main report shows a balance sheet and lists the totals for various nominal codes, the user can then highlight an amount and drill down into each nominal code's history which is done via an ondemand subreport. The details of the selected nominal code and the period range that the user has selected in the balance sheet is passed to the subreport through parameters.

The ondemand subreport is grouped in a way that each nominal line entry in the accounts system is grouped together by transaction reference so that the details of each transactions are together and this sorted in ascending order for the nominal code number. The header of the group then includes a summary of any nominal codes that match the selected nominal code and this is shown in the group header whilst the details of the transaction are suppressed and can be drill down into if the user wishes to see every line of the transaction.

The difficulty I am now having is that I need the subreport to show at the top of the group header of the nominal listing the balance brought forward. Because the user can select which periods they wish to select I have a formula which will add all the previous periods together, this is fine, however the difficulty for me is getting the formula to work with the nominal code that has been selected and passed as a parameter by the user rather than the first nominal code of the first nominal entry in the first transaction (which is first level of grouping). Ideally the order of the grouping for the transactions needs to stay as it is, and I don't believe you can create a grouping based on a parameter value.

1) Does anyone have any ideas how I can do this? is there a way to work around this?

2) I wish to use the subreport again and pass a different set of parameters to it so I saved the subreport and gave it another name. When I re-import the subreport, although I saved it as another name, the name of the report that is shown in main report (and hence gets passed as the reports capation) is exactly the same as before. Does anyone know how to change this. It seems the preview tabs of ondemand subreports can't show different values.

 

[lbass]

This is hard to follow in the abstract. Please provide the formula you are using for the balance forward and some sample data.

Are you trying to show the formula results within the subreport? Is the difficulty that you don't know how to limit the formula results to the sum of previous periods for that code only? How are you accessing data from previous periods? By previous periods, do you mean those previous periods shown in the report? If so, are you using a shared variable to calculate this?

-LB

 

[alie123]

Hi Ibass

The balance brought forward does need to appear at the begin of the subreport. In the subreport each record in a table is equal to one nominal code entry, and a group of nominal codes with the same reference number make one transaction. So one transaction might have the following codes

Nominal code: Amount: Dr/Cr Transaction Ref
900200 $15.00 Dr Transaction 189
900100 $100.00 Dr Transaction 189
405000 $60.00 Dr Transaction 189
900100 $25.00 Dr Transaction 189
990000 $200.00 Cr Transaction 189

This grouping is the first level of grouping, in the header of the group I have formulas to calculate the total for the nominal code the user has selected/drilled down to.

So for example, if the user is looking into nominal code 900100, the header of the group will show:

Nominal code: Amount: Dr/Cr
900100 $125.00 Dr

The user can then drill down into the transaction to see all of the other entries that make up the transaction. I am passing from the main report a parameter that contains the relevant nominal code ({?Pm-@Pass_NCode}) the user has drilled down on and also codes containing the start period and start year of values to look at.

I now that the formula I have is working in as much as abstraction previous account period balances, that is fine, but it is looking at the first record of the first transaction rather than the selected nominal code that has been passed across.

So in my example if I have choosen code 900100 as the field thats been passed across, the formula for the balance brought forward is looking not at code 900100 but the first record of the transaction, 900200, and provides the balance brought forward for that code instead. How can I make it look at the selected nominal code?

Below is the code I have used for the formula that is placed in a header. The formula itself makes no direct reference to the selected nominal code that been passed across, but as the header of the group only shows the summary of the selected nominal records I thought this would be enough.

//{?Pm-@Pass_StartYear2} = Start Year
//{?Pm-@Pass_StartP2} = Start Month (1-13)

global numberVar YearTotal;

if {?Pm-@Pass_StartYear2} = "C" and {?Pm-@Pass_StartP2} = 2 then
YearTotal := YearTotal+{NL_ACCOUNTS.NTURNOVER_C1} else
if {?Pm-@Pass_StartYear2} = "C" and {?Pm-@Pass_StartP2} = 3 then
YearTotal := YearTotal+{NL_ACCOUNTS.NTURNOVER_C1}+{NL_ACCOUNTS.NTURNOVER_C2} else
if {?Pm-@Pass_StartYear2} = "C" and {?Pm-@Pass_StartP2} = 4 then
YearTotal := YearTotal+{NL_ACCOUNTS.NTURNOVER_C1}+{NL_ACCOUNTS.NTURNOVER_C2}+{NL_ACCOUNTS.NTURNOVER_C3} else
if {?Pm-@Pass_StartYear2} = "C" and {?Pm-@Pass_StartP2} = 5 then
YearTotal := YearTotal+{NL_ACCOUNTS.NTURNOVER_C1}+{NL_ACCOUNTS.NTURNOVER_C2}+{NL_ACCOUNTS.NTURNOVER_C3}+{NL_ACCOUNTS.NTURNOVER_C4} else
if {?Pm-@Pass_StartYear2} = "C" and {?Pm-@Pass_StartP2} = 6 (and so on)

 

[GJParker]

You will need to use the {?Pm-@Pass_NCode} parameter as part of the record selection of your subreport i.e.

{MyTable.NominalCode} = ({?Pm-@Pass_NCode})

HTH

Gary

Gary Parker
MIS Data Analyst
Manchester, England

 

[alie123]

No, I dont't think I can enter that as part of the record selection as then all of the other nominal details for the transaction will not be shown and I need to show them.

For example, if I enter your code in the record selection transaction 187 will end up looking like this:

Nominal code: Amount: Dr/Cr Transaction Ref
900100 $100.00 Dr Transaction 189
900100 $25.00 Dr Transaction 189


I need to show all of the lines for the transaction, the user needs this functionality.

 

[lbass]

Is that why you are using the linking parameters only in your formula? You don't need to use a variable here. First, I would create the same parameters within the subreport, and then link the subreport to the main report on the parameters, using the dropdown to choose {?@Pass_StartP2}, etc., NOT the versions that have a prefix of {?Pm-?<parameter>}. Then create a formula like:

if {?@Pass_StartYear2} = "C" and
{MyTable.NominalCode} = {?@Pass_NCode} then
(
if {?@Pass_StartP2} = 2 then
{NL_ACCOUNTS.NTURNOVER_C1} else
if {?@Pass_StartP2} = 3 then
{NL_ACCOUNTS.NTURNOVER_C1}+{NL_ACCOUNTS.NTURNOVER_C2} else
if {?@Pass_StartP2} = 4 then
{NL_ACCOUNTS.NTURNOVER_C1}+{NL_ACCOUNTS.NTURNOVER_C2}+{NL_ACCOUNTS.NTURNOVER_C3} else
if {?@Pass_StartP2} = 5 then
{NL_ACCOUNTS.NTURNOVER_C1}+{NL_ACCOUNTS.NTURNOVER_C2}+{NL_ACCOUNTS.NTURNOVER_C3}+{NL_ACCOUNTS.NTURNOVER_C4} else
if {?@Pass_StartP2} = 6 //etc.
)

Place this in the detail section of the subreport and insert a maximum on this and then place it in the group header.

-LB

 

[alie123]

Ibass

I see the theory behind this and techinically it does seem to work, so thank you for your time and input, the difficulty however is that some nominal codes can contain negative balances as well (its a standard). Is there a different way instead of using the maximum command in the running balance to take account of this?

I would want to therefore show therefore either the largest positive number or, the largest negative number.

Alie

 

[alie123]

In addition, as the formula is placed in the details section if there are no transactions in the selected period I think that the brought forward balance will remain empty as it's dependant on there being values present in the details section.

It's very possible that you can have no records in the current period, but obviously there will be an opening balance.

 

[lbass]

You haven't explained what the brought forward balance consists of. It looks like it consists of all periods prior to the current period, and that this data is found in each record in the fields {NL_ACCOUNTS.NTURNOVER_Cx}. Do these fields have different results depending upon the code? Otherwise, the formula won't work for that reason.

I thought that the subreport contained records from all periods and years, which is why you needed to define the period and year in the formula. Therefore, the details section would not be empty. Or are you saying that {NL_ACCOUNTS>NTURNOVER_Cx} can be null?

Also I'm assuming that each record that matches the parameter criteria would have the same results for the {NL_ACCOUNTS.NTURNOVER_Cx} fields, i.e., the minimum or maximum is necessary to that you don't add duplicate values. Is this true?

Try something like this for {@bal}:

if {?@Pass_StartYear2} = "C" and
{MyTable.NominalCode} = {?@Pass_NCode} then
(
if {?@Pass_StartP2} = 2 then
(
if isnull({NL_ACCOUNTS.NTURNOVER_C1}) then 0 else
{NL_ACCOUNTS.NTURNOVER_C1}
) else
if {?@Pass_StartP2} = 3 then
(
if isnull({NL_ACCOUNTS.NTURNOVER_C1}) then 0 else
{NL_ACCOUNTS.NTURNOVER_C1}
)+
(
if isnull({NL_ACCOUNTS.NTURNOVER_C2}) then 0 else
{NL_ACCOUNTS.NTURNOVER_C2}
)+ //etc.
)

Then create a second formula to use in the header:

if sum({@bal},{table.group}) < 0 then
minimum({@bal},{table.group}) else
maximum({@bal},{table.group})

-LB

 

[alie123]

I've managed to solve this problem by calculating the balance brought forward in the main report and then passing it across to the subreport as a parameter. This works, although I don't know if calculating the brought forward balance in the main report would make the report any faster.

Does anyone have any ideas about my second point? Why does a saved subreport retain it's old name in the caption field after you have saved it as something else?

 

[GJParker]

To change the subreport name

right click on the subreport -> format subrepotr -> subreport tab -> Edit subreport name field.

HTH

Gary Parker
MIS Data Analyst
Manchester, England