Substring

SQL Server reporting services: How to use the Split function in the SSRS expressions to get sub-string?

Standard

Problem Statement:
How do you use SQL Server Reporting Services (SSRS) expression to get sub-string from the inputted text?

Solution:
I am going to show you few SSRS expressions that you can use in your SSRS reports:

SSRS SQL Server Reporting Services Expression SPLIT

Here’s the same in a text:

Input:SSRS Expression used:Output:
[Date].[Fiscal Year].&[2008]Split(Parameters!DateFiscalYear.Value,”&”)(1)[2008]
[Date].[Fiscal Year].&[2008]Split(Parameters!DateFiscalYear.Value, “.”)(2)&[2008]
[Date].[Fiscal Year].&[2008]Split(Split(Split(Parameters!DateFiscalYear.Value, “&”)(1),”[“)(1),”]”)(0)2008
[Date].[Fiscal Year].&[2008]Parameters!DateFiscalYear.Value.Split(“&”)(1)[2008]

Conclusion:
In this post, you saw how to use the split function in SQL Server Reporting Services (SSRS) expressions to get a sub-string.

Example of an DAX Substring Equivalent:

Standard

DAX (Microsoft’ Data Analysis Expressions Language) does not have a Substring function but I needed something like that for the following problem:

I had domain/username as input and I needed to extract just the username part of the string.

Input format: domain/username

output format needed: username

Input column name: UserID

so here’s the DAX formula I used: RIGHT([UserID],LEN([UserID])-SEARCH(“”,[UserID]))

Note: The Formula is shown for demo purpose only, It may not work directly before making appropriate changes to the formula like making sure the column name is right & If the double quotes show problems, try deleting it & typing them back again.

Note that I combined some of the available DAX text functions to achieve what I was looking for. There might be other way to do this and I would be happy to learn about it too. Meanwhile, I hope if you reading this, this might give a good starting point while researching your DAX problem.