HPQC: Showing defects with no links to Test Set

Here is a trick to see the defects which are not linked to a run (more precisely to a test set) :

1. Go to the Defects module

2. Define a filter with a cross filter on the test sets where the test set open date is <= [To-Day].

3. Click OK : you should see only the defects that are linked to a test set (either directly or indirectly, through a test instance or a run) ; all the listed defects should display a red paper-clip in the second column of the defects grid.

4. Click on define a filter again.

5. Copy the filter to the clipboard (second icon at the top of the dialog box).

6. Open a new document in any text editor (Notepad is OK).

7. Edit / Paste the filter definition. You should see a line with the following text :

EXISTS_IN_IDS:Y,

8. Replace Y with N.

9. Select the whole text and Edit / Copy.

10. Go back to the Define Filter dialog box and paste the new filter definition (third icon at the top of the dialog box). There will be no change in the way the filter definition is displayed in that dialog box, but…

11. Click OK to apply the new filter : the defects grid will show all the defects that are NOT linked to any test set (either directly or indirectly..). None of the displayed defect should have a red paper-clip (some of them may display it : it means they are linked to a test – in the Test Plan module – or to a requirement).

This trick is applicable to all cross-filter definitions.

Solution to my question, by Christian Grzelka, SQAForums.

HPQC Excel Report: Defects reopened

This Excel Report list defects with Status changed to Reopen.

Useful to see how many defects was not fixed properly and had to be reopened.

SELECT

BUG.BG_BUG_ID as “Defect Id”,

BG_SUMMARY as “Defect Summary”,

AP_OLD_VALUE as “Old value”, — Status prior to Reopen

AP_NEW_VALUE as “New value”, — Reopen

au_time as “Time changed”, — When it was reopened

au_user as “Changed by” — Who reopened it

 

FROM audit_log, audit_properties, bug

WHERE AUDIT_PROPERTIES.AP_NEW_VALUE = ‘Reopen’

and AU_Action_ID = AP_Action_ID

AND AU_ENTITY_ID = BG_BUG_ID

AND AU_ENTITY_TYPE = ‘BUG’

AND AP_FIELD_NAME = ‘BG_STATUS’

order by bg_bug_id 

HPQC Excel Report: Defect LifeCycle TimeSpan

The following code can be used to see how long it takes from a defect is submitted to defect closure.

Our defects goes from Submitted (via Assigned, Open, Fixed, Verified) to Closed.

 

This simple query is all it takes to see how long it took from detection to closure: 

SELECT

BUG.BG_BUG_ID as “Defect Id”,

left(BG_SUMMARY, 6) as “Screen”, — We add screen unique ID as first 6 bytes of defect.

BG_PRIORITY as “Priority”,

BG_SEVERITY as “Severity”,

datediff(“d”, BG_CLOSING_DATE, AU_TIME) as “Fix Time”, — Calculate LifeSpan

AU_TIME as “Submitted”, — When defect was submitted

BG_CLOSING_DATE as “Closing Time” — When defect was closed (date only unfortunatly)

FROM audit_log, audit_properties, bug

WHERE

AP_NEW_VALUE = ‘Submitted’ — (initial status; change to “New” or appropriate) 

and AU_Action_ID = AP_Action_ID — table connect

AND AU_ENTITY_ID = BG_BUG_ID — table connect

AND AU_ENTITY_TYPE = ‘BUG’

AND AP_FIELD_NAME = ‘BG_STATUS’

AND BG_STATUS = ‘Closed’ — can only see entire LifeSpan for closed defects

order by “Priority”, “Severity”, “Fix Time” — Want to see how long it took for high priority defects first.

 

In addition, Post-processing can be done like this to format the output in the Excel Spreadsheet:

Sub QC_PostProcessing()

Dim MainWorksheet As Worksheet

‘ Make sure your worksheet name matches!

Set MainWorksheet = ActiveWorkbook.Worksheets(“Sheet1″)

Dim DataRange As Range

