One Foot In The Cloud

SQL Server Reporting Services - Creating Professional Tool Tips

2014-10-13T20:15:00.004+01:00

Creating tools tips in SSRS charts is very straightforward, simply right click on the series in the chart and then select Series Properties.

Then you can choose one of the fields in the data set as the tool tip using the drop down arrow.

This gives quite basic information but you can create a more customised tool tip using the fx button. Once you are in the function section you can

Add labels
Include the position on the X or Y axis
Show multiple pieces of information on separate lines
Format numbers using commas or decimal point accuracy

The code to achieve this is

="Channel: " + Fields!Channel.Value & VbCrLf + "Day" + ": " + Fields!DayNameOfWeek.Value & VbCrLf + "Orders" + ": " + FormatNumber(CStr(SUM(Fields!Tickets.Value)), 0)

The spaces between the lines can be created by & VbCrLf

When using numbers you must cast it as a string using CStr() around the field value. By wrapping the string with FormatNumber( , 0) you can format the way a number appears. This will include a comma between thousands and not include any decimal places. If you change the 0 to 2 you will include two decimal places.

All of the strings can be concatenated together using the + character. Labels can be created by enclosing text in between quotation marks.

SSRS - Order By Day Of The Week

2013-10-06T16:00:00.000+01:00

In SQL Reporting Services there is no out of the box way of order a report or chart by the days of the week. If you try to order by the name you end up with the days alphabetically sorted which is not very helpful.

One of the ways around this is to click use a switch statement in the Sort By section.

=SWITCH(Fields!DateNamePart.Value = "Monday", 1, Fields!DateNamePart.Value = "Tuesday", 2, Fields!DateNamePart.Value = "Wednesday", 3, Fields!DateNamePart.Value = "Thursday", 4, Fields!DateNamePart.Value = "Friday", 5, Fields!DateNamePart.Value = "Saturday", 6, Fields!DateNamePart.Value = "Sunday", 7)

After you have added this the days are now sorted in order and the data now has some visual value and it is much easier to spot when the trend in the chart.

DAX for DATEADD(DD, -1, GETDATE())

2013-08-27T20:01:00.000+01:00

In DAX the equivalent of GETDATE() is NOW().

The DATEADD function varies depending on what time unit you are adding. To add or subtract days it couldn't be easier.

NOW() -1 takes a day from the current time. NOW() + 5 adds five days.

To add a month you use the EDATE function. So to add two months to the current date you use EDATE(NOW(), 2).

So to use DATEADD(DD, -1, GETDATE()) in a filter or calculate table you would do something like.

CALCULATETABLE('Contacts','Contacts'[CreatedOn] > now() -1 )

SSRS 2008 R2 - Alternate Bar Chart Colours

2012-12-06T14:09:00.001+00:00

A simple Bar Chart or Column Chart in SQL Services Reporting Services (SSRS) that only has one category group can look a bit boring.

An easy way to make it a bit more interesting on the eye is to change the fill colour on the bars (or columns) for the alternating ones.

Right click on the series and select Series Properties.

Select Fill and then click on the expression button.

In the expression box simply paste in the same code you use to alternate rows in a table.

=IIF(RunningValue(Fields!County_Name.Value,COUNTDISTINCT,NOTHING) MOD 2 = 0, "#4573D5","#7399EA")

In this case I'm using colour codes #4573D5 and #7399EA but you can also use names like this:

=IIF(RunningValue(Fields!County_Name.Value,COUNTDISTINCT,NOTHING) MOD 2 = 0, "LightGrey","White")

Your chart will now be a bit more pleasing on the eye.

KB2661254 Kills My Sharepoint Foundation Web Front End

2012-10-15T15:40:00.003+01:00

The Windows Update KB2661254 took down one of my Sharepoint Foundation Web Server Front Ends today. Luckily I have split when my servers update so it did not take down my Farm.

This update only seems to be affecting the Web Front Ends on Windows Server 2008, the Web Front Ends on Windows Server 2008 R2 are unaffected.

This Windows Update is to fix a problem that Microsoft are having with minimum certificate key length. It is not a security update so I blocked it in WSUS and uninstalled it and now I am back up and running.

Be sure to test this update before putting it onto your live Sharepoint 2010 Farm.

Sharepoint 2013 - Sign in as Different User has been removed

2012-09-19T15:21:00.000+01:00

The ability to sign in as a different user, which was very handy for testing permissions has been removed in Sharepoint 2013.

