Forum Discussion

stribou's avatar
stribou
New Contributor II
6 months ago

Base Members as a List In SQL Queries

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 ...
BI Viewer
Dashboards
Parameters
  • sameburn's avatar
    sameburn
    6 months ago

    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

Henning's avatar
Henning
Valued Contributor II
6 months ago

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!