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.

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:

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:

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.

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.

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.

Between 2009 and 2013 I had the opportunity to build a lot of videos designed to introduce the various functional capabilities of SAP Business One - I started when Version 8.8 was the current release and continued through until Version 9.x.

In that time, I built quite a lot of content covering the majority of functions in SAP Business One and over time, I got a lot of feedback that the videos were helpful not only for learning about B1 when deciding if the solution was the right one but partners and SAP Business One users all around the world used the videos to help them learn SAP Business One and deploy the solution.

So that's why in a lot of these videos, I refer to myself as a member of the SAP Business One Global team...

SAP Business One Video Content Central

So now that I am here helping out the ASUG team with the SAP Business One Community, we thought it would be a great idea to bring all these videos in and centralise them into a single access point - that's the purpose of this page - over time all of the various videos I have built will be indexed and catalogued here for you so that you can use them as a resource to find the answers you need and the guidance on using and deploying the solution.

A quick point to note, even though some of these videos go back as far as Version 8.8 they are all still relevant - in fact in many instances the functionality is exactly the same, only the look and feel of the Business One menus and cockpits have changed as well as some background plumbing that of course made the solution more robust and faster.

So there's a very strong chance that even though the "skin" of B1 may look different these videos will still apply - so we hope you enjoy having access to these and of course, give me your feedback if theres a topic that is missing - if its not on the drawing board to produce already i'll see what I can do to get it there for you!

Often times you need to track different activities in SAP Business One, manage KPI's or generate ad-hoc data sets for further analysis. The SAP Business One Query Tools are great to do this but sometimes it can take a while to master the process of building queries and understanding the different concepts of table joins asnd also the data structures in SAP Business One.

This is where the assistance of your peers can be invaluable to help you understand how something is achieved. If you are like me, when you are trying to understand how a technology works it's helpful to have examples to refer to and its even better when those examples can be leveraged in day to day operations of the solution.

Fortunately I found a recent scenario on the Facebook group I moderate ehre a member of the group wanted to find out which suppliers were late with their deliveries and two other members stepped up and provided the necessary queries to assist and they agreed to share them with you here.

One of the core challenges that many organisations face is managing their SAP Business One implementation in a cost effective and time efficient manner.

SAP are continuing to focus on this area and I will talk more about that in my upcoming webinar on the SAP Business One Roadmap and you'll see this as we drill down in to the new 9.3 release.

I wrote a couple of LinkedIn posts on the SAP Business One Cloud Control Center and I got the feedback that people would like some detailed training on how to deploy the technologies behind that and so I have gone down that path and built the sessions (and continue to do so).

So here are the sessions that I have built and I would encourage you to come along to my Moving to the Cloud session at the upcoming Biz.ONE Conference to learn more about how you can continue to leverage these technologies.

For many small businesses, the deployment of an ERP system is done in response to a specific set of challenges that exist in the business at a given point in time.

With the problem solved, they then move on to other pressing issues but in my experience there is a big opportunity that is going begging and that is around the concept of what the Japanese call Kaizen - constant improvement.

A great approach is to stop and review what has been achieved as the result of deploying SAP Business One and what are the other ways that you can utilise the solution to help drive efficiency in your business.

Often times, it's an area that you may not want go to as there's a concern that there will be additional money that needs to be spent on licenses and consulting services and of course, thats an option and you'll do a cost-benefit analysis to determine if it makes sense but there are many ways that you can potentially utilise your existing investments to greater effect.

A big area is the under-utilisation of the CRM and sales force automation capabilities of the solution - quite a few organisations have gone out to market looking for a best of breed CRM solution to plug in to their SAP Business One solution because they may not have really mapped out what they are looking to achieve and end up focusing on the tools that are available and what they claim they can do.

Many times, particularly with organisations that deploy Salesforce as an example, they end up only using 25% of the available functionality - and apart from some of the benefits of the web and mobile UI of Salesforce, the functionality that gets deployed is already existent inside SAP Business One.

This coming November, we are going to have a focus month where we'll deliver a few sessions and a focused webinar designed to specifically help you build your strategy and systems alignment plans for 2018 so mark your diary to attend the sessions and if you're heading to the upcoming Biz.ONE conference in Anaheim, make sure you plan to attend my 2 sessions on moving to the cloud and reporting as both of these sessions will align to this concept of mapping your systems, strategy, skills and staff to drive better business outcomes.

I am a big fan of the McKinsey 7S Model for business planning and alignment of strategy and tactics - we used it at Great Plains Software in the 90's and the approach helped to contribute to the exponential growth of the company and the team's ability to adapt to and drive that growth - so I would encourage you to take a look at this document that talks in more detail about the model - I'll be referring to this quite a lot as we discuss this process and its a good primer to focus your thoughts and start the 2018 planning process.

As previously announced, one of the benefits of your ASUG membership is access to all the new content that we are building including our series of 15 Minute Fundamentals sessions, offering basic training sessions for SAP Business One users.

These are primarily focused on new users, but should also be helpful to experienced users if you need a quick reminder of how the core functionality in the solution works.

Bookmark this page, because as the sessions are published they will be added on to this page so you have a single place to go to get access to all the sessions grouped according to the functional area.

If you have any questions or would like to see a session done on a particular topic in more detail, remember to ask your questions in the Q&A forum of our community site.

Often times, it seems like people in the SAP world use a completely different language, full of esoteric and important sounding acronyms like ABAP, iDocs, FICO, PLM and others.

In fact when I was at SAP I often heard it referred to as SAPanese.

But when someone uses it on you, it's no laughing matter as its usually as part of an explanation on how to solve a problem and your stress levels are already high enough with people adding to it.

