cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rdegr Regular Visitor
Regular Visitor

in selector json what is the 't' attribute

"{"selector":"{\"data\":[\"{\\\"comp\\\":{\\\"k\\\":0,\\\"l\\\":{\\\"col\\\":{\\\"s\\\":{\\\"e\\\":\\\"qtEmergencyRecords\\\"},\\\"r\\\":\\\"Year\\\"}},\\\"r\\\":{\\\"const\\\":{\\\"t\\\":4,\\\"v\\\":2017}}}}\"]}","highlight":false}"


"{"selector":"{\"data\":[\"{\\\"comp\\\":{\\\"k\\\":0,\\\"l\\\":{\\\"col\\\":{\\\"s\\\":{\\\"e\\\":\\\"qtFacility\\\"},\\\"r\\\":\\\"Facility County\\\"}},\\\"r\\\":{\\\"const\\\":{\\\"t\\\":1,\\\"v\\\":\\\"Baker\\\"}}}}\"]}","highlight":false}"

 

Seems like some sort of type code but doesn't match anything I see in metadata.  Please advise.  I am trying to build a filter that lets me select table/column, then value, but the generated selectors build filters across all the columns I've added to the control. so I'd like to build simple filters as above manually.

1 ACCEPTED SOLUTION

Accepted Solutions
jppp
Advisor

Re: in selector json what is the 't' attribute

Hi @rdegr,

 

I had a similar request andwas able to solve it in the following was:

 

1. I create an array of custom identities, in this case it uses the category as text, but that can be changed to SQExprBuilder.number if needed.

let categoryIdentities = categories.map((category) => {
    let sqlExpr = powerbi["data"].SQExprBuilder.equal(dataView.metadata.columns[0].expr, powerbi["data"].SQExprBuilder.text(category));
    return powerbi["data"].createDataViewScopeIdentity(sqlExpr);
});

# ref: https://github.com/liprec/powerbi-boxWhiskerChart/blob/89179879a7209b030245fd05c317eee2034db394/src/boxWhiskerChart.ts#L260

2. When I loop thru the individual catagories (=i variable)I use the following code to create the correct SelectionId identifier:

let selectionId = new SelectionId({ data: [ categoryIdentities[i] ] }, false);

# ref: https://github.com/liprec/powerbi-boxWhiskerChart/blob/89179879a7209b030245fd05c317eee2034db394/src/boxWhiskerChart.ts#L282

I added links to my solution for more reference and hope this can also work for your visual.

 

-JP

16 REPLIES 16
Moderator v-viig
Moderator

Re: in selector json what is the 't' attribute

What is expected behavior for filtering?

 

Ignat Vilesov,

Software Engineer

 

Microsoft Power BI Custom Visuals

pbicvsupport@microsoft.com

rdegr Regular Visitor
Regular Visitor

Re: in selector json what is the 't' attribute

I want to first select a column from a dropdown list of the columns added to the control in edit mode, then select values from that column- I have that much working- then filter on the selected values for the selected column only.  The problem is that once I have multiple columns and switch between them the code generates filters that filter across multiple columns so I want to build my own filter string or otherwise only filter the selected column.  Here is my selection method

 

 

        public setSelectedByValues() {
            let arr:any[] = [];

            for (let i = 0; i < this.dropdownByValues.selectedOptions.length; i++) {
                arr.push(this.selectionIds[this.dropdownByValues.selectedOptions[i].value+'']);
            }

            this.selectionManager.clear().then(()=> {
                this.selectionManager.select(arr, false).then((ids: ISelectionId[])=> {
                    debugger;
                    this.selectionManager.applySelectionFilter();
                });
            });
                
        }

 and this.selectionIds populated as follows:

                    let cat = this.dataView.categorical.categories[index-1];

                    cat.values.forEach((val:any, valIndex:number) => {

                        if (!hash[val+'']) {
                            hash[val+''] = true;
                        
                            let optV:HTMLOptionElement = document.createElement("option");
                            optV.text = val;
                            optV.value = val;
            
                            this.dropdownByValues.options.add(optV);

                            this.selectionIds[val+''] = this.host.createSelectionIdBuilder()
                                .withCategory(cat, valIndex)
                                .createSelectionId();
                        }
                    });

