CODEWITHSUNDEEP
kentico
Dii
Dii

Reputation: 147

Customizting Reports in Kentico

Any one have an idea on how to add new parameters to reports in Kentico 9 I want to add from-to date parameters in Top customers by number of orders report but I don't know how to implement it?

I've checked Kentico DOC but still I can't customize it, I can only add new fields but don't have idea how to link it with the query.

Also need to know if it possible to export only report to send it for importing it on another server?

Upvotes: 0

Views: 162

Answers (1)

Kristian Bortnik
Kristian Bortnik

Reputation: 838

To add from date and to date parameters for the Top customers by number of orders report:

  1. In the Reporting application, edit the report and open the Parameters tab
  2. Add a new field, named FromDate, of the Date and time type. Set the field caption, as well. Click Save
  3. Create a new ToDate field, similar to step 2
  4. Click on the General tab, and scroll to the bottom - find the Tables dropdown
  5. Select table in the Tables dropdown, click on the ellipsis button, and click Edit Tables edit menu
  6. In the Query field, add the following to the WHERE clause:
AND (OrderDate BETWEEN COALESCE(@FromDate, CAST('1753-1-1' AS DateTime)) AND COALESCE(@ToDate, CAST('9999-12-31' AS DateTime)))

The full query would look something like this:

SELECT . . .
WHERE . . .
    AND (OrderDate BETWEEN COALESCE(@FromDate, CAST('1753-1-1' AS DateTime)) AND COALESCE(@ToDate, CAST('9999-12-31' AS DateTime)))
GROUP BY . . .

This will add a condition to filter orders that have an OrderDate between FromDate and ToDate (which are selectable from the report filters). COALESCE is there to convert NULL values to minimum/maximum dates, in case some date filter values are not set.


Also, to export a report, simply right-click the table header, and choose the export format that is the best fit for your needs.

Report export

Upvotes: 2

Related Questions