The Devexpress Report Builder

On this page you will find tips and tricks on creating your very first report.

Creating a new report

If you are starting from clientele go to the Manage>Reports>Reports section click Add and press Yes.

When you already have the desgner open you can select New report

Optional - Make the report background dark gray via Page > Page Color and set it to #F5F5F5 (so that later added white boxes are visible in the report)

Set the report's units of measure to millimeters by selecting the entire report, clicking the gear icon, and measuring units from hundredths to tenths of a millimeter.

Then you set the margin of the report all to 100 via the properties

Also set the Paper kind of the report to “A4” in the Properties of the report.

Adding a datasource

You can add multiple types of data sources, but for the example we will work with a Clientele ITSM database.

Tables and Views

Choose the tables/views you want to use for the report (You can apply relations, parameters and filters to the queries at a later stage).

Report parameters

You can now add parameters on which you want to filter the query in the report.

Right click on Parameters in the field list and then on Add parameter.

You will now see a screen where you can determine the data of the parameter.

If you want to ask the user to fill in the parameters before generating the report, enable the Show in the parameters panel option

If you want to add a list of parameter options, you can do so via the Value Source section. Here you can choose from a static or dynamic list. You fill a dynamic list on the basis of a data source that you can add via the steps from step 2. Then choose the table for the parameter values ​​as data member. In the value member you can choose which field works as a value and the display member determines which data the user sees in the parameter list.

If the parameter is of type Date you can choose to set a Date range as parameter. You do this in the Value Source section and then choose Date Range.

Manage queries

Right click on the data source in the Field list and then click on Manage Queries.

At the query where you want to apply the relations, filters and parameters, click on the ellipsis (...) on the right side.

You will see an overview of your SQL query and the option to modify it by clicking on Run Query Builder.

Query Builder

Using the query builder you can drag tables and views from the left side to the middle to process them in your query and establish relationships between the tables. Select within the table which fields you want to include in your report.

You can also specify any sorting or grouping in the fields via the options located at the bottom of the form.

At the bottom left you can create parameters for the queries and determine filters.

Query parameters

You can set parameters that are only for the current query or use the pre-made parameters. To do this, click Edit parameters and then Add to add a new parameter. Make sure Expression is checked and then from the Value list choose the parameter you have preset.

Info

These report parameters are indicated with a “?” before their name. This way the query parameter gets the value of the parameter in your report.

Click OK when you are done

Query Filters

Click in the query builder or filters to specify which rows from the query are included in the report. You can determine these filters on the basis of fixed values ​​or the pre-made query parameters.

In the filter editor you can create groupings of "And, Or, Not and and Not" or filters by clicking on the filter group.

By clicking on the +(plus) sign in that group, you create a new filter. By clicking on each element in the filter you can change the values.

1. Click on the field to change the field you want to compare.

2. Click on the operator to change the comparator

3. Click on the icon in the value to change the type of the value to parameter, fixed value or another field

4. Click on the value to adjust the value.

(You can also create the filter in code via the field below)

Once you're done defining the tables, relationships, query parameters, and filters, you can exit the query builder to build the report. Click Ok to close the query builder.

Then click Next in the Query editor, here you get a final overview of all parameters in the query (You can still make changes here). Click Finish to completely close the query editor. Then click Ok to close the query manager overview.

Now set the default data source of the report by selecting the report in the designer and setting the Data Source and the data member in the properties. The data member determines which database table is used for the data on the report.

Insert Report Bands

Right-click anywhere in the report. Then navigate to Insert band > PageHeader (You choose Pageheader because ReportHeader only appears once at the top of the entire report and pageheader on every page), to insert the header of the report.

Then do the same for each part of the report by using InsertBand > GroupHeader (For each group header you can also add sub-bands for extra information in the same group).

The level of the group header determines the position of the section in the report relative to the detail section.

Placing panels

It is useful to divide elements into panels, this keeps things organized and makes it easier to move elements.

Place a panel from the left frame inside the report.

Make the background white to make the panel visible against the gray background of the report.

Adding tables

Create table header & structure

First, let's create the header of the table. This only needs to appear once on the page, so it contains no grouping.

Slide the table element out of the left frame of the designer.

Select the table in the report and click Design at the top of the designer. Here you can add rows and columns along with all other table settings.

