DQM error on ranking

[kimmal]

New to DQM, so bear with me.  I have a report that I have working in CQM.  It's basically a Top 10 products by sales quantiy.  The report has a chart and a list.  The list shows the top 10 products sorted by sales quantity, sorted in descending order (highest quantity at the top).  The chart is just a graphical representation of the table.  In CQM I had this working with the rank function, and Set Sorting on the Product Set with Sort Type Descending and Sort By as the Intersection of "Sales", but in DQM, I'm getting an error that says "The query is unsupported because there is a post auto-aggregate detail filter involving a relational function. You must replace the relational function with a dimensional function."

Thoughts?

[cognostechie]

Is the report using a relational package or a dimensional package? Are you expecting a report that was built earlier using a CQM package to work with a DQM package?
As I understand, reports made using a CQM package should be re-made when using a DQM package. My experience also says that some reports work and some do not when you simply change the package in the reports and point it to a DQM package. I would also wait for others to chip in here.

[MFGF]

New to DQM, so bear with me.  I have a report that I have working in CQM.  It's basically a Top 10 products by sales quantiy.  The report has a chart and a list.  The list shows the top 10 products sorted by sales quantity, sorted in descending order (highest quantity at the top).  The chart is just a graphical representation of the table.  In CQM I had this working with the rank function, and Set Sorting on the Product Set with Sort Type Descending and Sort By as the Intersection of "Sales", but in DQM, I'm getting an error that says "The query is unsupported because there is a post auto-aggregate detail filter involving a relational function. You must replace the relational function with a dimensional function."

Thoughts?

Hi,

It sounds very much like you are using a dimensional package here? If so, I'd advise you change the approach entirely and use a topCount() function to return the top 10 product members based on the Sales Quantity measure. Using detail filters with dimensional data is almost never a good idea.

Delete your filter, remove the Rank calculation, remove your Products level or member set from the list and the crosstab, and add a query calculation to the list and crosstab that uses the expression:

topCount([Your Products set or level],10,[your Sales Quantity measure])

This should deliver what you need, hopefully.

Cheers!

MF.

[kimmal]

Thanks MF, that worked. 

Question for you.  You say using Detail filters is not recommended.  I'm trying to make the report fairly interactive in the sense that I want to give the user the ability to choose a location (i.e East, West, North, South etc) and see the Top 10 for that location.  I had put in a value prompt in the report and passed that parameter to the detail filter something like [Location = ?p_location?].  Is there a better way to do this with a DMR, or am I still OK to do it this way

[MFGF]

Thanks MF, that worked. 

Question for you.  You say using Detail filters is not recommended.  I'm trying to make the report fairly interactive in the sense that I want to give the user the ability to choose a location (i.e East, West, North, South etc) and see the Top 10 for that location.  I had put in a value prompt in the report and passed that parameter to the detail filter something like [Location = ?p_location?].  Is there a better way to do this with a DMR, or am I still OK to do it this way

Hi,

You really need to keep away from detail filters when using a dimensional package! :)

In this case you can make use of a tuple function and a dimensional operator (->) in your expression to introduce the location context:

topCount([Your Products set or level],10,tuple([your Sales Quantity measure],[Your Location level] -> ?p_location?))

Cheers!

MF.

[bdbits]

This is perhaps the best explanation ever on why you should not use detail filters on OLAP. I bookmarked this for such occasions.  8)

http://www.cognoise.com/index.php/topic,18290.5.html

[MFGF]

It's also included in the FAQs sticky at the top of the Report Studio forum (FAQ 4), with a link to the very same post:

http://www.cognoise.com/index.php/topic,27563.0.html

Cheers!

MF.