May i know how to convert the SAP B1 MS SQL crystal report to HANA

[hotwebs]

Hi Expert,

Is anyone experience how to convert the SAP B1 crystal report MS-SQL to HANA version. 

I have 2 company running in different platform, appreciate anyone has any idea, how to convert it, it would be much appreciate. 

Crystal Report MS-SQL (Stock Usage by Month) USE Database_Name] GO /****** Object:  StoredProcedure [dbo].[StockUsage_ByMonth]     ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --exec StockUsage_ByMonth '20240101', 'MK001', 'MK002' ALTER Procedure [dbo].[StockUsage_ByMonth]  ( @PrintDate nvarchar(20), @ItemCodeFrom nvarchar(20), @ItemCodeTo nvarchar(20) ) As Begin If @PrintDate is null  Set @PrintDate=cast(getdate() as nvarchar) If @ItemCodeFrom is null or @ItemCodeFrom=''  Select @ItemCodeFrom='' If @ItemCodeTo is null or @ItemCodeTo=''  Select @ItemCodeTo='' /*Get Monthly Usage*/ Select T0.ItemCode, --First month sum(isnull(MovementQty,0)) as 'MovementQty1' Into #MonthlyUsage1 from [SIInventoryMovement] T0 Where T0.TransactionMvmtType='USAGE'  --and T0.ItemCode='MK001' and (@ItemCodeFrom='' or T0.ItemCode >= @ItemCodeFrom) and (@ItemCodeTo='' or T0.ItemCode <= @ItemCodeTo) and T0.DocDate>=CONVERT(varchar,DATEADD(MONTH, DATEDIFF(MONTH, 0, cast(@PrintDate as Date))-1, 0),112)        --T0.DocDate smaller than 1st day of current month and T0.DocDate<=CONVERT(varchar,DATEADD(MONTH, DATEDIFF(MONTH, -1, cast(@PrintDate as Date))-1, -1),112) Group by T0.ItemCode Select T0.ItemCode, --Second month sum(isnull(MovementQty,0)) as 'MovementQty2' Into #MonthlyUsage2 from [SIInventoryMovement] T0 Where T0.TransactionMvmtType='USAGE'  --and T0.ItemCode='MK001' and (@ItemCodeFrom='' or T0.ItemCode >= @ItemCodeFrom) and (@ItemCodeTo='' or T0.ItemCode <= @ItemCodeTo) and T0.DocDate>=CONVERT(varchar,DATEADD(MONTH, DATEDIFF(MONTH, 0, cast(@PrintDate as Date))-2, 0),112)        --T0.DocDate smaller than 1st day of current month and T0.DocDate<=CONVERT(varchar,DATEADD(MONTH, DATEDIFF(MONTH, -1, cast(@PrintDate as Date))-2, -1),112) Group by T0.ItemCode Select T0.ItemCode, --Third month sum(isnull(MovementQty,0)) as 'MovementQty3' Into #MonthlyUsage3 from [SIInventoryMovement] T0 Where T0.TransactionMvmtType='USAGE'  --and T0.ItemCode='MK002' and (@ItemCodeFrom='' or T0.ItemCode >= @ItemCodeFrom) and (@ItemCodeTo='' or T0.ItemCode <= @ItemCodeTo) and T0.DocDate>=CONVERT(varchar,DATEADD(MONTH, DATEDIFF(MONTH, 0, cast(@PrintDate as Date))-3, 0),112)        --T0.DocDate smaller than 1st day of current month and T0.DocDate<=CONVERT(varchar,DATEADD(MONTH, DATEDIFF(MONTH, -1, cast(@PrintDate as Date))-3, -1),112) Group by T0.ItemCode Select T0.ItemCode,  T0.ItemName,  T1.ItmsGrpNam, T0.InvntryUOM, isnull(T4.MovementQty3,0) as 'MovementQty3', isnull(T3.MovementQty2,0) as 'MovementQty2', isnull(T2.MovementQty1,0) as 'MovementQty1', sum((isnull(T4.MovementQty3,0) + isnull(T3.MovementQty2,0) + isnull(T2.MovementQty1,0))/3) as 'AvgMovementQty', T0.MinLevel,  T0.MaxLevel,  T0.OnHand,  sum(isnull(T0.OnHand,0)-isnull(T0.MinLevel,0)) as [Stock Required], T0.OnOrder as [Open PO Qty], sum(isnull(T0.OnHand,0)-isnull(T0.MinLevel,0)+isnull(T0.OnOrder,0)) as [Nett Required], T0.LeadTime from OITM T0 Left Join OITB T1 on T1.ItmsGrpCod=T0.ItmsGrpCod Left Join #MonthlyUsage1 T2 on T2.ItemCode=T0.ItemCode Left Join #MonthlyUsage2 T3 on T3.ItemCode=T0.ItemCode Left Join #MonthlyUsage3 T4 on T4.ItemCode=T0.ItemCode where --T0.ItemCode='MK001' (@ItemCodeFrom='' or T0.ItemCode >= @ItemCodeFrom) and (@ItemCodeTo='' or T0.ItemCode <= @ItemCodeTo) Group by T0.ItemCode, T0.ItemName, T0.ItmsGrpCod, T0.InvntryUOM, T0.MinLevel, T0.MaxLevel, T0.OnHand, T0.IsCommited, T0.OnOrder,T0.LeadTime, T1.ItmsGrpNam, T2.MovementQty1, T3.MovementQty2, T4.MovementQty3 Order by T0.ItemCode Drop table #MonthlyUsage1 Drop table #MonthlyUsage2 Drop table #MonthlyUsage3 End select top 10 * from OITM where oitm.IsCommited<>0