To get around this I had to use the old "Shift + Right Click" Run As Different User, which isn't quite as convienient.

Where is the Recycle Bin in Sharepoint 2013

2012-09-17T11:53:00.000+01:00

In Sharepoint 2013 the link to the Recycle Bin is no longer in the quick launch.

To get to the Recycle Bin click on Site Contents and you will then find it in the top right corner.

Sharepoint Infopath Cannot Save The Form

2012-09-14T16:44:00.003+01:00

A user complained that they could not save a form to an info path library in Sharepoint 2010. The error that they recived was

"InfoPath cannot save the form. The specified location cannot be accessed. Check that the location you entered is correct and that you have appropiate permissions."

I checked the user's permissions and found no problem but every time he tried to save the form he got the error. He was the only person with the problem and trying it on a different PC didn't solve anything. I checked the library to see if there was a previous file that had not being checked in but there was no sign in there.

Eventually I got him to try saving it with a different name and it worked without any problem. There was no sign of the file with the old name in the recycle bin or anywhere in the library, so it seems like a bug but the workaround was to name the file differently.

Sharepoint 2013 People Picker

2012-09-14T14:11:00.003+01:00

The People Picker in Sharepoint 2013 is a bit slicker.

Instead of having to click on search, start entering a name, finding the person and then adding you now just have to start typing the name in the box. As you type a dropdown is created of people who have those letters in their name, which gets filtered as you type. Simply click on the required person from the dropdown to add them.

Click on Show Options and you can then select a group or permission level.

Notify Users Who Leave Documents Checked Out

2012-03-20T14:44:00.002+00:00

We have a problem with some users, who check out documents but never check them back in. I wrote a Powershell script to loop through the libraries and look for files that had been checked out for longer than 1 day.

All of our libraries have a view called "Checked Out" documents which shows all the checked out documents in the library. The script creates a link to this view but you can easily change that to create a link to the library or directly to the file.

$snapin = Get-PSSnapin | Where-Object {$_.Name -eq 'Microsoft.SharePoint.Powershell'}
if ($snapin -eq $null)
{
    Write-Host "Loading SharePoint Powershell Snap-in"
    Add-PSSnapin "Microsoft.SharePoint.Powershell"
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
}

$site = Get-SPSite http://siteurl
$SmtpServer = "smtp.yourserver.com"
$Subject = "File Checked Out Longer Than 1 Day"
$from = "service.desk@yourcompany.com"
$date = Get-Date
$Warning = ((get-date).adddays(-1))

#Loop through the webs
foreach ($web in $site.AllWebs)
{
# loop through all lists in web
foreach ($list in $web.Lists)
{
    # examine if BaseType of list is NOT a Document Library
    if ($list.BaseType -ne "DocumentLibrary")
    {
      # forget the rest and return to top
      continue
    }
    # loop through each item
    foreach ($item in $list.Items)
    {
      $file = $item.File
      if(($file.CheckOutStatus -ne "None") -and ($file.CheckedOutDate -lt $warning))
      {
        $User = $file.CheckedOutByUser
        $Email = $file.CheckedOutByUser.email
        $body = "Hi " +$User + ",

You have had the following file checked out from Sharepoint for more than a day. Click on the link below to see checked out documents in this library.

" +$file.name + "     """ + $web.url + "/" + $list + "/Forms/Checked%20Out%20Documents.aspx" + """

Please check this document back in If you are no longer using it.

Thanks"
        $smtp = new-object Net.Mail.SmtpClient($SmtpServer)
        $smtp.Send($from, $Email, $subject, $body)
      }
    }
}
}

This document cannot be opened for editing

2012-02-24T16:53:00.000+00:00

A user told me that they were no longer able to edit word documents in the Extranet. This was not an issue in the past but seems to be a new problem since Office Web Apps has been installed. The option to Edit in Word was no longer available.

When the user tried to Edit in Browser they recieved "This document cannot be opened for editing" error.

A check in the ULS logs found this error but it didn't shed much light.

System.TypeInitializationException: The type initializer for 'Microsoft.SharePoint.Utilities.SandboxCommunicator' threw an exception. ---> System.ComponentModel.Win32Exception: Access is denied

I tried to Edit the same file internally and this opened without any issue which prompted me to check the settings in Authentication Providers. To get there open your Central Administration site, and then click on Manage Web Applications. Then highlight the affected site and click on Authentication Providers in the ribbon.

