Queries on Listing fields and its filter
Hi Community,
I seek your assistance for below scenario.
Scenario-1: How to add filter or where clause while displaying data with listing fields ( I have not used Custom SQL listing to display this data).
For below table, I have created a Cube Student and I have created listing fields of the columns mentioned in the table. Also I have created the pivot and I am able to create the same table as shown below.
Now in another pivot, if I want to display all same detail with Student marks greater than 82. How to do that. Basically I want to know how to apply filter when using listing fields in a cube. (I also checked this can be done by using Custom SQL listing and adding my SQL query there but in that case, my scenario-2 is not working.
Scenario-2
Apart from this table, I also want to place a filter on Student Name (like shown below) so that end user can filter the student name based on data shown in table.
I am not able to provide the correct source to this filter because if I add source as Student Name (Dimension of Cube) to this filter, it will take all the Student names whereas I only want to display only these five name in this filter because data is shown for 5 students only.
Student Name Filter
.png)
.png)
Please assist!
Thanks in advance !!
Comments
For your first scenario, you can filter listing results by defining an additional filter on a measure. The documentation says that for a given measure, you can specify an extra filter that is added to the detail listing query, and records that do not meet that criterion are removed. This is configured in the measure definition as a Measure-Specific Listing Filter, where you set Operator and Value. For your case, if you want only students with marks greater than 82, you would use a measure based on the marks field and set the listing filter to > with value 82. [1][2]
The supported operators for this listing filter are <, <=, >, >=, <>, =, MAX, MIN. Also, the cube class supports this through the measure attributes listingFilterOperator and listingFilterValue. [1][2]
For filtering the pivot table itself by a measure value, the subject area must include a searchable measure. Then in the Analyzer you can use Filters → Advanced Options → Add Condition, select the measure from the end of the list, choose the operator, and enter the comparison value. The documentation gives the example of filtering all patients whose test score is 65 or higher. So for marks greater than 82, this approach applies if your marks measure is searchable. [3][2]
Note: The filter control is part of the Pivot Builder area, not part of the Pivot Preview area. This has two implications:
-
When you save the pivot table, the Analyzer saves any selections in the filter control. When you next open the pivot table, the Analyzer redisplays the filter control as you saved it.
-
The pivot table as seen in a dashboard is filtered as you saved it and does not include the filter control. (You use a separate mechanism to provide filters to users of your dashboards.) [3]
For your second scenario, the available documentation only states that if you drag a level such as Student Name into the Filters box or filter bar, the drop-down lets you select from the members of that level, with options such as All, selecting one or more members, excluding members, or choosing a range. It does not describe a way to make that level-based filter automatically show only the names currently visible in the listing table. [3]
The documentation does say that you can define a named filter, and that a named filter can be saved with the subject area and used in multiple pivot tables and in filter controls on dashboards. But the retrieved material does not provide a method for restricting a Student Name filter control so it only lists the five names currently shown in the table. [3]
So, based on the available material:
- To get only rows with marks greater than 82 in a listing, use a measure-specific listing filter on the marks measure, or use an advanced pivot filter on a searchable marks measure. [1][3][2]
- For a Student Name filter that only shows the currently displayed five names, the retrieved documentation does not provide a documented way to do that. [3]
Sources: