Hi,
I need a report based on relational data to show list top 10 Articles + summarized Rest based on item [Revenue]=total([paid]).
Base query has items [Year], [Articlenumber],[Articlename],[Revenue].
I added [Rank]=rank([Revenue]).
This works fine so far, but when I try to replace [Articlenumber] and [Articlename] in list with items
[Number]= CASE WHEN [Rank] > 10
THEN 'Rest'
ELSE [Articlenumber]
END
[Name]=CASE WHEN [Rank] > 10
THEN 'Rest'
ELSE [Articlename]
END
to only get top 10 ranked lines + line with summarized Rest
then things run out of control and only 1 line is displayed.
Any ideas how to get the right result ?
Yes, I have a report that sports 20 graphs based on top x + rest , where the value of 10 is made dynamic by entering an x value through a text prompt.
The general idea:
Each list/graph runs on an union set:
Set 1: top x values based on a rank definition [Ranker] Set a filter like : [ranker] <= 10 (with application : after auto aggregation)
Set 2: fetches the rest of the data based on [ranker] > 10 (with application : after auto aggregation)
The trick is that you cannot refer to the ranked object directly, since the outcome depends on it (sort of circular reference)
By using a union set and filtering the sets AFTER the ranking is performed you will get the requested outcome..
Dear blom0344 - thanks for your help.
I managed to get top 10 in the first query. I have also created the second query with all the entries rank > 10 - but how do I get them aggregated ? Remember the Rank is done after auto aggregation. Therefore I get multiple lines as result of query 2, but as result I just want one line: Year(filtered by prompt to 1 year), 'Rest', total
The result of the second query should rollup to one line provided you do not add the rank itself to the contents or properties of the list..