How can I run a report to showing how much we spend on each animal?

 
1
0
-1

I would like to run a report showing how much we spend on each animal.  I see that the question was asked back in 2018 and I am wondering if there have been any updates to the system per the request.  Also, I am having difficulty with the way they are doing it.  It will list each journal entry for each dog.  I would like to see the total expense for the animal.  Is there currently a way to do this?  

    CommentAdd your comment...

    1 answer

    1.  
      1
      0
      -1

      The post from 2018 is a partial solution. The data table functionality built into RG doesn't allow for grouping, which is why you cannot generate a total for each animal. But, there is a simple solution. I've attached screenshots for reference.

      Part 1: Create the data table view

      1. As the other post mentioned, create a custom data table view
      2. Add the fields Animal ID, Animal (Name), Cost. These are necessary for later
      3. Add any other fields that are relevant to you
      4. You then have the option to add filters to exclude records you don't need. For example, you can filter based on the Cost field to only show Animal ID records that have a cost associated with them: Greater than 0. You can also filter by date to exclude any records before or after a specific date
      5. Just above the data view records there is an Options select list. Choose Export to CSV and wait for it to download

      Part 2: Import into Google Sheets or Excel

      This is the part where you will group records and show a total cost for every animal.

      1. Import the CSV into Google Drive (which will create a Google Sheet) or open in Excel
      2. Make sure the fields Animal ID, Animal (Name), and Cost are in columns A-C
      3. Select all data in those three columns by clicking on "A" then hold select and click on "C"
      4. From the top menu (in Sheets) click on Insert > Pivot Table
      5. Select insert into new sheet. This will create a worksheet in the same spreadsheet (you'll see the tab at the bottom of the spreadsheet
      6. Go to the pivot table worksheet
      7. For Rows, select Animal ID first. Then add a row (below Animal ID) for Animal (Name). You can keep both Show Totals checkboxes checked
      8. Leave Columns empty
      9. For Values, add the Cost field with Summarize By as SUM and Show as as Default
      10. Finally, you can add additional filters if you choose. For example, if you exported all journal records, even those with a $0 value, you can exclude them here if you choose

      Important Information

      1. Animal ID is the first row (primary) because each one is unique. if multiple records exist for the same animal then Animal ID is the proper field to group by. Additionally, if you have a lot of records, you've more than likely used the same Animal (Name) for different animals. Thus, using the animal name as the primary won't work
      2. If you want to see additional fields in the pivot table:
        1. Make sure you add the field to the data table view
        2. Once the data is in Sheets or Excel, repeat Part 2 #2 and move the column for the extra field so it comes after the Cost column
        3. Follow the step in Part 2 #3 but make sure to include the additional column (e.g., select columns A-D if the new field is in D)
        4. Follow all of the steps when creating a pivot table and then add the additional field/column as a Row, but under Animal ID and Animal


        CommentAdd your comment...