From the Blue Application Bar:

 

If you click on Reports, the Reports dashboard will open:

Here you can view a summary of information available:

• My Queries (will list the names of the Queries below)
• Default Queries (will list the names of the Queries below)
• My Reports (will list the names of the Queries below)
• My Charts (will list the names of the Queries below)

 

You can show the Queries by clicking on My Queries name or Default Queries name or hide them by clicking the names again.

 

Clicking on Accounting Report from the Reports module will open the Accounting Report dashboard:

 

A new accounting report can be created using the Report Generator box at the top of the page:

 

• Select Type (your options are Invoice, Payment, Refund, Refund Payment. If nothing is selected and you click Generate all will be selected by default)
• Enter Start Date
• Enter End Date
• Choose Options (click box beside Exclude exported transactions to exclude previously exported transactions)

 

You can select Mark for Export, which will export either all or selected items to QuickBooks. If you only want to import certain items from the list then you can select them by clicking on the box in front of the individual name and then select Mark for Export. A window will open asking you to confirm that you want to import the selected rows to QuickBooks. When generating the report the next time you can click on the box beside Options to exclude the previously exported invoices.

 

Clicking on the green  from the Accounting Report dashboard will open the Build New Report window.

 

You will select the desired items from each section, each row is a different selection that can be added to your report. You can add multiple items from each section or select only one.

Contacts - you can select from the following:

 

• Include contacts. Then click the Green arrow to move this over to report view
• Select from the drop down - First Name, Last Name, Email, Address, City, Province, Postal, Country, or Company. Select from the next drop down -   Start with, Does not start with, Ends with, Does not end with, Contains, Does not Contain, Exactly, Not exactly. Enter word or symbol freehand (in example below used @ sign) Once you have made your selections click the Green arrow to move this over to report view
• Satisfaction Rating – select from the drop down - No more than, No less than, Exactly, Not exactly. Then select the rating number 1, 2, 3, 4, 5. Once you have made your selections click the Green arrow to move this over to report view
• Include Address Type – select from the drop down - None, Office, Primary, Home, Shipping, or Billing. Once you have made your selections click the Green arrow to move this over to report view

 

 Groups - you can select from the following:

 

• Select from the drop down – Currently in, Currently not in. Select from the second drop down - the selection here would be based on your groups created under contacts. . Once you have made your selections click the Green arrow to move this over to report view

 

Transactions – you can select from the following:

• Select from the drop down – Purchased, Did Not Purchase. Select from the second drop down – the information within this area is based on what you create under items in the Accounting section. Once you have made your selections click the Green arrow to move this over to report view
• Single Transaction – select from the drop down – Amount more than, Amount less than, Amount exactly. Enter in a dollar amount. Once you have made your selections click the Green arrow to move this over to report view
• Transaction Sum – select from the drop down – Amount more than, Amount less than, Amount exactly. Enter in a dollar amount. Once you have made your selections click the Green arrow to move this over to report view.
• Make a transaction … select from Today, Yesterday, Between [YYYY-MM-DD] and [YYYY-MM-DD]. Once you have made your selections click the Green arrow to move this over to report view

 

Forms – you can select from the following:

• Select from the drop down – Filled out, Did not fill out. Select from the second drop down – The information within this area is based on what you create under Web forms.

 

Once you have made your selections from the above options you will see a list of items under Report View.

 

 

Now you are ready to save you report you can:

• Save
• Save As
• View Results

 

You can delete or reset

• Reset – select reset to start a new search
• Delete  - Select Delete to remove your saved search

 

When you select Save, Save As or View Results a new screen will open. This screen will show the results or your report and you will have the option from here as well to Save, Save As. You will also be able to Export As CSV, Export for Mailing, Create Chart. You can also go Back or select Delete.

 

Clicking on ‘Event Report’ in the left side-menu of the Reports module will open the Event Reports page.

 

The report can be configured using the options in the Configure Report box at the top of the page. The options are:

• Events: three events can be chosen for side-by-side comparison.
• Get Registrations From: Selecting ‘today only’ will select only registrations from the current day. Selecting ‘days back’ and entering a value X will select all registrations that took place on or before the date X days back from the start date. Therefore, if the event took place on February 18^th and you select 14 days back, the report will return all registrations that took place on or before February 4^th.
• Query Type: Selecting ‘By membership’ or ‘by payment type’ will group the report results by the current membership status of the registrants or by the payment packages chosen, respectively.

 

Once you have selected your report options, click on ‘generate’ to generate an event report. The three selected events will appear as three columns, with a breakdown of results by category followed by a total at the bottom of the page.

