Top X ranking report with 'Other' as remaining balance

[cogman8]

Cognoids,

I need to create a ranking report that shows the top X and the remaining balance as 'Other' in a pie chart and list report on one page. I got this report to work using a "Union" between two queries. The problem came when I set up drill through. The target report required 4 to/from date fields, so when I added the fields to my 2 queries, the list report repeated 'Other' several times.

I believe the way to approach this type of report is to drag one query on top of another, not a union or master detail approach. Has anyone done a report like similar to what I have described?

Any help would be greatly appreciated.

[Suraj]

Do you have to select date in the original report that you can pass to child or these just belong to child?
If the later is true, then you may want users to enter the prompt values after they click on the drill through links.

[cogman8]

The four date fields belong to the child report.  Unfortunately, the PM doesn't want the user to prompt on a drill through report.  I believe the best way to approach this report is to create a shortcut of the main query and use the shortcut to create the 'Other' field and Top X if/then/else statement.  I'll have to test it out.  Thanks for the response.

[Fiala]

Cogman,
Did you ever resolve this situation?  I have the same situation.  I'm doing a proof of concept for a project.  I'm using the GO Sales and Retailers package from Cognos (database = DB2).

OBJECTIVE
PIE Chart of top 5 Order Method codes based on total Revenue with the remaining Order Method Codes displayed as Other.

I have created a list frame and receive the correct results based on:
Ranking Revenue by Order Method then a calculation:
IF ([Ranking]<=5)
THEN ([Order Method Code])
ELSE ('Other')

However, when I try to use the new calculation column in the PIE Chart I receive the following error:

Unsupported 'if' expression for dataItem="Damage Code Ranked". The 'then' clause (expression="[Damage Code]") must return a single value

Now I understand why but I've tried grouping, etc but I still can't seem to find the right combination.  I tried joining 2 queries but I haven't tried Unioning.  I had hoped you had found a smoother manner in which to accomplish this?

Any direction would be "GREATLY" appreciated!!!!!!!!!!!

Fiala..........

[rockytopmark]

We have these in place at my current client.  We use a reference query to support the Pie Charts and this "All Others" grouping.  So you are very much on the right track...

Create a new Query, and make it a reference query to your existing list's query.  Bring in ONLY the columns from your existing query that you need in the Pie chart.  You should not have to mess with the Aggregation, but don't quote me on that.

[Fiala]

Thanks so much for responding so quickly.  I'll give it try...........