Drilldown in WebI

[Joe_Peters]

I can retrieve a WebI report that is in drill mode, but how do I enable hyperlinks to perform the drill action?  I see a bunch of JSONDATA stuff that appears to be related to drilling, but there is no embedded code to actually perform the linking.

[daniel_paulsen · Accepted solution]

Hi Joe,

The files required for those actions (drill dictionaries, image maps, contexts and outlines)  are not returned in the HTML when requesting the report in HTML format, so there's no way to enable that functionality.

To be able to interact with the report for actions such as drill up, drill down and drill by, you would need to redirect to opendocument and view the report in a fully featured viewer.

Dan

[Joe_Peters · Accepted solution]

Thanks, Dan.

Is the functionality going to be added in the future?  The openDocument solution does not work for us, as we are trying to rewrite an application that currently uses the Java SDK.

I find it strange that the REST API would include functions to call drilling actions, but with no support for linking.  What would be a use case where the drilling functions would be called without links?

[daniel_paulsen · Accepted solution]

I don't know if the functionality would be considered for the future.  It would be nice to have but then it adds a fairly high degree of complexity in some cases.

If you want to view the report in HTML, you need to export it to HTML.  Consider the case where you have an exported file (static HTML) sent to you through a schedule or you simple exported it to disk. If you had all of the active links for drilling, How would these links be able to post back to the server to get the additional drill views? (Where is the sessionID and how would you know which server to post back to?  You need a viewer, along with all of the javascript files supporting the interactions and an active session connected to the CMS.  The hyperlinks simply won't work in this scenario.

Now if you have a scenario where you have an active session in an application and you want to display all of the drill links in the HTML export, how would you trap the click event so as to keep track of the view in your application and grab the http response from the server and render the HTML back out to the browser?

In response to your question, I can't think of any use case where the functions would be called without links.  My guess is that from the development perspective, it was easiest to export static HTML content using the same HTML that the webi viewer uses, but with the dynamic content removed.  That's just a guess though as I'm not a developer.

If you can think of workflows that might work for both scenarios, please describe them so they can be considered.

Dan

[Joe_Peters · Accepted solution]

If you want to view the report in HTML, you need to export it to HTML.  Consider the case where you have an exported file (static HTML) sent to you through a schedule or you simple exported it to disk. If you had all of the active links for drilling, How would these links be able to post back to the server to get the additional drill views? (Where is the sessionID and how would you know which server to post back to?  You need a viewer, along with all of the javascript files supporting the interactions and an active session connected to the CMS.  The hyperlinks simply won't work in this scenario.

This exact scenario is supported today with the Java and WS SDKs. The generated HTML files include <SCRIPT> tags which reference static .js files on the web server.  The Javascript files then provide the necessary functionality for the drill functions.

Yes, the logon token needs to be maintained but the is easily implemented with a callback.  In fact, the REST API already supports this in the images that are generated for charts.

Now if you have a scenario where you have an active session in an application and you want to display all of the drill links in the HTML export, how would you trap the click event so as to keep track of the view in your application and grab the http response from the server and render the HTML back out to the browser?

The current SDKs support this by building in the Javascript events into the generate HTML; the links call Javascript functions in the static .js files.

So I'm not asking for anything that doesn't exist already in the other SDKs.  But I'm getting frustrated as I try to convert applications that work today in XI3 but have no means of conversion to BI4.

Our workflow is this: we have a very simple HTML page that is used to display a specific report.  The page uses web services to connect to BOE and retrieve the report (we are using CPU licenses so we are within the scope of our license).  The prompt panel and drilldown appearance is highly customized.  So openDocument would not work for us in this instance; it would display the report toolbar and not allow for our customization.  What we have today works well; I am just trying to get it to work in BI4.

[former_member197386 · Accepted solution]

Hello Joe,

How to you imagine a such feature through Raylight?

Does Raylight just need to reference a javascript filename (provided by user as a parameter) and perform callbacks to javascript function when drill actions are perform? In this case, would you be able to update the HTML content according to drill action result?

Actually, Raylight just offers an HTML output right now, not a dynamic HTML viewer. But, if you could enhance the current API to provide new extension capabilities, it would be great!

Regards,

Anthony

[Joe_Peters · Accepted solution]

I don't see any reason why it couldn't be implemented just as it is with the existing SDKs -- the REST host provides an HTML document that includes <SCRIPT> references to one or more .js files, and the drillable links have onClick events that call the appropriate functions.    

[Former Member · Accepted solution]

Hi, Joe Peters

    Now my partner want to implemented the drill function in HTML document ,which is exported from RESTFul.

    Do you resolve this issue?

    Thanks.

[Joe_Peters · Accepted solution]

Partly.  I wrote a lot of JQuery code to parse the report HTML and populate the hyperlinks.

[Former Member · Accepted solution]

Hi, Peter

   Can you share me these jQuery code?

[Joe_Peters · Accepted solution]

This is not fully tested:

var sessionToken;
var docID;
var reportID;
var drillMapping;
var lastPage=null;



$(document).ready(function() {
    var qs = getUrlVars();
    reportID = decodeURIComponent(qs["viewReport"]);

    // Done to prevent double-loading when the page is refreshed
    if(parent.parent.reportBodyOpening)
        parent.parent.reportBodyOpening=false;
    else
    {
        $("#WaitTable").hide();
        return false;
    }

    sessionToken = parent.getToken();
    docID = parent.getDocID();

    getReport(parent.getCurrentPage());
});

$(document).on("click","span.drillDown",function(ev) {
    var obj = $(ev.currentTarget);
    var drillInfo = obj.prop("drillInfo");
    var dillElement=null;
    var drillDownToID=null;
    var okToDrill = true;

    $.each(drillInfo,function() {
        if(drillDownToID==null) // first pass
        {
            drillDownToID = this.drillDownTo.id;
            drillElement = this.drillElement;
        }
        else // subsequent passes.  Check if the drillDownTo.id value is the same for all drill options.  If so, then we can drill on any one of them
        {
            if(drillDownToID != this.drillDownTo.id)
                okToDrill = false;
        }
    });

    if(!okToDrill)
    {
        alert("Multiple hierarchies.");
        return;
    }



    obj.closest("div").addClass("spinning");
    var postFilter = {};
    postFilter.filter = {};
    postFilter.filter.id=obj.prop("objectID");
    postFilter.filter.value=$.trim(obj.html());

    var postDrill = {};
    drillInstruction = (postDrill.instruction = {});
    drillInstruction["@type"]="Down";
    drillInstruction.elementId=obj.prop("parentID");
    drillInstruction.from={};
    drillInstruction.from.drillElement={};
    drillInstruction.from.drillElement.id=obj.prop("objectID");
    drillInstruction.from.drillElement.filterValue=$.trim(obj.html());
    drillInstruction.to={};
    drillInstruction.to.drillElement=drillElement; // from the hierarchy

    doDrill(postDrill);
});

$(document).on("click","img.drillUp",function(ev) {
    var obj = $(ev.currentTarget);
    var drillInfo = obj.prop("drillInfo")
    if(drillInfo.length==1) // only one hierarchy
    {
        var postDrill = {};
        drillInstruction = (postDrill.instruction = {});
        drillInstruction["@type"]="Up";
        drillInstruction.elementId=obj.prop("parentID");
        drillInstruction.from={};
        drillInstruction.from.drillElement={};
        drillInstruction.from.drillElement.id=obj.prop("objectID");
        $.trim(drillInstruction.from.drillElement.filterValue=obj.html());
        drillInstruction.to={};
        drillInstruction.to.drillElement=drillInfo[0].drillElement;
        doDrill(postDrill);
    }
    else
        alert("Multiple hierarchies.");

});

$(document).on("change",".DrillFilter",function(ev) {
    var obj = $(ev.currentTarget);

    var special = $(":selected",obj).prop("special");

    // Check if the "(remove)" option was selected
    if( special=="remove" )
    {
        new RESTAction(parent.getDocURL() + "/reports/" + reportID + "/driller/filters/" + obj.prop("filterID"))
            .setToken(sessionToken)
            .setRequestType("DELETE")
            .setInputData(JSON.stringify(postFilter))
            .setOnError(function (ex)
            {
                alert("Error removing drill filter: " + ex.data.errText);
            })
            .setOnSuccess(function(result)
            {
                lastPage=null;
                getReport(currentPage == -1 ? -1 : 1);
            })
            .go();   
    }
    else // update filter
    {
        var postFilterText;

        // If "All items" was selected, then leave the post empty
        if( special == "all" )
        {
            postFilterText="";
        }
        else
        {
            var postFilter = {};
            postFilter.filter = {};
            postFilter.filter.value=filterValue=obj.val();
            postFilterText = JSON.stringify(postFilter);
        }

        new RESTAction(parent.getDocURL() + "/reports/" + reportID + "/driller/filters/" + obj.prop("filterID"))
            .setToken(sessionToken)
            .setRequestType("PUT")
            .setInputData(postFilterText)
            .setOnError(function (ex)
            {
                alert("Error applying drill filter: " + ex.data.errText);
            })
            .setOnSuccess(function(result)
            {
                lastPage=null;
                getReport(currentPage == -1 ? -1 : 1);
            })
            .go();   
    }
});

function getReport(pageNum)
{
    elementData={}; // un-cache elements

    new RESTAction(parent.getDocURL() + "/reports/" + reportID + (pageNum==-1 ? "" : "/pages/" + pageNum))
        .setToken(sessionToken)
        .setFormat("html")
        .setOnError(function (ex)
        {
            if(ex.status==404)
            {
                alert("Page does not exist.");
                parent.setPageNav(currentPage);
            }
            else
                alert("Error getting report: " + ex.errText);
        })
        .setOnSuccess(function(body)
        {
            currentPage=pageNum;
            $("#ReportBody").html(body.data);
            parent.setPageNav(currentPage);

            // synchronous
            var docProperties = new parent.DocumentProperties();
            if(docProperties.getSuccess())
            {
                var partialRefresh = docProperties.getProperty("ispartiallyrefreshed")=="true";
                parent.frames["ReportToolbar"].showPartialResults(partialRefresh);
            }

            // Check if report is in drill mode
            checkIfDrillMode();
        })
        .go();   
}

function goToPage(pageNum)
{
    getReport(pageNum);
}

function checkIfDrillMode()
{
    new RESTAction(parent.getDocURL() + "/reports/" + reportID + "/driller")
        .setToken(sessionToken)
        .setOnError(function (ex)
        {
            // call will return 404 if not in drill mode
            if(ex.status!=404)
                alert("Error getting drill mode: " + ex.data.errText);
            else
                parent.showDrillMode(false);

        })
        .setOnSuccess(function(drillModeQuery)
        {
            if(drillModeQuery.data.driller.output==true)
            {
                parent.showDrillMode(true);

                // Get report hierarchies and display the drill content
                new RESTAction(parent.getDocURL() + "/reports/" + reportID + "/driller/hierarchies")
                    .setToken(sessionToken)
                    .setOnError(function (ex)
                    {
                        alert("Error getting hierarchies: " + ex.data.errText);
                    })
                    .setOnSuccess(function(result)
                    {
                        displayDrill(result.data);
                    })
                    .go();   

                // Doom any existing drill filters
                $("#FilterBar").children().addClass("DeleteMe");

                // Display any drill filters
                new RESTAction(parent.getDocURL() + "/reports/" + reportID + "/driller/filters")
                    .setToken(sessionToken)
                    .setOnError(function (ex)
                    {
                        alert("Error getting drill filters: " + ex.data.errText);
                    })
                    .setOnSuccess(function(result)
                    {
                        displayDrillFilters(result.data);
                    })
                    .go();                   
            }
        })
        .go();       
}

function displayDrill(hierarchies)
{
    fixArray(hierarchies.hierarchies,"hierarchy");

    // Create a mapping of drillable objects to the next lower level
    drillMapping={};
    $.each(hierarchies.hierarchies.hierarchy,function() {
        fixArray(this.drillelements,"drillelement");
        var elemCount = this.drillelements.drillelement.length;
        var parentDrillElement = this.drillelements.drillelement;

        var hierarchyInfo={name:this.name,id:this.id};

        $.each(this.drillelements.drillelement,function(index,obj) {
            if(drillMapping[obj.id]===undefined)
                drillMapping[obj.id]={};

            var thisDrillMap = drillMapping[obj.id];
            thisDrillMap.name=obj.name;

            if(index > 0) // can drill up
            {
                if(thisDrillMap.drillUp===undefined)
                    thisDrillMap.drillUp=[];

                thisDrillMap.drillUp.push(
                    {
                        hierarchy:hierarchyInfo,
                        drillUpTo:{id:parentDrillElement[index-1].id,name:parentDrillElement[index-1].name},
                        drillElement:{id:parentDrillElement[index-1].id,hierarchyId:hierarchyInfo.id}
                    }   
                );
            }
            if(index < elemCount-1) // can drill down
            {
                if(thisDrillMap.drillDown===undefined)
                    thisDrillMap.drillDown=[];

                thisDrillMap.drillDown.push(
                    {
                        hierarchy:hierarchyInfo,
                        drillDownTo:{id:parentDrillElement[index+1].id,name:parentDrillElement[index+1].name},
                        drillElement:{id:parentDrillElement[index+1].id,hierarchyId:hierarchyInfo.id}
                    }   
                );
            }
        });

    });

    new RESTAction(parent.getDocURL() + "/reports/" + reportID + "/elements")
        .setToken(sessionToken)
        .setOnError(function (ex)
        {
            alert("Error getting report elements: " + ex.data.errText);
        })
        .setOnSuccess(function(result)
        {
            var elements = result.data;
            // Get report elements that are VTables/HTables
            $.each(elements.elements.element,function() {
                var elemType = this["@type"];
                if(elemType=="VTable" || elemType=="HTable" || elemType=="XTable")
                {
                    var elemID = this.id;
                    new RESTAction(parent.getDocURL() + "/reports/" + reportID + "/elements/" + elemID)
                        .setToken(sessionToken)
                        .setOnError(function (ex)
                        {
                            alert("Error getting element: " + ex.data.errText);
                        })
                        .setOnSuccess(function(result)
                        {
                            if(elemType=="XTable")
                                doXTable(result.data);
                            else // VTable/HTable
                                doVTableHTable(result.data);
                        })
                        .go();
                }
            });           
        })
        .go();       
}

function displayDrillFilters(drillFilters)
{
    if(drillFilters.filters.filter!==undefined)
    {
        fixArray(drillFilters.filters,"filter");
        $.each(drillFilters.filters.filter,function()
        {
            var filterID = this.id;

            new RESTAction(parent.getDocURL() + "/reports/" + reportID + "/driller/filters/" + filterID)
                .setToken(sessionToken)
                .setOnError(function (ex)
                {
                    alert("Error drill filter: " + ex.data.errText);
                })
                .setOnSuccess(function(result)
                {
                    var drillFilter=result.data;
                    var assocDrillMapping = drillMapping[filterID];

                    // Create new dropdown
                    var newSelect = $("<select></select>")
                        .addClass("DrillFilter")
                        .prop("filterID",filterID);

                    $("<option></option>")
                        .prop("special","all")
                        .text(assocDrillMapping.name + " (All values)")
                        .appendTo(newSelect);

                    fixArray(drillFilter.filter.lov.values,"value");

                    $.each(drillFilter.filter.lov.values.value,function() {
                        var newOption = $("<option></option>")
                            .val(this.toString())
                            .text(this.toString())
                            .appendTo(newSelect);

                        if(this.toString() == drillFilter.filter.value)
                            newOption.prop("selected",true);
                    });

                    $("<option>(Remove)</option>")
                        .prop("special","remove")
                        .appendTo(newSelect);

                    $("#FilterBar").append(newSelect);                   
                })
                .go();
        });
    }

    // Remove old filters
    $(".DeleteMe","#FilterBar").remove();
}

function doVTableHTable(cell)
{
    // Look for elements that are in a hierarchy
    //fixArray(cell.element.content.axes,"axis");
    fixArray(cell.element.content.axes.axis.expressions,"formula");

    $.each(cell.element.content.axes.axis.expressions.formula,function(cellIndex,obj) {
        var objectID = obj["@dataObjectId"];

        // Check if the object exists in a hierarchy
        if(drillMapping[objectID]!==undefined)
        {
            var bodyBlockID=null;
            var headerBlockID=null;

            // Get associated body and header block IDs
            $.each(cell.element.content.layout.zone,function () {
                if(this["@verticalType"]=="Body" && this["@horizontalType"]=="Body")
                {
                    bodyBlockID = this.child[cellIndex]["@id"];
                }
                else if(this["@verticalType"]=="Header" || this["@horizontalType"]=="Header")
                {
                    headerBlockID = this.child[cellIndex]["@id"];
                }                       
            });

            // Check if object can drill down
            if(bodyBlockID != null && drillMapping[objectID].drillDown!==undefined)
            {
                // Apply drilldown link
                $("td[bid='" + bodyBlockID + "']").each(function() {
                    $("div > div > span",this)
                        .addClass("drillDown")
                        .prop("drillInfo",drillMapping[objectID].drillDown)
                        .prop("objectID",objectID)
                        .prop("parentID",cell.element.id);
                });
            }

            // Check if object can drill up
            if(headerBlockID != null && drillMapping[objectID].drillUp!==undefined)
            {
                // Apply drillup link to header
                $("[bid='" + headerBlockID + "']").each(function() {
                    var drillToField = drillMapping[objectID].drillUp[0].drillUpTo.name;
                    var img = $("<img style='float: right; cursor: pointer; text-decoration: underline;'>")
                        .attr("src","images/drill_up.png")
                        .attr("title","Drill Up to " + drillToField)
                        .addClass("drillUp")
                        .prop("drillInfo",drillMapping[objectID].drillUp)
                        .prop("objectID",objectID)
                        .prop("parentID",cell.element.id);
                    $(this).children("div").wrap("<div></div>");
                    $(this).children("div").prepend(img);
                });
            }
        }
    });
}

function doXTable(cell)
{
    // Look for elements that are in a hierarchy
    fixArray(cell.element.content.axes,"axis");

    $.each(cell.element.content.axes.axis,function (axisIndex,axis) {
        fixArray(axis.expressions,"formula");

        $.each(axis.expressions.formula,function(cellIndex,obj) {
            var objectID = obj["@dataObjectId"];

            // Check if the object exists in a hierarchy
            if(drillMapping[objectID]!==undefined)
            {
                var bodyBlockID=null;
                var headerBlockID=null;

                // Get associated body and header block IDs
                $.each(cell.element.content.layout.zone,function () {
                    fixArray(this,"child");
                    // If header cell is a row header, then we want the object that is a vertical type "Body" and horizontal type "Header"
                    if(axis["@role"] == "Row" && this["@verticalType"]=="Body" && this["@horizontalType"]=="Header")
                    {
                        headerBlockID = this.child[cellIndex]["@id"];
                    }                       
                    // If header cell is a column header, then we want the object that is a vertical type "Header" and horizontal type "Body"
                    else if(axis["@role"] == "Column" && this["@verticalType"]=="Header" && this["@horizontalType"]=="Body")
                    {
                        headerBlockID = this.child[cellIndex]["@id"];
                    }
                    // No body drilling for right now
                });

                // Check if object can drill down
                // Note that this is applied to the HEADER cell, not the BODY cell as with VTable/HTable
                if(headerBlockID != null && drillMapping[objectID].drillDown!==undefined)
                {
                    // Apply drilldown link
                    $("td[bid='" + headerBlockID + "']").each(function() {
                        $("div > div > span",this)
                            .addClass("drillDown")
                            .prop("drillInfo",drillMapping[objectID].drillDown)
                            .prop("objectID",objectID)
                            .prop("parentID",cell.element.id);
                    });
                }

                // Check if object can drill up
                if(headerBlockID != null && drillMapping[objectID].drillUp!==undefined)
                {
                    // Apply drillup link to header
                    $("[bid='" + headerBlockID + "']").each(function() {
                        var drillToField = drillMapping[objectID].drillUp[0].drillUpTo.name;
                        var img = $("<img style='float: right; cursor: pointer; text-decoration: underline;'>")
                            .attr("src","images/drill_up.png")
                            .attr("title","Drill Up to " + drillToField)
                            .addClass("drillUp")
                            .prop("drillInfo",drillMapping[objectID].drillUp)
                            .prop("objectID",objectID)
                            .prop("parentID",cell.element.id);
                        $(this).children("div").wrap("<div></div>");
                        $(this).children("div").prepend(img);
                    });
                }
            } // object in herarchy
        }); // each expression.formula
    }); // each axis
}

function doDrill(postInstruction)
{
    new RESTAction(parent.getDocURL() + "/reports/" + reportID + "/driller/instructions")
        .setToken(sessionToken)
        .setRequestType("POST")
        .setInputData(JSON.stringify(postInstruction))
        .setOnError(function (ex)
        {
            alert("Error performing drill: " + ex.errText);
        })
        .setOnSuccess(function(result)
        {
            lastPage=null;
            getReport(currentPage == -1 ? -1 : 1);
        })
        .go();
}