Can somebody please explain how to use the function ? User guide doesn't say much except the syntax.
Thanks in advance.
topPercent([Product Group],10,[Net Sales])
How do you list this in you list on a crosstab? I have added it to my query but it still pulls the same results.
Hi,
Have a read of the Cognos 8 Dimensional Functions document. This gives a bit more detail with examples of what the function is designed to do.
http://www.ibm.com/developerworks/data/library/cognos/page82.html
Rgds
Rob
Thank you for the link. I wnet through the process but it doesnt seem to filter my results on the top percentage. I still get all records iin my crosstab. Any ideas?
try substituting in topcount to ensure it's working ok
topcount is easier to debug
as % may not be falling into range you want - have you displayed the % calc on the crosstab?
Hi cogusergirl,
I tried adding it to the crosstab (CT) but it erros out on my and wont run the CT.
I have changed it to a Top Count and I am getting the same error when I try to add it to the report.
Also, it does not filter the results as needed.
my statement looks like this in the data items;
topcount ([Commodity],1,[PDRCQT - Quantity Received])
The error is:
Invalid coercion from 'string' to 'memberSet' for '[Commodity]' in 'topcount([Commodity],1,[PDRCQT - Quantity Received])'
Lou
Hi Lou,
Just to confirm that you are using a dimensional data source such as a cube or DMR and not a relational data source.
If it is dimensional would you be able to provide a bit more information on what the data item "Commodity" is.
Many thanks
Rob
Rob,
It is a cube, the commodity is a six digit numeric code.
I am trying to get the top ten percent of my items based of the quantity purchased by commodity.
Setup looks like this:
Commodity Code: Item Code: Qty:
999888 000100 50
999888 000101 4
999888 000102 15
777444 102100 33
777444 102101 27
777444 102102 95
777444 102103 152
Hope this helps.
Lou
What exactly is Commodity in terms of the dimensional structure, though? Is it a level of a hierarchy? Is it an attribute of a level? Is it a defined set of members?
The TopCount (and TopPercent) functions require a set of members (or a level) to be used as the first argument, and the function then orders the members in the set and returns the top n based on the count or percentage you specify.
It sounds to me like Commodity might be an attribute? If so, this explains your coercion error - it is not legal (or even logical) to use a text attribute here - you need to get the top n % from a collection (set) of members.
Cheers!
MF.
Hi MFGF!
It is a level in the hierarchy that is higher than the item. I cannot get more techincal, my apologies.
Hi,
Your expression should look like this, in that case:
topPercent ([Your Package].[Your Dimension].[Your Hierarchy].[Your Commodity Level],10,[Your Qty Measure])
I just tried this on a local dimensional package, and it returns expected results.
Your list (or crosstab) which currently uses the Commodity Level as an item should instead use a Query Calculation coded with the above expression.
Hope that helps!
MF.