Creating a DevExpress Report Printable for Creatio (formerly bpm’online)

Bpm’online has two different out of the box options for creating printables (three if you also count the Excel Reports package available in the marketplace). The usual go-to for printables is a Word printable. They are quick and easy to create. However, there’s only so much you can do in a Word printable. The other, less used although more powerful option is creating a DevExpress report printable. A DevExpress report is similar to Crystal Reports. You have far more capabilities to display the data the exact way you want for complex data or exact report needs. A DevExpress report is significantly more powerful than what can be accomplished in a Word printable, it allows for grouping, formulas, even C# code to manipulate data, and is a great solution for running a printable for several selected records at once. If a Word printable works for your needs, by all means, use it – they are much easier. However, I’ve had many cases where a Word printable just can’t do what I need for a report, but such tasks are perfect for a DevExpress report. Unfortunately, I was disappointed to find that there’s really not much information available about creating DevExpress reports for bpm’online. After figuring it all out via trial and error, I decided it was time for a series of posts on creating and working with DevExpress reports for bpm’online. This first article will provide all the details necessary to create your first DevExpress report for bpm’online.

Overview

To create a DevExpress report for bpm’online, you’ll use the Report Designer tool that can be downloaded from the Academy. This is a Windows application that is similar to tools like Crystal Reports, but specific to working with bpm’online. You’ll log into bpm’online, define what tables/columns you want in the report, then write code to retrieve the data for those tables/columns, this is the part where most people likely get confused. Let’s discuss how the data binding in the report works next.

Data Binding

There are two parts to data binding a DevExpress report:

1. Defining a DataSet (with DataTables and columns) that the report elements are bound to
2. Populating, or filling, that DataSet with actual data

In the report designer, you’ll see a Data button on the toolbar. Clicking this will bring up a dialog where you can select objects/entities and check which properties for these objects you want available in your report. When you do this, what you are doing is defining a DataSet *structure* that the report will be bound to. Each object you move from the left to the right side, is a DataTable in that DataSet. You’re responsible for writing code to fill that DataSet using the report’s onBeforePrint event to fill it with data. You can use whatever method you want to fill that DataSet, such as EntitySchemaQuery (which is the preferred method), direct database queries, Select objects, etc. When you run a report from the client, your report will be passed a collection of ESQ filters for the record or records in the client. You’ll use these to retrieve the necessary data to populate the report’s DataSet (this is why using an EntitySchemaQuery to retrieve the data is the preferred method)

Creating a Report

We will now go through all the steps to create a DevExpress report. I’ll provide you will some reusable code for a basic report. In this sample, we’ll be creating a report for the Account section, although these steps are the same when creating a report for *any* section, even custom ones.

First, download the Report Designer from the Academy. You’ll also find an article there outlining how to connect it to your bpm’online system and also an overview of the tool itself as well as a list of features (sadly, that’s the extent of information on using DevExpress in the Academy documentation).

Now, when you open the tool, you’ll have a blank report designer window. This is where we’ll create our Account report. The first thing we’ll need to do is set some required report properties. Set the following properties in the Properties pane on the right side:

• In the Design section, set the following:
  • Name: “UsrMyAccountReport” (set this to whatever you want the report schema to be named)
  • Caption: “My account report”
  • Package: Select the package you are saving this report to
• In the Parameters section, click the ellipsis and add the following:
  • Name: “Filters” (leave all other properties as-is). This is a parameter that the client will use to pass you a ESQ filter collection for the selected record(s) the report is for
• In the Behavior section, set the following:
  • Display name: “My account report”
  • Scripts (this one is important, this is where you’ll wire up the code to retrieve data for the report but we’ll come back to it in a minute)

Now, click the “Data” button on the toolbar. This is where we define what data we want to use in the report. As I mentioned earlier, this is just defining the structure of a DataSet that the report will be bound to, this doesn’t do anything for actually retrieving this data, we’ll add code in a bit to do that part. For this report, since it will be run from the Account section, we’ll need to add the Account object from the “Available Objects” section on the left. Once added, first check the checkbox for the Account at the top of the right side. Then expand it and check the boxes for the fields you want to include, also can expand child entities or lookups to include columns. Note, if you check a box in a child entity, you need to make sure the checkbox for the entity name is also checked. For example, if you check the “Name” property under “Type”, the “Type” node must also be checked in addition to the “Name” node.

