none
Span series across multiple charts when number of series exceeds n

    Question

  • I'm trying to find a way of spanning chart data across multiple line charts, but only when the number of series exceeds a certain threshold (for example 5). The number of series in a report will be determined by a parameter input so it will vary. In other words if the series grouping, which is a string "Name" contains 12 names in total there should be 3 Charts. Chart 1 & 2 both display lots of 5 unique names and the third chart displaying the remaining 2 names. This will reduce clutter and make the report/charts more eligible.

    It should also be mentioned the X axis plots date/time so the query will likely return many rows of data for each distinct series (Name).

    My approach would be to place the chart in a list/table and create a grouping for each row based on every 5 series.

    However, the problem I'm having is coming up with an expression that works. Perhaps I need to do some of this processing on the SQL query side and some in the grouping expression. Also it seems that using aggregate expressions (such as CountDistinct) is not allowed in group expressions.

    Can anyone offer a suggestion on how to solve this problem I'm having?

    Thanks.

    Wednesday, November 28, 2012 2:31 AM

Answers

All replies

  • I think this post gives you a similar technique... 

    http://www.sqljason.com/2010/05/better-way-of-repeating-charts-based-on.html

    In your case you want the first 5 to appear on the first chart (and mine was equally distributing). So instead of MOD you might want just the integer part of divide operator.

    If you dont get how to do it, let me know and I can help you further...


    Cheers,
    Jason | www.SqlJason.com
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    • Marked as answer by Z-ster Friday, December 14, 2012 3:33 AM
    Wednesday, November 28, 2012 11:20 AM
    Moderator
  • Hi Jason,

    Firstly thanks for your response to my issue.

    The solution you've posted didn't work for me but that is probably because I neglected to point out that although the "name" parameter is entered by the user the results are filtered by the WHERE clause. As a result the ranking is not concecutive.
    I must also point out that my skills in the areas of SQL queries, expressions, VB.net and limited. My primary role is a Performance and capacity engineer.

    Here is one of the dataset queries that I'm using in the report to extract data from the SCOM database. Note: in my original post I used the example "Name" this should be substituted with "vme.Path" for the query below.

    John.

    SELECT vme.Path,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), vpd.DateTime) as DateTime,vpd.SampleCount,vpd.AverageValue,vpd.MinValue,vpd.MaxValue,vpd.StandardDeviation,pri.InstanceName FROM vPerformanceRule pr
    INNER JOIN vPerformanceRuleInstance pri WITH (NOLOCK) on pri.rulerowId=pr.RuleRowId
    INNER JOIN vRule vr WITH (NOLOCK) on vr.RuleRowId=pr.RuleRowId
    INNER JOIN Perf.vPerfdaily vpd WITH (NOLOCK) on vpd.PerformanceRuleInstanceRowId=pri.PerformanceRuleInstanceRowId
    INNER JOIN vManagedEntity vme WITH (NOLOCK) ON vme.ManagedEntityRowId=vpd.ManagedEntityRowId
    INNER JOIN vManagedEntity vme2 WITH (NOLOCK) ON vme2.ManagedEntityRowId=vme.TopLevelHostManagedEntityRowId
    WHERE pr.ObjectName='Processor'
    AND pr.CounterName='% Processor Time'
    AND pri.InstanceName='_Total'
    AND DateTime BETWEEN @startdate and @enddate
    AND vme.Path IN (@Computers)

    Wednesday, November 28, 2012 11:52 PM
  • Hi Z-ster,

    Based on my test, there is an approach to achieve your goal but it is not perfect. That is when the series colums are not larger than 5, there will be an second page generated displaying "No Data Available". However, when the column is more than 5, the charts display as expected.

    To do this, you can refer to Challen's answer in the followoing thread:
    http://social.msdn.microsoft.com/Forums/en-GB/sqlreportingservices/thread/32f28407-e1ca-457e-92fd-d292e32dde4e

    The steps are almost the same. In the chart, we should use the Category group field in the expression "=Ceiling(Code.MyFunc(Fields!CategoryGroupName.Value)/5)". Besides, we need to modify the datase query to make it only order by the category group field.

    In the example shown in the screenshot below, I use the expression "=Ceiling(Code.MyFunc(Fields!Country.Value)/2)" and Category group has 5 instances. The parameter Country filters the Category group.

    If you have any questions, please feel free to let me know.

    Regards,


    Mike Yin
    TechNet Community Support

    • Marked as answer by Z-ster Thursday, November 29, 2012 8:54 PM
    • Unmarked as answer by Z-ster Tuesday, December 04, 2012 3:31 AM
    Thursday, November 29, 2012 11:25 AM
    Moderator
  • Hi Mike,

    The solution posted in the link you provided worked perfectly on the first attempt.

    Thank you very much for posting this solution, and thanks to Jason for his answer as well.

    John.

    Thursday, November 29, 2012 8:54 PM
  • Hi Mike

    Ok, just an update on my implementation of this solution.

    Although this worked, I've since discovered that it only works for the fist chart in the report despite all charts being placed in their own list and configured with the same grouping and grouping expression as the working chart.
    Note: each chart has it's own dataset but I can't see how this would be an issue.

    Also, when I delete the list and the working chart within it, the next chart in the report takes on the correct behaviour.

    Is the issue within the code and if so how can this code be modified to work with all charts in the report?

    Thanks

    John.


    • Edited by Z-ster Tuesday, December 04, 2012 4:21 AM
    Tuesday, December 04, 2012 3:32 AM
  • Ok finally got this working using Jason method but had to make some changed to the query, specifically the "Rank" portion of the query.

    Dense_Rank() OVER (ORDER BY VME.Path ASC) AS Ranking

    Friday, December 14, 2012 3:50 AM