Advanced custom reporting features – Workamajig

Workamajig offers a very robust and customizable reporting system. There are three types of reports that we call listings, standard reports, and custom reports. This guide will tell you about the different ways you can customize many of the listings and reports.

Report types

Views

These are screens such as the views with any of the Today pages, such as Projects or Client Invoices. Each of these screens comes with pre-defined views. If you have the right to modify and memorize reports, then do be aware that any changes you make here, will be seen by all other users who access these views.

Standard reports

These are found by going to the Menu > Everyone > Report Center > Standard. In the different folders, you will see reports with a clipboard icon that has a white top. These are our standard reports and many of them are not customizable since they touch multiple areas of data and have detailed calculations that take place within the report. But do offer a wide range of filters when running the report.

Custom reports

Custom reports are broken out by what we call Datasets, and access to each dataset can be controlled by security group rights. Custom reports can be accessed by going to Menu > Everyone > Report Center > Custom. This will open a new screen with the dataset you have access to. Once you create a custom report, it will show in the Other section by default and have a clipboard icon with a black top.

NOTE: Datasets do not follow the individual rights established in the security group settings.

Edit

The button called Edit is at the top of the report next to the print button. This is how you access the layout of the report.

You can modify a standard listing or create a new listing option by selecting the Modify button on the screen. You can then change the name of the listing option to a more descriptive name. When modifying a custom listing option, there are up to four separate tabs for the layout, columns, grouping, filter, and advanced.

When you customize a listing option, you can change the field columns in the list, the sort order, subtotaling options, and the conditions used to search for records. You can also control which groups of users can use that listing option or make that listing option private to your login.

Columns

This is where you can adjust the field columns, add calculated columns, and set locked columns.

Available Columns: The Columns tab allows you to customize the fields that show up in the custom listing option. When you select the Columns tab, the list of available fields can be selected and moved to the selected side of the screen and ordered the way you need to see the field columns.

Add a Calculated Column: This allows the user to name a field column that calculates an amount based on other available numeric fields in that listing option.

Locked Columns: This option allows the user to specify the number of field columns from the left side that can be locked so that when users scroll to the right, those field columns stay visible on the screen.

Use Advanced Grouping Options: (only available in Custom Reports) Unchecked by default. This column allows you to bring "like rows" together into a single line by way of the Group By settings.
NOTE: When using this feature, all of the underlying information is still there, you are deciding to create a "displayed" version of the information.

Column: Select the column you want to display from the Available columns list. The display order is top is the first/left column when displayed/printed.

Caption: Enter a descriptive name for the field. This is the name that will appear at the top of the listing. You may want to shorten the name to fit better if the display width is not very wide. The name here can be useful if you are exporting the data from Workamajig into another application for ease of importing on that side.

Word Wrap: If this is checked, then the values in that field will make the row expand to show all of the data entered.

Print Width: Defines the width of the column when printing. This defaults to 1 inch. This roughly corresponds to the display view. The width is measured in inches. The system will approximate an inch on the web page as 72 pixels per inch (i.e. The system sets the width of the column to 72 * number of inches on the web page).

Sort: Drop-down box. This allows you to define whether the sort for the column will be in ascending or descending order.

Sort Order: Set to O by default. This defines which column will be used to sort by and in what order. If all are set to O, the top/first column will be used and is based upon the alpha-numeric sort pattern. You do not have to select a sorting option for each field. You also have the option to re-sort the list while viewing it by clicking on a column heading. If you have added groups to the report, the list will always sort first by the report groups and then by the other fields selected to sort on. You can select more than one field to sort the list.

Group By: Only available in custom reports (this excludes listings and many standard reports). Must check Use Advanced Grouping Options as described above.

Group: Will allow a single row per selected column. This is typically used for grouping all users or project info into one row. This should not be used with a number field
Sum: Most common use of this feature. This will sum up the "number" column. This should not be used for top-level numbers that may already be summed
Average: Will average the number column
Count: Will count the number of entries. Useful for counting the number of projects, etc.
Maximum: Will display the highest amount from all entries in a number column
Minimum: Will display the lowest amount from all entries in a number column

Groupings

The Grouping tab allows users to set the fields to group or subtotal by. A common use of the grouping option would be to group projects by client name. When you group by a field, the system will automatically sort on that field first. You can create as many sub-groups as you want on a listing option.

Available Columns: This is usually the same list that is shown for the columns. And allows you to choose what variable to group by.

Add a Calculated Group: This allows you to create one grouping of labeled data. It helps reduce clutter stair-stepping) if you have multiple groups with the same end-line result.

For example, you can combine two fields as one, such as client name and billing contact:

[Client Name]+# - #+[Client Billing Contact]

Which will produce an end result of "Client Name - Contact Name".
Or another example where you get a bit fancier with the result:

