Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Jeffrey DiOrio]

Hi everyone, and thanks in advance for any help….it is very much appreciated!

Background: my company just went Live on D365 Finance & Operations last Monday.  Crazy few weeks (and few months!!), but very exciting.  

I have successfully connected Power BI to our Production environment and have been getting data out of the system in the manner.  However there is data that I have for the life of me been unable to find.  

Specifically for this thread, it’s time related to Project Resource Timesheets.  I have successfully found the TimeSheetTables table.  However that just gives ‘ApprovalStatus’, ‘Name’, ‘StartDate’, and a few other pieces of information.  It does not provide the time spent on each Category.

I’m assuming (hoping!) that table names are stock between Environments….we are supposedly using fairly canned D365 Fin-Ops.  

Can anyone please help me point towards how I can find a table that provides the times people are submitting on their Project Timesheets?

The screenshot I pasted into here looks fuzzy to me….hopefully the one I attached looks better.  But basically, when I lookup ‘Form information’ for the time data, it says the form is ‘TSTimesheetEntry’, but I cannot find any such table in Power BI.  So not sure where else to look.

——————————
Jeffrey DiOrio
Vice President of Finance
The Heico Companies
Azusa
——————————

[Donna Cribbin]

Hello Jeffrey,

I looked at the Data Entities in Data Management and found two tables related to timesheets:  TSTimesheetTable and TSTimesheetLine.  TSTimesheetTable has the header data and TSTTimeSheetLine has the line date.  The fields in the TSTimesheetLine data entity includes the hours, project id, etc.  Try looking for these tables.  

Donna Cribbin
Solution Architect
DXC Eclipse

——————————
Donna Cribbin
DXC Technology
New York NY
——————————
——————————————-

[Jeffrey DiOrio]

Hi Donna, and thanks so much for the reply!

When I try and look up either TSTimeSheetLine or TSTimesheetTable entities, nothing comes up at all. Even without searching and just scrolling through the options, there’s nothing there at all starting with TST.  Yet I can see the data plain as day in D365 and per the screenshot in my first post, it’s showing as being in a Form starting with TST.

Might you know if there’s a possibility that certain tables need to be specifically allowed to be accessed by Power BI or perhaps there’s some kind of indexing feature that may not have been done for all Tables?  

——————————
Jeffrey DiOrio
Vice President of Finance
The Heico Companies
Azusa
——————————
——————————————-

[David Simon]

Not sure of the screen you are looking up the entities in but did you try “Timesheet…”

A user friendly name (in F&O’s Data Managment) for these data entities are:
* Timesheet Headers
* Timesheet Lines

——————————
David Simon
System Administrator
Johnson, Mirmiran & Thompson, Inc.
Sparks MD
——————————
——————————————-

[Juan Sebastian Grijalba]

Hello,

I can see TSTimesheetLineEntity and TSTimesheetTableEntity in the list of entities. The reason why you might not see them when using ODATA is because these entities are set to private. You will need a developer to mark them as public in VS so you can use them in PowerBI.
However using data management workspace you can download the data from them and see if they will give you all the fields you need.

——————————
Juan Sebastian Grijalba
Berkowitz Pollack Brant
Miami
——————————
——————————————-

[David Simon]

There are FOUR (4) “TS Timesheet” tables that I frequently use.

Here is an example of a SQL select statement that I have used that includes those 4 “TS Timesheet” tables.  The joins help to explain how you those tables relate.  BOTH the TSTimesheetLineWeek (lw) and the TimesheetTimesheetTrans (t) have hours.  You just have to figure out which one you want.  Often, you will NOT need all four of these.  This just happens to show all 4 to show how they all relate.

select ts.timesheetnbr, r.name, r.wrkctrid, r.dataareaid, ts.periodto, ts.approvalstatus, ts.poststatus, ts.modifieddatetime, ts.createddatetime,
ts.jmtexportedutilpro, ts.jmtexportedutilprodate,
l.projid, l.activitynumber, l.categoryid, l.approvalstatus, l.linenum, l.linepropertyid, l.projperiodtimesheetweek, l.modifieddatetime, l.createddatetime,
lw.hours, lw.hours2_, lw.hours3_, lw.hours4_, lw.hours5_, lw.hours6_, lw.hours7_, lw.poststatus, lw.transid, lw.recid,
t.transdate, t.transid, t.journalid, t.voucher, t.approvalstatus, t.poststatus, t.hours
from TSTimesheetTable ts
left join wrkctrtable r on ts.resource_ = r.recid
left join TSTimesheetLine l on ts.timesheetnbr = l.timesheetnbr and ts.resource_ = l.resource_
left join TSTimesheetLineWeek lw on l.recid = lw.tstimesheetline
left join tstimesheettrans t on lw.recid = t.tstimesheetlineweek
where r.wrkctrid = ‘04374’ and ts.periodto = ‘1/13/2018’ and ts.timesheetnbr = ‘TS171075’
order by ts.createddatetime, l.linenum

