SQL query for Power BI report views

CREATE VIEW [dbo].[A1ProfitCentreView] AS

select DimensionFinancialTag.Value, DimensionFinancialTag.Description from [dataverse].[dbo].[DimensionFinancialTag]  

join [dataverse].[dbo].[DimensionAttributeDirCategory]    on DimensionAttributeDirCategory.DirCategory = DimensionFinancialTag.FinancialTagCategory

join DimensionAttributeView  on  DimensionAttributeView.RecId = DimensionAttributeDirCategory.DimensionAttribute and DimensionAttributeView.TYPE =1

and DimensionAttributeView.Name = 'A1ProfitCentre'

-----------------------

CREATE VIEW [dbo].[A2BusinessUnitView] AS

select DimensionFinancialTag.Value, DimensionFinancialTag.Description from [dataverse].[dbo].[DimensionFinancialTag]  

join [dataverse].[dbo].[DimensionAttributeDirCategory]    on DimensionAttributeDirCategory.DirCategory = DimensionFinancialTag.FinancialTagCategory

join DimensionAttributeView  on  DimensionAttributeView.RecId = DimensionAttributeDirCategory.DimensionAttribute and DimensionAttributeView.TYPE =1

and DimensionAttributeView.Name = 'A2BusinessUnit'

---------------------------

CREATE VIEW [dbo].[A3DivisionView] AS

select OMOperatingUnit.OMOPERATINGUNITNUMBER AS VALUE, DirPartyTable.NAME from [dataverse].[dbo].[dirpartytable]

join [dataverse].[dbo].[OMOperatingUnit] on OMOperatingUnit.recid = DirPartyTable.recid 

and OMOperatingUnitType = 4 and instancerelationtype = '23952'

--------------------------

CREATE VIEW [dbo].[A4CostCentreView] AS

select OMOperatingUnit.OMOPERATINGUNITNUMBER AS VALUE, DirPartyTable.NAME from [dataverse].[dbo].[dirpartytable]

join [dataverse].[dbo].[OMOperatingUnit] on OMOperatingUnit.recid = DirPartyTable.recid 

and OMOperatingUnitType = 2 and instancerelationtype = '23952'

-------------------------

CREATE VIEW [dbo].[A5VesselView] AS

select DimensionFinancialTag.Value, DimensionFinancialTag.Description from [dataverse].[dbo].[DimensionFinancialTag]  

join [dataverse].[dbo].[DimensionAttributeDirCategory]    on DimensionAttributeDirCategory.DirCategory = DimensionFinancialTag.FinancialTagCategory

join DimensionAttributeView  on  DimensionAttributeView.RecId = DimensionAttributeDirCategory.DimensionAttribute and DimensionAttributeView.TYPE =1

and DimensionAttributeView.Name = 'A5Vessel'

-----------------------

CREATE VIEW [dbo].[A6LocationView] AS

select DimensionFinancialTag.Value, DimensionFinancialTag.Description from [dataverse].[dbo].[DimensionFinancialTag]  

join [dataverse].[dbo].[DimensionAttributeDirCategory]    on DimensionAttributeDirCategory.DirCategory = DimensionFinancialTag.FinancialTagCategory

join DimensionAttributeView  on  DimensionAttributeView.RecId = DimensionAttributeDirCategory.DimensionAttribute and DimensionAttributeView.TYPE =1

and DimensionAttributeView.Name = 'A6Location'

-----------------------

CREATE VIEW [dbo].[A7ProjectView] AS 

SELECT T1.PROJID AS VALUE, T1.NAME AS NAME FROM [dataverse].[dbo].[PROJTABLE] T1

GO

------------------------

CREATE VIEW [dbo].[A8BuildingView] AS

select DimensionFinancialTag.Value, DimensionFinancialTag.Description from [dataverse].[dbo].[DimensionFinancialTag]  

join [dataverse].[dbo].[DimensionAttributeDirCategory]    on DimensionAttributeDirCategory.DirCategory = DimensionFinancialTag.FinancialTagCategory

join DimensionAttributeView  on  DimensionAttributeView.RecId = DimensionAttributeDirCategory.DimensionAttribute and DimensionAttributeView.TYPE =1

and DimensionAttributeView.Name = 'A8Building'

---------------------

CREATE VIEW [dbo].[A9IntercompanyView] AS

select DimensionFinancialTag.Value, DimensionFinancialTag.Description from [dataverse].[dbo].[DimensionFinancialTag]  

join [dataverse].[dbo].[DimensionAttributeDirCategory]    on DimensionAttributeDirCategory.DirCategory = DimensionFinancialTag.FinancialTagCategory

join DimensionAttributeView  on  DimensionAttributeView.RecId = DimensionAttributeDirCategory.DimensionAttribute and DimensionAttributeView.TYPE =1

and DimensionAttributeView.Name = 'A9Intercompany'

------------------------

Create  VIEW [dbo].[DimensionAttributeView] AS

SELECT name,BackingEntityType, TYPE,recid FROM [dataverse].[dbo].[DimensionAttribute] WHERE ((Type = 0) OR (Type = 1)) AND ((BackingEntityType = 2169) 

OR (BackingEntityType = 4862) OR (BackingEntityType = 5468) OR (BackingEntityType = 6390) OR (BackingEntityType = 9140) 

OR (BackingEntityType = 9703) OR (BackingEntityType = 9896) OR (BackingEntityType = 10010) OR (BackingEntityType = 11916) 

OR (BackingEntityType = 12973) OR (BackingEntityType = 13212) OR (BackingEntityType = 13363) OR (BackingEntityType = 16885) 

OR (BackingEntityType = 16922) OR (BackingEntityType = 16965) OR (BackingEntityType = 17349) OR (BackingEntityType = 18588) 

OR (BackingEntityType = 18633) OR (BackingEntityType = 18721) OR (BackingEntityType = 19689) OR (BackingEntityType = 19720) 

OR (BackingEntityType = 20565) OR (BackingEntityType = 20986) OR (BackingEntityType = 21039) OR (BackingEntityType = 21790) 

OR (BackingEntityType = 21858) OR (BackingEntityType = 22074) OR (BackingEntityType = 24071) OR (BackingEntityType = 24107) 

OR (BackingEntityType = 25768) OR (BackingEntityType = 26535) OR (BackingEntityType = 26883) OR (BackingEntityType = 27031) 

OR (BackingEntityType = 27454) OR (BackingEntityType = 27527) OR (BackingEntityType = 28020) OR (BackingEntityType = 29044)) 

-------------------------