Query/report with several records on one line.

I need to modify a Crystal report. Currently it shows a row for each entry in a table. I want to only show the first 5 rows, regardless of how many are in the table, as columns. Here is an example.

Current Result                              
Record      Inspection Number      Inspection Value                  
1      20039      0.3                  
2      20039      0.4                  
3      20039      0.5                  
4      20039      0.3                  
5      20039      0.3                  
Desired Result                              
Inspection Number      Inspection Value 1      Inspection Value 2      Inspection Value 3      Inspection Value 4      Inspection Value 5
20039                  0.3                  0.4                  0.5                  0.3                  0.3

I'm sorry that the columns don't line up but I'm sure you get the idea. How would I write this query and/or modify the report?

Thank you.
Brian ThorPresidentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Is that record number a column in your table?

 If it is, will those numbers always be consecutive, starting with 1 (1, 2, 3, 4, 5, 6, etc.), or could the numbers start with something besides 1, or skip some values?

 Assuming the simplest case, where the record number is a column in the table, and the first 5 records are always numbered 1 - 5, you could use a record selection formula like the following to only include the first 5 rows:

{record number field} <= 5

 For the report output, you could use a cross-tab, with Inspection Number for the rows, record number for the columns, and Inspection Value for the summarized field.

 If the record number is a column in the table, but the first 5 values are not always 1 - 5, then a cross-tab may still work.  The problem becomes limiting the data to the first 5 records.

Will you have a sinle inspection number or multiple.

If the cross tab idea doesn't work I can think of ways to use variables and formulas with groups to produce the output you want.

Brian ThorPresidentAuthor Commented:

Thanks for your time. I'm sorry I am slow to respond but I have been away from this project until today.

A cross tab seems like a way to go but I am having difficulty with it. The cross tab examples I have found all deal with summarizing the data. In this case I simply want the first 5 values (still trying to figure out the best method to get the first 5) for a given measurement. In the example I am working with there are 32 attributes (measurements) I need to report for 5 samples (parts). So, my cross tab would be 32 rows of 5 columns of data (plus a other informational columns which apply to all rows). I know that there are 160 rows of data (32*5) in my data source.

I don't know if this will help you. I am stumped.
Monitor and Analyze Slow Network Performance

SolarWinds® Bandwidth Analyzer Pack, is designed to detect, diagnose, and resolve network performance issues, and monitor and test network throughput and traffic patterns from a single customizable console.

The cross tab examples I have found all deal with summarizing the data.
If you only have 1 record for each sample (part) and attribute, then the summaries in the cross-tab will just be the values from each record.

 The main problem would seem to be limiting the data to the first 5 records in each set.  If there is no good way to do that, then a cross-tab may not work.

 Are those records numbered?  If so, what do those numbers look like?  I'm guessing that it's not simply 1, 2, 3, etc., because then you probably wouldn't be having any problem figuring out how to get the first 5 records.

Brian ThorPresidentAuthor Commented:

You are correct about not finding a simple 1,2,3... There is what appears to be a phony date stamp that is getting used as the sort.

I forgot to add that these values are not numeric but text string and they can't be converted because some values will be numeric and other will be text, like pass/fail.


I forgot to add that these values are not numeric but text string and they can't be converted because some values will be numeric and other will be text, like pass/fail.
You mean the values that you're "summarizing"?  That shouldn't be a problem.  There is an option in the Cross-tab Expert to tell it not to summarize the value.  If that option is not available (I seem to recall that it's not in some cases), you can set the "summary" to be Minimum or Maximum.  That will work on string fields.

 As for limiting the data to the first 5 values, if you can't use some simple test, like Record_number <= 5, then the best solution would probably be to create your own db query (where you would have more control/options), assuming that you're not already using a query like that.  You could create a query in the db (eg. a view or stored procedure), or use a CR Command (where you write the query, but you create it in CR and it's stored in the report).  Then you could hopefully limit the results in the query.

 If you're going to try creating a query, you could get a start on it by going to Database > "Show SQL Query" and copying the query that CR created for the report.  If that query has the data that you need for the report, you would theoretically just need to modify it to limit the results to the first 5 in each set.

 If you can't create a query that limits the data, you may have to use suppression in the report (to suppress everything after the first 5 values), in which case a cross-tab probably won't work and you'll have to create the columns manually.  That should be possible, but a cross-tab would be so much simpler.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.