ParamCount function in a Query?

[marymichan]

Has anyone used the ParamCount report Function in Report Studio?  I am trying to see how many values the user has selected in the prompt and I would like to use this information in a query to calculate an avarage. For example if the user selects two values in a multiselect value prompt, in a query I would like calculate a measure / number of values selected.

If I create a dataitem "Number_of_prescriber" = [N_Product]  / ParamCount('N_Month')

where N_Month is a parameter associated in the value prompt, when I execute this report I have this error message:

   The expression operator 'ParamCount' is unsupported.

How can I do?

Thanks all,

[RKMI]

Hi Mary,

To get the number of selected items within a parameter, your layout calculation should be defined as follows:

ParamCount(ParameterName)

For example, if you wanted to know the number of countries selected by the user for the parameter name of parmCountry, your expression would be:

ParamCount("parmCountry")

In your case; "Number_of_prescriber" = [N_Product]  / ParamCount("parmN_month"); that means N_month is a param in your report.

Thanks,
RK

[marymichan]

Excuse me but I don't understand your solution. I cannot create a dataitem in a query with Paramcount function as in my message.

[RKMI]

Hi,

ParamCount can only be used in layout calculation. Sorry, I was not implying to create a new data item just help create the expression in the layout along with syntax. I know when you try to write the expression in a data item it complains for local processing which might be the reason creating a query calculation is also unsupported.

Thanks,
RK

[CognosPaul]

The ParamCount function can only be used in a report expression. So you can drag a layout calculation onto your page, and paste the expression in there. If that is not feasible (for instance, if you're trying to populate a value in a graph), you'll have to find a way to do it in the query. It is possible, but it depends heavily on what type of database you're using.

In the meantime, here's an example of ParamCount working against the sales and marketing cube in Cognos 10.1

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/7.0/" useStyleVersion="10" expressionLocale="en-us">
<modelPath>/content/folder[@name='Samples']/folder[@name='Cubes']/package[@name='Sales and Marketing (cube)']/model[@name='2008-07-25T15:28:38.072Z']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>
<layouts>
<layout>
<reportPages>
<page name="Page1">
<style>
<defaultStyles>
<defaultStyle refStyle="pg"/>
</defaultStyles>
</style>
<pageBody>
<style>
<defaultStyles>
<defaultStyle refStyle="pb"/>
</defaultStyles>
</style>
<contents><list horizontalPagination="true" name="List1" refQuery="Query1">



<style>
<CSS value="border-collapse:collapse"/>
<defaultStyles>
<defaultStyle refStyle="ls"/>
</defaultStyles>
</style>
<listColumns><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Revenue"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Revenue"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><staticValue>Text Item</staticValue></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><reportExpression>[Query1].[Revenue] / ParamCount('Parameter1')</reportExpression></dataSource></textItem></contents></listColumnBody></listColumn></listColumns></list><selectValue parameter="Parameter1" refQuery="Query2" multiSelect="true" selectValueUI="listBox" required="false"><useItem refDataItem="Product line"/></selectValue><promptButton type="next">
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="bp"/>
</defaultStyles>
</style>
</promptButton></contents>
</pageBody>
</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/></XMLAttributes><queries><query name="Query1"><source><model/></source><selection><dataItemMeasure name="Revenue"><dmMember><MUN>[sales_and_marketing].[Measures].[Revenue]</MUN><itemCaption>Revenue</itemCaption></dmMember><dmDimension><DUN>[sales_and_marketing].[Measures]</DUN><itemCaption>Measures</itemCaption></dmDimension><XMLAttributes><XMLAttribute name="RS_dataType" value="9" output="no"/></XMLAttributes></dataItemMeasure></selection><detailFilters><detailFilter use="optional"><filterExpression>[sales_and_marketing].[Products].[Products].[Product line] in (?Parameter1?)</filterExpression></detailFilter></detailFilters></query><query name="Query2"><source><model/></source><selection><dataItem name="Product line" aggregate="none"><expression>[sales_and_marketing].[Products].[Products].[Product line]</expression></dataItem></selection></query></queries></report>

[marymichan]

Thanks a lot PaulM. Your solution is perfect in a List object but it don't work in a combination chart object because I cannot insert text in a default measure (x-axis)  :'(

[CognosPaul]

Which DB are you using?

[marymichan]

The DB is Oracle 11g.

[CognosPaul]

I don't have access to Oracle, so I can't test this, but you should be able to do something like.

length(regexp_replace(#sq(promptmany('paramname','string','','','','|||'))#, '[^|||]'))  / 3

The ||| is added to the end of each option. Regexp_replace will replace all the of string that is not 3 pipes, it will then count the length of the string and divide by 3.

I don't think that the oracle regexp functions are recognized by Cognos, so you may have to wrap that function in curly braces to get it to work.