05-23-2023 07:10 AM - edited 05-23-2023 07:12 AM
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
05-23-2023 04:18 PM
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.
05-29-2023 03:58 AM
Hi,
I was wondering if you could kindly provide me with a more detailed or elaborate explanation of the solution you shared.
05-23-2023 05:14 PM - edited 05-23-2023 05:15 PM
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).
05-23-2023 05:21 PM
I read this as coming from an outside source. If internal, then you're absolutely correct Jack
05-23-2023 06:38 PM
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.
05-29-2023 04:01 AM
Hi,
I was wondering if you could please elaborate further on the solution you provided.
05-23-2023 07:58 PM - edited 05-23-2023 07:59 PM
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;