CubeGrid setHideEmptyFacetValues() not working as expected!

[pankaj.jain]

Hi,

We are evaluating version 6.1 of SmartGWT.
However, with the cubegrid, we are facing some issues when I try to hide the empty facets.

I have 2 facets as Quarter and Month, and for the invalid combinations, the empty row/column isn't fully hidden. The invalid facet titles still appear in the next facets area.
I understand that ideally this should be a single facet, but due to certain constraints on our side, we are evaluating both the combinations (Single facet vs 2 facets)

I have attached the screenshot for a sample scenario.

Current version: Isomorphic SmartClient/SmartGWT Framework (SNAPSHOT_v11.1d_2016-10-09/EVAL Deployment 2016-10-09)

Thanks!

[Isomorphic]

A screenshot is useless in terms of helping with this problem; in order to help, it would be necessary to see the complete code for the CubeGrid and the data provided.

So far, all we can do is remind you of the docs: for hideEmptyFacetValues to hide a given combination of facetValues, there must be no cellRecords that contain those facetValues.

Note that it is not sufficient for the value for the cellRecord to be null, the cellRecord must be entirely absent.

[pankaj.jain]

Thanks for the reply.
I have created a sample project, where I am still getting a similar problem.
Also, my data set does not have any entry in the table for invalid facet combinations (cell records/table rows are absent)

Please find the code below:

package com.pankaj.gwt.example.client.cube;

import com.google.gwt.core.client.EntryPoint;
import com.smartgwt.client.data.DSCallback;
import com.smartgwt.client.data.DSRequest;
import com.smartgwt.client.data.DSResponse;
import com.smartgwt.client.data.DataSource;
import com.smartgwt.client.types.DSOperationType;
import com.smartgwt.client.types.FetchMode;
import com.smartgwt.client.widgets.cube.CubeGrid;
import com.smartgwt.client.widgets.cube.Facet;
import com.smartgwt.client.widgets.cube.FacetValue;
import com.smartgwt.client.widgets.layout.VLayout;

import java.util.ArrayList;
import java.util.List;

public class AdvancedCubeGrid implements EntryPoint {

    private String f1Values, f2Values, f3Values, f4Values, f5Values;

    final String F1 = "F1";
    final String F2 = "F2";
    final String F3 = "F3";
    final String F4 = "F4";
    final String F5 = "TITLE";
    final String ALL = "ALL";

    private CubeGrid cube;
    private VLayout mainLayout;

    @Override
    public void onModuleLoad() {

        getCubeGrid();

        mainLayout = new VLayout();
        mainLayout.setWidth100();
        mainLayout.setHeight100();
        mainLayout.setMembersMargin(8);
        mainLayout.setLayoutMargin(10);

        mainLayout.draw();
    }

    private void getCubeGrid() {
        getCubeFacets();
    }

    private void getCubeFacets() {

        final DSRequest request = new DSRequest();
        request.setOperationType(DSOperationType.FETCH);
        request.setOperationId("fetchf1");
        request.setShowPrompt(true);
        request.setWillHandleError(true);
        final DataSource dataSource = DataSource.get("cubeData");

        dataSource.fetchData(null, new DSCallback() {
            @Override
            public void execute(DSResponse dsResponse, Object data, DSRequest dsRequestOld) {
                f1Values = CubeUtil.getStringFromData(dsResponse.getDataAsRecordList(), F1);
                request.setOperationId("fetchf2");
                dataSource.fetchData(null, new DSCallback() {
                    @Override
                    public void execute(DSResponse dsResponse, Object data, DSRequest dsRequestOld) {
                        f2Values = CubeUtil.getStringFromData(dsResponse.getDataAsRecordList(), F2);
                        request.setOperationId("fetchf3");
                        dataSource.fetchData(null, new DSCallback() {
                            @Override
                            public void execute(DSResponse dsResponse, Object data, DSRequest dsRequestOld) {
                                f3Values = CubeUtil.getStringFromData(dsResponse.getDataAsRecordList(), F3);
                                request.setOperationId("fetchf4");
                                dataSource.fetchData(null, new DSCallback() {
                                    @Override
                                    public void execute(DSResponse dsResponse, Object data, DSRequest dsRequestOld) {
                                        f4Values = CubeUtil.getStringFromData(dsResponse.getDataAsRecordList(), F4);
                                        request.setOperationId("fetchf5");
                                        dataSource.fetchData(null, new DSCallback() {
                                            @Override
                                            public void execute(DSResponse dsResponse, Object data, DSRequest dsRequest) {
                                                f5Values = CubeUtil.getStringFromData(dsResponse.getDataAsRecordList(), F5);
                                                createCube();
                                            }
                                        }, request);
                                    }
                                }, request);
                            }
                        }, request);
                    }
                }, request);
            }
        }, request);
    }

