Complex Charts with Top Rule

(0) Share

Report

Posted on by RaulOcana

8,270

Hi guys,

I have this chart where I use the fields "City (series)" vs "City(category)" and "Created On (category)" but I cannot add a Top/bottom Rule. I tried adding the "count=10" to the fetch and an order attribute via xml, but the results won't show me (in this case) the TOP 10 of countries; is there a way to accomplish this?

<fetch mapping="logical" aggregate="true" count="10">
          <entity name="lead">
            <attribute groupby="true" alias="_CRMAutoGen_groupby_column_Num_0" name="address1_city" />
            <attribute alias="_CRMAutoGen_aggregate_column_Num_0" name="address1_city" aggregate="count" />
            <attribute groupby="true" alias="_CRMAutoGen_groupby_column_Num_14" dategrouping="month" name="createdon" />
	    <order alias="_CRMAutoGen_aggregate_column_Num_0" descending="true" />
          </entity>
        </fetch>

Fitlers: Microsoft Dynamics CRM Online 2013

Thanks,

Raul

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (1)

Community Member on at

Like (0)

Report

You can only use Top 10 for some data that is included in the chart. So if you have a count of Leads by country, you can sort and filter on those values. If you are counting cities, then you can use a Top 10 rule to show the 10 most popular cities. You can't calculate one count but filter by another aggregate that is not in your chart (and from the XML you posted, country is not mentioned anywhere).

Being more specific, it looks like you are planning to do something like a stacked column chart arranged by city, then broken down by month (or the other way round - you have not included your measure collection so I can't see). This means each data point represents a single city and single month. So your Top 10 would show the columns that contain the 10 highest data points, not the 10 highest total stacked columns.

For example, you might generate 100 Leads in one city every month. In other cities this might usually be only 50, but in 10 cities you have had at least 1 great month generating 101. These 10 months for those 10 cities are the top 10, even though over a year they only generating 651 in total (11x50 + 101) compared to 12 x 100 = 1200 for the more consistent city.

Was this reply helpful? Yes No
RaulOcana 8,270 on at

Like (0)

Report
Hi Adam,

I didn't post all the "chart", maybe this will help. This is the Chart that I use to see the total of Leads on a Period of Time and grouped by Cities.

<visualization> <visualizationid>{03B1D97A-3540-E411-8991-6C3BE5A8EA28}</visualizationid> <name>SM New Leads (City)</name> <primaryentitytypecode>lead</primaryentitytypecode> <datadescription> <datadefinition> <fetchcollection> <fetch mapping="logical" aggregate="true" count="20"> <entity name="lead"> <attribute groupby="true" alias="_CRMAutoGen_groupby_column_Num_0" name="address1_city" /> <attribute alias="_CRMAutoGen_aggregate_column_Num_0" name="address1_city" aggregate="count" /> <attribute groupby="true" alias="_CRMAutoGen_groupby_column_Num_14" dategrouping="month" name="createdon" /> <order alias="_CRMAutoGen_aggregate_column_Num_0" descending="true" /> </entity> </fetch> </fetchcollection> <categorycollection> <category alias="_CRMAutoGen_groupby_column_Num_0"> <measurecollection> <measure alias="_CRMAutoGen_aggregate_column_Num_0" /> </measurecollection> </category> </categorycollection> </datadefinition> </datadescription> <presentationdescription> <Chart Palette="None" PaletteCustomColors="91,151,213; 237,125,49; 160,116,166; 255,192,0; 68,114,196; 112,173,71; 37,94,145; 158,72,14; 117,55,125; 153,115,0; 38,68,120; 67,104,43; 124,175,221; 241,151,90; 186,144,192; 255,205,51; 105,142,208; 140,193,104; 50,125,194; 210,96,18; 150,83,159; 204,154,0; 51,90,161; 90,138,57;"> <Series> <Series ChartType="Bar" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"> <SmartLabelStyle Enabled="True" /> </Series> </Series> <ChartAreas> <ChartArea BorderColor="White" BorderDashStyle="Solid"> <AxisY LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount"> <MajorGrid LineColor="239, 242, 246" /> <MajorTickMark LineColor="165, 172, 181" /> <LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" /> </AxisY> <AxisX LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount"> <MajorTickMark LineColor="165, 172, 181" /> <MajorGrid LineColor="Transparent" /> <LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" /> </AxisX> </ChartArea> </ChartAreas> <Titles> <Title Alignment="TopLeft" DockingOffset="-3" Font="{0}, 13px" ForeColor="59, 59, 59"></Title> </Titles> <Legends> <Legend Alignment="Center" LegendStyle="Table" Docking="right" IsEquallySpacedItems="True" Font="{0}, 11px" ShadowColor="0, 0, 0, 0" ForeColor="59, 59, 59" /> </Legends> </Chart> </presentationdescription> <isdefault>false</isdefault> </visualization>

And the Count=20 won't show me the Top 20 cities, but 20 resutls.

Regards,

Raúl

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Your fetchcollection includes two groupby clauses for two attributes - City and Created On (by month).

Only the city is referenced in the measure collection, your second groupby does not do anything. You are using a Bar chart, not a stacked Bar, so a second level of categorisation or breakdown would not be shown in any case. <edit: sorry, I see now that this is simply a clustered chart rather than a stack. Maybe the bars were putting me off as I expected time to go horizontally rather than vertically. Possibly consider a column chart so that time does go across in a more conventional way>

However, the top 20 is probably being confused by the second groupby clause that should tell it to find the top 20 data points for city and month combined, which you can't do.

Try deleting this from your xml and see if it works better:

<attribute groupby="true" alias="_CRMAutoGen_groupby_column_Num_14" dategrouping="month" name="createdon" />

Was this reply helpful? Yes No
RaulOcana 8,270 on at

Like (0)

Report

If I remove that attribute I won't be able to see the results by month.

The point of the chart is being able to see how many leads by city and by month do I have in this year.

The xml that I posted does that, now I have too many cities of a country and only want to show the top 10 cities.

I hope I made myself clear :)

