{"id":44,"date":"2019-09-16T16:36:16","date_gmt":"2019-09-16T19:36:16","guid":{"rendered":"https:\/\/pedrotornich.com\/a\/?p=44"},"modified":"2019-09-16T17:14:12","modified_gmt":"2019-09-16T20:14:12","slug":"get-financial-dimension-value-and-description","status":"publish","type":"post","link":"https:\/\/pedrotornich.com\/a\/2019\/09\/16\/get-financial-dimension-value-and-description\/","title":{"rendered":"Get financial dimension value and description"},"content":{"rendered":"\n<p>We were posed with the following task the other day, get all financial dimension values and descriptions<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"342\" src=\"https:\/\/pedrotornich.com\/a\/wp-content\/uploads\/2019\/09\/Screen-Shot-2019-09-16-at-16.14.51-1024x342.png\" alt=\"\" class=\"wp-image-45\" srcset=\"https:\/\/pedrotornich.com\/a\/wp-content\/uploads\/2019\/09\/Screen-Shot-2019-09-16-at-16.14.51-1024x342.png 1024w, https:\/\/pedrotornich.com\/a\/wp-content\/uploads\/2019\/09\/Screen-Shot-2019-09-16-at-16.14.51-300x100.png 300w, https:\/\/pedrotornich.com\/a\/wp-content\/uploads\/2019\/09\/Screen-Shot-2019-09-16-at-16.14.51-768x256.png 768w, https:\/\/pedrotornich.com\/a\/wp-content\/uploads\/2019\/09\/Screen-Shot-2019-09-16-at-16.14.51.png 1540w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>But those fields can come from different tables depending on the type of the dimension.<br>For example, custom dimensions are stored in the DimensionFinancialTag, while Departments will be stored in the DirPartyTable.<\/p>\n\n\n\n<p>To overcome that issue Microsoft introduced several views that will assist developers correctly retrieve financial dimensions.<\/p>\n\n\n\n<p>The DimensionAttribute table relates the attribute to its source view. The field BackingEntityType holds the TableId of the view and the field NameAttribute holds the FieldId of the description field on the underlining view.<\/p>\n\n\n\n<p>You can run the following query to find all views behind financial dimension values and descriptions:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">SELECT\n     BACKINGENTITYTYPE AS TABLEID,\n     NAMEATTRIBUTE AS FIELDID,\n     (SELECT TOP 1 NAME FROM SQLDICTIONARY WHERE TABLEID = BACKINGENTITYTYPE AND FIELDID = 0 AND SQLNAME &lt;> '') AS TABLENAME,\n     (SELECT TOP 1 NAME FROM SQLDICTIONARY WHERE TABLEID = BACKINGENTITYTYPE AND FIELDID = NAMEATTRIBUTE AND SQLNAME &lt;> '') AS FIELDNAME\n FROM DIMENSIONATTRIBUTE\n GROUP BY BACKINGENTITYTYPE, NAMEATTRIBUTE<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/pedrotornich.com\/a\/wp-content\/uploads\/2019\/09\/Screen-Shot-2019-09-16-at-17.08.43.png\" alt=\"\" class=\"wp-image-51\" width=\"421\" height=\"261\" srcset=\"https:\/\/pedrotornich.com\/a\/wp-content\/uploads\/2019\/09\/Screen-Shot-2019-09-16-at-17.08.43.png 872w, https:\/\/pedrotornich.com\/a\/wp-content\/uploads\/2019\/09\/Screen-Shot-2019-09-16-at-17.08.43-300x186.png 300w, https:\/\/pedrotornich.com\/a\/wp-content\/uploads\/2019\/09\/Screen-Shot-2019-09-16-at-17.08.43-768x476.png 768w\" sizes=\"(max-width: 421px) 100vw, 421px\" \/><\/figure>\n\n\n\n<p>Even with this views, getting values from different dimensions would require a good effort, so MS also created the <strong>FinancialDimensionValueEntityView<\/strong> that does a union of all the views behind all dimensions.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"130\" src=\"https:\/\/pedrotornich.com\/a\/wp-content\/uploads\/2019\/09\/Screen-Shot-2019-09-16-at-16.35.47-1024x130.png\" alt=\"\" class=\"wp-image-46\" srcset=\"https:\/\/pedrotornich.com\/a\/wp-content\/uploads\/2019\/09\/Screen-Shot-2019-09-16-at-16.35.47-1024x130.png 1024w, https:\/\/pedrotornich.com\/a\/wp-content\/uploads\/2019\/09\/Screen-Shot-2019-09-16-at-16.35.47-300x38.png 300w, https:\/\/pedrotornich.com\/a\/wp-content\/uploads\/2019\/09\/Screen-Shot-2019-09-16-at-16.35.47-768x97.png 768w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>We were posed with the following task the other day, get all financial dimension values and descriptions But those fields can come from different tables depending on the type of the dimension.For example, custom dimensions are stored in the DimensionFinancialTag, while Departments will be stored in the DirPartyTable. To overcome [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1,3,4],"tags":[],"class_list":["post-44","post","type-post","status-publish","format-standard","hentry","category-d365fo","category-financial-dimensions","category-sql"],"_links":{"self":[{"href":"https:\/\/pedrotornich.com\/a\/wp-json\/wp\/v2\/posts\/44"}],"collection":[{"href":"https:\/\/pedrotornich.com\/a\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pedrotornich.com\/a\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pedrotornich.com\/a\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/pedrotornich.com\/a\/wp-json\/wp\/v2\/comments?post=44"}],"version-history":[{"count":5,"href":"https:\/\/pedrotornich.com\/a\/wp-json\/wp\/v2\/posts\/44\/revisions"}],"predecessor-version":[{"id":54,"href":"https:\/\/pedrotornich.com\/a\/wp-json\/wp\/v2\/posts\/44\/revisions\/54"}],"wp:attachment":[{"href":"https:\/\/pedrotornich.com\/a\/wp-json\/wp\/v2\/media?parent=44"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pedrotornich.com\/a\/wp-json\/wp\/v2\/categories?post=44"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pedrotornich.com\/a\/wp-json\/wp\/v2\/tags?post=44"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}