I think the real issue may be in this code. Here is an example of a selector where all I want is to filter on year but I also get facility.

{"selector":"{\"data\":[\"{\\\"and\\\":{\\\"l\\\":{\\\"comp\\\":{\\\"k\\\":0,\\\"l\\\":{\\\"col\\\":{\\\"s\\\":{\\\"e\\\":\\\"qtEmergencyRecords\\\"},\\\"r\\\":\\\"Year\\\"}},\\\"r\\\":{\\\"const\\\":{\\\"t\\\":4,\\\"v\\\":2017}}}},\\\"r\\\":{\\\"comp\\\":{\\\"k\\\":0,\\\"l\\\":{\\\"col\\\":{\\\"s\\\":{\\\"e\\\":\\\"qtFacility\\\"},\\\"r\\\":\\\"Facility County\\\"}},\\\"r\\\":{\\\"const\\\":{\\\"t\\\":1,\\\"v\\\":\\\"Alachua\\\"}}}}}}\"]}

 

 

rdegr Regular Visitor
Regular Visitor

Re: in selector json what is the 't' attribute

When I manually build "selector" strings identical to the ones I get if I only have a single column added to the control, then I get 

 

Uncaught TypeError: e.hasIdentity is not a function

 

at

 

this.selectionManager.select(arr, false)

rdegr Regular Visitor
Regular Visitor

Re: in selector json what is the 't' attribute

Trying to build the whole selector object, but don't know what the classes are and still get the same error.

 

                    let cat = this.dataView.categorical.categories[index-1];

                    cat.values.forEach((val:any, valIndex:number) => {

                        if (!hash[val+'']) {
                            hash[val+''] = true;
                        
                            let optV:HTMLOptionElement = document.createElement("option");
                            optV.text = val;
                            optV.value = val;
            
                            this.dropdownByValues.options.add(optV);

                            // this.selectionIds[val+''] = this.host.createSelectionIdBuilder()
                            //     .withCategory(cat, valIndex)
                            //     .createSelectionId();

                            var expr:any = cat.source.expr;
                            var typ:any = cat.source.type;
                            var typVal = (typ.primitiveType == 1 ? "\"" + val + "\"" : val);
                            var dm:string = expr.source.entity + "." + expr.ref;

                            var obj:any = {
                                expr: {
                                    comparison: 0,
                                    kind: 13,
                                    left: { kind: 2, ref: expr.ref, source: expr.source },
                                    right: { kind:17, type: typ, value: typVal }
                                },
                                key: "{\"comp\":{\"k\":0,\"l\":{\"col\":{\"s\":{\"e\":\"" + expr.source.entity + "\"},\"r\":\"" + expr.ref + "\"}},\"r\":{\"const\":{\"t\":" + typ.primitiveType + ",\"v\":" + typVal + "}}}}",
                                kind: 1  
                            };

                            this.selectionIds[val+''] = {
                                highlight: false,
                                key: "{\"selector\":\"{\\\"data\\\":[\\\"{\\\\\\\"comp\\\\\\\":{\\\\\\\"k\\\\\\\":0,\\\\\\\"l\\\\\\\":{\\\\\\\"col\\\\\\\":{\\\\\\\"s\\\\\\\":{\\\\\\\"e\\\\\\\":\\\\\\\"" + expr.source.entity + "\\\\\\\"},\\\\\\\"r\\\\\\\":\\\\\\\"" + expr.ref + "\\\\\\\"}},\\\\\\\"r\\\\\\\":{\\\\\\\"const\\\\\\\":{\\\\\\\"t\\\\\\\":" + typ.primitiveType + ",\\\\\\\"v\\\\\\\":" + typVal + "}}}}\\\"]}\",\"highlight\":false}",
                                keyWithoutHighlight: "{\"selector\":\"{\\\"data\\\":[\\\"{\\\\\\\"comp\\\\\\\":{\\\\\\\"k\\\\\\\":0,\\\\\\\"l\\\\\\\":{\\\\\\\"col\\\\\\\":{\\\\\\\"s\\\\\\\":{\\\\\\\"e\\\\\\\":\\\\\\\"" + expr.source.entity + "\\\\\\\"},\\\\\\\"r\\\\\\\":\\\\\\\"" + expr.ref + "\\\\\\\"}},\\\\\\\"r\\\\\\\":{\\\\\\\"const\\\\\\\":{\\\\\\\"t\\\\\\\":" + typ.primitiveType + ",\\\\\\\"v\\\\\\\":" + typVal + "}}}}\\\"]}\"}",
                                selector: {
                                    data: [{
                                        obj
                                    }]
                                },
                                selectorsByColumn: {
                                    dataMap: {
                                    }
                                }
                            };

                            this.selectionIds[val+''].selectorsByColumn.dataMap[dm] = obj;
                        }
                    });

 

rdegr Regular Visitor
Regular Visitor

Re: in selector json what is the 't' attribute

OK, after much tracing through the code I am beginning to make some progress. The missing piece of my object construction was encoded values.  Unfortunately, what I find in https://github.com/deldersveld/PowerBI-visuals/blob/master/src/Clients/VisualsData/semanticQuery/pri... suggests I need to distinguish between "decimal" and "double" numeric types and nothing in the category source type makes that distinction.  I could very much use a mapping for the category.source.type.underlyingType values but I have not found it yet. 

 

                            if (cat.source.type.integer) {
                                obj.expr.right.valueEncoded = val + "L";
                            }
                            else if (cat.source.type.numeric) {
                                obj.expr.right.valueEncoded = val + "D"; // TODO decimal v double
                            }
                            else if (cat.source.type.text)
                            {
                                obj.expr.right.valueEncoded = "'" + val.replace("'", "''") + "'";
                            }
                            else
                            {
                                obj.expr.right.valueEncoded = typVal;
                            }

Complete construction that now gives desired behavior for one integer and one text column. Desired behavior being to ignore any other column but the selected column.

                    let cat = this.dataView.categorical.categories[index-1];

                    cat.values.forEach((val:any, valIndex:number) => {

                        if (!hash[val+'']) {
                            hash[val+''] = true;
                        
                            let optV:HTMLOptionElement = document.createElement("option");
                            optV.text = val;
                            optV.value = val;
            
                            this.dropdownByValues.options.add(optV);

                            // this.selectionIds[val+''] = this.host.createSelectionIdBuilder()
                            //     .withCategory(cat, valIndex)
                            //     .createSelectionId();

                            let expr:any = cat.source.expr;
                            let typ:any = cat.source.type;
                            let typVal = (typ.primitiveType == 1 ? "\"" + val + "\"" : val);
                            let dm:string = expr.source.entity + "." + expr.ref;

                            expr.source.accept = function(e,t) {
                                return e.visitEntity(expr.source, t);
                            }

                            

                            let obj:any = {
                                expr: {
                                    comparison: 0,
                                    kind: 13,
                                    left: { kind: 2, ref: expr.ref, source: expr.source, accept(e,t) { return e.visitColumnRef(obj.expr.left, t); } },
                                    right: { kind:17, type: typ, value: typVal, accept(e,t) { return e.visitConstant(obj.expr.right, t); } },
                                    accept(e,t) {
                                        return e.visitAnd(obj.expr, t);
                                    }
                                },
                                key: "{\"comp\":{\"k\":0,\"l\":{\"col\":{\"s\":{\"e\":\"" + expr.source.entity + "\"},\"r\":\"" + expr.ref + "\"}},\"r\":{\"const\":{\"t\":" + typ.primitiveType + ",\"v\":" + typVal + "}}}}",
                                kind: 1
                            };

                            if (cat.source.type.integer) {
                                obj.expr.right.valueEncoded = val + "L";
                            }
                            else if (cat.source.type.numeric) {
                                obj.expr.right.valueEncoded = val + "D"; // TODO decimal v double
                            }
                            else if (cat.source.type.text)
                            {
                                obj.expr.right.valueEncoded = "'" + val.replace("'", "''") + "'";
                            }
                            else
                            {
                                obj.expr.right.valueEncoded = typVal;
                            }

                            let selectorObj = {
                                highlight: false,
                                key: "{\"selector\":\"{\\\"data\\\":[\\\"{\\\\\\\"comp\\\\\\\":{\\\\\\\"k\\\\\\\":0,\\\\\\\"l\\\\\\\":{\\\\\\\"col\\\\\\\":{\\\\\\\"s\\\\\\\":{\\\\\\\"e\\\\\\\":\\\\\\\"" + expr.source.entity + "\\\\\\\"},\\\\\\\"r\\\\\\\":\\\\\\\"" + expr.ref + "\\\\\\\"}},\\\\\\\"r\\\\\\\":{\\\\\\\"const\\\\\\\":{\\\\\\\"t\\\\\\\":" + typ.primitiveType + ",\\\\\\\"v\\\\\\\":" + typVal + "}}}}\\\"]}\",\"highlight\":false}",
                                keyWithoutHighlight: "{\"selector\":\"{\\\"data\\\":[\\\"{\\\\\\\"comp\\\\\\\":{\\\\\\\"k\\\\\\\":0,\\\\\\\"l\\\\\\\":{\\\\\\\"col\\\\\\\":{\\\\\\\"s\\\\\\\":{\\\\\\\"e\\\\\\\":\\\\\\\"" + expr.source.entity + "\\\\\\\"},\\\\\\\"r\\\\\\\":\\\\\\\"" + expr.ref + "\\\\\\\"}},\\\\\\\"r\\\\\\\":{\\\\\\\"const\\\\\\\":{\\\\\\\"t\\\\\\\":" + typ.primitiveType + ",\\\\\\\"v\\\\\\\":" + typVal + "}}}}\\\"]}\"}",
                                selector: {
                                    data: []
                                },
                                selectorsByColumn: {
                                    dataMap: {
                                    }
                                },
                                getKey() {
                                    return selectorObj.key;
                                },
                                getKeyWithoutHighlight() {
                                    return selectorObj.keyWithoutHighlight;
                                },
                                getSelector() {
                                    return selectorObj.selector;
                                },
                                getSelectorsByColumn() {
                                    return selectorObj.selectorsByColumn;
                                },
                                hasIdentity() {
                                    return true;
                                }
                            };

                            selectorObj.selectorsByColumn.dataMap[dm] = obj;
                            selectorObj.selector.data[0] = obj;

                            this.selectionIds[val+''] = selectorObj;
                        }
                    });
jppp
Advisor

Re: in selector json what is the 't' attribute

Hi @rdegr,

 

I had a similar request andwas able to solve it in the following was:

 

1. I create an array of custom identities, in this case it uses the category as text, but that can be changed to SQExprBuilder.number if needed.

let categoryIdentities = categories.map((category) => {
    let sqlExpr = powerbi["data"].SQExprBuilder.equal(dataView.metadata.columns[0].expr, powerbi["data"].SQExprBuilder.text(category));
    return powerbi["data"].createDataViewScopeIdentity(sqlExpr);
});

# ref: https://github.com/liprec/powerbi-boxWhiskerChart/blob/89179879a7209b030245fd05c317eee2034db394/src/boxWhiskerChart.ts#L260

2. When I loop thru the individual catagories (=i variable)I use the following code to create the correct SelectionId identifier:

let selectionId = new SelectionId({ data: [ categoryIdentities[i] ] }, false);

# ref: https://github.com/liprec/powerbi-boxWhiskerChart/blob/89179879a7209b030245fd05c317eee2034db394/src/boxWhiskerChart.ts#L282

I added links to my solution for more reference and hope this can also work for your visual.

 

-JP

rdegr Regular Visitor
Regular Visitor

Re: in selector json what is the 't' attribute

Very cool.  I will try this out.  My way still has an issue of recordsets growing as more columns are added (something trying to cross-reference) which this may work around.

rdegr Regular Visitor
Regular Visitor

Re: in selector json what is the 't' attribute

Does not solve the cross-reference issue but does replace some ugly handrolled selector code of mine.  Thanks!

jppp
Advisor

Re: in selector json what is the 't' attribute

As far as I know you can add extra columns to the `sqlExpr` variable and combine them with `powerbi["data"].SQExprBuilder.and` method.

 

There is no need to construct this kind of filters/selectors by yourself as all the needed methods of `SQExprBuiler` are exposed to be used with a Custom Visual. Take a look at the repository of the old Power BI visuals: https://github.com/avontd2868/PowerBI-visuals/tree/master/src/Clients/Data/semanticQuery for more details.

 

-JP