    private void createCube() {
        cube = new CubeGrid();
        cube.setWidth100();
        cube.setHeight100();
        cube.setDataFetchMode(FetchMode.LOCAL);
[B]        cube.setHideEmptyFacetValues(true);[/B]

        List<Facet> facetList = new ArrayList<>();
        facetList.add(getFacet(F1, "Origin", f1Values, true));
        facetList.add(getFacet(F2, "Destination", f2Values, true));
        facetList.add(getFacet(F3, "Quarter", f3Values, true));
        facetList.add(getFacet(F4, "Month", f4Values, true));
        facetList.add(getFacet(F5, "Values", f5Values, false));

        cube.setAutoFetchData(true);
        cube.setFacets(facetList.toArray(new Facet[facetList.size()]));
        cube.setValueProperty("VAL");

        cube.setRowFacets(F1, F2);
        cube.setColumnFacets(F3, F4, F5);

        DataSource dataSource = DataSource.get("cubeData");
        cube.setDataSource(dataSource);
        cube.setFetchOperation("fetchCubeData");

        mainLayout.addMember(cube);
    }

    private Facet getFacet(String facetName, String facetTitle, String facetValues, boolean isTree) {
        Facet facet = new Facet();
        facet.setId(facetName);
        facet.setTitle(facetTitle);
        facet.setIsTree(isTree);
        facet.setCollapsed(false);
        facet.setValues(getFacetValues(facetValues, !isTree));
        return facet;
    }

    private FacetValue[] getFacetValues(String facetValuesString, boolean isFixedFacet) {
        List<FacetValue> facetValues = new ArrayList<>();
        if (!isFixedFacet) {
            facetValues.add(new FacetValue("sum", ALL));
        }
        String[] facetValuesArray = facetValuesString.split(",");
        FacetValue facetValue;
        for (String facetString : facetValuesArray) {
            if (!isFixedFacet) {
                facetValue = new FacetValue(facetString, facetString, "sum");
            } else {
                facetValue = new FacetValue(facetString, facetString);
            }
            facetValues.add(facetValue);
        }

        for (FacetValue facet : facetValues) {
            facet.setWidth(100);
        }

        return facetValues.toArray(new FacetValue[facetValues.size()]);
    }
}

package com.pankaj.gwt.example.client.cube;

import com.smartgwt.client.data.RecordList;

public class CubeUtil {
    public static String getStringFromData(RecordList recordList, String propertyName) {
        StringBuilder stringBuilder= new StringBuilder();
        for(int i = 0 ; i < recordList.getLength(); i ++) {
            stringBuilder.append(recordList.get(i).getAttribute(propertyName));
            if (i < recordList.getLength() - 1) {
                stringBuilder.append(",");
            }
        }
        return stringBuilder.toString();
    }
}

and the ds.xml

<DataSource
        ID="cubeData"
        tableName="CUBE_DATA"
        serverType="sql"
        dbName="Oracle">
    <fields>
        <field name="F1" type="text"/>
        <field name="F2" type="text"/>
        <field name="F3" type="text"/>
        <field name="F4" type="text"/>
        <field name="TITLE" type="text"/>
        <field name="VAL" type="integer"/>
    </fields>

    <operationBindings>
        <operationBinding operationType="fetch" operationId="fetchCubeData"></operationBinding>

        <operationBinding operationType="fetch" operationId="fetchf1">
            <selectClause>
                DISTINCT F1
            </selectClause>
        </operationBinding>

        <operationBinding operationType="fetch" operationId="fetchf2">
            <selectClause>
                DISTINCT F2
            </selectClause>
        </operationBinding>

        <operationBinding operationType="fetch" operationId="fetchf3">
            <customSQL>
                SELECT DISTINCT F3
                FROM CUBE_DATA
                ORDER BY
                CASE F3
                WHEN 'Q1' THEN 1
                WHEN 'Q2' THEN 2
                WHEN 'Q3' THEN 3
                WHEN 'Q4' THEN 4
                END
            </customSQL>
        </operationBinding>

        <operationBinding operationType="fetch" operationId="fetchf4">
            <customSQL>
                SELECT DISTINCT F4
                FROM CUBE_DATA
                ORDER BY
                CASE F4
                WHEN 'JAN' THEN 1
                WHEN 'FEB' THEN 2
                WHEN 'MAR' THEN 3
                WHEN 'APR' THEN 4
                WHEN 'MAY' THEN 5
                WHEN 'JUN' THEN 6
                WHEN 'JUL' THEN 7
                WHEN 'AUG' THEN 8
                WHEN 'SEP' THEN 9
                WHEN 'OCT' THEN 10
                WHEN 'NOV' THEN 11
                WHEN 'DEC' THEN 12
                END
            </customSQL>
        </operationBinding>

        <operationBinding operationType="fetch" operationId="fetchf5">
            <selectClause>
                DISTINCT TITLE
            </selectClause>
        </operationBinding>
    </operationBindings>
</DataSource>

Attaching the screenshot and and sql file for the data.

