Top 'x' Accounts based on Invoice Total

(0) Share

Report

Posted on by bmansfield81

Hi,

I would like to be able to display my top 100 accounts in a list view based on the total value of the account's invoices. We have thousands of account records but I want certain members of our sales team to focus on our biggest customers in terms of revenue. I can easily display them in an invoice chart using the top 'x' option however I would like to be able to see all of the accounts in a list view so I can action on the records and send to Excel.

Is this possible with standard tools? I saw some articles regarding exporting the standard solution and using FetchXML to update a system view in the solution. Can someone please advise on the best way to achieve this?

Thank you

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (0)

Karth on at

Like (0)

Report

The best way is to build a custom report for this funtionality.

One alternative is to add the Account Lookup as part of the Invoice List view so that when you run this chart, the data displayed on the list view is context sensitive. You can then export the data to excel and build pivots to group the invoices by Account Name.

The other alternative [if you are using Outlook Client] is to use Dynamic Pivots and use excel for your manipulations. The advantage of using Dynamic Pivot is that you dont need to export this data every time. All you need to do is to use the "Refresh from CRM" button on Excel to get updated data.

Was this reply helpful? Yes No
bmansfield81 on at

Like (0)

Report

Thanks for the reply however the goal is to have the list of accounts in the application. We need to easily add them to a marketing list, schedule activities, etc. I read this could be possible with some customization to the views but was looking for some guidance on best practice for editing the fetchxml

Was this reply helpful? Yes No
Royal King 27,686 on at

Like (0)

Report

You are right just editing the fetchxml of the chart you can show top X account .

Here is the Fetchxml that i used to get top 2 Accounts with total invoice Sum. You can add more conditions to it if you like to restrict based on years or invoice status.

<datadescription>
<datadefinition>
<fetchcollection>
<fetch mapping="logical" aggregate="true" count="2">
<entity name="invoice">
<attribute name="customerid" groupby="true" alias="_CRMAutoGen_groupby_column_Num_0" />
<attribute name="totalamount" aggregate="sum" alias="_CRMAutoGen_aggregate_column_Num_0" />
<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>

Here is my chart that shows Invoice sum and top X accounts in the chart. Only issue with this chart is it does not filter invoices that are corresponding to the account until user clicks on the chart bar. You can adjust your view to show customer name that way you can click on the record to directly go to account record.

Was this reply helpful? Yes No
bmansfield81 on at

Like (0)

Report

The chart is easy to create without even editing the XML. You can just use the options in the chart setup. The goal of this is to only display a single line per account. The view would be displayed under the account entity. It would have account name, invoice total (YTD, MTD, or whatever the the Fetch call was set to), and be sorted from highest to lowest.

I think I'll just shoot it all out to Excel, group and sort, and then import a field on the accounts that ranks them every so often.

Was this reply helpful? Yes No
Royal King 27,686 on at

Like (0)

Report

Here is Chart definition that shows the top 20 Accounts based on Invoice revenue. This chart is based on the account entity so you can see this chart in any view of the account entity. If you want to add any conditions to filter just add that condition to the fetchxml . It is very easy to update when you want to make changes as well there is no need of excel manipulation.

<visualization>
<visualizationid>{5EA17955-D124-E411-BD3D-6C3BE5BDA978}</visualizationid>
<name>Top 10 Revenue accounts</name>
<primaryentitytypecode>account</primaryentitytypecode>
<datadescription>
<datadefinition>
<fetchcollection>
<fetch aggregate="true" mapping="logical" count="20">
<entity name="account">
<link-entity name="invoice" to="accountid" from="customerid" alias="i" link-type="inner">
<attribute name="customerid" alias="customer" groupby="true" />
<attribute name="totalamount" alias="sum_total" aggregate="sum" />
<order alias="sum_total" descending="true" />
</link-entity>
</entity>
</fetch>
</fetchcollection>
<categorycollection>
<category alias="customer">
<measurecollection>
<measure alias="sum_total" />
</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="Column" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40" />
</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" />
</Titles>
</Chart>
</presentationdescription>
<isdefault>false</isdefault>
</visualization>

Was this reply helpful? Yes No