If you’ve taken my SAP Business One Reports and customization course or been around SAP Business 1 for a while, you’re familiar with reports in SQL and reports in Crystal Reports. I will often write reports in SQL and want them to be a pretty as Crystal reports, but you have an entirely different way of selecting Crystal report fields. This week, Let’s talk about a way to import your SQL directly into a new Crystal Report.
We’ll start with a simple report in the query preview. You can make this a lot more complex with joins and the like, but I’ll use this for simplicity.
I’ll copy the query, then head over to Crystal reports, where I have ready an SAP Business One Connection. You’ll notice on the open connection an item called command above the database. Right-click on command, then select Add Command to report.
That gives you a new window with two blank areas.
For now, we are interested in the area on the left. Paste the query into the box. Click Okay.
Like tables you’ve loaded in the past, Command appears on the right side of the database manager screen.
Click Okay. You go to the Design window.
Open Command in the field inspector. Only the columns you selected are here.
Only the data you identified in the WHERE is here. You have smaller tables and a more efficient report.
You use these like any other Crystal Report field. I can group using the group expert by status, for example.
I can make a quick formula for formatting the status.
Move several of the fields out and place them in the detail of the report, and I’ll select and format the dates together and make them a little smaller.
Run this, and you got a crystal report.
Unlike tables, you’ll set parameters in your query. Let’s see how to do this. Head to database expert.
Right-click on Command and modify the command. The query window appears again. On the right side is a series of buttons. Click create to create a parameter.
Fill in the boxes to make a dateFrom, with a title Date from, and DateTime for a type. Remove the default setting if you want.
I’ll repeat this for a dateTo.
Replace the code in the WHERE clause with these two dates.
Click okay The parameters appear. Add some values here.
Then Okay for both windows. There’s your report.
Using command and SQL is an easy way to take what you already have available and use it in a crystal report. It is a smaller flat table than a linked table if you create the tables in Crystal Reports.
Some add-ons like Boyum B1 up require you to do this for importing crystal reports. Check the documentation for your favorite add-on for specific requirements and directions.
This was a Biz Oneness PowerTip. For more on SAP Business One, Check out my courses in the LinkedIn Learning Library
BizOneness 