Set DataRange = MainWorksheet.UsedRange

 

    ‘ Set header row to bold

    Rows(“1:1″).Select

    Selection.Font.Bold = True

    ‘ Set colum “Fix Time” to bold

    Selection.Find(What:=”Fix Time”, After:=ActiveCell, LookIn:=xlFormulas, _

        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

        MatchCase:=False, SearchFormat:=False).Activate

    activecell.entireColumn.select

    Selection.Font.Bold = True

 

    Cells.Select

    Cells.EntireColumn.AutoFit

    ‘ Borders

    DataRange.Select

    Selection.Borders(xlDiagonalDown).LineStyle = xlNone

    Selection.Borders(xlDiagonalUp).LineStyle = xlNone

    With Selection.Borders(xlEdgeLeft)

        .LineStyle = xlContinuous

        .Weight = xlThin

        .ColorIndex = xlAutomatic

    End With

    With Selection.Borders(xlEdgeTop)

        .LineStyle = xlContinuous

        .Weight = xlThin

        .ColorIndex = xlAutomatic

    End With

    With Selection.Borders(xlEdgeBottom)

        .LineStyle = xlContinuous

        .Weight = xlThin

        .ColorIndex = xlAutomatic

    End With

    With Selection.Borders(xlEdgeRight)

        .LineStyle = xlContinuous

        .Weight = xlThin

        .ColorIndex = xlAutomatic

    End With

    With Selection.Borders(xlInsideVertical)

        .LineStyle = xlContinuous

        .Weight = xlThin

        .ColorIndex = xlAutomatic

    End With

    With Selection.Borders(xlInsideHorizontal)

        .LineStyle = xlContinuous

        .Weight = xlThin

        .ColorIndex = xlAutomatic

    End With

    DataRange.Select

End Sub 

 

 

This gives output like this: 

Defect Id	Screen	Priority	Severity	Fix Time	Submitted	Closed
54	abc123	1 – High	1. Critical defect	-21	21.12.2010 16:01	11.01.2011 00:00
90	abc321	1 – High	1. Critical defect	-8	04.01.2011 10:19	12.01.2011 00:00
91	abc213	1 – High	1. Critical defect	-8	04.01.2011 11:48	12.01.2011 00:00

EverNote – remember everything

As a consultant moving from project to project, it’s sometimes hard to keep track of all tools, document templates and experience gained over the years. PCs are renewed, email addresses change, USB-drives gets corrupted and notebooks disappears… Thus, when you’re stuck on a problem you know you’ve solved before, it can be very frustrating when trying to recollect the required tools/documents you once wrote down to solve it.

Behold; Evernote.

  

Evernote is not a new app. It’s been on the market for quite some time, and I’m finding it ever more usefull. I’m using it to keep a home inventory (with images of receipts attached to photos of items in my home), I’m storing receipes and various stuff, and most important (for this blog’s angle): I’ve created a category labeled “Test”, in which I store documents, templates, link to tools (or a copy of them), etc etc etc.

By using Evernote like this, I can access what I need from any computer around the world. Evernote has a desktop version, but is fully web-functional from a computer or i.e. an iPhone app.

I can store anything I want there, including notes from my Project notebook (hand written); just take a snapshot of it with your cellphone, add a tag (and description and more if you want), and it’s in Evernote in seconds. You can also store entire webpages, or just the selected text, using a bookmarklet in your browser. You can also take snapshots of your iPhone screen:

You can also make notes public, which means you can share your valuable files with others who might need them.

This has been usefull to my several times, and the more I use, the more I understand the advantages of it. Now I’m completing my collection of manuals for my hi-fi, I’m creating a base of code snippets, and I’m keeping all details for the upcoming summer holiday there as well (tickets, car rental details, receipts from inflight orders etc).

Smplst

Simplicity is the highest form of sophistication
   – Leonardo DaVinchi

Make things as simple as possible, but not simpler.
   – Albert Einstein

Beauty lies in the abscence of the superfluous.
   – unknown

Receive with simplicity everything that happens to you.
   – Rashi

Everything can and should be improved.
Not a single day should go by without some kind of improvement being made.
Don’t just criticize, suggest an improvement.
   – Kaizen

Automagisk risikoklassifisering i Excel

Innledning
Dersom man har mange krav som skal risikoklassifiseres basert på ”Chance of failure” og ”Damage in case of failure”, kan det være tidkrevende å finne den endelige risikoen ved å gjøre manuelle oppslag i tabell. 
Jeg har laget et verktøy for oppslag i risikotabell i Excel, som gjør at man kan skrive inn ”Chance of failure” og ”Damage in case of failure”, og automagisk få den absolutte risikoklassifiseringen ut fra dette.

Oppslagstabell
Lag først en oppslagstabell som inneholder risikotabellen ”Absolute Risk Classification” (TMap Next s. 479). Tabellen har i dette eksempelet verdiene ”1 – High”, ”2 – Medium” og ”3 – Low” som absolutte risikoklasser, ettersom det er disse som brukes i prosjektet jeg er på nå. Dette kan selvsagt enkelt endres til ”A”, ”B”, ”C” eller hva man måtte ønske. 

Kolonne A inneholder klassene for ”Chance of failure”.
Rad 2 inneholder klassene for ”Damage in case of failure”.  

Markér A2:D5, og kall området ”Tabell”; denne kan også refereres til fra andre ark/faner i regnearket:

Kravspesifikasjon 
Selve kravspesifikasjonen inneholder følgende felter: 
A: Navn på kravet 
B: Beskrivelse 
C: Chance of failure: må tilsvare en av verdiene i kolonne A i ”Tabell” (High/Medium/Low).
D: Damage i.c.o failure: må tilsvare en av verdiene i rad 2 i ”Tabell” (High/Medium/Low). 
E: Kalkulert risikoklasse (hentes fra oppslagstabell).

 

