Datewise Balance

[Former Member]

Hi All Experts,

I want to know How to Write Query to get Balance of Business Partner Datewise.

Regards

Gayatri shukla.

[kvbalakumar]

Hi,

Its better to try this kind of reports in Crystal Reports where you will get more room for complex formulas and grouping too.

Regards,

Bala

[Former Member]

Hi BalaKumar,

How Can I Achieve this in crystal report,Can you help me ???

Regards

Gayatri shukla.

[Former Member]

Hi,

You can achieve this by creating the SQL Procedure of above query and create connection between this SQL Procedure and Crystal Report through Database Expert.

You can also take the help of this link....

http://www.pioneerb1.com/wp-content/uploads/2012/04/How-to-work-with-Crystal-Reports-8.8.pdf

Hope this help

Thanks'

--

--

Regards::::

Atul Chakraborty

[former_member205766]

Hi Gayatri

Try this

SELECT t1.GroupName as 'Group Name',t0.cardcode as 'BP Code', t0.cardname as 'BP Name', t2.SlpName,

(select sum(debit) -sum(credit) from jdt1  where t0.cardcode = shortname and refdate >= '1/04/2009' and refdate <='31/03/2010') as 'Period Balance 09',

(select sum(debit) -sum(credit) from jdt1  where t0.cardcode = shortname and refdate >= '1/04/2010' and refdate <='31/03/2011') as 'Period Balance 10',

(select sum(debit) -sum(credit) from jdt1  where t0.cardcode = shortname and refdate >= '1/04/2011' and refdate <='31/03/2012') as 'Period Balance 11'

(select sum(debit) -sum(credit) from jdt1  where t0.cardcode = shortname and refdate >= '1/04/2012' and refdate <='31/03/2013') as 'Period Balance 12'

from OCRD t0

INNER join OCRG t1 on t0.groupcode = t1.groupcode

inner join OSLP t2 on t2.SlpCode = t0.slpcode

group by t0.cardcode, t0.cardname, t1.GroupName, t2.SlpName

Regards

Balaji

[Former Member]

Hi Balaji,

Actually I Don't want yearly Account balance of BP But Client wants a report where he can see the Account balance According to the Date .

Regards ,

Gayatri Shukla.

[kothandaraman_nagarajan]

Hi Gayatri,

Please try this query:

SELECT T0.[RefDate], T1.[CardCode], T1.[CardName], T1.[CardType], T0.[BalDueCred], T0.[BalDueDeb], T0.[BalFcCred], T0.[BalFcDeb], T0.[BalScCred], T0.[BalScDeb] FROM JDT1 T0 inner join OCRD T1 on T0.[ShortName] = T1.[CardCode] WHERE T0.[RefDate]  between [%0] and [%1] and  T1.[CardCode] = 'cvet01' GROUP BY T0.[RefDate], T1.[CardCode], T1.[CardName], T1.[CardType], T0.[BalDueCred], T0.[BalDueDeb], T0.[BalFcCred], T0.[BalFcDeb], T0.[BalScCred], T0.[BalScDeb]

Thanks & Regards,

Nagarajan

[Former Member]

Hi Naga Rajan,

Can you explain me What is T0.[BalDueCred],T0.[BalDueDeb]  Field??

Regards

Gayatri Shukla.

[kothandaraman_nagarajan]

Hi Gayatri,

T0.[BalDueCred]--> Balance Due - Credit

T0.[BalDueDeb] --> Balance Due - Debit.

If not required remove from the query,it is an additional field in the query.

Thanks & Regards,

Nagarajan

[Former Member]

In the Business Partner Master, if you go to the Account Balance detail and uncheck "Display Unreconciled Transactions Only" you will have a complete list of the customer's account balance by date.  You can select any date range you wish in the header of the window.