In today’s data-driven business environment, timely and accurate reporting is critical for decision-making, compliance, and customer communication. Whether it’s a sales order, invoice, or shipment summary, efficiently generating and sharing these reports can significantly reduce operational friction.
In a rapidly evolving digital landscape, the ability to promptly generate and distribute accurate reports is essential.
Whether you’re dealing with invoices, sales orders, or delivery summaries, streamlined reporting plays a vital role in effective decision-making, regulatory compliance, and seamless customer communication.
That’s where bConnect makes a real difference.
One of the powerful capabilities of bConnect lies in its ability to automate the generation and delivery of reports using Crystal Reports and a robust configuration framework. Let’s look at how this process works and how businesses can benefit from it.
What is a Query Report?
Query Reports are reports generated by running SQL queries on a database to extract specific, targeted data. Unlike standard reports, which may follow a fixed format or template, Query Reports in SAP B1 allow users to define exactly what data they want to retrieve by writing custom SQL commands. This makes them highly flexible and useful for generating tailored insights from complex datasets.
In business tools like bConnect, Query Reports can be generated using SAP Business One ERP solution databases and distributed via email and other options, helping teams access the right information at the right time for better decision-making and operational efficiency.
Creating a Query Report Rule
To start things off, users first need to create a “Rule”.
Users must first click on the New Rule option and select Advanced Rule in the main bConnect window
This opens the Rule window with tabs for General, Report, Parameters, Email, Format, Schedule, and Options
In the General tab, the users must fill out the desired details in the given options. For example, in the following image, the Title is entered as Query Report, Source Database is set to GstOneV6, Folder is set to Monthly, Rule Type is set to Email, Source Type is set to Query Report, and Status is set to Active
Once done, users must then click the Add button on the bottom left to add the Query Report rule in the Rule Definition window
When added, the newly created Query Report rule is now available in the Rule Definition window
Query Report Generation
As the rule for Query Report is now created, we will take a look at how users can generate one.
In the Rule Definitions window, users have to select the Query Report rule and then click on the Edit option, present above the Folder window
This opens the Rule window, where users can then edit all the parameters as needed. We start with the General option
Since the General was already created according to requirements, there is no change needed.
Next, users move on to the Query tab, where they can find the option SQL Command for Report. In this option, the user has to add queries with the fields they require in their query report. In the image below, we can see an example of a SQL Command for Report query
Going further, users move on to the Parameters tab, where Command Type, SBO Query, and SQL Command for Parameters are available. In this tab, users can pass various parameters related to the query report. An example of this can be seen in the image below
Moving forward, the user has to go to the Email tab, where multiple options related to email are present
Here, the user has to fill in/select the required options, such as the ones visible in the example image
Next, users must select the Format tab and fill in/select the required options. An example of this is seen in the image below
Moving on to the Schedule tab, users need to define the schedule for Query Report generation. Here’s a specimen of the same
Once done, users simply have to click the Update button located in the bottom left of the Rule window
Sending Query Report
To send reports, users must first select one of the rules from the various rules created and available to them in the Rule Definitions columns in bConnect. Second, they have to select the Rule from the columns, in this case Purchase Order
Once selected, they have to then click on the Edit button beside the New Rule button sitting on top of the Folders column
Once done, the Rule window will then pop up with tabs for – General, Query, Parameters, Email, Format, Schedule, and Options
Within the General Tab, users can find fields for – Title, Description, Source Database, Folder, Rule Type, Source Type and Status.
Here, the users have to select SQL Query in the Source Type Field. It has to be set to SQL Query to send the Query Report in the mail body.
Next, users have to switch to the Query tab. Users have to add the fields in the SQL Command for Report box to send the data of those particular fields through the report, as visible in the picture.
Then, coming to the Email tab, users have to come to the Send Message As field and select the Body option from the two given choices.
Next, in the Format tab, users have to set the Message Format to HTML and enable the Use Fields Names on First Row checkbox by clicking on it.
When all of it is done, users then have to click on the Update button on the bottom left of the Rule window.
Once done, users then simply have to right-click on the Purchase Order rule and select the Execute Now option.
bConnect will then prompt the user through a pop-up asking whether to execute the rule. Users simply have to click Yes
When done, users will be able to see the progress of the query they just executed, as visible in the image below.
View of a Query Report
If you were wondering what a Query Report looks like, then we have you covered.
Conclusion
In an age where speed, accuracy, and efficiency are paramount, automated reporting tools like bConnect empower organisations to stay ahead. By simplifying the process of creating, scheduling, and distributing data-rich query reports, bConnect ensures that key stakeholders always have timely access to the insights they need.
Whether a routine purchase order or a complex delivery summary, configuring query report automation in SAP B1 using Query Reports through a flexible rule-based system reduces manual effort, minimises errors, and accelerates decision-making.
From defining rules to customising report formats and automating email delivery, bConnect turns reporting from a cumbersome task into a streamlined, strategic advantage. If your business relies on consistent, accurate reporting, it’s time to leverage the full power of automation, and bConnect is built to deliver just that.
