Create a bug progress report from a Sharepoint 2003 bug list

Sharepoint 2003 includes a list template for bug tracking. It provides very basic functionality like keeping bug statuses, description, comments, dates, assignments etc. For every update that is made to a bug report, Sharepoint just adds a new item to the list. It doesn't however include a bug progress report.

In order to create such a report, I will create a new view on the bug report list in Sharepoint in order to expose the necessary information. In MS Access I will created a linked table that allows us to perform queries on the data to put it in the correct format for use in a pivot table. This pivot table and pivot chart will contain the bug progress report.

Sharepoint bug reports: progress details

On the sharepoint bug list, create a new view containing the following fields:

• Id
• Issue Id
• Status
• Modified
• Current

This will give you the entire history for every Issue but from this information you can't yet determine when a bug changed from one status to another.

MS Access linked tables

In order to create a bug progress report, we need to build a list of status changes for each of the issues. I have created four queries for the different situations that can occur.

The "StatusSequenceHistory" query determines the sequence of Ids of bug reports for each Issue Id. For each change it collects what Id was changed (FromId) and which new Id was created (ToId)

SELECT fr.[Issue Id] AS IssueId,
       fr.Id AS FromId,
       fr.Status AS FromStatus,
       min(to.Id) AS ToId
FROM [Bug Reports: Progress Details] AS fr
LEFT JOIN [Bug Reports: Progress Details] AS [to]
ON fr.[Issue Id]=to.[Issue Id]
WHERE fr.Id < to.Id
  AND to.Current=False
  AND fr.Current=False
GROUP BY fr.[Issue Id], fr.Id, fr.Status
ORDER BY fr.[Issue Id], fr.id;

Not all status will have a ToId and a FromId. The "StatusSequenceNew" query selects bug reports that have just been created but do not have a history yet.

SELECT [Issue Id] AS IssueId, min(Id) AS ToId
FROM [Bug Reports: Progress Details]
GROUP BY [Issue Id]
HAVING Count(Id) = 1;

The "StatusSequenceFirst" query selects the very first bug report that was created.

SELECT [Issue Id] AS IssueId, min(Id) AS ToId
FROM [Bug Reports: Progress Details]
WHERE Current=False
GROUP BY [Issue Id];

The "StatusSequenceLast" query selects the final bug report that was created which in fact is the current version of the bug report

SELECT fr.[Issue Id] AS IssueId,
       max(fr.Id) AS FromId,
       to.Status AS ToStatus,
       to.Id AS ToId
FROM [Bug Reports: Progress Details] AS fr
LEFT JOIN [Bug Reports: Progress Details] AS [to]
ON fr.[Issue Id]=to.[Issue Id]
WHERE fr.Id > to.Id
AND to.Current=True
AND fr.Current=False
GROUP BY fr.[Issue Id], to.id, to.Status
ORDER BY fr.[Issue Id], to.id;

The StatusSequence query

This query joins the complete history together and adds the status for each change

(
SELECT IssueId,FromId, FromStatus, ToId, det.Status as ToStatus
FROM StatusSequenceHistory old
LEFT JOIN [Bug Reports: Progress Details] det
ON old.ToId = det.Id
)
UNION (
SELECT IssueId,FromId, det.Status as FromStatus, ToId, ToStatus
FROM StatusSequenceLast cur
LEFT JOIN [Bug Reports: Progress Details] det
ON cur.FromId = det.Id
)
UNION (
SELECT IssueId, NULL as FromId, NULL as FromStatus, ToId, det.Status as ToStatus
FROM StatusSequenceFirst fir
LEFT JOIN [Bug Reports: Progress Details] det
ON fir.ToId =det.Id
)
UNION (
SELECT IssueId, NULL as FromId, NULL as FromStatus, ToId, det.Status as ToStatus
FROM StatusSequenceNew new
LEFT JOIN [Bug Reports: Progress Details] det
ON new.ToId =det.Id
);

The ProgressReport query

This query adds the number of status changes for each change and for each day

