Report Builder 2.0

• kdnicholsster

  Ten Centuries

  Points: 1152

  More actions

  December 15, 2011 at 4:14 pm

  #252158

  Hello All,

  I have the following code in Report Builder 2.0:

  DECLARE @ReleaseRegressionMediumBugCount INT;

  DECLARE @PilotRegressionSevHighBugCount INT;

  DECLARE @PilotRegressionSevMedBugCount INT;

  DECLARE @PilotRegressionSevLowBugCount INT;

  DECLARE @PilotRegressionCriticalBugCount INT;

  DECLARE @PilotCriticalSevBugCount INT;

  DECLARE @PilotHighSevBugCount INT;

  DECLARE @PilotMediumSevBugCount INT;

  DECLARE@ReleaseCriteriaCriticalTechnicalDemonstratorBugCount INT;

  DECLARE @ReleaseCriticalPilotBugCount INT;

  DECLARE@ReleaseCriteriaHighReleaseBugCount INT;

  DECLARE @ReleaseCriteriaHighTechnicalDemonstratorBugCount INT;

  DECLARE @ReleaseCriteriaMediumPilotBugCount INT;

  DECLARE @ReleaseCriteriaHighPilotBugCount INT;

  DECLARE @ReleaseCriteriaMediumHighBugCount INT;

  DECLARE @ReleaseCriteriaMediumReleaseBugCount INT;

  --===================REGRESSION=====================================

  SELECT @ReleaseRegressionMediumBugCount = COUNT(DWI.System_Id) FROM DimWorkItem DWI

  INNER JOIN DimIteration DI ON DI.IterationSK = DWI.IterationSK

  WHERE DI.IterationPath LIKE '\GeoSigns%'

  AND DI.IterationPath NOT LIKE '\GeoSigns\(_Bug Migration)%'

  AND DI.IterationPath NOT LIKE '\GeoSigns\(Bug Inbox)%'

  AND DWI.System_WorkItemType = 'Bug'

  AND DWI.System_State <> 'Done'

  AND DWI.System_State <> 'Removed'

  AND DWI.Microsoft_VSTS_Common_Severity = '3 - Medium'

  AND DWI.System_Title NOT LIKE '%Pilot:%'

  AND DWI.System_Title NOT LIKE '%TechnicalDemonstrator%'

  AND DWI.System_Title NOT LIKE '%NonProductionCode%'

  AND DWI.System_Rev = (SELECT MAX(System_Rev) FROM DimWorkItem DWI_1

  WHERE DWI_1.System_Id = DWI.System_Id AND DWI_1.System_WorkItemType = 'Bug' AND DWI_1.TeamProjectSK = 159);

  ----Release Regression High

  SELECT @PilotRegressionSevHighBugCount = COUNT(DWI.System_Id) FROM DimWorkItem DWI

  INNER JOIN DimIteration DI ON DI.IterationSK = DWI.IterationSK

  WHERE DI.IterationPath LIKE '\GeoSigns%'

  AND DI.IterationPath NOT LIKE '\GeoSigns\(_Bug Migration)%'

  AND DI.IterationPath NOT LIKE '\GeoSigns\(Bug Inbox)%'

  AND DWI.System_WorkItemType = 'Bug'

  AND DWI.System_State <> 'Done'

  AND DWI.System_State <> 'Removed'

  AND DWI.Microsoft_VSTS_Common_Severity = '2 - High'

  AND DWI.System_Title NOT LIKE '%Pilot:%'

  AND DWI.System_Title NOT LIKE '%TechnicalDemonstrator%'

  AND DWI.System_Title NOT LIKE '%NonProductionCode%'

  AND DWI.System_Rev = (SELECT MAX(System_Rev) FROM DimWorkItem DWI_1

  WHERE DWI_1.System_Id = DWI.System_Id AND DWI_1.System_WorkItemType = 'Bug' AND DWI_1.TeamProjectSK = 159);

  ----Release Regression Critcal

  SELECT @PilotRegressionCriticalBugCount = COUNT(DWI.System_Id) FROM DimWorkItem DWI

  INNER JOIN DimIteration DI ON DI.IterationSK = DWI.IterationSK

  WHERE DI.IterationPath LIKE '\GeoSigns%'

  AND DI.IterationPath NOT LIKE '\GeoSigns\(_Bug Migration)%'

  AND DI.IterationPath NOT LIKE '\GeoSigns\(Bug Inbox)%'

  AND DWI.System_WorkItemType = 'Bug'

  AND DWI.System_State <> 'Done'

  AND DWI.System_State <> 'Removed'

  AND DWI.Microsoft_VSTS_Common_Severity = '1 - Critical'

  AND DWI.System_Title NOT LIKE '%Pilot:%'

  AND DWI.System_Title NOT LIKE '%TechnicalDemonstrator%'

  AND DWI.System_Title NOT LIKE '%NonProductionCode%'

  AND DWI.System_Rev = (SELECT MAX(System_Rev) FROM DimWorkItem DWI_1

  WHERE DWI_1.System_Id = DWI.System_Id AND DWI_1.System_WorkItemType = 'Bug' AND DWI_1.TeamProjectSK = 159);

  --===========================CRITICAL===========================

  ----Release Criteria Critcal Pilot

  SELECT @ReleaseCriticalPilotBugCount = COUNT(DWI.System_Id) FROM DimWorkItem DWI

  INNER JOIN DimIteration DI ON DI.IterationSK = DWI.IterationSK

  WHERE DI.IterationPath LIKE '\GeoSigns%'

  AND DI.IterationPath NOT LIKE '\GeoSigns\(_Bug Migration)%'

  AND DI.IterationPath NOT LIKE '\GeoSigns\(Bug Inbox)%'

  AND DWI.System_WorkItemType = 'Bug'

  AND DWI.System_State <> 'Done'

  AND DWI.System_State <> 'Removed'

  AND DWI.Microsoft_VSTS_Common_Severity = '1 - Critical'

  AND DWI.System_Title LIKE '%Pilot:%'

  AND DWI.System_Rev = (SELECT MAX(System_Rev) FROM DimWorkItem DWI_1

  WHERE DWI_1.System_Id = DWI.System_Id AND DWI_1.System_WorkItemType = 'Bug' AND DWI_1.TeamProjectSK = 159)

  ----Release Criteria Critcal Pilot

  SELECT @ReleaseCriticalPilotBugCount = COUNT(DWI.System_Id) FROM DimWorkItem DWI

  INNER JOIN DimIteration DI ON DI.IterationSK = DWI.IterationSK

  WHERE DI.IterationPath LIKE '\GeoSigns%'

  AND DI.IterationPath NOT LIKE '\GeoSigns\(_Bug Migration)%'

  AND DI.IterationPath NOT LIKE '\GeoSigns\(Bug Inbox)%'

  AND DWI.System_WorkItemType = 'Bug'

  AND DWI.System_State <> 'Done'

  AND DWI.System_State <> 'Removed'

  AND DWI.Microsoft_VSTS_Common_Severity = '1 - Critical'

  AND DWI.System_Title NOT LIKE '%Pilot:%'

  AND DWI.System_Title NOT LIKE '%TechnicalDemonstrator%'

  AND DWI.System_Title NOT LIKE '%NonProductionCode%'

  AND DWI.System_Rev = (SELECT MAX(System_Rev) FROM DimWorkItem DWI_1

  WHERE DWI_1.System_Id = DWI.System_Id AND DWI_1.System_WorkItemType = 'Bug' AND DWI_1.TeamProjectSK = 159)

  ----Release Criteria Critcal Technical Demonstrator

  SELECT @ReleaseCriteriaCriticalTechnicalDemonstratorBugCount = COUNT(DWI.System_Id) FROM DimWorkItem DWI

  INNER JOIN DimIteration DI ON DI.IterationSK = DWI.IterationSK

  WHERE DI.IterationPath LIKE '\GeoSigns%'

  AND DI.IterationPath NOT LIKE '\GeoSigns\(_Bug Migration)%'

  AND DI.IterationPath NOT LIKE '\GeoSigns\(Bug Inbox)%'

  AND DWI.System_WorkItemType = 'Bug'

  AND DWI.System_State <> 'Done'

  AND DWI.System_State <> 'Removed'

  AND DWI.Microsoft_VSTS_Common_Severity = '1 - Critical'

  AND DWI.System_Title NOT LIKE '%TechnicalDemonstrator%'

  AND DWI.System_Rev = (SELECT MAX(System_Rev) FROM DimWorkItem DWI_1

  WHERE DWI_1.System_Id = DWI.System_Id AND DWI_1.System_WorkItemType = 'Bug' AND DWI_1.TeamProjectSK = 159)

  --=========================HIGH=============================

  ----Release Criteria High Pilot

  SELECT @ReleaseCriteriaHighPilotBugCount = COUNT(DWI.System_Id) FROM DimWorkItem DWI

  INNER JOIN DimIteration DI ON DI.IterationSK = DWI.IterationSK

  WHERE DI.IterationPath LIKE '\GeoSigns%'

  AND DI.IterationPath NOT LIKE '\GeoSigns\(_Bug Migration)%'

  AND DI.IterationPath NOT LIKE '\GeoSigns\(Bug Inbox)%'

  AND DWI.System_WorkItemType = 'Bug'

  AND DWI.System_State <> 'Done'

  AND DWI.System_State <> 'Removed'

  AND DWI.Microsoft_VSTS_Common_Severity = '2 - High'

  AND DWI.System_Title NOT LIKE '%Pilot%'

  AND DWI.System_Rev = (SELECT MAX(System_Rev) FROM DimWorkItem DWI_1

  WHERE DWI_1.System_Id = DWI.System_Id AND DWI_1.System_WorkItemType = 'Bug' AND DWI_1.TeamProjectSK = 159)

  ----Release Criteria High Release

  SELECT @ReleaseCriteriaHighReleaseBugCount = COUNT(DWI.System_Id) FROM DimWorkItem DWI

  INNER JOIN DimIteration DI ON DI.IterationSK = DWI.IterationSK

  WHERE DI.IterationPath LIKE '\GeoSigns%'

  AND DI.IterationPath NOT LIKE '\GeoSigns\(_Bug Migration)%'

  AND DI.IterationPath NOT LIKE '\GeoSigns\(Bug Inbox)%'

  AND DWI.System_WorkItemType = 'Bug'

  AND DWI.System_State <> 'Done'

  AND DWI.System_State <> 'Removed'

  AND DWI.Microsoft_VSTS_Common_Severity = '2 - High'

  AND DWI.System_Title NOT LIKE '%Pilot%'

  AND DWI.System_Title NOT LIKE '%TechnicalDemonstrator%'

  AND DWI.System_Title NOT LIKE '%NonProductionCode%'

  AND DWI.System_Rev = (SELECT MAX(System_Rev) FROM DimWorkItem DWI_1

  WHERE DWI_1.System_Id = DWI.System_Id AND DWI_1.System_WorkItemType = 'Bug' AND DWI_1.TeamProjectSK = 159)

  ----Release Criteria High TechnicalDemonstator

  SELECT @ReleaseCriteriaHighTechnicalDemonstratorBugCount = COUNT(DWI.System_Id) FROM DimWorkItem DWI

  INNER JOIN DimIteration DI ON DI.IterationSK = DWI.IterationSK

  WHERE DI.IterationPath LIKE '\GeoSigns%'

  AND DI.IterationPath NOT LIKE '\GeoSigns\(_Bug Migration)%'

  AND DI.IterationPath NOT LIKE '\GeoSigns\(Bug Inbox)%'

  AND DWI.System_WorkItemType = 'Bug'

  AND DWI.System_State <> 'Done'

  AND DWI.System_State <> 'Removed'

  AND DWI.Microsoft_VSTS_Common_Severity = '2 - High'

  AND DWI.System_Title LIKE '%TechnicalDemonstrator%'

  AND DWI.System_Rev = (SELECT MAX(System_Rev) FROM DimWorkItem DWI_1

  WHERE DWI_1.System_Id = DWI.System_Id AND DWI_1.System_WorkItemType = 'Bug' AND DWI_1.TeamProjectSK = 159)

  ------=======================MEDIUM========================

  ----Release Criteria Medium Pilot

  SELECT @ReleaseCriteriaMediumPilotBugCount = COUNT(DWI.System_Id) FROM DimWorkItem DWI

  INNER JOIN DimIteration DI ON DI.IterationSK = DWI.IterationSK

  WHERE DI.IterationPath LIKE '\GeoSigns%'

  AND DI.IterationPath NOT LIKE '\GeoSigns\(_Bug Migration)%'

  AND DWI.System_WorkItemType = 'Bug'

  AND DWI.System_State <> 'Done'

  AND DWI.System_State <> 'Removed'

  AND DWI.Microsoft_VSTS_Common_Severity = '3 - Medium'

  AND DWI.System_Title LIKE '%Pilot%'

  AND DWI.System_Rev = (SELECT MAX(System_Rev) FROM DimWorkItem DWI_1

  WHERE DWI_1.System_Id = DWI.System_Id AND DWI_1.System_WorkItemType = 'Bug' AND DWI_1.TeamProjectSK = 159)

  ----Release Criteria Medium Release

  SELECT @ReleaseCriteriaMediumReleaseBugCount = COUNT(DWI.System_Id) FROM DimWorkItem DWI

  INNER JOIN DimIteration DI ON DI.IterationSK = DWI.IterationSK

  WHERE DI.IterationPath LIKE '\GeoSigns%'

  AND DI.IterationPath NOT LIKE '\GeoSigns\(_Bug Migration)%'

  AND DI.IterationPath NOT LIKE '\GeoSigns\(_Bug Inbox)%'

  AND DWI.System_WorkItemType = 'Bug'

  AND DWI.System_State <> 'Done'

  AND DWI.System_State <> 'Removed'

  AND DWI.Microsoft_VSTS_Common_Severity = '3 - Medium'

  AND DWI.System_Title NOT LIKE '%Pilot%'

  AND DWI.System_Title NOT LIKE '%TechnicalDemonstrator%'

  AND DWI.System_Title NOT LIKE '%NonProductionCode%'

  AND DWI.System_Rev = (SELECT MAX(System_Rev) FROM DimWorkItem DWI_1

  WHERE DWI_1.System_Id = DWI.System_Id AND DWI_1.System_WorkItemType = 'Bug' AND DWI_1.TeamProjectSK = 159)

  ----Release Criteria Medium Pilot

  SELECT @ReleaseCriteriaMediumReleaseBugCount = COUNT(DWI.System_Id) FROM DimWorkItem DWI

  INNER JOIN DimIteration DI ON DI.IterationSK = DWI.IterationSK

  WHERE DI.IterationPath LIKE '\GeoSigns%'

  AND DI.IterationPath NOT LIKE '\GeoSigns\(_Bug Migration)%'

  AND DI.IterationPath NOT LIKE '\GeoSigns\(_Bug Inbox)%'

  AND DWI.System_WorkItemType = 'Bug'

  AND DWI.System_State <> 'Done'

  AND DWI.System_State <> 'Removed'

  AND DWI.Microsoft_VSTS_Common_Severity = '3 - Medium'

  AND DWI.System_Title LIKE '%Pilot%'

  AND DWI.System_Title NOT LIKE '%TechnicalDemonstrator%'

  AND DWI.System_Title NOT LIKE '%TechnicalDemonstrator%'

  AND DWI.System_Rev = (SELECT MAX(System_Rev) FROM DimWorkItem DWI_1

  WHERE DWI_1.System_Id = DWI.System_Id AND DWI_1.System_WorkItemType = 'Bug' AND DWI_1.TeamProjectSK = 159)

  SELECT @ReleaseRegressionMediumBugCount AS 'ReleaseRegressionMediumBugCount', @PilotRegressionSevHighBugCount AS 'PilotRegressionSevHighBugCount',

  @PilotRegressionSevMedBugCount AS 'PilotRegressionSevMedBugCount', @PilotRegressionSevLowBugCount AS 'PilotRegressionSevLowBugCount,',

  @PilotHighSevBugCount AS 'PilotHighSevBugCount', @PilotCriticalSevBugCount AS 'PilotCriticalSevBugCount', @PilotMediumSevBugCount AS 'PilotMediumSevBugCount',

  @ReleaseCriteriaCriticalTechnicalDemonstratorBugCount AS 'ReleaseCriteriaCriticalTechnicalDemonstratorBugCount',

  @ReleaseCriteriaHighTechnicalDemonstratorBugCount AS 'ReleaseCriteriaHighTechnicalDemonstratorBugCount',

  @ReleaseCriteriaHighPilotBugCount AS 'ReleaseCriteriaHighPilotBugCount',

  @ReleaseCriteriaMediumPilotBugCount AS 'ReleaseCriteriaMediumPilotBugCount',

  @ReleaseCriteriaMediumReleaseBugCount AS 'ReleaseCriteriaMediumReleaseBugCount';

  How do I get the date to appear on the X Axis of the chart do I have to Select the date for each one of these?

  Is there a better way to do this?

  Please advise.

  Kurt

