Base Members as a List In SQL Queries

[stribou]

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!

[Henning]

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!

[sameburn · Accepted solution]

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