Basically, I am looking for 2 things:
1. Being able to hide the facet combination (on the common axis) if they do not have any value. (Example, Quarter 1 should not show May or June if there is no corresponding row in DB)
2. Is there a way to provide the sum at various levels, kind of a roll-up of values to higher levels? (The screenshot shows empty values for the combination of 'ALL', i wanted it to be automatically summed up). Is this possible? (I read about setRollupValue(), but this doesn't seem to be what I am looking for with the current data set, or maybe I have not understood its correct usage!)

[Isomorphic]

We can take a look at this, however:

1. you are seeing headers that should be absent entirely shrunken to 1px instead. Make sure you do not have any external CSS in the project and make sure you are using the correct DOCTYPE (see FAQ). Having checked this, please report what browser you can reproduce this in - test more than one and list the affected ones

2. let us know if you can reproduce this in 6.0 as well - 6.1 is a development version, so reporting a bug against 6.0 receives much higher priority

3. for faster results, a test case that uses clientOnly DataSources or inlined data is always preferred over a test case that requires a SQL table to be set up against a particular SQL engine. This is also a lot simpler for you.

[Isomorphic]

Note also: the "Basic Cube" sample uses hideEmptyFacetValues, and isn't showing an issue like you describe (in either 6.0 or 6.1).

[pankaj.jain]

Hi,

I do not have any external CSS in the project.
I have tried this in our actual project, and also in a sample prject and I see similar issues.
I see this happening for latest 6.0 build as well.
Also, this seems to happen across browsers (FF, Chrome, IE11).
Any ideas?

Also, is it possible in the CubeGrid to roll up the values and show (for summation across different axes) without we having to do it at SQL level?

Thanks!

[Isomorphic]

We'll take a look at your test case and see if we can reproduce the problem.

About the summation question: we don't provide client-side summing because that would only work for "toy" datasets where the entire dataset can be loaded to the client in advance. In our experience, no one uses the CubeGrid for such tiny datasets, so such a feature adds no value beyond a demo.

You can either pre-calculate sums or do it on the fly via SQL or some other approach.

[pankaj.jain]

Hi,

Any update on this issue?
What we have noticed so far is that it happens only when data is pulled from DB.
This could be due to the delay since the data may take some time to arrive from DB while the cube is drawn!
Any thoughts?

Thanks!

[Isomorphic]

We have reproduced the bug, that it applies to second-level headers with load-on-demand only. We are working on it.

Regards
Isomorphic Software

[pankaj.jain]

Thanks for the update.

[pankaj.jain]

Any timelines by when this will be fixed?

[Isomorphic]

We've made a change to address this issue. Please try the next nightly build, dated October 26.

Regards
Isomorphic Software

[pankaj.jain]

Hi,

Yes, the build fixed the issue.
However, I am now faced with a different challenge.
I need to fetch the cube data for only the given cubeID (i.e. the primary key in the DB)

Thus, I changed my code a bit by adding:

cube.setInitialCriteria(getCriteria());
cube.setCriteria(getCriteria());

DSRequest dsRequest = new DSRequest();
dsRequest.setCriteria(getCriteria());

cube.fetchData(getCriteria(), new DSCallback() {
    @Override
    public void execute(DSResponse dsResponse, Object o, DSRequest dsRequest) {

    }
}, dsRequest);

and the getCriteria Method just adds a simple criteria for the id:

private Criteria getCriteria() {
    Criteria criteria = new Criteria();
    criteria.addCriteria("cube", CUBE_ID);
    return criteria;
}

and in the respective DMI, I add this:

dsRequest.addToCriteria("cubeID", valueMap.get("cube"));

This was the only way I could get the filtered data from the table for the Cube!
However, this, as the documentation mentions about 'setHideEmptyFacetValues' needing the whole data set to be loaded, which doesn't happen in this case as I filter only some data, seems to create a problem where April now starts appearing for Quarter1 even though there is no data for it!
Note that it works fine if the cube is opened expanded, but we would like to open it collapsed!

Is there any other way to fetch filtered data and still retain the goodness of setHideEmptyFacetValues on both the axes?

[Isomorphic]

You should apply the criteria 3 times, just setting it as initialCriteria is enough.

Even setting it 3 times should not create the issue you describe. It sounds like you may be returning data for those facetValues instead.

Since you have not provided either code or data we could use to check, a simple test is to *not* apply criteria to the grid, and simple write your DMI so that it applies the cubeId as additional criteria to the DSRequest, in your server side code. This way, the CubeGrid is totally unaware that criteria are being applied, so if you still have your issue, you know it's related to the data you are returning and not related to criteria applied to the CubeGrid.

[pankaj.jain]

Hi Isomorphic,

The original problem, which you had fixed in the build dated Oct, 26, was it pushed to the SmartGWT 6.0 branch as well?

I am able to reproduce the problem in the build:
Isomorphic SmartClient/SmartGWT Framework (v11.0p_2016-12-10/EVAL Deployment 2016-12-10)

but not in the build:
Isomorphic SmartClient/SmartGWT Framework (SNAPSHOT_v11.1d_2016-11-27/EVAL Deployment 2016-11-27)

Basically the branch 6.0 doesn't seem to have got the fix that was reported for 6.1!