Skip to Content
author's profile photo Former Member
Former Member

Need most recent tests from test set


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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Posted on Jul 24, 2014 at 03:12 PM

    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}


    {Run ID} = {%SQL Expression Field}


    Add a comment
    10|10000 characters needed characters exceeded

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


Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.