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

All responses (6)

Answers (1)

RaulOcana 8,270 on at

Like (0)

Report

RE: Complex Charts with Top Rule

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.
Verified answer

Community Member Microsoft Employee on at

Like (0)

Report

RE: Complex Charts with Top Rule

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.
RaulOcana 8,270 on at

Like (0)

Report

RE: Complex Charts with Top Rule

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
Community Member Microsoft Employee on at

Like (0)

Report

RE: Complex Charts with Top Rule

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" />
RaulOcana 8,270 on at

Like (0)

Report
RE: Complex Charts with Top Rule

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
Community Member Microsoft Employee on at

Like (0)

Report

RE: Complex Charts with Top Rule

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.

Complex Charts with Top Rule

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Tips for Writing Effective Suggested Answers

Leaderboard

Featured topics

Product updates

Complex Charts with Top Rule

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Tips for Writing Effective Suggested Answers

Subscribe to

Leaderboard

Featured topics

Product updates