Wednesday, November 28, 2012 2:31 AM
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?
Wednesday, November 28, 2012 11:20 AM
I think this post gives you a similar technique...
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...
Jason | www.SqlJason.com
P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)
- Marked As Answer by Z-ster Friday, December 14, 2012 3:33 AM
Wednesday, November 28, 2012 11:52 PM
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.
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)
Thursday, November 29, 2012 11:25 AMModerator
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:
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.
TechNet Community Support
Thursday, November 29, 2012 8:54 PM
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.
Tuesday, December 04, 2012 3:32 AM
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?
- Edited by Z-ster Tuesday, December 04, 2012 4:21 AM
Friday, December 14, 2012 3:50 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