calculate total in sub-report and display in main rpt 2

[namas]

Environment: CR 8.5

I want to calculate beginning balance of general ledger accounts from table 'gl_balance' in sub-report.
The beginning balance is in period 0 for each fund (eg. 1000, 1001, 1002...).
There are 13 periods (period 0, period1,period 2...).
Each fund can have many general ledger accounts (eg. cash, cash equivalent, a/p, AR, etc).

year period fund_id gl_account gl_amount
2006 0 1001 1000 7734.32
2006 0 1500 1000 270.72
2006 0 1000 1000 1749.35

I got subreport in CR. I want to get beginning balance
(period 0 total amount) of each GL accounts for each fund in subreport
and display it in the main report. Reason beginning balance is in sub-report is because the main report display account detail transactions from period 1 to 12. I want then be able to subtract the total debit amount from beginning balance and get the ending balance.

How can I do this? Thanks.

 

[synapsevampire]

You need to be specific.

Where in the main report is the subreport, and how are they linked?

Returning values from a subreport is simple, use shared variables, as in:

whileprintingrecords;
shared numbervar MySubAmt:= sum({table.value})

Then after the subreport section in the main report you can reference the value, as in:

whileprintingrecords;
shared numbervar MySubAmt;
MySubAmt

If you need (period 0 total amount) of each GL accounts for each fund in subreport, then you'll probably want to group the main report by the fund and link accordingly, then do your sum in the subreport and return that value.

Kind of hard to know withough any understanding of how eitehr report is designed.

-k

 

[namas]

Thanks. I don't have the data for main rpt tonite, I will try your tips first. If not, I will post tomorrow.
About summing period 0 total in sub-report, how to sum
just for period 0 using your fomula below?

whileprintingrecords;
shared numbervar MySubAmt:= sum({table.value})

 

[namas]

Main report is grouped by fund_id.
The subreport is placed in fund_id group header. The two rpts are linked by fund_id, year, period and gl_account.

 

[synapsevampire]

If they're linked by those entities, then the sum should return the proper values for those that are period 0 as linking filters data in the subreport.

-k

 

[namas]

How to pass variable from sub-rpt to main rpt?

In the main report, when I use the shared variable 'getBeginBalance', main report shows 0 as beginning balance even though the shared variable 'setBeginBalance' shows the correct beginning balance amount in the sub-rpt. In the main rpt, the sub-rpt is in main report header, on section b that is suppressed, and then I use the shared variable. The two reports are linked by gl_account. Thanks!!

In sub report:
formula for setBeginBalance:

whileprintingrecords;
shared numbervar BeginBalance:= Sum ({gl_balance.GL Balance $}, {gl_balance.GL Account (short)})


In the main report:
formula for getBeginBalance:

whileprintingrecords;
shared numbervar beginBalance;
beginBalance;

 

[synapsevampire]

and then I use the shared variable." where?

The display formula should be ni a section AFTER the subreports section.

So if the subreport is in the report header B, right click repot header B and select insert section below and place the display formula in there.

Doesn't make sensse that the subreport would bee in the report header if it is lining by all of those entities, it would only display data for those that match the first row in the main report as the report header is only done once, and in the beginning of the report (hence first row).

-k

 

[lbass]

The section that contains the subreport cannot be suppressed, but you can suppress all sections within the subreport, minimize the subreport height, and format the section above to "underlay following sections" to minimize the resulting white space.

-LB

 

[synapsevampire]

Ahhh, good catch, LB, I didn't notice that lil bit.

-k

 

[namas]

Thanks. I am seeing the result now but beginning balance amount from sub-rpt is not correctly tied to the gl_account in main report. I am not sure if this is correct site to ask, but here is the SQL that calculates the Beginning Balance. I don't know the logic behind the calculation.
If anyone can explain, it will be great. I am just using the two tables mentioned to do the Beginning Balance calculation and it's not working as I imagined.