——————————
David Simon
Solutions Architect
Johnson, Mirmiran & Thompson, Inc.
Sparks MD
——————————
——————————————-

[Jeffrey DiOrio]

Hi everyone.  First off, I just wanted to give a big ‘Thank You’ to everyone.  The insight and assistance has been very much appreciated.

The current belief is that the needed tables are somehow set to Private or some other similar setting which is preventing me from viewing in Power BI. In fact, current mindset is that what I am seeing in Power BI are only templates out of D365 and none of the underlying tables.  I may be misstating….I’m still a novice, but that is what I believe the thought is at the moment. 

Going to work with the Technical Team to see if making those available is a long job or a quick ‘flip of the switch’.  I’m keeping my fingers crossed!

——————————
Jeffrey DiOrio
Vice President of Finance
The Heico Companies
Azusa
——————————
——————————————-

[Ben Morgan]

Hi Jeffrey,

You’re basically correct.  Just to give you some background on the way D365 data works, there are three basic categories relevant to the current discussion: 

1. Tables – The actual tables the system uses for its basic functions.  You can’t see these at all in your production environment, but you can access them via the various Dev environment VMs.
2. Entities via Data Management (DMF) – These are essentially ‘views’ of various combinations of the underlying D365 data tables.  Some are 1:1 with the tables, but most of the delivered ones are not.  
3. Entities via OData – Same concept as the DMF entities, and probably 95% of the DMF entities are available here, but they are technically not quite the same.  And as you’ve discovered, there are a few that are present in DMF and not published via OData.  This is where the ‘private’ indicator comes in.  

To answer your question more directly, while there are timesheet-related data entities, they may or may not contain all the data you need as they don’t have everything from the underlying tables.  You could publish these to OData and see if that works, but it is just as easy to create your own data entities directly off the underlying tables, especially if you keep them 1:1.  Here is some info on how to do that: 

https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/data-entities/build-consuming-data-entities
https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/data-entities/data-entities

This is one of the easier modifications you can make – I just did one, and I’m a total noob.  If you do wind up creating your own data entities, the timesheet tables you’ll be potentially interested in are: 

TSTimesheetTable: timesheet header info – 1 record per timesheet
TSTimesheetLine: one of two timesheet line tables (not sure why they do it this way), containing project and category info – 1 record per timesheet line
TSTimesheetLineWeek: the other timesheet line table containing the hours, comments, and pricing for each timesheet line – 1 record per timesheet line
TSTimesheetTrans: project transactions created from each timesheet entry.  This is the ‘exploded’ view by day, so if a person fills in 5 days worth of project hours on one timesheet line, there will be 5 transaction records created from that line.

——————————
Ben Morgan
M3 Engineering & Technology Corp
——————————
——————————————-

[Justin Potter]

I’m not 100% this will have everything you are looking for, but try the ProjectPostTransView entity in BI. You get more that just Timesheets, but you can filter it down on the Transaction Origin or Transaction Type. Origin would be Timesheet, Type would be Hour. That will get the posted timesheets only, anything not posted to the project yet will not be in that data set.

Our organization is new to D365 as well as of the start of the year and we have been digging for data much the same.

——————————
Justin Potter
Meta Special Aerospace
Oklahoma City OK
——————————
——————————————-

[Jeffrey DiOrio]

Thanks so much everyone!  I think that beyond my immediate needs of gathering Timesheet data, we have some bigger questions that need to be answered regarding gaining access to underlying data.  The input everyone has had will go A LONG way towards answering those questions.

In the meantime, Justin….I do have access to the ProjectPostTransView entity in BI and I think it’s going to give me exactly what I need.  Thank you SO MUCH!  This will at least keep things moving for now, and I don’t think I ever would have stumbled upon that entity by myself!

——————————
Jeffrey DiOrio
Vice President of Finance
The Heico Companies
Azusa
——————————
——————————————-