With this step done, you’ll now be able to use the Data tab (on the left with the Property pane) to drag fields onto the report designer, but we’ll come back to that.

Now, we’ll add the code that retrieves the data. I’ll be providing some generic, reusable code that will do this using the structure of the DataSet designed by the object & properties you selected in the Data dialog. However, as I mentioned you can use whatever method you need to do this. For a basic report, such as this one, we’ll just have one object added (along with child objects). This code will work in this scenario (more on this after the code sample).

To add this code, in the properties pane, in the Behavior section, expand Scripts, then use the dropdown on “Before Print” and select New. This will take you to the Script editor (you can get to this, or back to the report, by clicking the “Scripts” button on the toolbar). You’ll have an OnBeforePrint event created for you. First, add the following namespace:

using Terrasoft.Core.DB;

This namespace is needed in the code below for the second line to the end. It adds an extension for DataTable that allows you to Load the DataTable from an EntitySchemaQuery.

Now, add this code, exactly as-is, to the OnBeforePrint method:

    // be sure to add using Terrasoft.Core.DB;

    // get a reference to the report & connection
    var report = (Report)sender;
    var userConnection = report.UserConnection;

    // get the dataset defined in the Data dialog
    var data = report.CreateDataSet();

    // create ESQ query and add columns defined in report dataset
    var esq = new EntitySchemaQuery(userConnection.EntitySchemaManager, data.Tables[0].TableName);
    foreach (DataColumn column in data.Tables[data.Tables[0].TableName].Columns) 
    {
        esq.AddColumn(column.ColumnName);
    }

    // add the filters passed from the client for the selected record(s)
    var filters = report.Parameters["Filters"].Value as IEntitySchemaQueryFilterItem;
    if (filters != null) esq.Filters.Add(filters);

    // fill the dataset from the ESQ query and set as datasource for report
    data.Tables[data.Tables[0].TableName].Load(userConnection, esq);
    report.DataSource = data;

Any time you add code, it’s always a good idea to click the “Validate” button on top of the code editor window. This will check your code for any errors. When you save and publish the report it doesn’t necessarily do this, so the Validate button will help find issues before you go through that process. As I mentioned, the code above uses the defined structure of the report DataSet to know what entity to use, and the columns, for the EntitySchemaQuery (and also applies the ESQ filters passed from the client for the selected record(s). You don’t have to create your EntitySchemaQuery like this, you can do it how you would normally, but doing it this way will make it dynamic & generic, you can check more properties in the Data dialog to add to your report and this will pick them up, no need to modify the code to add more columns to your ESQ. The above code will work for any report where there’s one main entity added. If you’ll be adding more than one entity, such as Account and Activity, for example, you’ll need to do something similar, doing the following:

1. Populate each DataTable in the DataSet
2. Be sure to use the passed ESQ filters to apply the filters for each object retrieved
3. Then, this part is important, set up DataRelations in the DataSet to describe how the data is related, such as the following:

ds.Relations.Add("AccountActivity",  
  ds.Tables["Account"].Columns["Id"],  
  ds.Tables["Activity"].Columns["AccountId"]);

Again, this is only necessary if you’ve added more than one base object to the report (meaning, moved more than one entity from the “Available Objects” list in the Data dialog to the right).

Now, click the “Scripts” button again to close the Script editor and get back to the report designer. You can now, set up the report by using the Data tab (on the left with the Property pane) to drag fields onto the report designer. Right-click to add new bands, for grouping head or footer sections or additional detail sections as needed.

When done save/publish the report using the Save button on the toolbar. Once complete, the report will now exist in your package.

Now, open the printables lookup, you’ll be able to select the report you saved to your package for the report. Click New, then select DevExpress. Select Section the report is for (this should match the base entity you set for the report, in our case Account). Click the lookup for “Template”, then choose the report you created. You’re done!

Troubleshooting the Report

When you run the report, if something goes wrong you’ll get a “Failed” report download:

This usually is due to an issue with your code, but knowing how to find the answer to the problem or troubleshoot can be difficult. To troubleshoot, open the browser dev tools, go to the network tab, then run the report again. You’ll see the failed report as a request to “CreateReport”, click it and view the preview tab and you’ll see the error details (which is most likely a problem with the report code or query. The error details will likely provide a line number that is the issue).