Query/report with several records on one line.

Brian Thor
Brian Thor used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.

 James
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
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.

mlmcc
Brian ThorPresident

Author

Commented:
All,

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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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.

 James
Brian ThorPresident

Author

Commented:
James,

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.

Thanks.

Brian
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.

 James

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial