Merge columns

ClosedGetting Started
Who Can Use It? Where Is It?
  • User
  • Assistant Project Manager
  • Project manager
  • Director
  • Administrator
  • System Administrator
    		•

    Reports > Columns tab

    Merge Columns

    Advanced reporting feature

    When you select the Columns tab when editing a report, the Merge fx{} column is shown. Here explore the merge options shown in this drop down list and when you would apply them.

    Merge fx {}

    What does this setting do?

    Use it with

    Sum

    Sums numeric values in the column.

    Numerical columns

    Average

    Displays an average of the grouped items.

    Numerical columns

    First

    Displays the first item in the group, determined by sorting.

    Numeric or Text columns

    Last

    Displays the last item in the group, determined by sorting.

    		 Numeric or Text columns

    Max

    Displays the largest item in group.

    		 Numerical columns

    Min

    Displays the smallest item in group.

    		 Numerical columns

    Concat

    Combines the values (as text) with a line in between. Join the duplicate data together so the information is not repeated.

    Numeric or Text columns

    Count

    Count the number of rows that were merged

    		 Numeric or Text columns

     

    Tips:

     

    Concat merge option

    ExampleClosedA report exists Owing Invoices showing Debtor Notes. This report contains a Merge column setting of Concat which is applied to the Debtor Note column and is important so that if an invoice has more than 1 debtor note, the invoice details won't be displayed a duplicate number of times.

     

    ClosedOption 1: Without Concat applied to Debtor Note column.

    Notice the invoice details and Owed w Tax ($) are duplicated

    ClosedOption 2: With Concat applied to Debtor Note column

    'First' merge option

    The following is a simple example, to give you an overview of how the 'First' merge option works.  

    ClosedExample overview

    1. The following is an example of the Transaction Summary Report - however all columns were removed and the 5 columns you see have been added.

    2. You will notice the Project Fee Total is displayed as $196,000 and you'll notice the Project fee appears several times down the page - once for each transaction on the sub project. This is also the case with Sub Project Fee.  

    the Sub Project totals are incorrect

    ClosedExample

    Below are the steps to produce a report that doesn't duplicate the Project or Sub project values.

    1. Select the Sorting and Grouping tab.

    2. Select Merge duplicate rows. At this stage you will start to see a change in the report layout - though the figures still won't reflect the correct Project and Sub Project values.

    1. Select the Modify the report layout and filters button.

    2. Select the Columns tab on the report.

    3. Click into the Merge fx{} cell against Project Fee ($).

    4. Select First.

    5. This will display the First fee value in the list ONLY - it will not include the Project Fee in the calculation for each transaction line - just the 'First' one.

    6. Then repeat this also for the Sub Project Fee ($).

    Tip: The above example can be used also for the Merge options - Last, Min and Max.

    Average Merge FX {} option

    Apply the Average Merge FX option, when using the 'merge duplicate rows' feature. The average of a numerical data column can be shown, to average what was grouped together when the 'merge duplicate rows' was applied to the report. The average shown for the group is calculated as the (total number of items in the group / total sum for the group). Use the average merge fx option to compact the results shown, to create a summary view of the report.NEW 4.8

    ClosedExample

    In this example we have used the 'Transaction WIP Report' to display a list of timesheet transactions for a selected Project. In the image below you can see that the Transaction Task is duplicated on several entries. The column labelled SUM is displaying the 'transaction cost'.

    Create a summary view of the transactions for this project, which also displays the Cost Average:

    1. Open the report you want to update.
    2. Go to the Sorting and Grouping tab , and check the 'merge duplicate rows' box.
    3. Go to the Columns tab. Add an extra cost column such as 'Cost Standard $' to apply the average too. Double click the column to add it.
    4. Re-order the added column if required.
    5. Re-label the added column heading to contain the word 'Average'.
    6. In the 'merge fx {}' column select 'Average' from the list.
    7. The report is ready to run to see results showing the average cost, and the summed cost for each 'transaction task' group. This is shown in the image below.

    Tip: The averaged column totals shown are incorrect. The totals shown are a SUM of the Averaged details shown within the column. Please be aware of this is when displaying report totals.

    Does Hide Duplicates do the same thing as Merge FX {}?

    Let's use the Owing Invoices showing Debtor Notes Report as an example, and use Hide Duplicates method. Hide Duplicates will not display the same data from the previous line if it is duplicated, where as merge fx will group the duplicated data together in a single row.

    ClosedExample

    Want to learn more?

    Looking for more help? Try reviewing the following topics: Concept Link IconView Topics

     

    © 2019 Total Synergy Pty Ltd

     

      

    Open topic with navigation