All settings regarding the contents of the table can be found in the properties by selecting the table.

Create table contents

Slide another panel and table into the detail below GroupHeader.

Select the detail that contains the content table and then click the gear icon, to the right of it, to display the detail tasks of the details. Here you click on the ellipsis of Sort fields to establish a Grouping.

By setting up a grouping, you determine iterations of a GroupHeader or Detail based on rows in your data source.

In the Group Field Collection Editor, choose which field you are going to group on. Press Add and in the Field name choose the group field. Then press OK. You can also group on several values ​​at the same time and determine the sort order of each value.

Remember that the report sections of a table are always set up like this:

• GROUPHEADER: for table header
• DETAIL: for table contents (iteration)
• GROUPFOOTER: for static info under table (optional)

Info

If you do not have a groupfooter, a groupheader can also be used as a detail.

To get values ​​in the table, drag the values ​​from the Field list to the cell.

Generate Sample

To see the result now, click on Preview in the top right of the designer, in this screen you set the values of the parameters and press Submit to generate the report.

Additional information and tutorials regarding tables can be found here.

Calculated fields

Adding a calculated field

With a calculated field you can show values ​​on the report that result from calculations on the query fields together with any filters. You create a calculated field by right-clicking in the field list and then on Edit Calculated Fields. (You can also press ADD but then you have to change it later)

Click Add and choose a name for the calculated field. Determine the data source & data member of the calculated field to determine its scope. Also determine the field type.

In this example we create a calculated field that counts how many records are closed. For other specific expressions for calculated fields, see:

• DevExpress Calculated fields tutorial

• DevExpress Expressions tutorial

Click on the ellipsis at Expression to write the Expression.

Write Expression

You can write expressions by calculating with fields (eg: [Fieldname1] * [Fieldname2]). Or by using IF statements (eg: Iif(Condition,Result,Else Statement).

But in this example we are going to work with aggregate expressions, the formula is as follows:

[Collection][Condition].Aggregate(Expression)

Calculated field in report Close the Expression editor and calculated field collection editor by clicking OK. Then drag your calculated field into the report to use it.

You can see the result by previewing the report.

Summary functions

Create Summary field

You can also apply aggregate functions to fields in the report. The function is limited to that field and it cannot be reused as with a calculated field

Select the label or field where you want to apply the summary function and click on the cogwheel and then on the ellipsis (...) of Summary.

Set Summary

In the running summary, choose which scope the summary works on. This determines from which point the function starts calculating again (Group, Page or Report).

Via the summary function you choose which aggregate function you want to apply to the field.

Via the Argument expression you write the conditions that the function must meet by means of an expression

In the example we count again the number of records that are closed.

For more information about summary functions, please visit:

• DevExpress Summary function tutorial

Charts

Placing a chart

Drag the chart from the left bar into the report

Charts are separate from the data on the report, so you have to set the Data source and data member for each cher via the Properties.

Chart designer

Open the chart designer by selecting the chart element, clicking on the gear and then on Run designer

Create Chart Series

In this example we are going to create a Bar chart that counts how many closed invoices there are per week.

You can generate charts based on 2 fields from your database, but we chose this example to also show aggregate functions in charts.

Click on the + (plus) next to Series to create a new Series and then choose Bar (You can choose 2D or 3D according to your preference)

Note

The preview you will see is an EXAMPLE, no real data is shown here and is only intended to give an idea of ​​what the chart will look like.

Chart Argument

Each chart consists of at least one Argument(X) and a Value(Y) field. We will calculate the value via an aggregate summary value, but because we want to divide these values ​​per week, our argument is the InvoiceDate field.

Navigate to the Data section and drag the field to the Argument of the Series.

Chart aggregate summary values

At this point, a Bar would be generated on any date from the query. Only the values ​​of this are still empty because they are not yet set. We are going to set up an aggregate summary function via the properties section.

Since the argument is of type Date, the Summary function will automatically be a Date-Time summary function.

Via the Measure unit you choose by how long you want to group the values ​​(in this example we use Week)

The measure unit multiplier value determines whether this is every 1, 2, 3… weeks that the data is grouped.

Press the Summary function field to determine what aggregate function will be used (count, sum, average…)

Via the Filter string you can apply conditions to the data as with the Query filters.

Close all screens by pressing OK and view the result via the Preview