SAP Business One Consultants | IT Support | 0843 886 1449

All posts tagged SAP Business One HANA SQL

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

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