Thursday, May 29, 2014

SSRS Format Expression for Millions


So you want to display a number in millions (i.e. $2,123,456 as $2.12) on an SSRS report, perhaps as a data label on a chart.

Here is the format expression to do so as well as for Thousands and Billions.

Wednesday, May 28, 2014

SSAS Binding Too Small


So you've updated some tables used in a cube by increasing a column's length.  When you go to process the cube you are getting an error like "Errors in the back-end database access module. The size specified for a binding was too small, resulting in one or more column values being truncated."

Seems easy enough, right?  You just need to increase the Dimension Attribute's length.  Make sure you do it for both the NameColumn and KeyColumns sections:

Thanks to

Tuesday, May 13, 2014

Maximum Column Content Lengths


So you are working on loading data into a table from another table and you're getting truncation errors.  To keep the destination table as small as possible for speed, you need to find out which column is causing the truncation error and how much you need to increase the destination columns size.

Let's use the handy SYS schema to help us out...

Thursday, May 8, 2014

MDX Calculated Member Check if Measure is Null


So you want to create a count of a measure when it is not null.  To do so in MDX, you can do the following:

WITH Member [Measures].[Count of Non Null]

IIF(ISEMPTY([Measures].[Might Be Null - Avg]), NULL, 1)

       [Measures].[Might Be Null - Avg],
[Measures].[Count of Non Null]
       [Dim Date].[Fiscal Quarter].[Fiscal Quarter].ALLMEMBERS
   } ON ROWS

Tuesday, May 6, 2014

SQL Format Function


If you need a date/time in a format other than the standard ones covered by the CONVERT function, try out the FORMAT function:

FORMAT(@SomeDateTime'yyyy-MM-dd'AS FormattedDateTime

This will turn a date/time like "2014-04-24 14:38:13.0000000" into "2014-04-24".