Skip to Content

SQL Stored Procedure: Add Currency to GL Balance report

Experts,

I'm fairly new to using SQL commands to generate Crystal Reports. I have a Cash GL Balance Report where I just want to add the Currency and Exchange Rate for that day.

My issue is that it does not show a column with the rate....

Any help would be greatly appreciated,

Marli

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;


WITH qry as 
(
	SELECT T1.[Account]
		  ,t2.AcctName
		  ,T1.[RefDate] RefDate
		  ,SUM(T1.[Debit])De
		  ,SUM(T1.[Credit])Cr
		  ,T2.ActCurr
	FROM   OJDT T0
		   INNER JOIN JDT1 T1
				ON  T0.TransId = T1.TransId
		   INNER JOIN oact t2
				ON  t1.Account = t2.AcctCode
					AND t2.Finanse = 'Y'
					AND t2.FrozenFor = 'N'
					AND not t2.AcctName like '%Petty Cash%'
	GROUP BY
		   T1.[Account]
		  ,T1.[RefDate]
		  ,t2.AcctName
		  ,T2.ActCurr
),
currencies as
(
	SELECT getdate() as tempdatecol, cast(convert(varchar(10), getdate(), 110) as datetime) AS 'Today',


T1.[Currency], T1.[Rate], T0.[CurrName], T1.[RateDate] 
FROM OCRN T0  RIGHT OUTER JOIN ORTT T1 
ON T0.[CurrCode] = T1.[Currency]


WHERE T1.[RateDate] = CONVERT(date,GETDATE())


)




	SELECT 'Inguran_SandBox' as company
		  ,c.Account AS Accountcode
		  ,c.AcctName
		  ,sum(de) - sum(cr) AS Closing
		  ,c.ActCurr
	FROM   qry c
	LEFT JOIN currencies ON currencies.CurrName = C.ActCurr
	GROUP BY c.Account, c.AcctName, c.ActCurr
	ORDER BY 
	  case
		when CHARINDEX('Clearing', c.AcctName) > 1 then 'z'+ c.AcctName
		else c.AcctName
	  end
	  


	 


END
1-001.png (13.1 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Best Answer
    Jan 22 at 04:26 PM

    Nagarajan,

    I appreciate it. This still gives me NULL values on the Currency columns, see screenshot.

    As a work around, I have created the 2 commands as separate Stored Procedures and that works in Crystal with a few Formulas. It does not seem to slow down report performance.

    Thank for your help,

    Marli

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 19 at 11:42 PM

    Hi,

    Try this query to get currency and exchange rate,

    BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;

    WITH qry as

    ( SELECT T1.[Account] ,t2.AcctName ,T1.[RefDate] RefDate ,SUM(T1.[Debit])De ,SUM(T1.[Credit])Cr ,T2.ActCurr FROM OJDT T0 INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId INNER JOIN oact t2 ON t1.Account = t2.AcctCode AND t2.Finanse = 'Y' AND t2.FrozenFor = 'N' AND not t2.AcctName like '%Petty Cash%' GROUP BY T1.[Account] ,T1.[RefDate] ,t2.AcctName ,T2.ActCurr ), currencies as

    ( SELECT getdate() as tempdatecol, cast(convert(varchar(10), getdate(), 110) as datetime) AS 'Today', T1.[Currency], T1.[Rate], T0.[CurrName], T1.[RateDate] FROM OCRN T0 RIGHT OUTER JOIN ORTT T1 ON T0.[CurrCode] = T1.[Currency] WHERE T1.[RateDate] = CONVERT(date,GETDATE()) )

    SELECT 'Inguran_SandBox' as company ,c.Account AS Accountcode ,c.AcctName ,sum(de) - sum(cr) AS Closing ,c.ActCurr ,Currencies.Currency ,Currencies.Rate ,Currencies.RateDate FROM qry c LEFT JOIN currencies ON currencies.CurrName = C.ActCurr

    GROUP BY c.Account, c.AcctName, c.ActCurr,Currencies.Currency,Currencies.Rate,Currencies.RateDate

    ORDER BY case when CHARINDEX('Clearing', c.AcctName) > 1 then 'z'+ c.AcctName else c.AcctName

    end

    END

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 23 at 02:04 PM

    Hi,

    Try this query,

    WITH qry as ( SELECT T1.[Account] ,t2.AcctName ,T1.[RefDate] RefDate ,SUM(T1.[Debit])De ,SUM(T1.[Credit])Cr ,T2.ActCurr FROM OJDT T0 INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId INNER JOIN oact t2 ON t1.Account = t2.AcctCode AND t2.Finanse = 'Y' AND t2.FrozenFor = 'N' AND not t2.AcctName like '%Petty Cash%' GROUP BY T1.[Account] ,T1.[RefDate] ,t2.AcctName ,T2.ActCurr ), currencies as ( SELECT getdate() as tempdatecol, cast(convert(varchar(10), getdate(), 110) as datetime) AS 'Today', T1.[Currency], T1.[Rate], T0.[CurrName], T1.[RateDate] FROM OCRN T0 LEFT JOIN ORTT T1 ON T0.[CurrCode] = T1.[Currency] WHERE T1.[RateDate] = CONVERT(date,GETDATE()) ) SELECT 'Inguran_SandBox' as company ,c.Account AS Accountcode ,c.AcctName ,sum(de) - sum(cr) AS Closing ,c.ActCurr , Currencies.Currency ,Currencies.Rate ,Currencies.RateDate FROM qry c LEFT JOIN currencies ON currencies.Currency = C.ActCurr GROUP BY c.Account, c.AcctName, c.ActCurr,Currencies.Currency,Currencies.Rate,Currencies.RateDate ORDER BY case when CHARINDEX('Clearing', c.AcctName) > 1 then 'z'+ c.AcctName else c.AcctName end

    Make sure you have entered exchange rate for today.

    Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 23 at 01:58 PM

    Hi Marle,

    I know you have a workaround but to answer your original question....

    In your output statement, I do not see a column for rate and I think you are joining the currencies table using the wrong field.

    Using this as the output statement, comments on the altered lines :-

    SELECT 'Inguran_SandBox' as company
      ,c.Account AS Accountcode
      ,c.AcctName
      ,sum(de) - sum(cr) AS Closing
      ,c.ActCurr
      ,currencies.Rate    /* column for currency rate */
    FROM   qry c
    LEFT JOIN currencies ON currencies.Currency = C.ActCurr /* join on currency code, not currency name */
    GROUP BY c.Account, c.AcctName, c.ActCurr, currencies.Rate
    ORDER BY 
      case
    when CHARINDEX('Clearing', c.AcctName) > 1 then 'z'+ c.AcctName
    else c.AcctName
      end

    works for me! ;-)

    HTH and best wishes

    Dave

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 24 at 05:29 PM

    David,

    Thanks that worked! Appreciate your and Nagarajan's help!

    Add comment
    10|10000 characters needed characters exceeded