Crystal Reports

[hwolfgram]

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

 

[hilfy]

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.

-Dell

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

http://www.decisionfirst.com

 

[PeterDimitrov]

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,?

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.