With Impel's Ad Hoc Reporting Engine, you can build your own fully customized reports. You can build your reports on any object (such as Accounts, Contacts, Opportunities, Activities, Tickets, Orders, Invoices, etc.) within Impel. The Ad Hoc Reporting Engine allows you to choose any object within Impel, associate it with it's related objects and build reports that make sense for you. You can build reports, run them, export them to Excel. You can also set up reports to run automatically at pre-specified frequencies and be delivered directly to your email box. You can view the reports in Tabular, Collapsed Tabular form (with sub totals) or in a Matrix form. You can also view report data as various types of Charts for better visualization.

For visual instructions on creating Ad Hoc reports in Impel, you can check out our tutorial video on building Custom Reports.

The Impel Ad Hoc mechanism is a comprehensive, complete platform to generate reports that include any data from anywhere in your system, be they Base objects and fields or Custom objects and fields. While building Ad Hoc reports is online and intuitive, here are some tips that will help you build complex reports on your own.

  • Consistency of data models. Ad Hoc Reports, Dashboards and Data Views in Impel, all use the same basic structure in Impel. So you will be able to get data in the way that you want, with the columns that you have selected, with the filters that you have set up, using Ad Hoc Reports, Dashboards or Data Views. 
  • Base and Dependent Objects. Ad Hoc Reports allows you to put a filter on dependent data to filter out specific base data. E.g. you could build a report on Accounts where the Activity Count is greater than 5. You would choose Accounts as the base object and associate it with Activities. You would then add a filter saying the count of Record ID should be greater than 5. 
  • Special columns. Objects have columns called Record ID and GUID that make them unique. The Record ID is a unique number that you can use to make your records unique when retrieved. This is particularly useful when, say, you want all the lines of all invoices of a particular Type. Adding the Record ID as a column will make sure all the records are actually included regardless of what other columns are or are not included. the GUID can be used similarly, but it is a random string of characters that is globally unique and, therefore, may not look very good in reports. Note that, separately, Impel can be configured to stamp every record with a specific sequence number - neither Record ID nor GUID is good for that function. 
  • Unique Records. When you create reports in Impel, the Reporting Engine attempts to improve efficiency by only showing the unique rows among the columns selected. So, if multiple rows, given the columns that you have selected, have the same data, they may show up combined. For example, if you have an invoice with two different invoice detail lines that have the same product, quantity and price, only one of the detail lines will show up in an Invoice Header-Invoice Detail report if only those columns are selected for the report. The way to get around this is to use a field that will make the row unique. You could use the Record ID, which is a unique ID for each record in Impel. If you use the Record ID as one of the columns along with product, quantity and price, you will then see all the entries for the identical rows in the report. 
  • Special Filter-strings. There are 3 special filter-string values available in Impel: one to choose records for the logged in user, one to choose records for the logged-in user's branch and the third to choose records upto the current time. The way to use these special filters is type them in the format "::me::", "::my branch::", "::now::" in the Value

    column in the filter. E.g. for a Ticket report, on the Received by user field, you can choose "::me::". This will ensure that regardless of which user runs the report, s/he will see the Tickets received by that particular individual. On an Invoice report, for the branch, you can choose "::my branch::". This will ensure that any logged in user, will see only the invoices created for his/her branch. On an Activity Report for users, you can choose Activity Start Date till "::now::". This will give you a list of all Activities for the user, upto the Current Date and Time. Activities scheduled for future dates and times will not show up. 
  • Generic filter conditions for Dates. There are several date related filters that you can use to set up dates generically. You have Date Filters options such as today,

    Date filters

    tomorrow, yesterday, this week, last week, next week, this month, last month, next month, this quarter, last quarter, next quarter. All of these filters will give you data for specific time-frames based on the current date. You could also choose specific dates by using the fixed date filter. E.g. if you choose the fixed-date option and select an Activity Report with dates of September 1 2013 to September 30 2013, you will always get data for that specific date range whenever you run the report. Whereas, if you choose Last Month as the date filter, when you run the report in October 2013, you will get data for September 2013. If you run the same report in January 2014, you will get data for December 2013, without making any changes to the filter conditions. 
  • Maximum records. You can get a maximum of 10,000 rows in any Ad Hoc Report. If you have more than that many records, you may need to change the filter criteria that you have used to get within 10,000 rows in a given run of the report. Or you can just use the pagination mechanism to see the next and previous sets.
  • Filters are associated with specific objects. When you create reports in Impel, they will always be associated with the Object that you have applied the filter to, not to a parent Object. Let's say you choose Accounts as the Base Object, select fields from related Invoice Headers and their Details and then apply a filter on the Invoice Created Date of Last Month . This report  will not be a report of Accounts for which you have raised invoices in the last month; instead, it will be a list of ALL Accounts; the Invoices included will only be those within your date-range. If, on the other hand, you want a list of Accounts that you have invoices last month, you should create a report based on the Invoice header and then include fields from Invoice Details and Accounts. On such a report, if you apply a filter on the Invoice Created Date for last month, you will get a list of all the invoices created last month, with the associated data from Invoice Details and Account objects. 
  • Most Recent data. Almost any report in which you can choose multiple rows based on a date can be filtered to show the most recent / oldest / count of records based on

    Most recent data

    the date. E.g. if you run an Account Report with a list of Activities for that Account, you can choose to show that Account with only the most recent Activity showing. 
  • Derived date formats. Date formats internally in Impel are stored in the form dd-mm-yyyy hh:mm. In Ad Hoc Reports, when you choose a date column, you can choose the

    Derived date formats

    date as dd-mm-yyyy. But all that  really happens is that the time segment of the date gets suppressed in the display of the report. You will still see multiple rows in the report. They do not get combined because they are really unique from the date-time perspective. To get around this, we have provided several derived date formats. E.g. Created Date which is a base date has derived date formats such as Crtd Day of Month, Crtd Day of Week, Crtd MMM DD, Crtd Month, Crtd Week Number and so on. Especially for Matrix reports, please use one of the derived date formats for correct results in your reports. 
  • Sorting data. Data is automatically sorted in the order of the columns that you have selected. If you want to change that order, you can move columns up and down in the report selection by dragging and dropping a specific field in the order in which you would like to see the data sorted. By default, the data is sorted in ascending order of each column. You can click on the arrow next to the column to sort it in descending order. And you can set up multiple sort-sequences as the first, the second and so on.
  • Multiple filter conditions. You can have multiple filter conditions on a report. These conditions are considered as "AND" conditions. Currently there is no "OR" condition available in Impel, but you work around that using "inverse" selections - see below.
    • Negative filters. A powerful feature in Ad Hoc Reports is the ability to exclude data based on certain conditions. Let's say you want a report of all Accounts that were

      Opp stage

      not called on during the last month. They way to do this would be to create a report with Accounts as the Base object and to associate it with Activities. In the filters tab, you would choose Activity Start Date equals Last Month as one filter and choose count of Record-ID being less than 1 as the second filter.
    • Multiple uses of fields. Each field in the filter can be chosen multiple times, upto a maximum of 6 times within the same report. E.g. the same Opportunity Stage Filter date can be selected twice to specify a date range. You could choose two separate filter conditions Created Date greater than 31/08/2013 and Created Date lesser than 01/10/2013 to get a list of records created between September 1 and September 30 2013. 
    • Inverse selections. You can use a filter in an inverse selection mode. E.g. let us see you wanted to see all opportunities that are in two particular Opportunity Stages say Quotation OR Negotiation. Since there is no "OR" condition available, you could use inverse selection and set up your filter to say Opportunity Stage not equal to Qualified Lead AND Opportunity Stage not equal to Prospect and Opportunity Stage not equal to Customer. (Qualified Lead, Prospect and Customer being the other three stages in your Opportunity process).
Still have a question? Send us an email - we're always happy to help.