Ever needed to generate reports using SharePoint designer for all sites in a site collection? The following code takes a pre-pared list of sites from your site collection and programmatically runs the specified report on each.
The example used below automatically creates a ‘Customized Pages” report for every site, which is saved into a document library in the site collection. This is achieved by using Visual Basic (VB) code inside a SharePoint Designer module (Macro), to load each site and save the required reports.
The following is a full list of reports supported by SharePoint Designer. To run a different report, change the highlighted text to the type required for the report. If you require multiple types reports to be run, add a new line to save the report for each required type. To avoid report types for a single site or sub-site overwriting each other is saving multiple, I would suggest adding the name of the report type to the beginning of the filename. For more information, or specific details regarding the various reports available in SharePoint Designer 2007, please see Microsoft’s Use reports to measure site performance and usage page.
Report Types:
WebViewAllFiles
WebViewAspNetMasterPages
WebViewAssignedTo
WebViewBrokenLinks
WebViewBrokenLinks
WebViewBrowserTypes
WebViewCategories
WebViewCheckoutStatus
WebViewComponentErrors
WebViewCSSLinks
WebViewDailyPageHits
WebViewDailySummary
WebViewFolders
WebViewGhostedPages
WebViewLinks
WebViewMasterPages
WebViewMonthlyPageHits
WebViewMonthlySummary
WebViewNavigation
WebViewOlderFiles
WebViewOsTypes
WebViewPage
WebViewPublishStatus
WebViewRecentlyAddedFiles
WebViewRecentlyChangedFiles
WebViewReferringDomains
WebViewReferringURLs
WebViewRemoteSite
WebViewReviewStatus
WebViewSearchStrings
WebViewSharedBorders
WebViewSiteSummary
WebViewSlowPages
WebViewThemes
WebViewUnlinkedFiles
WebViewUsageSummary
WebViewVisitingUsers
WebViewWeeklyPageHits
WebViewWeeklySummary
Usage:
1: Generate a list of all sites in a site collection
- Start at the root/top level site of your site collection
- Go to Site Settings – Site Actions (menu) à Site Settings à Modify all site settings
- Under the Site Collection Administration – Select “Site Hierarchy” ( http://siteURL/_layouts/vsubwebs.aspx )
- Copy the list of urls for each subsite
- Paste into Excel (or similar)
- Remove unwanted columns (only one column containing site URLs should remain)
- Copy and paste / Save as a text document (each URL on a new line)
Note: To view a list of all sites, you must be logged in as a Site Collection Administrator.
2: Set up and run the SharePoint Designer macro
- Copy the following code into a SharePoint Designer module (Macro)
- Update the “filePath” variable to point to your sites list text file
- Update the “saveLocation” variable to point to the directory to save the report
VB:
Sub CustomizedPagesReport()
End Sub |
Posts
12 comments:
This is cool, but I just tried it and got a syntax error at the second If...
JohnB
Hi jwb3 thanks for your comment, the second "IF" statement should actually be the closing if statement for the first "If".
I have edited the code in the post so it should paste into a text editor properly.
To fix yourself,
Change the line 34, 35 from:
Line 34: End
Line 35: If
To:
Line 34: End If
Thanks
Daniel
That worked! Thanks, Daniel!
You are a lifesaver! This is exactly what I was looking for...
Thanks for sharing!
Lisa
Does running these customized reports have a performance impact on the sites.
Hi Manjeet,
Running any report using SPD, or any browser request for a page on a SharePoint site for that matter will have an impact on the server.
In general, using SharePoint Designer to make changes to pages on a site or running reports has minimal impact on the server. If SharePoint Designer wan't so slow when processing requests, this may be a different story.
Running the reports on all sites won't affect site performance directly, but will use overall server resources, which in turn may slow load times for any sites/pages hosted by the server.
Performing actions such as this should always be first tested in a development or test environment prior to running in a production environment, especially if you are concerned about performance impacts or a more serious error occurring.
If in a production environment, process the reports at a time when the server is under no pressure. You can monitor the server's performance while running the reports regardless. Always ensure that the entire environment is backed up before executing any major operation, although running reports using SharePoint Designer is relatively harmless.
Daniel
This only works for me when I choose WebViewAllFiles but I get an error on Ln43 whenever I substitute WebViewUsageSummary or WebViewMonthlyPageHits. Do you have any idea what's causing this?
New to all of this, but I received the following pop-up: "Compile Error: Argument not optional"
Not sure what I did wrong. :(
Daniel, this report would save me a ton of time but I am receiving the following error message:
Run-time error '429':
ActiveX component can't create object
on this line:
Set filesys = CreateObject("Scripting.FileSystemObject")
Any ideas why I am receiving this error message?
Hi All, I am newbie to Sharepoint designer coding, can anyone suggest where to write this code in sharepoint designer? what are the exact steps?
thanks
Julie
@Julie, This is done using a Macro running from SharePoint Designer, so you would need to create a new macro and past the code from the example above into the macro.
It is essential automating the process of running a separate instance of a report available from SharePoint Designer on various sites.
Please also check that you are able to successfully run the reports manually before executing from a macro to help prevent errors.
This article was originally written for SharePoint Designer 2007 and MOSS, which I haven't tried using SPD 2010.
Has anyone got this working in 2010?
Steps to create a macro in SharePoint Designer:
You should test the report you wish to automate manually first by running on one or more sites ("Site" menu -> Reports).
1. Open SharePoint Designer 2007 and a SharePoint site (eg. the top level site).
2. From the "Tools" menu, select "Macros", then "Visual Basic Editor"
3. Paste the code from above into the Module that loads when the Visual Basic Editor opens.
4. Complete Step 2 from this post to configure the macro.
5. Run the macro. You can do this from the Tools -> Macros dialog, or directly from the Visual Basic Editor under the "Run" menu, which I would recommend. Using the Debug -> "Step Into" / (F8) option will allow you to step through the code line by line which may also help.
Thanks
Post a Comment