CODEWITHSUNDEEP
sqlvb.netfunctionreporting-servicesssrs-2012
Istaley
Istaley

Reputation: 399

SQL function that evaluates values returned in SSRS fields

I am tasked with doing a SQL coalesce on DEA and NPI numbers in order to return the available value, there will be no cases where both DEA and NPI values are available for a Dr. This is what I am currently using in my script:

coalesce(nullif(convert(varchar,NationalProviderIdentifier),'0'), DEANumber) as 'DrID'

Unknown values can be defined as having the following values: "Unknown" 0 0000000000 "blank"

I will need to provide a hover over the Dr. id value that will either say "DEA Number" or "NPI Number" depending on what is being displayed. I have written the following VB code for the ToolTip expression and it works fine:

=IIF(Fields!DrID.Value = "Unknown" or Fields!DrID.Value = "" or Fields!DrID.Value = "0000000000", "DEA/NPI Unavailable", 
IIF(Len(Fields!DrID.Value) > 9, "NPI", "DEA"))

There will be cases when both DEA and NPI number are unknown, which is what I am checking for in the first IIF section of the VB. The second IFF statement is determining whether the value being returned is an NPI (10 digits long) or DEA (9 digits long) number.

I was advised to make my VB less complex by using a SQL function. I have not written a whole lot of functions and I am not not sure how to proceed. I assume that I will not reference the function in the Visual Studio but somewhere in the main dataset that retrieves the DrID data field?

Upvotes: 1

Views: 93

Answers (1)

RBarryYoung
RBarryYoung

Reputation: 56725

Well the simple way would be to add an additional column to your SQL query, like so (assumes that NationalProviderIdentifier is an INT and that neither allows NULLs):

CASE WHEN NationalProviderIdentifier <> 0 THEN 'NPI'
     WHEN DEANumber IN('Unknown','','0000000000') THEN 'N/A'
     ELSE 'DEA' END  as 'DrIdSrc'

Upvotes: 2

Related Questions