Best regards,

Raúl

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

I think you are being clear in some ways and not others.

I get that you want to show leads by city and by month, so each data point will represent a number of leads in a particular city and a particular month. But you also want to select the top ten cities.

You can't do that. I hope I made myself clear.

You can chart leads by city and show the top ten cities, or by month and show the top ten months. If you look in the UI, you can't add a Top or Bottom rule when you have selected more than one category (and although your import does not fail, and the button shows a tooltip for "Top 20" which it sees in the XML, it is ignored. If you remove a category, the Top 20 rule comes back.

What you are asking for does not work when you think it through - what do you mean by the "top 10 cities"? The top ten for total Leads in that city ever? (or at least in the period being included by the view for the chart) But this total is not calculated anywhere in your chart, it only has data points and values for each city in each single month.

Some tips to make your XML easier to work with:

Your fetchcollection includes an aggregate count of the "city" field - this adds to the confusion since you are not counting cities, you are counting leads. Consider using the leadid field instead (this will show up in the UI as Lead - Count:All, which is self-explanatory). Also consider replacing the automated alias names with your own that explain what each item is (eg LeadCount, CreatedMonth, etc), so the overall intent is easier to read.

You also say you have too many cities of a country - where does country come into this? Are you using the chart with a view that provides the filtering to a particular country? You mention things like country and year that are not part of the chart definition and only add to the confusion.

Was this reply helpful? Yes No
RaulOcana 8,270 on at

Like (0)

Report

You're right, I didn't mention that the view that I use filters by actual year and region, that's why I talked about Country and Year.

So it's not possible. Well at least that count is helping me to avoid lots of data being shown in the chart that makes it unreadable.

Thanks Adam.

Was this reply helpful? Yes No

Community site session details

Complex Charts with Top Rule

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

Featured topics

Product updates

Community site session details

Complex Charts with Top Rule

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

Featured topics

Product updates