SSRS Display Zero for Nulls

Josh,

So you have a report where there are null values in some of the text boxes.  To display zero instead, use this handy expression.

Create Hash of Multiple Columns

Josh,

So, you need to compare several columns of one table to several columns of another.  Perhaps you are checking to see if data has changed before updating a row, or you need to track if multiple columns have changed.  Besides doing a bunch of AND statements, a nifty feature of SQL is the HASHBYTES function.

Using ROW_NUMBER() to Get the Last Event for Each Category

Josh,

So the scenario is you have a table with a list of events like this:

CREATE TABLE [dbo].[Events](
   [EventDate] [datetime] NULL,
   [Category] [varchar](10) NULL,
   [EventDescription] [varchar](60) NULL
)  

You only want the latest record for each Category.  One way to accomplish this is the ROW_NUMBER() function...

SSAS Dynamic Prior Period

Josh,

When using the prior period function and you want the calculation to work on any level of the hierarchy, you'll need to remember the "CurrentMember.Level" syntax:

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.

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

Function to Only Display Last Word in SSRS

Josh,

So you have an MDX query that returns back months like "FY 2013 - 2014 January", but you only want to show "January" on the report.

Here is the function to accomplish this: