Skip to main content

Notifications

Announcements

No record found.

Finance | Project Operations, Human Resources, ...
Unanswered

Data entity for given SQL code

(0) ShareShare
ReportReport
Posted on by 123
Hello,
as This data enties will be used for power bi, I am not getting how to create data entity from this given SQL code
 
select 
     custtable.CUSTGROUP [sales type],
     salestable.SALESSTATUS [Order Status],
     convert(date,salestable.CREATEDDATETIME)  [Date],
     CASE WHEN (MONTH(salestable.CREATEDDATETIME)) <= 3 
                        THEN convert(varchar(4), YEAR(salestable.CREATEDDATETIME)-1) + '-' + convert(varchar(4), YEAR(salestable.CREATEDDATETIME)%100)    
                        ELSE convert(varchar(4),YEAR(salestable.CREATEDDATETIME))+ '-' + convert(varchar(4),(YEAR(salestable.CREATEDDATETIME)%100)+1)END AS Fiscal_Year,
     logisticsaddresscountryregiontranslation.LONGNAME [Country],
     salestable.INVENTSITEID                [Site/Location],
     salestable.INVENTLOCATIONID            [Warehouse],
     salestable.SALESID                        [Sales ID],
     salesline.LINEAMOUNT                    ,
     salesline.CURRENCYCODE                    [Currency],
     case when salesline.CURRENCYCODE = 'EUR' then salesline.LINEAMOUNT * 86 
          when salesline.CURRENCYCODE = 'INR' then salesline.LINEAMOUNT 
          when salesline.CURRENCYCODE = 'SGD' then salesline.LINEAMOUNT * 54
          when salesline.CURRENCYCODE = 'USD' then salesline.LINEAMOUNT * 72 end [Amount],
     dirpartytable.NAME                        [Customer],
     LOGISTICSPOSTALADDRESS.DISTRICTNAME [District]
     
from custtable custtable
left join SALESTABLE salestable on custtable.ACCOUNTNUM = salestable.CUSTACCOUNT 
left join DIRPARTYTABLE dirpartytable on custtable.PARTY = dirpartytable.RECID
left join SALESLINE salesline on salestable.SALESID = salesline.SALESID
--left join dirpartylocation dirpartylocation on custtable.PARTY = dirpartylocation.PARTY 
left join LOGISTICSPOSTALADDRESS logisticspostaladdress on salestable.DELIVERYPOSTALADDRESS = logisticspostaladdress.RECID
left join LOGISTICSADDRESSCOUNTRYREGIONTRANSLATION logisticsaddresscountryregiontranslation on logisticspostaladdress.COUNTRYREGIONID = logisticsaddresscountryregiontranslation.COUNTRYREGIONID
where 
SALESTABLE.DOCUMENTSTATUS in (3)
 
  • GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 on at
    Data entity for given SQL code
    Hi M_R,
     
    I would suggest you create a normal data entity - List out all the tables and find out the relation between them.
    Based on that create a data entity - For conversions you can use Computed column or virtual field in Data entity.
    Refer to the below documentation for creating Computed column and virtual field.
    Also, you can add Data entity ranges.
     
    Thanks,
    Girish S.
  • Martin Dráb Profile Picture
    Martin Dráb 230,966 Most Valuable Professional on at
    data Entity
    Moved from D365 general forum to the Finance forum, because its seems to be about F&O and not about D365 in general. I also changed the topic from data Entity to Data entity for given SQL code, to make it a bit more concrete.
     
    I also suspect I've seen this question before - isn't it a duplicate?
     
    Do you have any particular problem that you want to discuss?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Congratulations to the January Top 10 leaders!

Check out the January community rock stars...

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,187 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,966 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans