Profile Picture

Creating a chart from multiple lists

Posted By Jessey Rose 5 Years Ago
Author
Message
Jessey Rose
Problem Posted 5 Years Ago
View Quick Profile
Forum Newbie

Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)

Group: Forum Members
Last Active: 2 Years Ago
Posts: 3, Visits: 13
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

Nevron Support
Posted 5 Years Ago
View Quick Profile
Supreme Being

Supreme Being (3,962 reputation)Supreme Being (3,962 reputation)Supreme Being (3,962 reputation)Supreme Being (3,962 reputation)Supreme Being (3,962 reputation)Supreme Being (3,962 reputation)Supreme Being (3,962 reputation)Supreme Being (3,962 reputation)Supreme Being (3,962 reputation)

Group: Forum Members
Last Active: 49 minutes ago
Posts: 2,896, Visits: 3,114
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



Jessey Rose
Posted 5 Years Ago
View Quick Profile
Forum Newbie

Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)

Group: Forum Members
Last Active: 2 Years Ago
Posts: 3, Visits: 13
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

Jessey Rose
Posted 5 Years Ago
View Quick Profile
Forum Newbie

Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)

Group: Forum Members
Last Active: 2 Years Ago
Posts: 3, Visits: 13
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



Similar Topics


Reading This Topic