Introduction: The reporting tools available for SAP Business One

Reporting is a fundamental element of any ERP system. Regarding SAP Business One, there are a number of different reporting alternatives available, such as:

• The standard built-in reports inside the SAP Business One client
• The SQL query tools inside the SAP Business One client
• XL Reporter
• A large number of 3rd party solutions provided by SAP partners

Lately, there's been a lot of discussion about SAP buying Business Objects and the potential that Crystal Reports would bring to SAP Business One customers. With all these tools, one might wonder if they all are really needed and/or if it makes any sense to keep adding new solutions to the list.

Built-in Standard reports

Surely, it makes sense to have certain essential reports (such as the profit-and-loss statement and balance reports) built in to the system so that they can be used even in an out-of-the-box installation. The nice thing about these reports is that most of them support such selection criteria as cost center, project and item/business partner properties. Also, the layouts of these reports can be modified, at least to a certain extent.

The trouble with the built-in standard reports is that they can not really be extended. Quite often you are faced with a situation where the standard report provides 90% of the things you need, but there's no way to get the missing 10% included on the report. As a workaround, you can of course export the standard report to Excel and then use your favorite hack to add the missing parts there. If you don't like workarounds and manually executed tricks, you need to roll your own reports, though.

User queries

User queries are great. Not only do they allow you to create your own parameterized reports, but they also allow you to access many of the features provided by the SQL Server database engine (such as dataabase views, user functions, temporary tables, T-SQL queries, queries to non-B1 databases and tables etc.). You even have the nice orange drill-in arrows that let you open masterdata objects or documents directly from the query results with a single click. It's also great to be able to export the results to Excel for further processing or build a layout for your query with the Print Layout Designer or Advanced Layout Designer.