• Koen Verbeeck

  SSC Guru

  Points: 259115

  More actions

  December 16, 2011 at 2:56 am

  #1422351

  If I get this correctly, you are selecting 16 integers with 16 different queries. In those queries I don't see a date somewhere. So my best guess is that you get a date into those queries and that you use a GROUP BY on the date column. That way you get results for every date in the table, and you can use that as an axis.

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• kdnicholsster

  Ten Centuries

  Points: 1152

  More actions

  December 16, 2011 at 4:40 am

  #1422389

  Hello Koen and all,

  Thanks for your reply. Can you provide an example to get today's date and a date range.

  I am also investigating the possibility of using the Warehouse Cube.

  In any event can you or someone else show me an example?

  Thanks,

  Kurt

• Koen Verbeeck

  SSC Guru

  Points: 259115

  More actions

  December 16, 2011 at 4:51 am

  #1422392

  kdnicholsster (12/16/2011)

  ---

  Hello Koen and all,

  Thanks for your reply. Can you provide an example to get today's date and a date range.

  I don't know your database. Do the tables have a datetime column?

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• kdnicholsster

  Ten Centuries

  Points: 1152

  More actions

  December 16, 2011 at 5:06 am

  #1422396

  Yes it is called LastUpdatedDateTime and it is in both the DimIteration and DimWorkItem.