Once a report has been generated, it can be printed using the ‘Print’ button in the top-left corner of the results area.

 

When you select Custom Queries the following screen will open.

To create a new query, click on the New Query icon 

 

When you open the “New Query” page, you will see the following screen.

 

 

The table is where you can create the parameters of your search. At this point, the table will be empty. The textbox is where advanced users can manually enter/edit a search query using custom SQL.

Give a name to your search by typing in box beside Query Name at the top of the page and click on  button. Newly created searches will appear in the list of saved searches on the main Custom Queries page.

To add a search parameter, you first need to select a database table from the Table drop down. The next step is to choose a column from this table. You can then select the operator you want to use to filter that column.

Note:  The Column value must be added to successfully add the Search Parameter to the report.

Note:  You can use “%” as a wild card to substitute one or more characters in the value field. For example, you can type in “%iz-Z%” (without quotes) to match “Biz-Zone”.

When you have completed all entries you can then select . Once it is saved, you can access it from the main “Custom Queries” page.

 

Once you have created a custom query, select it on the Custom Queries page to review and edit the query.

 

If you select “DNA_ADNA_Company” another screen will open; from here you can select only certain items to show in your custom Query.

For each row that you enter a table for a new DNA_ADNA tab will open that can be adjusted.

 

The following is a guide to the various operators that can be used when creating queries.

Like	Returns records from the selected Table where the value in the specified Column Matches the Pattern (Value). For example, to find contacts whose first name starts with a ‘Ab’, set: Table Contacts Column First Name Operator Like Value Ab%
Not Like	Returns records from the selected Table where the value in the specified Column Does NOT Match the Pattern (Value). For example, to find contacts whose first name DOES NOT start with a ‘Ab’, set: Table Contacts Column First Name Operator Not Like Value Ab%
=	Returns records from the selected Table where the value in the specified Column Is Equal to the queried value. For example, to find members who joined on 2007-01-01, set: Table Membership Column Join date Operator = Value 2007-01-01
!=	Returns records from the selected Table where the value in the specified column Is NOT Equal to the queried value. For example, to find contacts who does not work in Calgary, set: Table Contacts Column City Operator != Value Calgary
<	Returns records from the selected Table where the value in the specified Column is Less than the queried value. For example, to find members who joined before 2007-01-01, set: Table Membership Column Join Date Operator <  Value 2007-01-01
<=	Returns records from the selected Table where the value in the specified Column is Less than or Equal to the queried value. For example, to find members who joined on or before 2007-01-01, set: Table Membership Column Join Date Operator <= Value 2007-01-01
>	Returns records from the selected Table where the value in the specified Column is Greater than the queried value. For example, to find members who joined after 2007-01-01, set: Table Membership Column Join Date Operator >  Value 2007-01-01
>=	Returns records from the selected Table where the value in the specified Column is Greater Than or Equal to the queried value. For example, to find members who joined on or after 2007-01-01, set: Table Membership Column Join Date Operator >= Value 2007-01-01
IS NULL	Returns records from the selected Table where the value in the specified Column IS NULL. In this case there is no value so leave this field blank. For example, to find members who do not have a set expiry date, set: Table Membership Column Join Date Operator IS NULL Value
IS NOT NULL	Returns records from the selected Table where the value in the specified Column IS NOT NULL. In this case there is no value so leave this field blank. For example, to find members who have a set expiry date, set: Table Membership Column Join Date Operator IS NOT NULL Value
REGEXP	A regular expression (also "RegExp" or "regexp") is a string that is used to describe or match a set of strings according to certain syntax rules. The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message.
IN	Returns records from the selected Table where the value in the specified Column IS IN a list of values. The items in this list should be separated by a comma, and placed in quotation marks individually. For example, to find members who are in Toronto or Calgary,  set: Table Contacts Column City Operator IN Value “Toronto”, “Calgary”
NOT IN	Returns records from the selected Table where the value in the specified Column is NOT IN a list of values. The items in this list should be separated by a comma, and placed in quotation marks individually. For example, to find members who not in Toronto or Calgary,  set: Table Contacts Column City Operator NOT IN Value “Toronto”, “Calgary”

                                                                                                                                                                               

Clicking on ‘Quick Report’ from the Reports module will open the Quick Report page.

 

From here, you can quickly view certain pre-generated reports. Click on a report’s name to view its details.

 

Change Log

Date	DNA Version	Change	DNA Area	Changed by
June 18, 2018	V1.6.1 onward	Added new due date information Sample invoice AR Aging Report	Money > Invoices, AR Aging Report	Julie King

 

Back