Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Crystal Reports

Crystal Reports

Crystal Reports

My data is stored in Oracle and the only way I can run a report with it is using Crystal Reports. I have a set of data that looks like this ,,,,,,,,,,1, or ,1,,,,,,, or ,1,,,,,1,,,,1,. There are more variations. Each one means a value is true for a record. There are about 54 'ticks/commas' What I want is all records with the one at the X spot. So for one report I may want all records in the 10th spot that have a 1. There may be other times where I want the records where the 1 is after spot 36. I agree it will pull other records but the main once I want is the X spot.

How do I get this? I tried a Like command but that does not narrow the data down far enough. I am familiar with SQL but not Crystal. Any help would be great. TIA

RE: Crystal Reports

How many rows of data do you have? The reason I ask is that to have Crystal do this filter will mean that Crystal will pull all of the data into memory and filter it there instead of pushing the filter down to the database. This can significantly affect the speed of the report.

Here's how you would do it in Crystal:

1. Create a parameter that will accept a number indicating which position you want to check. I'll call this {?Position}

2. Use something like this formula in the Select Expert to filter the data:

Split({MyTable.MyField}, ",")[{?Position}] = "1"

If there's a lot of data to filter through, you could create a stored function in the database that would get the value at that position. You could then call the function in a SQL Expression and use it in the Select Expert or you could write a Command and use it in the Where clause of the command to filter your dat.


DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

RE: Crystal Reports

You need to replace the empty spaces with a character and then you can use like operator. I cannot check the formula but something like this
mid(replace(','+ YourData +',',',,',',0,'),1,len(YourData) - 2)

should transform YourData = ,,,1, to 0,0,0,1,0
then, you can search using like operator and string like this ?,?,?,1,?

www.R-Tag.com Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close