SAP Business One Consultants | IT Support | 0843 886 1449

All posts tagged SAP Business One Reports

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 to 30 seconds with a slight rewrite of the report – anyone who has been in a situation where there screen is frozen for 90 minutes will know how frustrating it is and what a massive improvement this is.

Ways to improve your reporting:

  • Is it possible to improve the Joins?
  • Is it possible to reduce the data calls?
  • Will a database View reduce the run time?
  • Can a temporary table(s) be created to reduce the run time?
  • Is the report written in the best possible way?
  • Are there unnecessary joins?
  • Can the data be accessed from ‘better’ sources than already used?
  • Are you able to create a better report from multiple sources rather than a nested, cascading report?

If you still have slow reports after addressing the above points then contact us and we will help you reduce the run time.

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 join OSRN T5 on T3.SnBMDAbs = T5.AbsEntry and T3.ItemCode = T5.ItemCode
where
T1.AbsEntry >= 0 and T1.WhsCode >= @WhsCode and T1.WhsCode <= @WhsCode and (T2.AbsEntry is not null)
and T0.ItemCode in((select U0.ItemCode from OITM U0 inner join OITB U1 on U0.ItmsGrpCod = U1.ItmsGrpCod
      where U0.ItemCode is not null and U0.ItemCode >= @ItemCode and U0.ItemCode <= @ItemCode))
and T1.BinCode = @BinLocation
union all
select
T0.BinAbs, T0.ItemCode, T3.onHandQty, T4.DistNumber, T4.MnfSerial,
T4.LotNumber, T5.DistNumber, T5.MnfSerial, T5.LotNumber, T5.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 join OSRN T5 on T3.SnBMDAbs = T5.AbsEntry and T3.ItemCode = T5.ItemCode
where
T1.AbsEntry >= 0 and T1.WhsCode >= @WhsCode and T1.WhsCode <= @WhsCode and (T3.AbsEntry is not null)
and T0.ItemCode in((select U0.ItemCode from OITM U0 inner join OITB U1 on U0.ItmsGrpCod = U1.ItmsGrpCod
      where U0.ItemCode is not null and U0.ItemCode >= @ItemCode and U0.ItemCode <= @ItemCode))
and T1.BinCode = @BinLocation
union all
select
T0.BinAbs, T0.ItemCode, T0.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 join OSRN T5 on T3.SnBMDAbs = T5.AbsEntry and T3.ItemCode = T5.ItemCode
where
T1.AbsEntry >= 0 and T1.WhsCode >= @WhsCode and T1.WhsCode <= @WhsCode
and (T2.AbsEntry is null and T3.AbsEntry is null)
and T0.ItemCode in((select U0.ItemCode from OITM U0 inner join OITB U1 on U0.ItmsGrpCod = U1.ItmsGrpCod
and T1.BinCode = @BinLocation
where
U0.ItemCode is not null and U0.ItemCode >= @ItemCode and U0.ItemCode <= @ItemCode))

Business Partner Email Report SQL HANA

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

 

 

 

 

SAP Business One HANA SQL version –

/*

 

–Start. Marketing Email List V1.1

SELECT T1.”CardCode”, T0.”CardCode”, T1.”E_MailL”

FROM OCRD T0  INNER JOIN OCPR T1 ON T0.”CardCode” = T1.”CardCode”

WHERE T0.”CardType” = ‘C’

–End. Author Stuart Folley www.1dash1.co.uk  0843 886 1449

 

 

 

Your Name (required)

Your Email (required)

Your Telephone

Your Company

Subject

Your Message

 

 

SAP Business One Missing Months / Periods in Reports

Have you run a Balance Sheet, Profit and Loss or Trial Balance in B1 V8.1, 8.2 or a recently upgraded V9 and discovered that you are missing periods or months from the report? Weird, eh? It is as much use as an Octopus tentacle snooker cue, but don’t worry, it can be fixed!

The issue appeared in V8.x and is fixed in V9. The problem is, you upgrade V9 and the problem still exists. The problem has been fixed and will not occur again but you need to fix the cause of the issue to see the months again. All this requires is a quick fix to a users settings.

  1. Find the formid, itemid of the problematic grid
    a) Open SBO, find the problematic grid in a form
    b) Enable the System/Debug information(Go to Menu->View, check System/Debug information)
    c) Put the mouse on any field of the problematic grid, find the formid (The number after ‘Form=’) and the itemid(The number after ‘Item=’) on the status bar at the left bottom of SBO
    d) Record this formid and itemid
  2. Close SBO. Make sure no SBO clients are connected to the server. (This step is very important! If SBO is not closed, this solution will not work!)
  3. Open SQL Analyzer, run the update query below


Open SQL Analyzer, run the update query below.
Before running the query, do please replace:
/!Formid found in Step 1!/ to the formid found in step 1.
/!Itemid found in Step 1!/ to the itemid found in step 1.
/!Problematic User Code!/ to the user code of problematic user.
–begin
–delete all the user settings
delete cprf where formid=’/!Formid found in Step 1!/’ — for this form
and itemid=’/!Itemid found in Step 1!/’ — and this document
and usersign =
(
select internal_k from ousr where
user_code = ‘/!Problematic User Code!/’
)
–endMissing months financial report fix

 

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,

case

when OPCH.docnum is null then VPM2.docEntry

else OPCH.docnum

end as ‘docnum’

from OVPM INNER join VPM1 on OVPM.docnum = VPM1.docnum

left join  VPM2 LEFT JOIN OPCH ON VPM2.DocEntry = OPCH.DocEntry

on OVPM.docnum = VPM2.docnum

where OVPM.Canceled = ‘N’ AND VPM1.DueDate >=@fromdate AND

VPM1.DueDate <=@tilldate

*/

 

