SAP Business One Consultants | IT Support | 0843 886 1449

All posts in SAP Business One

SAP Business One. ERP system solution. Small & Medium Business system.

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.

I have been asked this on site a few times over the last few months and thought it worthwhile to post the answer for anyone looking for an answer.

Question:

Is it possible to change the order of the User Defined Fields (UDFs) in SAP Business One?

Answer:

The system automatically assigns a numbering order to the UDFs as they are created. It is possible to change the order of the UDFs. You have to open the form that you wish to change the UDF order of and ensure that you can see the UDF window. You then go to Tools/ Customisation Tools/ Settings/ Order and reassign the numbering order here. Number 1 will be the top UDF displayed. Please note that it is worth mentioning that if you have over 100 UDFs this may take some timeif you want a completely different order than already displayed!

Another solution is to apply categories to the UDFs which you can select at the top of the UDF window on the form. This is a neat and simple solution if you are able to categorise your UDFs in a sensible manner.

Call 0843 886 1449 now to find out how a site visit can give you more from your system.

Exciting times at 1 Dash 1. Over the last 2 weeks we have been assisting an Oil industry client by building a Production Process solution to enable them to work more efficiently and provide their customers with the best service possible by ensuring that their purchases are received within budget and on time before time. Not only has the new system reduced data entry by 90% but the ability to see all the required data in an easy to read and in real time has vastly improved how the system users can perform their jobs.

The solution consisted of automatically calculating fields, creating new fields to capture data which was previously maintained across several spreadsheets, drawing and replicating data from parent to child forms, real time reports, real time supplier reports, capturing technical sheets, capturing and providing certification and tolerance documents and much, much more.

Saving our clients time and money is what we strive to provide and most importantly it’s what we enjoy!

If your company requires assistance with improving a process whether it is in SAP Business One or anything else, we can help.

 

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 !!

SAP Support Desk History

Exciting times! Our SAP Business One support lines are now open!

SAP Business One Support

What’s included in our support services?

  • Telephone Support
  • Email Support
  • Remote Access Support

SAP Business One Support Line

Ad Hoc Support Prices

Only £37.50 + VAT per support call*
Monthly Support

Just £500 + VAT per Month for unlimited users **

<< Check out the Support page in detail here >>

 

* & ** – See our main support page for more details.

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))

This is a short and to the point Crystal Reports post to assist anyone trying to count the number of Groups in a report. If you are using Crystal Reports you will know that the Summary does not work on Groups. We hope you find the following useful!

Crystal Report Group Count

To count the number of Crystal Report group entries in a report, rather than the entries within a Group, use the following process:

1) Create a formula @GroupCountReset and place this formula in the page header and make it suppressed (hidden)

whileprintingrecords;

numbervar i:=0;

2) Create another formula @GroupCountEval place this in the group header where you want to count the entries and suppress if required

whileprintingrecords;

numbervar i:= i+1;

3) Create another formula @GroupCountDisplay and place this in report footer. Don’t suppress this one! You need to see it!

whileprintingrecords;

numbervar i;

 

You should now have a count of Group entries.

Useful information for Crystal Reports and Crystal Reports for SAP Business One.

SAP Business One 9.0 Business Accounting System

SAP Business One 9.0 Advanced GL Account Determination

SAP Business One 9.0 provides a flexible and centralised system to manage inventory G/L account determination. With the advanced G/L determination rules, you can assign inventory G/L accounts by item groups, items, warehouses, business partner groups, ship-to countries, and various combinations of all the above criteria.

 

SAP Business One 9.0 Financials

Deferred Tax Enablement in Manual JE

SAP Business One 9.0 enables you to manage deferred tax in manual journal entries, journal vouchers, posting templates, and recurring postings

 

SAP Business One 9.0 Fixed Assets

SAP Business One 9.0 allows you to manage fixed assets using a fully integrated solution. In addition to the functions provided by the Fixed Asset Solution in SAP Business One 8.82, the fixed assets functionality implemented in SAP Business One 9.0 enables you to do many things that were out of reach with previous versions of SAP Business One.

