Filtering Data from a SharePoint List in SSRS Reports

December 22, 2011

I have created a few SSRS reports which pull data from SharePoint lists and while its relatively easy I have come across the same problem a few times. Every time I try and filter my results using dates it never seems to work as I expect.

In this latest scenario I had a custom SharePoint List which had a column for the item start date. All I wanted to do was present the user with 2 date pickers which allow them to set a start and an end date. I then wanted to execute a query using these to return all items where the start date was in between the selected dates.

I generated my CAML query using U2U CAML Query Builder and it worked as expected with some hardcoded dates.

Next I opened Visual Studio and created a new Report Project. I then created a new report using the wizard to help setup a new DataSource of type ‘Microsoft SharePoint List’. I entered the query using the one generated in U2U leaving the values as hard coded to test it was working.

After formatting the report I ran it and all was well. Next I decided to create two new parameters one for the start date and one for the end date. I set the default values to be the first day of the month for the start date and the last day of the month for the end date, see formula for default values below.

Start date default value: =CDate(DateSerial(Year(Now), Month(Now), 1))
End date default value: =DateSerial(Year(Now), Month(Now)+1, 0)

I checked and these displayed on the report preview with the correct default values. At this point I was thinking everything was going too smoothly and I was right. My final step was to update the query to add in the values from the date pickers. I started by adding parameters to the dataset properties and mapped these to my report parameters, see figure 1.

Figure 1
DSProps

Next I went to my query and removed the hardcoded, see figure 2, values and added what I think is the correct way to include parameters, see figure 3.

Figure 2
DSHardCodedQuery

Figure 3
DSParamQuery

As soon as I make this change my report stops returning any results. I have spent time looking around and playing with passing different formats of the dates but no matter what I do it simply doesn’t return any values.

For now as a workaround I have managed to get my report working by not including any filters in my query but by adding a filter on the dataset, see figure 4. While this works I am aware this will probably return all items from the list and then show or hide the rows as needed. If anyone can help with a better solution I would appreciate it.

Figure 4
DSRowFilters


PowerShell script for updating workflows

December 22, 2011

The other day when working on a project I needed to update some existing Visual Studio workflows. I had a Visual Studio project which contained a variety of workflows which were used across several SharePoint sites. While keeping all the workflows in one solution works it is not ideal for deployment as you can’t auto associate the workflows as part of the deployment.

I decided to try and automate the deployment so started thinking what my options were. Being a developer my first thought was code in a feature receiver on activated and deactivated, however I decided that this would be another opportunity to practice some PowerShell. This gave me the flexibility of changing the script if need be without having to alter code.

Before starting to write the script I went through in my head what it need to do and came up with the following:

  1. Get the list to which the workflow should be deployed
  2. Check to see if the workflow was already deployed
    1. If it was already deployed check to see if there were any running instances
      1. If there were running instances set the workflow to not allow any new instances but allow existing ones to finish.
      2. If not remove the workflow and go to step 3
    2. If not move on to step 3
  3. Associate the new version of the workflow to the list

 

With these steps in mind I started to create my script. I will cover each of the above and go through the script required to achieve the goal.

Point 1

First I wanted to get the list the workflow was associated with. This is very easy and can be accomplished by getting the web object then using that to get the list object much the same way you would if you were writing C#

Get Workflow List
  1. Write-Host 'Get web'
  2. $hrWeb = Get-SPWeb $siteURL
  3.  
  4. Write-Host 'Get Lists'
  5. $list = $hrWeb.Lists[$listName]
  6. $taskList = $hrWeb.Lists[$taskListName]
  7. $workflowHistoryList = $hrWeb.Lists[$historyListName]

 

Point 2

With the list the next task was to see if the workflow is currently deployed to the list. This presented a problem as my plan was to automatically deploy the workflow with a name and the date of the deployment i.e. “Workflow 22/12/2011’. This caused problems as I wouldn’t know the last deployment date so I had to use a like comparison on the name. I wasn’t comfortable just using a like comparison as I felt this wasn’t always going to get me what I wanted so I added an additional check to validate the name of the workflow but also if it was of the correct base workflow type.

