The best tool you can possess for your company (or as a consultant) is having a basic working knowledge of SQL within the Query Generator or Query Wizard in SAP B1. Some SQL provides just “data” (for example, lists of Customers or AR Invoices within a certain date range), while others actually border on falling in line with Business Intelligence by providing “information”; data converted into actionable reports with decisions to be made (for example, Average Lapse Days used by Finance to determine the Credit Worthiness of Customers, or a Mark Up Ratio Report to determine the direction of the Enterprise's pricing strategy). With companies I have worked with in implementing their SAP B1, SQL is one tool I have used constantly to cover those data or reporting “holes” which sometimes appear in SAP B1. And many times there is unique information the company might need. Since SAP B1 is used by so many different lines of business, the possibility of writing SQL to provide data required for daily processing or information for business decisions is endless.
BACKGROUND: Once I started getting into the SAP B1 Forums, I found it rather amazing how often the same query is/was requested over and over again. Many people had the same need for information which is not in the standard SAP B1 reports. But other Forum Members were looking for more specific SQL; might have been the requester needed to see more than what SAP B1 has or maybe they needed to see it in a different way. But SQL still seems to be a hot topic. I believe the most requested item on the SAP B1 forums is probably directly related to SQL and queries; needing a new query or maybe how to correct the SQL someone had attempted to write themselves.
THE IDEA IS KICKED AROUND: So, an idea was brewed, discussed, debated, and semi-formulated about these types of Forums postings and how SQL contributions could be made to the SAP B1 Community which would be lasting. Most Forum postings just get “lost” by aging (and, ahem, the search command does not work that well either in SAP). Plus there is no real organization as to how Forum replies are posted or stored. Slowly but surely, SQL jewels slipped into oblivion and special tips/tricks just passed away. It seemed a shame that so much energy was not being utilized for everyone's benefit who use SAP B1, and not just for the one person who requested some special SQL or assistance in tidying up what they had already written.
THANKS TO DARIUS AND OTTO: It was finally decided something should be done. With some help, guidance, and urging onwards by Darius Heydarian and Otto Gold, some SAP B1 Forum “SQL Experts” decided to band together and provide to you and/or your company some very common and some pretty amazing queries they have written or corrected over the years inside the SAP B1 Forums. [I put “experts” in quotes, because some of them (such as myself who is self-taught) might not actually be a real certified SQL Expert, but they have been helping others enough in the Forums to stand out as being pretty good at providing assistance in the area of SAP B1 SQL.]
SOME FOUNDERS AND NEW IDEAS APPEAR: The beginning had to start somewhere and it only seemed to make sense to poll these individuals about creating an area to help others. The founders banding together are Joseph Anthony, Neetu Dhami, Gordon Du, Mike Heard, Istvan Koros, and Janos Nagy who will try to post some SQL on a regular basis. It is going to be quality over quantity for this area. If you have been in the SAP B1 Forums, most of these names are pretty familiar. Another idea which came to the forefront rather quickly was to provide a way for beginners to get some on-line training or online resources. The need for a tips and tricks area was also recognized as being needed. More guidance on what books or SAP Manuals was added somewhere along the way. Rules and standards were developed to ensure there is consistency and proper explanation of what is being done. Ideas were thrown out and accepted or rejected – it was a tumultuous affair until finally “the stake was placed in the ground”. Everything was finally formulated into a “take-off” point which Darius placed on the SAP Business One Wiki page called “SQL, Tips, and Tricks”. The final step to getting the word out is a Forum “sticky note” being placed in several SAP B1 Forums. The cat is out of the bag now, and we will see how this moves forward.
HIGHLIGHTS OF THE WIKI SECTION:
Standards: There are specific standards required to post to the Wiki. One being a specific format and an explanation of what the SQL is doing. Several of the SQL have suggestions about how certain SQL commands are used. Included with most of the SQL posted is an image shot of what the final SQL view is.
Usability: Probably the best idea introduced is that the SQL must be “universal” which can be used by anyone just by “cutting and pasting” the SQL into their own system. Not only would SQL commands and instruction be given, but the SQL could be immediately used. It also falls in line with the direction SAP is taking with Version 8.8 and beyond. Now this is a benefit which can directly help the SAP B1 Community!
Organization: The SQL is organized according to the Main Modules of SAP B1 where a person would expect the data would reside. The idea is to help a person navigate quickly to the SQL required by placing the SQL within the area where it seems to best be used. A separate area is given to Formatted Searches and Miscellaneous SQL. Of course, some SQL could be placed in two Modules, but the placement is a subjective decision and stands as it is.
Tips and Tricks: Short notes on things you might need to be aware of, along with some examples of the tip or trick. I cannot count the number of times I have seen the Forum question of “why did my drill-down arrows disappear and how do I get them to display again?”. There is a note specifically on that along with others such as the limitation of Alert Messages and obtaining a list of all SAP B1 Tables.
The Five Step Rule: You should be able to get back to your original starting point in five steps or less, so time spent searching is reduced. There should be no copied links to the Forum or any other place to get the SQL – the posting is complete within itself and no need to go stepping further to the information you need. We want to avoid the common habit of link-posting which seems to irritate Mentors, Moderators, and Forum Users alike.
The Contributors: Without a doubt, the biggest highlight is the group of individuals who are taking the time to contribute to the SQL being posted, by going beyond just posting SQL in the Forums. They have taken the time to explain the SQL, given examples with images, and are organizing the SQL in a way so that you can quickly get to the SQL you need.
AND ONWARDS WE GO: The Wiki has gone beyond the conceptual stage and is slowly building up with executable SQL, Tips, and Tricks. If you would like to contribute to the Wiki, please feel free to do so by staying within the framework and by ensuring it is quality over quantity; several individuals will review each and every entry to make sure both entries and the Wiki area remain viable and accurate. This Wiki addition to the SAP B1 area will be a great “give and take” area where everyone wins by getting new SQL and ideas, and by leaving SQL and their own ideas. Hop on over and take a walk-around with what is out there;
http://wiki.sdn.sap.com/wiki/display/B1/SAP+B1+SQL+Tips+and+Tricks
.
Comments