Difference between revisions of "ProDon:Custom Reports"

From Logilys
Jump to: navigation, search
Line 360: Line 360:
 
==== Display  ====
 
==== Display  ====
  
{| border="1" cellspacing="1" cellpadding="1" style="width: 515px; height: 529px;" class="wikitable"
+
{| border="1" cellspacing="1" cellpadding="1" class="wikitable" style="width: 515px; height: 529px;"
 
|-
 
|-
 
|  
 
|  
Line 399: Line 399:
 
|-
 
|-
 
|  
 
|  
'''<span style="color: rgb(255, 0, 0);">G: </span>'''Displays a check box on the report rather than 0 and 1 for the fields that are check boxes. To view an [[ProDon:Custom Reports#Examples_of_Display|example of check box]], see letter '''<span style="color: rgb(255, 0, 0);">G</span>'''.  
+
'''<span style="color: rgb(255, 0, 0);">G: </span>'''Displays a check box on the report rather than 0 and 1 for the fields that are check boxes. To view an [[ProDon:Custom Reports#Examples_of_Display|example of checkbox]], see letter '''<span style="color: rgb(255, 0, 0);">G</span>'''.  
  
 
|-
 
|-
Line 431: Line 431:
 
===== Examples of Display  =====
 
===== Examples of Display  =====
  
{| border="1" cellspacing="1" cellpadding="1" class="wikitable" style="width: 710px; height: 935px;"
+
{| border="1" cellspacing="1" cellpadding="1" style="width: 710px; height: 935px;" class="wikitable"
 
|-
 
|-
 
| colspan="2" | [[Image:Custom Reports 012.png]]<br>
 
| colspan="2" | [[Image:Custom Reports 012.png]]<br>
Line 462: Line 462:
 
|}
 
|}
  
<br>  
+
<br>
  
 
==== Column Calculation  ====
 
==== Column Calculation  ====

Revision as of 15:12, 8 August 2012

Template:Header ProDon


Introduction

The "Custom reports" tool allows you to create reports from a search previously conducted.

Firstly, it is important to know that:

  • It is possible to create a custom report from all the management grids in which the report icon Button Print Report.png is active (blue).
  • A report is always attached to a precise management grid. For instance, if it is created in the "Transaction management", it does not appear in the "Client management".
  • The fields available for the creation of a report are the same as the ones in the corresponding management grid (columns).
  • It is possible to include summaries to the reports only from the "Client management" and "Analysis and history".
  • Each management grid contains lines of specific information (e.g. the clients in the "Client management" grid, the transactions in "Analysis and history") and it is possible to generate an existing report from each of these files. However, it is not possible to create a new one.

Managing the Reports Available

First of all, go in the management associated to the type of report you want to create, modify or delete.

Click on the Button Print Report.png button.

This window opens.

Custom Reports 001.png

A: The Bouton ajouter rapport.png button allows you to create a new report.

B: The Bouton modifier rapport.png button allows you to modify a report.

C: The Bouton supprimer rapport.png button allows you to delete a report.

NOTE: The reports provided by Logilys are not modifiable nor deletable.


Creation of a Custom Report

Go in the management associated to the type of report you want to create.

Conduct a search by criterion using the search bar or a filter, so that the management grid corresponds to what should be found on the report.

Once the management grid corresponds to what should be found on the report, click on the Button Print Report.png button.

To create a new report, click on the Bouton ajouter rapport.png button.

This window opens.

Custom Reports 002.png

A: This section allows you to configure the visual options.

B: The "General options" tab allows you to configure the general options of the custom report.

C: The "Header font" tab allows you to choose the header font of the report.

D: The "Columns" tab allows you to add columns to the report.

E: The "Separations" tab allows you to add separations to the report.


Visual Option Configuration

For all the following examples concerning the visual configuration, refer to the "Examples of Visual Options" section.

Custom Reports 003.png

A: Allows you to name the custom report.

B: Allows you to decide whether the report will be generated in "Portrait" or "Landscape" orientation.

C: Allows you to choose on which paper format the report will be generated (e.g. 8.5 X 11", 8.5 X 14", etc.).

D: This option allows you to align the separations to the left. To view an example of left alignment, see letter B.

E: This option allows you to centre the separations alignment. To view an example of centred separation, see letter G.

F: This option allows you to offset the separations alignment. To view an example of offset separation, see letter I.

G: Allows you to choose the unit of measurement to be used for the width of the report columns.


Custom Reports 004.png

A: Allows you to include separations in the grid. To view an example of separation above the grid, see letter B. To view an example of separation in the grid, see letter G.

B: Displays the header and the logo of the organization on the first page only or on all the report pages. To view an example of header, see letter A.

C: Displays the header of the columns. To view an example of column header, see letter C.

D: Displays the data lines. To view an example of data lines, see letter D.

E: Displays the grid. To view an example of report with a grid, see letter E. To view an example of report without a grid, see letter J.

F: Uncheck this box if Excel sends an error message during the exportation.

G: If the box is checked, the pagination will be on the report. If the box is not checked, the pagination will not be displayed.

H: Activates the grouping option on the report. To view an example of report without the grouping function, see letter F. To view an example of report with the grouping function, see letter H. For more information concerning the grouping option, click here.


Examples of Visual Options

First Example
Custom Reports 005.png

A: It is the header including the logo of the organization.

B: Example of report when the separations are aligned to the left and above the grid.

C: This line is a column header.

D: These lines are the data lines.

E: Example of report when the option "Display grid" is checked.

F: Example of report when the grouping option is not checked. If it had been checked, Mrs. Gwen Burton would have appeared only once and her two donations would be added. See the example in H for a grouped report. For more information concerning the grouping option, click here.


Second Example
Custom Reports 006.png

G: Example of report where the separations are centred and in the grid.

H: Example of report where the grouping option is checked. If it had not been checked, Mrs. Gwen Burton would have appeared twice in the grid, since she has made two donations for the same activity. See the example in F for a report that is not grouped. For more information concerning the grouping option, click here.


Third Example
Custom Reports 007.png

I: Example of report where the separations are offset.

J: Example of report where the option "Display grid" is not checked.


Adding Columns to the Report

To add a column to a report, click on the Button add plus.png button in the "Columns" tab, which is at the bottom of the "Add custom report" window.

Custom Reports 008.png

A: Allows you to add a new column to the report.

B: Allows you to modify an existing column.

C: Allows you to duplicate an existing column.

D: Allows you to delete a column from the report.


After clicking on the Button add plus.png button, this window opens.

Custom Reports 009.png

A: The section "Information on the column" allows you to specify the source of information to use and to configure information as well.

B: The section "Display" allows you to modify the presentation of the column in the report.

C: The section "Column calculation" allows you to perform a mathematical operation in order to enter the result at the bottom of a column (e.g. the donations average by person, the total of donations for an activity, the total of donations for an occasion, etc.).

D: The "Font (Column)" tab allows you to choose the font to be used in the data lines.

The "Font (Header)" tab allows you to choose the font to be used in the header line of the column.

E: The "Button Selection 3 dots.png Summary" button is a shortcut to the "Summary management", if you want to quickly create a summary that will be available at the very bottom of the list of fields in A.

F: The "Remaining width" displays the available space on the report.

NOTE: If the unit of measurement had been defined in centimetres in the visual option configuration, the "Remaining width" would be written in centimetres.


Information on the Column

Custom Reports 010.png

A: Allows you to choose the fields that will be present in the report.

It is also possible to choose, using the drop-down list, a summary. The list of summaries can be found at the very bottom of the drop-down list.

The following options are also available: "Customized value", "Conditional value" and "Calculation function", these are the three first choices of the drop-down list.

B: Allows you to decide on the order in which the information is displayed. In the first box, you have to specify the sorting priority and in the second box, you have to specify whether it is an ascending or descending order.


Example: The sorting must first be in alphabetical order of client (1-ascending) and then in ascending order of UCN (2-ascending), so that:


If two clients have the same name and that one or the other has two transactions to his or her name, both transactions from the same client will automatically follow each other:

    UCN            Client            Amount

- 004 321   Smith, Matthew      $300

- 004 542   Smith, Matthew      $200

- 004542    Smith, Matthew      $150


If the order by UCN has not been determined, the clients could have been mixed up:

    UCN            Client           Amount

- 004 542   Smith, Matthew     $200

- 004 321   Smith, Matthew     $300

- 004 542   Smith, Matthew     $150

C: Allows you to modify the presentation of a piece of data in the column.

The Bouton point interrogation.png button contains the indications on the configuration of format.


Example: The default format of the UCN is "000 000" and the UCN is "001 234". If the format were changed to "#", the UCN would become "1234".


Another example: The date format is "yyyy/mm/dd" and the date entered is "2011/07/28". If the format were changed to "mmmm", the date would become "July".

D: Allows you to choose a mathematical operation whereof the result has to be displayed in the columns of the data lines. This menu is active only if the option "This report is a grouping" is checked in the report main screen.


For more information on the grouping option and on the types of operations available, click here.


Display

For all the following examples concerning display, refer to the "Examples of Display" section.

Custom Reports 011.png

A: Allows you to indicate a reference to be displayed above the column header in order to identify groups of columns. To view an example of group header, see letter A.

B: Allows you to change the name of the column header on the report. By default, the name of the header is the same one as the field's. To view an example of header name different from the field's, see letter B.

C: Allows you to specify the space the column must occupy on the report. The unit of measurement depends on the one that was chosen in the visual option configuration. To view an example of column width, see letter C.

D: Allows you to choose the alignment of the data lines in the column. To view an example of alignment, see letter D.

E: Allows you to enter something in an empty field instead of leaving the field empty. To view an example of empty field, see letter E.

F: Allows you to configure a field without displaying it in the report.


For instance, to display the "Individual" column (that contains the individual's first name and last name) in alphabetical order of last names, you have to add the "Last name" column, and assign it the sorting order. Since the "Individual" column already appears on the report, it is not necessary to see the "Last name" column, so by checking "Invisible", the individuals will be sorted by last names and their flast names will be visible only once.

To view an example of invisible field, see letter F.

G: Displays a check box on the report rather than 0 and 1 for the fields that are check boxes. To view an example of checkbox, see letter G.

H: Allows you to convert a numeric field in duration.


For example: "1.5" would become "1h30".

I: Allows you to remove the "Time" part of a "Date and Time" type of field.


For example: "2015/01/12, 8:53:24" would become "2015/01/12".

J: Allows you to remove the "Date" part of a "Date and Time" type of field.


For example: "2015/01/12, 8:53:24" would become "8:53:24".


Examples of Display
Custom Reports 012.png

A: Group headers named "Identity" and "Donation" were added. So that these four fields are in the same group, you simply have to enter the exact same name as in the "Group header" field when configuring the display.

B: The name of the field in the database was "Co./Org." and the name of the column header has been changed to "Company". To do so, you simply have to modify the "Header" field. By default, the name entered is the field's name.

C: The column width is 0.75 inch.

D: The default alignment of the "UCN" was on the right, and it has been changed to be on the left. To do so, using the drop-down list, you simply have to select the desired alignment.

E: This individual's file did not contain her city, so instead of remaining empty, it is marked as "Unknown".

F: The individuals are placed in alphabetical order of last name, since the "Last name" field has been added, but it is invisible. If the field had not been added, the "Individual" column would have been placed in alphabetical order of first name.

G: The box is checked if the donor is a company. If the "Checkbox" option had not been checked in the display configuration, it would be written "1" for checkbox and "0" for an empty box.


Column Calculation

For the following examples concerning the calculation on column, refer to the "Examples of Column Calculation" section.

Custom Reports 013.png

A: Allows you to choose a mathematical operation in order to enter a result at the bottom of the column (e.g. the average of donations per person, the total of donations for an activity, the total of donations for an occasion, etc.).

NOTE: The mathematical operations available are based on the selected field.

To view an example of column calculation, see letter A.


There already are six types of operations available:

Average: Calculates the average of the existing values (does not consider empty lines).

For example, if the values are: "2", "Empty", "Empty" and "4", the result will be "3".

Number: Counts the number of lines (does not consider empty lines, nor the "0's").

For example, if the values are: "1", "0", "Empty" and "2", the result will be "2".

Maximum: Indicates the greatest existing value.

Minimum: Indicates the smallest existing value (considers empty lines).

For example, if there is an empty line, the result will be "0".

Amount: Adds the values.

Number of cells: Counts the number of lines (considers empty lines). It is the only type of total available on the "Text" type of fields.

CAUTION: Do not confuse these operations with the ones of the grouping option.

B: Displays the list of the separations present in the report  and indicates in which separation the calculation has to be done. For more information concerning separations, click here.

C: Displays or not a result at the end of the report. To view an example of grand total, see letter B.


Example of Column Calculation
Custom Reports 014.png
Custom Reports 015.png

A: This calculation by column is the sum of the donations for the activity "Commitment" of the occasion "Campaign 2011".

B: The grand total is equal to the sum of all the donations for the occasion "Campaign 2011".


Adding Separations to the Report

Adding separations allows you to section the report based on a specific piece of information. The sectioning is done each time the report encounters a new piece of information.

To add a separation to a report, click on the Button add plus.png button in the "Separations" tab that is at the bottom of the "Add custom report" window.

Custom Reports 016.png

A: Allows you to add a new separation to the report.

B: Allows you to modify an existing separation.

C: Allows you to duplicate an existing separation.

D: Allows you to delete a separation from the report.


After clicking on the Button add plus.png button, this window opens. For all the following examples concerning the separations, refer to section "Examples of Separations".

Custom Reports 017.png

A: Allows you to choose a label for the separation. To view an example of named separation, see letter A.

B: Allows you to decide whether the separations will be sorted in ascending or descending order. To view an example of ascending sorting order, see letter B.

C: Allows you to change page between each separation. To view an example of page change between separations, see letter C.

D: Allows you to insert a separation without displaying it in the report. To view an example of invisible separation, see letter D.

E: Allows you not to display the name given in A. To view an example of separation without label, see letter E.

F: Allows you to specify on which field the separation has to be based. To add more than one field, click on the Button add plus.png button on the right. To view an example of separation containing two fields, see letter F.

NOTE: Separations can cause errors if they are misinterpreted. For instance, the "Client" field contains the piece of information "Company name" OR "Last name" AND "First name". So, if two clients have exactly the same last names and first names, they will be grouped because the information of the "Client" field is the same. Therefore, it is wise to separate the clients by "UCN" because it is impossible that two clients have the same "UCN".


Examples of Separations

Custom Reports 018.png

A: The separation label is "Activity".

B: The "Activity" separations are sorted in ascending order.

E: The separation is named "Occasion", but the label is not visible.

Custom Reports 019.png

C: The option "Change a page" has been checked, there is therefore only one activity per page. The second activity can then be found on the second page.

D: A separation per activity was added and the "Invisible" box was checked. This way, the name of the separation does not appear.

Custom Reports 020.png

F: Instead of adding two separations (one "Occasion" separation and one "Activity" separation), only one separation with two fields has been added (the separation label is "Per Occasion and Activity"), which allows the name of the occasion and the name of the activity to be on the same line and in each separation. If two separations had been added, the name of the occasion would have been indicated only once; which means right before the first activity, to view an example, see letters A and E.


Customized Value

A customized value is used to include several fields in the same column.

For instance, instead of displaying the "Address", "City", "Province" and "Postal code" fields on four columns, it is possible to display these four pieces of information in a configurable value.

Custom Reports 021.png

A: In the "Field" drop-down list, select "*Customized value".

B: After selecting the "*Customized value" field, the Bouton configurer.png button allowing you to customize the field, appears.


By clicking on the Bouton configurer.png button, this window opens.

Custom Reports 022.png

C: Contains the list of fields that can be used in the "Customized value". Simply double-click on the field and it will be added in D.

D: Displays the information that will be in the column. It is possible to type directly in this box to add text in between the fields to be used. In this example, "Enter's" were added. To view the result, click here.


Example of Customized Value

Custom Reports 023.png


Conditional Value

A conditional value displays a piece of information based on the response to a given condition.

For instance, if a client is anonymous, the mention "Anonymous" must appear in the column, otherwise the donor's name must be entered.

Custom Reports 024.png

A: In the "Field" drop-down list, select "*Conditional value".

B: After selecting the field, the "Column type choice" window appears. Choose the column type associated to the fields that will be selected in C.

C: After choosing the field, the Bouton configurer.png button allowing you to configure the field, appears.

NOTE: The column type will have an impact on the possible result of the condition, as well as on the options available for this column. For example, it is impossible to record an amount on a "Text" type.


Custom Reports 025.png

D: To configure the condition, double-click in the blank box under "Condition".

E: To configure the result to be displayed when the condition in D is true, click in the blank box unber "Result".

F: It is possible to add or remove conditions using these two buttons.


After double-clicking in the blank box under "Condition", this window opens.

Custom Reports 026.png

A: Contains the list of fields that can be used to define the condition. To insert a field in the condition, simply double-click on the one to insert and it will be added in C.

B: Allows you to insert operations in the condition.

C: Displays the condition. It is possible to manually add information, such as the "1" in this example.

NOTE: To know what the buttons mean, point the mouse over the desired button.

NOTE: The "1" means that the "Anonymous" box must be checked. If the field had been equal to "0", the condition would have been that the "Anonymous" box must not be checked.


After double-clicking in the blank box under "Result", this window opens.

Custom Reports 027.png

A: Contains the list of fields that can be used to define the result of the condition. To insert a field in the result, simply double-click on the one to insert et it will be added in C.

B: Allows you to insert operations in the result.

C: Displays the result. It is possible to add fields or to type something, using the keyboard. In this example, if the condition is true, "Anonymous" will be entered.

NOTE: To write something, using the keyboard, you must always enter it in between apostrophes. It is also possible to leave the field empty if the condition is true. Simply type two apostrophes (not a quote).


Custom Reports 028.png

It is possible to add as many conditions as desired. Moreover, if the last condition is empty, its result will be applied to all the situations not corresponding to one of the previous conditions. In this example, if the "Anonymous" box is checked, the mention "Anonymous" will be entered in the report. In all other cases, the donor's name will be entered. To view the result of this conditional value, click here.


Example of Report with a Conditional Value

Custom Reports 029.png

In this example, the conditional value was named "Donor".


Calculation Function

A "Calculation function" is a mathematical operation allowing you to obtain a precise result.

For instance, it is possible to take the "Amount" field and to multiply it by 0.10 to obtain a column that displays 10% of the value of the amounts of the transaction.

Custom Reports 030.png

A: In the "Field" drop-down list, select "*Calculation function".

B: After selecting the "*Calculation function" field, the Button edit calculation function.png button allowing you to configure the field, appears.


Custom Reports 031.png

C: Contains the list of fields that can be used for the "Calculation function" field. Simply double-click on the field and it will automatically be added in E.

D: Allows you to insert operations to the formula.

E: Displays the calculation formula. It is possible to manually add text in this box, by using the keyboard.


Example of the "Calculation Function" Field

Custom Reports 032.png

In this example, the "Calculation function" field was named "10% of the Donation".


Using the Option "This report is a grouping"

This option activates when configuring the visual options and displays several lines of the grid in one single line in the report. In order to be grouped, the lines must contain an identical piece of information in all the columns used in the report, except for the columns on which a grouping operation is performed.

There are six different types of operations available:

Average: Calculates the average of the existing values (does not consider empty lines).

For example, if the values are: "2", "Empty", "Empty" and "4", the result will be "3".

Number: Counts the number of lines (does not consider empty lines).

For example, if the values are: "1", "0", "Empty" and "2", the result will be "3".

Maximum: Indicates the greatest existing value.

Minimum: Indicates the smallest existing value (does not consider empty lines).

Amount: Adds the values.

Number of distinct values: Calculates the number of different values (considers empty lines).

For example, if the values are: "1", "Empty", "1" and "3", the result will be "3".

CAUTION: Do not confuse these operations with the ones of the "Column Calculation" section.


Example of an Onscreen Search

Custom Reports 033.png

A manual count allows you to find:

  • 35 transactions made
  • 24 different clients
  • 1 occasion


Result of the Onscreen Search

Custom Reports 034.png

In this report, the columns "UCN", "Individual"' "Occasion" and "Amount" are displayed. Furthermore, a grouping operation was made on the "Amount" field.




Document name: ProDon:Custom Reports