Choose the zone that is causing problems and scroll to the end to check the Client Integration setting. If this is set to No you can set it to Yes. Note that this will allow the user to edit in Word but they may have to enter their credentials twice. Once this has been updated check your Extranet again to see if Edit in Word is now available.

Sharepoint Workspace - Not all views sync

2012-01-13T15:11:00.000+00:00

Working on a library in Sharepoint Workspace I noticed that not all views for that Library were syncing.

I found a few posts that said custom views with filters could not sync however this was not correct. All of the views in the library had filters but four of them were missing from Workspace. I looked further and found that the views that were missing were filtering on a date calculation or on a column criteria not being blank.

Removing the date or entering a value fixes this issue and the views sync but this changes the behaviour, so the users may have to do without in Workspace.

Sorting can also cause the view not to appear in Workspace. Sorting on Modified for example works but sorting on Owner or custom columns.

Save Template Error - No Windows Identity for DOMAIN\User

2012-01-06T12:30:00.000+00:00

When trying to save a template today I encountered an error, I never had an issue saving templates before so I had a look in the logs to check what was causing it. In the logs I found the following error:

SPSecurityContext.WindowsIdentity: Could not retrieve a valid windows identity for NTName='DOMAIN\User', UPN='User@domain.com'. UPN is required when Kerberos constrained delegation is used. Exception: System.ServiceModel.FaultException`1[System.ServiceModel.ExceptionDetail]: WTS0003: The caller is not authorized to access the service. (Fault Detail is equal to An ExceptionDetail, likely created by IncludeExceptionDetailInFaults=true, whose value is: System.UnauthorizedAccessException: WTS0003: The caller is not authorized to access the service.    at Microsoft.IdentityModel.WindowsTokenService.CallerSecurity.CheckCaller(WindowsIdentity callerIdentity)     at Microsoft.IdentityModel.WindowsTokenService.S4UServiceContract.PerformLogon(Func`1 logonOperation, Int32 pid)     at SyncInvokeUpnLogon(Object , Object[] , Object[] )     at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs)     at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc)     at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc)     at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage4(MessageRpc& rpc)     at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet))

Claims Authentication No windows identity for domain\user.

Looking online gave me no answers until I came across this blog by Doug Ware which talked about a change made by KB979917.

In the Application Settings for my Sharepoint Site I changed the aspnet:AllowAnonymousImpersonation to false and then performed an iisreset (beware that this will cause disruptions to your sharepoint website so either take the servers out of the load balancer one at a time or organise downtime).

After that I tried to save a site as a template and got a rather pleasing Operation Completed Successfully.

On my UAT environment I got a further error:

VtemplateManager::loadUncustomizedFormSchema failed with error code 0x80004005.    a93fecf5-fef9-4498-ae66-569b5872f930

CFormWebPartXmlFromRowSetData::Parse failed with 0x80004005    a93fecf5-fef9-4498-ae66-569b5872f930

CWebPartXmlBuilder::Add failed with 0x80004005    a93fecf5-fef9-4498-ae66-569b5872f930

Cannot complete this action. Please try again. a93fecf5-fef9-4498-ae66-569b5872f930

Which I fixed by running the script in my post about fixing broken discussion boards. After running the script I tried to save the template and it completed successfully.

Retention Policy for Sharepoint Foundation 2010

2012-01-03T12:54:00.000+00:00

There are no Retention Policies available for Sharepoint Foundation 2010 but you can get around this if you are a little bit creative. I have created a Powershell script that will delete files from a library that have not been modified within a specified time.

In our case we wanted to setup a temporary library for files that would be deleted if they had not been modified in the past three months.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")

$SPWeb = Get-SPWeb http://yourSiteURL/
$list=$SPWeb.Lists["Documents Library Name"]
$listItems = $list.Items
$listItemsTotal = $listItems.Count

$ThreeMonthsAgo = ((get-date).AddMonths(-3))

for ($x=$listItemsTotal-1;$x -ge 0; $x--)
{
    if([System.DateTime]$listItems[$x]["Modified"] -lt $ThreeMonthsAgo)
    {
        $listItems[$x].Delete()
    }
}

This script will delete the files but you could send them to the Recyle Bin instead using

$listItems[$x].Recycle()

You can change the expiry date using there properties to best fit your requirements.

 AddDays
 AddHours
 AddMilliseconds
 AddMinutes
 AddMonths
 AddSeconds
 AddTicks
 AddYears

After that I simply setup a Scheduled Task to run the Powershell script daily and I now have a Retention Policy on our temporary library.

