A Report is a great way of showing important information to you in graphic, detail or summary form. This data can be exported to PDF, Excel or CSV.
You can show report data on your home page, giving you an overview of the data you wish to see.
Examples of Reports are:
- A list of Sales Orders created by members of the Sales team on a weekly basis.
- A list of how many invoices are raised for each Company.
- How many Helpdesk tickets are raised by each Customer.
This can be a one-off report or a recurring one that is set to occur when you need it – monthly, weekly - you can select and define how often the report needs to be run. You can also select which users you would like to receive these scheduled reports.
You can create reports for one or more modules (for example Contacts and Opportunities) and in addition to the module reports you are able to report on Opportunity Revenue, Opportunity Sales Stage and Audit Trails, enabling you to track and report on record changes in OpenCRM
Before you get started:
- Ensure you are starting in the correct module – if you start in the wrong module, for example you are in Contacts module and want to create the Report relating to Personnel, you will need to start from scratch again.
- Decide which module in OpenCRM is likely to be home to the bulk of the data. E.g. if you want to see a report showing Invoices, by company raised, the logical step would be to create an Invoice Report, since the Invoice will hold the Company name. However if you also wanted to see, in the same Report the contact information for that company, for example, or any company specific information which is not stored on the Invoice itself, you will need to select 'Companies' as your Related Module.
- Conversely, If you require a lot of Company data and only the name or the date of that companies' most recent Invoice then your primary module should be the 'Companies' module and the related module would be set to Invoices.
- You also need to consider how much data you wish to feature in the report. This is where you need to filter data. A report showing ALL leads is likely be too big to be of any use, a report showing new leads from the last seven days is probably more useful.
Example of Report Creation
Let’s Create a Report to show Quotes by Company.
Go to the Reports Module and select New Report, and then select the Modules that you want in the Report, in this case Companies and Quotes and click on Continue.
You are now on the screen where you can set the parameters for your report:
Step 1: Report Type
You will need to select the type of Report you would like to run. A tabular report will show you a grid of all your data. A summary report will show you this same grid and then, at the bottom, show the sums, counts, etc. of particular groups of fields. (For example, you may wish to group this example report by User, to see who is generating the most Quotes).
In this example, we'll create a summary Report.
Step 2: Columns
We now need to select which columns we want to show on the report. Make sure you include any columns you need to filter on or sort by.
The menu on the left allows you to select the fields from the Companies and Quotes modules. In order to show Companies that actually have Quotes linked instead all Companies we now need to apply a filter to remove records where the Quote field is empty. This is done by specifying the criteria, the filters work the same way as when creating Custom Views - please refer to this FAQ.
Step 3: Grouping
This will only appear if you have selected to create a summary Report.
Here you can select how you wish to group you data. For example you may wish to see all the Quotes grouped by the Company and their assigned user.
If you are planning to show any totals or counts, make sure you tick the "Display group totals" boxes.
Step 4: Totals
This will only appear if you have selected to create a summary Report.
This is where you will select which number fields you would like to see totaled by the groups you selected in the previous step. For this example, let's assume we want to see the total amount for all the Quotes, as well as their average.
Step 5: Filters
This is where you will filter down your data to make sure you only see the records you most need to view.
As you can see in the screenshot below, we have set a further filter to only show Company Quotes from the last month that have not been cancelled.
We can also sort the view according to any of the fields we have entered – User, Company or Quote Stage.
You can also filter using your pre-set financial periods. You can also set up your own financial periods by following the steps in this FAQ.
As of version 3.9.7, when you're in the standard filter section you will have some additional options when you select "custom" you will be able to select "Selected Dates", "Next", "Last", "More Than" and "Less That" you will then get the choice of unit of time so 1, 2, 3 etc then you will have option between Days, Weeks, Months and Years. Selecting these will do as they say "Leads created within the last 3 days" for example.
Step 6: Scheduling
You will now see series of options, that will allow you to specify the frequency of the schedule and who should receive the report, sent by email. You have a time field which allows you to specify the time that you wish the report to be created.
Important: Be sure to set either a Number of Recurrences or an End By Date before you hit Save.
Within this container is a user multi-select pop up and contact selector, similar to that found on Emails. Click on either of the icons to select who you wish to send the report to.
Once a Contact has been added to this box when the report is ran on a schedule it will send the generated PDF to the specified contacts.
For more information on our tips on the best ways to manage your scheduled reports, please see this article: https://opencrm.co.uk/help/?kb=1022287&article=Managing-your-scheduled-Reports
Step 7: Graphing
Important: You can only set a graph on a summary Report.
The X (horizontal) axis will by the fields you set as the Groups. the Y (vertical) axis will either be the columns you totaled or (if you prefer) you can use a count of the records by setting 'Number of Records' under 'Graph Values.
Step 8: Home Page
If you would like the graph, Report grid, summary, or a combination of those results on your or any other users' home page, you will need to set this here.
Step 9: Security
Last step! 🙂 And it's a big one.
If you do not make any changes to this section, your Report will be assigned to the person who created and the security will be set using the Default Organisation Access you have set up.
Should you wish to restrict access to this (or any other) Report, you can do so by
1. Selecting the User to whom the report is assigned, this will by default be the User creating the report, when creating the report you have the ability to reassign.
2. From the next drop-down menu you can select the report security level:
3. You can use the final two menus items to select and restrict access to the report at both Group and User level.
Step 10: Results
By clicking "Save and Run" you will be able to set the name of your Report and choose a folder for it.
We do suggest giving it a sensible name and description so you (or anyone else) can find it later.
You now have the finished report, which can be exported to PDF, Excel or CSV file. You can see that the totals have been included at the bottom of the Report.
If you are finding a blank column on your report, you need to check the parameters of your search. If for example you select a view to show all Quotes created for Companies, you need to filter the results so as not to show blank records. Please refer to this FAQ for details on how to filter view search results.