Creatio has some great reporting capabilities that allow for list, chart and various pipeline reporting formats. When building a chart to display totals based on an opportunity, currently there are options to summarize data by Year, Month, Week, Day, Hour, and a few combinations of these options such as Month & Year etc.

One option is currently not available in Creatio: summarization by quarter. It's possible to get this information, but we'll need some pre-calculations.

The solution involves setting up an SQL query (i.e. SQL “View”) that will first perform the necessary calculations to determine which quarter we are currently in for each record that needs to be summarized in the chart.

In this example, we will consider summarizing opportunities by their close date. The chart will need to determine the total closed sales opportunity amount per quarter.

Lets start by creating a query that will perform the quarter based calculation for each opportunity. In order to provide some flexibility in the formats, the query below includes different ways of specifying the quarter. We might just want the quarter number ( i.e. 1,2,3,4) or possibly append it to a “Q” so that it shows up as Q1, Q2 etc.

The SQL Executor tool can be used to write and establish a SQL view named “UsrOpportunityCalculations”. Here is how the query is structured:

Note that the query needs to include a few system fields (Created, Modified dates & users) and an Opportunity identifier field that links data back to an opportunity record. Once this is set up, we are ready to proceed to the next step of integrating it into Creatio as an object.

For the object setup, we need to set up a Creatio schema object that points to this query. Make sure to flag this object not as a regular table, but as a view by checking the box indicated in the screenshot below (“Represents structure of database view”).

Once created and saved, add the needed columns (to match the columns defined in the SQL view). This will tell Creatio what columns of data are available to pull when using the query in a report.

Save and publish these changes and you should be ready to start using it in a chart.

For the final step, we will set up a dashboard with a chart in the Opportunities section. Select the column that should be summarized (“opportunity amount”). Then in the “How to group” section, you will be able to select “Quarter” from the query we built previously. If everything is set up correctly, the chart should be generated with data summarized and grouped by calendar quarter of a year.