Archive for the 'SharePoint' Category

28
Feb

Claims Authentication in SharePoint 2010

By nathan 0 Comments
Categories: SharePoint and Tutorial
Claims Authentication in SharePoint 2010
View more presentations from Nathan DeWitt

28
Feb

Remote BLOB Storage in SharePoint 2010

By nathan 0 Comments
Categories: SharePoint and Tutorial
Remote Blob Storage in SharePoint 2010
View more presentations from Nathan DeWitt

12
Jan

SharePoint Saturday VA Beach

By nathan 0 Comments
Categories: SharePoint

I attended SharePoint Saturday in VA Beach and had a blast. I am so excited about 2010 that it’s not even funny. The most interesting thing I learned about had to be the Windows Phone 7 integration with SP2010. I’m a disappointed with some of the limitations (can’t connect to MOSS2007 from Office mobile?) but hopefully that will be fixed in an upcoming release.

25
Sep

Using SharePoint List as Data Source for PivotChart

By nathan 0 Comments
Categories: SharePoint and Tutorial

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

By nathan 0 Comments
Categories: SharePoint and Tutorial

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

By nathan 0 Comments
Categories: SharePoint and Tutorial

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.