Skip navigation

One of the great things about SAP Business One is how easy it is to build queries using the Query Manager andget access to the information you need thats stored inside your SAP Business One system.

 

But when you migrate to HANA, those queries need a little bit of maintenance to help them work with SAP HANA and the way it expects SQL queries to be formatted.

 

The good news is that theres some resources to help smooth the way for you put together by SAP (remember that many of these tools are provided "as is" and are not supported directly by SAP).

 

Heres a tool and some documents that will explain the process.

https://partneredge.sap.com/en/library/assets/products/ent_mng/sbo/about/sbo_b1h/versions/hat_htg_b1h_sqlconvert.html

How to Convert SQL from the Microsoft SQL Server Database to the SAP HANA Database

 

This tool enables you to convert structured query language (SQL) in the Microsoft SQL Server database (using T-SQL grammar) to SQL that can be used in the SAP HANA database (using ANSI-SQL grammar).


The zip file includes the tool, a configuration file, a token file, an API example, necessary libraries, and a guide.

 

 

I know that our community members all have different ways to keep up with the content and updates that we share here on ONE.Source. With that in mind, i am pleased to start offering a new way to get your news - the ONE.Source Podcast.

 

Please let me know what you thought of this format and of course, what you would like us to cover to make the community even more valuable for you.

 

ASUG ONE.Source - SAP Business One Community Podcast - January 2018 by Richard Duffy | Free Listening on SoundCloud

 

iStock-678860564.jpg

 

