12-18-2023 03:00 AM
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...!
Solved! Go to Solution.
12-18-2023 07:22 AM
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);
06-20-2024 04:58 AM
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()
12-18-2023 03:03 AM
Hello @Akalya_N ,
Can you please specify what do you mean by Threshold operation, and if it's for reporting only ?
Regards,
12-18-2023 03:08 AM
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...
12-18-2023 07:22 AM
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);
12-20-2023 02:42 AM
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...!
06-18-2024 06:43 PM
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 Works
When combined no luck. Any suggestions?
06-19-2024 03:32 AM
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
06-19-2024 05:14 PM
Hello Fred,
You hit on the key point. There is not data that meets the criteria of my sample filter. Upon further validation, when the filter gets a hit then it is applied. However, when the filter does not get a hit then all data is returned. I would think it would return a message saying no records or no data. It becomes useless to me if that is not a configurable option. Suggestions?
06-19-2024 06:13 PM
Just to follow up I added this to loop through the table and if there is an invalid account then send an error message. Issue is the evaluation really slows it down.
'Loop through DT and see if Emp_HC is present. If so error
Dim valueToFind As String = "Emp_HC"
'Loop through each row in the Datatable
For Each row As DataRow In dt_ICU.Rows
If Row("Account").ToString = valueToFind Then
Throw New XFUserMsgException(si, Nothing, Nothing, Me.m_MsgNoData)
Else
Dim timename As String = PlanTime
Dim filePath As String = Me.GetFilePath(si, api, args, timeName)
Dim csvHelper As New CsvHelper
Using writer As New StreamWriter(filePath, True)
writer.Write(csvHelper.CreateCsvString(dt_ICU))
End Using
End If
Next
06-19-2024 06:39 PM
I moved the Next and it is now working quick.
06-19-2024 04:21 AM
Assuming that there is data for that Account and UD1 combination, I don't see any reason why it shouldn't work. I had done similar filters and they are working fine in all versions upto 8.2.
Not that it would (in theory both are same) make any difference but try this. I had done most of my filters using IN operator:
Filter = "Account='A30000' And UD1 IN ('D1059', 'D1061', 'D1062')"
Best
Sai
06-19-2024 05:14 PM
Hello Sai,
There is no data that meets the criteria of my sample filter. Upon further validation, when the filter gets a hit then it is applied. However, when the filter does not get a hit then all data is returned. I would think it would return a message saying no records or no data. It becomes useless to me if that is not a configurable option. Suggestions?
06-20-2024 04:58 AM
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()
06-20-2024 03:53 AM
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