Need help for cognose report studio rank function (Trying to show a graph)

[ptcjr]

Hi, very new to cognos so I apologize in advance if this is covered somewhere or if the issue itself isn't described well. 

I am using a dynamic cube and have been able to generate a ranked list query (simple table view) using the rank function then filtering based on a parameter defined in a prompt.  Ex:  Showing top 5 Stores based on Sales Value. 

However, when I try to take that query and use it for a bar graph, it then generates this error. ..
Temporarily unsupported feature: 'Detail Post Auto Aggregate filter with scalar function.'.

Now, if I switch filter to "before auto-aggregation" I get this error:  The column '54' of 'LoadLevelsMultiDimensionalSubquery__SFD_tr__' could not be found in the model. Possibly caused by out-of-date query subject definition: 'LoadLevelsMultiDimensionalSubquery__SFD_tr__'

I have found that if I remove the parameter and hard code the value it works. 
Anyone ever seen this or have any idea what is happening?  Thanks.

[MFGF]

Hi,

There are two distinct reporting styles for reports in Cognos 10 - relational (where you are reporting off a package based on one or more databases) and dimensional (where you are reporting off an OLAP cube or relational data that has been modelled to appear like an OLAP cube).

The issue you are facing is that you are using relational reporting techniques (rank function and detail filter) against an OLAP source (dynamic cube). Instead, you should use a dimensional reporting technique to achieve the result.

It sounds to me like you are trying to retrieve the top n members from a level or a set for use in a chart? If so, rather than using the rank / filter approach you should use a dimensional function called topCount() in a query calculation.

Drag a query calculation to your chart, give it a name, and set the type to "other expression" then click OK. When prompted for the expression, go to the functions tab on the left, expand Dimensional Functions > R-Z and click on the topCount function. You will see the help for the function displayed in the Information window below the expression definition area. Use this function in your expression to get your desired top n members - eg:

topCount( [Your Stores level], 5, [Your Sales Value measure])

would return the top 5 stores based on sales value.

Cheers!

MF.

[mehow89]

Thanks, that has helped me so much.

[dmk.3678]

Hi, I have similar problem in topCount() function. Below are the details.

i have a cross tab with 2 measure M1, M2 and Level L1. I am creating a query calculation Q1 with expression M1/M2. Now i want to find the TopCount for N Number (N is nothing but the number input by end user. Here i will take 10). My topCount expression is
Top = topCount(L1,?number?,Q1)
L1=Level Against i want count
?number?=Integer prompt value provided by client
Q1= M1/M2.

I am placing the Top at raw level and M1,M2 and Q1 at columns. I am getting L1 10  results but, the value for M1,M2 and Q1 is always 0,0,/0 respectively for all 10 raws.

Same thing is  happening with bottomCount as well.

Please help me.. Thanks very much in advance.

[MFGF]

Hi, I have similar problem in topCount() function. Below are the details.

i have a cross tab with 2 measure M1, M2 and Level L1. I am creating a query calculation Q1 with expression M1/M2. Now i want to find the TopCount for N Number (N is nothing but the number input by end user. Here i will take 10). My topCount expression is
Top = topCount(L1,?number?,Q1)
L1=Level Against i want count
?number?=Integer prompt value provided by client
Q1= M1/M2.

I am placing the Top at raw level and M1,M2 and Q1 at columns. I am getting L1 10  results but, the value for M1,M2 and Q1 is always 0,0,/0 respectively for all 10 raws.

Same thing is  happening with bottomCount as well.

Please help me.. Thanks very much in advance.

Hi,

There's more going on here than you're telling us, I suspect. I just tried the exact example you describe using the sample great_outdoors_sales_en powercube package, and it works perfectly for me.

The report spec is attached below - you can try it if you have the samples installed.

Cheers!

MF.

[dmk.3678]

I did the same thing. Only thing in my case different is ?NoRequest? prompt is defined on the report output page along with 3 slicers (year, quarter, and month).

All Slicers are optional and defaulted. Also NoRequest is defaulted to 10. Does slicers and defining prompt on report page have to do anything with this?

[MFGF]

I did the same thing. Only thing in my case different is ?NoRequest? prompt is defined on the report output page along with 3 slicers (year, quarter, and month).

All Slicers are optional and defaulted. Also NoRequest is defaulted to 10. Does slicers and defining prompt on report page have to do anything with this?

I just modified my example to move the prompt to the report page, and to add a slicer prompt on the report page too. It still works just fine for me. The report is attached.

MF.

[dmk.3678]

Thanks. I got it working. But now the issue here is thats. My Slicers is set to default right now. So Year Slicer is set to FY15, and Quarter slicer is set to 'Qtr1' and 10 as the NoRequest.

so when i first run the report, it uses the default set values for year=FY15, Quarter=Qtr1 and NoRequest=10 and it successfully shows the 10 counts. But now when i select the Qtr1 and Qtr2 for FY15 and keeping NoRequest=10, it runs the report but it does not show 10 raws, but it reduces to 6 or 7 raws.

but is mentioned in prompt, it should show 10 raws.

Is there anything related to slicer?