Search code examples
reporting-servicesstring-comparisoncomparison-operators

SSRS and Comparison Operators on Numeric Portion of varchar

Each returned transaction I am to report on is stored with a return reason code and a description of the return reason code. I built a tablix with two columns - one for return codes and another for descriptions. This works just peachy. The report owner is upset that a long list of codes will split pages - sigh. I was told to display them side-by-side.

I am new to t-sql and SSRS and its idiosyncrasies. I have minimal support from our DBAs. Two tables, filtered to display codes that meet a criteria sound simple enough.

My research:

MSDN's support network, Operators in Expressions page, and various help topics. I also found SO posts regarding split functions in t-sql and similar as well as one specifically asking about comparison and varchar. I found sites with helpful information like ResultData and Network Steve. I haven't found what I think I'm looking for.

My problem:

The return reason code is a varchar that always consists of the letter 'r' and two numeric digits (R00 to R99). It appears I can't run a comparison operator on an entire varchar that is alphanumeric; it doesn't recognize IIF((Fields!... <= R17),True,False). Additionally, the company will not allow the warehouse or its functions to be edited so I cannot create my own.

My solution ideas:

  • Add each Rnn code to the tablix filter, individually. This means ~50 filters per tablix and seems a sloppy or inefficient way of handling this
  • Separate the varchar string in to its alpha and numeric components and compare the latter using standard operators. This sounds the cleanest method but I'm unsure how to accomplish this in an expression or within SSRS
  • Forgo the two-table idea and create one table with four columns (code, description, code, description). This still leaves me with how to set a limit on the number of rows that can be created before 'spilling over' to the other side

I appreciate being pointed to any resources or any offered input to the issue and my (not so?)logical approach to it.

Solution

  • You can achieve your second option as follows:

    CInt(Fields!ReturnCode.Value.Substring(1,2))