I am unsure how to approach this, so I hope I'll get some ideas from some forum members. I have a table where I track Resource Earnings. There are 3 fields for earnings: Project Earnings, Fixed Earnings, and Bonuses. There is a date field next to each of earning type which tracks when the earning was made. What I would like to do is to track the total monthly and yearly earnings per employee.

I tried to do this by adding a calculated field with the following formula: SELECT SUM (IsNull([Project_Earnings], 0) +IsNull([Fixed_Earnings], 0) +IsNull([Bonus], 0)) FROM AM_tbl_Resources_Earnings WHERE [Resource_ID] = .target[Resource_ID] AND Year([Date_1])=2022 AND Year([Date_2])=2022 AND Year([Date_3])=2022.

This formula gives me the correct result but the thing is that I see that result in the tabular report as a separate column and I want to see the total yearly earnings per employee in the bottom row as total. Or, even better, I would like to see it as in the screenshot below for total revenue, like a separate value.

I tried to create a separate column Total Earnings that would give me a sum of Project Earnings, Fixed Earnings, and Bonus and eventually work with that field, but for some reason, my formula is not working. All field types that refer to earnings are currency.

You can post now and register later.
If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

## Question

## Aleksandra

Hello,

I am unsure how to approach this, so I hope I'll get some ideas from some forum members. I have a table where I track Resource Earnings. There are 3 fields for earnings: Project Earnings, Fixed Earnings, and Bonuses. There is a date field next to each of earning type which tracks when the earning was made. What I would like to do is to track the total monthly and yearly earnings per employee.

I tried to do this by adding a calculated field with the following formula: SELECT SUM (IsNull([Project_Earnings], 0) +IsNull([Fixed_Earnings], 0) +IsNull([Bonus], 0)) FROM AM_tbl_Resources_Earnings WHERE [Resource_ID] = .target[Resource_ID] AND Year([Date_1])=2022 AND Year([Date_2])=2022 AND Year([Date_3])=2022.

This formula gives me the correct result but the thing is that I see that result in the tabular report as a separate column and I want to see the total yearly earnings per employee in the bottom row as total. Or, even better, I would like to see it as in the screenshot below for total revenue, like a separate value.

I tried to create a separate column Total Earnings that would give me a sum of Project Earnings, Fixed Earnings, and Bonus and eventually work with that field, but for some reason, my formula is not working. All field types that refer to earnings are currency.

Thank you all in advance.

## Link to comment

## Share on other sites

## 2 answers to this question

## Recommended Posts

## Join the conversation

Note:Your post will require moderator approval before it will be visible.