Parse strange field in Crystal Reports

Question

I'm using Crystal Reports Basic for Visual Studio 2008 to generate a report from a database. In the database there is a "structured data" field called data. It is a way of representing a Hashtable and the format is:

XLLLKeyYMMMValue
where:
X is the length of the length of the Key
L is the length of the Key
Y is the length of the length of the Value
M is the length of the Value

so if I were to encode

Name = John Robert Oxley
Age Of Poor Little Developer = 27

I would have.

Name (L = 4 therefore X = 1)
John Robert Oxley (M = 17 therefore Y = 2)
Age Of Poor Little Developer (L = 28 therefore X = 2)
27 (M = 2 therefore Y = 1)

so the field for this record would contain

14Name223John Robert Oxley228Age Of Poor Little Developer1227

There are a number of fields that I know I want in the report. They may not be in the data field in the table however. Now for my questions

• I'm guessing I have to create a custom function parseStructuredData(data, field) which returns the field I want or null if not present.
• Are there any resources on the net for custom functions or a book that I should buy for Crystal Reports (preferably ebook) as I have very little experience?
• Then do I use a "Formula Field" to put it on the report?

Update This is running on MS SQL server but I would prefer not to use a stored procedure to parse the data as I don't want to modify the database.

Answer 1

I see in your edit that you do not want to modify the database, but getting the output you are looking for is going to be very difficult if not impossible without a stored procedure to transform it into more of a tabular view. Even if you were able to do it in Crystal I'd imagine that you'd have to use looping and string manipulation which performing these calculations on each row would be very slow and processor intensive.

I'd recomend that you use T-SQL or temp tables in a Stored Procedure to format the data instead of trying to do it in Crystal. Granted using a cursor in SQL Server can be slower I believe it would be much faster than in Crystal.

EDIT: I missed the part where you said that you were doing this using VS2008. If you do not want to do this in an SP because you do not want to change the database then you can create a function instead to do this which passes an ArrayList or DataTable as the reports datasource.

I'll try to reinvestigate this in a bit and edit my answer to provide how I might go about doing this in a stored procedure or a function.

Answer 2

Here is my function. It is uglier than sin and i feel ashamed of posting this on the internet. But it works. Haven't ever used VB properly before so here goes:

Function ParseStructData (struct As String, key As String) As String
 Dim leng As Number
 Dim ll As Number
 Dim data As String
 Dim rest As String
 Dim kk As String

 rest = struct

 Do
  ll = Val(Left(rest, 1))
  If ll < 1 Then
   Exit Function
  End If
  rest = Right(rest, Len(rest) - 1)
  leng = Val(Left(rest, ll))
  rest = Right(rest, Len(rest)-ll)
  kk = Left(rest, leng)
  rest = Right(rest, Len(rest)-leng)
  ll = Val(Left(rest, 1))
  rest = Right(rest, Len(rest)-1)
  leng = Val(Left(rest, ll))
  rest = Right(rest, Len(rest)-ll)

  data = Left(rest, leng)
  If kk = key Then
   ParseStructData = data
   Exit Function
  End If
  rest = Right(rest, Len(rest)-leng)
 Loop Until Len(rest) <= 0

End Function

If anyone can come up with a better function, please put in an answer.