Cognos SDK -- cube drill down

Here is a C# Cognos SDK code snippet that shows the ability to drill down into a cube report by using the Cognos SDK.  Take note of the bolded code lines below.  You will need to use the Report Service's .drill method and pass in values for the drillOptionParameterValues property.

 

simpleParmValueItem item = new simpleParmValueItem();
                item.use = "[great_outdoors_company].[Years].[Years].[Year]->:[PC].[@MEMBER].[20050101-20051231]";
                item.inclusive = true;

 

option[] drillOptions = new option[1];
                drillOptionParameterValues d1 = new drillOptionParameterValues();
                d1.name = drillOptionEnum.down;
                d1.value = parameters;
                drillOptions[0] = d1;

 

response = this.rptService.drill(response.primaryRequest, parameters, drillOptions);

 

 

 

 

private void GoToDrillDown(reportService1 rptService)
        {
            if (this.rptService == null)
            {
                this.rptService = rptService;
            }
            String reportPath = "/content/package[@name='Great Outdoors Company']/report[@name='MyTestReport']";
           String savePath = "c:\\temp";
            parameterValue[] parameters = new parameterValue[1];
            asynchReply response;
            searchPathSingleObject reportPathObj = new searchPathSingleObject();
            reportPathObj.Value = reportPath;
            try
            {
                option[] runOptions = new option[6];

                runOptionBoolean saveOutput = new runOptionBoolean();
                saveOutput.name = runOptionEnum.saveOutput;
                saveOutput.value = false;
                runOptions[0] = saveOutput;

                // Specify the output format.
                runOptionStringArray outputFormat = new runOptionStringArray();
                outputFormat.name = runOptionEnum.outputFormat;
                outputFormat.value = new String[] { "HTML" };
                runOptions[1] = outputFormat;

                //Set the report not to prompt as we pass the parameters if any
                runOptionBoolean rop = new runOptionBoolean();
                rop.name = runOptionEnum.prompt;
                rop.value = false;
                runOptions[2] = rop;

                runOptionInt maxRows = new runOptionInt();
                maxRows.name = runOptionEnum.verticalElements;
                maxRows.value = 20;
                runOptions[3] = maxRows;

                option[] drillOptions = new option[1];
                drillOptionParameterValues d1 = new drillOptionParameterValues();
                d1.name = drillOptionEnum.down;
                d1.value = parameters;
                drillOptions[0] = d1;

                //Set the option to always have the primaryRequest in the response
                asynchOptionBoolean includePrimaryRequest = new asynchOptionBoolean();

                includePrimaryRequest.name = asynchOptionEnum.alwaysIncludePrimaryRequest;
                includePrimaryRequest.value = true;
                runOptions[4] = includePrimaryRequest;

                runOptionLanguageArray roOutputFormat = new runOptionLanguageArray();
                roOutputFormat.name = runOptionEnum.outputLocale;
                String[] fmt = { "en-us" };
                roOutputFormat.value = fmt;
                runOptions[5] = roOutputFormat;

                //Run the report
                response = this.rptService.run(reportPathObj, new parameterValue[] { }, runOptions);
                //If response is not immediately complete, call wait until complete
                if (!(response.status == (asynchReplyStatusEnum.complete)))
                {
                    while (!(response.status == asynchReplyStatusEnum.complete))
                    {
                        //before calling wait, double check that it is okay
                        if (hasSecondaryRequest(response, "wait"))
                        {
                            response =
                             this.rptService.wait(
                               response.primaryRequest,
                               new parameterValue[] { },
                               new option[] { });
                        }
                        else
                        {
                            Console.Write("Error: Wait method not available as expected.");
                            return;
                        }
                    }
                    //check if output is ready

                    if (outputIsReady(response))
                    {
                        response =
                         this.rptService.getOutput(response.primaryRequest, new parameterValue[] { },
                          new option[] { });
                    }
                    else
                    {
                        Console.Write("output is not ready!");
                    }
                }

                String data = getOutputPage(response);
                // Write the report output to file system
                Console.Write("Writing the output of the original report..");

                StreamWriter sw = File.CreateText(savePath + "\\original.html");
                sw.Write(data);
                sw.Flush();
                sw.Close();

                //Drill down in the report
                //set up drill down parameter.
                simpleParmValueItem item = new simpleParmValueItem();
                item.use = "[great_outdoors_company].[Years].[Years].[Year]->:[PC].[@MEMBER].[20050101-20051231]";
                item.inclusive = true;

                parmValueItem[] pvi = new parmValueItem[1];
                pvi[0] = item;

                parameters[0] = new parameterValue();
                parameters[0].name = "Year";
                parameters[0].value = pvi;

                response = this.rptService.drill(response.primaryRequest, parameters, drillOptions);

                if (!(response.status == (asynchReplyStatusEnum.complete)))
                {
                    while (!(response.status == asynchReplyStatusEnum.complete))
                    {
                        //before calling wait, double check that it is okay
                        if (hasSecondaryRequest(response, "wait"))
                        {
                            response =
                             this.rptService.wait(
                               response.primaryRequest,
                               new parameterValue[] { },
                               new option[] { });
                        }
                        else
                        {
                            Console.Write("Error: Wait method not available as expected.");
                            return;
                        }
                    }
                    //check if output is ready

                    if (outputIsReady(response))
                    {
                        response =
                         this.rptService.getOutput(response.primaryRequest, new parameterValue[] { },
                          new option[] { });
                    }
                    else
                    {
                        Console.Write("output is not ready!");
                    }
                }

                data = getOutputPage(response);
                // Write the report output to file system
                Console.Write("Writing the output of the drill down report..");

                StreamWriter sw2 = File.CreateText(savePath + "\\drilldown.html");
                sw2.Write(data);
                sw2.Flush();
                sw2.Close();

                // release the conversation to free resources.
                this.rptService.release(response.primaryRequest);

            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message.ToString());
                Console.WriteLine(ex.StackTrace);

            }
        }