In order for you to better manage your fixed assets in SAP Business One, SAP Business One 9.0 has also enhanced some functionality from the add-on, such as the use of the ‚ ‘Declining Balance‛‚ ‘Special Depreciation‛, and ‘Multilevel‛ depreciation methods, depreciation forecast, depreciation run, asset history sheet, and so on.

 

Internal Reconciliation in SAP Business One 9.0

The internal reconciliation functionality has been enhanced to support the following system currency aspects:

  • When performing internal reconciliation, conversion difference postings are automatically created
  • You can display system currency balances due, in various windows, and reports
  • The Conversion Differences report has been enhanced with an optimised calculation of the unrealised conversion difference

 

SAP Business One 9.0 Sales and Purchasing

Cancellation of Sales and Purchasing Documents

You can now cancel most sales and purchasing documents that normally trigger postings. Upon cancelling such a document, a corresponding cancelation document is created to reverse the associated accounting, tax, and inventory changes. The base documents belonging to the cancelled documents are reopened with balances due restored. You can now copy these base documents to new documents.

 

Adjusting Total Freight in Goods Receipt POs Based on A/P Reserve Invoices

SAP Business One 9.0 supports adjustment of the total freight when creating a goods receipt PO based on A/P reserve invoices under the following conditions:

  • The freight of the goods receipt PO does not exceed the unallocated freight of the base A/P reserve invoices
  • If you have allocated all open quantities of the base A/P reserve invoices to this goods receipt PO, you must allocate all the remaining freight of the reserve invoices to the goods receipt PO

Adjusting Item Cost in Non-Based A/R Return and A/R Credit Memo

The item cost in non-based A/R return and an A/R credit memo is determined by item cost at the time of the document creation. Starting from SAP Business One 9.0, the user can adjust the item cost in these documents. This enhancement applies only to items managed by Moving Average Price or FIFO methods.

 

SAP Business One 9.0 Banking

Payment, Cheque, and Deposit Cancellation Improvements

With SAP Business One 9.0, you are able to do the following:

  • Cancel incoming payments with deposited cheques
  • Cancel deposits partially
  • Cancel deposited cheques
  • Cancel non-deposited cheques with automatic journal entries

 

Down Payment Request Reconciliation

SAP Business One 9.0 enables you to manually reconcile, fully or partially, a payment of down payment request with an invoice (regular invoice or reserve invoice, correction invoice is not supported).

 

SAP Business One 9.0 Inventory

Bin locations in SAP Business One 9.0 allows you to manage bin locations for your warehouses. You can enable the bin location function for individual warehouses.

 

Bin Location Related Documents

After you enable the bin location function for intended warehouses, you need to record bin locations in those warehouses for all receipts and issues of inventory, including the processing of the following documents or transactions:

  • Goods receipt PO, goods return, A/P invoice, A/P credit memo, delivery, return, A/R invoice, A/R invoice + payment, A/R credit memo
  • Goods receipt, goods issue, inventory transfer, inventory taking
  • Receipt from production, issue for production
  • Pick and Pack

During the receipt, issue, and transfer of goods, SAP Business One 9.0 lets you automate the process of allocating goods from and to bin locations. You also can perform the allocation manually.

 

Inventory in Bin Locations

To better manage the inventory in your bin locations, SAP Business One 9.0 enables you to do the following:

 

  • View the information of stored items, such as the item quantity, and number of items
  • Restrict the storage of your bin locations, for example, to a particular batch, item, or item group
  • Restrict the use of your bin locations, for example, to receiving or issuing goods only
  • Replenish the inventory in your bin locations if the inventory level falls below the minimum

Bin Location Management

To facilitate the management of bin locations, SAP Business One 9.0 supports the generation, update, and deletion of bin locations in batches.

 

Serials and Batches

SAP Business One 9.0 lets you maintain serials and batches in your bin locations. When you issue serials and batches from bin locations, you can decide whether you want to pick the items according to their bin locations, or their serial and batch information.SAP Business One 9.0 lets you maintain serials and batches in your bin locations. When you issue serials and batches from bin locations, you can decide whether you want to pick the items according to their bin locations, or their serial and batch information.

 

