SSRS support for both US Letter and UK A4

One of those annoying things about the US is they use different paper sizes to us Brits.

US Letter is 215.9 by 279.4 mm (8.5 by 11.0 inches)
The UK equivalent, A4, is 210 by 297 mm (8.26 by 11.69 inches)

One of our customers is based in the UK but has remote sales offices in the US, so their SSRS report are set to A4 and when they printed them in the US offices, the footer is cut off. The fix? Simple. Change the height to 279.4 mm.

Assuming you have a footer, it will now hover 17.6mm higher up the A4 page, which hopefully won’t be too terrible. It will now be American proof.

Windows Insider – new features

Today’s Windows Insider email included a few new cool features, here are my Top 3

Do more at once with the new Compact Overlay window. Keep watching a movie or video chat on one corner of your screen — even when switching apps to check email or browse the web. When an app window enters compact overlay mode, it’ll be shown above other windows so it won’t get blocked. Look for updates to the Movies & TV app and Skype Preview app to take advantage of this feature in the near future.

A smart way to lock your PC. Dynamic Lock automatically locks your Windows 10 PC when you’re not around, based on the proximity of your Bluetooth-paired phone. If your Bluetooth-paired phone is not found near your PC, Windows turns off the screen and locks the PC after 30 seconds.

Public Preview of Windows Analytics: Update Compliance. Update Compliance is a free service that provides you with a holistic view of Windows 10 update compliance for the devices in your organisation, including installation status for both monthly quality updates and new feature updates. Click here for details on how to set up the service for your organisation.

MSSQL server failed on Ubuntu on Windows 10

I’ve been getting errors with MSSQL server on Ubuntu on Windows 10. The error,

Failed to connect to bus: No such file or directory
dpkg: error processing package mssql-server (–remove):
subprocess installed post-removal script returned error exit status 1
Processing triggers for libc-bin (2.23-0ubuntu5) …
E: Sub-process /usr/bin/dpkg returned an error code (1)

I managed to fix it in the end by

Of course, you should try uninstalling correctly first, which is latter of the two

I’ve included remove Microsoft GPG key and un-register the Microsoft SQL Server Ubuntu repository. For more read Microsoft doc.

Date Format

I’ve had a problem recently when it can to formatting a datetime as just date where FormatDateTime didn’t work

The fix was to change it to Format.

Oddly, despite the parameter being a datetime, I found I still had to cast it as a date.

FormatDateTime was introduced in Vs 2008. I’m not 100% sure why this didn’t work.

TextBoxImpl

Another error I hit

Warning               1

[rsRuntimeErrorInExpression]

The Value expression for the textrun ‘Textbox28.Paragraphs[0].TextRuns[0]’

contains an error: Overload resolution failed because no Public ‘/’ can be called with these arguments:

‘Public Shared Operator /(d1 As Decimal, d2 As Decimal) As Decimal’:

Argument matching parameter ‘d2’ cannot convert from ‘TextBoxImpl’ to ‘Decimal’.

C:\Projects\Reports\1. Report.rdl

Thankfully Google found Qiuyun answer. I was missing the .Value at the end.

So (wrong)

=ReportItems!Textbox1

Fixed (working)

=ReportItems!Textbox1.Value

SSRS copy and paste fail

In case you haven’t heard, I’ve started a new job and one of my first tasks was to speed up a SSRS report. One of the first issues I stumbled across was this:

An error occurred during local report processing.

The definition of the report ‘/1. Report’ is invalid.

The Value expression for the textrun ‘Tx412.Paragraphs[0].TextRuns[0]’ contains an error: [BC30456] ‘RdlObjectModel’ is not a member of ‘ReportingServices’

Looking into it the issue it appears its expanded expressions when it got copied – it’s basically making the it fully qualified.

So

Cint

becomes:

Microsoft.ReportingServices.RdlObjectModel.ExpressionParser.VBFunctions.Cint

The fix was to just edit to code then do a find and replace, replacing the added reference with nothing. Simple.

SSRS 2016 by default

For the past 4 months I’ve been using Visual Studio 2015 rather then Visual Studio 2013 and yesterday, I hit a bug with SSRS (shows how much I love SSRS).

The bug appeared when it came to deployment

