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...!                
Cube View
Dashboards
Parameters
Reports
  • Sai_Maganti's avatar
    Sai_Maganti
    2 years ago

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

  • FredLucas's avatar
    FredLucas
    7 months ago

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

     

     

Sai_Maganti's avatar
Sai_Maganti
Contributor II

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

SWilyums's avatar
SWilyums
Contributor
7 months ago

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?

  • FredLucas's avatar
    FredLucas
    Contributor III
    7 months ago

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