Create a report to show time card cost details which are capex and which are opex

[Ashish Arovind2]

Hi Everyone , 

I am completely new to ITBM and still reading on it but i have come across a requirement where i need some help .

We are creating a report where the time card submitted by people should show for which week they are submitting and the total cost that is coming up and we want to identify whether the cost is capex or opex .

For this purpose i created a database view combining both time card and expense line table with the formula :

fm_expense_lin (prefix: el)

time_card(prefix: tc) formula = el_source_id = tc_task

The formula is bringing up all the timecard expense line records where i am seeing multiple expense line records for one time card record .

I am thinking of combining time card and cost type table now but i need some guidance in this .

Can anyone please help me on this .

Regards

Ashish Arovind Raj

[Vinayak Belgaon]

You do not have to combine the cost type table. There is already an Expense type field available on the Expense line table which you can use to group by on your reports.

I would suggest you create a Pivot type of report on your database view to display your data.

something like the below screenshot

Kindly mark helpful/correct if this resolved your query.

Regards

Vinayak

[Ashish Arovind2]

Hi Vinayak

Thank you for helping me ,I appreciate your help .but this will not apply to my use case . 

I want a report where manager should be able to see

Time card data by user , per week ,per project and whether the cost is on Capex or Opex

That is why i created a db view on time card and expense line . in time card we do the get the cost type .

Can you please guide me on this .

Regards
Ashish Arovind Raj

[Vinayak Belgaon]

Question to you is how are you identifying if the expense line created is for time_card entry, apart from task is there any other relation, then you would have to add this into the database view?

Is this expense line created when time sheet is approved or is it created from the other integration?

For the report 

create a pivot report on the database table you created.

in the filter add filter to week starts on this week

in the columns you can select the Week starts on from the time card table

in the rows you can select the task field from time_card table and User from time card table, and Expense type from the fm_expense_line table.

in the aggregation you can select sum for Actual/amount field of the expense line table.