Create chart showing monthly sum from daily entry custom list

Question

I have a custom list of about 200 entries, which has three columns (name, mm/dd/yy, $amount). I would like to be able to view a summary of the total amount by month, either in a Chart web part or a custom view, but I can't seem to be able to.

    A, 01/01/12, $50
    B, 01/07/12, $50
    C, 01/21/12, $50
    D, 02/01/12, $50
    E, 02/02/12, $50
    F, 02/03/12, $50

This would yield a bar chart with two columns [Jan $150] and [Feb $150]

Can anyone suggest a method please?

Thanks.

Answer 1

Gil,

You can try Nevron Chart Web Part. Your requirement can be achieved with the help of the Chart web part pivot capabilities.

Take a look at: Count aggregated records and make trend lines with the Chart Web Part

You can use similar approach (but simpler) for you scenario.
For the Category grouping, you need to use an expression like:
=FORMAT(FIRST(Fields!DateTime), "Y", "en-US")
For the Values grouping, you can use the following expression:
=SUM(Fields!Amount)

If the DateTime values in your list are not consecutive, you can also perform Sorting from the Pivot >> Categories >> Sorting:
=Fields!DateTime
Direction: Ascending

This should result in a bar chart with the total amount for each month you have in the list.

For more information on the Pivot capabilities, you can refer to the Help Documentation:
Pivot Engine >> Grouping, Filtering and Sorting
Pivot Engine >> Functions

Full disclosure: I am working at Nevron Software and I have been assisting with the development of the Chart web part based on user requirements.