Sharepoint 2010 - Change Library URL

2011-12-19T12:43:00.001+00:00

There are times when we need to change the URL for a list or library. In order to do this you need to use Sharepoint Designer. Open the Site where the document library or list is located and then choose All Files.

Select the library or list, right click and choose to Rename.

Type in the new name which will make up the new URL. This causes the Library or List URL and Name to change while the items in it remain unchanged. There is one more bit of housekeeping needed and that is to update the Quick Launch URL to the new URL.

Open PDF in Browser Not Working

2011-12-15T13:10:00.002+00:00

In Sharepoint 2010 you can change the Web Application settings so PDFs open in the browser. You can do this in Central Admin, Manage Web Applications. Select the Application and then click on General Settings. In the Browser File Handling section you can change the settings to be Permissive.

Once this change is made it should work but you may still have a problem with sites that have been created from a template. You can run this script to update these sites to Permissive which will then fix all of the remaining sites.

$site = Get-SPSite(“http://siteurl")
foreach ($web in $site.AllWebs) {
    Write-Host "Inspecting " $web.Title
    foreach ($list in $web.Lists) {
        if($list.browserfilehandling -eq "Strict") {
            Write-Host "Changing " $list.Title
            $list.browserfilehandling = "Permissive";
            $list.update();
            $site.url,
            $list.title,
            $list.browserfilehandling
        }
    }
}

The user does not exist or is not unique

2011-12-14T12:26:00.000+00:00

One of the web front end servers in our farm was throwing an error when users were being added to a Sharepoint group. The error displayed was the rather generic User does not exist message so I went sniffing in the logs.

Using the Correlation ID I found what was causing the exception,

An exception occurred in AD claim provider when calling 
SPClaimProvider.FillResolveClaim(): Requested registry access 
is not allowed.

A search on this error led me to Bernardo's excellent blog on the problem. I followed his steps and found that my app pool account was indeed being refused access to the registry key

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\
Web Server Extensions\14.0\Secure

I added the App Pool account to the WSS_Restricted_WPG_V4 instead of the local admin group on that server, it worked fine and resolved the issue.

Crawl Status Paused for External Request

2011-12-13T13:26:00.001+00:00

There is a known issue with the Symantec Backup of Sharepoint 2010 foundation which causes the search crawl to be paused but does not resume it. Read about it.

Rather than manually restarting the crawls I wrote a quick script to do it instead.

Add-PSSnapin Microsoft.SharePoint.PowerShell

$searchapp = Get-SPEnterpriseSearchServiceApplication "Search Service Application"
 $searchapp.resume()

Now the crawler is restarted every night after the backup and I no longer have to remember to do it in the morning.

Remove Renamed Application Database

2011-12-12T16:07:00.001+00:00

After cleaning up a few of the non friendly Database names that SharePoint installs I noticed that they were still being referenced.
In Central Admin -> Manage Database Upgrade Status you see that the removed databases are still listed and their status is Not Responding.

You can remove them using Powershell using the following command

Get-SPDatabase | Where{$_.Exists -eq $false} | ForEach {$_.Delete()}

Sharepoint 2010 - Discussion Boards stopped working

2011-12-12T12:33:00.000+00:00

I hit an interest problem recently when users started getting errors when they tried to add items to Discussion boards that had been working previously. The error message they were getting was a fairly generic message informing them that the list does not exist.

I grabbed the list guid from the url in the Sharepoint logs that matched this Correlation ID, a
SELECT * FROM AllLists WHERE tp_ID = 'AAFC8333-CC4B-4A4A-83D9-CE14AE17EE76'
promptly brought back the details of a list which proved that it did in fact exist.
I did a bit more digging but didn't get very far so I hit google but did not find much help there. Eventually after googling obscure errors in the Sharepoint Logs I came across this page which talked about how a previous CU had caused their Discussion Boards to break. They had troubleshooted the problem with Microsoft who provided this script:

UPDATE awp SET awp.tp_ContentTypeId=0x0, awp.tp_BaseViewID=0
FROM AllWebParts as awp
JOIN AllLists as al
ON awp.tp_ListId=al.tp_Id
WHERE al.tp_FeatureId='00BFEA71-6A49-43FA-B535-D15C05500108'
AND al.tp_ServerTemplate=108
AND awp.tp_BaseViewID=3
AND awp.tp_View IS NULL
GO

I ran the script on my testing box and it fixed the problem, I then ran it on my production DB and "jobs a good un"!!