1 Dash 1 can provide you and your SAP Business One users with excellent training of the usage of SQL and Crystal Reports. If you would like to find out more simply enter your details in the boxes below or call us.

Your Name (required)

Your Email (required)

Your Phone

Subject

Training Course Topic

Your Message

SAP Business One on Hana leaves every other Small to Medium business software behind with it’s instant in-memory computing!

So what does it mean for you?

SAP Business One on Hana

Speed, accessibility and reporting within seconds for your business; Locally or Globally! Reports that take you days in other ERP software systems will take seconds on SAP Business One on Hana. This means that you will save money and time on the reports themselves but will also give you the edge over your competition.

How? You ask.

Let’s say a trend begins for one of your products that one of your competitors sell. You see the trend instantly and can react to the increase within minutes, maximising on sales and profitability. It will probably be at least a week to a month before your competitor even sees the increase!

What could this mean to your business? Find out by contacting us.

Get ahead, get SAP Business One on Hana.


 

SAP Business One on Hana

SAP’s announces that HANA powered versions of its ERP Business Suite range has gone on general sale.

The new version (Version 9) of Business One, which runs on Microsoft SQL and was announced yesterday at the Sapphire NOW conference in Florida, can be deployed on premise or in the cloud and is accessible from mobile devices.

The latest version of Business One also includes new capabilities for business partner and warehouse management, infrastructure and inventory management.

SAP Business One Powered by HANA

  • Run your entire business with a single solution that will scale as you grow
  • Drive measurable performance gains in all of your key business processes
  • Use analytics to get up-to-the second information on purchasing habits and more
  • Generate insightful, interactive reports in seconds – no delays, no waiting
  • Enjoy the power of the SAP HANA platform, priced specifically for small business
  • Access real-time business insights – for faster, smarter decision making
  • Empower employees to create standard and ad hoc reports with ease
  • Generate interactive reports in seconds – no delays, no waiting
  • Enjoy the power of the SAP HANA platform, priced for small business
  • Reduce your reliance on consultants and IT for reporting needs

The HANA in-memory database is currently SAP’s biggest technology initiative, and SAP co-CEO Jim McDermott, in a keynote Tuesday, said HANA would be the platform for all SAP product development going forward.

Version 9 SAP Business 1 powered by HANA

In a keynote Wednesday, SAP co-CEO Jim Hagemann-Snabe re-emphasized that point when he announced the general availability of Business Suite on HANA.

“I would claim that HANA is the biggest innovation in 20 years in business software,” Hagemann-Snabe said. Along with the technology’s ability process huge volumes of data, it also greatly speeds up the performance of Business Suite, SAP’s ERP and industry application set for bigger companies, he said. “This idea of putting data in main memory is a quantum leap.”

 

Find out more about the cost of SAP Business One.

Contact 1 Dash 1 to find out more about SAP Business One powered by HANA.

Your Name (required)

Your Email (required)

Your Telephone

Your Company

Subject

Your Message

Here are a few basic SQL queries to be used in SAP Business One. You can copy and paste the queries into the Query window of Business One. Simply copy between from “–begin…” to “…–end” and paste into your Query window.

SQL for Open Sales Orders

–begin
select t0.docnum, t0.cardcode, t0.cardname, t0.sysdoctotal
from ordr t0
where t0.docstatus = ‘O’
–end

By Date

–begin
select t0.docnum, t0.cardcode, t0.cardname, t0.sysdoctotal
from ordr t0
where t0,docstatus = ‘O’
and t0.docdate between [%0] and [%1]
order by t0.docdate
–end

SQL for Open Deliveries

–begin
select t0.docnum, t0.cardcode, t0.cardname, t0.sysdoctotal
from odln t0
where t0.docstatus = ‘O’
–end

SQL for Open Invoices

–begin
select t0.docnum, t0.cardcode, t0.cardname, t0.sysdoctotal
from oinv t0
where t0.docstatus = ‘O’
–end

Your Name (required)

Your Email (required)

Your Telephone

Your Company

Subject

Your Message

 

A 10 minute video of creating an advanced Crystal Report for SAP Business One. A few hints and tips are contained in the video – so don’t share!

 

 

With SAP Business One and SAP Crystal Reports, customers have access to leading solutions for printing and reporting that are in use all across the world by tens of thousands of businesses. Crystal Reports, combined with SAP Business One, the powerful, yet affordable, business management software, has been designed specifically for the needs of small and midsize enterprises.

Every SAP Business One customer receives one free license of the report designer and every licensed user of SAP Business One can run reports built with Crystal Reports.