• Koen Verbeeck

  SSC Guru

  Points: 259115

  More actions

  December 16, 2011 at 5:59 am

  #1422426

  Then add that column in your select list and add a GROUP BY clause over that column.

  That way you get your result for every date in the LastUpdatedDateTime column.

  Question: does the LastUpdatedDateTime column make sense business-wise? Or is it just a metadata column telling you when the row was last updated?

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• kdnicholsster

  Ten Centuries

  Points: 1152

  More actions

  December 16, 2011 at 6:19 am

  #1422444

  I think you are correct it is just a column for audit purposes.

  What else can I use?

  Many thanks for your help. I need to get this going.

• Koen Verbeeck

  SSC Guru

  Points: 259115

  More actions

  December 16, 2011 at 7:08 am

  #1422488

  kdnicholsster (12/16/2011)

  ---

  I think you are correct it is just a column for audit purposes.

  What else can I use?

  Many thanks for your help. I need to get this going.

  If there isn't a date column, then it doesn't make much sense making a graph plotting the values against a date axis, isn't it?

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• kdnicholsster

  Ten Centuries

  Points: 1152

  More actions

  December 16, 2011 at 7:15 am

  #1422491

  True story.

  Let's try using the LastUpdatedDateTime.

  Once I see your syntax that will help me figure the rest of it out.

  I am also going to be looking at the Warehouse Cube.

  Again many thanks for your efforts.

• kdnicholsster

  Ten Centuries

  Points: 1152

  More actions

  December 18, 2011 at 6:17 pm

  #1422936

  Hello All,

  I still need some help with this.

  Any ideas?

  Kurt