Report Example – Quotations This Month

The report opens with the period set to This month, so it always shows the current month’s quotations. You can choose another period above the report. Right-clicking a quotation opens it, or opens the customer. A second version, fixed to the current month, shows the quotations as a chart on the Mobile App Dashboard.

💡This example shows how to create a custom report that lists the quotations for the current month, with their customer, status and totals.

What the Report Shows#

Before You Start#

Find the code of your Quotations Transaction Type: it is in the Code column of the Transaction Types list (e.g. QT; the Setup Example – Quotations uses CUS QT). The SQL below uses QT; replace it with your code.

How to Create the Report#

1. Navigate to Edit Reports.
2. Click New (button) and fill in the fields as follows:
   1. Description = Enter Quotations.
   2. Code = Enter Quotations.
   3. Chart Type = Leave Column diagram selected.
   4. Note = Enter Lists the quotations for the selected period (this month by default). (optional).
3. Click Save (button).

How to Add the SQL#

1. Click Actions (button) > Edit SQL.
2. Paste the SQL below into the editor. Replace QT in the WHERE line with the code of your Quotations Transaction Type.
3. Click Test (button). A message says “Test passed.” and lists the parameters date_start and date_end. Click OK (button).
4. Click Save (button).
5. Close the SQL popup (X in the top corner) and wait a few seconds while the fields are generated.

SELECT
  th.trans_header_id,
  th.contact_id_entity AS customer_id,
  ts.code || ' ' || COALESCE(th.trans_no_text, th.trans_no::varchar) AS quotation_no,
  th.date_trans,
  th.date_expires,
  COALESCE(cus.full_name, cus.nickname) AS customer,
  COALESCE(per.full_name, per.nickname) AS contact,
  COALESCE(rep.full_name, rep.nickname) AS rep,
  th.description AS reference,
  st.code AS status,
  th.total AS total_exc,
  th.vat,
  th.total_vat AS total_inc
FROM trans_headers th
JOIN trans_setup ts ON ts.trans_setup_id = th.trans_setup_id
LEFT JOIN contacts cus ON cus.contact_id = th.contact_id_entity
LEFT JOIN contacts per ON per.contact_id = th.contact_id_person
LEFT JOIN contacts rep ON rep.contact_id = th.contact_id_rep
LEFT JOIN status st ON st.status_id = th.status_id
WHERE ts.code = 'QT'          -- the code of your Quotations Transaction Type
  AND th.deleted_id <> 1      -- leave out deleted quotations
  AND (CAST(:date_start AS date) IS NULL
       OR th.date_trans BETWEEN CAST(:date_start AS date) AND CAST(:date_end AS date))
ORDER BY th.date_trans, th.trans_no

How the SQL Works#

How to Set Up the Fields#

On the Fields tab, click Edit (button) if it is not blue, and edit each field as follows. The Name and Data Type columns are filled in from the SQL and cannot be changed. The captions of hidden fields do not matter.

To add a chart, check Chart Category for customer and Chart Series for total_exc (optional). The report’s Chart tab then shows one bar per quotation, labelled with the customer. Click Save (button).

How to Check the Period Filter#

1. Click Filters & Drilldown (tab).
2. Check that the Filters section has a Period (Date Start and Date End) filter. Edit Reports adds it when it generates the fields, because the SQL uses :date_start and :date_end. Its caption cannot be changed.

How to Add the Drilldowns#

1. On the Filters & Drilldown tab, click Edit (button) if it is not blue.
2. In the Drilldown section, add a row for the quotation:
   1. Type = Select Module.
   2. Module = Select Transaction.
   3. Fieldname = Select trans_header_id.
   4. Menu Caption = Enter Open Quotation.
3. Click Save (button).
   • Note: An empty row is added for the next drilldown.
4. In the new row, add the drilldown for the customer:
   1. Type = Select Module.
   2. Module = Select Contact.
   3. Fieldname = Select customer_id.
   4. Menu Caption = Enter Open Customer.
5. Click Save (button).

How to Give Users Access#

1. Click Menu (button) > Edit User Access.
2. Move the users who may see the report from the Available section to the Selected section.
3. Click Save (button).

How to View the Report#

1. Close QuickEasy BOS and open it again.
2. Add the report to a Menu Group, on its own or with your other custom reports (see Menu Groups).
3. Open Custom Reports from the menu and select Quotations in the report filter (if the menu has more than one report).
4. Check that the period is This month, or select another period.
5. Click View (button).
6. Right-click a quotation and select Open Quotation or Open Customer to open it.

How to Show the Report on the Mobile App#

The Mobile App Dashboard runs a custom report’s SQL exactly as it is saved. It has no filters or drilldowns, so the SQL cannot use parameters such as :date_start, and it shows each report as a chart. Create a second report for the Mobile App, with the current month fixed in the SQL.

1. Create a new report as described in How to Create the Report, with Description = Quotations (mobile app) and Code = Quotations (mobile).
2. Add the SQL below, as described in How to Add the SQL. It is the same SQL with the two date lines replaced, so the report always shows the current month. When you click Test (button), the message “Test passed.” appears.
3. On the Fields tab, set up the chart:
   1. Chart Category = Check it for quotation_no (one bar per quotation). Exactly one field can be the category.
   2. Chart Series = Check it for total_exc (and total_inc if you want a second bar).
      • Note: The other field settings, including the captions, do not matter, because the Mobile App shows only the chart. The caption of the Chart Series field appears in the chart’s legend (e.g. total_exc, or Total Exc if you change it).
4. Click Save (button).
   • Note: No Period filter is created, and no drilldowns are needed.
5. Give the users access to the report (see How to Give Users Access).
6. For each user who must see the chart, add the report to their Mobile > Dashboard (Select from Custom Reports) user setting: in Users, right-click the user and select View/Edit User Settings, click Edit (button) in the setting’s Value column, and move the report from Available to Selected.
7. On the Mobile App, open the Dashboard and click Refresh (button).

SELECT
  th.trans_header_id,
  th.contact_id_entity AS customer_id,
  ts.code || ' ' || COALESCE(th.trans_no_text, th.trans_no::varchar) AS quotation_no,
  th.date_trans,
  th.date_expires,
  COALESCE(cus.full_name, cus.nickname) AS customer,
  COALESCE(per.full_name, per.nickname) AS contact,
  COALESCE(rep.full_name, rep.nickname) AS rep,
  th.description AS reference,
  st.code AS status,
  th.total AS total_exc,
  th.vat,
  th.total_vat AS total_inc
FROM trans_headers th
JOIN trans_setup ts ON ts.trans_setup_id = th.trans_setup_id
LEFT JOIN contacts cus ON cus.contact_id = th.contact_id_entity
LEFT JOIN contacts per ON per.contact_id = th.contact_id_person
LEFT JOIN contacts rep ON rep.contact_id = th.contact_id_rep
LEFT JOIN status st ON st.status_id = th.status_id
WHERE ts.code = 'QT'          -- the code of your Quotations Transaction Type
  AND th.deleted_id <> 1      -- leave out deleted quotations
  AND th.date_trans >= date_trunc('month', CURRENT_DATE)::date                      -- from the 1st of this month
  AND th.date_trans < (date_trunc('month', CURRENT_DATE) + INTERVAL '1 month')::date  -- up to the 1st of next month
ORDER BY th.date_trans, th.trans_no

In the second example below, customer was selected as the Chart Category instead of quotation_no, and the caption of total_exc was changed to Total Exc.

Variations#

Troubleshooting#