(
SELECT
Int(d.Modified) AS ModifiedDate, s.FromStatus AS Status, -1 * COUNT(s.FromStatus) as Diff
FROM StatusSequence s
RIGHT JOIN [Bug Reports: Progress Details] d
ON s.ToId = d.Id
WHERE s.FromStatus <> ''
GROUP BY Int(d.Modified), s.FromStatus
)
UNION (
SELECT
Int(d.Modified) AS ModifiedDate, s.ToStatus AS Status, 1 * COUNT(s.ToStatus) as Diff
FROM StatusSequence s
LEFT JOIN [Bug Reports: Progress Details] d
ON s.ToId = d.Id
GROUP BY Int(d.Modified), s.ToStatus
);

Microsoft Excel: Bug progress report

In Excel you can now create a link to the MS Access database as an external source. Choose the PivotTable/PivotChart option when connecting. Put the Modified Date in the left column of the PivotTable, the Status in the upper row and the Issue Id as in the data fields. Configure the Ids in order to show as a running total

Windows Activation: No records found for given DNS query.

I recently installed Windows 7 Enterprise Edition on one of my PC. Eager to get my PC up and running with all software I daily use, I skipped activation. When I tried to activate a few days later, I was greeted by this error message: Searching for this message, you might end up on this Microsoft support page telling you to check you DNS settings. In my case this didn't apply at all.

In the end it turns out that I ot this message because I hadn't entered my product key yet because I skipped activation during setup. To solve the issue, I:

• went to "Control Panel\System and Security\System"
• clicked "change product key"
• entered a valid product key

Windows automatically activated.

Injecting a version number into WiX via TeamBuild

I have found several ways of injecting a version number into Wix via TeamBuild, but most of them require way too much work. After a few days of experimenting I ended up with this solution. My TFSBuild.proj file contains the Major and Minor versions and a target "GenerateBuildNumber" to generate build and revision numbers. I use these to create a 'Versionnumber' property and add that to the <custompropertiesforbuild> property.

<propertygroup>
  <assemblymajorversion>2</assemblymajorversion>
  <assemblyminorversion>7</assemblyminorversion>
  <custompropertiesforbuild>
    SignAssembly=true;DelaySign=false;AssemblyOriginatorKeyFile=$(PrivateKeyLocation)
  </custompropertiesforbuild>
</propertygroup>

<target name="BuildNumberOverrideTarget" dependsontargets="GenerateBuildNumber">
  <createproperty value="$(AssemblyMajorVersion).$(AssemblyMinorVersion).$(GeneratedBuildNumber).$(GeneratedRevisionNumber)">
    <output taskparameter="Value" propertyname="Versionnumber" />
  </createproperty>

  <!-- Add the dynamically generated Versionnumber to the custom build properties for use in the WIX project -->
  <createproperty value="$(CustomPropertiesForBuild);Versionnumber=$(Versionnumber)">
    <output taskparameter="Value" propertyname="CustomPropertiesForBuild" />
  </createproperty>
</target>

For setting the version number in a WiX project, you need to get the value in the Product.wxs file. My predecessor had defined PRODUCTVERSION to contain a placeholder value "_versionnumber_". Note: The official way would have been to use the <defineconstants> in the WiX project file but in my case, other builds were already depending on the placeholder so I didn't want to change this.

<?define PRODUCTVERSION="_versionnumber_"?>

In my WiX project file I have overriden the "BeforeBuild" target to replace the "_versionnumber_" placeholder with the actual value of $(Versionnumber). I'm using the 'File.Replace' task from the SDC Tasks library.

<Target name="BeforeBuild">
     <microsoft.sdc.tasks.file.replace
       Path="Product.wxs"
       OldValue="%22_versionnumber_%22"
       NewValue="%22$(Versionnumber)%22"
       Force="true"
       IgnoreCase="true">
   </Target>

As a final step, I have overriden the "AfterBuild" target of the WiX project to rename the output msi in order to contain the versionnumber in the filename as well.

<Target name="AfterBuild">  
 <Createproperty value="$(TargetDir)%(CultureGroup.OutputFolder)">  
   <Output taskparameter="Value" propertyname="TargetDir">  
 </CreateProperty>  
 
 <Copy sourcefiles="$(TargetDir)$(TargetName)$(TargetExt)"  
       destinationfiles="$(TargetDir)$(TargetName).$(Versionnumber)$(TargetExt)">  
   
 <Delete files="$(TargetDir)$(TargetName)$(TargetExt)">  
</Target>