Then again, there are also some problems with user queries. The menus through which you need to navigate to the reports are not very user-friendly to start with (they could've at least added a possibility to assign a user report directly to a function key). Over time, as the number of queries in the system grows, you will notice that it becomes harder and harder to find the query you're looking for. The fact that sometimes you need to define multiple queries that do almost the same thing (instead of being able to make one flexible query that the user could control via parameters) only makes this phenomenon worse.

There are also some technical issues. Sometimes certain queries might stop working after a version update because they're rendered differently. Also - especially when supplying datetime values as query parameters - you might find out that the results of a query either contain records that shouldn't be there or vice versa. Last but not least - you will need a SAP B1 client to use the reports. If a user only needs the reports, B1 is a pretty expensive report viewer.

XL Reporter

Well, then there's the beloved XL Reporter (XLR), invented by those wild Norsemen at iLytix. XLR looks really good - both on paper and in PowerPoint. The pre-defined report set even looks pretty good in a working demo system (if you manage to get it up and running, that is). Because of its good looks, XLR is an ultimate promiseware application. If you're a slick salesman and your bonus is only dependent on the closing of the contract, you might get away with promising "sure, we can do that with XLR". However, even for the demo, it's best to stick to PowerPoint slides.

Especially the earlier versions of XL Reporter had severe problems related to the installation and to the stability of the whole application (certainly, this is at least partly due to UI API). It took a lot of effort to get it installed and then to keep it up and running. This is not exactly what you would expect from a world-class reporting solution. However, these were just minor nuisances compared with the fact that the whole concept of XL Reporter is fundamentally flawed. It is a schoolbook example on how not to build an effective reporting solution (see the section on performance below).

XLR looks deceivingly simple and powerful. Because it is based on MS Excel, it has instant appeal for people with accounting/financial/managerial background (if you're educated to use a hammer, everything starts to look like a nail). In reality, XLR is neither simple nor powerful. Whatever your reporting needs are, there are better alternatives available. You have been warned.

Crystal Reports

While it is rather obvious that SAP didn't buy Business Objects in order to bundle Crystal Reports with B1, it seems that something is really starting to happen also on this front. Crystal Reports is a pretty decent reporting tool/layout editor that many 3rd party add-ons are already using as their reporting component. Personally, I prefer Perpetuumsoft's Report SharpShooter (especially because their solution allows the end-users to modify report layouts without purchasing a license, unlike Crystal Reports) for doing layouts, but that's a matter of taste. I see the main potential of Crystal Reports in the SAP B1 context as a replacement for the horrible Print Layout Designer. Surely, it can also be used to produce really nice reports. If the alternative was XLR, I would choose Crystal Reports without a second thought.

What about performance ?

The most important real-life lesson I've ever learned about reporting is that the number-crunching must be done as close to the data as possible. In order to produce a meaningful report, you normally need to join records from several tables. The end result might be just 50 records, but the records involved in the retrieval process with complex joins in a production database can easily add up to hundreds of thousands or millions of records. It's not so hard to imagine what the bottlenecks might be if you loaded all that data on the client. Together with badly designed queries, you will not be saved from awful performance no matter how much you invest in hardware. Obviously, you would wish to do the joining and summarizing on the server.

Among the tools discussed above, XL Reporter is clearly the worst and most obvious example of poor-performance-by-design. However, actually all the tools mentioned above are based on a traditional client-server (fat client) model. In each of these reporting solutions, at least the report rendering, but also a lot of the actual data processing is done by the client. This really doesn't matter if you're just playing around with a demo database that has a few thousand records in it. Once you're in a real live database that's been accumulating data for a while, it really does make a huge difference.

What's wrong with Excel as a reporting tool ?

Above, I was referring to Excel above in a bit negative sense, This doesn't mean that there's anything wrong with the product per se. Quite the opposite - for many purposes it is a fantastic tool. However, it is perhaps the most abused piece of software. As with any tool, MS Excel is no silver-bullet or panacea that could be successfully applied to any problem. I see Excel as "very smart paper". When it comes to calculations, it's certainly light years ahead from traditional paper. With a bit of knowledge of the formula language, you can perform small wonders. However, there are some tasks I would never do with Excel. One of them is word processing and the other is database-like transaction processing. Lately, I participated a one-day "advanced" Excel course that was directed to Financial Managers. As a "real-life" example, the trainer showed us how to build a simple invoicing system based on Excel, with invoice layouts and everything ;-) I was not impressed.

Excel is great in the final stages of the reporting-cycle, especially for distributing pre-prepared reports and doing simple one-time analysis tasks on the prepared data. It is also a worthwhile user interface for SQL Server Analysis Services. However, it doesn't score quite so well in rendering reports from raw data (as done in XL Reporter). If you try to use Excel for collecting the data used for serious reporting, you're even worse off. If you're serious about being a professional (regardless of being positioned either in the IT or business domain), for these scenarios you simply need a database. Period.

Summary

There are already many reporting tools available for SAP Business One and some of them are rather good. However, even with all these tools, there still is an obvious social demand for a tool that would have the following properties:

• It must be easy to develop new reports and modify existing reports
• The tool must not be limited by the object model of DI API (this is a major handicap of PLD and ALD).
• The tool must be robust and reliable
• The tool should be compatible with the technical infrastructure of SAP B1 (Windows operating system, MS SQL Server etc.)
• The license should be relatively inexpensive
• It should be possible to view the reports without the B1 client, preferably via a web browser.
• It should be possible to easily export the reports into Excel.
• The installation and configuration must be simple (something that can be done in a day).
• We should be able to trust that the tool will be supported also in the foreseeable future.
• Last but not least, the system should not have architectural issues that spoil the performance.

These are the elements that I would require from an excellent reporting solution. This certainly isn't an easy wishlist to fulfil. XLR surely wasn't the answer, and I'm afraid that neither is Crystal Reports. Doesn't it make you wonder if there even is such a product that would fulfil all of these requirements...?

Enter the SQL Server 2005 Business Intelligence stack

Along with MS SQL Server 2005, Microsoft published the Business Intelligence (BI) stack, which consists of three main components: Reporting Services (SSRS), Integration Services (SSIS) and Analysis Services (SSAS). SSRS and SSAS were already released as free components in SQL Server 2000. However, they were totally revamped in MSSQL 2005. SSIS is also a complete rewrite of an ancient ETL tool that was known as the Data Transformation Service (DTS) in MSSQL 2000. Only this time it's much more flexible and powerful.

It seems that the norm with Microsoft products is that the first release is a bit clumsy and limited, but then they quickly improve from version to version. The MSSQL 2005 BI stack is already a very impressive set of tools. Considering that SQL Server remains the de facto database platform for SAP Business One, it's truly amazing how silent the SAP Business One community has been about SSRS. I initially got interested in SSRS when a couple of my most enlightened clients first evaluated XLR (unsurprisingly, they were totally disappointed with it) and then soon opted for SSRS instead. After getting to know SSRS, I started to realize what a hidden gem it is.

While the other elements of the BI Stack are equally interesting, in this blog I will concentrate on SSRS.

The benefits of SSRS in a B1 context

Flexible delivery of reports

SSRS integrates with Internet Information Services (IIS) and thus the main user interface for SSRS is the web browser. In addition to viewing the reports with a web  browser, you can also export them to a multitude of formats: MS Excel (SSRS renders drill-in reports beautifully into pivot tables in Excel), PDF, CSV, XML etc. Additionally, you may opt to have the reports sent to selected persons or stored on a shared folder - all of this can of course be scheduled and automated.

Great performance

All the SSRS reports are rendered on the server. Compared with any of the B1 reporting tools mentioned above, the rendering process of SSRS is lightning-fast. If you have lots of users or are running very heavy reports, you can also use the excellent caching and scheduling features of SSRS to make it even faster.

Independent of B1

For some people, it might seem a handicap that the reports are not loaded from inside SAP B1. Personally, I see this as a virtue. The concept of embedded add-ons sounds good, but in practice the UI API add-ons have proven unreliable and clumsy. Even though the deployment procedure of add-ons to clients is sort of semi-automated, there is nevertheless a deployment needed. In proportion to the security level of your IT infrastructure, this might be a somewhat small problem or a major headache. Also, there are lots of users who don't really need the B1 client but just need to access the reports. It does not make much sense to buy an expensive B1 license for such users just to see the reports. Perhaps the only thing I'm missing in SSRS from inside the B1 client are the orange drill-in arrows.

Appealing and easy-to-navigate user interface

SSRS allows you to organize your reports in hierarchical folders. You can give a meaningful name each folder and report. Additionally you can specify a descriptive text for each folder and report. SSRS also contains a search funtion that helps you find the report you are looking for (it searches both the names and descriptions of the reports).

Integrated authentication and flexible user rights management

As SSRS runs on IIS, it can be configured so that the user is automatically authenticated with her Windows credentials. The user rights can be set separately for each folder and report. Each user will only see those folders and reports that she is allowed to access.

Inexpensive

If you have a license for Standard or Enterprise Edition of SQL Server 2005, you are automatically entitled to use all the components of the BI Stack. Unfortunately, if you bought the SQL license from SAP along with your B1 licenses, that does not entitle you to use SSRS. In that case, you would have to buy additional licenses from Microsoft. Even then, the price of the SQL Server license is pretty good value for money compared with many reporting solutions that offer similar features.

Simple and powerful development environment

The SSRS reports are developed in BI Development Studio, which is actually a stripped down version of Visual Studio 2005. Visual Studio is a familiar environment to most people who have done any development with the B1 SDK. It is also immediately recognizable to anyone familiar with MS Access. My first impression of the BI Development Studio was "they've taken MS Access and put it in a web browser". The development process of a report is very straightforward and intuitive:

1. Define a dataset, which basically means one or several SQL queries (to be precise, the queries could also be multidimensional MDX queries, but that's another story).
2. Define a layout, which can either be a table/matrix (in SSRS 2008, they actually have a hybrid called tablix ;-) and/or one or several charts. While developing the report, you can preview it whenever you like.
3. Once you're satisfied with the report, you simply deploy it to the server with a single click. Then, you can access the server via a browser and either view reports or modify their properties such as user rights assignments. Redeployment of a modified report is just as easy - just two mouseclicks away.

Interactivity

As the main delivery channel of SSRS is the IIS, there's a hidden bonus: you can specify dynamic URLs to any cell in the report. This means that you could for instance take the docentry of any single record in your resultset and generate an URL that is pointing to a completely different webpage (such as a HTML preview page of an invoice or order).

In fact, my plan is to combine certain SSRS reports with an ASP.NET application that uses DI Server for modifying the data inside SAP B1. As a large part of what users actually do in SAP B1 is browsing and searching through documents and masterdata, SSRS would basically provide us with half of the functionality needed in a completely web-enabled SAP B1 solution. The other half - adding and updating data - would be handled via DI Server.

Summary

In my opinion - based on the findings above - SSRS is the ideal reporting platform for SAP Business One (or any other ERP running on MSSQL). However, I don't want you to take my word for it. I want you to try it out yourselves.

A sample solution with SSRS: Sales matrix by customer group/customer and item group/item

In this sample scenario, we build a simple matrix report that shows the turnover for a given time period by item group and by customer group. Additionally, we are going to use the drill-in feature to allow us to view the values also on item and customer level.

Step1: Start a new Report Server Project

In Business Intelligence Development Studio, start a new project. Choose the type "Report Server project" as shown below.

Step 2: Define data source

After initializing the new project, add a new shared data source by right-clicking the "Shared Data Sources" folder as shown in the image below.

 

In the Shared Data Source form, click the Edit button and specify the connection properties for your database environment. Then click "Test Connection" to see that the connection works.

 Step 3: Start creating a new report

In the Solution explorer, right click "Reports" and choose the function "Add new report".

 

Step 4: Select data source

Choose the data source you wish to use in your report. If this is your first report, you will only see the datasource you just created in step 2.

 

Step 5: Define the query

There is a nice visual query designer in BI Development Studio, but you can also simply type in your SQL Query (Often a good way to start working with the Query Builder is to copy your favorite user queries from SAP B1 and see how they can be enhanced in SSRS).

Here's the complete query

SELECT 
OCRD.CardCode, 
OCRD.CardName, 
SUM(INV1.Price * INV1.Quantity) AS turnover, 
OITB.ItmsGrpNam, 
OCRG.GroupName, 
OITM.ItemCode, OITM.ItemName

FROM  INV1 
INNER JOIN OITM ON INV1.ItemCode = OITM.ItemCode 
INNER JOIN OITB ON OITB.ItmsGrpCod = OITM.ItmsGrpCod 
INNER JOIN OINV ON OINV.DocEntry = INV1.DocEntry 
INNER JOIN OCRD ON OINV.CardCode = OCRD.CardCode 
INNER JOIN OCRG ON OCRD.GroupCode = OCRG.GroupCode

GROUP BY 
OCRD.CardCode, 
OCRD.CardName, 
OITB.ItmsGrpNam, 
OCRG.GroupName, 
OITM.ItemCode, 
OITM.ItemName

Step 6: Select the report type

There are two report types in SSRS 2005: tabular and matrix. The tabular report is the more 'traditional' report type, which nevertheless provides such nice features as drilldown. The matrix report type, on the other hand, provides a way to have drill-in functionality both on row and header level. In this specific case, we will select the matrix report type.

 

Step 7: Specify the contents of the matrix

Here, you may choose which database columns will be assigned to row/column level and which database columns will provide the actual detail data. PLEASE NOTE: do not forget to check the "Enable drilldown" checkbox in the bottom left corner.

Step 8: Choose style

In this step, we can choose from a selection of pre-specified report styles.

 

Step 9: Complete the wizard

 

Step 10: First preview

As we can see, the initial version of the report is already pretty good looking. There are some issues such as the decimal count that call for further adjustment.

Step 11: Adjust layout formatting

One of the nice features of the layout designer is that you can use standard .NET type formatting strings for any field. For instance, to drop the non-wanted decimals (only leaving two), I can use the formatting string #0.00 (see the picture below).

Step 12: Second preview

Now that we have adjusted the formatting of the detail cell and also changed column widths a bit, the report is already looking very good.

Step 13: Add parameters to the query

In this report, we will use two datetime parameters: StartDate and EndDate. You can add new parameters while in the Data or Layout tab is active (not when you're in the preview tab, however) by choosing Report-Add Parameter from the top menu. See how the parameters are referred to in the query.

Here's the modified query:

SELECT 
OCRD.CardCode, 
OCRD.CardName, 
SUM(INV1.Price * INV1.Quantity) AS turnover, 
OITB.ItmsGrpNam, 
OCRG.GroupName, 
OITM.ItemCode, 
OITM.ItemName
FROM         
INV1 
INNER JOIN OITM ON INV1.ItemCode = OITM.ItemCode 
INNER JOIN OITB ON OITB.ItmsGrpCod = OITM.ItmsGrpCod 
INNER JOIN OINV ON OINV.DocEntry = INV1.DocEntry 
INNER JOIN OCRD ON OINV.CardCode = OCRD.CardCode 
INNER JOIN OCRG ON OCRD.GroupCode = OCRG.GroupCode 
WHERE (OINV.DocDate BETWEEN @StartDate AND @EndDate)
GROUP BY 
OCRD.CardCode, 
OCRD.CardName, 
OITB.ItmsGrpNam, 
OCRG.GroupName, 
OITM.ItemCode, 
OITM.ItemName 

In fact, you can specify parameters simply by writing them into the query. However, by default these parameters will be of type String and this is not always what you want. The parameter configuration screen not only lets you decide the type of the parametes, but also assign available values, default values etc.

Step 14: Third preview

Now we are happy with the previewed results. It's time to deploy the report to production use.

Step 15: Adjust the deployment settings

For each new project (however, not for each report inside a project), you need to specify the TargetServerURL and TargetReportFolder parameters before doing the deployment.

 

Step 16: Deploy

Right click the name of the project (or individual report, if you only wish to deploy that specific report) and choose Deploy.

 

Step 17: See the report in SSRS

Here's a standard screen that you should expect to see once SSRS is properly installed and configured. Notice the search field which allows you to look for reports by name and description

Step 18: The report seen through a web browser

Here's a glimpse of the kind of data you should see on the report after deployment.

 

Step 19: Export to Excel

Simply choose Excel from the target listbox and then click "Export" to initiate the export.

 

Step 20: The exported results in Excel

As you can see, the results look almost the same in MS Excel as in the web browser.

In this scenario, we have barely scratched the surface of SSRS. We could have decorated the report with subtotals, additional charts etc. There are so many great features in SSRS that it is impossible to demonstrate in a single blog. Anyway, hopefully you've gotten a somewhat clear picture of the things you can accomplish with SSRS.  

Conclusions

The B1 community has been almost dead silent about SQL Server Reporting Services. This is a pity, because in addition to being instantly available to the majority of B1 customers, it is also one of the best reporting solutions relevant for this market. It's no wonder that Microsoft has defined MSSQL and SSRS as the key integration point between their various Dynamics ERP products. If you are looking for a reporting solution, you should definitely take a look as SSRS.