Home → Data Management → Printer Friendly Version
Data Management
1. Reports module
1.1. Overview
The Reports module is the main reporting tool within RubiCRM and can be located from Main Menu > Data Management > Reports.
You can use the reporting module to generate customised reports based on the data held within your RubiCRM system.
Please note the reports module is not designed to replace or compete with market-leading data visualization software such as Power BI or Excel. The RubiCRM reports module is a simple data in > data out module with basic data manipulation and visualisation capabilities.
If you require extensive data manipulation/visualisation we recommend exporting/linking any RubiCRM reports into more specialised software. Please refer to the related article on this.
The reports module consists of four areas:
- Dashboard
- Report Builder
- Saved Reports
- Audit
Dashboard
When you first open the Reports module the Dashboard is the first area you are greeted with.
The area is designed for you to add reports that you run regularly so that they are quickly accessible avoiding the need to search through the list of "Saved Reports".
Report Builder
The Report Builder is the core area of the Reports module. This area allows you to generate reports based on outputs and filters and display them in different ways. You can display data in a grid, chart or export it to Excel for further data manipulation.
Saved Reports
The Saved Reports area of the Reports module allows you to view previously saved reports. This allows you to reuse reports avoiding the need to recreate reports you run regularly saving you time.
From the Saved Reports area, you can select which reports you wish to add to your reports Dashboard allowing for quick access to your most in-demand reports.
Audit
The Audit area of the reports module allows you to view which reports have been run by other Rubi Users. The audit provides basic information such as:
- Who ran the report
- When was the report run
- The name of the report (if it was a saved report)
- The outputs and filters run
1.2. Building a report
A report consists of two fundamental things:
- Outputs
- Filters
Once you have the Outputs and Filters you can start adding Output Settings for each Output you have selected by giving your Outputs specific names which we call an Alias, adding a Function to the Output or choosing to Sort the data in a specific order.
Once you have your Outputs and Filters, you can choose how you want to view the data: on a grid, exported to Excel, visualised onto a chart, or shown on a pivot table.
Outputs
Outputs are the fields of data you want to see. If you were working in Excel imagine these as the different columns of data in the Excel file.
Outputs can be anything in the CRM and are categorised into the following:
- Primary Record
- Contact Record
- Interaction
- Booking
- Event / Training
- Export Doc
- International Trade
- Sale / Pipeline
- Record Activity
Filters
Filters allow you to say what kind of data you want to see from the results.
This could be anything from:
- Adding a filter to only return primary records which have a membership/customer status of "Full Member" or "Active".
- Adding a filter to only return contacts who are marked as a "Main" contact.
- Adding a filter to only return events/training if held within a specific date range.
- Adding a filter to return only the companies that have bookings where the booking status is "No Show".
Output Settings
Once you have your Outputs and Filters you may wish to start adding additional settings to your Outputs. To open the Output's Settings click on the icon () next to the Output you want to edit.
This will open the Output Settings popup where you can change the Alias, select a function or add a sort order.
Report Data Settings
The Report Builder also allows you to limit the number of results the report returns. This is done in the Report Data Settings section of the Report Builder.
Under the "Columns", "Outputs" and "Filters" sections there is the Report Data Settings section. Click the downward arrow () to open the section.
By default, the limit is set to 0 for all reports which means there is no limiter in place.
If you were to add a value of 5 this would mean the report would return only 5 results.
This is a useful tool when paired with the Output Settings Sort. Using the limiter and sort setting can allow you to create Top 5 / Top 10 results if you wish to.
For example, maybe you want to show the top 10 most recent companies to join your organisation.
Displaying your results
Now that we have added Outputs, Filters and Output Settings we are ready to run the report and choose a Report Type to display the data. Typically users will display reports using the Grid or Export option.
The reports module allows for 4 different display options. These being:
- Grid
- Export
- Pivot
- Chart
Grid
The Grid option displays the information on the Report Builder screen in a grid.
Export
The Export option provides the data in an Excel file for you to download. Once you have selected Export the report will run and show an Excel icon on the right-hand side of the screen. Click the Excel icon () to download the report.
Saving your report
When you have run a successful report you can save it for future use. This will save time as you will be able to review your saved reports on the Saved Reports page.
To save your report click the "Save Report" button () on the top right of the Report Builder page.
You can save your report into a folder to keep your reports nice and tidy and easy to access in future. You can add new folder options by clicking the cog icon ().
You are provided with additional options of:
- Private - Ticking this option will make the report private preventing general Rubi Users in your system from running the report.
- Lock Changes - Ticking this option will result in any changes made to the report not being saved.
- Export Only - Ticking this option means you can only export the report to an Excel file.
- Password Protected - Ticking this option means when you export the report it will automatically be password protected.
You can watch and follow along how to run some typical reports here! 📊📈
1.3. Watch how to create typical reports
Getting to grips with writing reports can be tough.
Why not follow along with our video demos to get an idea of how you can begin writing some different reports...
New Members/Customers this month
A standard report you may want to run is finding the members/customers who have joined you in the current month.
Return a chart of different Membership/Customer types to have joined in the current year
You may be interested in reporting on the type of members/customers who have joined your organisation in the current year.
Return contacts with a contact interest of "E-Newsletter"
There may be communications that you wish to send to users as part of a bulk contact. You may only want to send the communication to a targeted group of contacts such as those with the contact interest of "E-Newsletter".
Return a list of records who have a high number of no-shows to events/training courses
You may want to track if there are a high number of records that have been booked onto events or training that haven't shown up regularly. This can be helpful if you offer free events whereby the record has continually booked but not shown up taking valuable spaces.
Return a list of Member News items that have been submitted in the last two weeks waiting for approval
Organisations with web integrations will have members posting items all throughout the week. It can be hard to keep track of what hasn't yet been approved so it's helpful to have a report to add to your dashboard of member items awaiting your approval.
Creating an events dashboard
You may want to have a quick glance at the current active bookings for upcoming events/training courses. This data may best be displayed in a horizontal bar chart.
Once you have created a report make sure to save it for future use here!
1.4. Saved reports
The Reports module allows you to save any reports once you have successfully created and run the report.
Once you have saved your report it will be stored in the "Saved Reports" page of the Reports module.
Reports are filed into folders for easy location. It is always best to have a clear folder logic in your organisation. For example, any membership-related reports being saved in a "Membership" folder or any event-related reports being saved in an "Event / Training" folder.
In the case below we have standard reports saved in an appropriate folder.
Each saved report allows you to do the following actions:
- Edit report settings
- Open the report in the report builder
- Export the report to Excel (if applicable)
- Retrieve a URL to link the report to 3rd party data visualisation software
- Add the report to your Reports module Dashboard
1.5. Dashboard
The Dashboard is the first area you are greeted with when opening the Reports module.
The area is designed for you to add reports that you run regularly so that they are quickly accessible avoiding the need to search through the potentially long list of "Saved Reports".
At the top of the Dashboard will show the reports you have favourited.
Below your favourite reports you will see each report that you have added to your Dashboard from the Saved Reports page. The reports are grouped by the folder they are contained within on the Saved Reports page.
Each tile represents a report that you have added to your Dashboard. The tile contains the report's name and the report's description.
Each tile also contains the following actions:
- Allows you to run the report without the need to open the full Report Builder
- Add the report to the top of the Dashboard as a favourite
- Add the report/chart to every Rubi User's home dashboard to be selectable to show
- Add the report to your RubiCRM home page to be selected to show
- Open the report in Report Builder
- Export the report to Excel (if applicable)
To make reports selectable on the RubiCRM home page please review the related article here.
1.6. Automatically exclude contacts
RubiCRM allows you to automatically exclude contacts who have either "Contact Left" ticked or "Do Not Contact" ticked.
This prevents Rubi Users from having to include these filters each time they create a contact related report.
Rubi System Admins can set RubiCRM to exclude these contacts automatically by updating the appropriate system property. To set RubiCRM to exclude these contacts automatically go to Main Menu > System > Settings > Settings section and search for the system properties:
Commonly Asked Questions
How do I know if my reports are automatically excluding these contacts?
If your report module is automatically excluding these contacts this will be signified with the following two icons above the "Filters" section ().
Can I still include these contacts in reports if RubiCRM is set to exclude them?
Yes, you can still report on these contacts however you must add the filter to include them.
1.7. The Exists filter
Each section includes an "Exists" filter.
By selecting "Yes" for the filter, you ensure that only rows with existing data are displayed.
Use Case 1
One use case may be choosing an output from the Company section and setting the "Exists" filter to "Yes" in the Interactions section. This will yield records that have any sort of interaction recorded.
Conversely, selecting "No" will display records which have not had any interactions.
Use Case 2
Another use case may be choosing an output from the Company section and setting the "Exists" filter to "Yes" in the Bookings section. This will yield records that have any bookings recorded for events/training courses.
Conversely, selecting "No" will display records which do not have any bookings.
Using Exists with dates or other filters
Using the Exists filter in conjunction with time frames or other filters follows a set logic.
Selecting a "No" Exists filter for a subsection (e.g. Booking > General) means any additional filters applied to that subsection will cumulate, and the corresponding data will be excluded from the results.
For instance, if you create a report as shown in the screenshot, the process is as follows:
- Retrieve all company names.
- Identify which companies from the above list made a booking in the previous calendar year.
- Because a "No" Exists filter has been used remove the companies identified in Step 2 from the initial list and present the remaining data.
1.8. Linking reports to 3rd party software
RubiCRM is not a data visualization software nor does it try to be. It is capable of basic data visualization but we leave the advanced areas to the dedicated software.
Software such as Excel and Power BI are dedicated to large data crunching and visualization of data.
You can return the data you require in RubiCRM through a report and link said report to 3rd party software.
Please watch our quick video on how to link your reports.
2. Reports Classic
2.1. Overview
Reports Classic is the older reporting tool for RubiCRM and can be located from Main Menu > Data Management > Reports Classic.
Users can use the reporting tool to generate customised reports based on the data held within your RubiCRM system.
The classic reporting tool allows users to create new reports as well as manage and run saved reports.
2.2. Creating Reports
Users can create reports by selecting the required output columns and filters from the report value list on the left-hand side of the reporting tool.
IMPORTANT: By default the General: ID and General: Name outputs should be added to your report when running reports about Organisations and Contacts. These outputs are required for most reports in order for you to identify which record the data relates to. If you are running a report based solely on Event/Training information, these outputs should be removed.
Users can quickly locate output columns and filters by using the search box located at the top of the list.
Add "Output" columns to your report as needed. Your generated report will then include columns with requested data as per your chosen selections.
Returned data can be pre-filtered within the classic reporting module. Apply filters as required and submit. Your report can include multiple filters. Multiple filters are currently applied on an " AND " basis.
Active filters will be added to the lower part of the page.
When all of the required output columns and filters are in place, users can run the newly created report by selecting ‘RUN’ from the report actions menu, as shown below.
2.3. Saving Reports
Saved reports can be created to enable users to quickly extract data from RubiCRM.
To save a report, ensure that all of the required output columns and filters are in place and then select ‘SAVE’ from the report actions menu, as shown below.
Saved reports can also be used as part of the Bulk Contact tool.
Once selected, a pop-out window will appear to provide users the option to password-protect the report. If the user selects ‘Yes’, all Excel files created for this saved report will be password protected. If the user selects ‘No’, no password protection will be applied to Excel files created for this saved report.
The saved report will now be listed within the Saved Reports grid and can be edited at any time by selecting the ‘Open/Edit’ option as shown below.
2.4. Report Protection
For added security, reports can be locked or unlocked by the report creator or a System Administrator.
Locked reports ensure that other users are unable to run or change the search criteria within the saved report.
Unlocked reports can be accessed by all users (with the appropriate permissions) and changes can be made by other users.
Locked
Locked reports can be locked and unlocked by the report creator or a Rubi System Admin.
Rubi Locked
Rubi locked reports can only be unlocked by contacting the Rubi Support Team at support@rubicrm.com.
2.5. Loading Reports
Saved reports can be quickly loaded by selecting ‘LOAD’ from the report actions menu, as shown below.
Saved reports can also be found in the My Profile section in the top-right hand corner of RubiCRM.
Click the "Open/Edit" icon to edit options about your report, click the "link" icon to run the report and open the results in a browser and click the name of the report to open for editing.
2.6. Cloning Reports
Saved reports can be easily cloned to save time when creating new reports with similar values.
To clone a saved report, select ‘Load’ from the report actions menu and then select the ‘Open/Edit’ option next to the report that you wish to clone.
Once selected, choose the ‘Clone’ option as shown below.
2.7. Removing Reports
Unlocked reports can be removed by any user that has access to the reporting tool. Locked reports can only be removed by the report creator or a System Administrator.
To remove a saved report, select the ‘Open/Edit’ option next to the report that you wish to remove.
Once selected, choose the ‘Remove’ option as shown below.
2.8. Linking to Excel
Saved reports, in RubiCRM, can be linked to an Excel file for accurate and up-to-date data analysis.
Click here for a guide on how to link a saved report to Excel.
2.9. Reports Classic - Hints & Tips
Reports run in RubiCRM will have a maximum of 15000 results returned in a single dataset.
Please note that adding filters will return more refined results lowering the total number of results.
Bookings Reports
If you need info about bookings you must make sure to include an output from the “Bookings” section.
Booking status is always a good one as then you know the status of the booking, or can filter on it.
You can then add in the Contact outputs that you need and any other info including Event Name, Date etc.
Add filters for Event Date and Booking Status as needed.
Main Contacts
There are 2 ways to run a report to vier Main Contacts. If you would like a single row of all records which has a single Main contact per row, use the General: Main Contact Name and General: Main Contact Email.
Running the report without any Outputs or Filters from the "Contact" sections will return a single row per record.
If you would like to run a report for all Main Contacts, choose the Outputs and Filters from the "Contact" sections. Use the filter for "Main" and set to "Yes".
The report will then return all Main contacts for all records.
Bulk Contact Sample Report
When generating a report for use with emailing Contacts, make sure to include the "ContactGUID" as an Output.
This can be combined with any other filters needed to return a specific list of contacts that you would like to contact.
Adding in filters for the Email with a wildcard search for the "@" symbol along with filters for the "Receive Email" setting will prevent contacts who do not wish to be contacted from being returned.
Contacts that are set as "Do Not Contact" or "Contact Left" will be excluded from report results by default.
Events Report
When generating a report about Event/Training Course info only, remove the default Output options that are added and replace them as per the screenshot below.
Set filters as needed to return values.
Do not include any other outputs outside of the Events section as this will require an alternative report (see Bookings Reports above).
We recommend adding a filter for the Event Start Date to restrict the total number of results.
3. Library
3.1. Overview
The Library is a central storage area for your Rubi Users to store and manage files.
Files stored within the Library can be accessed at any time and used as attachments when generating email interactions.
To access the Library, go to Main Menu > Data Management > Library.
To view a video on "Library" click here.
3.2. Adding files
To add new files to the Library, select the "File(s)" option from the Add New menu in the top right-hand corner of the Library, as shown below.
To view a video on "Adding Files" click here.
3.3. Editing/removing files
To edit or remove files within the Library, select the pencil icon option as shown below.
To remove a file click the trash icon.
To view a video on "Editing/Removing Files" click here.
4. Imports
4.1. Overview
Various data import routines are available in your RubiCRM including
- Importing New Records
- Importing Bookings
- Importing Sales/Pipelines
- Importing Bulk Interactions
- Importing essCert/TradeCert
Additional import routines will be added in future updates.
4.2. Import new primary records
Rubi allows you to import data, in bulk, using a standard "Import" Excel file. This file can be downloaded from the section bar by clicking the Excel file.
Complete all applicable columns and rows with appropriate data. For example, all "Company" names should be entered into the "CompanyName" column. Rubi will attempt to prevent duplicate records based on all data in columns A through N being exactly the same. Any deviation of values in these columns will cause a duplicate record to be created with the unique values.
Multiple contacts can copy all data about the "Company" from columns A through N for a matching record, then complete the contacts column data as applicable. Rubi will then detect a match based on columns A through N and will add the additional contact to the existing match record.
Hover over column headers for additional notes regarding how to maintain data integrity. Columns with a blue background require the value to be available in Rubi as a List value prior to import. Lists can be amended in Rubi via Main Menu > System > Lists and look for the appropriate list.
When importing your file, any errors found in the data will prevent the import of the data set and an Excel file will be returned to you outlining any errors. The errors will be highlighted in red.
Please note, that the returned Excel file will be zoomed out so that errors can be quickly identified. The returned Excel file is for information purposes only and amendments should be made in your original Excel import file. Missing list values should be added to Rubi in the appropriate list as mentioned above.
Dates must be in an appropriate format. Invalid dates will cause errors in the import.
Report error resolution process until final import is completed.
Failure to follow the steps above may result in duplicate records existing in your RubiCRM. You do have the option to Merge Records by performing an "Advanced Search" and then selecting the "Merge" button. Please view the related page on merging primary records.
Adding Contacts in Bulk
You can add contacts in bulk to an existing company by uploading the Excel file with the company name and company tel fields exactly matching the company you wish to add the contact to.
Those two fields must be added for each row so RubiCRM can match the contact to the appropriate company record.
You can then freely enter the contact information for each contact you are adding to the CRM.
4.3. Import bookings
RubiCRM allows you to import data, in bulk, using a standard "Import" Excel file. This file can be downloaded from the section bar by clicking the Excel icon.
Complete all applicable columns and rows with appropriate data. RubiCRM will add each row as an independent booking and associate the item to the company Company ID specified for the row. It will also associate the item with the ContactID provided.
Columns of Data Required
EventID - This value indicates the ID of the event the booking will be imported against. Values can be found in the event record.
CompanyID - This value indicates the ID of the primary record (company) the item will be imported against. Values can be found in RubiCRM via a search or from a Rubi Report.
BookingStatus - This value indicates the Booking Status of the booking. This must be the full text value of an Booking Status option you have in the CRM system list 'Booking Status' such as "Active", "Waitlist", "None" etc.
AccountsStatus - This value indicates the Accounts Status of the booking. This must be the full text value of an Accounts Status option you have in the CRM system list 'Accounts Status' such as "To Invoice", "Paid", "None" etc.
PaymentType - This value indicates the Payment Type of the booking. This must be the full text value of an Payment Type option you have in the CRM system list 'Accounts Payment Types' system list such as "Card Payment", "Invoice", "None" etc..
PriceType - This value indicates the Price Type of the booking. This must be the full text value of an Price Type option set up for the event. This could be "Member Rate", "Non Member Rate", "Standard Rate" etc but please ensure you have added the Price Type to the event.
AttendeeFirstName
AttendeeLastName
AttendeeEmail
BookingPrice - This value indicates the price of the booking excluding VAT.
BookingSaving - This value indicates the saving of the booking.
BookingNotes- Any notes which should be associated with the booking.
ContactID - This value indicates the ID of the Contact the item will be imported against. This applies if the contact exists within the CRM already. Values can be found in RubiCRM via a search. We recommend including the ContactID if one exists however the contact will be located using a series of checks as described below.
AttendeeJobTitle
RubiCRM does a number of checks to try and match a contact based on the data you provide per row. RubiCRM does the following checks in order:
- Find the contact based on a match given the CompanyID and Email Address provided.
- Find the contact based on a match given the CompanyID, AttendeeFirstName and AttendeeLastName provided.
- If no matches create the contact in the company as per the CompanyID provided.
- Find the contact based on a match given the CompanyID and Email Address provided now that a contact has been created in step 3.
- Find the contact based on a match given the CompanyID, AttendeeFirstName and AttendeeLastName provided now that a contact has been created in step 3.
If you are not familiar with this feature we recommend trialing the import with a small number of rows to ensure you are happy the insert has concluded as you'd expect.
4.4. Import sale pipeline
RubiCRM allows you to import data, in bulk, using a standard "Import" Excel file.
This file can be downloaded from the section bar by clicking the Excel file.
Complete all applicable columns and rows with appropriate data.
RubiCRM will add each row as an independent sale pipeline item and associate the item to the primary record Company ID specified for the row. It will also associate the item with the Contact ID provided.
Columns of Data Required
CompanyID - This value indicates the ID of the primary record the item will be imported against. Values can be found in RubiCRM via a search or from a report.
ContactID - This value indicates the ID of the Contact the item will be imported against. Values can be found in RubiCRM via a search or from a report.
ContactFirstName
ContactLastName
ContactEmail
Service - This value indicates the Service the item will be created with. Values provided must exist within the Sale Service system list within your RubiCRM system.
PaymentType - This value indicates the Payment Type the item will be created with. Values provided must exist within the Accounts Payment Types system list within your RubiCRM system.
AccountsStatus - This value indicates the Accounts Status the item will be created with. Values provided must exist within the Accounts Status system list within your RubiCRM system.
DateExpected - The date must be in an appropriate format. Invalid dates will cause errors in the import.
ItemQuantity - Number of these items if you wish to log 1 sale pipeline (1 row) for the item but quantify more than one.
If you are not familiar with this feature we recommend trialing the import with a small number of rows to ensure you are happy the insert has concluded as you'd expect.
4.5. Import interactions
Your RubiCRM allows you to import interactions for records.
The RubiBaseInteractions Excel file requires - at a minimum - the CompanyID and ContactGUID for the individual Contact that the interaction should be assigned to.
These values can be obtained either directly from a record in RubiCRM, or (preferred) via a report. Make sure to include the ID and ContactGUID as Output options in your report.
The values you wish to use when bulk creating your interactions should be pasted into the RubiBaseInteractions Excel file and then saved.
The following values can also be supplied via the Excel file:
Service - This is the Interaction Service being assigned to the interaction and must be an exact match to an existing Service in your RubiCRM
Outcome - This is the Interaction Outcome being assigned to the interaction and must be an exact match to an existing Outcome in your RubiCRM
Method - This is the Interaction Method being assigned to the interaction and must be an exact match to an existing Method in your RubiCRM
UserName - This is the name of the user that the interactions will be assigned to the interaction. Must be an exact match to an existing user in your RubiCRM
InteractionDate - This is the date that will be tagged to the interaction when created
Notes - Any short, plain text, notes that will be attached to the interaction.
If your interactions will all be allocated the same options above then leave the columns blank in the Excel file and choose from the section in RubiCRM.
4.6. Import Quest downloads
The Quest Downloads import allows users to export an Excel file from Quest and upload it to the imports area of RubiCRM.
For information on the Quest integration please refer to the related chapter here.