Relational Queries

[nborrerojr.]

Is there any way to do a relational query with ILM data other than using a report recipe? If not, is there by any chance an add-in for Access or something similar that will treat item types as tables to allow relational queries to be created?

[VolkerEckardt]

Hello Nolin,

could you please tell us your Use Case that we can better understand what you are trying to achieve? Further more, could you give us an example?

[nborrerojr.]

It makes more sense for our specific configuration, but to use an example with the default ALM config, it would be something like this:

For each Requirement within a Requirement Document, I'd want to see every Test Suite that is related to a Requirement based on a trace to a Test Case.

The problem that I'm going to run into is that the Test Suite will be returned multiple times if there are multiple Test Cases within it traced to a single Requirement.

So what I'm looking for would be just a standard SQL query I could run where I can use things like Select Distinct and also just have the simplicity of a query builder. What I think would be great would be an add-in for access that would use the API to return queries and would treat each item type as a separate table.

[VolkerEckardt]

Hi Nolin,

last question before I will try to answer:

How should the output look exactly?

And, should it be inside Integrity or a report (I am not clear about this)

When you have it, what do you do with this then (Business Value)?

[nborrerojr.]

he reports that I have found to be the most valuable have been the XML reports because the can be used with Excel. The three issues with them though are that:

1. They're too fragile. If you're using a plain text field then they can break if someone uses a &, <, or > in the field. I think there are other things that can break them also. Since they're XML, you can't use JavaScript to clean it up. Also, I don't like the idea of forcing users to not use these characters in the field or storing a "cleaned" version anywhere else.

2. Regular users won't be able to make recipes on the fly. I'm pretty good with them and it still takes me at least two hours to create a recipe that I need.

3. There is no way to remove duplicate records.

The HTML reports are really good for things that are delivered but they don't allow people to work with the data. This is why I've been using the XML reports instead and just informing people of the limitations. Usually, they just have to make sure not to use an apostrophe in document names.

Ideally, we would be able to do this inside of Integrity as well as creating reports. This is really for us being able to analyze data and to provide data to other people that aren't using Integrity.

[VolkerEckardt]

Thanks, and how should the output look exactly?

ReqID    Text    TraceToTestDocIDs

100       Some Req Text   5003,6005

This you need?

[nborrerojr.]

In this case, I would want to see the Document Short Title instead of the IDs.

So, I know how to do this with the report recipe. This is just an example of the type of relational queries I'd want to be able to make. To take this a step further, I may want to do something like show the Defects associated with Test Cases or something like that.

[Shilpa_Dubey]

Hi Volker,

Yes this is what exactly I'm looking for.

Req ID | Text or Title of req ID | Test ID | Title of Test

Thanks and Regards,

Shilpa