Lately I have been getting quite fixated on SQL queries with SAP Business One, and looking around to see what queries are out there and where I can find them. (You've probably seen me blogging about them on our main blog. These posts were inspired by our most popular community post Useful SAP B1 queries which dates back to 2009.)

 

The good news is that there are a lot of queries that have been written and posted on various sites on the Interwebs.

 

 

But why go looking when you have ONE.Source and Richard Duffy as my personal information butler I hear you ask?

 

 

Good point - so here's a few useful ones I found from long time social collaborator, SAP Mentor and SAP Business One community member, Tim Guest.

 

You'll also find some additional tips about using the Golden Arrows for drill downs.

 

Enjoy!!

 

and kudos to Tim Guest (and Zal Parchem) for sharing their knowledge!

 

 

Open Invoices Not Emailed:

This query shows all AP Invoices added to SAP that are Open but have not been sent. It’s easy to forget to send invoices and this report has helped us be more efficient. Please note we are using Boyum Print and Deliver module:

 

SELECT t0.DocEntry, t0.CardCode, t0.CardName, t0.DocDueDate as ‘Due Date’, t0.DocCur, t0.DocTotal

 

FROM OINV t0

 

WHERE t0.DocStatus = ‘O’ and (t0.Printed = ‘N’ and (SELECT count(*)

 

FROM [dbo].[@BOY_85_DEL_LOG]  s0

 

WHERE s0.[U_BOY_id]  = t0.DocEntry and  s0.[U_BOY_OBJECTID]  = ’13’ and  s0.[U_BOY_ACTION]  = 4) = 0)

 

Partial Stock Take

We needed a way to list items in stock AND items not in stock for stock taking so we can run an inventory report between locations. The Bin Locations Content report in SAP only shows items 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

 

Items with No Default Bin Location

We want to report on all items in SAP not assigned a Default Bin Location. This has helped our Warehouse Manager to ensure ALL stock has a Default Bin:

 

SELECT T0.[ItemCode], T0.[WhsCode], T0.[DftBinAbs] FROM OITW T0 WHERE T0.[WhsCode]  = ‘main’ and  T0.[DftBinAbs]  is null

 

Purchase Invoice Analysis

The SAP Purchase Analysis report is in System Currency, mine is GBP. No use to me when I buy in EUR. This report shows all AR Invoices for a set period for a particular Business Partner in the currency on the Invoice (FC):

 

SELECT T0.[DocEntry], T0.[CardCode], T0.[DocDate], T0.[DocTotalFC]

 

FROM OPCH T0

 

WHERE T0.[DocDate] BETWEEN [%0] AND [%1]  AND T0.[CardCode] =[%2]

 

If you want to run the same report but on Purchase Orders, simply substitute OPCH for OPOR

 

Top 10 Customers (Thanks to Zal Parchem for this)

 

SELECT TOP 10 T0.CardCode, MAX(T0.Cardname) as Customer,  SUM(T0.doctotal) as “Amount(LC)”

FROM dbo.OINV T0

WHERE t0.docdate BETWEEN [%0] AND [%1]

GROUP BY T0.CardCode

Order by SUM(T0.doctotal) DESC

 

Sales History by Item Group

Yes I know this exists in Sales Analysis reports but I prefer my layout:

 

SELECT T0.[DocEntry], T0.[DocDate],T0.[CardName], T2.[ItmsGrpCod], T2.[ItemCode], T2.[ItemName], T1.[Quantity]

 

FROM ORDR T0  INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode

 

WHERE T2.[ItmsGrpCod] = [%0] AND T0.[DocDate] BETWEEN [%1] AND [%2]

 

Sales Orders with no SLP Code

Some of our Sales People forget to enter their name when adding a Sales Order. This handy report helps me identify these quickly and easily

 

SELECT T1.[DocNum], T1.[CardName], T1.[DocDate], T0.[SlpCode]

 

FROM OSLP T0  INNER JOIN ORDR T1 ON T0.SlpCode = T1.SlpCode

 

WHERE T0.[SlpCode] = ‘-1’

 

ORDER BY T0.[SlpCode]

 

 

In addition, Barbara Peterson asked on the Q and A Forum about getting the Golden Arrow to show up on her queries - I gave here an aswewr and you can view the thread here

 

Formatted Seach

 

But I thought you might find these tips from SAP handy as well when you aren't getting the love you need from the Golden Arrows in your queries

 

Your Golden Arrows may not show when one of these conditions occurs:

 

1. The field does not represent keys of a table.
2. Queries that contain groupings.
3. Queries that are based on Archive tables ("A" tables).
4. Queries that contain UNION clause.
5. Queries that contains temporary tables.
6. Queries for Formatted Search.
7. Queries with parameters declared.
8. Queries with certain restricted names in comment section


Also add the "FOR BROWSE" statement at the end of your query.


FOR BROWSE means that the result displays the orange arrow to navigate into the master record or document behind it.

 

 

 

 

Other terms

 

 

Orange arrow, query, missing, drill, browse, golden arrow, link buttons, hide, foreign keys, SQL, appears,

 

OS_990x150_Topics_NEW2.jpg

Integration with SAP Business One

Throughout 2018, our ONE.Source curriculum will explore monthly themes from across all perspectives of the SAP Business One ecosystem: Customers, Partners, SAP themselves, as well as our Community Evangelist.

 

In February, we will cover the SAP Integration tools that are at your disposal as well as a number of other tools to manage the flow of data and transactions between SAP Business one and other applications.

 

Your ERP solution is the backbone of your business operations and SAP Business One is a fully featured solution to build your business on. However, there are scenarios where you need to use other applications, maybe for Marketing Automation, EDI, warehouse management or just simple office productivity solutions like Office365 and so you need to integrate those systems together.

 

Integration used to be the biggest consumer of ERP project budgets however there are now new solutions available that make this process much more cost effective, easier to build and easier to deploy.

 

While this curriculum is primarily facilitated through a monthly webcast series, you can also expect regular blogs, videos, and (most importantly) community discussions on this topic. Visit the ONE.Source Activity feed to stay up-to-date on the latest, follow us on Linkedin or Twitter, or like us on Facebook.

 

Webcasts

 

Discussions

 

Blogs

iStock-649229226.jpg

It goes without saying that when you are building queries and reports that you need a good reference to the database structure of application and of course SAP Business One is no different.

 

 

There are a couple of ways to determine the database structure of SAP Business One but as I was doing my regular trawling around the internet looking for handy resources to share I happened to stumble across this little gem.

 

 

SAP Tables - SAP Business One/ERP Table Definitions

 

 

This site allows you to select the B1 version and gives you a breakdown of the tables and fields.

 

 

A great resource to pin to your Bookmarks Bar in my opinion.

 

 

i hope you agree

iStock-648182720.jpg

At the recent BizONE Conference in Anaheim, I had the opportunity to catch up with Rob Burke from McCroskey Mattress Company, a long time user of SAP Business One and we got to chatting about performance tuning SAP Business One on SQL Server and what were the basic things that should be done to keep your SQL Server running at its optimum.

 

 

In prior years you would need to set up a number of manual SQL Maintenance jobs to re index and compress the databases as a starting point, not to mention running the regular backups of the data.

 

 

The good news is that now, with the Remote Support Platform you can set up those tasks based on downloaded tasks that come in from SAP as well as a number of other repair and system validation checks.

 

 

Theres also a number of other things to look at with the setup of your SQL Server deployment that I wrote about a few (actually more than a few) years back so i thought I would post them here as Rob dropped me an email following up on our conversation yesterday.

 

 

 

So here's the links for you Rob and for anyone else that may find them useful...as well as some other blogs and contributions from others in the general B1 community

 

 

Rob also was asking about the impact of increasing the number of cores on their SQL Server and whether or not this would force them to generate a new license key.

This one is open to conjecture - my understanding is that if all you are doing is allocating more cores to SQL Server (from the SQL Management Console) then it wont impact it - the disabling of the license key when there are major changes was introduced to prevent moving from one machine to another so this shouldn't really have an impact.

 

But if it does, of course, you can generate a new license key immediately as long as you have an S number and access to the rights to generate a key, which your partner should have given you.

 

Here's a short video that shows you how to do it

 

OS_990x150_Topics_1.jpg

 

Logistics with SAP Business One

Throughout 2018, our ONE.Source curriculum will explore monthly themes from across all perspectives of the SAP Business One ecosystem: Customers, Partners, SAP themselves, as well as our Community Evangelist.

 

In January, we explore the topic of Logistics. Did you know more than 60% of SAP Business One customers buy and sell products that they need to manage inventory for? We'll focus on how you can utilize existing SAP Business One functionality to optimize your logistics processes.

 

This post will serve as your curriculum overview and a working document to track all our activities around this topic. Bookmark this page or check back for updates along the way.

 

While this curriculum is primarily facilitated through a monthly webcast series, you can also expect regular blogs, videos, and (most importantly) community discussions on this topic. Visit the ONE.Source Activity feed to stay up-to-date on the latest, follow us on Linkedin or Twitter, or like us on Facebook.

 

Webcasts

  • January 11: SAP Business One Logistics Best Practices - Ask the Expert Session
  • January 23: The Amazon Factor: Are you ready for business and digital disruption? (Partner Showcase: Produmex)
  • January 25: Logistics with SAP Business One - Insights from SAP
  • January 30: Using SAP Business One with WiSys WMS - Business Process Improvements as you "Buy It, Make It and Ship It" (Partner Showcase: WiSys)
  • Bonus Extension - Two Customer Webcasts Added
    • February 14:Logistics Customer Spotlight - Business Transformation at OCuSOFT, Inc.
    • March 15: Deploying Purchasing and Inventory Control after Sales and Financials (Customer Story: Gaumard Scientific)

 

Blogs

 

Videos

 

Podcast

For February 2018, we are going to pivot our monthly them to focus on Integration (more details to come soon). But not to worry, we will merge our initial topic (General Ledger) with our planned focus on AP and AR in March to make a new Finance with SAP Business One.

OS_990x150_Topics_NEW.jpg

 

Finance with SAP Business One (focus on G/L, AP, and AR)

Throughout 2018, our ONE.Source curriculum will explore monthly themes from across all perspectives of the SAP Business One ecosystem: Customers, Partners, SAP themselves, as well as our Community Evangelist.

 

In March, we will explore the Finance with a focus on the topics of General Ledger, Accounts Payable, and Accounts Receivable. Almost every transaction ends up in the General Ledger module of an ERP solution but It’s often overlooked as a module that can help your business make better decisions and get greater visibility in to your operations. Getting paid on time and paying your accounts are also critical components of a healthy business. This month we will also look at how you can utilize functionality in SAP Business One to manage both these critical areas more efficiently.

 

This post will serve as your curriculum overview and a working document to track all our activities around this topic. Bookmark this page or check back for updates along the way.

 

While this curriculum is primarily facilitated through a monthly webcast series, you can also expect regular blogs, videos, and (most importantly) community discussions on this topic. Visit the ONE.Source Activity feed to stay up-to-date on the latest, follow us on Linkedin or Twitter, or like us on Facebook.

 

Videos

 

Webcasts

 

Blogs

OS_990x150_Topics_6.jpg

 

Project Management with SAP Business One

Throughout 2018, our ONE.Source curriculum will explore monthly themes from across all perspectives of the SAP Business One ecosystem: Customers, Partners, SAP themselves, as well as our Community Evangelist.

 

In June, we will explore the topic of Project Management. A new feature that was added to SAP Business One with Version 9.1, the Project Management module can be used in lots of different ways - not just for external billable projects, but in many other ways.

 

This post will serve as your curriculum overview and a working document to track all our activities around this topic. Bookmark this page or check back for updates along the way.

 

  • We'll kick off the month with an introductory blog from our subject matter expert and community evangelist, Richard Duffy.
    • Expect this on the first Monday of the month.
  • On the first Thursday of the month, join Richard for a live "Ask the Expert" & Best Practices webcast.
  • During the second week, we will share customer stories with real-world lessons learned and other insights.
  • In week three, we continue our monthly webcast series with insights and updates from SAP
  • SAP Business One partner expertise and solutions related to this theme wrap up the month.

 

While this curriculum is primarily facilitated through a monthly webcast series, you can also expect regular blogs, videos, and (most importantly) community discussions on this topic. Visit the ONE.Source Activity feed to stay up-to-date on the latest, follow us on Linkedin or Twitter, or join our Facebook group.

 

Webcasts

 

Discussions

 

Blogs

 

Videos

OS_990x150_Topics_7.jpg

 

Cloud Computing & e-Commerce with SAP Business One

Throughout 2018, our ONE.Source curriculum will explore monthly themes from across all perspectives of the SAP Business One ecosystem: Customers, Partners, SAP themselves, as well as our Community Evangelist.

 

In July, we will explore the topics of Cloud Computing and e-Commerce. Cloud computing has moved from being a bleeding edge technology to the mainstream and there are multiple options available for you to incorporate the cloud in to your business and with your SAP Business One deployment. In this mobile and online world, e-Commerce is also becoming increasingly for businesses to keep up.

 

This post will serve as your curriculum overview and a working document to track all our activities around this topic. Bookmark this page or check back for updates along the way.

 

  • We'll kick off the month with an introductory blog from our subject matter expert and community evangelist, Richard Duffy.
    • Expect this on the first Monday of the month.
  • On the first Thursday of the month, join Richard for a live "Ask the Expert" & Best Practices webcast.
  • During the second week, we will share customer stories with real-world lessons learned and other insights.
  • In week three, we continue our monthly webcast series with insights and updates from SAP
  • SAP Business One partner expertise and solutions related to this theme wrap up the month.

 

While this curriculum is primarily facilitated through a monthly webcast series, you can also expect regular blogs, videos, and (most importantly) community discussions on this topic. Visit the ONE.Source Activity feed to stay up-to-date on the latest, follow us on Linkedin or Twitter, or join our Facebook group.

 

Webcasts

 

Discussions

 

Blogs

 

Videos

OS_990x150_Topics_8.jpg

 

Reporting & Business Intelligence with SAP Business One

Throughout 2018, our ONE.Source curriculum will explore monthly themes from across all perspectives of the SAP Business One ecosystem: Customers, Partners, SAP themselves, as well as our Community Evangelist.

 

In August, we will explore the topic of Reporting and Business Intelligence. SAP Business One offers a set of core reporting tools for both SQL Server and SAP HANA users. Our focus this month will be on helping you make the most of these tools and introducing you to a range of new and not so new additional solutions.

 

This post will serve as your curriculum overview and a working document to track all our activities around this topic. Bookmark this page or check back for updates along the way.

 

 

While this curriculum is primarily facilitated through a monthly webcast series, you can also expect regular blogs, videos, and (most importantly) community discussions on this topic. Visit the ONE.Source Activity feed to stay up-to-date on the latest, follow us on Linkedin or Twitter, or join our Facebook group.

 

Webcasts

 

Discussions

 

Blogs

 

Videos

OS_990x150_Topics_9.jpg

 

Maintenance & EDI with SAP Business One

Throughout 2018, our ONE.Source curriculum will explore monthly themes from across all perspectives of the SAP Business One ecosystem: Customers, Partners, SAP themselves, as well as our Community Evangelist.

 

In September, we will explore the topic of Electronic Data Interchange (EDI). EDI is a hot topic among SAP Business One customers. We will discuss best practices and other considerations for optimizing EDI with your SAP Business One implementation.

 

Additionally, we will cover the topic of maintenance. Every year you pay your annual maintenance from SAP and potentially your partner adds a component for their support services. What should you be doing to maximize the value of your maintenance and ensure that you take advantage of the investments you are making?

 

This post will serve as your curriculum overview and a working document to track all our activities around this topic. Bookmark this page or check back for updates along the way.

 

  • We'll kick off the month with an introductory blog from our subject matter expert and community evangelist, Richard Duffy.
    • Expect this on the first Monday of the month.
  • On the first Thursday of the month, join Richard for a live "Ask the Expert" & Best Practices webcast.
  • During the second week, we will share customer stories with real-world lessons learned and other insights.
    • Customer Story: Western United Electric Supply (more details TBA)
    • Customers, submit a proposal here. You can get a free year of membership if you present.
  • In week three, we continue our monthly webcast series with insights and updates from SAP
  • SAP Business One partner expertise and solutions related to this theme wrap up the month.

 

While this curriculum is primarily facilitated through a monthly webcast series, you can also expect regular blogs, videos, and (most importantly) community discussions on this topic. Visit the ONE.Source Activity feed to stay up-to-date on the latest, follow us on Linkedin or Twitter, or join our Facebook group.

 

Webcasts

 

Discussions

 

Blogs

 

Videos

OS_990x150_Topics_10.jpg

 

Manufacturing with SAP Business One

Throughout 2018, our ONE.Source curriculum will explore monthly themes from across all perspectives of the SAP Business One ecosystem: Customers, Partners, SAP themselves, as well as our Community Evangelist.

 

In October, we will explore the topic of Manufacturing. The manufacturing functionality in SAP Business One has received multiple boosts since the product was released. We'll review what’s available and how it can be used effectively in your organization.

 

This post will serve as your curriculum overview and a working document to track all our activities around this topic. Bookmark this page or check back for updates along the way.

 

  • We'll kick off the month with an introductory blog from our subject matter expert and community evangelist, Richard Duffy.
    • Expect this on the first Monday of the month.
  • On the first Thursday of the month, join Richard for a live "Ask the Expert" & Best Practices webcast.
  • During the second week, we will share customer stories with real-world lessons learned and other insights.
  • In week three, we continue our monthly webcast series with insights and updates from SAP
  • SAP Business One partner expertise and solutions related to this theme wrap up the month.

 

While this curriculum is primarily facilitated through a monthly webcast series, you can also expect regular blogs, videos, and (most importantly) community discussions on this topic. Visit the ONE.Source Activity feed to stay up-to-date on the latest, follow us on Linkedin or Twitter, or join our Facebook group.

 

Webcasts

 

Discussions

 

Blogs

 

Videos

OS_990x150_Topics_11.jpg

 

Year-End with SAP Business One

Throughout 2018, our ONE.Source curriculum will explore monthly themes from across all perspectives of the SAP Business One ecosystem: Customers, Partners, SAP themselves, as well as our Community Evangelist.

 

In November, we will explore the topic of Year-End. This is a critical time for most finance departments and preparing correctly will make the process even easier. We'll share some best practices and discuss how you can make Year-end less taxing.

 

This post will serve as your curriculum overview and a working document to track all our activities around this topic. Bookmark this page or check back for updates along the way.

 

  • We'll kick off the month with an introductory blog from our subject matter expert and community evangelist, Richard Duffy.
    • Expect this on the first Monday of the month.
  • On the first Thursday of the month, join Richard for a live "Ask the Expert" & Best Practices webcast.
  • During the second week, we will share customer stories with real-world lessons learned and other insights.
  • In week three, we continue our monthly webcast series with insights and updates from SAP
  • SAP Business One partner expertise and solutions related to this theme wrap up the month.

 

While this curriculum is primarily facilitated through a monthly webcast series, you can also expect regular blogs, videos, and (most importantly) community discussions on this topic. Visit the ONE.Source Activity feed to stay up-to-date on the latest, follow us on Linkedin or Twitter, or join our Facebook group.

 

Webcasts

 

Blogs

 

Videos

 

Discussions