How to create reports in SAP Commerce (Hybris) Backoffice? We are going to learn how to create a basic report in Backoffice.

Backoffice Report

Backoffice Report Definitions are for basic reports. SAP Commerce gives an ability to get reports on Backoffice. However, Backoffice reports are not recommended for marketing and statistic reports. It is not useful for working and reporting big data. It has an Excel Export feature but it has a row limit. So, it would be best if you considered using a different tool for reports with big data.

You can create any report in Backoffice and you can export the report by CSV. This feature comes with SAP Commerce OOTB (Out of the box). Main part of a report is Flexible Query. So, you can display any report by using Flexible Query.

Scenario

We are going to build a report to fetch orders which have users with a custom field. This field is defined in User table in items.xml as following. Column name is amcNumber.

<typegroup name="User">
	<itemtype code="Customer" autocreate="false" generate="false">
        ...
        <attribute qualifier="amcNumber" type="java.lang.String">
	        <persistence type="property" />
	</attribute>
    </itemtype>
</typegroup>

Flexible query for fetching orders that have users with amcNumber in between dates:

select {c.uid} as email, {c.amcNumber} as amcNumber, {o.code} as orderCode, {o.totalPrice} as totalPrice, {o.creationTime} as orderDate from {Order as o join Customer as c on {c.pk} = {o.user}}
where {c.amcNumber} is not null and {o.creationTime} between ?startDate and ?endDate

This query joins Order and Customer tables. Fetches orders that amcNumber is not null between given dates. Aliases are imported for backoffice reports. They will be used as parameters in the query. You should use aliases.

Create Report Definition

In Backoffice Administration Cockpit go to System -> Report Definitions

Click create button (plus icon). Give identifier as you wish and paste your query in the Query part.

Columns: Select part of your query. In our sample there will be email, amcNumber, orderCode, totalPrice, orderDate

Params: Where part of your query. In our sample there will be startDate and endDate

Columns

You have to add columns of your query. Click create new view attribute descriptor

Qualifier: Name of the column. It should be same as alias in the Flexible Search Query.

Feature Type: Type of the column. You can get the type definition of the column in -items.xml file.

Create columns for all fields of your query.

Create Params same as Columns. Params is for where part of the query.

Final result of initial definitions

Click done.

Report definition setup is done. Click the button to run report.

Running Backoffice Report

Fill the required fields. Put the date range. Click search button and you will see results.

You can get export as CSV if you want by clicking Export to CSV button. Don’t forget there is a line limit to exporting CSV

Customizing Backoffice Report Columns

There are endDate and startDate columns in the report. Params part is coming to report as default. But they are empty. Nonsense. I don’t want to see them in the result. To remove columns and change column order, we need to customize -backoffice-config.xml file.

Add the following lines to your backoffice extensions backoffice config file.

<ybackoffice>–backoffice-config.xml

<context type="OrdersWithAmcNumber" merge-by="type" parent="GenericItem" component="listview">
    <list-view:list-view>
        <list-view:column qualifier="email" />
        <list-view:column qualifier="amcNumber"/>
        <list-view:column qualifier="orderCode"/>
        <list-view:column qualifier="totalPrice"/>
        <list-view:column qualifier="orderDate"/>
    </list-view:list-view>
</context>

Run ant all and start the server again.

Run the report in Backoffice -> System -> Report Definitions

Creating a basic report in SAP Commerce is as easy as you see. I hope this article would be helpful for you.