#Client = #+[Client Name]+# / Contact = #+[Client Billing Contact]

Which will produce an end result of "Client = Client Name / Contact = Contact Name".
The ## symbols allow you to enter data in between them that the system will ignore and display as plain text.

Automatically Expand Groups: The Automatically Expand Groups option when checked will always expand the listing options when the listing is using grouping fields.

Column: Shows the name of the actual field.

Caption: This shows the name that will be at the top of the report for that data. Typically not visible in the report itself, whereas the column's caption above is.

Subtotal: The subtotal option is ignored for non-numeric fields and can only subtotal numeric fields in that listing option. Grouping fields can also be sub-totaled. You may group a listing by client name and project type, but may only want sub-totals by client name, and not by project type, for example.

Page Break: If this is checked, then the PDF you create from this report will start a new page each time the data comes across another grouping of that data.

Color: The Color option for each grouping field can be set to show the grouping rows in a different color.

Filters

This is where you can change, remove and add 'Search Options' to the listing/report. You can add as many different conditions to a listing as you want. You can either enter the values onto the report or you can set up the condition to prompt the user on the listing screen.

Available Columns: Typically the same list from your columns tab. Allows you to bring over a column by which to filter your result.

Show Advanced Options: This adds the brackets "(((" and ")))" options into play. Think of these like algebra when using them.

(((: You have three levels deep of using brackets. These allow you to group a set of options together. Especially useful when using the "or" segment.

Column: Name of the actual field being used.

Condition: Determines how the field is to be filtered. Depending on the type of data (i.e. Date, Numeric, Text) you will see a limited set of the following variables:

Equal To
Not Equal To
Equal To My Name
Between
Not Between
Any Of
Greater Than (Numeric Specific)
Less Than (Numeric Specific)
Greater Than or Equal To (Numeric Specific)
Less Than or Equal To (Numeric Specific)
Begins With
Does Not Begin With
Ends With
Does Not End With
Contains
Does Not Contain
Is Blank
Is Not Blank
Is After (Date Specific)
Is Before (Date Specific)
Is On or After (Date Specific)
Is On or Before (Date Specific)
Is On or After Today (Date Specific)
Is On or Before Today (Date Specific)
Month To Date (Date Specific)
Year To Date (Date Specific)
Today (Date Specific)
Yesterday (Date Specific)
Last Week (Date Specific)

Value 1: If the filter does not have the Prompt checked, then you typically need to supply a value here. This is a great way to have the report pre-filtered so you do not have to enter the value in the Search Options.

Value 2: Rarely needed. For example, it works best if you are using the condition of Contains, and Value 1 is "This" and Value 2 is "That". Then only results where This and That are somewhere in that same field will be shown.

Prompt: If checked, this will make it a user-receptive filter on the front side of the reports Search Options. You must also supply a Prompt Label.

Prompt Label: If Prompt is checked, then you need to label the filter. Typically it will be the same as the Column name, but you can make this suit your need or give a brief description of the filter.

))): Used for the closing of the bracketed data.

and/or: Allows for numerous ways to filter your reports. The thing to note is that when a report is run, it goes through the filters, starting from the top down. It's easy to get messed up with your filters here. So feel free to experiment with the filters until it works the way you want.

NOTES:

Based on the type of Condition you select, you may only be able to enter values for one, both, or none.
The condition of "contains" tends to be the best for doing more general searches vs. having to know the exact spelling of the value.
Select the Prompt Label to allow the user to enter a value prior to running the search. This is useful if you want someone to be able to search by a specific value. Rather than have them edit the custom listing each time, you can have them enter the criteria on the listing page. If you select this option, you must enter a prompt text. This is the text that they will see on the listing page telling them what kind of criteria to enter. If you were allowing them to search on Project Number, then the logical prompt text would be "Project Number", for example.
If you want to create a more sophisticated search that may require additional criteria such as OR statements and bracketing of conditions, then advanced options allow you to create complex Where clause options that create a SQL Where condition. If you are not familiar with SQL, contact support@workamajig.com for assistance. Most situations will not require using advanced conditions.

Advanced

The Options tab contains many of the settings for printing and security.

Title: In the Title text box, enter a descriptive name for this listing option. In the Printed Sub Title text box, enter a subtitle that you would like to appear directly below the printed title. You can select alignment options using the drop-down box to the right of the printed subtitle text box.

Title Alignment: In the Print Orientation drop-down box, you can select the orientation of the printed PDF file.

Align Left, Align Center, or Align Right.

Sub Title:

Sub Title Alignment:

Align Left, Align Center, or Align Right.

Orientation: Determines who the report should print to PDF. While the margins are not customizable, you can set the Print Width of the Columns to fit within either 7.5 or 10-inch widths respectively.

Portrait or Landscape.

