Need most recent tests from test set

[Former Member]

Hello,

I'm new to Crystal Reports 2008 and I'm still feeling my way around. To date, I've written SQL to get my data, and I'm getting used to where and how to do everything in CR.  Here's my issue:

I'm using CR to get data from a database behind HP Quality Center. I was able to create the report that I needed in QC, but the higher powers have decided that we need to use CR to report. In my scenario I have a test sets. Those test sets contain multiple tests. I want to be able to report on the most recent run of each test for a particular test set. I was able to do this writing the SQL in Quality Center, but finding a way to do that in CR is eluding me. I was able to add a parameter to select the test set that I want, but getting the most recent run for each test is what is the trouble spot.

For example if I had the following data

Test Set	Test	Run ID	Run Status	Date
TS1	Test 1	1	Fail	7/2/14
TS1	Test 2	2	Pass	7/2/14
TS1	Test 1	3	Pass	7/2/14
TS2	Test 1	4	Pass	7/3/14
TS2	Test 2	5	Pass	7/3/14
TS2	Test 3	6	Fail	7/3/14

In the above example, the Run ID is a unique database value and is the primary key of the run table. It is ascending, so you know that the higher the number is the more recent the run.

In my results if I selected TS1 in my parameter I would expect to see rows 2 and 3. If I selected TS2 as the parameter, I would expect that rows 4,5 and 6 are returned.

I hope my scenario was clear enough for everyone. I just need to find a way to return the most recent run of a test using CR. If it helps, I also attached my SQL that I wrote in QC to this. In there you'll see in my join that there is a subquery when joining the run table that produces the result that I need. You can ignore all the commented lines. I use this report for multiple projects and different people like to see different columns.

I did figure out how to use the Add Command function to just insert my SQL. I'm just wondering how you might do this in CR if I didn't have the SQL written.

Thank you in advance for your help

sample_test_run_sql.txt.zip

[abhilash_kumar]

Hi Brian,

I would not look at another way to do this in CR *if I had the SQL Query you attached*!

If not for the SQL Query, you can do something like this whilst being 'almost as efficient' as the SQL Query:

1) Add a SQL Expression Field to return the latest RUN_ID for each 'Test'. E.g:

(

     Select Max("Run_ID") from Table

     where "TEST" = "Table.TEST"

)

2) Add a Record Selection formula that returns data for the selected TS1 and for the latest RUN. E.g:

{Test Set} = {?Test Set Prompt}

AND

{Run ID} = {%SQL Expression Field}

-Abhilash

[Former Member]

Thanks, Abhilash. I'll give that a try.

I was looking for a way to do it in CR to see if when I had to create this report for another project it might be easier. Quality Center assigns a unique schema name to each project. Entering the SQL via Add Command, I'd just have to update all the schema names and create a new report each time. If I select the tables in CR I think I'd be able to change the data source and do a 'save as' for a new report on a new project (schema).

I appreciate the help for a CR newbie.

Brian