SAP Business One Consultants | IT Support | 0843 886 1449

All posts in SAP Business One Support

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.

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

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.

Locked Business one license SAP multiple users

SAP B1 Version 9 and above users – take note!

SAP Business One users who are temporarily locked out whilst another machine is logged in as the same user isn’t an error! It is new, core functionality to stop license over usage (read as abuse!).  Once the second machine has logged off the first user unfreezes.

Please note:
If a SAP B1 named user is being used for interfaces/ APIs then there are possibilities that locks or blocks will occur! Not good for an interface and there are better ways of overcoming this.

A bit about the SAP Business One license model.

SAP Business One licenses are based on a named user licensing model. Each user name needs to have a license granted to it.   A named user is an employee who is authorized to access directly or indirectly the licensed SAP Business One software, which is enforced by License Policy Technical Enforcement features in version 9.0.

Employees may not share a single user name to logon to SAP Business One. Licenses assigned to a named user are not specific to a single company database.

Locked Business one license SAP multiple users

So What/Why has it changed?

In prior releases of SAP Business One up to version 8.82 using the same user name, a named user can log on to SAP Business One twice, for example to access a second company. In reality however, this is not the case. Up to the 8.82 SAP Business One version level, even with a clear emphasis of a named user licensing model (one named user utilizing/assigned one SAP Business One license)   the system allows 2 possible login to the same database company using the same user name and password. This then allows customers to have a work around of having two employees using the same login and password to process things within an SAP Business One database company. The drawback in this case is that instead of one license needed for the two employees it now only requires each user to have their own login and password.

Now with the release of SAP Business One version 9.0, inquiries started to pour in as more customers from the SAP Business One 8.8 versions started upgrading to the latest release of SAP Business One. The scenario where the same name user and password logging in as two separate instances to the same database does not work anymore. This has led to many customers asking the questions “Why I have been allowed to do this in SAP Business One 8.82 version and earlier why not now?”.

What to do?

Basically, if you are under licensed, you will need to purchase more licenses!

If you don’t currently benefit from SAP Business One (err, why on Earth did you find this page??!!) but would like a powerful and easy business system to use for your business, get in touch!

 

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

 

SAP Business One Year End Closing Instructions

SAP kindly provide a 50 page document which provides in depth instructions on the Year End process but most people only need to know how to run the actual Year End process in SAP B1.  Because it is a once yearly procedure, it is forgotten year to year. Here are the instructions for running the Year End process in Business One.

Year End Process in SAP B1

To carry forward the balances of the P&L accounts to the corresponding account in the balance sheet
(or several different balance sheets), you can use the period-end closing function. You can store the
results initially as a report and then post them at a later stage.
If you make postings after entering the balances carried forward, you need to restart the period-end
closing routine to include these subsequent postings.

EXAMPLE
Administration / Utilities / Period-End Closing

When you execute the report, the Period-End Closing window opens:

In the Period-End Closing – Selection Criteria window, you can specify the same account
in the Retained Earnings Account and the Period-End Closing Account fields.

Once you have executed the period-end closing for the selected expenses and revenue accounts, 2
transactions are created for each account and 2 journal entries are automatically created to reflect
those transactions.
The balances of those P&L accounts are posted to the period-end closing account on the same day
(the last day of the period) and the balances are reduced to zero. The first journal entry reflects this.
On the first day of the subsequent period, the period-end closing account is posted to the
corresponding balance carried forward to the retained earnings account in the balance sheet. The
second journal transaction reflects this.

The balance of the period-end closing account is zero.

This is how to run the SAP Business One Year End Process.

 

Contact Stuart Folley @ 1Dash1 if you have any questions or suggestions.

Your Name (required)

Your Email (required)

Your Telephone

Your Company

Subject

Your Message

 

 

Down Payment Interim Account & Down Payment Clearing Account

An explanation / definition of what the Down Payment Interim and Clearing Accounts are for and how they are used.

When a company receives or makes a down payment, the payment amount is recorded in an interim account before it’s deducted from the final transaction. For period end auditing purposes, the transactions that make up the value of the interim accounts must be easily auditable.

In SAP Business One, postings are made to the following two interim accounts in the down payment process:

  • Down payment clearing account
  • Down payment interim account

The postings to these two accounts are automatically reconciled as follows:

  • Down Payment Request ProcessWhen you create a payment for an A/R or A/P down payment request, the down payment interim account is debited or credited with the payment amount, and the down payment clearing account is credited or debited with the payment amount excluding tax.

    When you fully or partially draw the down payment to the final A/R or A/P invoice, the down payment interim account is credited or debited with the drawn amount, and the down payment clearing account is debited or credited with the drawn amount excluding tax.

    As a result, the postings to the down payment interim account and the down payment clearing account are fully or partially reconciled accordingly.

  • Down Payment Invoice ProcessWhen you create a full or partial payment for an A/R or A/P down payment invoice, the down payment clearing account is credited or debited with the invoice amount excluding tax.

    When you fully or partially draw the down payment to the final A/R or A/P invoice, the down payment clearing account is debited or credited with the drawn amount excluding tax.

    As a result, the postings to the down payment clearing account are fully or partially reconciled accordingly.

In the above two processes, if the final A/R or A/P invoice is then fully or partially drawn to a credit memo, the postings to the two accounts generated by the A/R or A/P invoice are proportionately reversed and reconciled.

However, if either of the two accounts has been fully, manually reconciled before the automatic system reconciliation takes place, SAP Business One does not perform the system reconciliation.

If you would like further help and support with SAP Business One contact 1 Dash 1 and a qualified SAP Business One consultant will contact you to discuss your requirements.

 

Your Name (required)

Your Email (required)

Your Telephone

Your Company

Subject

Your Message

SAP Business One – Date Deviates From Permissible Range

The ever popular Date Deviates From Permissible Range error message appears for two reasons and IS easily solved!

The usual occurrence is in the New Year when new posting periods need to be created. To resolve this simply create a new posting year/period in (and watch out for your sub-periods!):

Within SAP Business One in the Main Menu or top menu Modules option go to –

Administration –> System Initialisation –> Posting Periods

The second occurrence is when you are trying to post documents with a due date that is not covered by the created Posting Periods Due Date range. This usually occurs towards the end of the year. You can solve this problem with the following solution.

Administration –> System Initialisation –> Posting Periods

Change the Due Date range to allow you to enter the document.