Second Life of a Hungarian SharePoint Geek

September 10, 2013

SharePoint reporting using a scheduled PowerShell script

Filed under: PowerShell, SP 2010 — Tags: , — Peter Holpar @ 20:49

We have same custom SharePoint application pages that enable custom reporting for our support group. Reports are created in plain text (comma separated values / CSV) format and can be opened from the UI via SharePoint application pages. Since some of the reports contain rather complex SharePoint queries, opening them “on-demand” takes some time and system resources as well. While the data in the reports isn’t very volatile, our goal was to automate the report creation, for example, scheduling the task for the midnight hours. We wanted the outcome of the reports to be stored in a SharePoint document library with folder names corresponding to the current date (+ postfix _1, _2, etc. if the folder already exists), the latest version of the reports stored in a dedicated folder called latest.

The “business logic” for the reports was already encapsulated in a custom .NET assembly as static methods with some parameters, we planned the automation however using PowerShell.

I share the results here, as it has some interesting points that one might find useful, like:

– Creating / extending the folder structure in the SharePoint document library using the current date pattern.

– Loading the custom assembly into the context of the PowerShell script.

– Calling static methods of .NET assemblies with parameters from PowerShell.

– Converting the text (String) return value of the methods to byte array to enable uploading the content as a new file into the SharePoint library.

So here is the script with some values that should be updated to match the parameters of your system:

# URL of the SharePoint site
$sitePath = "http://yoursite"
# web of the Reports doc. lib
$webPath = "/ReportsWeb"
# name of the doc. lib.
$docLibName = "Reports"
# name of the ‘latest’ folder
$latestFolderName = "latest"

$site = Get-SPSite($sitePath)
$web = $site.OpenWeb($webPath)

$date = Get-Date -Format "yyyMMdd"
$folderName = $date

$docLib = $web.Lists[$docLibName]

$tryCount = 0

# get the first available folder name corresponding to our date pattern
Do
{
  $newFolderName = $folderName
  $folder = $web.GetFolder( ($web.Url, $docLib.RootFolder.Url, $folderName -join "/") )
  $tryCount++
  $folderName = $date + "_" + $tryCount 
}
While ($folder.Exists)

Write-Host $newFolderName

#creating the new folder
$newFolder = $docLib.Folders.Add("",[Microsoft.SharePoint.SPFileSystemObjectType]::Folder, $newFolderName);
$newFolder.Update()

$enc = [system.Text.Encoding]::UTF8

# load the custom assembly (GACed!) into the PowerShell context
[void][System.Reflection.Assembly]::LoadWithPartialName("MyCompany.ReportHelper.AssemblyName")

Write-Host "Generating report 1"
$rep1 = $enc.GetBytes([MyCompany.ReportHelper.ClassName]::GetReport1($site.ID, "report param 1"))
$newFile = $newFolder.Files.Add($newFolder.Url + "/" + "Report1.csv", $rep1)

Write-Host "Generating report 2"
$rep2 = $enc.GetBytes([MyCompany.ReportHelper.ClassName]::GetReport2($site.ID, "report param 2"))
$newFile = $newFolder.Files.Add($newFolder.Url + "/" + "Report2.csv", $rep2)

# delete the ‘latest’ folder if already exists
Write-Host "Updating latest folder"
$latestFolderUrl = $web.Url, $docLib.RootFolder.Url, $latestFolderName -join "/"
$latestFolder = $web.GetFolder($latestFolderUrl)
if ($latestFolder.Exists)
{
  $latestFolder.Delete()
}

# re-create the ‘latest’ folder, copying the actual version
$newFolder.CopyTo($latestFolderUrl)

$web.Dispose()
$site.Dispose()

Write-Host Finished.

We scheduled the PowerShell script using the Task Scheduler component of Windows. A good introduction into scheduled PowerShell tasks can be found on Dmitry’s PowerBlog.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: