B1 Dashboards - Financial Year views

Completed

Julie Jamieson - Business Evolution

• November 05, 2017 20:24

The standard queries that come with the dashboard are constructed to use calendar years. It would be really nice if these were to use look ups to the OFPR tables to display financial year data instead?

Vote

1

• Official comment

  

  Rasmus Jensen

  • November 08, 2017 07:14

  We are actually already doing this on the latest MSSQL versions of Dashboard so if you are on MSSQL you might have an older version of the dashboard (as we build new existing dashboards do not get these features). In such a case you can restore the default dashboards (NB: You would loose any modifications you made) or edit the SQLs of the dashboards and include this new way to choose from/to dates that use financial year (new is in Bold):

  DECLARE @Today DATETIME = CAST(CAST(GETDATE() AS DATE) AS DATETIME)
  DECLARE @SameDayLastWeek DATETIME = DATEADD(DD, -7, @Today)
  DECLARE @SameDayLastMonth DATETIME = DATEADD(MONTH, -1, @Today)
  DECLARE @SameDayLastQuarter DATETIME = DATEADD(QQ, -1, @Today)
  DECLARE @SameDayLastYear DATETIME = DATEADD(YEAR, -1, @Today)
  DECLARE @StartOfCurrentMonth DATETIME = DATEADD(MONTH, DATEDIFF(MONTH,0,@Today), 0)
  DECLARE @StartOfPreviousMonth DATETIME = DATEADD(MONTH, DATEDIFF(MONTH,0, @Today)-1,0)
  DECLARE @StartOfCurrentQuarter DATETIME = DATEADD(QQ, DATEDIFF(QQ,0, @Today),0)
  DECLARE @StartOfPreviousQuarter DATETIME = DATEADD(QQ, DATEDIFF(QQ,0, @Today)-1,0)
  DECLARE @StartOfCurrentYear DATETIME = CAST(YEAR(@Today) AS NVARCHAR(4))+'0101'
  DECLARE @StartOfPreviousYear DATETIME = CAST(YEAR(@Today)-1 AS NVARCHAR(4))+'0101'
  DECLARE @EndOfCurrentYear DATETIME = CAST(YEAR(@Today) AS NVARCHAR(4))+'1231'
  DECLARE @EndOfPreviousYear DATETIME = CAST(YEAR(@Today)-1 AS NVARCHAR(4))+'1231'
  DECLARE @EndOfCurrentMonth DATETIME = DATEADD(S, -1, DATEADD(MONTH, DATEDIFF(MONTH,0,@Today)+1,0))
  DECLARE @EndOfPreviousMonth DATETIME = DATEADD(S,-1, DATEADD(MONTH, DATEDIFF(MONTH,0, @Today),0))
  DECLARE @EndOfCurrentQuarter DATETIME = DATEADD(S,-1, DATEADD(QQ, DATEDIFF(QQ,0, @Today)+1,0))
  DECLARE @EndOfPreviousQuarter DATETIME = DATEADD(S,-1, DATEADD(QQ, DATEDIFF(QQ,0, @Today),0))

  DECLARE @CurrentFinanceYear DATETIME = (select MAX(FinancYear) as financYearStart from OACP where FinancYear<= GETDATE())
  DECLARE @PreviousFinanceYear DATETIME = (select MAX(FinancYear) as financYearStart from OACP where FinancYear< @CurrentFinanceYear)
  DECLARE @StartOfCurrentFinancialYear DATETIME = (SELECT MIN(T1.F_RefDate) FROM OACP T0 JOIN OFPR T1 ON T0.PeriodCat = T1.Category WHERE T0.FinancYear = @CurrentFinanceYear)
  DECLARE @StartOfPreviousFinancialYear DATETIME = (SELECT MIN(T1.F_RefDate) FROM OACP T0 JOIN OFPR T1 ON T0.PeriodCat = T1.Category WHERE T0.FinancYear = @PreviousFinanceYear)
  DECLARE @EndOFCurrenctFinancialYear DATETIME = (SELECT MAX(T1.T_RefDate) FROM OACP T0 JOIN OFPR T1 ON T0.PeriodCat = T1.Category WHERE T0.FinancYear = @CurrentFinanceYear)
  DECLARE @EndOfPreviousFinancialYear DATETIME = (SELECT MAX(T1.T_RefDate) FROM OACP T0 JOIN OFPR T1 ON T0.PeriodCat = T1.Category WHERE T0.FinancYear = @PreviousFinanceYear)

  DECLARE @CurrentStart DATETIME = @StartOfCurrentFinancialYear
  DECLARE @CurrentEnd DATETIME = @Today
  DECLARE @PreviousStart DATETIME = @StartOfPreviousFinancialYear
  DECLARE @PreviousEnd DATETIME = @SameDayLastYear

  ***

  When it comes to SAP HANA this is not yet available as we have not found a way to write the same SQL in HANA :-( But it is on the todo-list if/when we find a way
• 

  Rasmus Jensen

  • November 06, 2017 20:18

  Thank you for your request. The development team have now added it to our internal planning-system for evaluation.

Please sign in to leave a comment.