Skip to Content

Crystal Report imported into SAP B1 not showing all lines

Experts,

I have a GL Balance and Bank Balance report that is based of Stored Procedures from several B1 databases. It works well except for on Sub-Report not showing all lines in SAP B1. That part of the report is looking at a UDT I created for the user to enter bank balances into, linked to the GL Balances via the SYS_number.

When I run this report in Crystal I get all the lines (3) for that sub-report. When I import this to SAP B1, the sub-report shows only 1 line (middle one).

When I run the Stored Procedure query in SQL, I get all 3 lines...

IN SAP B1:

In Crystal reports:

In SQL:

Your help would be much appreciated!

Marli

run-sap.png (6.0 kB)
crystal-run.png (38.3 kB)
sql-run.png (5.1 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

7 Answers

  • Best Answer
    Feb 07 at 06:03 PM

    Experts,

    I have found the issue is that I have Stored Procedures and a Table (OADM) pulling into the main and Sub-reports. Somehow when this is imported in SAP B1, ALL sub-reports pull OADM from the Company I'm running this report in. As I'm using the Main Currency in that table for formulas it somehow, sometimes (relating to SYSnumbers) pull data from the company i'm running it in and other times it returns correct data.

    When I added this logic to my SP's and deleted the OADM connection, it run fine. Hope this helps some-one else.

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 05 at 08:47 PM

    Hi Marli,

    Interesting question! I personally haven't met this scenario, but I think it's common for financial consolidation reporting.

    My guess would be the B1 user may not have permission to other company databases. However, when you ran the report in Crystal Designer or the query in SQL Server, you were using a SQL user with more privileges.

    I would like to see how others would reply in dealing with such a scenario too.

    Cheers,

    Jimmy

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 07 at 11:06 PM

    Hi,

    Great!

    Thank you for sharing with us.

    Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 06 at 02:05 PM

    Hi,

    Share complete query here to check.

    Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 06 at 05:12 PM

    Nagarajan,

    I tried suppressing (and deleting) the Main Report, but issue were still there in the Sub-Reports.

    I do not know if this is relating to the same issue, but the Account Name is populated with a Formula to not show the Segment letters, if I change this to the normal Account Name field, the sub-report shows the Main Reports data - even though it is pointed to a separate DB.

    Here is the Formula for the Account Name:

    Left({GetCurrentBalanceWithFX.AcctName}, Instr({GetCurrentBalanceWithFX.AcctName},'(REPRO')-1)

    The 2 SP's I use are duplicated across 5 DB's, but is the same.

    Here is the Stored Procedure for the Current GL Balance portion:

    USE [ST_TEST]
    GO
    /****** Object:  StoredProcedure [dbo].[yRptSP_GetCurrentBalanceWithFX]    Script Date: 2/6/2018 11:02:44 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author: Marli Schutte	
    -- Create date: 01/23/2018
    -- Description:	Gets the data for the Daily Bank Balance report
    -- Original query written by Kennedy T on SCN & Dell Stinnett-Christy
    -- =============================================
    ALTER PROCEDURE [dbo].[yRptSP_GetCurrentBalanceWithFX] 
    AS
    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
    ),
    rates as
    (
    --select 1 as value
    
    
    	SELECT 
    		--getdate(), 
    		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())
    ),
    usdrate as
    (
    -- For non-US companies:
    --SELECT  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())
    --and T1.Currency = '$'
    
    
    -- For US companies:
    select 1 as Rate
    )
    --In US DB's CASE statements that makes c.ActCurr = 1 IF it is '##' and "$" will not be in issue
    --In non-US DB's it will need extra logic, as when a "$ is found it most probably needs to be converted into System Currency
    
    
    	SELECT 'ST_TEST' as company
    		  ,c.Account AS Accountcode
    		  ,c.AcctName
    		  ,sum(de) - sum(cr) AS Closing
    		  ,c.ActCurr as AccountCurrency
    		  ,rates.Rate
    		  ,case c.ActCurr
    			when '##' then 1
    			when '$' then 1
    			else rates.Rate
    		  end as DisplayRate
    		   ,case c.ActCurr
    			when '##' then (sum(de) - sum(cr))
    			when '$' then (sum(de) - sum(cr))
    			else (sum(de) - sum(cr)) * rates.Rate
    		  end as ValueInSysCurrency
    		  --, (sum(de) - sum(cr)) * rates.Rate as 'ValueInSysCurrency'
    		  ----, if(true,1, 2) as 'ValueInSysCurrency'
    		  , (select Rate from usdrate) as USDRate
    		  ,case c.ActCurr
    			when '##' then (sum(de) - sum(cr)) / (select Rate from usdrate)
    			when '$' then (sum(de) - sum(cr)) / (select Rate from usdrate)
    			else (sum(de) - sum(cr)) * rates.Rate / (select Rate from usdrate)
    		  end asValueInUSD
    
    
    
    
    	FROM   qry c left outer join rates
    	on c.ActCurr = rates.Currency
    	GROUP BY c.Account, c.AcctName, c.ActCurr, rates.Rate
    	ORDER BY 
    	  case
    		when CHARINDEX('Clearing', c.AcctName) > 1 then 'z'+ c.AcctName
    		else c.AcctName
    	  end
    END
    

    Here is the Stored Procedure for the Bank Balances UDT portion:

    USE [ST_TEST]
    GO
    /****** Object:  StoredProcedure [dbo].[yRptSP_GetCurrentUDTBANKBalanceWithFX]    Script Date: 2/6/2018 11:02:57 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		Marli Schutte
    -- Create date: 2/2/2018
    -- Description:	Pulling Bank Balance (not GL) entered in UDT @BANKBALANCES adn converting it to USD
    -- =============================================
    ALTER PROCEDURE [dbo].[yRptSP_GetCurrentUDTBANKBalanceWithFX] 
    	AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    
        -- Insert statements for procedure here
    WITH qry as 
    
    
    	(
    Select T0.U_SYS_Number
    		,T0.U_Account_Name
    		,T0.U_Bank_Balance
    		,T1.ActCurr 
    	FROM [@ABANKBALANCE] T0 INNER JOIN OACT T1 
    			ON T0.U_SYS_Number = T1.[AcctCode]
    	GROUP BY T0.U_SYS_Number
    	,T0.U_Account_Name
    	,T0.U_Bank_Balance
    	,T1.ActCurr 
    	),
    
    
    	rates as
    (
    --select 1 as value
    
    
    	SELECT 
    		--getdate(), 
    		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())
    ),
    usdrate as
    (
    -- For non-US companies:
    --SELECT  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())
    --and T1.Currency = '$'
    
    
    -- For US companies:
    select 1 as Rate
    )
    --In US DB's CASE statements that makes c.ActCurr = 1 IF it is '##' and "$" will not be in issue
    --In non-US DB's it will need extra logic, as when a "$ is found it most probably needs to be converted into System Currency
    
    
    	SELECT 'ST_TEST' as company
    		  ,c.U_SYS_Number --AS Accountcode
    		  ,c.U_Account_Name
    		  ,c.U_Bank_Balance
    		  ,c.ActCurr as AccountCurrency
    		  ,rates.Rate
    		  ,case c.ActCurr
    			when '##' then 1
    			when '$' then 1
    			else rates.Rate
    		  end as DisplayRate
    		   ,case c.ActCurr
    			when '##' then (c.U_Bank_Balance)
    			when '$' then (c.U_Bank_Balance)
    			else (c.U_Bank_Balance) * rates.Rate
    		  end as ValueInSysCurrency
    		  --, (sum(de) - sum(cr)) * rates.Rate as 'ValueInSysCurrency'
    		  ----, if(true,1, 2) as 'ValueInSysCurrency'
    		  , (select Rate from usdrate) as USDRate
    		  ,case c.ActCurr
    			when '##' then (c.U_Bank_Balance) / (select Rate from usdrate)
    			when '$' then (c.U_Bank_Balance) / (select Rate from usdrate)
    			else (c.U_Bank_Balance) * rates.Rate / (select Rate from usdrate)
    		  end asValueInUSD
    
    
    
    
    	FROM   qry c left outer join rates
    	on c.ActCurr = rates.Currency
    	GROUP BY c.U_SYS_Number, c.U_Account_Name, c.U_Bank_Balance, c.ActCurr, rates.Rate
    	--ORDER BY 
    	--  case
    	--	when CHARINDEX('Clearing', c.AcctName) > 1 then 'z'+ c.AcctName
    	--	else 
    	--c.AcctName 
    
    
    	 
    END
    
    
    
    Add comment
    10|10000 characters needed characters exceeded

  • Feb 06 at 05:18 PM

    Nagarajan,

    Here is a screenshot showing Crystal where it looks like the issue is if the same SYS-number is used in the Main and Sub-reports:

    In Crystal:

    In SAP:

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 07 at 04:56 PM

    Dear Experts,

    I have started this report from scratch, did a quick check of data and all pulled correctly. It still use the same SP's I added above.

    Then I started fleshing out the Sub-reports to have all the data necessary, and on the second one I saw that in Crystal the FX (currency) is pulling differently in SAP B1 than in Crystal....

    Here is the formula on that field:

    IF {GetCurrentBalanceWithFXSTC.AccountCurrency} = '##' 
    THEN {OADM.MainCurncy}
    ELSE {GetCurrentBalanceWithFXSTC.AccountCurrency}
    
    
    Add comment
    10|10000 characters needed characters exceeded