Try and find workflow
  1. Write-Host 'Get base template'
  2. $basetemplate = $hrWeb.WorkflowTemplates.GetTemplateByName($workflowTemplateName,$culture);
  3. Write-Host $basetemplate.Id
  4.  
  5. #set up variable to hold workflow instance if we find it
  6. $existingWorkflow = $null
  7.  
  8. Write-Host 'Get workflow association'
  9. #loop through all associations on list
  10. foreach($tempWorkflowAssociation in $list.WorkflowAssociations)
  11. {
  12. #check if the base template id matches the base template of the current WF associaton
  13. #in additon check the name of the current WF association against the one we are interested in
  14. if($tempWorkflowAssociation.BaseTemplate.Id -eq $basetemplate.Id -and $tempWorkflowAssociation.Name -like $workflowName +"*" )
  15. {
  16. $existingWorkflow = $tempWorkflowAssociation
  17. break
  18. }
  19. }
  20. #check we have a workflow
  21. if($existingWorkflow -ne $null)
  22. {

 

Point 2.1

As you can see the last line in the previous snippet makes sure we have a workflow returned so at this point we know there is a version of the workflow we are interested in currently deployed to the list. Now I need to know if there are any running instances for this.

Check running instances
  1. Write-Host 'Got workflow associated with list'
  2. if($existingWorkflow.RunningInstances -ge 0)
  3. {

 

Point 2.1.1

If there are running instances these must continue working until they are complete but not allow any new instances of this workflow to be initiated. This can be done by setting the ‘No new instances’ option through the workflow settings in the UI or by the following command.

No new instances
  1. Write-Host 'There are running instances so set to allow no new running instances'
  2. $existingWorkflow.set_Enabled($false)
  3. $list.UpdateWorkflowAssociation($existingWorkflow)

 

Point 2.1.2

If there are no running instances then we want to remove the current version. This can be done by the command below

Remove workflow
  1. Write-Host 'No running instances so remove'
  2. $list.RemoveWorkflowAssociation($existingWorkflow)

 

Point 3

We are now in a position where we can try and associate the new version of the workflow to the list. As I mentioned above when attaching the new workflow I am adding it with the name and the date so I first build up the name then create an new workflow association object the add it to the list.

Associate new workflow
  1. Write-Host 'Create workflow association details'
  2. $date = Get-Date
  3. $workflowName = $workflowName + " " + $date.ToShortDateString()
  4. $newWorkflow=[Microsoft.SharePoint.Workflow.SPWorkflowAssociation]::CreateListAssociation($basetemplate, $workflowName,$taskList,$workflowHistoryList)  
  5.  
  6. $newWorkflow.AllowManual = $allowManualStart
  7. $newWorkflow.AutoStartChange = $autoStartChange
  8. $newWorkflow.AutoStartCreate = $autoStartCreate
  9.  
  10. Write-Host 'Add workflow to list'
  11. $list.AddWorkflowAssociation($newWorkflow)

At this point the new version of the workflow should be attached to the list. I started creating this with some hardcoded values but then extracted it out into a reusable function. The full function is below.

Fully assocaition function
  1. function AssocaiteWorkflow([string]$siteURL, [string]$listName, [string]$taskListName, [string]$historyListName,
  2.     [string]$workflowTemplateName, [string]$workflowName, [bool]$allowManualStart, [bool]$autoStartChange, [bool]$autoStartCreate)
  3. {
  4.  
  5. Write-Host 'Start WF assocaition for ' $workflowName ' on list ' $listName ' and site ' $siteURL
  6.  
  7. #get culture
  8. $culture= Get-Culture
  9.  
  10. Write-Host 'Get web'
  11. $hrWeb = Get-SPWeb $siteURL
  12.  
  13. Write-Host 'Get Lists'
  14. $list = $hrWeb.Lists[$listName]
  15. $taskList = $hrWeb.Lists[$taskListName]
  16. $workflowHistoryList = $hrWeb.Lists[$historyListName]
  17.  
  18. Write-Host 'Get base template'
  19. $basetemplate = $hrWeb.WorkflowTemplates.GetTemplateByName($workflowTemplateName,$culture);
  20. Write-Host $basetemplate.Id
  21.  
  22. #set up variable to hold workflow instance if we find it
  23. $existingWorkflow = $null
  24.  
  25. Write-Host 'Get workflow association'
  26. #loop through all associations on list
  27. foreach($tempWorkflowAssociation in $list.WorkflowAssociations)
  28. {
  29. #check if the base template id matches the base template of the current WF associaton
  30. #in additon check the name of the current WF association against the one we are interested in
  31. if($tempWorkflowAssociation.BaseTemplate.Id -eq $basetemplate.Id -and $tempWorkflowAssociation.Name -like $workflowName +"*" )
  32. {
  33. $existingWorkflow = $tempWorkflowAssociation
  34. break
  35. }
  36. }
  37. #check we have a workflow
  38. if($existingWorkflow -ne $null)
  39. {
  40. Write-Host 'Got workflow associated with list'
  41. if($existingWorkflow.RunningInstances -ge 0)
  42. {
  43. Write-Host 'There are running instances so set to allow no new running instances'
  44. $existingWorkflow.set_Enabled($false)
  45. $list.UpdateWorkflowAssociation($existingWorkflow)
  46. }
  47. else
  48. {
  49. Write-Host 'No running instances so remove'
  50. $list.RemoveWorkflowAssociation($existingWorkflow)
  51. }
  52. }
  53. else
  54. {
  55. Write-Host 'No workflow associated with list'
  56. }
  57.  
  58. Write-Host 'Create workflow association details'
  59. $date = Get-Date
  60. $workflowName = $workflowName + " " + $date.ToShortDateString()
  61. $newWorkflow=[Microsoft.SharePoint.Workflow.SPWorkflowAssociation]::CreateListAssociation($basetemplate, $workflowName,$taskList,$workflowHistoryList)  
  62.  
  63. $newWorkflow.AllowManual = $allowManualStart
  64. $newWorkflow.AutoStartChange = $autoStartChange
  65. $newWorkflow.AutoStartCreate = $autoStartCreate
  66.  
  67. Write-Host 'Add workflow to list'
  68. $list.AddWorkflowAssociation($newWorkflow)
  69. }

 

You can then call this in the standard way i.e.

Call function
  1. AssocaiteWorkflow "http://sharepoint" "Test List Name" "Tasks List Name" "Workflow History List Name" "Visual Studio Workflow Name" "Workflow Instance Name" $true $true $true

 

As with all PowerShell scripts while they are very useful please always test them on a development environment before running them on live. As always I can’t be responsible for any issues that arise so if you use this you do so at your own risk.


Custom errors in custom Central Admin pages

December 19, 2011

I have been developing using SharePoint for several years but the other day I needed to create a new page to be displayed in Central Administration. Since I had already installed CKSDev on my development environment I was able to use the ‘Central Administration Page’ template which creates the aspx page and the custom actions for a custom group and link.

I used this as a starting point and set about modifying it to meet my requirements. As with all projects/code I started to encounter some errors but I was unable to see the actual error detail. Instead I just got the standard error screen, see figure 1, telling me to configure the settings in the web.config.

Figure 1

StandardErrorScreen

I think most developers have seen this message a thousand times or more so I knew I had to set the CallStack attribute of the SafeMode element under the SharePoint element to true. In addition I set the mode attribute of the customErrors element to Off. As I mentioned most developers have done this several times so this in its self wasn’t new but what I found was even with these settings I still received the standard error screen, figure 1.

My first thought was I must have either set the wrong web.config or the settings were wrong but after checking and rechecking everything was correct. At this point I turned to Google and I found a few generic articles on setting the customErrors and CallStack but this wasn’t what I was interested in.

Eventually I found an article Custom errors in web.config and SharePoint 2010 which had a little section at the bottom which explains that if you put pages in certain locations then you also need to update additional web.configs. In my case I had deployed my page to the admin folder under 14/template. Once I updated the web.config here the full error details started to display and I could work out the actual issue.

Just go to show no matter how long you are doing something you can always learn Smile


Custom SharePoint Designer action not displaying in a workflow when selected from the actions list

December 8, 2011

As part of my studying for SharePoint development exams 70-573 and 70-576 I have been looking at areas I haven’t had any previous experience and trying them out. As part of this I decided to look at creating a customer SharePoint Designer action.

The scenario I decided to look into was creating an action which accepted some parameters and then created a new site at a specific location. I won’t go into the code for this as its fairly straightforward. What I wanted to highlight was the problems I encountered when trying to add the action to a test SharePoint Designer workflow.

When starting out I have a look on the web and found a few articles to get me going, see links below. I followed the first article but found when I deployed this the new action appeared in SharePoint Designer but when I clicked on this nothing happened the action didn’t appear in the workflow.

Looking at the comments on the site it appeared a few other people had the same problem but there was no answer out supplied. I checked and re-checked my .actions file, the manual web.config entry and setting in in the advanced section of the package in Visual Studio. All the components like the assembly name, version number and public token key all looked the same. Eventually out of frustration I copied the whole string  in Assembly Name attribute set in the package, see figure 1, and copied and paste it into the two other locations and it worked. At first I couldn’t believe this would make a difference as long as the assembly name, version number and token were correct but it seems like the string has to match exactly, see below figures 1 2 and 3 in which the only difference is figure 2 doesn’t have a space between the assembly name and the version and this doesn’t work.

Hopefully this will save someone else from the frustrations I enjoyed Smile

Figure 1

SharePointDesignerAction

Figure 2

SharePointDesignerAction2

Figure 3

SharePointDesignerAction3

Links

http://msmvps.com/blogs/sundar_narasiman/archive/2010/12/26/develop-custom-workflow-activity-for-sharepoint-2010-workflow.aspx

http://toddbaginski.com/blog/how-to-create-a-custom-windows-workflow-activity-and/


My first Powershell command

December 6, 2011

The other day I finally decided it was time to stop using STSADM and start learning PowerShell. I decided a good way to do this was to rewrite existing STSADM commands in PowerShell.

The first thing I decided to do was to have a quick look at the basics so after a Google I found a video by Todd Klindt called Windows PowerShell for SharePoint Server 2010 Administrators. I found this very useful as a starting point assuming no knowledge and it allowed me to get quickly up and running by searching for commands and getting examples on how to use certain commands.

After watching this I then decided to have a look on TechNet to review some of the commands I would need to know in order to deploy/update a solution package. I managed to find a list of exactly what I was looking for a full list of all PowerShell commands for features and solutions.

Using the commands for features and solutions linked above I started to plan what steps I need to take to as part of my PowerShell script. The feature and solution I was updating contained a Module which loads the a custom JS file to the web.

I think it is always good practice to manually write down the steps you want to accomplish before actually getting started writing any script/code as this allows you to plan out what you are trying to achieve and gives you something to refer to at a later date. With this in mind I worked out the deployment steps should be as follows:

  1. Deactivate feature on each web
  2. Uninstall solution
  3. Remove solution
  4. Add solution
  5. Deploy solution
  6. Activate feature on each web

 

Before starting my PowerShell script I first done some research to see if there was a way to detect if a certain command had finished running. The reason I investigated this was I have had experience before when writing STSADM commands which would not run a timer job was currently in progress and another command was initiated. After some looking around I found the ‘Wait For Command To Complete’ script, see below, on the MSDN site. This meant I was able to kick off a command such as uninstall then call my wait function so the next section of the script had to wait until the previous command completed.

Wait For Command To Complete
  1. # Wait for the SharePoint timer job name that contains "solution-deployment".
  2. function Wait4Timer
  3. {    
  4.     Write-Host -NoNewLine "`nFinding timer job"
  5.     
  6.     # The language-dependent display name of the timer job contains "Solution Retraction".
  7.     # while (($jd = Get-SPTimerJob | ?{ $_.DisplayName -like "*Solution Retraction*"+$fileName+"*" }) -eq $null)
  8.     while (($jd = Get-SPTimerJob | ?{ $_.Name -like "*solution-deployment*" + $fileName + "*" }) -eq $null)
  9.     {
  10.         Write-Host -NoNewLine .
  11.         Start-Sleep -Seconds 1
  12.     }
  13.     $jdName = $jd.Name
  14.     Write-Host "`njob: $jdName"
  15.     Write-Host -NoNewLine Waiting to finish
  16.     
  17.     while ((Get-SPTimerJob $jdName) -ne $null)
  18.     {
  19.        Write-Host -NoNewLine .
  20.        Start-Sleep -Seconds 1
  21.     }
  22.     
  23.     Write-Host
  24.     $jd.HistoryEntries | %{ Write-Host job history: $_.Status }
  25.     Write-Host
  26. } # End of function Wait4Timer.

 

With the wait in place I was then able to go about writing the rest of the script. Referring back to my 6 steps documented above I then proceed to flesh these out and add in the actual PowerShell script. After a few iterations I ended up with the script below. With me still being a novice in this area if there are any issues with this please let me know but I have tested this and it all seems to work ok.

PLEASE NOTE: If you take a copy of this script please run it on a development environment first as while this should be generic and should run on any farm it is always best practice to never run these scripts directly on live without first being tested elsewhere.

  1. # Wait for the SharePoint timer job name that contains "solution-deployment".
  2. function Wait4Timer
  3. {    
  4.     Write-Host -NoNewLine "`nFinding timer job"
  5.     
  6.     # The language-dependent display name of the timer job contains "Solution Retraction".
  7.     # while (($jd = Get-SPTimerJob | ?{ $_.DisplayName -like "*Solution Retraction*"+$fileName+"*" }) -eq $null)
  8.     while (($jd = Get-SPTimerJob | ?{ $_.Name -like "*solution-deployment*" + $fileName + "*" }) -eq $null)
  9.     {
  10.         Write-Host -NoNewLine .
  11.         Start-Sleep -Seconds 1
  12.     }
  13.     $jdName = $jd.Name
  14.     Write-Host "`njob: $jdName"
  15.     Write-Host -NoNewLine Waiting to finish
  16.     
  17.     while ((Get-SPTimerJob $jdName) -ne $null)
  18.     {
  19.        Write-Host -NoNewLine .
  20.        Start-Sleep -Seconds 1
  21.     }
  22.     
  23.     Write-Host
  24.     $jd.HistoryEntries | %{ Write-Host job history: $_.Status }
  25.     Write-Host
  26. } # End of function Wait4Timer.
  27.  
  28. Add-PSSnapin Microsoft.SharePoint.PowerShell
  29.  
  30. #get the current directory
  31. $scriptpath = $MyInvocation.MyCommand.Path
  32. $dir = Split-Path $scriptpath
  33.  
  34. #set the solution name
  35. $solutionName="SolutionName.wsp"
  36. #build up the full path to the file
  37. $solutionPath = $dir + "\" + $solutionName
  38.  
  39. #set the feature ID
  40. $featureID = "6208eb36-05cb-4109-a9d2-b620d3b1b34d"
  41. Write-Host 'Start to disable feature in each site'
  42. #please change the SharePoint site URL as appropriate
  43. $site = Get-SPsite http://sharepoint
  44. foreach($web in $site.AllWebs) {
  45. Write-Host "Deactivate feature for Web " $web.Url
  46. disable-spfeature -identity $featureID -confirm:$false -url $web.Url
  47. }
  48.  
  49. Write-Host 'Start to uninstall solution'
  50. Uninstall-SPSolution -identity $solutionName -confirm:$true
  51.  
  52. Write-Host 'Waiting for unistall job to finish'
  53. Wait4Timer
  54.  
  55. Write-Host 'Start to remove solution'
  56. Remove-SPSolution ?Identity $solutionName -confirm:$true
  57. Write-Host 'Start to add solution'
  58. Add-SPSolution -LiteralPath $solutionPath -confirm:$true
  59.  
  60. Write-Host 'Start to install solution to all web applications'
  61. Install-SPSolution ?Identity $solutionName -GACDeployment -confirm:$true
  62.  
  63. Write-Host 'Waiting for install job to finish'
  64. Wait4Timer
  65.  
  66. Write-Host 'start to enable Feature'
  67. foreach($web in $site.AllWebs) {
  68. Write-Host "Activate featur for Web " $web.Url
  69. Enable-spfeature -identity $featureID -confirm:$false -url $web.Url
  70. }
  71.  
  72. Remove-PSSnapin Microsoft.SharePoint.PowerShell


%d bloggers like this: