25
Sep
09

Using SharePoint List as Data Source for PivotChart

In Dashboard Designer, I create a new PivotChart report. Then I click the Chart Wizard icon, right beside the help button. This brings up the Commands and Options dialog.

When you click the Connection… button, you switch to the next tab where you choose the type of connection.

When you click on Edit under the Data Details tab, the Select Data Source dialog opens.

Clicking on the New Source… button opens up the Data Connection Wizard.

Select the Data retrieval services from the list box and click Next >. You will then select Windows SharePoint Services lists from the list box.

Click Next > and you will specify your SharePoint site location in the next window.

Click Next > and you will then select the list containing your data.

Now we get to select the fields that we want to query. Notice the pretty names in the Selected Columns list box.

Select your Sort Order and specify a Filter, if desired.

You can limit the number of rows returned, if desired.

Now save the Data Connection File and click Finish.

Now in the Commands and Options window that appears you can see that you are actually running a dsQuery on the site specified in Connection. If you open the uxdc file you created in Notepad, you’ll see that it’s just one big SOAP query, hitting the DspSts.asmx web service for your site. FYI.

Now we’ve reached the meat of our chart creation. I’ve created the chart, and you can see that while the chart is displaying my data perfectly, I have no choice in the names displayed in the legend. There are two glaring issues. The first is that the spaces are replaced with _0020_. The second is that when you drag Funded_0020_Amount to the chart, it automatically aggregates this for you, creating a new Total called Sum of Funded_0020_Amount. You can change the aggregation type to Average, Count, Min, Max, Variance, etc. But you still get Count of Funded_0020_Amount, Variance of Funded_0020_Amount, etc.

You can’t right-click and the field and do anything except delete it.

If you click on the Legend Entry and open the Commands and Options window, you can just set the font properties.

As a last resort I attempted to enter an Alias into the actual dsQuery, but that just reset my chart. The Field List doesn’t pick up my Aliases.

Unfortunately, I haven’t found a way to get pretty names on the chart. If you know a way to change that query to use the Alias, let me know and I’ll be happy to update this blog entry. A workaround is to create very descriptive Axis labels.


0 Responses to “Using SharePoint List as Data Source for PivotChart”


  1. No Comments

Leave a Reply

You must login to post a comment.