Skip to end of metadata
Go to start of metadata

*Author: Bruno Louifi Romaric Sokhan
*Submitted: 31 / 03 / 2010
*Related Links:

Use case :
For example, a hierarchy "COUNTRY" can have this structure

Level 00: WORLD
Level 01: +++++REGION (EMEA)
Level 02: +++++++++++CONTINENT (EUROPE)
Level 03: +++++++++++++++++++++ZONE (WEST_EUROPE)
Level 04: +++++++++++++++++++++++++++COUNTRY (FRANCE , BELGIUM etc..)

When a user opens a report with this hierarchy structure, he is at top level (Level 00). If he wants to go to Level 03 (WEST EUROPE), he has to drill through Level 01, 02 and 03. This can cause 3 drill action in the WebIntelligence interface, thus causing that many MDX roundtrips.

Goal of this code :
The idea is to leverage the Opendocument function with an SDK extension that will allow direct access to the desired level of hierarchy of a report, thus avoiding unnecessary roundtrips to the BW server, for example, open a webi report with the hierarchy level 03 directly instead of going through level 00,01,02

What the code does:

1) Get the hierarchy type by passing hierarchy variable through opendocument querystring
2) Get the desired drill level by passing drillTo variable through opendocument querystring
3) Passing those 2 variables to customDrillTo.jsp via sTarget parameter in opendocument
4) In customDrillTo.jsp, check if hierarchy variable is specified
5) We then must check on which dimension to drill from and to drill to.
6) If drillTo variable is not in the scope (in the query), we add it to the query
7) We run the query with the new drillTo dimension
8) We set the prompts by redirecting to processDocWithPrompts.jsp and set the strLoop flag to true
9) We search for a cross table and replace the initial top dimension to the one we want (
10) Apply the changes to the report and redirect to report.jsp

Example of use :

http://myserver:8080/OpenDocument/opendoc/openDocument.jsp?iDocID=ASQ_obRJjNxKr29Ybnh8Nis&sIDType=CUID&sType=wid&sRefresh=N&lsSEnter%20L00%20Country%3A=WORLD&lsSEnter%20L01%20Country%3A=EMEA&lsSEnter%20L02%20Country%3A=EUROPE&lsSEnter%20L03%20Country%3A=WEST_EUROPE&hierarchy=Country&drillTo=L03%20Country&sTarget=customDrillTo

How to install :

- Copy the below code in a file called customDrillTo.jsp
- Copy customDrillTo.jsp in Tomcat/webapps/AnalyticalReporting/viewers/cdz_adv
- Use an OpenDocument URL as above (adapted to your webi report of course)

-------------------------------------------------------------------------

<%@ include file="wistartpage.jsp" %>

<%@ page import="java.net.URLDecoder,java.net.URLEncoder" %>

<%

response.setDateHeader("expires", 0);


try