Cognos 8 SDK -- prompt page with optional parameters

If you need to execute a Report Studio report that has optional parameters defined on the prompt page then you should code your Cognos SDK application to pass an empty parameter instead of an empty string.


// parameter values
ParmValueItem pvi[] = new ParmValueItem[1];

// If you pass an empty string to an optional parameter
// it will assume that you want to match an empty string in
// the filter. To get the desired behaviour, pass an empty
// parameter value
if (!inputValue.equalsIgnoreCase("")) pvi[0] = item1;

// Assign the values to the parameter.
params[i] = new ParameterValue();
params[i].setName(prm[i].getName());
params[i].setValue(pvi);

display the contents of a folder in the Public Folder using C# and SDK

Here is the content store request in order to find all of the reports that exist under a specified folder.

baseClass[] bc = crn.query( "/content/folder[@name='ABCDEFG']//report", props, s, qo );

Cognos SDK Guide by BI Centre Sample?

Hello, did you want to have a quick preview of the guide before purchasing? If so then send us an email requesting your free sample of the guide today.

Output.aspx.cs -- Displaying the report results

Now that the end user is able to interact with the report’s prompt page we need to execute the report and display the report values to our Output.aspx page. The Output.aspx.cs logic will contain a reference to the CognosSDK.cs function named resolvePrompts(). This function is used to determine the prompt type and prompt value that was executed on the report’s prompt page. These parameter values are then used to satisfy the parameter value collection that is passed in to the executeReport().


try
{
_frmData = Request.Form;
_selectedPkg = _frmData.GetValues("hidPkgName").GetValue(0).ToString();
_selectedRpt = _frmData.GetValues("hidRptName").GetValue(0).ToString();
_path = "/content/package[@name='" + _selectedPkg + "']/report[@name='" + _selectedRpt + "']";

asynchDetail[] _paramDetail = _cogSDK.GetParams(_selectedPkg,_selectedRpt,_rptService);
asynchDetailParameters _params = (asynchDetailParameters)_paramDetail[0];
int _pCount =_params.parameters.Length;

parameterValue[] _pv = _cogSDK.resolvePrompts(_frmData, _pCount);
string[] reportFormat = {"HTML"};

strHTML = _cogSDK.executeReport(_path, _pv, _rptService, reportFormat, false);
Response.Write(strHTML);
}

catch(Exception ex)
{
_ErrMsg = ex.Message.ToString();
Response.Write(_ErrMsg);
}

return;



We have already seen that the executeReport() will return a string variable value of the report’s output. This is achieved by applying an asynchReply variable to the Report Service’s .run() method.

try
{
spSingle.Value = reportSearchPath;
asynchReply C8reply = reportService.run(spSingle, pv ,arrRunOpts);

if (C8reply != null)
{
strHTML = getOutputPage(C8reply);

return strHTML;
}

else
{
return null;
}

}
catch (SoapException exSoap)
{
ExHandler exCognos = new ExHandler(exSoap);
return _ErrMsg = exCognos.Details + " :-: " + exCognos.Message + " :-: " + exCognos.Severity + " :-: " + exCognos.ErrorCode;
}
catch (Exception ex)
{
return _ErrMsg = ex.Message.ToString();
}


Output.aspx.cs will finally pass this string value to the page’s Reponse.Write() and render the dataset to the end user.