Hey fellow Cognos users,
I want to create a textbox prompt that filters top items depending on what the user will input. For example "10", then it will show top 10 items.
The data item looks like this:
topcount([Item Group];?tophierarki?;[Volumes Current+Last Month])
Any smart solutions/tips will be greatly appreciated.
Live well,
Best regards,
Oscar
Nevermind solved it
Quote from: oscarca on 18 Jul 2018 04:10:33 AM
Hey fellow Cognos users,
I want to create a textbox prompt that filters top items depending on what the user will input. For example "10", then it will show top 10 items.
The data item looks like this:
topcount([Item Group];?tophierarki?;[Volumes Current+Last Month])
Any smart solutions/tips will be greatly appreciated.
Live well,
Best regards,
Oscar
Just replace the ?tophierarki? parameter with a prompt macro, eg #prompt('tophierarki','token','10')#
Cheers!
MF.
Hey MFGF,
I'll try what you are suggesting since my solution didn't work in the end, got this error:
The MDX function CURRENTMEMBER failed because the coordinate for....
So I tried what you posted MFGF but still got this error:
Java_com_cognos_xqe_data_providers_olap_odbo_ODBOConnection_cExecuteMDX]: Query (8, 5617) The MDX function CURRENTMEMBER failed because the coordinate for the 'ItemGroupLevel2IDName' attribute contains a set.
Quote from: oscarca on 18 Jul 2018 06:42:47 AM
So I tried what you posted MFGF but still got this error:
Java_com_cognos_xqe_data_providers_olap_odbo_ODBOConnection_cExecuteMDX]: Query (8, 5617) The MDX function CURRENTMEMBER failed because the coordinate for the 'ItemGroupLevel2IDName' attribute contains a set.
The problem looks like it is with the [Item Count] item. Is this a calculation of some sort? Does your expression work if you just use a value instead of the prompt?
So I resolved all the errors but performance issues has occured.
When displaying the top item groups or the top brands by using the top count() and the prompt parameter the report loads decently fast. But when choosing to display the lowest level in the hierachy i.e. top items the report just loads forever.
topcount([Item];?topItem?;[Volumes Current+Last Month])
The datasource is a SSAS cube. I have done the same logic with a previous Transformer cube and it generates the items (lowest level) relatively fast.
Is the top count sorting for items to heavy for the report to perform ?
topCount should work fine in SSAS. There are a few possible reasons the report is running slow.
Are there any detail filters? Slicers? What are the expression behind [Item] and [Volumes Current+Last Month]? Can you describe where you're putting the item? What's nesting in it? What is it nested in?
Hey Paul,
The report is not having any slicers but it does have a few detail filters such as:
[CBI Sales].[Item].[Category] in (?pCategory?)
[CBI Sales].[Item].[Brand] in (?pBrand?)
[CBI Sales].[Item].[Item Group] in (?pItemGroup?)
[Item] looks like this:[CBI Sales].[Item].[Item Type].[Item Type Level 3]
and
[Volumes Current+Last Month]:
if (tuple([Actual];[Quantity Net Weight];[Current Month]) = NULL) then (0) else (tuple([Actual];[Quantity Net Weight];[Current Month]))
+
if (tuple([Actual];[Quantity Net Weight];[YTD Current Month]) = NULL) then (0) else (tuple([Actual];[Quantity Net Weight];[YTD Current Month]))
+
if (tuple([Budget];[Quantity Net Weight];[Current Month]) = NULL) then (0) else (tuple([Budget];[Quantity Net Weight];[Current Month]))
+
if (tuple([Budget];[Quantity Net Weight];[YTD Current Month]) = NULL) then (0) else (tuple([Budget];[Quantity Net Weight];[YTD Current Month]))
+
if (tuple([Actual];[Quantity Net Weight];[Last Month]) = NULL) then (0) else (tuple([Actual];[Quantity Net Weight];[Last Month]))
+
if (tuple([Actual];[Quantity Net Weight];[YTD Last Month]) = NULL) then (0) else (tuple([Actual];[Quantity Net Weight];[YTD Last Month]))
+
if (tuple([Budget];[Quantity Net Weight];[Last Month]) = NULL) then (0) else (tuple([Budget];[Quantity Net Weight];[Last Month]))
+
if (tuple([Budget];[Quantity Net Weight];[YTD Last Month]) = NULL) then (0) else (tuple([Budget];[Quantity Net Weight];[YTD Last Month]))
The purpose of [Volumes Current+Last Month] is to check if the top items comes from Current Month, Last Month etc based on quantity net weight. The reason for this is that it could happen that Current Month hasn't had any sold items yet, therefore no top 10 and then it should check Last month and so on.
[Item] is later tupled with [Volumes Current+Last Month] creating the data item "[Top items]":
topcount[Items];?topItems?;[Volumes Current+Last Month]
Maybe the if-statement expression could be written in another way or maybe choose just one period that it should check for top 10 items for example Rolling 12 month.
Live well,
Best regards,
Oscar
Detail filters on OLAP tend to cause unexpected results, and this may be a case where it's making things slower than it needs.
Item Type is a separate hierarchy from Category, Brand, and Item Group?
The measure calculation can also be tightened up a bit. Can you describe the difference between Current Month and YTD Current Month? Wouldn't YTD include the current Month?
Try this:
member(total(tuple([Actual];[Quantity Net Weight]) within set set([Current Month];[YTD Current Month];[Last Month];[YTD Last Month]));'volumecurrent';'volumecurrent';[Cube].[Measures])
Replace the [Cube].[Measures] with the reference to the actual measures dimension.
Hey Paul,
Yes Item type is a separate hierarchy.
Current month is for example 2018-08 while YTD Current Month is 2018-01 - 2018-08 (8 months).
I tried your solution:
member(total(tuple([Actual];[Quantity Net Weight]) within set set([Current Month];[YTD Current Month];[Last Month];[YTD Last Month]));'volumecurrent';'volumecurrent';[Cube].[Measures])
and it actually reduced the loading time significantly. But the loading time is still 30-40 seconds and that is not accepted by the users.. I tried to cut some of the columns and that reduced it to 12 seconds but all the columns are necessary.
Thanks for you help Paul, great as always!
And also the slicers being used looks like this:
#promptmany('pOrg','memberuniquename','[CBI Sales].[Profit Center].[Profit Center Functional].[(All)]->:[M16].[Profit Center].[Profit Center Functional].[All]')#
#promptmany('pOrg','memberuniquename','[CBI Sales].[Division].[Division].[(All)]->:[M16].[Division].[Division].[All]')#
Thank you Paul for your time.