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 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!
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
