Reports

  • 1
  • Question
  • Updated 4 years ago
I am creating my own reports on the cloud version of Replicon. I need to create the following custom columns based on timesheet data but do not know how. The report columns are: user, "hours worked", "annualized FTE equivalent" and "percent of total". In the report I have filters set on project and timesheet period (ideally I wanted to select "months back" instead of a date range). 1) "annualized FTE equivalent" = hrs worked * 12 / (1500 * number months back) I do not know how to calculate number months back. E.g. if time period is 1/5/2014 to 1/7/2014 then number months back = 2. 2) "Percent of total" = hrs worked/(sum of hrs worked by everyone on the project during time period) I could not find the variable for "sum of hrs worked by everyone on the project during time period". Does anyone know how to do this?

(Originally posted by cng)
Photo of Archived Post

Archived Post, Official Rep

  • 2132 Posts
  • 6 Reply Likes

Posted 4 years ago

  • 1
Photo of Archived Post

Archived Post, Official Rep

  • 2132 Posts
  • 6 Reply Likes
Hi,

Thank you for using Replicon Community.

Please help me clarify some details of what you have requested so that I can verify if we can achieve what you are looking for. 

number months back = E.g. if time period is 1/5/2014 to 1/7/2014 then number months back = 2

The time period you have mentioned “1/5/2014 to 1/7/2014”, is it the period selected in the date range filter or is it the time period from the start date of the Project (1/5/2014) to the end date of the project (1/7/2014)?
If the time period you are referring to is a filter value, then the report cannot capture the value as a field value and we will not be able to use it in the report. However, if the time period you are referring to is the start and end date of a project, then we can use it in the custom column.

Percent of total = hours worked/ (sum of hrs worked by everyone on the project during time period)

When you say “sum of hrs worked by everyone on the project during time period”, is it the total hours entered against a project till date or is it the total hours entered against a project in the time period which is selected in the date range filter.

In this case also, if you are referring to the filter then we would not be able to use it for the custom calculation.

Thanks



(Originally posted by Lingaraj Dharwad)
Photo of Archived Post

Archived Post, Official Rep

  • 2132 Posts
  • 6 Reply Likes
Hi CNG, I was reading through the forums today and saw your post; I have two quick follow-up questions for you: 1)Based on the columns and formulas you described, would the report's purpose happen to be related to a compliance issue?; 2) When you stated that "ideally I wanted to select "months back" instead of a date range," how were you intending to choose the commencement date for the subsequent lookback to start from (select a month and year vs select a specific date)? Thanks, Jon Burns Labor Rule Specialist

(Originally posted by jon.burns)
Photo of Archived Post

Archived Post, Official Rep

  • 2132 Posts
  • 6 Reply Likes
Thanks Krishnakumar. I was referring to the filter value in both cases. I really want to be able to use filter values in the report. Would it be possible to add this feature in a future release?

(Originally posted by cng)
Photo of Archived Post

Archived Post, Official Rep

  • 2132 Posts
  • 6 Reply Likes
Hi,

Thank you for the response.

Unfortunately there would not be a change in this. I have tried to explain the working of the custom column and reason why the filters can't be used.

When a report is run, Custom formula process the data retrieved from the database and displays in the custom column. For example, if a report has 5 columns of which one is a custom column, then the data seen in the 4 regular column are retrieved first, directly from the database. The formula in the custom column then processes the data for each row (based on the information requested by the formula) and displays along with other columns. Hence, the data displayed in the regular columns are primary and those in custom columns are secondary.
The filters in a report are conditions which are used to retrieve data for the specified range, and hence cannot be used as a calculation agent.



Thanks



(Originally posted by Lingaraj Dharwad)
Photo of Archived Post

Archived Post, Official Rep

  • 2132 Posts
  • 6 Reply Likes
Hi Jon, Our reports have nothing to do with compliance. We are using our reports to distribute cost over projects and to predict future expenditure. We can use "months back" or select a time period. The financial manager prefers using "months back". W

(Originally posted by cng)