Forum Discussion

Akalya_N's avatar
Akalya_N
New Contributor
2 years ago

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()

     

     

  • 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_N's avatar
      Akalya_N
      New 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...!

    • SWilyums's avatar
      SWilyums
      Contributor

      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?

      • FredLucas's avatar
        FredLucas
        Contributor III

        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
        

         

         

  • 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

  • Sergey's avatar
    Sergey
    Contributor III

    Hello Akalya_N ,

    Can you please specify what do you mean by Threshold operation, and if it's for reporting only ?

    Regards,

     

    • Akalya_N's avatar
      Akalya_N
      New 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...