Hello,

I am trying to create a line graph that displays projected expenditures along with actual expenditures. These values are contained in two different SharePoint lists. I want the Chart to display the SUM of the actual expenditures for a each month and display both actual and projected values as dollars.

I cam across this example from a NEVRON KB article, "Chart From Multiple SharePoint Lists". After a few attempts I was able to get the data source to display the data, however the data for the actual and projected expenditures are displayed as text rather than numbers. I have not been able to find the right formula that will sum the total actual expenditures for each month. No mater what formula I enter I receive an error that the data is not in the correct format, which goes back to the SharePoint Site Query not displaying the data in the correct format. Below are my entries to the data source CAML:

List CAML:






View Fields CAML:



Here is the formula I ma using to try and display the data:

=SUM(FORMAT(Fields!Expensed), "C", "en-us")


Any help anyone can provide will be greatly appreciated

Jessey

Hello Jessey,
You can use the TONUM function.
Take a look at the Help Documentation: Pivot Engine > Functions > Type Functions

Your expression for the Values Data Groupings should look like:
=SUM(TONUM(Fields!Expensed))

To display the values as dollars, the Label expression should look like:
=FORMAT(SUM(TONUM(Fields!Actual)), "C", "en-us")

Hopefully this helps.

Best Regards,
Nevron Support Team

Thanks for the quick reply. I used he formula that you suggested, however no data is displayed for the expensed amount. If I remove the format piece data is displayed. Your thoughts?

Jessey

Should have read your reply closer. Added it to just the values group and data displayed correctly. My problem now is filtering the chart based on a project number. I haven't seemed to find the correct filter expression. This is what I current have:

=IF(Params!SPF_ProjectID.STR = "",true, FORMAT(Fields!Sponsor_x0020_DPA) = Params!SPF_ProjectID.STR)

This formula shows all projects, so I don't think the project ID is being passed in the correct format.

Jessey