Webi Reports can have hierarchical list of values. E.g. County Name is level one and City Name is level two prompt. Retrieving all the nested list of values in the hierarchical prompts is tricky. You can easily get level one of prompt using Report Engine SDK. Here is a CS.NET and Java Report Engine SDK code that retrieves all the hierarchical list of values for prompts in a Webi report. Such prompts have to be treated it like a nested prompt and you have to explicitly set the prompt value for each nested prompt. If you do not call SetNestedPrompts(), you won’t get the list of values for the parent. The sample uses recursion retrieving all LOVs for a specific prompt in the hierarchy of prompts and goes to next level. bq. CS.NET CODE<br /><textarea cols="75" rows="10">using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using CrystalDecisions.Enterprise;
using BusinessObjects.Enterprise.Desktop;
using BusinessObjects.ReportEngine;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
EnterpriseSession es = new SessionMgr().Logon("<username>","<password>","<cmsname>","secEnterprise");
EnterpriseService eService = es.GetService("InfoStore");
InfoStore oInfoStore = new InfoStore(eService);
InfoObjects oInfoObjects = oInfoStore.Query("select * from ci_infoobjects where si_name='<reportname>' and si_instance=0 and si_kind='webi'");
InfoObject oInfoObject = oInfoObjects[1];
ReportEngines repEngines = (ReportEngines) es.GetService("ReportEngines").Interface;
IReportEngine repEngine = repEngines.getService(ReportEngineType.WI_ReportEngine);
IDocumentInstance docInstance = repEngine.OpenDocument(oInfoObject.ID);
IPrompts prompts = docInstance.GetPrompts();
foreach (IPrompt prompt in prompts)
{
ILov lov = prompt.Lov;
ListNestedPrompts(lov, Response, 1);
}
docInstance.CloseDocument();
es.Logoff();
}
public static void ListNestedPrompts(ILov lov, System.Web.HttpResponse Response, int nestLevel)
{
Response.Write("<br>Nesting Level = " + nestLevel + "<br>");
if (lov.MustFillNestedPrompts)
{
IPrompts nestedPrompts = lov.NestedPrompts;
foreach (IPrompt nestedPrompt in nestedPrompts)
{
IValues values = nestedPrompt.Lov.GetAllValues();
string[] setVal = new string[values.Count];
for (int j = 0; j < values.Count; j++)
{
Response.Write(values[j] + "<br>");
setVal[j] = values[j];
}
nestedPrompt.EnterValues(setVal);
lov.SetNestedPrompts();
ListNestedPrompts(nestedPrompt.Lov, Response, nestLevel + 1);
}
}
else
{
foreach (ValueFromLov value in lov.GetAllValues())
{
Response.Write(value.Value + "<br>");
}
}
}
}
</textarea>
<blockquote>Java CODE<br /><textarea cols="75" rows="10"><%@ page import="com.crystaldecisions.sdk.framework.*,
com.crystaldecisions.sdk.occa.infostore.*,
com.businessobjects.rebean.wi.*"
%>
<%
IEnterpriseSession oEnterpriseSession = CrystalEnterprise.getSessionMgr().logon("<username>", "<password>","<cmsname>","secEnterprise");
ReportEngines repEngines = (ReportEngines) oEnterpriseSession.getService("ReportEngines");
ReportEngine repEngine = (ReportEngine) repEngines.getService(ReportEngines.ReportEngineType.WI_REPORT_ENGINE);
IInfoStore oInfoStore = (IInfoStore)oEnterpriseSession.getService("","InfoStore");
IInfoObjects oInfoObjects = oInfoStore.query("select * from ci_infoobjects where si_name='<reportname' and si_instance=0 and si_kind='webi'");
IInfoObject oInfoObject = (IInfoObject) oInfoObjects.get(0);
DocumentInstance docInstance = repEngine.openDocument(oInfoObject.getID());
Prompts prompts = docInstance.getPrompts();
for(int i=0; i<prompts.getCount(); i++)
{
Prompt prompt = prompts.getItem(i);
Lov lov = prompt.getLOV();
ListNestedPrompts(lov, out, 1);
}
docInstance.closeDocument();
oEnterpriseSession.logoff();
%>
<%! public void ListNestedPrompts(Lov lov, javax.servlet.jsp.JspWriter out, int nestLevel) throws java.io.IOException
{
out.print("<br>Nesting Level = " + nestLevel + "<br>");
if (lov.mustFillNestedPrompts())
{
Prompts nestedPrompts = lov.getNestedPrompts();
for(int i=0; i< nestedPrompts.getCount(); i++)
{
Prompt nestedPrompt = nestedPrompts.getItem(i);
Values values = nestedPrompt.getLOV().getAllValues();
String[] setVal = new String[values.getCount()];
for (int j = 0; j < values.getCount(); j++)
{
out.print(values.getValue(j) + "<br>");
setVal[j] = new String(values.getValue(j));
}
nestedPrompt.enterValues(setVal);
lov.setNestedPrompts();
ListNestedPrompts(nestedPrompt.getLOV(), out, nestLevel+1);
}
}
else
{
Values values = lov.getAllValues();
for(int i=0; i< values.getCount(); i++)
{
out.print(values.getValue(i) + "<br>");
}
}
}
%>
</textarea></blockquote>
