Data source of Chart web part is the statistics of a Customized List

Question

Chart web part can use the data from a list but in my case the data of list cannot be used directly.
The data of list must be processed to get the statistics. And the statistics is the data source for the chart web part.

For a simple example, I have a list like below:
------------------------------------------------
id         book_name         catalog
01        blablabla1           music
02        blablabla2           science
 .                .                      .
 .                .                      .
 .                .                      .
99        blablabla99          geography
-------------------------------------------------



I want to make the chart web part displaying a pie graph representing the following information:
-------------------------------------------------
catalog                count
music                  23
science                30
    .                       .
    .                       .
    .                       .
geography           13
--------------------------------------------------

How can I get this? Is there any existing sharepoint components I can use?

Answer 1

You will need to create a seperate list to contain your catalog names and then use that list to be called via the lookup column by your existing list to count them properly. Confusing I know, but follow this:

Create a custom list for the catalog names (let's say Catalogs)
Add a column of "Single line of text" called Catalog
Create a new item for each catalog (NO DUPLICATES) (ex - one for music, one for science, etc)
Create a custom list that will be your main list (like your simple example existing list - let's say Books)
Add the ID column of type whatever you need that to be
Add the book_name column of type whatever you need that to be
Add the catalog column of type LOOKUP
When the page refreshes after selecting the Lookup radio button, scroll down to the middle section and select Get information from: Catalogs and In this column: Catalog
Click OK
Go back to the custom list called Catalogs (the one with unique catalog values)
Add a column of type LOOKUP called Count
When the page refreshes after selecting the Lookup radio button, scroll down to the middle section and select Get information from: Books and In this column: Catalog (Count Related)
Click OK
Go back to the Catalogs list and you should now see a list that has each catalog and the count of items from the Books list for that catalog (if you have populated your Books list)
Use this Catalogs list as your data source for the Chart Web Part

I hope that helps! Let me know how it goes!

Thanks,
Adam Preston

---

Blog: http://sptwentyten.wordpress.com

Answer 2

Adam, Thank you! It almost works!

But seems the count related column(Count) cannot be used as the data source of Chart web part. Don't know why.
While connecting the data source, all went well till it said:


And then things became odd. If you click Back (in fact also the other two buttons) , you might encounter a error page saying:



Or another possibility if you succeed to go back to the previous page, and you click the Next button again, you would be able to set the X Field but not the Y Field . The drop-down menu is blank. You got nothing to select:


Seems the Chart web part cannot use the count related data.
Any idea?

Answer 3

Sorry, you are correct - a lookup column cannot be used as a source.

Do this to make it work:

Add the Catalog list as a web part to the same page where the chart is located (Insert > Web Part > List and Libraries Category > Catalogs)
Use option one under Choose a Data Source: Connect to another Web Part and point it to XsltListViewWebPart - Catalogs
Click Next
Leave Data Format as Table
Click Next
Now you can use the Count column as your X/Y axis to complete the wizard as you normally would

**TIP**
If you do not want the Catalog list web part to be displayed on the page, just hide it:

Edit the page
Edit the web part to bring up its properties
Expand the layouts section
Check Hidden
Hit Apply, OK, and Save your page

Thanks,
Adam Preston

---

Blog: http://sptwentyten.wordpress.com

Answer 4

Thank you very much!
I'll try it out tomorrow since today I don't have much time.
And I'll let you know my success of it. :)

Answer 5

It works. I tried it out today. :)
Though for my job it won't be so easy, I think I can use sharepoint supported functions to figure it out.
Thanks again!

Answer 6

Excellent! Glad I was able to help! You are very welcome.

- Adam Preston

---

Blog: http://sptwentyten.wordpress.com

Answer 7

Excellent! Glad I was able to help! You are very welcome.

- Adam Preston

---

Blog: http://sptwentyten.wordpress.com

Dear Adam,

I found your post really helpful. Thanks for it :)

I have a small variation in my requirement though. I have a list something like this-

Title                                Posted by

discussion 1                       user 1

Discussion 2                       user 2

Discussion 3                        user 1

discussion 4                        User 3

And so on...

The Problem is that the users are dynamic. that is everyday, more and more unique visitore post discussions on this site. 

How should i then get a list showing the top contributors? That is, I want the users along with the number of discussions each has posted. 

Please help.

-Ria.