Formel 
Formelen i celle E12 (og nedover) sjekker verdien i C12 og D12, og gjør et oppslag i ”Tabell” for å hente riktig verdi i forhold til klassifikasjonsmetoden. Kravspesifikasjonen over viser alle varianter, og ”Risk Class” gitt utfra spesifikasjon i ”Tabell”.  

Formel på norsk:
=FORSKYVNING(Tabell;SAMMENLIGNE($C12;FORSKYVNING(Tabell;0;0;RADER(Tabell);1);0)-1;SAMMENLIGNE($D12;FORSKYVNING(Tabell;0;0;1;KOLONNER(Tabell));0)-1) 

Formel på engelsk:
=OFFSET(Tabell;MATCH($C12;OFFSET(Tabell;0;0;ROWS(Tabell);1);0)-1;
MATCH($D12;OFFSET(Table;0;0;1;COLUMNS(Tabell));0)-1) 

OBS: Etter å ha skrevet inn eller editert formelen, er det viktig å trykke CTRL+SHIFT+ENTER for å gå ut av cellen (ikke bare TAB eller ENTER). Dette for å fortelle Excel at formelen inkluderer et område/array.

Risikoklassifisering i Excel.doc

HPQC Excel Report 3: Requirement Direct Cover Status with Test Instance Status

– Report shows Direct Cover Status for all requirements (or filtered), including Test Instance Status from Test Lab.
SELECT REQ.RQ_REQ_ID AS “Req ID”,
REQ.RQ_REQ_STATUS AS “Direct Cover Status”,
REQ.RQ_REQ_NAME AS “Requirement”,
TEST.TS_NAME AS “Test Case”,
CYCL_FOLD.CF_ITEM_NAME AS “Test Set Folder”,
CYCLE.CY_CYCLE AS “Test Set”,
TESTCYCL.TC_STATUS AS “Test Instance Status”
FROM REQ, REQ_COVER, TEST, TESTCYCL, CYCLE, CYCL_FOLD
WHERE REQ.RQ_REQ_ID = REQ_COVER.RC_REQ_ID
AND TEST.TS_TEST_ID = REQ_COVER.RC_ENTITY_ID
AND TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
AND TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
AND CYCLE.CY_FOLDER_ID = CYCL_FOLD.CF_ITEM_ID
AND TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
– AND RQ_REQ_PATH LIKE ‘??????%’
ORDER BY REQ.RQ_REQ_ID

HPQC Excel Report 2: Number of tests covering requirements

– Report showing how many tests in Test Plan are connected to the Requirement.
SELECT REQ.RQ_REQ_ID,
REQ.RQ_REQ_NAME,
COUNT (REQ_COVER.RC_ITEM_ID) AS “Covered by number of tests”
FROM REQ, REQ_COVER
WHERE REQ.RQ_REQ_ID = REQ_COVER.RC_REQ_ID
– AND RQ_REQ_PATH LIKE ‘??????%’
GROUP BY REQ.RQ_REQ_ID, REQ.RQ_REQ_NAME

HPQC Excel Report 1: Requirements and Folders Paths

– This Report is a tool for finding Path to requirements. This is needed for some Reports as a filter.
SELECT REQ.RQ_REQ_ID AS “Requirement ID”,
REQ.RQ_REQ_NAME AS “Requirement Name”,
REQ.RQ_REQ_PATH AS “Requirement Path”
FROM REQ
– WHERE RQ_REQ_PATH LIKE ‘??????%’
ORDER BY RQ_REQ_PATH, RQ_REQ_NAME

Excel spreadsheet in Word

http://www.lockergnome.com/it/2005/10/07/importing-excel-data-into-word/

If you want to be able to edit that data within Excel and have those changes updated automatically in Word, you can do so by creating a dynamic link.

1. Open your Word document.
2. Open your Excel spreadsheet.
3. With Excel, select the cells you want to copy into Word.
4. From the Edit menu, click Copy.
5. Switch back to your Word document.
6. Place the insertion point where you want the data placed.
7. From the Edit menu, click Paste Special.
8. Select Microsoft Excel Worksheet Object.
9. Select the Paste Link radio button.
10. Click OK.

Now when you double click the table in Word, the worksheet will open in Excel.

- – -

If you want to include the contents of a cell within your text, and have it update automaticly if it changes in the spreadsheet, you can do this too:

Follow the same instructions as above, but simply copy the cell containing the value you want to include in the text.

Then, in Word:
Edit -> Paste Special -> Unformatted Text (make sure to select the “paste link” option). Now when you have both the Word and Excel documents open, F9 will update the text in your Word document as you change the numbers in your Excel.