Avatar of pensiongenius
pensiongeniusFlag for United States of America asked on

How to display ten 10 records in a 3D bar graph using SSRS 2008?

Hello,

   I have a dataset that contains records for counts by a deferral rate. I'm sorting the report by deferral rate in ascending order (smallest to largest). At the top of the report, I have a 3D bar graph. The category group for the bar graph is the deferral rate in ascending order. I have a end-user requesting to display only the top ten deferral rates in the bar graph. Is that possible? If so how is that done?  I'm using Microsoft reporting services 2008.

    I'm a beginner at SSRS so if you can explain in detail how to do that, I would appreciate it.

    If you have any questions jsut let me know.

Thanks for your help,
Ting
Microsoft DevelopmentMicrosoft SQL ServerSSRS

Avatar of undefined
Last Comment
Tony303

8/22/2022 - Mon
Tony303

Are you able to do a "TOP 10" clause in your query with an order by?

This will show only the top 10 highest Deferral Rate entries.

So

SELECT Top 10 *
FROM YourTable
ORDER BY DEFERRAL RATE DESC
ASKER
pensiongenius

HI Tony303,

   I could but I wish it was that simple.

   I need all the records returned in the dataset to be used by the SSRS report to be displayed in the body of the report.  The end-user is requesting if possible to display the top 10 highest deferral rate entries in the 3D bar graph that displays at the top of the report. The category group for the bar graph is the deferral rate in ascending order. Is there a way for me to specify in the bar graph properties to display only the top 10 highest deferral rates? If so how would I do that? If not then is there another way to do this so that the bar graph only displays the 10 highest deferral rates?

     If you have more questions just let me know.

Thanks for your help,
Ting
Tony303

I think you should add a derived column in your query.
It is the Ranking of all the rows.

Use the RANK or DENSE RANK function...

http://www.sqlservercurry.com/2009/04/rank-vs-denserank-with-example-using.html


That way you can then filter your 3D Bar Graph by filtering the Ranking column <= 10.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Tony303

If you can't make changes to the source query as I suggested above, how about making a new dataset in your report that has the Top 10 records.

SELECT Top 10 *
FROM YourTable
ORDER BY DEFERRAL RATE DESC

Then use that dataset to feed your 3D Bar Graph.

Let me know how you get on.
ASKER CERTIFIED SOLUTION
Tony303

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
pensiongenius

Hi Tony303,

    Thanks for your most recent recommendation of filtering the 3D Bar Graph by choosing Deferral Rate then using 'Top N' for the operator and value of 10 and your previous recommendations.

    Before you posted your recent recommendation, I was trying different things with the 3D bar graph. I discovered the Top N operator for the filtering option and it worked. That is a great secret we both discovered.

    Thanks so much for all your help. Have a great day.

Regards,
Ting
ASKER
pensiongenius

Thanks Tony303,

   Great recommendation. It works perfectly.

Ting
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Tony303

We're all learning every day!! Thanks.