locked
Get top 10 values for each month in chart ssrs 2008 RRS feed

  • Question

  • Hi

    I am trying to get top 10 CSR names who have highest Utilization and they should be displayed in a chart, per month (month on X-Axis). For example if the user chooses from May to December, top 10 CSRs for each month should be grouped and displayed. Here Utilization is a calulated field for me. I am getting the required values from the cube and added Utilization as calulated field. I am able to get top 10 across the time perios chosen but not top 10 per month. Any suggestions would be of great help

    Thanks

    Madhavi


    Madhavi Pasapula

    Tuesday, December 18, 2012 7:28 PM

Answers

  • Hi Anivash, 

    I dont think what you're asking is doable in SSRS. To achieve what you want,  you will have to create a more complex sql query such that your recordset is a union of the top 10 of each month from start month to end month, but since you are sorting by utilization, the utilization will have to be calculated in your sql, not in SSRS.

    The solution i was suggesting earlier will result in each month having their own x/y axis showing the top 10 values. You can remove x axis labels and title and make the sub report pivot so that the months are horizontally grouped, not vertically grouped and then display the month name as an additional row in your pivot below the subreport(chart).

    krootz

    • Proposed as answer by Charlie Liao Friday, December 21, 2012 1:25 AM
    • Marked as answer by Charlie Liao Friday, December 28, 2012 5:37 AM
    Thursday, December 20, 2012 2:12 PM

All replies

  • Hi,

    1. Create the report that will accept the month as a parameter. Using SQL select the top 10 for that month, say January, and make sure everything works, including the chart.

    2. Then create a main report which has a dataset with the complete listing of the months you want to display, from Jan to Dec.  Make the report from step 1 as a sub report that will display on your main report passing the month as the parameter to the sub report.

    End result will be what you want. Top 10 for each month.

    Hope this helps!

    • Proposed as answer by Avanish Tomar Wednesday, December 19, 2012 8:30 AM
    Tuesday, December 18, 2012 8:56 PM
  • Hi Avinash

    I tried your solution but I didnt get the result the way it is supposed to. When the user selects the Start and End months, the months should be displayed on X-axis and for each the Top10 CSRs should shown with their performance as bars. All the months should be shown on a single graph with months on X-axis.

    Thanks

    Madhavi


    Madhavi Pasapula

    Wednesday, December 19, 2012 7:17 PM
  • Hi Anivash, 

    I dont think what you're asking is doable in SSRS. To achieve what you want,  you will have to create a more complex sql query such that your recordset is a union of the top 10 of each month from start month to end month, but since you are sorting by utilization, the utilization will have to be calculated in your sql, not in SSRS.

    The solution i was suggesting earlier will result in each month having their own x/y axis showing the top 10 values. You can remove x axis labels and title and make the sub report pivot so that the months are horizontally grouped, not vertically grouped and then display the month name as an additional row in your pivot below the subreport(chart).

    krootz

    • Proposed as answer by Charlie Liao Friday, December 21, 2012 1:25 AM
    • Marked as answer by Charlie Liao Friday, December 28, 2012 5:37 AM
    Thursday, December 20, 2012 2:12 PM