The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.
Forum Discussion
Akalya_N
2 years agoNew Contributor
Threshold Operation in FDX using SQL Query
Hi Community...!
I want to perform Threshold operation in FDX report using SQL Query.. Anyone who knows please share the format for reference... Thank You...!
If you meant that you're running an FDX query and want to see values > 30000 in the returned datatable then you can use the Filter parameter
filter = "Amount > 30000"
BRApi.Import.Data.FdxExecuteDataUnit(si, cubeName, entityMemFilter, consName, scenarioTypeId, scenarioMemFilter, timeMemFilter, viewName, suppressNoData, filter, parallelQueryCount, logStatistics);
Hi SWilyums,
You are 100% correct. There is an existing bug - "22557 - FDX Query Filter; When no records match the filter argument, then all data records are returned."
Look for future release notes to find out when it's been resolved or reach out to support for updates.As a workaround, you may want to try the data table select functionality as mentioned by Sai_Maganti or the filtering capabilities offered by the vb.net DataView objects. If it's a big data set and performance is important you may want to try both methods to understand which one would be best in your use case (code sample below):
'Option A: dim dtFilteredRows as DataRow() = dt.Select("Account='A30000' And (UD1 ='D1059' OR UD1 = 'D1061' OR UD1 = 'D1062')") 'Option B: Dim sRowFilter As String = "Account='A30000' And (UD1 ='D1059' OR UD1 = 'D1061' OR UD1 = 'D1062')" Dim sSort As String = String.Empty Dim dview As New DataView(dt,sRowFilter,sSort,DataViewRowState.CurrentRows) dt = dview.ToTable()
13 Replies
- Sai_MagantiContributor II
If you meant that you're running an FDX query and want to see values > 30000 in the returned datatable then you can use the Filter parameter
filter = "Amount > 30000"
BRApi.Import.Data.FdxExecuteDataUnit(si, cubeName, entityMemFilter, consName, scenarioTypeId, scenarioMemFilter, timeMemFilter, viewName, suppressNoData, filter, parallelQueryCount, logStatistics);
- Akalya_NNew Contributor
Hi...! I've did this Threshold Operation like this...You can find it here
But...Here i've given random value (30000),If the user wants to give any number means...You can use parameters for this..Thank You...!
- SWilyumsContributor
Hello Sai_Maganti,
A filter question for you.
I have a filter that looks like this:
Filter = "Account='A30000' And (UD1='D1059' OR UD1='D1061' OR UD1='D1062')"
The filter is not enforced, and I get extra data.If just filter on a single dimenion the filter works
Filter = "Account='A30000'" - This works
Filter = "UD1='D1059' OR UD1='D1061' OR UD1='D1062'" - This WorksWhen combined no luck. Any suggestions?
- FredLucas
OneStream Employee
Hi SWilyums,
I put together a very quick example to test a similar filter to see if I was able to figure out what could be going on but it seems to be working ok in my case.
Here's the code I've tried:
Dim dt As Datatable = BRApi.Import.Data.FdxExecuteDataUnit(si, "PeoplePln", "E#UK", "Local", scenarioTypeId.Budget, "S#BudgetDefault", "T#2021M5", "YTD", True, "UD1 = 'None' And (Account = 'Bonus' OR Account = 'Staff Costs' OR Account = 'None')", 1, False)Stupid question I know 🙂 but have you double checked that there's data for that exact combination you are filtering?
I've ran my test on v8.2.0 and I don't see anything wrong with your filter so the only options that come to my mind are version related or that the combination of filters does actually result in no data.
You can use the following function to print the content of your data table, maybe that can help you with the debug:
Public Function PrintDataTable(ByVal si As SessionInfo, ByVal dt As DataTable, ByVal Optional strSeparator As String = "|", ByVal Optional limit As Integer = 0, ByVal Optional boolPrintTableName As Boolean = True, ByVal Optional list As List(Of String) = Nothing, ByVal Optional strTextSep As String = Nothing) As String Dim strLog As New text.StringBuilder Dim strBRName As String = System.Reflection.MethodInfo.GetCurrentMethod().Name Dim listColToSkip As New List(Of String) ({"None"}) If Not IsNothing(list) Then listColToSkip = list End If strLog.AppendLine(String.Format("{0} - Sep: {1}, Limit: {2}, Print Table Name: {3}, Col(s) To Skip: {4}", strBRName, strSeparator, limit.ToString, boolPrintTableName.ToString, String.Join(", ", listColToSkip))) If strSeparator.XFEqualsIgnoreCase("tab") Then strSeparator = vbTab End If Try Dim result As New Text.StringBuilder If boolPrintTableName Then result.AppendLine("Table name: " & dt.TableName) End If Dim header As String = Nothing For Each dc As DataColumn In dt.Columns strLog.AppendLine(String.Format("{0} - Col Name: {1}, To Skip: {2}", strBRName, dc.ColumnName, listColToSkip.Contains(dc.ColumnName))) If Not listColToSkip.Contains(dc.ColumnName) Then header = header & dc.ColumnName & strSeparator End If Next If Not String.IsNullOrEmpty(header) Then result.AppendLine(header) End If If limit = 0 Then limit = dt.Rows.Count -1 If limit > dt.Rows.Count -1 Then limit = dt.Rows.Count -1 For curRow As Integer = 0 To limit Dim row As String = String.Empty For curCol As Integer = 0 To dt.Columns.Count -1 If Not listColToSkip.Contains(dt.Columns(curCol).ColumnName) Then If strTextSep Is Nothing Then row = row & dt.Rows(curRow)(curCol).ToString.Trim & strSeparator Else row = row & strTextSep & dt.Rows(curRow)(curCol).ToString.Trim & strTextSep & strSeparator End If End If Next If Not String.IsNullOrEmpty(row) Then result.AppendLine(row) End If Next Return result.ToString Catch ex As Exception BRApi.ErrorLog.LogMessage(si, strLog.ToString) Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Function
- Sai_MagantiContributor II
Hmm not sure why it is not honouring the filter..However, you can ignore the filter here and fetch all the data and then do a select on the datatable returned. See above post by OP. The filtering was done outside the fdx using a select.
dim dtFilteredRows() as DataRow = dtFdx.Select("Account='A30000' And UD1 IN ('D1059', 'D1061', 'D1062')")
Best
Sai
- Akalya_NNew Contributor
Sure... If the Threshold value is 30000 means..the values below 30000 in the data table should be suppressed and the values above 30000 should only display...
Related Content
- 3 years ago
- 4 years ago