SAP Business One – Improving The Speed Of Reports Along with general consultancy, system implementations, training, interfaces and development of SAP Business One, one of the most requested jobs is to improve the speed of reports. Reports can be written in SQL Queries, Crystal Reports or any other reporting tool. As a rule of thumb, if a report is running slowly, it is due to either the way it was originally written or the data it is reporting on. With some out of the box thinking it is usually possible to massively reduce the way a report runs by approaching it from a different view point. I have personally managed to reduce report run times from 90 minutes…
Tag: SAP Business One SQL
SAP Business One SQL Prompt Variables /* SELECT FROM [dbo].[OSLP] T1 */ declare @slpName as varchar(155) /*WHERE*/ set @SlpName = /* T1.SlpName */ ‘[%0]’ /* SELECT FROM [dbo].[OCRD] T2 */ declare @CardCode as varchar(15) /* WHERE */ set @CardCode = /* T2.CardCode */ ‘[%1]’ SAP Business One HANA Prompt Variables /* select from [dbo].[OINV] T0 */ /* select from [dbo].[OSLP] T1 */ Declare FromDate timestamp; Declare ToDate timestamp; Declare SALESEMP char(155); SALESEMP := /* T1.”SlpName” */ ‘[%0]’; FromDate := /* T0.”DocDate” */ ‘[%1]’; ToDate := /* T0.”DocDate” */ ‘[%2]’; !! Remember to end your HANA SQL Script with a semicolon and replace the single and double quotes above which are enforced formatting !!
An SAP Business One Stock Report with All Items The following stock report will display all Items with or without quantities in Stock. select t0.ItemCode, t1.ItemName, isnull(t3.OnHandQty,0) as ‘In Stock’, t2.BinCode, t2.SL1Code, t2.SL2Code from OITW T0 inner join OITM t1 on t0.ItemCode = t1.ItemCode inner join OBIN t2 on t2.AbsEntry = T0.DftBinAbs left outer join OIBQ t3 on t0.ItemCode=t3.ItemCode and t0.WhsCode = t3.WhsCode where t2.BinCode between [%0] and [%1] order by t2.SL1Code, t2.SL2Code, t0.ItemCode
Shared by Owen Slater on the SCN SAP Business One SQL Batch or Serial Number Quantities in a specific Bin Location declare @WhsCode nvarchar(9), @ItemCode nvarchar(21), @BinLocation nvarchar(228) set @WhsCode = ’01’ set @ItemCode = ‘B10000’ set @BinLocation = ’01-ABC’ select T0.BinAbs, T0.ItemCode, T2.onHandQty, T4.DistNumber, T4.MnfSerial, T4.LotNumber, T5.DistNumber, T5.MnfSerial, T5.LotNumber, T4.AbsEntry, T1.BinCode, T4.AbsEntry, T5.AbsEntry, T1.WhsCode from OIBQ T0 inner join OBIN T1 on T0.BinAbs = T1.AbsEntry and T0.onHandQty <> 0 left outer join OBBQ T2 on T0.BinAbs = T2.BinAbs and T0.ItemCode = T2.ItemCode and T2.onHandQty <> 0 left outer join OSBQ T3 on T0.BinAbs = T3.BinAbs and T0.ItemCode = T3.ItemCode and T3.onHandQty <> 0 left outer join OBTN T4 on T2.SnBMDAbs = T4.AbsEntry and T2.ItemCode = T4.ItemCode left outer…
Want a list of your Business Partners email address for marketing? Email your business partners from your favourite email marketing site by using this report query. It’s SQL that you run from within Query Generator or SQL Server. Simply copy the code between the /* … and … */ for your version of SAP B1 and paste into the Query Generator. Here’s something for free! Have a great bank holiday weekend! SAP Business One SQL Server version – /* –Start. Marketing Email List V1.1 select t1.CardCode, t2.cardname, t1.E_MailL from ocpr t1 inner join OCRD t2 on t1.CardCode = t2.cardcode and t2.cardtype = ‘C’ –End. Author Stuart Folley www.1dash1.co.uk 0843 886 1449 */ …
Using variables in SQL queries for SAP Business One is straight forward for simple select statements but what about the more complex variables or if you want to declare a variable for your SQL? Answer: You have to get a bit more creative! See the example below. To select a from and to date as a variable in a declare statement you have to add a nifty bit of extra code. /* /SELECT FROM [dbo].[VPM1] T0/ declare @fromdate as datetime /* WHERE */ set @fromdate = /* T0.DueDate */ ‘[%0]’ /SELECT FROM [dbo].[VPM1] T1/ declare @tilldate as datetime /* WHERE */ set @tilldate = /* T0.DueDate */ ‘[%1]’ select OVPM.docnum, VPM1.dueDate,VPM1.CheckNum, VPM1.Branch, VPM1.BankCode, VPM1.CheckSum, OVPM.CardName,…