Filter Multple Selections
From OpenI Wiki
| Table of contents |
|
1.1 row/column analysis - all selected |
[edit]
Scenario's
Scenario's below are based on Foodmart budget cube.
[edit]
row/column analysis - all selected
SELECT {[Measures].[Amount]} ON columns,
{[Store].[All Stores], [Store].[All Stores].[Canada], [Store].[All Stores].[Mexico], [Store].[All Stores].[USA]}
ON rows FROM [Budget] WHERE ([Time].[1998])
| Measures | |
| Store | Amount |
| All Stores | -$1,037,817.52 |
| Canada | -$29,052.00 |
| Mexico | -$790,921.84 |
| USA | -$217,843.68 |
- very typical analysis, no problems here.
- We have stores on the rows, and a measure on the column
[edit]
row/column analysis - US, Mexico, All
SELECT {[Measures].[Amount]} ON columns,
{[Store].[All Stores], [Store].[All Stores].[Mexico], [Store].[All Stores].[USA]}
ON rows FROM [Budget] WHERE ([Time].[1998])
Results in:
| Measures | |
| Store | Amount |
| All Stores | -$1,037,817.52 |
| Mexico | -$790,921.84 |
| USA | -$217,843.68 |
- Notice All Stores value represents all children, even though Canada has been deselected.
- Again, this is not the major concern for this features, many of our users will export to excel, to get them the expected total:
| Measures | |
| Store | Amount |
| Mexico+USA | -$1,008,765.52 |
| Mexico | -$790,921.84 |
| USA | -$217,843.68 |
- this case is not the feature request, just here for descriptive purposes
[edit]
Filter multiple selections on page level
- this is the real feature request
- Currently not possible from the UI: currently only a radio button is visible when you try to filter on the page level - only one member per dimension is allowed.
[edit]
What users want
- put a dimension on page level
- filter that selection using a checkbox (not a radio)
- filter by multple members in the same dimension, regardless of levels, totals, etc. (note you can do this in Msft Excel)
[edit]
MDX
[edit]
Page filtering currently: WHERE Clause implementation
Right now, when you move a dimension onto the page level, then filter: The resultant MDX is an MDX where clause. A where clause does not permit multiple members in the same dimension.
[edit]
Page filtering: WITH MEMBER implementation
If you write your own mdx, it is possible to accomplish multiple members on a page level filter.
WITH MEMBER [Store].[Some Stores] AS
'[Mexico] + [USA]'
SELECT {[Measures].[Amount]} ON columns,
{[Store].[Some Stores]} ON rows FROM [Budget]
WHERE ([Time].[1998])
| Measures | |
| Store | Amount |
| Some Stores | ($1,008,765.52) |
[edit]
Iterations
- verify: can you put multple members from the same dimension in an MDX where clause? If you canNOT:
- change page level UI to have checkboxes instead of radio buttons
- modify jpivot to use a WITH MEMBER clause, instead of WHERE - this is very tricky, and probably crosses many tiers in jpivot. This is not expected to be easy.
[edit]
Investigation
- MSAS 2000 does not support multiple member of a dimension in where
- SQL Server 2005 Analysis Services allows multiple members of a dimension placed inside curly bracket. e.g
SELECT {[Measures].[Amount]} ON columns,
{[Store].[All Stores], [Store].[All Stores].[Canada], [Store].[All Stores].[Mexico], [Store].[All Stores].[USA]}
ON rows FROM [Budget] WHERE {[Time].[1997],[Time].[1998]}
[edit]
UI/Olap Navigator
Initial state of navigator should look like this:
- columns
- Amount
- rows
- Time
- filter
- Store
- others
- select Stores
- Replacing radio buttons with check boxes you select Mexico and USA
- All Stores
- Canada
- X Mexico
- X USA
- All Stores
- MDX creates the WITH MEMBER clause, and automatically uses the WITH MEMBER in the WHERE Statement:
WITH MEMBER [Store].[Some Stores] AS '[Mexico] + [USA]' SELECT {[Measures].[Amount]} ON columns, {[Time].[1997], [Time].[1998]} ON rows FROM [Budget] WHERE ([Store].[Some Stores])
- Emulate excel's behavior!
[edit]
JPivot Investigation
- com.tonbeller.jpivot.navigator.Navigator WCF component renders the Olap Navigator
- By default, Navigator renders com.tonbeller.jpivot.navigator .HierarchyNavigator. When user selects any member, it renders com.tonbeller.jpivot.navigator.MemberNavigator
- Each item displayed in navigator can be com.tonbeller.jpivot.navigator.hierarchy .AxisCategory or com.tonbeller.jpivot.navigator.hierarchy .SlicerCategory. Both extends AbstractCategory class and AbstactCatagory implements SlicerCategory iterface. AxisCategory represents row or column members whereas SlicerCategory represents filter items
- Whether WCF renders option or check box for navigator members depends on their selection model. AxisCatagory uses SelectionModel.MULTIPLE_SELECTION and SlicerCatagory uses SelectionModel.SINGLE_SELECTION .
- Slicer category can be changed to allow SelectionModel.MULTIPLE_SELECTION – see com.tonbeller.jpivot.navigator.hierarchy .SlicerCategory .itemClicked() method. If done so, checkbox will be displayed for slicer rather than option button . Also you have to set AxisCatagory validation to allow multiple selection- see com.tonbeller.jpivot.navigator.hierarchy .SlicerCategory.validateSelection() method.
- MDX generated on multiple selections in slicer can not be executed by OLAP server. Here we need to change it so that the MDX is converted to ‘WITH’ member MDX.
- Jpivot generates ‘WITH’ using com.tonbeller.jpivot.olap.mdxparse.Formula class via com.tonbeller.jpivot.olap.mdxparse .ParsedQuery class.
- com.tonbeller.jpivot.olap.mdxparse.Formula can be added using com.tonbeller.jpivot.olap.mdxparse. ParsedQuery. addFormula() method
- com.tonbeller.jpivot.olap.mdxparse. ParsedQuery reference can be retrieved using com.tonbeller.jpivot.xmla.XMLA_Model.getPQuery() method. JPivot wcf components do not use XMLA_Model rather they use com.tonbeller.jpivot.tags .OlapModelProxy class. However XMLA_Model can be accessed using com.tonbeller.jpivot.tags .OlapModelProxy. getDelegate () method.
[edit]
Changes For 'WITH MEMBER' Based Solution
[edit]
Changing Navigator to Display CheckBox for slicer
- change com.tonbeller.jpivot.navigator.hierarchy.SlicerCategory.itemClicked() as follows :
public void itemClicked(RequestContext context, HierarchyItem item) {
// create a selection model
MemberSelectionModel selection = new MemberSelectionModel();
if (navi.getSlicerExtension() == null)
selection.setMode(SelectionModel.NO_SELECTION);
else
selection.setMode(SelectionModel.MULTIPLE_SELECTION);
selection.setSelection(item.getSlicerSelection());
navi.itemClicked(context, item, selection, false);
}
- change com.tonbeller.jpivot.navigator.hierarchy.SlicerCategory.validateSelection() as follows:
String validateSelection(HierarchyItem item, Collection selection) {
return null;
}
[edit]
Required Changes To Generate 'WITH MEMBER' MDX For Muliple Selection In Slicer
- Change com.tonbeller.jpivot.navigator.hierarchy.HierarchyNavigator#OkHandler.request() method as follows :
public void request(RequestContext context) throws Exception {
editing = false;
boolean valid = validate(context);
// the following will fire multiple ModelChangeEvents
for (Iterator it = categories.iterator(); it.hasNext();)
((AbstractCategory) it.next()).deleteDeleted();
for (Iterator it = categories.iterator(); it.hasNext();)
((AbstractCategory) it.next()).prepareApplyChanges();
List slicerMembersMdx = null;
for (Iterator it = categories.iterator(); it.hasNext();) {
//added for 'WITH MEMBER'
AbstractCategory cat = (AbstractCategory)it.next();
if(cat instanceof SlicerCategory) {
SlicerCategory scat = (SlicerCategory)cat;
List items = scat.getItems();
Iterator iter = items.iterator();
while(iter.hasNext()) {
HierarchyItem item = (HierarchyItem)iter.next();
List selection = item.getSlicerSelection();
if(selection.size()>1) {
slicerMembersMdx = new ArrayList();
Iterator selIter = selection.iterator();
List deleted = new ArrayList();
while(selIter.hasNext()) {
XMLA_Member member = (XMLA_Member)selIter.next();
deleted.add(member);
slicerMembersMdx.add(member.toMdx());
}
for(Iterator delIter = deleted.iterator(); delIter.hasNext();)
selection.remove(delIter.next());
}
}
}
///////////////////////
cat.applyChanges();
}
if (valid && hide)
setVisible(false);
if(slicerMembersMdx == null ) {
return;
}
//// added for 'WITH MEMBER'
XMLA_Model model = ((XMLA_Model)((CachingOlapModel)((OlapModelProxy)olapModel).getDelegate()).getDelegate());
ParsedQuery pquery = model.getPQuery();
String newMember = slicerMembersMdx.get(0).toString();
newMember = newMember.substring(0, newMember.lastIndexOf("."));
String names[] = new String[2];
names[0] = newMember;
names[1] = "[Some Member]";
Exp cids[] = new Exp[slicerMembersMdx.size()];
Iterator iter = slicerMembersMdx.iterator();
for(int i=0 ; iter.hasNext(); i++){
String name = (String)iter.next();
name = name.substring(name.lastIndexOf(".") + 1);
cids[i] = new CompoundId(name, false);
}
FunCall fun = new FunCall("+",cids, FunCall.TypeInfix);
pquery.addFormula(names, fun, new MemberProperty[0]);
//setup slicer for calculated member
String fullname = slicerMembersMdx.get(0).toString();
fullname = newMember + ".[Some Member]";
FunCall slicer = (FunCall)pquery.getSlicer();
Exp[] args = null;
int index = 0;
if(slicer != null ) {
index = slicer.getArgs().length;
args = new Exp[index +1 ];
System.arraycopy(slicer.getArgs(),0,args,0,index);
} else {
args = new Exp[1];
}
String []nameparts = fullname.split("\\.");
CompoundId cid = new CompoundId(nameparts[0], false);
for(int i=1 ; i < nameparts.length; i++) {
cid.append(nameparts[i], false);
}
args[index] = cid;
pquery.setSlicer(new FunCall("()", args, FunCall.TypeParentheses));
}
