Forum Discussion

Rithik_Prem's avatar
Rithik_Prem
New Contributor III
2 years ago

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

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

Dashboards
Parameters
Reports
  • Mike_Sabourin's avatar
    Mike_Sabourin
    Contributor II
    2 years ago

    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.

    • Rithik_Prem's avatar
      Rithik_Prem
      New Contributor III
      2 years ago

      Hi, 

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

  • JackLacava's avatar
    JackLacava
    Honored Contributor
    2 years ago

    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).

  • Mike_Sabourin's avatar
    Mike_Sabourin
    Contributor II
    2 years ago

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

  • ckattookaran's avatar
    ckattookaran
    VIP
    2 years ago

    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. 

    • Rithik_Prem's avatar
      Rithik_Prem
      New Contributor III
      2 years ago

      Hi, 

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

  • DanielWillis's avatar
    DanielWillis
    Valued Contributor
    2 years ago

    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;