How to Achieve dynamic data retrieval: Current month + 12 months prior using SQL in Dashboards

Rithik_Prem
New Contributor III

Hi All,

I am currently facing a challenge with my SQL code in dashboards and would greatly appreciate your help in finding a solution. The issue at hand is that I have hardcoded the code to pull data from tables, but now I need to modify it so that when a user selects a specific month (e.g., 2023M1), the dashboard displays data for the selected month as well as the 12 months prior to it.

To provide more context, here is the query I am currently using:

SELECT ISNULL(Name, 'Total |!SelectAcc!|') AS Name, 
       SUM(CASE WHEN Period = '2022M12' THEN |!SelectAcc!|  END) AS '2022M12',
       SUM(CASE WHEN Period = '2022M11' THEN |!SelectAcc!|  END) AS '2022M11',
       SUM(CASE WHEN Period = '2022M10' THEN |!SelectAcc!|  END) AS '2022M10',
       SUM(CASE WHEN Period = '2022M9' THEN |!SelectAcc!|  END) AS '2022M9',
       SUM(CASE WHEN Period = '2022M8' THEN |!SelectAcc!| END) AS '2022M8',
       SUM(CASE WHEN Period = '2022M7' THEN |!SelectAcc!|  END) AS '2022M7',
       SUM(CASE WHEN Period = '2022M6' THEN |!SelectAcc!|  END) AS '2022M6',
       SUM(CASE WHEN Period = '2022M5' THEN |!SelectAcc!|  END) AS '2022M5',
       SUM(CASE WHEN Period = '2022M4' THEN |!SelectAcc!|  END) AS '2022M4',
       SUM(CASE WHEN Period = '2022M3' THEN |!SelectAcc!|  END) AS '2022M3',
       SUM(CASE WHEN Period = '2022M2' THEN |!SelectAcc!|  END) AS '2022M2',
       SUM(CASE WHEN Period = '2022M1' THEN |!SelectAcc!|  END) AS '2022M1',
SUM(|!SelectAcc!|) As 'TOTAL'
FROM (
    SELECT Name, |!SelectAcc!|, Period FROM Account_TABLE WHERE Period IN ('2022M12', '2022M11','2022M10', '2022M9','2022M8','2022M7', '2022M6','2022M5', '2022M4','2022M3', '2022M2','2022M1')
    UNION
    SELECT Name, |!SelectAcc!|, Period FROM Second_TABLE WHERE Period IN ('2022M12', '2022M11','2022M10', '2022M9','2022M8','2022M7', '2022M6','2022M5', '2022M4','2022M3', '2022M2','2022M1')
) AS a
GROUP BY Rollup (Name)
ORDER BY 
    CASE
        WHEN ISNULL(Name, 'Total |!SelectAcc!|') = 'Total |!SelectAcc!|' THEN 1
   Else 0
    END, 
    ISNULL(Name, 'Total |!SelectAcc!|')

 

As you can see, the query currently involves hardcoding all the 12 months prior to the selected month, which is not an efficient and scalable approach.

I am looking for a solution that allows for dynamic retrieval of data, enabling the display of the current month and the 12 months preceding it. For example, if a user enters "2023M1," the dashboard should show data for 2023M1 as well as all the 12 months prior to it.

If anyone could provide guidance, suggestions, or insights on how to modify this query to achieve the desired dynamic retrieval of data, I would be immensely grateful.

Thank You,

Rithik

7 REPLIES 7

Michel_Sabourin
Contributor II

Not really similar, but I had a use case to parse the current period into a format to be able to convert to a separate date format to run in a SQL query. What I did was create an xfbr business rule and call it from the SQL statement. In this particular case, the call went like:

AND a.C1EFT>= 'XFBR(MFC_FX_Date_Converter, MonthStart,TimeMe=[|!FXU_Param_FX_ML_Select_Time!|])'

You could create a rule that creates a string of you r sum statements and pulls that back into the SQL.

Hi, 

     I was wondering if you could kindly provide me with a more detailed or elaborate explanation of the solution you shared.

JackLacava
Community Manager
Community Manager

I'll be that guy: this might be done in an easier manner with a Cube View (where expanding Time with functions is trivial), either coupled with a Data adapter of type Cube View MD (which allows you to loop over 2 dimensions, for an effective total of 4+2+2 expandable dimensions) or with a Dashboard Dataset using an FDX call (which you can execute over and over to keep looping on the CV, or even just simply point at the DataUnit, hence without limits on the number of expandable dimensions).

Michel_Sabourin
Contributor II

I read this as coming from an outside source. If internal, then you're absolutely correct Jack

Depending on where you are running this SQL query, can't you get the year from the user entry and use a loop to build your SQL query like something similar?

dim strUserTime as String
dim intUserYear as integer = Left(strUserTime,4)
dim intUserPriorYear as integer = intUserYear-1
Dim lstTimeList As new List(of String)
For each i = 1 to 12
  lstTimeList.Add($"{intUserPriorYear}M{i}")
Next

Now you can then do String.Join(", ", lstTimeList), to get the whole list of time. 

Hi, 

     I was wondering if you could please elaborate further on the solution you provided.

DanielWillis
Contributor III

There are probably a lot of other ways to do it in SQL (perhaps others more optimal even) but one way is doing something like the following. You can join on OSPeriods instead of sticking your periods in the WHERE clauses. Note that I've hardcoded  the starting date twice which you'd have to make dynamic. I just threw it together now but performance seems OK. Building a hard-coded query dynamically in OS may yield better results instead of running this query a lot of times at once.

 

WITH dateList(periodDate) AS (
    SELECT CAST('01-JAN-22' AS datetime)
    UNION ALL
    SELECT DATEADD(month, -1, periodDate)
    FROM dateList
    WHERE periodDate > DATEADD(month,-11,CAST('01-JAN-22' AS datetime))
), 
OSPeriods(OSPeriod) AS (
	SELECT  format(periodDate,'yyyy\MM') AS OSPeriod FROM dateList
)
select OSPeriod from OSPeriods;