Excel report into SQL - View
-
Please assist.
What would be the quickest and most convenient/effective way of turning an Excel report into SQL - View?
Here is the view of the Pivot in Excel:
Here a table/report is created in Excel containing the pivoted data:
Please note that column 11 is made up of combined columns, e.g "=-SUMIF('SAP Source'!C[-10];'Cash Flow'!RC[-4];'SAP Source'!C[-3])+334776"
-
First question I'd ask is "what problem are you trying to solve by putting the Excel data into SQL Server?". How will putting the data into SQL Server help you?
But to answer your question, the "quickest and most convenient" way to use the import wizard. Not the most "effective" way, but definitely the easiest and tons of tips online on how to do it:
https://stackoverflow.com/questions/39610133/how-can-i-import-an-excel-file-into-sql-server
Now, why do I say this is not the "most effective" way, because of data typing and normalizing the data as well as calculations. Do the calculations need to be persisted to the database OR is it better to re-calculate them each time you look at the data? Plus, I have a feeling that the pivot table is not going to import nicely into SQL Server. It might, I'm not saying it won't, I just have a feeling it is going to import weirdly.
Another reason why this is not the "most effective" way is that your calculated columns are going to be pulled in as static text as SQL Server doesn't care about cross worksheet calculations when importing the data if I remember right.
What feels to me like a better option is to store the underlying RAW data in SQL and then use Excel, PowerBI, SSRS, etc to pull the data out and make it look and feel how you want. Use SQL to store the RAW data, the stuff that can't be calculated, then use the tools at your disposal to manage the calculations. If the calculation is too complex in your tools (ie slow in Excel), then put the calculation into a view and pull data out of the view.
BUT my very first question to you would be "what problem are you trying to solve by putting the Excel data into SQL Server?". If you don't know what problem you are trying to solve, how will you know that your solution worked?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
I am trying to solve the manual task of reporting in Excel.
The idea is to use Power BI to replace Excel. The calculations can be done in Power BI, but because we use different tools in a big organisation, the solutions wont be fixed to Power BI.
The code below gives me this:
That is simply connected to Power BI.
Column G needs to be broken into Levels, like in the example code.
USE [_FINAL]
GO
/****** Object: View [Prod].[SSA - Plant Costs (Consolodated)_View_SAP BW] Script Date: 11/2/2023 11:09:38 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--SELECT DISTINCT [Metric Level 4],[Metric Level 3],[Metric Level 2],[Metric Level 1] FROM [Prod].[SSA - Plant Costs (Consolodated)_View_SAP BW]
--SELECT * FROM [Prod].[SSA - Plant Costs (Consolodated)_View_SAP BW] WHERE [Metric Level 1] !='Not assigned' GROUP BY [Calendar day]
CREATE VIEW [Prod].[SSA - Plant Costs (Consolodated)_View_SAP BW]
AS
(
SELECT[Calendar day]
,[Calendar Month]
,[Plant_Code]
,[Product_Code]
,Element
,Element_Grouped
,CASE WHEN [Metric] IN (
[Metric])
THEN 'Total Cost' END AS [Metric Level 1]
,CASE WHEN [Metric] = 'Accrual for Leave - from Payroll' THEN 'Total Variable Cost'
WHEN [Metric] = 'Consumption Detergents' THEN 'Total Variable Cost'
WHEN [Metric] = 'Consumption Nails' THEN 'Total Variable Cost'
WHEN [Metric] = 'Consumption Paint' THEN 'Total Variable Cost'
WHEN [Metric] = 'Consumption RPC/IBC/Auto' THEN 'Total Variable Cost'
WHEN [Metric] = 'Consumption Spares' THEN 'Total Variable Cost'
WHEN [Metric] = 'Consumption Stk Consums' THEN 'Total Variable Cost'
WHEN [Metric] = 'Consumption Timber' THEN 'Total Variable Cost'
WHEN [Metric] = 'Consumption Tools' THEN 'Total Variable Cost'
WHEN [Metric] = 'Diesel' THEN 'Other Costs'
WHEN [Metric] = 'Fixed Depreciation' THEN 'Total Fixed Cost'
WHEN [Metric] = 'Fixed Forklift Costs' THEN 'Total Fixed Cost'
WHEN [Metric] = 'Fixed Labour Salary' THEN 'Total Fixed Cost'
WHEN [Metric] = 'Fixed Rent Buiding' THEN 'Total Fixed Cost'
WHEN [Metric] = 'Fixed Repair and Maintenace' THEN 'Total Fixed Cost'
WHEN [Metric] = 'Fixed Running Costs' THEN 'Total Fixed Cost'
WHEN [Metric] = 'Other Costs Allocated to Ops at Total Company Level' THEN 'Other Costs'
WHEN [Metric] = 'Overtime Wage (Permanent Stuff)' THEN 'Total Variable Cost'
WHEN [Metric] = 'PRODSPARE' THEN 'Other Cost'
--WHEN [Metric] = 'Raw Material from Inventory consumption' THEN 'Total Variable Cost'
WHEN [Metric] = 'Raw Material Purchase offset' THEN 'EW&T Recovery for Total Company'
WHEN [Metric] = 'Repairs and Maintenance (Land & Building)' THEN 'Total Variable Cost'
WHEN [Metric] = 'Third Party service Centre ' THEN 'Total Variable Cost'
WHEN [Metric] = 'Variable Consumption' THEN 'Total Variable Cost'
WHEN [Metric] = 'Variable Labour' THEN 'Total Variable Cost'
WHEN [Metric] = 'Variable Third Party' THEN 'Total Variable Cost'
WHEN [Metric] = 'Variable Transport Costs' THEN 'Total Variable Cost'
WHEN [Metric] = 'Waste Disposal' THEN 'Total Variable Cost'
WHEN [Metric] = 'Wages (Permanent stuff)' THEN 'Total Variable Cost'
WHEN [Metric] = 'EW&T Recovery for Total Company' THEN 'Other Costs'
ELSE 'Not assigned'
END AS [Metric Level 2]
,CASE WHEN [Metric] = 'Accrual for Leave - from Payroll' THEN 'Labour Cost'
WHEN [Metric] = 'Consumption Detergents' THEN 'Consumption Cost'
WHEN [Metric] = 'Consumption Nails' THEN 'Consumption Cost'
WHEN [Metric] = 'Consumption Paint' THEN 'Consumption Cost'
WHEN [Metric] = 'Consumption RPC/IBC/Auto' THEN 'Consumption Cost'
WHEN [Metric] = 'Consumption Spares' THEN 'Consumption Cost'
WHEN [Metric] = 'Consumption Stk Consums' THEN 'Consumption Cost'
WHEN [Metric] = 'Consumption Timber' THEN 'Consumption Cost'
WHEN [Metric] = 'Consumption Tools' THEN 'Consumption Cost'
WHEN [Metric] = 'Diesel' THEN 'Consumption Cost'
WHEN [Metric] = 'Fixed Depreciation' THEN 'Depreciation'
WHEN [Metric] = 'Fixed Forklift Costs' THEN 'Forklift Cost'
WHEN [Metric] = 'Fixed Labour Salary' THEN 'Labour Cost'
WHEN [Metric] = 'Fixed Rent Buiding' THEN 'Rental Cost'
WHEN [Metric] = 'Fixed Repair and Maintenace' THEN 'Repairs and Maintenace'
WHEN [Metric] = 'Fixed Running Costs' THEN 'Running Costs'
WHEN [Metric] = 'Other Costs Allocated to Ops at Total Company Level' THEN 'Other Costs'
WHEN [Metric] = 'Overtime Wage (Permanent Stuff)' THEN 'Labour Cost'
WHEN [Metric] = 'PRODSPARE' THEN 'Consumption Cost'
WHEN [Metric] = 'Raw Material from Inventory consumption' THEN 'Raw Material Cost'
WHEN [Metric] = 'Raw Material Purchase offset' THEN 'Raw Material Cost'
WHEN [Metric] = 'Repairs and Maintenance (Land & Building)' THEN 'Repairs and Maintenance Cost'
WHEN [Metric] = 'Variable Consumption' THEN 'Other Consumption'
WHEN [Metric] = 'Variable Labour' THEN 'Labour Cost'
WHEN [Metric] = 'Variable Third Party' THEN 'Third Party Cost'
WHEN [Metric] = 'Variable Transport Costs' THEN 'Transport Cost'
WHEN [Metric] = 'Waste Disposal' THEN 'Waste Cost'
WHEN [Metric] = 'Wages (Permanent stuff)' THEN 'Labour Cost'
WHEN [Metric] = 'EW&T Recovery for Total Company' THEN 'Other Costs'
ELSE 'Not assigned'
END AS [Metric Level 3]
,[Metric] AS [Metric Level 4]
,[Metric Value]
,[Metric Value R12M]
,[Metric Value YTD]
FROM
(
-----------------------------------------------------------------------------------------------------------
--Including the transformation for YTD & R12M and also ensuring that columns comorm with the Union bellow
-----------------------------------------------------------------------------------------------------------
SELECT
[Calendar day]
,[FY]
,CAST(CONCAT([FY],'-',RIGHT([Period],2),'-12') AS DATE) AS [Calendar Month]
,[Plant_Code]
,[Product_Code]
,Element
,Element_Grouped
,[Metric]
,[Metric Value]
,SUM([Metric Value]) OVER (PARTITION BY
[Plant_Code]
,[Product_Code]
,Element
,Element_Grouped
,[Metric]
ORDER BY [Calendar day] ROWS 11 PRECEDING) AS [Metric Value R12M] ---Calculating rolling 12 month number
,SUM([Metric Value]) OVER (PARTITION BY
[FY]
,[Plant_Code]
,[Product_Code]
,Element
,Element_Grouped
,[Metric]
ORDER BY [Calendar day]) AS [Metric Value YTD] ----Calculating YTD
FROM
(
SELECTDISTINCT Table_2.[Last Day of period] AS [Calendar day]
,Table_2.Period
,RIGHT([Fiscal year/period],4) AS [FY]
,Table_1.[Plant_Code]
,Table_1.[Product_Code]
,Table_1.Element
,Table_1.Element_Grouped
,Table_1.[Metric]
--,Table_1.[Cost influencers]
,Table_1.[Metric Value]
FROM
(
SELECT [Fiscal year/period]
,[Plant_Code]
,[Product_Code]
,Element
,Element_Grouped
,[Metric]
,[Metric Value]
FROM
(SELECT
CAST([Fiscal year/period] AS VARCHAR(12)) AS [Fiscal year/period]
,[Plant] AS [Plant_Code]
,[AME Product Group] AS [Product_Code]
,[KPI] AS [Element]
,CASE
WHEN [KPI]='Repair' Then 'Conditioning'
WHEN [KPI]='Washing' Then 'Conditioning'
ELSE [KPI]
END AS [Element_Grouped]
-----------------------------------------------------------------------------------------------------------------------------
--Calculated Columns
----------------------------------Plant Costs Derived------------------------------------------------------------------------------
,coalesce([Fixed Depreciation],0) AS [Fixed Depreciation]
,coalesce([Fixed Labour Salary],0) AS [Fixed Labour Salary]
,coalesce([Fixed Repair and Mai],0) AS [Fixed Repair and Maintenace]
,coalesce([Fixed Forklift Costs],0)
+ coalesce([Fixed Rent Buiding],0)
+ coalesce([Fixed Running Costs],0) AS [Fixed Running Costs]
,coalesce([Variable Consumption],0) AS [Variable Consumption]
,coalesce([Variable Labour],0)
+coalesce([O'time Waged Perm],0)+coalesce([Wages Permanent],0)
+coalesce([Ann/Stat hol pay Wag],0) AS [Variable Labour]
,coalesce([Variable Transport C],0) AS [Variable Transport Costs]
,coalesce([Variable Waste Dispo],0) AS [Waste Disposal]
,coalesce([Variable 3rdP Cycle1],0)
+coalesce([TParty Serv Ctre Pro],0)
--+coalesce([Rep &Maint Land Buil],0)
--+coalesce([RawMat fr Inv consum],0)
AS [Variable Third Party]
----------------------------[Other Costs]-------------------------------------------------------------------------------
,coalesce([GCOA/91354],0) AS [Other Costs Allocated to Ops at Total Company Level]
,coalesce([Raw Mat purch offset],0) AS [EW&T Recovery for Total Company]
FROM
(SELECT
[Fiscal year/period]
,[Plant]
,[AME Product Group]
,[Other Measures/KPI]
,[KPI]
,([Measure]) AS [Total]
FROM [Prod].[SSA - Plant Consolodated Metrics_SAP BW]
where [Type] IN ('OtherCosts','PlantCosts')
UNION ALL--- Appending the MEA SLA adjusted costs into the Cost from SAP BW
(SELECT
REPLACE(CONCAT([Period],CONCAT('.',[Financial Year])),'P','0') AS [Fiscal year/period]
,[Plant]
,[Structure] AS [AME Product Group]
,[Cost Element] AS [Other Measures/KPI]
,[Business Process Activity] AS [KPI]
,[Value] AS [Total]
FROM
(
SELECT DISTINCT
CONCAT('20',SUBSTRING(COST_ADJUST.[FY],3,2)) AS [Financial Year]
,COST_ADJUST.[Period]
,COST_ADJUST.[Plant]
,COST_ADJUST.[Structure]
,COST_ADJUST.[Business Process Activity]
,CASE WHEN COST_ADJUST.[Cost Element] = 'TP Service Centre' THEN 'TParty Serv Ctre Pro'
WHEN COST_ADJUST.[Cost Element] = 'Fixed Repairs and Maint.' THEN 'Fixed Repair and Mai'
ELSE COST_ADJUST.[Cost Element] END AS [Cost Element]
,COST_ADJUST.[Value]
FROM [_FINAL].[Prod].[ - Plant Metrics - Cost Adjustment_View_Other] AS COST_ADJUST
) AS Cost_Adjustments )
) t
PIVOT (
SUM ([Total])
FOR [Other Measures/KPI] IN ([Fixed Depreciation]
,[Fixed Labour Salary]
,[Fixed Repair and Mai]
,[Fixed Forklift Costs]
,[Fixed Rent Buiding]
,[Fixed Running Costs]
,[Variable Consumption]
,[Variable Labour]
,[O'time Waged Perm]
,[Wages Permanent]
,[Ann/Stat hol pay Wag]
,[Variable Transport C]
,[Variable Waste Dispo]
,[Variable 3rdP Cycle1]
,[TParty Serv Ctre Pro]
,[Rep &Maint Land Buil]
,[RawMat fr Inv consum]
,[GCOA/91354]
,[Raw Mat purch offset]
)
) pivot_table_2 ) Table_3
UNPIVOT
( [Metric Value] FOR [Metric] IN
(
[Fixed Depreciation]
,[Fixed Labour Salary]
,[Fixed Repair and Maintenace]
,[Fixed Running Costs]
,[Variable Consumption]
,[Variable Labour]
,[Variable Transport Costs]
,[Waste Disposal]
,[Variable Third Party]
,[Other Costs Allocated to Ops at Total Company Level]
,[EW&T Recovery for Total Company]
)
) AS UNPVT
WHERE [Metric Value] <>0
) Table_1
LEFT OUTER JOIN (
SELECT
CAST([Date] AS DATE) AS [Date]
,CONCAT('20',SUBSTRING([FY],3,2)) AS [Financial Year]
,[Period]
,FIRST_VALUE([Date]) OVER (PARTITION BY FY,[Period] ORDER BY [Date]) AS [First Day of period]
,FIRST_VALUE([Date]) OVER (PARTITION BY FY,[Period] ORDER BY [Date] DESC) AS [Last Day of period]
,DATEDIFF(DAY,(FIRST_VALUE([Date]) OVER (PARTITION BY FY,[Period] ORDER BY [Date])),(FIRST_VALUE([Date]) OVER (PARTITION BY FY,[Period] ORDER BY [Date] DESC))) + 1 AS [Number of days]
FROM [_FINAL].[Prod].[ - Calendar Days Data_View_Other] AS CDF
WHERE
[FY] IS NOT NULL
)
AS Table_2 ON CONCAT('P', SUBSTRING(Table_1.[Fiscal year/period],2,2)) =Table_2.[Period] AND SUBSTRING(Table_1.[Fiscal year/period],5,8)=Table_2.[Financial Year]
) TRANSFORMED_MODEL ----Include R12M and YTD
UNION ALL
SELECT [Calendar day]
,[FY]
,[Calendar Month]
,[Plant_Code]
,[Product_Code]
,[Element]
,[Element_Grouped]
,[Metric]
,[Metric Value]
,[Metric Value R12M]
,[Metric Value YTD]
FROM [_FINAL].[Prod].[SA - Plant Consumption Costs (Fact)_View_SAP BW]
) t
)
GO -
I am trying to solve the manual task of reporting in Excel.
The idea is to use Power BI to replace Excel. The calculations can be done in Power BI, but because we use different tools in a big organisation, the solutions wont be fixed to Power BI.
The code below gives me this:
That is simply connected to Power BI.
Column G needs to be broken into Levels, like in the example code.
USE [_FINAL]
GO
/****** Object: View [Prod].[SSA - Plant Costs (Consolodated)_View_SAP BW] Script Date: 11/2/2023 11:09:38 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--SELECT DISTINCT [Metric Level 4],[Metric Level 3],[Metric Level 2],[Metric Level 1] FROM [Prod].[SSA - Plant Costs (Consolodated)_View_SAP BW]
--SELECT * FROM [Prod].[SSA - Plant Costs (Consolodated)_View_SAP BW] WHERE [Metric Level 1] !='Not assigned' GROUP BY [Calendar day]
CREATE VIEW [Prod].[SSA - Plant Costs (Consolodated)_View_SAP BW]
AS
(
SELECT[Calendar day]
,[Calendar Month]
,[Plant_Code]
,[Product_Code]
,Element
,Element_Grouped
,CASE WHEN [Metric] IN (
[Metric])
THEN 'Total Cost' END AS [Metric Level 1]
,CASE WHEN [Metric] = 'Accrual for Leave - from Payroll' THEN 'Total Variable Cost'
WHEN [Metric] = 'Consumption Detergents' THEN 'Total Variable Cost'
WHEN [Metric] = 'Consumption Nails' THEN 'Total Variable Cost'
WHEN [Metric] = 'Consumption Paint' THEN 'Total Variable Cost'
WHEN [Metric] = 'Consumption RPC/IBC/Auto' THEN 'Total Variable Cost'
WHEN [Metric] = 'Consumption Spares' THEN 'Total Variable Cost'
WHEN [Metric] = 'Consumption Stk Consums' THEN 'Total Variable Cost'
WHEN [Metric] = 'Consumption Timber' THEN 'Total Variable Cost'
WHEN [Metric] = 'Consumption Tools' THEN 'Total Variable Cost'
WHEN [Metric] = 'Diesel' THEN 'Other Costs'
WHEN [Metric] = 'Fixed Depreciation' THEN 'Total Fixed Cost'
WHEN [Metric] = 'Fixed Forklift Costs' THEN 'Total Fixed Cost'
WHEN [Metric] = 'Fixed Labour Salary' THEN 'Total Fixed Cost'
WHEN [Metric] = 'Fixed Rent Buiding' THEN 'Total Fixed Cost'
WHEN [Metric] = 'Fixed Repair and Maintenace' THEN 'Total Fixed Cost'
WHEN [Metric] = 'Fixed Running Costs' THEN 'Total Fixed Cost'
WHEN [Metric] = 'Other Costs Allocated to Ops at Total Company Level' THEN 'Other Costs'
WHEN [Metric] = 'Overtime Wage (Permanent Stuff)' THEN 'Total Variable Cost'
WHEN [Metric] = 'PRODSPARE' THEN 'Other Cost'
--WHEN [Metric] = 'Raw Material from Inventory consumption' THEN 'Total Variable Cost'
WHEN [Metric] = 'Raw Material Purchase offset' THEN 'EW&T Recovery for Total Company'
WHEN [Metric] = 'Repairs and Maintenance (Land & Building)' THEN 'Total Variable Cost'
WHEN [Metric] = 'Third Party service Centre ' THEN 'Total Variable Cost'
WHEN [Metric] = 'Variable Consumption' THEN 'Total Variable Cost'
WHEN [Metric] = 'Variable Labour' THEN 'Total Variable Cost'
WHEN [Metric] = 'Variable Third Party' THEN 'Total Variable Cost'
WHEN [Metric] = 'Variable Transport Costs' THEN 'Total Variable Cost'
WHEN [Metric] = 'Waste Disposal' THEN 'Total Variable Cost'
WHEN [Metric] = 'Wages (Permanent stuff)' THEN 'Total Variable Cost'
WHEN [Metric] = 'EW&T Recovery for Total Company' THEN 'Other Costs'
ELSE 'Not assigned'
END AS [Metric Level 2]
,CASE WHEN [Metric] = 'Accrual for Leave - from Payroll' THEN 'Labour Cost'
WHEN [Metric] = 'Consumption Detergents' THEN 'Consumption Cost'
WHEN [Metric] = 'Consumption Nails' THEN 'Consumption Cost'
WHEN [Metric] = 'Consumption Paint' THEN 'Consumption Cost'
WHEN [Metric] = 'Consumption RPC/IBC/Auto' THEN 'Consumption Cost'
WHEN [Metric] = 'Consumption Spares' THEN 'Consumption Cost'
WHEN [Metric] = 'Consumption Stk Consums' THEN 'Consumption Cost'
WHEN [Metric] = 'Consumption Timber' THEN 'Consumption Cost'
WHEN [Metric] = 'Consumption Tools' THEN 'Consumption Cost'
WHEN [Metric] = 'Diesel' THEN 'Consumption Cost'
WHEN [Metric] = 'Fixed Depreciation' THEN 'Depreciation'
WHEN [Metric] = 'Fixed Forklift Costs' THEN 'Forklift Cost'
WHEN [Metric] = 'Fixed Labour Salary' THEN 'Labour Cost'
WHEN [Metric] = 'Fixed Rent Buiding' THEN 'Rental Cost'
WHEN [Metric] = 'Fixed Repair and Maintenace' THEN 'Repairs and Maintenace'
WHEN [Metric] = 'Fixed Running Costs' THEN 'Running Costs'
WHEN [Metric] = 'Other Costs Allocated to Ops at Total Company Level' THEN 'Other Costs'
WHEN [Metric] = 'Overtime Wage (Permanent Stuff)' THEN 'Labour Cost'
WHEN [Metric] = 'PRODSPARE' THEN 'Consumption Cost'
WHEN [Metric] = 'Raw Material from Inventory consumption' THEN 'Raw Material Cost'
WHEN [Metric] = 'Raw Material Purchase offset' THEN 'Raw Material Cost'
WHEN [Metric] = 'Repairs and Maintenance (Land & Building)' THEN 'Repairs and Maintenance Cost'
WHEN [Metric] = 'Variable Consumption' THEN 'Other Consumption'
WHEN [Metric] = 'Variable Labour' THEN 'Labour Cost'
WHEN [Metric] = 'Variable Third Party' THEN 'Third Party Cost'
WHEN [Metric] = 'Variable Transport Costs' THEN 'Transport Cost'
WHEN [Metric] = 'Waste Disposal' THEN 'Waste Cost'
WHEN [Metric] = 'Wages (Permanent stuff)' THEN 'Labour Cost'
WHEN [Metric] = 'EW&T Recovery for Total Company' THEN 'Other Costs'
ELSE 'Not assigned'
END AS [Metric Level 3]
,[Metric] AS [Metric Level 4]
,[Metric Value]
,[Metric Value R12M]
,[Metric Value YTD]
FROM
(
-----------------------------------------------------------------------------------------------------------
--Including the transformation for YTD & R12M and also ensuring that columns comorm with the Union bellow
-----------------------------------------------------------------------------------------------------------
SELECT
[Calendar day]
,[FY]
,CAST(CONCAT([FY],'-',RIGHT([Period],2),'-12') AS DATE) AS [Calendar Month]
,[Plant_Code]
,[Product_Code]
,Element
,Element_Grouped
,[Metric]
,[Metric Value]
,SUM([Metric Value]) OVER (PARTITION BY
[Plant_Code]
,[Product_Code]
,Element
,Element_Grouped
,[Metric]
ORDER BY [Calendar day] ROWS 11 PRECEDING) AS [Metric Value R12M] ---Calculating rolling 12 month number
,SUM([Metric Value]) OVER (PARTITION BY
[FY]
,[Plant_Code]
,[Product_Code]
,Element
,Element_Grouped
,[Metric]
ORDER BY [Calendar day]) AS [Metric Value YTD] ----Calculating YTD
FROM
(
SELECTDISTINCT Table_2.[Last Day of period] AS [Calendar day]
,Table_2.Period
,RIGHT([Fiscal year/period],4) AS [FY]
,Table_1.[Plant_Code]
,Table_1.[Product_Code]
,Table_1.Element
,Table_1.Element_Grouped
,Table_1.[Metric]
--,Table_1.[Cost influencers]
,Table_1.[Metric Value]
FROM
(
SELECT [Fiscal year/period]
,[Plant_Code]
,[Product_Code]
,Element
,Element_Grouped
,[Metric]
,[Metric Value]
FROM
(SELECT
CAST([Fiscal year/period] AS VARCHAR(12)) AS [Fiscal year/period]
,[Plant] AS [Plant_Code]
,[AME Product Group] AS [Product_Code]
,[KPI] AS [Element]
,CASE
WHEN [KPI]='Repair' Then 'Conditioning'
WHEN [KPI]='Washing' Then 'Conditioning'
ELSE [KPI]
END AS [Element_Grouped]
-----------------------------------------------------------------------------------------------------------------------------
--Calculated Columns
----------------------------------Plant Costs Derived------------------------------------------------------------------------------
,coalesce([Fixed Depreciation],0) AS [Fixed Depreciation]
,coalesce([Fixed Labour Salary],0) AS [Fixed Labour Salary]
,coalesce([Fixed Repair and Mai],0) AS [Fixed Repair and Maintenace]
,coalesce([Fixed Forklift Costs],0)
+ coalesce([Fixed Rent Buiding],0)
+ coalesce([Fixed Running Costs],0) AS [Fixed Running Costs]
,coalesce([Variable Consumption],0) AS [Variable Consumption]
,coalesce([Variable Labour],0)
+coalesce([O'time Waged Perm],0)+coalesce([Wages Permanent],0)
+coalesce([Ann/Stat hol pay Wag],0) AS [Variable Labour]
,coalesce([Variable Transport C],0) AS [Variable Transport Costs]
,coalesce([Variable Waste Dispo],0) AS [Waste Disposal]
,coalesce([Variable 3rdP Cycle1],0)
+coalesce([TParty Serv Ctre Pro],0)
--+coalesce([Rep &Maint Land Buil],0)
--+coalesce([RawMat fr Inv consum],0)
AS [Variable Third Party]
----------------------------[Other Costs]-------------------------------------------------------------------------------
,coalesce([GCOA/91354],0) AS [Other Costs Allocated to Ops at Total Company Level]
,coalesce([Raw Mat purch offset],0) AS [EW&T Recovery for Total Company]
FROM
(SELECT
[Fiscal year/period]
,[Plant]
,[AME Product Group]
,[Other Measures/KPI]
,[KPI]
,([Measure]) AS [Total]
FROM [Prod].[SSA - Plant Consolodated Metrics_SAP BW]
where [Type] IN ('OtherCosts','PlantCosts')
UNION ALL--- Appending the MEA SLA adjusted costs into the Cost from SAP BW
(SELECT
REPLACE(CONCAT([Period],CONCAT('.',[Financial Year])),'P','0') AS [Fiscal year/period]
,[Plant]
,[Structure] AS [AME Product Group]
,[Cost Element] AS [Other Measures/KPI]
,[Business Process Activity] AS [KPI]
,[Value] AS [Total]
FROM
(
SELECT DISTINCT
CONCAT('20',SUBSTRING(COST_ADJUST.[FY],3,2)) AS [Financial Year]
,COST_ADJUST.[Period]
,COST_ADJUST.[Plant]
,COST_ADJUST.[Structure]
,COST_ADJUST.[Business Process Activity]
,CASE WHEN COST_ADJUST.[Cost Element] = 'TP Service Centre' THEN 'TParty Serv Ctre Pro'
WHEN COST_ADJUST.[Cost Element] = 'Fixed Repairs and Maint.' THEN 'Fixed Repair and Mai'
ELSE COST_ADJUST.[Cost Element] END AS [Cost Element]
,COST_ADJUST.[Value]
FROM [_FINAL].[Prod].[ - Plant Metrics - Cost Adjustment_View_Other] AS COST_ADJUST
) AS Cost_Adjustments )
) t
PIVOT (
SUM ([Total])
FOR [Other Measures/KPI] IN ([Fixed Depreciation]
,[Fixed Labour Salary]
,[Fixed Repair and Mai]
,[Fixed Forklift Costs]
,[Fixed Rent Buiding]
,[Fixed Running Costs]
,[Variable Consumption]
,[Variable Labour]
,[O'time Waged Perm]
,[Wages Permanent]
,[Ann/Stat hol pay Wag]
,[Variable Transport C]
,[Variable Waste Dispo]
,[Variable 3rdP Cycle1]
,[TParty Serv Ctre Pro]
,[Rep &Maint Land Buil]
,[RawMat fr Inv consum]
,[GCOA/91354]
,[Raw Mat purch offset]
)
) pivot_table_2 ) Table_3
UNPIVOT
( [Metric Value] FOR [Metric] IN
(
[Fixed Depreciation]
,[Fixed Labour Salary]
,[Fixed Repair and Maintenace]
,[Fixed Running Costs]
,[Variable Consumption]
,[Variable Labour]
,[Variable Transport Costs]
,[Waste Disposal]
,[Variable Third Party]
,[Other Costs Allocated to Ops at Total Company Level]
,[EW&T Recovery for Total Company]
)
) AS UNPVT
WHERE [Metric Value] <>0
) Table_1
LEFT OUTER JOIN (
SELECT
CAST([Date] AS DATE) AS [Date]
,CONCAT('20',SUBSTRING([FY],3,2)) AS [Financial Year]
,[Period]
,FIRST_VALUE([Date]) OVER (PARTITION BY FY,[Period] ORDER BY [Date]) AS [First Day of period]
,FIRST_VALUE([Date]) OVER (PARTITION BY FY,[Period] ORDER BY [Date] DESC) AS [Last Day of period]
,DATEDIFF(DAY,(FIRST_VALUE([Date]) OVER (PARTITION BY FY,[Period] ORDER BY [Date])),(FIRST_VALUE([Date]) OVER (PARTITION BY FY,[Period] ORDER BY [Date] DESC))) + 1 AS [Number of days]
FROM [_FINAL].[Prod].[ - Calendar Days Data_View_Other] AS CDF
WHERE
[FY] IS NOT NULL
)
AS Table_2 ON CONCAT('P', SUBSTRING(Table_1.[Fiscal year/period],2,2)) =Table_2.[Period] AND SUBSTRING(Table_1.[Fiscal year/period],5,8)=Table_2.[Financial Year]
) TRANSFORMED_MODEL ----Include R12M and YTD
UNION ALL
SELECT [Calendar day]
,[FY]
,[Calendar Month]
,[Plant_Code]
,[Product_Code]
,[Element]
,[Element_Grouped]
,[Metric]
,[Metric Value]
,[Metric Value R12M]
,[Metric Value YTD]
FROM [_FINAL].[Prod].[SA - Plant Consumption Costs (Fact)_View_SAP BW]
) t
)
GO -
Is your question: "How do I create a report in Power BI which looks like my existing Excel solution?"
If so, it looks like a Paginated Report would be the way to go, rather than a PBIX.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
This was removed by the editor as SPAM
-
This was removed by the editor as SPAM
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply