Skip to Content
author's profile photo Former Member
Former Member

Opening Balace problem in GL report

Dear all,

kindly help me out to get Opening balance for those vendors who also opened in BP as customer. i wrote query to select those customers having "CardType" 'S' and as well as 'C'. View is below:

SELECT TOP (100) PERCENT dbo.OJDT.Memo, dbo.JDT1.Debit, dbo.JDT1.Credit, dbo.OCRD.CardName, dbo.OCRD.CardType, dbo.OCRD.CardCode, dbo.OACT.AcctName, dbo.OCRD.Balance, dbo.OJDT.RefDate, dbo.OCRD.U_CSCode FROM dbo.JDT1 INNER JOIN dbo.OJDT ON dbo.JDT1.TransId = dbo.OJDT.TransId INNER JOIN dbo.OCRD ON dbo.JDT1.ShortName = dbo.OCRD.CardCode INNER JOIN dbo.OACT ON dbo.JDT1.Account = dbo.OACT.AcctCode WHERE (NOT (dbo.OCRD.U_CSCode IS NULL)) AND (dbo.OCRD.CardType = 'S')

UNION ALL

SELECT TOP (100) PERCENT OJDT_1.Memo, JDT1_1.Debit, JDT1_1.Credit, OCRD_1.CardName, OCRD_1.CardType, OCRD_1.CardCode, OACT_1.AcctName, OCRD_1.Balance, OJDT_1.RefDate, OCRD_1.U_CSCode FROM dbo.JDT1 AS JDT1_1 INNER JOIN dbo.OJDT AS OJDT_1 ON JDT1_1.TransId = OJDT_1.TransId INNER JOIN dbo.OCRD AS OCRD_1 ON JDT1_1.ShortName = OCRD_1.CardCode INNER JOIN dbo.OACT AS OACT_1 ON JDT1_1.Account = OACT_1.AcctCode WHERE (NOT (OCRD_1.U_CSCode IS NULL)) AND (OCRD_1.CardType = 'C')

ORDER BY CardName

it shows me both either its customer or is vendor.

then i create another view as sub_view as follows:

SELECT SUM(dbo.JDT1.Debit) - SUM(dbo.JDT1.Credit) AS Balance, dbo.OCRD.CardCode, dbo.OCRD.CardName, dbo.OJDT.RefDate FROM dbo.JDT1 INNER JOIN dbo.OJDT ON dbo.JDT1.TransId = dbo.OJDT.TransId INNER JOIN dbo.OCRD ON dbo.JDT1.ShortName = dbo.OCRD.CardCode WHERE (NOT (dbo.OCRD.U_CSCode IS NULL)) AND (dbo.OCRD.CardType = 'S')

GROUP BY dbo.OCRD.CardCode, dbo.OCRD.CardName, dbo.OJDT.RefDate

UNION ALL

SELECT TOP (100) PERCENT SUM(JDT1_1.Debit) - SUM(JDT1_1.Credit) AS Balance, OCRD_1.CardCode, OCRD_1.CardName, OJDT_1.RefDate FROM dbo.JDT1 AS JDT1_1 INNER JOIN dbo.OJDT AS OJDT_1 ON JDT1_1.TransId = OJDT_1.TransId INNER JOIN dbo.OCRD AS OCRD_1 ON JDT1_1.ShortName = OCRD_1.CardCode WHERE (NOT (OCRD_1.U_CSCode IS NULL)) AND (OCRD_1.CardType = 'C')

GROUP BY OCRD_1.CardCode, OCRD_1.CardName, OJDT_1.RefDate

ORDER BY CardName

In crystal report i create primary report with two parameters: BPName and DATE

after passing value to above parameters , primary report shows result perfectly.

now i need opening balance for the BP in parameter to do this i make sub report and make formula in primary report to fetch minimum date and then i pass this formula to sub report date field and e.g subreport.date < formulaminimum_date.

when i run the report it shows me first customer opening balance but not for 2nd vendor.

please help me if is there another easyway to calculate Opening balance kindly guide. thanks in advance

GL.jpg (106.8 kB)
Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on Jun 08, 2012 at 09:36 AM

    Hi Shafi,

    Can you check if this issue is similar or related to following thread:

    https://scn.sap.com/thread/3182497

    If not, then probably the sub report parameter is accepting only first value from main report parameter.

    In that case:

    You would need to use Join({?MainReport Param},'>') in main report formula >> link it to subreport default parameter >> and write {YourTable_CustomerField} in Split({?Pm-SubreportParam},'>') to show all vendors in sub report >> then claculate their sum using manual running total >> share it in main report using shared variable.

    Thanks,

    Prathamesh

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.