Show Search Conditions: If this is checked, then the filters used for this report will be displayed at the top of the PDF printout. This is most useful to replay what the data being shown is in regards to.

Data Set: This shows what custom report dataset this was based on.

Display in Group: Custom Reports Only. Allows you to choose what Report Group you want this custom report to show under.

Owner: Designates who is the main user of this report. Defaults to the person initially creating the report.

Private: If this is checked, then only the Owner and any Administrator of the system can see and access this report.

Visible To: Select the Security Groups (all users within those groups) that you would like to be able to view this custom listing. By default, all of the security groups are given access to view the listing.

Saving

The button called Apply in the upper right comer of the report is how you save your changes.

Once clicked, a drop-down appears with the following options:

Memorize

This will overwrite the custom report of the listing you have open with the changes you have applied.

Copy

From the Edit screen, you can click More and Copy View.

Add a calculated column

From the columns tab, there is an option called Calculated Column. From here you can create a simple (1+1) calculation.

NOTE: You cannot make a calculation of a calculation, meaning the calculations run off the results of the report and do not calculate as part of the report for which another calculation can be made from its results. Also, note that line percentage calculations DO NOT subtotal properly at the grouping level. For more complex calculations like these, you can export the data to Excel by clicking the Excel button in the upper left of the screen, then make your complex calculations there.

Creating the calculated column

When you modify a report or listing, there is a tab called Columns. From this section of the layout, there is a button called Add A Calculated Column. Upon clicking this button, you will need to choose a Caption, Data Type, and the desired calculation. On the left will be a list of available columns that you can bring over to the calculation area.

Data types

Depending on what kind of calculation you are doing, you have the option to choose a specific Data Type. This will determine how the field will behave in reports. The most commonly used is Numeric and is the default when you create a new calculated column.

Simple calculations

If you are simply trying to get a total of two or more available fields, you can bring over the first field into the calculation area, then just add a + after the field. Now bring over the other field(s).

You should get something similar to this:

[Total Labor] + [Total Expenses]

There is no need to add an equal sign after this as it will calculate just how you placed them. Also, you can use (rounded brackets) around the [square brackets] to calculate other data first, similar to algebra.

For example:

[Total Gross] / ([Total Labor] + [Total Expenses])

If you are looking to do a more advanced calculation, please contact support@workamajig.com.

Example custom report

I want to see how many hours I worked on each project and using what service. I will create a custom report using Menu > Reports > select any group: top right corner click New Custom Report > Traffic > Time Detail Data.

Add the columns: Project Full Name, Service Description and Actual Hours Worked
Click on the Grouping tab

Add User Full Name.
Click on Filter.

Add User Full Name and change the Condition to Equal to My Name.
Click Apply.
Click Screen.

You will see every time entry I have made in Workamajig.
Now we will use the Advance Grouping Options and the Grouping tab to see if I can get a better-looking report.
Click Modify.

From the Columns tab, check "Use Advanced Grouping Options". Change the Actual Hours Worked column to Group By: SUM.
Now to get a total of all hours and add color to the group, to make it easier to see.
Click on the Grouping tab.

Check the Subtotal box and click into the Color box and select yellow.
Click Apply.
Click Screen.

We should see that it did group and provide a total for each Service Description. But we still see that the Project Full Name is repeated. This is because the grouping function is looking to group like things. The Project Full Name is the same, but the Service Description is different, so Workamajig is only able to display groupings to that level.
To have the project show up only once would be better. So we have a choice. We can remove the Service Description column, this will allow the Project number to group into 1 row. Or we can use the Grouping tab to group by User Full Name and Project Full Name. Since we want to see the Service Descriptions, for this we will use the later choice.
Click on Modify.
Click on the Columns tab.

Highlight the Project Full Name line and clicked the icon to remove the line.
Click on the Grouping tab.

Add the column for Project Full Name, check the Subtotal checkbox and add color.
Click on Apply.
Click on Screen.

There we go. We now have a report that provides me with the total work done. We can also see how much was done on each project and what service the time was put towards.

I will now click Memorize > New Report to save this report for future use.

Search options/use folders

The search options are based on what filters are in use for this listing/report.

Some listings also have the ability to use folders. These are for the contact management section only; such as leads, companies, contacts, and opportunities. These are enabled in System Setup and are only needed if you want further control of what records users have access to, beyond what is capable with security group rights.

Delete a custom report or listing

In order to delete a custom report or listing, you must have the security right to modify custom reports and or custom listings.

1) Click Modify

2) Click Delete Layout/Report

3) Confirm Deletion

By doing this, you are not deleting any data from the system. You are only removing a report.

NOTE: There is no Undo. You will need to recreate the report/listing if it was inadvertently deleted.

Export a custom report or view

Please refer to the Importing custom reports for more info on exporting and importing custom reports.

Articles in this section