How to display the rank # for a specific store in the list?

[lookingforK]

I am using Report Studio 8.4 to generate reports.

The data source is a relational package.

For part of the reports, I just want to display a Rank # for a specific store. I don't want the list to show up rank info for all stores.

For example:
There are 100 stores. The rank # of store 0012 is 7 in terms of sales.
When I select a value for the prompt parameter (i.e. ?Store Number?), e.g. 0012,  the list should only display the rank # for the store # 0012. That is, 7 in this case.

How to deal with this issue?

Thank you in advance.

[CognosPaul]

Are you showing a list of all 100 stores, but only want to show the rank on a single row? In that case a render variable would be appropriate.

Is the report displaying data for a single store? Create a new query to find the rank, and set the filter on the store to be after auto-aggregation

[lookingforK]

Thank you PaulM.

I am not showing a list of all 100 stores. I just want a list with a single row showing the rank # (based on Sales) for a specific store.

The list has the following columns:
[Store Number] ..... [Store Size] ...... [Sales] ...... [Rank on Sales]

I can not set the filter on the single store (with the parameter ?Store Number?, and "After auto-aggregation") because if so, I just get the Rank # = 1 for whichever the store I select.

I try to set the filter on the Rank # for the store with "After auto-aggregation", but has the same problem - getting the Rank # = 1 for whichever the store I select.

[blom0344]

I think you need to use 2 queries feeding the output of the first into the 2nd , applying a filter against the 2nd query.  This is the DB equivalent of an inline-view / CTE

[lookingforK]

Omg ...

Thank you blom0344.

You did it.

Yes, it works.