[CarolHunter1]

---

@hotwebsBallSportsGear wrote:

Hi Expert,

Is anyone experience how to convert the SAP B1 crystal report MS-SQL to HANA version. 

I have 2 company running in different platform, appreciate anyone has any idea, how to convert it, it would be much appreciate. 

Crystal Report MS-SQL (Stock Usage by Month) USE Database_Name] GO /****** Object:  StoredProcedure [dbo].[StockUsage_ByMonth]     ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --exec StockUsage_ByMonth '20240101', 'MK001', 'MK002' ALTER Procedure [dbo].[StockUsage_ByMonth]  ( @PrintDate nvarchar(20), @ItemCodeFrom nvarchar(20), @ItemCodeTo nvarchar(20) ) As Begin If @PrintDate is null  Set @PrintDate=cast(getdate() as nvarchar) If @ItemCodeFrom is null or @ItemCodeFrom=''  Select @ItemCodeFrom='' If @ItemCodeTo is null or @ItemCodeTo=''  Select @ItemCodeTo='' /*Get Monthly Usage*/ Select T0.ItemCode, --First month sum(isnull(MovementQty,0)) as 'MovementQty1' Into #MonthlyUsage1 from [SIInventoryMovement] T0 Where T0.TransactionMvmtType='USAGE'  --and T0.ItemCode='MK001' and (@ItemCodeFrom='' or T0.ItemCode >= @ItemCodeFrom) and (@ItemCodeTo='' or T0.ItemCode <= @ItemCodeTo) and T0.DocDate>=CONVERT(varchar,DATEADD(MONTH, DATEDIFF(MONTH, 0, cast(@PrintDate as Date))-1, 0),112)        --T0.DocDate smaller than 1st day of current month and T0.DocDate<=CONVERT(varchar,DATEADD(MONTH, DATEDIFF(MONTH, -1, cast(@PrintDate as Date))-1, -1),112) Group by T0.ItemCode Select T0.ItemCode, --Second month sum(isnull(MovementQty,0)) as 'MovementQty2' Into #MonthlyUsage2 from [SIInventoryMovement] T0 Where T0.TransactionMvmtType='USAGE'  --and T0.ItemCode='MK001' and (@ItemCodeFrom='' or T0.ItemCode >= @ItemCodeFrom) and (@ItemCodeTo='' or T0.ItemCode <= @ItemCodeTo) and T0.DocDate>=CONVERT(varchar,DATEADD(MONTH, DATEDIFF(MONTH, 0, cast(@PrintDate as Date))-2, 0),112)        --T0.DocDate smaller than 1st day of current month and T0.DocDate<=CONVERT(varchar,DATEADD(MONTH, DATEDIFF(MONTH, -1, cast(@PrintDate as Date))-2, -1),112) Group by T0.ItemCode Select T0.ItemCode, --Third month sum(isnull(MovementQty,0)) as 'MovementQty3' Into #MonthlyUsage3 from [SIInventoryMovement] T0 Where T0.TransactionMvmtType='USAGE'  --and T0.ItemCode='MK002' and (@ItemCodeFrom='' or T0.ItemCode >= @ItemCodeFrom) and (@ItemCodeTo='' or T0.ItemCode <= @ItemCodeTo) and T0.DocDate>=CONVERT(varchar,DATEADD(MONTH, DATEDIFF(MONTH, 0, cast(@PrintDate as Date))-3, 0),112)        --T0.DocDate smaller than 1st day of current month and T0.DocDate<=CONVERT(varchar,DATEADD(MONTH, DATEDIFF(MONTH, -1, cast(@PrintDate as Date))-3, -1),112) Group by T0.ItemCode Select T0.ItemCode,  T0.ItemName,  T1.ItmsGrpNam, T0.InvntryUOM, isnull(T4.MovementQty3,0) as 'MovementQty3', isnull(T3.MovementQty2,0) as 'MovementQty2', isnull(T2.MovementQty1,0) as 'MovementQty1', sum((isnull(T4.MovementQty3,0) + isnull(T3.MovementQty2,0) + isnull(T2.MovementQty1,0))/3) as 'AvgMovementQty', T0.MinLevel,  T0.MaxLevel,  T0.OnHand,  sum(isnull(T0.OnHand,0)-isnull(T0.MinLevel,0)) as [Stock Required], T0.OnOrder as [Open PO Qty], sum(isnull(T0.OnHand,0)-isnull(T0.MinLevel,0)+isnull(T0.OnOrder,0)) as [Nett Required], T0.LeadTime from OITM T0 Left Join OITB T1 on T1.ItmsGrpCod=T0.ItmsGrpCod Left Join #MonthlyUsage1 T2 on T2.ItemCode=T0.ItemCode Left Join #MonthlyUsage2 T3 on T3.ItemCode=T0.ItemCode Left Join #MonthlyUsage3 T4 on T4.ItemCode=T0.ItemCode where --T0.ItemCode='MK001' (@ItemCodeFrom='' or T0.ItemCode >= @ItemCodeFrom) and (@ItemCodeTo='' or T0.ItemCode <= @ItemCodeTo) Group by T0.ItemCode, T0.ItemName, T0.ItmsGrpCod, T0.InvntryUOM, T0.MinLevel, T0.MaxLevel, T0.OnHand, T0.IsCommited, T0.OnOrder,T0.LeadTime, T1.ItmsGrpNam, T2.MovementQty1, T3.MovementQty2, T4.MovementQty3 Order by T0.ItemCode Drop table #MonthlyUsage1 Drop table #MonthlyUsage2 Drop table #MonthlyUsage3 End select top 10 * from OITM where oitm.IsCommited<>0

---

Hello, @hotwebs 

I can see your query i give you my best please see below and follow information...

To convert the SAP B1 crystal report MS-SQL to HANA version, you need to do the following steps:

1. Convert the SQL queries in the stored procedure and the command to HANA compatible syntax. You can use any converter tools or do it manually. For example, you need to replace the # sign with the colon sign for temporary tables, use the DATE_TRUNC function instead of the CONVERT function for date manipulation, and use the NVARCHAR type instead of the NCHAR type for string variables. 

2. Change the database connection in the crystal report from MS-SQL to HANA. You can do this by going to Database → Set Datasource Location in the SAP Crystal Report for SAP Business One. You need to select the HANA database and provide the credentials and the schema name. 

3. Verify the crystal report output and make sure it matches the expected results. You can use the SAP Business One, version for SAP HANA client to preview the report and compare it with the MS-SQL version.

I hope this helps you with your conversion. If you have any other questions, feel free to ask me. 😊

Best regard,
Carol Hunter