Receiving Bin Locations
SAP Business One 9.0 allows you to designate certain bin locations as receiving bin locations. When you receive goods, these goods can first be automatically placed in receiving bin locations.

 

Reports

You can get an overview of all bin locations, and check the inventory statuses and movements in bin locations using the following reports:

 

  • Bin Location List
  • Bin Location Content List
  • Inventory Posting List

Multiple Unit of Measurement (UoM) in SAP Business One 9.0

SAP Business One 9.0 provides a high degree of flexibility to your management of products in multiple units of measurement (UoM). The Multiple UoM feature enables you to sell, purchase, receive and ship products in any UoM that your business needs, and to define default UoMs for various types of transactions. All sales and purchasing documents support items with multi-UoM definitions.The highlights of this new feature are as follows:

  • Unlimited global and product-specific UoMs
  • Setup of UoM groups with conversion rules between base UoMs and alternative UoMs within the groups
  • These UoM groups are used in item master data, marketing documents, and inventory documents
  • Default sales UoMs, purchasing UoMs, inventory UoMs, and automatic conversion to other applicable UoMs in transaction processing
  • Related enhancement of packaging, bar code and pricing functions:
  • Package settings for multiple UoMs are incorporated into item master data and are used in sales documents to recommend the number of packages
  • For an item using multiple UoMs, you can define multiple bar codes and different prices for each of its UoMs

Note that production transactions are processed in a single inventory UoM.

Price List and Discount Enhancements

In SAP Business One 9.0, the price list and the discount-groups functionality has been enhanced to improve efficiency and to support specific business processes. Using the enhanced price list function you can now do the following:

  • Assign a price list to a business partner group
  • Define multiple currency prices in a specific price list or in a special prices list
  • Define prices and special prices for different units of measurement, of a specific item
  • Define price lists as active or inactive

In addition, you can now decide whether to remove un-priced items from the database in order to reduce the database size and impact on performance when running applications on the database.Using the enhanced discount groups function you can now do the following:

  • Define discount groups for all business partners, for customer groups or for vendor groups
  • Define discounts for specific items
  • Define variable discounts based on quantity bought
  • Define discount groups as active or inactive
  • Define discountable control for a business partner and for an item
  • Allocate multiple discounts

You can get an overview of all prices and discounts using the following reports:

  • Price report
  • Discount report

These reports are also available from within marketing documents, enabling the user to track the source price and discount of each item during document creation.

Serial and Batch Items in Drop Ship Warehouses

SAP Business One 9.0 now lets you manage serial and batch items in drop ship warehouses. After you enable the serial and batch management for your drop ship warehouses, you can do the following:

  • Allocate serial and batch items in drop ship warehouses to newly created documents. The documents include the following:
  • Sales order, delivery, return, A/R invoice, A/R invoice + payment, A/R credit memo, A/R Debit Memo, A/R reserve invoice
  • Goods receipt PO, goods return, A/P invoice , A/P credit memo, A/P Debit Memo
  • Maintain the serial and batch information for items in drop ship warehouses using Serial Number Management and Batch Management
  • View the transactions of the serial and batch items in drop ship warehouses using Serial Number Transactions Report and Batch Number Transactions Report

In addition, if your serial items in drop ship warehouses are delivered, SAP Business One can create customer equipment cardsautomatic
ally for these items in the same way as for the items in non-drop ship warehouses.

Inventory Tracking

SAP Business One 9.0 provides new capabilities for running a complete inventory taking process. It starts with cycle counting alerts and continues with well-structured tasks and innovated reporting.Major enhancements include the following:

  • Providing documents to record and manage inventory counting and inventory posting
  • Freezing items during inventory counting
  • Recording counting results and variance by two different inventory takers
  • Improving the cycle count function to enable continuation of counting process from cycle count alerts and recommendations
  • Counting the exact serial numbers and batches
  • Supporting inventory taking for bin locations
  • Providing inventory-taking reports to consolidate and analyse existing inventory counting and inventory posting documents
  • Providing an Inventory Counting Recommendation dashboard report
  • Providing documents to record and manage inventory opening balances
  • Counting by unit of measurement