by Martin Laukkanen | Feb 20, 2010 | Development, SharePoint 2007
[Update: 23/02/10] I have updated this script in a new post here which does not rely on STSADM and therefore does not need to be run as admin.
With all the 2010 lab testing I have been doing lately I’ve been meaning to get around to creating / finding a new WarmUp script to use but this time based on PowerShell.
What I found is fortunately there are loads of examples out there to get you started as with most things PowerShell. See Kirk Hofer’s Blog for the one I started with.
So anyway I have added a little to the script, now in addition to opening each site in the farm it also parses a text file (C:ToolsWarmupwarmup-extrasites.txt) for additional URL’s, which I use to warm up particular SSRS or Excel reports.
Warmup.ps1
############################################################################
#Assumptions:
#-Running on machine with WSS/MOSS
############################################################################
$stsadmexe = 'C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions12BINSTSADM.exe'
$extrasitelistfile = 'c:ToolsWarmupwarmup-extrasites.txt'
function get-webpage([string]$url,[System.Net.NetworkCredential]$cred=$null)
{
$wc = new-object net.webclient
if($cred -eq $null)
{
$cred = [System.Net.CredentialCache]::DefaultCredentials;
}
$wc.credentials = $cred;
return $wc.DownloadString($url);
}
#This passes in the default credentials needed. If you need specific
#stuff you can use something else to elevate basically the permissions.
#Or run this task as a user that has a Policy above all the Web
#Applications with the correct permissions
$cred = [System.Net.CredentialCache]::DefaultCredentials;
#$cred = new-object System.Net.NetworkCredential("username","password","machinename")
[xml]$x=&$stsadmexe -o enumzoneurls
foreach ($zone in $x.ZoneUrls.Collection) {
[xml]$sites=&$stsadmexe -o enumsites -url $zone.Default;
foreach ($site in $sites.Sites.Site) {
write-host $site.Url;
$html=get-webpage -url $site.Url -cred $cred;
}
}
# Warm up other sites specified in warmup-extrasites.txt file (such as SSRS)
if (test-path $extrasitelistfile) {
$extrasites = get-content $extrasitelistfile
foreach ($site in $extrasites) {
write-host $site;
$html=get-webpage -url $site -cred $cred;
}
}
Warmup-ExtraSites.txt
http://servername/ReportServer
Download both files here.
Take note of the variables at the top of the script with the paths pointing to the 14 hive and the extra sites text file.
Save those files somewhere (default c:ToolsWarmUp) then schedule them to run with a command line as follows:
C:WindowsSystem32WindowsPowerShellv1.0powershell.exe C:ToolsWarmupwarmup.ps1
Finally just make sure you select the Run with highest privileges option as stsadm will need that to enumerate the sites.
Enjoy!
by Martin Laukkanen | Feb 8, 2010 | How to, Project 2007, Reporting
Update 31/03/2011: See the following blog about how much simpler this is with SQL Server 2008 R2.
This is one of the most common gripes that I hear about Project Server’s reporting database, the fact that only the default fields in your Project workspace lists, ie Project Risks, Project Issues, etc are available to report on.
Unfortunately I have yet to come across a client who uses (for example) a percentage probability of risk, I’m guessing it’s all that PRINCE2 influence. So as a result you are usually left with two options;
- Update the default fields to match your requirements, which actually is possible in 90% of the cases with some careful modifications to the workspace template and possibly some custom MDX measures for your cube. The biggest drawback with this one is if you want to report on new lists, such as for example a Change Register it is simply not possible without extending the RDB and cube!
-
Secondly you could find and purchase a third-party product that will allow you to report directly on all of the list contents of your workpace, some of these link directly into the sites, others use a staging database.
There is of course a third option though, do it yourself, and that is what this blog post is about.
How to access the SharePoint data?
The first question is how, and of course I should be clear as a one-time minion of the capital MS, the question must be how to do it in a supported manner. So that completely rules out direct SQL access to the databases!
Fortunately there is another way: XML.
Let me start by saying that there is a lot of information is out there on this method, so here are some links to some of the most helpful pages; at codeproject, and other blogs here and here. (That last Blog one I probably found most useful!)
Before I get into it it’s worth mentioning that my example involves using Visual Studio 2008 with the SQL Business Intelligence Design Studio (BIDS) installed. Don’t let the VS2008 bit scare you it would be possible to do much of this using Report Builder, at least once you have your data connections created, perhaps I’ll leave that for a future blog?
In summary this is what you need to do:
- Create a SRS data source pointing to your PWA Reporting database.
- Use the above data source to identify the field named "ProjectWorkspaceInternalHRef" from the "MSP_EpmProject_UserView" table for each project you want to report on.
- Then use the URL indentified to dynamically construct your XML data source query by basically appending "/vti_bin/lists.asmx" to the end of the string.
- Finally using XML pull down the information that you want.
Too easy!
Okay show me the details!
Okay so first thing you need to do after firing up Visual Studio is start a new blank Report Server Project, then:
Step 1: Create your Shared Data Source
Create a standard SQL data source pointing to your Project Server Reporting database, such as:
| Data Source=EPM2007DEMO;Initial Catalog=ProjectServer2007_Litware_Reporting |
Step 2: Enumerate Workspace URLs and Select your Project
Firstly create a blank report with a simple SQL query to enumerate a list of all projects and their associated workspace URL:
Example query:
SELECT ProjectName, ProjectWorkspaceInternalHRef
FROM MSP_EpmProject_UserView
|
Then create a Parameter to prompt the user for a selection:
Name: SelectedProject
Prompt: Select Project
Select Get values from a query for Available Values:
Value field: ProjectWorkspaceInternalHRef
Label field: ProjectName
Step 3: Create your XML Data Source
Now the interesting bit, create a new non-shared datasource (Embedded connection).
Type: XML
Connection String: Expression
Credentials: Do not use credentials
Expression should be as follows:
| =Parameters!SelectedProject.Value & "/_vti_bin/lists.asmx" |
Step 4: Now create your Dataset using XML
Now you need to create your first Dataset in this XML data source, this is where we reference the actual list item, in this example Project Risks.
Create a new dataset using the following query:
<Query>
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems"/>
<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
</Query>
|
Populate the Parameters with the "listName" (note: this is case sensitive) parameter with the value "Risks":
Step 5: Select your Fields to include in the report
Now the annoying bit, under Fields you must include each field that you want to include in your report, however because we are dynamically generating the data source we cannot automatically populate this.
Note: To automatically populate this list hard code the data source to an actual workspace site then when you create the query for this dataset select Refresh Fields.
Otherwise you can manually populate these, just open the list in question in a workspace and open list settings:
Then use the Column name in the Fields list source by simply prefixing each name with ows_, for example: ows_Title
Resulting in something like this:
Step 6: Finally Create a Test Report to see the Results!
Now add a table to your report and add those columns to preview the data, you should see something like this:
Unfortunately you might notice some fields being blank, there’s actually something we forgot! One of the possible parameters in the dataset above is "viewName", that does exactly what you would think select’s the desired view, so as my example above (using the EPM2007DEMO databases with default column names) Owner, Description and Mitigation Plan are all blank, this is because the Default view configured for that list does not include them.
So you can fix this by changing the default view to include the required fields or by creating your own view and using the viewName parameter. Note though; that parameter requires the GUID of the view not it’s name, so you need to use something like http://meyerweb.com/eric/tools/dencoder/ to decode the GUID from the URL of the view. (See the above linked blog by David Wise for additional details on the parameters available)
One that is done, you should now see something more like this:
Looking good! (Almost!)
Just one last thing there, you can see the special characters in the data, for the Description and Mitigation Plan it is an easy matter of changing the ‘Placeholder Properties’ to Markup Type: HTML. However for the Owner and as you’ll find various other fields they contain internal codes used by SharePoint, these have to be removed with code, again David’s blog above has some code to do just that, or if you’re like me and didn’t quite bother to read down that far you can create your own and add it to the Report Properties – Code section:
Public Function CleanSPSInput(strIn As String) As String
Dim RegEx as System.Text.RegularExpressions.Regex
‘ Replace invalid characters with empty strings
if strIn = "" OR IsDBNull(strIn)
Return ""
Else
Return Regex.Replace(strIn, ".+;#", "")
End If
End Function
|
Then just replace your Owner field name with the following expression:
| =code.CleanSPSInput(Fields!Owner.Value) |
Now your report should look something like this:
All Done!(Well for me at least)
What’s the catch?
There is actually a big one which you might have guessed: performance.
The above report is effectively generating the report by browsing to each page and pulling down each list item every time the report is generated. For one single project like in the example above, that’s not such an issue, but if you wanted to create a portfolio summary report for example or batch process a single project report for all active projects then performance would quickly become a problem.
Much of that can be addressed through Reporting Services itself, using caching and pre-generation, etc or better yet using SQL Integration Services (SSIS). With that and some much more interesting scripting a DTS job could be created to setup your very own staging database using the same XML methods as above! Definitely something to blog about another time…
But what about??
Yes there are lots of questions left unanswered;
- What if someone renames the list used above?
- What if someone changes / deletes the view I selected?
- What if …
I think you can see some of the limitations here and possible also how they can be overcome (workspace template standards, etc) but I’ll leave that to you.
