Skip to Content
0

Crystal Report imported into SAP B1 not showing all lines

Feb 05 at 07:05 PM

68

avatar image

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)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

7 Answers

Best Answer
Marli Schutte Feb 07 at 06:03 PM
0

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.


issue.png (50.1 kB)
issue-001.png (30.1 kB)
Share
10 |10000 characters needed characters left characters exceeded
Jimmy Liang Feb 05 at 08:47 PM
1

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Jimmy,

I'm a super user on all DB's, and this happens when I run the query.

Marli

0
Nagarajan K Feb 07 at 11:06 PM
1

Hi,

Great!

Thank you for sharing with us.

Regards,

Nagarajan

Share
10 |10000 characters needed characters left characters exceeded
Nagarajan K Feb 06 at 02:05 PM
0

Hi,

Share complete query here to check.

Regards,

Nagarajan

Share
10 |10000 characters needed characters left characters exceeded
Marli Schutte Feb 06 at 05:12 PM
0

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


Share
10 |10000 characters needed characters left characters exceeded
Marli Schutte Feb 06 at 05:18 PM
0

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:


crystal.png (216.4 kB)
sap.png (79.5 kB)
Share
10 |10000 characters needed characters left characters exceeded
Marli Schutte Feb 07 at 04:56 PM
0

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}


Share
10 |10000 characters needed characters left characters exceeded