{

	String strEntry = requestWrapper.getQueryParameter("sEntry", true);

	String strHierarchy = requestWrapper.getQueryParameter("hierarchy", false, "");

	strHierarchy = URLDecoder.decode(strHierarchy, "UTF-8");


	String strReportId = requestWrapper.getQueryParameter("iReport", true);

	int iReport = Integer.parseInt(strReportId);

	String strLoop = requestWrapper.getQueryParameter("loop", false, "");

	DocumentInstance doc = reportEngines.getDocumentFromStorageToken(strEntry);

	Reports reports = doc.getReports();

	Report report = reports.getItem(iReport);


	String strDrillTo = requestWrapper.getQueryParameter("drillTo", true);

	strDrillTo = URLDecoder.decode(strDrillTo, "UTF-8");


	String strDrillFrom = "";


	if (report.getReportMode() == ReportMode.Analysis)

	{

		DrillInfo drillInfo = (DrillInfo) report.getNamedInterface("DrillInfo");


		String strDimensionFromID = null;

		String strDimensionToID = null;


		//Get selected hierarchy

		DrillHierarchies drillHierarchies = drillInfo.getDrillHierarchies();

		if (drillHierarchies != null)

		{

			DrillHierarchy hierarchy = null;

			for (int i=0; i<drillHierarchies.getCount(); i++)

			{

				DrillHierarchy dh = drillHierarchies.getItem(i);

				if (i == 0 && strHierarchy.equals(""))

				{
					//Default hierarchy
					hierarchy = dh;
					break;
				}				else if (dh.getName().equalsIgnoreCase(strHierarchy))

				{
					hierarchy = dh;
					break;
				}			}


			if (hierarchy != null)

			{

				System.out.println("hierarchy: " + strHierarchy);

				//drill through the hierarchy to locate drillFrom and drillTo dimensions

				DrillDimensions drillDimensions = hierarchy.getDrillDimensions();

				if (drillDimensions !=  null && drillDimensions.getCount() > 0)

				{

					for (int i=0; i<drillDimensions.getCount(); i++)

					{

						DrillDimension drillDimension = drillDimensions.getItem(i);

						if (i == 0)

						{
							//Drill From dimension
							//This dimension must be in the scope
							strDimensionFromID = drillDimension.getID();
							strDrillFrom = drillDimension.getName();
							System.out.println("dimensionFrom: " + strDrillFrom);
						}						else if (drillDimension.getName().equalsIgnoreCase(strDrillTo))

						{

							//Drill To dimension

							//Check if this dimension is in the scope

							System.out.println("dimensionTo: " + strDrillTo);

							strDimensionToID = drillDimension.getID();

							if (drillDimension.isInScope())

							{
								System.out.println(strDrillTo + " is in scope");
								strDimensionToID = drillDimension.getID();
							}							else

							{

								System.out.println("add " + strDrillTo + " into the dictionary");

								//Add this dimension the result Object

								DataProvider dp = doc.getDataProviders().getItem(0);

								Query query = dp.getQuery();

								DataSourceObject dsoTo = dp.getDataSource().getClasses().getChildByName(strDrillTo);


								if (dsoTo != null)

								{

									// Adding the

									query.addResultObject(dsoTo);

									System.out.println("run query");

									dp.runQuery();

									if (doc.getMustFillPrompts())

									{
										System.out.println("must answer prompts");
										strEntry = doc.getStorageToken();
										requestWrapper.setQueryParameter("sEntry", strEntry);
									}								}

							}

						}

					}

				}

			}

		}


		if (strLoop.equals("") && objUtils.hasRequestPromptsParam(request))

		{
System.out.println("redirect to processDocWithPrompts.jsp");
			requestWrapper.setQueryParameter("sTarget", "customDrillTo");
			requestWrapper.setQueryParameter("loop", "yes");
			%>

			<%
		}		if (strDimensionFromID != null && strDimensionToID != null)

		{

			//Replace the dimensionFrom with dimensionTo


			ReportExpression expFrom = null;

			ReportExpression expTo = null;


	        ReportDictionary rd = doc.getDictionary();

	       	for (int i = 0; i < rd.getChildCount(); i++)

	       	{

				ReportExpression exp = rd.getChildAt(i);

				if (strDimensionFromID.equals(exp.getID()))

				{
					expFrom = exp;
				}				else if (strDimensionToID.equals(exp.getID()))

				{
					expTo = exp;
				}	       	}


			//Find Default block in the report

			ReportElementContainer reportStructure = doc.getStructure();

			ReportContainer repConrtainer = (ReportContainer) reportStructure.getChildAt(iReport);

			ReportBody repBody = repConrtainer.getReportBody();


			int count = repBody.getChildCount();

			for (int i = 0; i < count; i++)

			{

				ReportElement child = (ReportElement) repBody.getChildAt(i);

				if (child instanceof ReportBlock) {

					//Keep first block

					ReportBlock block = (ReportBlock) child;

					Representation representation = block.getRepresentation();

		            BlockType blockType = representation.getType();


		            if (blockType instanceof TableType && blockType == TableType.XTABLE)

		            {
System.out.println("CrossTable found");
		            	//boolean found = false;
						BlockAxis axis =  block.getAxis(TableAxis.VERTICAL);
System.out.println("remove " + strDrillFrom);
		            	//Remove From dimension
		            	axis.removeExpr(expFrom);
System.out.println("insert " + strDrillTo);
		            	//Add To dimension
		            	axis.insertExpr(expTo, 0);
		            }	            }

			}

		}

	}



	//Apply changes

	doc.applyFormat();

	strEntry = doc.getStorageToken();

	requestWrapper.setQueryParameter("sEntry", strEntry);


	%>

	<jsp:forward page="report.jsp"></jsp:forward>

	<%


}

catch(Exception e)

{
	objUtils.displayErrorMsg(e, "_ERR_DRILL", true, out, session);
}%>






1 Comment

  1. Unknown User (g6fuitf)

    Romaric, your post is exactly what we are looking for! we have a DashBoard in Xcelsius with links to a Webi report. we need to change dimensions in Webi report based on the parameters passed from the DashBoard. I tried your code but can not make it work.

    This is the link:

    ="<a href=http://bodev/OpenDocument/opendoc/openDocument.jsp?sWindow=New&sPath=%5BDormanSales%5D&sReportName=OI+Sales&sDocName=Sales+-+KPI+Analysis&appKind=InfoView&lsSEnter+Director%3A="URLEncode([SelectedDirector])"&lsSEnter+Salesperson%3A="URLEncode([SelectedSalesPerson])"&isApplication=true&sType=wid&lsSEnter+FiscalYear%3A="URLEncode([SelectedYear])"&lsSEnter+ManagedLevel%3A="URLEncode([SelectedManagedLevel])"&lsSEnter+Company%3A="URLEncode([SelectedCompany])"&Hierarchy=Customer&drillTo=BillToName&sTarget=customDrillTo">Details</a>"

    it looks like the customDrillTo gets executed - i can see it in the log, but i don't see report changing the dimension to "BillToName". Not sure how to troubleshoot this problem. Please, help.

    Thanks in advance.

    Galina