Blog >
Whether you’re new to SAP Business One or an experienced user, it’s important to continue deepening your knowledge of the solution’s reporting capabilities; the more you learn, the more insight you stand to gain. Over time, users will create many queries in SAP Business One to obtain the information they need from their database. This collection of queries can then be further leveraged as the basis of an SAP Crystal reports or for Cross Tab reports as well. In this week’s article we’ll look at Cross Tab reporting: what are they, what they can do, and how to insert them into your SAP Business One reports using an existing query.
Cross Tabs format data into a matrix within SAP Crystal reports that enable users to specify information from existing queries and display them totaled in columns and rows. This summarization makes the data easier to understand and allows the user to spot trends quickly.
Looking at an existing query, enter ‘Edit Mode’ using the pencil icon, then highlight and copy your query. Next, go to SAP Crystal Reports and start with a blank report. Upon connecting to SAP Crystal Reports, in the Database Expert, click on the ‘Add Command to Report’ button to enter a query you have previously created then click ‘ok'.
In the field explorer menu, you’ll see the database commands. From here you can drag and drop fields like ‘CardName’, ‘DocDate’, ‘DocEntry’, ‘ItemCode’, ‘Description’, ‘Quantity’, ‘LineTotal’, or whatever you’d like to see, over to the design window for your report.
From the top of the screen, click the ‘View’ menu option and then click ‘Print Preview’ to see what the report would look like. In a Vision33 TOTAL Care Wednesday Webchat video, SAP Business One ambassador Carl Lewis, illustrates an example where a user might want to insert Cross Tabs. For instance, upon viewing a preview of their report, a user may want to summarize the data by item code to see how many units sold from revenue. Cross Tab reporting will enable the user to do just that.
Select ‘Insert’, then ‘Cross Tab’ from the main menu. A highlighted rectangle is now attached to your cursor. You can then drop it into the header section of the report design screen. Next, right-click inside and select the ‘Cross Tab Expert’. From here the user can select the information that will appear in columns and rows of the Cross Tab.
There’s a ‘Group Options’ button that reveals formatting for the fields. So, for instance, if you put ‘DocDate’ in the column of the Cross Tab, you can select how the date will be presented. In this example, Lewis selected “for each year”. You could add something like the ‘Command. Description’ in the rows and lastly for the ‘Summarized Fields’ you could add ‘Sum of Commandline.Total’ and ‘Sum of Command.Quantity.’
Once you click ‘ok’, you’ll then see that the report now contains a summary of the items specified in the column and rows of the Cross Tab. What’s more, users can add more than one Cross Tab per report to view things like the details of items purchased by each customer in the design section of the report.
SAP Crystal Reports has many formatting capabilities that can be applied to your Cross-Tab report. By customizing your report with formattings such as background color, borders, and fonts, users can highlight important data in their reports to improve comprehension.
To learn more about the Cross-Tab Reporting in SAP Business One, and to access many other useful SAP Business One resources, watch the recent Vision33 TOTAL Care Wednesday Webchat video where Lewis describes a scenario in which Cross Tabs reporting can summarize information from a query on a report.
This resource is hosted in our Vision33 Resource Library. Gain free access to this resource by signing up. You will also have access to Vision33’s extensive enterprise technology library of training materials for SAP Business One. Inside you’ll find ERP tips and tricks, video demonstrations, product brochures, and many more enterprise technology resources. Select from ERP functional areas to expand your knowledge of specific solutions like SAP Business One as you grow your business. Signup today.