The error was deploying SSRS report

Exception calling “CreateCatalogItem” with “7” argument(s): “The definition of this report is not valid or supported
by this version of Reporting Services. The report definition may have been created with a later version of Reporting
Services, or contain content that is not well-formed or not valid based on Reporting Services schemas. Details: The
report definition has an invalid target namespace
‘http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition’ which cannot be upgraded.

The report that error deploying was the one I had just changed. Looking at the code diff in Visual Studio Team Services you can see its added in some new 2016 goodness. I checked and double-checked my settings. Visual Studio 2015 wants to give you a 2016 file. If you manually deploy from within Visual Studio it works. Checking out the bin\debug folder reveals that it’s using the 2016 version to build a legacy version, which makes sense as the 2016 is the old (2008R2) with extra goodness slapped in – like being able to define parameter location.

So, we now have to build our SSRS reports. To do so, I’ve just created a PowerShell script that builds it using devenv.exe with the parameters for a silent build. This worked great, only it took over 50mins to run. The main lag was every time it loaded the solution it builds\checks\something with the database projects. It takes an age. The fix was to create a dedicated Report solution file (Report.sln) – I did this with a PowerShell script that copying the main solution file, which included all the SSDT (database), Integration (SSIS) packages, analysis models as well as the SSRS reports, then just removing all projects but the SSRS projects. I did this via PowerShell just because I don’t like extra manual steps. It’s a pain, when you add a new SSRS project you’d have to add it to Report.sln, which you’d probably forget and waste spend time debugging a silly mistake.

Creating the Report.sln reduce the build time down to just over 2mins (from over 50mins!), so it was worth doing.

Swollen SSISDB caused by Continuous Deployment

I recently had incident logged where the drive that hosted SSISDB database filled up on the UAT environment. We had SSISDB set to only keep 2 copies of the projects, however the additional copies are only removed when the SQL job is run – SSIS Server Maintenance job.

The SQL job is scheduled to run at 00:00 (midnight) every day, which is the default, which runs two steps

SSIS Server Operation Records Maintenance
TSQL Script: EXEC [internal].[cleanup_server_retention_window]

SSIS Server Max Version Per Project Maintenance
TSQL Script: EXEC [internal].[cleanup_server_project_version]

The problem is we do Continuous Deployment (CD), we use Octopus Deploy to automate our deployment process – it’s so effective we often do multiple releases a day. This in turned caused the log file to swell when a large number of old projects are removed, which results in the drive filling – despite the recovery model being set to simple.

The solution was simple, add an additional log file, located on another drive (I used the backup drive as it has tons of free space), run the job, truncate the log files, then remove the extra log file. Then it was just a question of running the two cleanup stored procedures post deployment.

Pretty time

This week I’ve been working on another new cube which had the time spent, which is in minutes. To pretty it up it wanted to be in days, mins and hours.

Here is a simplified version of the final output

prettytime

and the hopefully easier to understand MDX code

Adding the days option isn’t much more work – it does however make the code look alot more angry. Also you have the issue of what is a day – is it 24 hours? Is it the working hours, or is it less, is it the realistic billable time after you deduce admin time?

Dangers of distribution lists

Distribution list are amazing things, they allow us to send mail to our team without having to remember who exactly is in our team, this is especial true in this modern age where we are part of many different teams, sometimes without even realising. These lists can contain hundreds or even thousands of members and the latest NHS IT bug has left IT departments maybe wanting to double-check their distribution lists – hopefully before the security team comes a knocking.

Luckily, this is really easy in Excel 2016. Built into Excel 2016 is PowerQuery and one of the out-of-box connectors is Active Directory. With a bit of PowerQuery magic, you can easily pull this data out.

So we want to:

  • Connect to our Active Directory
  • List out all our Groups
  • Filter to email-able Groups
  • Include
    • the group owner
    • the members
    • any security \ restrictions on who can email the list

Note: If your unluckily enough to be running an old version of Excel, it possible to do the same thing in Power BI Desktop, which is a free download.

Below is the M code to do this:

For more help on how to enter the M code into Excel see my previous post.

UPDATE: Please note that this only works with Active Directory and doesn’t work with Azure Active Directory. Others have said this is possible via odata or such however I haven’t tried… yet.