Archive for September, 2009

25
Sep

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.

21
Sep

PIVOT and UNPIVOT

I finally had the need to use PIVOT and UNPIVOT in SQL Server 2005 while creating a pretty large report for a client.
The report needed to show a number of groups and how much of their budget mapped to certain strategic objectives. This is a pretty standard crosstab report, but the problem is that the numbers don’t really exist anywhere – it’s all percentages of the aggregated sum of the amounts budgeted to different projects.
The previous iteration of the report just had hard-coded textboxes, mimicking a crosstab report but was really just a bunch of textboxes on the page. (The previous developer was under the gun for a proof of concept, and ended up winning us the contract to put this into production.)
The data looked something like this:

GroupName ProjectName BudgetedAmount FundedAmount FiscalYear
Group1 Project1 $500 $450 FY09
Group1 Project2 $300 $300 FY09
Group2 Project3 $1000 $950 FY09
Group3 Project4 $750 $600 FY09

I added a table like this:

FiscalYear GroupName StrategicObjective Percentage
FY09 Group1 Obj1 .50
FY09 Group1 Obj2 .50
FY09 Group2 Obj2 .75
FY09 Group2 Obj3 .25
FY09 Group3 Obj4 1.00

The query to apply the percentage and aggregate this data looks like this:

SELECT tt.GroupName, 
	tt.FiscalYear, 
	tf.StrategicObjective, 
	tf.Percentage * tt.FundedAmount AS Portion
FROM TestTable tt 
JOIN TestFunding tf ON tt.FiscalYear = tf.FiscalYear
AND tt.GroupName = tf.GroupName
WHERE tt.FiscalYear = 'FY09'

Producing:


Then I used the PIVOT query to produce the dataset I needed for my report:

SELECT GroupName, [Obj1], [Obj2], [Obj3], [Obj4]
FROM ( 
	SELECT tt.GroupName, 
		tt.FiscalYear, 
		tf.StrategicObjective, 
		tf.Percentage * tt.FundedAmount AS Portion
	FROM TestTable tt 
	JOIN TestFunding tf ON tt.FiscalYear = tf.FiscalYear
	AND tt.GroupName = tf.GroupName
	WHERE tt.FiscalYear = 'FY09'
) p
PIVOT (
	SUM(Portion)
	FOR StrategicObjective IN ([Obj1], [Obj2], [Obj3], [Obj4])
) AS pvt

Producing:


If I need to produce another type of graphic showing the aggregate data again (bar charts or something mapped to the objectives) then I can do an UNPIVOT like this:

SELECT StrategicObjective, SUM(Amt) AS Amount
FROM (
	SELECT GroupName, [Obj1], [Obj2], [Obj3], [Obj4]
	FROM ( 
		SELECT tt.GroupName, 
			tt.FiscalYear, 
			tf.StrategicObjective, 
			tf.Percentage * tt.FundedAmount AS Portion
		FROM TestTable tt 
		JOIN TestFunding tf ON tt.FiscalYear = tf.FiscalYear
		AND tt.GroupName = tf.GroupName
		WHERE tt.FiscalYear = 'FY09'
		) p
	PIVOT (
		SUM(Portion)
		FOR StrategicObjective IN ([Obj1], [Obj2], [Obj3], [Obj4])
	) AS pvt
) p2
UNPIVOT (
	Amt FOR StrategicObjective IN ([Obj1], [Obj2], [Obj3], [Obj4])
) AS unpvt
GROUP BY StrategicObjective

This produces the following resultset:


In conclusion, this could all be accomplished with CASE statements, but I think this is much cleaner and frankly easier to write and read. That hardcoded ‘FY09′ can just be replaced with a parameter in the report and we’re good to go for future years.

21
Sep

Synchronize Excel 2003 to a SharePoint List

This is for those people who are unfortunate enough to be working with Office 2003. It’s crazy, but some clients are still working with the older versions of things.

  1. Get your data straightened out in your spreadsheet – replace N/A with a blank cell, replace — with 0, only one header, etc.
  2. Remove all spaces and strange characters from your header (Dashboard Designer chokes on some of these)
  3. In Excel 2003, Data –> List –> Create List (+L).

    Make sure that you have all your data selected, and check the box “My list has headers” (if it applies). Click OK.
  4. Data –> List –> Publish List. This will bring up a dialog, which you then use to enter the site collection name other info. Check the box that links the sheet to the SharePoint list.
  5. Next you will make sure that your columns are the proper data type. If they are not right, Cancel and fix the data in the columns (remove “-” from a numeric column, etc).
  6. If you checked the box that links your spreadsheet to the SharePoint list, you can go to Data –> List –> Synchronize List and get the latest updates.
21
Sep

Dashboard Designer Charts and Office 2007

If you use Dashboard Designer for PerformancePoint 2007, be careful about the chart components. Even though it has the Office 2007 Ribbon UI and acts like a 2007 suite member, it uses the Chart components from Office 2003.

When I installed Office 2007, I was unable to get to Data Retrieval Services when creating a data source for a report. This would let me use a SharePoint list as a data source. I thought I needed to reinstall the Office 2003 Chart Components, but that didn’t help.

I installed Excel 2003 side by side with Office 2007 and everything works again. Apparently the Chart Components are read only if you do not have a 2003 product installed.

17
Sep

SOAP Exception in Dashboard Designer 2007

Encountered an issue this morning after some patches were installed on the main PerformancePoint server over the weekend. Opening Dashboard Designer and refreshing encountered this error:

A general SOAP exception has occurred. Contact an administrator.

Well, that’s annoying. Some googling indicated that there might be an issue with the ASP.NET version that the PerfPoint web sites were running under. Opened IIS, saw that there was no ASP.NET version, period. I saw that one of the suggestions was to hit the Central Site and try reinstalling Dashboard Designer. That did nothing for me, but the other link was for the preview site. I checked to see if it was working, and it was. At least, it worked until I tried to browse to one of the test dashboards. Then I got a generic “some problem occurred”. So I browsed to it from the server and got this error:

System.InvalidOperationException: Unable to generate a temporary class (result=1).

Ugh. So I googled that and got to KB908158 telling me to grant Read and List Folder Contents on %windir%\Temp to the user account for the errant website. The identity for the app pool is jfcom\moss.sspapppool so it was a simple thing to grant those perms. Once that was completed I tried dashboard designer again and voila – it worked like a charm.