SSRS Format Expression for Millions

Josh,

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.

SSAS Binding Too Small

Josh,

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 http://shahfaisalmuhammed.blogspot.com/2012/03/size-specified-for-binding-too-small.html

Maximum Column Content Lengths

Josh,

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...

MDX Calculated Member Check if Measure is Null

Josh,

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]
AS
IIF(ISEMPTY([Measures].[Might Be Null - Avg]), NULL, 1)

SELECT
   NON EMPTY
   {
       [Measures].[Might Be Null - Avg],
       [Measures].[Count of Non Null]
   } ON COLUMNS,
   NON EMPTY
   {
       [Dim Date].[Fiscal Quarter].[Fiscal Quarter].ALLMEMBERS
   } ON ROWS
FROM
   [MyCube]

SQL Format Function

Josh,

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

SELECT
   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".