So as time progresses I will try to explain some of these that you may encounter as an SAP Business One user to help clear the confusion and give you something to talk about at parties.

So, many of the systems and portals at SAP are locked down so that only Authorised users can access them.

The codes that are provided can be a number of different ones - for example if you are an SAP employee in Germany, you are given a D-number, International employees get I-numbers, contractors get C-numbers and customers and partners get S-numbers.

If you need to access the SAP Help Portal and many of the Customer only documents, you'll need an S number and the place to go to get that is your partner - as an SAP customer you are entitled to access these resources (don't let anyone tell you differently) as long as you are current on your SAP annual maintenance.

Once you have an S-number you can access the areas that SAP have determined are appropriate for you as an SAP Business One customer including the software downloads area.

To make it easier you can also apply for an SAP Passport which is a digital certificate that you can install in your browser and use instead of remebering your S-number and password.

I'll cover that process in another blog shortly - oh and by the way I was only joking about talking SAPanese at parties - unless its an ASUG party, if you do it you'll most probably not get invited back

SAP's recommendation is that you should participate in the Early Adopter Care program if you plan to implement SAP Business One 9.3 or SAP Business One 9.3, version for SAP HANA in a productive environment.

As has been the case with previous Early Adopter releases you will benefit from accelerated problem resolution and special attention from SAP.

Leveraging the SAP HANA platform and building on the semantic layer, SAP plan to offer new cockpit templates. With a new analytics portal, end users will be able to schedule and consume reports without the need to log into SAP Business One.

With add-on solutions playing a key role in shaping the SAP Business One solution, SAP have also announced plans to make it easier for partners (and customers) to develop add-ons with the support of XML methods for user defined tables and the latest visual studio 2015 suite.

Deployment and management of SAP Business One will be simplified with a centralized approach. SSO will be utilized for apps for SAP Business One, version for SAP HANA making partner and self developed add-ons easier to manage and consume.

SAP have also re-committed to continuing to focus on building for optimal performance, making SAP Business One 9.3 more stable and easier to support.

You can learn more in the ASUG upcoming webinars focusing on the SAP Business One Product Roadmap and the 9.3 In Focus sessions due shortly as well as reviewing the 15 minute fundamentals sessions built using SAP Business One 9.3

Integration tools are a critical component of most ERP solutions - in fact, as the functionality to automate the business processes inside your organisation become commonplace in most solutions there are really only a few remaining areas where products can differentiate themselves.

Some integration tools are designed to be used by your existing end users and in SAP Business One these are tools like the Query Designer, Formatted search and the Data Transfer Workbench or DTW.

Other tools require a greater level of technical skill and capability such as the DI-API (Data Interface Application Programming Interface) which enables you to write your own software with tools like Microsoft Visual Studio and languages such as Java but these are definitely tools for the experienced programmer.

Other tools exist such as the SAP Business One Integration Framework which is designed to enable consultants and experience technical users to build workflow based integrations between SAP Business One and a number of other solutions - these are commonly referred to in integration scenarios as "endpoints".

But how do you learn these tools without spending large sums of money to gain the pre-requisite knowledge to make them work their magic?

Well, for some time SAP have been publishing courses on their HANA technology on their Open Learning site at open.sap.com - now the very first SAP Business One related course has been published and its focused on the SAP Business One Integration Framework and its a free course available to anyone that wants to sign up.

Of course you'll need some pre-requisite skills and for this course they are :

General understanding of IT

Basic knowledge of SQL

Knowledge of XML/XSLT

And of course, SAP Business One knowledge is useful

The course runs over a 5 week period and requires you to attend 4 hours of course content each week so its not overwhelmingly time consuming and you'll need access to an SAP Business One deployment with the B1if components installed.

Take a look at the course, learn the Integration Framework and then go out and apply your new skills to build an even better SAP Business deployment for your organisation - its free and the functionality is robust and that makes it a great tool to use.

At the upcoming BizONE Conference in Anaheim, I will be presenting a workshop entitled Taking your SAP Business One deployment to the Cloud - Why the discussion should not be if but when and how

We'll be covering a number of SAP Business One specific topics relating to utilising the cloud as part of your deployment as well as looking at various technology options but this session isn't a sales pitch.

It will be your opportunity to understand the different factors that will impact on your business and whether or not cloud computing will be a part of your IT mix.

As a hands on session, you should also come along prepared to do some work - I'll be leading you through a couple of exercises (mental not physical) that will help you build your business strategy and determined when you should move to the cloud, what components you'll put in the cloud and what cloud models are best for you.

To get you started - here's a nice little explainer video from the team at Common Craft on cloud computing that will help you get the basics straight in preparation to dive deeper at the Conference as well as a preview of what we'll cover at the Conference

The onboarding process in many organisations is a well structured process where you are led through all the things you need to know in order get productive in your new role as quickly as possible.

However when it comes to learning the business applications that are in place, often times it can be a challenge to get to know the basics and find someone with the time to take you through the fundamentals of using those systems.

As a member of ASUG, you are in luck though if you are using SAP Business One as we will be delivering a complete set of sessions delivered in 15 minute blocks covering the fundamentals of SAP Business One from system navigation to journal entries - from sales order processing to reporting with the Print Layout Designer and Crystal Reports Basics.

These sessions will be called (funnily enough) 15 Minute Fundamentals and we'll be delivering these over an extended period of time starting this Friday and every Friday until we have delivered a complete set.

Of course, remember that you can also ask any questions about using SAP Business One in the Q and A Forum and I'll endeavour to answer them as quickly as possible.