06-24-2024 06:00 PM - edited 06-24-2024 06:01 PM
Hi - I am currently working on a dashboard that leverages a data adapter to an external database to fetch detailed GL transactions (similar to drillback functionality) and return them in a BI Viewer table within a dashboard. The SQL statement currently works as intended for when referencing a single, base member account and comparing to a bound account parameter a la:
WHERE sql_data_account = '|OSAccountParameter|'
I'd like to modify this so that records are returned for all base members of an account a la:
WHERE sql_data_account IN '|OSAccountParameter|.base'
However, I can't get anything similar to the above to work. I assume it's because '|OSAccountParameter|.base' gets read a string literal. Any ideas on how I can reference a list of base member given an account provided by a parameter in SQL?
Any help will be greatly appreciated!
Solved! Go to Solution.
06-25-2024 04:36 AM - edited 06-25-2024 04:38 AM
Hi @stribou, If you write your SQL in a business rule, you can use the SqlStringHelper class to build an IN clause dynamically by passing in your variables as a List(Of String) e.g.
' Get DimPk for Dimension
Dim dimPk As DimPk = BRApi.Finance.Dim.GetDimPk(si, "Time")
' Retrieve Base Members as List(Of String)
Dim memberList As List(Of String) = BRApi.Finance.Members.GetBaseMembers(si, dimPk, TimeDimHelper.GetIdFromName("2024"), Nothing). _
Select(Function(lambda) lambda.Name).ToList()
' Create In Clause with memberList
Dim sql As String = "SELECT * From YourTable WHERE " + SqlStringHelper.CreateInClause("Time", memberList, True)
' Log
BRApi.ErrorLog.LogMessage(si, "LogSql", sql)
You can see what to pass in to the method using Intellisense
06-25-2024 12:39 AM
Hi, ".base" and other such functions do not work in SQL. Those are OneStream functions that work e.g. in Business Rules and Member Filter Builder. You will need to either return a list of string from your parameter with all the base members in it, or you can pull that information with SQL code.
Here is a post where someone shared such SQL code (including parents, so you need to adjust it according to your needs):
SQL query to retrieve base members of a hierarchy - OneStream Community (onestreamsoftware.com)
People have contributed a lot and shared their own code. A great post in my view!
06-25-2024 04:36 AM - edited 06-25-2024 04:38 AM
Hi @stribou, If you write your SQL in a business rule, you can use the SqlStringHelper class to build an IN clause dynamically by passing in your variables as a List(Of String) e.g.
' Get DimPk for Dimension
Dim dimPk As DimPk = BRApi.Finance.Dim.GetDimPk(si, "Time")
' Retrieve Base Members as List(Of String)
Dim memberList As List(Of String) = BRApi.Finance.Members.GetBaseMembers(si, dimPk, TimeDimHelper.GetIdFromName("2024"), Nothing). _
Select(Function(lambda) lambda.Name).ToList()
' Create In Clause with memberList
Dim sql As String = "SELECT * From YourTable WHERE " + SqlStringHelper.CreateInClause("Time", memberList, True)
' Log
BRApi.ErrorLog.LogMessage(si, "LogSql", sql)
You can see what to pass in to the method using Intellisense