SELECT "gl_detail"."gl_year"
, "gl_detail"."gl_period"
, "gl_detail"."fd_id"
, "gl_detail"."gl_id"
, Substr("gl_detail"."gl_id",1,4) AS GL_ACCOUNT_DESC
, "gl_detail"."gl_tran_date"
, "gl_detail"."gl_short_ref"
, "gl_detail"."gl_source"
, "gl_detail"."sy_batch_id"
, (IF "gl_detail"."gl_amount" > 0 THEN "gl_detail"."gl_amount" ELSE 0 ENDIF) AS GL_AMOUNT_DEBIT
, (IF "gl_detail"."gl_amount" < 0 THEN "gl_detail"."gl_amount" ELSE 0 ENDIF)* -1 AS GL_AMOUNT_CREDIT
, "gl_detail"."gl_amount"
, "gl_detail"."gt_id"
, "gl_detail"."sa_id"
, "gl_detail"."dn_id"
, "gl_detail"."gl_je_id"
, "gl_detail"."ck_id"
, "gl_detail"."ap_inv_id"
, "ap_inv_hdr"."ap_name1" AS AP_NAME
, "ck_master"."ck_payee1" AS CK_NAME
, "fd_master"."fd_abbrev_name"
, "gt_master"."gt_org_name1" AS GT_NAME
, Month("gl_detail"."gl_tran_date") AS MONTH
, Coalesce(AP_NAME,GT_NAME,CK_NAME) AS SORT_NAME
,fd_master.fd_entity
, "gl_master"."gl_description"
, "gl_department"."gl_department_desc"
, "gl_cost_center"."gl_cost_center_desc"
, "gl_detail"."gl_id" AS GENERAL_LEDGER
FROM {oj "gl_detail" LEFT OUTER JOIN "ap_inv_hdr" ON "gl_detail"."ct_id" = "ap_inv_hdr"."ct_id" AND
"gl_detail"."ap_inv_id" = "ap_inv_hdr"."ap_inv_id" AND
"gl_detail"."ap_ictype" = "ap_inv_hdr"."ap_ictype" AND
"gl_detail"."fd_company" = "ap_inv_hdr"."fd_company"}
, {oj "gl_detail" LEFT OUTER JOIN "gt_master" ON "gl_detail"."gt_id" = "gt_master"."gt_id"}
, {oj "gl_detail" LEFT OUTER JOIN "ck_master" ON "gl_detail"."ck_id" = "ck_master"."ck_id" AND "gl_detail"."ck_bank_account" = "ck_master"."ck_bank_account" }
, "fd_master"
, "gl_master"
, "gl_department"
, "gl_cost_center"
WHERE ("gl_detail"."fd_id" = "fd_master"."fd_id" )
AND (Substr("gl_detail"."gl_id",1,4) = "gl_master"."gl_id")
AND ("gl_detail"."gl_year" = _iYear )
AND ("gl_detail"."gl_period" >= _iPeriodStart )
AND ("gl_detail"."gl_period" <= _iPeriodEnd )
AND ("gl_detail"."fd_id" = '1000')
AND ((Substr("gl_master"."gl_id",1,4) = _szGlID) OR p_szGlID IS NULL))
AND (("gl_detail"."sy_batch_id" = _szBatchID) OR p_szBatchID IS NULL))
AND (("fd_master"."fd_entity" = _szEntity) OR p_szEntity IS NULL))
AND ((Substr("gl_detail"."gl_id",7,2) = _szSubDept) OR p_szSubDept IS NULL))
AND ("gl_master"."gl_id" = Substr("gl_detail"."gl_id",1,4))
AND ("gl_department"."gl_department" = Substr("gl_detail"."gl_id",5,2))
AND ("gl_cost_center"."gl_cost_center" = Substr("gl_detail"."gl_id",7,2))
UNION ALL

SELECT "gl_balance"."gl_year"
, 0
, "gl_balance"."fd_id"
, "gl_balance"."gl_id"
, Substr("gl_balance"."gl_id",1,4)
, NULL
, ' Beginning Balance:'
, ''
, ''
, (IF BEG_BALANCE > 0 THEN BEG_BALANCE ELSE 0 ENDIF) AS GL_AMOUNT_DEBIT
, (IF BEG_BALANCE < 0 THEN BEG_BALANCE ELSE 0 ENDIF)* -1 AS GL_AMOUNT_CREDIT
, (COALESCE(SUM("gl_balance"."gl_balance_amt"),0) ) -
(SELECT COALESCE(SUM("bal2"."gl_balance_amt"),0)
FROM gl_balance bal2
WHERE "bal2"."gl_year" = "gl_balance"."gl_year"
AND "bal2"."gl_period" >= _iPeriodStart
AND "bal2"."gl_period" <= _iPeriodEnd
AND "bal2"."fd_id" = "gl_balance"."fd_id"
AND "bal2"."gl_id" = "gl_balance"."gl_id") AS BEG_BALANCE
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, "fd_master"."fd_abbrev_name"
, ''
, ''
, ''
,fd_master.fd_entity
, "gl_master"."gl_description"
, "gl_department"."gl_department_desc"
, "gl_cost_center"."gl_cost_center_desc"
, "gl_balance"."gl_id" AS GENERAL_LEDGER
FROM "gl_master"
, {oj "gl_balance" LEFT OUTER JOIN "fd_master" ON "gl_balance"."fd_id" = "fd_master"."fd_id"}
, "gl_department"
, "gl_cost_center"
WHERE (Substr("gl_balance"."gl_id",1,4) = "gl_master"."gl_id")
AND "gl_balance"."gl_year" = _iYear
AND "gl_balance"."gl_period" <= _iPeriodEnd
AND ("gl_balance"."fd_id" = '1000')
AND ((Substr("gl_master"."gl_id",1,4) = _szGlID) OR p_szGlID IS NULL))
AND (("fd_master"."fd_entity" = _szEntity) OR p_szEntity IS NULL))
AND ((Substr("gl_balance"."gl_id",7,2) = _szSubDept) OR p_szSubDept IS NULL))
AND ("gl_department"."gl_department" = Substr("gl_balance"."gl_id",5,2))
AND ("gl_cost_center"."gl_cost_center" = Substr("gl_balance"."gl_id",7,2))
GROUP BY
"gl_balance"."gl_year"
, "gl_balance"."fd_id"
, fd_master.fd_entity
, "gl_balance"."gl_id"
,"fd_master"."fd_abbrev_name"
, "gl_master"."gl_description"
, "gl_department"."gl_department_desc"
, "gl_cost_center"."gl_cost_center_desc"
ORDER BY
3 ASC
, 25 ASC
, 4 ASC
, 6 ASC
, 8 ASC
, 24 ASC

 

[lbass]

It would be more helpful if you explained your group structure more clearly. It sounds like you only have one group (fund ID), but it sounds like you want a separate beginning balance per GL Account within the fund ID per year and period. To me, it would make more sense to have these groups in the main report and then link the subreport on all group fields and place the subreport in the GL Account group section, assuming this is the innermost group.

-LB