Archive for the 'SharePoint' Category
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.
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.
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.
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:
I added a table like this:
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'
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
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.
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.
- Get your data straightened out in your spreadsheet – replace N/A with a blank cell, replace — with 0, only one header, etc.
- Remove all spaces and strange characters from your header (Dashboard Designer chokes on some of these)
- In Excel 2003, Data –> List –> Create List (
Make sure that you have all your data selected, and check the box “My list has headers” (if it applies). Click OK.
- 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.
- 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).
- 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.