Claims Authentication in SharePoint 2010
View more presentations from Nathan DeWitt
Archive for the 'Tutorial' Category
Remote Blob Storage in SharePoint 2010
View more presentations from Nathan DeWitt
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.
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 (
+L).
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.
21
Aug
MSDN Virtual Labs
Ok, I came across this a while ago, and promptly forgot about it. MSDN Virtual Labs (and its companion TechNet Virtual Labs) are awesome resources to get hands-on experience with technologies from Microsoft. They have a whole slew of stuff on developing inside of WSS, and it’s just a great resource.
23
Jul
Blogging with Word 2007
I’m using Word 2007 to create this post. I was actually starting to create a new document and I figured I would try creating a new blog post to see how it works.
I was first presented with this dialog to register my blog:
I selected WordPress from the list and saw this dialog:
I put in my blog (http://nathandewitt.net/blog/xmlrpc.php) and entered my account information. I clicked OK and presto changeo I was done.
Next I entered my title:
Then I clicked on the Insert Category. It reconnected to my blog to download my Categories, and then I selected Meta from the dropdown list.
I’m going to play around with some of the Word 2007 features to see how it handles them:
An Equation:
Word Art:
Some Code:
private
SPListItemCollection nominated(SPList calendarList)
{
SPQuery query = new
SPQuery();
query.ViewAttributes = "Scope=\"Recursive\"";
query.Query = "<Where>" +
"<Eq>" +
"<FieldRef Name=\"" + nominateField + "\" />" +
"<Value Type=\"bit\">1</Value>" +
"</Eq>" +
"</Where>";
return calendarList.GetItems(query);
}
Finally, I typed everything up and clicked Publish. It seems pretty straight-forward.
Pros:
- Simple to get up and running
- Handles styles decently
- Images get uploaded and stored without hassle
- It’s nice to work in the familiar environment of Word
- Making changes just involves publishing again
Cons:
- Equations don’t get converted to PNG or anything, they just vanish
- Difficult to predict formatting, had to put each picture’s text wrapping properties to Top & Bottom
- Word Art images have white background
- The code gets messed up – coloring is fine but each line is a separate paragraph
Conclusion:
This was easy to get going, but I’m not pleased with the embedding of other items (pictures, equations, code). This is a great solution for just writing a typical blog entry without a lot of pictures or style changes. Maybe I’ll use this more for blogging longer essays. I think I’ll stick with handcoding the HTML for now.
There are many ways to run custom code in SharePoint 2007. Arguably one of the simplest is to create a Custom Application Page, sticking all the source code into a script tag at the top of the page. You don’t have to muck around with compiling dlls or modifying the web.config file.
- Create a new aspx file. Put this at the top:
<%@ Assembly Name="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71E9BCE111E9429C" %> <%@ Page language="C#" MasterPageFile="~/_layouts/application.master" Inherits="Microsoft.SharePoint.WebControls.LayoutsPageBase" %>
Watch out! SharePoint Designer likes to remove the ~/ from your MasterPageFile declaration. I have no idea why it does this. I sat there entering it, hitting save, and watching it disappear like 4 times before I opened up Notepad and put it in. Weird.
- Add any namespaces needed.
<%@ Import Namespace="System" %> <%@ Import Namespace="System.Web" %> <%@ Import Namespace="System.Web.UI" %>
- Add a script tag (don’t forget to runat server!).
<script runat="server"></script>
- Copy the code from your code-behind and stick it into the shiny new script tag you just created. Don’t include those pesky namespace or class declarations, just the functions you want.
- Application Master has some ContentPlaceHolders; we care about three of them.
- PlaceHolderMain will hold your tag soup from the ASPX file.
- PlaceHolderPageTitle will hold the title that shows in the title bar of the browser.
- PlaceHolderPageTitleInTitleArea holds the page title that goes in the SharePoint chrome (the yellow bar of a typical application page).
<asp:Content ID="Main" contentplaceholderid="PlaceHolderMain" runat="server"> <!-- all your aspx content goes here --> </asp:Content> <asp:Content ID="PageTitle" runat="server" contentplaceholderid="PlaceHolderPageTitle"> Title in browser title area </asp:Content> <asp:Content ID="PageTitleInTitleArea" runat="server" contentplaceholderid="PlaceHolderPageTitleInTitleArea"> Title in SharePoint chrome </asp:Content>
- Create a folder in the LAYOUTS virtual directory. Should be in the C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\TEMPLATE\LAYOUTS\ directory. Don’t ever skip this step! Always separate your grubby code from the pristine beauty of the default code with folders!
- Copy your newly created .ASPX into the folder created in step 6.
- Now you can get to your snazzy new Custom Application page in any site by adding /_layouts/PageName.aspx to whatever site you want.
- There is no step nine.
Have fun!
11
May
Save as .doc
Ok, this post is for a friend of mine. By default, Office 2007 saves files in a new format, .docx. If you have clients that have not upgraded yet, there might be some confusion when they open your files. Ideally, you would have them get the Office Compatability Pack, but that’s not always feasible. You can save each file as a .doc file, but that can be onerous to remember every time.
Here’s how you set Office 2007 to save documents every time so that Office 2003 users can use your docs with no headaches.
- Open Word 2007
- Click the Office circle icon in the upper left corner
- Click Word Options button
- Click Save in the list on the left
- In the “Save files in this format:” drop down list, select “Word 97-2003 Document (*.doc)”
- Click OK
If you send other files (Excel, PowerPoint) then you might want to do the same thing for them. But otherwise, that’s it. You’re done.
