MECM: SQL query for a report by collection, model, count
Wanted to create a SQL query to get a count of every model, per vendor like Dell based on the collection being used for creating a report in SSRS.
This is a start.
SELECT
coll.Name AS [Collection Name],
sys.Model0 AS [Model],
COUNT(DISTINCT sys.ResourceID) AS [Model Count]
FROM v_FullCollectionMembership fcm
JOIN v_Collection coll
ON fcm.CollectionID = coll.CollectionID
JOIN v_GS_COMPUTER_SYSTEM sys
ON fcm.ResourceID = sys.ResourceID
WHERE sys.Manufacturer0 LIKE 'Dell%'
GROUP BY coll.Name, sys.Model0
ORDER BY coll.Name, sys.Model0;
Example Output:
| Collection Name | Model | Model Count |
|---|---|---|
| HR Laptops | Latitude 7420 | 5 |
| IT Desktops | OptiPlex 7090 | 3 |
This list service tags comma separated
SELECT
coll.Name AS [Collection Name],
sys.Model0 AS [Model],
COUNT(DISTINCT sys.ResourceID) AS [Model Count],
STRING_AGG(enc.SerialNumber0, ', ') WITHIN GROUP (ORDER BY enc.SerialNumber0) AS [Service Tags]
FROM v_FullCollectionMembership fcm
JOIN v_Collection coll
ON fcm.CollectionID = coll.CollectionID
JOIN v_GS_COMPUTER_SYSTEM sys
ON fcm.ResourceID = sys.ResourceID
JOIN v_GS_SYSTEM_ENCLOSURE enc
ON sys.ResourceID = enc.ResourceID
WHERE sys.Manufacturer0 LIKE 'Dell%'
GROUP BY coll.Name, sys.Model0
ORDER BY coll.Name, sys.Model0;
Example Output:
| Collection Name | Model | Model Count | Service Tags |
|---|---|---|---|
| HR Laptops | Latitude 7420 | 5 | ABC1234, DEF5678, GHI9012, … |
| IT Desktops | OptiPlex 7090 | 3 | XYZ1111, XYZ2222, XYZ3333 |
more work on the RDL for building the report even further with added warranty
add parameters
SELECT CollectionID, Name FROM v_Collection ORDER BY Name
SELECT
coll.CollectionID,
coll.Name AS CollectionName,
cs.Model0 AS Model,
COUNT(DISTINCT sys.ResourceID) AS SystemCount,
warr.WarrantyEndDate0 AS WarrantyEndDate
FROM v_FullCollectionMembership fcm
JOIN v_Collection coll
ON fcm.CollectionID = coll.CollectionID
JOIN v_R_System sys
ON fcm.ResourceID = sys.ResourceID
JOIN v_GS_COMPUTER_SYSTEM cs
ON sys.ResourceID = cs.ResourceID
LEFT JOIN v_GS_WARRANTY_INFO warr
ON sys.ResourceID = warr.ResourceID
WHERE cs.Manufacturer0 LIKE 'Dell%'
AND (@CollectionID = 'ALL' OR coll.CollectionID = @CollectionID)
GROUP BY coll.CollectionID, coll.Name, cs.Model0, warr.WarrantyEndDate0
ORDER BY coll.Name, cs.Model0;
Now we have a XML report to be updated to SSRS and saved as an RDL report
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<AutoRefresh>0</AutoRefresh>
<rd:ReportUnitType>Inch</rd:ReportUnitType>
<rd:ReportID>c9c9c9c9-1111-2222-3333-444444444444</rd:ReportID>
<Description>MECM Hardware Dashboard - Dell models by collection with KPI, charts, and summary table.</Description>
<DataSources>
<DataSource Name="DataSource">
<DataSourceReference>DataSource</DataSourceReference>
<rd:DataSourceID>11111111-2222-3333-4444-555555555555</rd:DataSourceID>
</DataSource>
</DataSources>
<ReportParameters>
<ReportParameter Name="CollectionList">
<DataType>String</DataType>
<Prompt>Select Collections</Prompt>
<MultiValue>true</MultiValue>
<AllowBlank>true</AllowBlank>
<Hidden>false</Hidden>
<ValidValues>
<DataSetReference>
<DataSetName>CollectionListDS</DataSetName>
<ValueField>CollectionID</ValueField>
<LabelField>Name</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
</ReportParameters>
<DataSets>
<!-- Dataset: Collections for parameter -->
<DataSet Name="CollectionListDS">
<Query>
<DataSourceName>DataSource</DataSourceName>
<CommandText>
SELECT CollectionID, Name
FROM v_Collection
ORDER BY Name;
</CommandText>
</Query>
<Fields>
<Field Name="CollectionID">
<DataField>CollectionID</DataField>
</Field>
<Field Name="Name">
<DataField>Name</DataField>
</Field>
</Fields>
</DataSet>
<!-- Dataset: Summary for table and bar chart -->
<DataSet Name="DellModelsSummary">
<Query>
<DataSourceName>DataSource</DataSourceName>
<CommandText>
SELECT
coll.CollectionID,
coll.Name AS CollectionName,
cs.Model0 AS Model,
COUNT(cs.ResourceID) AS ModelCount
FROM v_FullCollectionMembership fcm
INNER JOIN v_Collection coll
ON fcm.CollectionID = coll.CollectionID
INNER JOIN v_GS_COMPUTER_SYSTEM cs
ON fcm.ResourceID = cs.ResourceID
WHERE cs.Manufacturer0 LIKE '%Dell%'
AND (@CollectionList IS NULL OR coll.CollectionID IN (@CollectionList))
GROUP BY coll.CollectionID, coll.Name, cs.Model0
ORDER BY coll.Name, cs.Model0;
</CommandText>
</Query>
<Fields>
<Field Name="CollectionID">
<DataField>CollectionID</DataField>
</Field>
<Field Name="CollectionName">
<DataField>CollectionName</DataField>
</Field>
<Field Name="Model">
<DataField>Model</DataField>
</Field>
<Field Name="ModelCount">
<DataField>ModelCount</DataField>
</Field>
</Fields>
</DataSet>
<!-- Dataset: KPI total Dell devices -->
<DataSet Name="TotalCount">
<Query>
<DataSourceName>DataSource</DataSourceName>
<CommandText>
SELECT COUNT(*) AS TotalCount
FROM v_GS_COMPUTER_SYSTEM
WHERE Manufacturer0 LIKE '%Dell%';
</CommandText>
</Query>
<Fields>
<Field Name="TotalCount">
<DataField>TotalCount</DataField>
</Field>
</Fields>
</DataSet>
<!-- Dataset: Pie data (same as summary, used for charts) -->
<DataSet Name="PieData">
<Query>
<DataSourceName>DataSource</DataSourceName>
<CommandText>
SELECT
coll.CollectionID,
coll.Name AS CollectionName,
cs.Model0 AS Model,
COUNT(cs.ResourceID) AS ModelCount
FROM v_FullCollectionMembership fcm
INNER JOIN v_Collection coll
ON fcm.CollectionID = coll.CollectionID
INNER JOIN v_GS_COMPUTER_SYSTEM cs
ON fcm.ResourceID = cs.ResourceID
WHERE cs.Manufacturer0 LIKE '%Dell%'
AND (@CollectionList IS NULL OR coll.CollectionID IN (@CollectionList))
GROUP BY coll.CollectionID, coll.Name, cs.Model0
ORDER BY coll.Name, cs.Model0;
</CommandText>
</Query>
<Fields>
<Field Name="CollectionID">
<DataField>CollectionID</DataField>
</Field>
<Field Name="CollectionName">
<DataField>CollectionName</DataField>
</Field>
<Field Name="Model">
<DataField>Model</DataField>
</Field>
<Field Name="ModelCount">
<DataField>ModelCount</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Body>
<ReportItems>
<!-- ========================= -->
<!-- REPORT TITLE -->
<!-- ========================= -->
<Textbox Name="ReportTitle">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>MECM Hardware Dashboard</Value>
<Style>
<FontSize>18pt</FontSize>
<FontWeight>Bold</FontWeight>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<Top>0.2in</Top>
<Left>0.2in</Left>
<Height>0.4in</Height>
<Width>7.8in</Width>
<ZIndex>1</ZIndex>
<Style>
<TextAlign>Center</TextAlign>
</Style>
</Textbox>
<!-- ========================= -->
<!-- KPI TILE (BOXED) -->
<!-- ========================= -->
<Rectangle Name="KPI_Tile">
<Top>0.8in</Top>
<Left>0.2in</Left>
<Height>0.9in</Height>
<Width>7.8in</Width>
<ZIndex>2</ZIndex>
<Style>
<Border>
<Style>Solid</Style>
<Color>#666666</Color>
</Border>
<BackgroundColor>#F2F2F2</BackgroundColor>
</Style>
<ReportItems>
<!-- KPI Label -->
<Textbox Name="KPI_Label">
<Top>0.1in</Top>
<Left>0in</Left>
<Height>0.3in</Height>
<Width>7.8in</Width>
<ZIndex>3</ZIndex>
<Value>Total Dell Devices</Value>
<Style>
<FontSize>12pt</FontSize>
<FontWeight>Bold</FontWeight>
<TextAlign>Center</TextAlign>
</Style>
</Textbox>
<!-- KPI Value -->
<Textbox Name="KPI_Value">
<Top>0.4in</Top>
<Left>0in</Left>
<Height>0.4in</Height>
<Width>7.8in</Width>
<ZIndex>4</ZIndex>
<Value>=First(Fields!TotalCount.Value, "TotalCount")</Value>
<Style>
<FontSize>20pt</FontSize>
<FontWeight>Bold</FontWeight>
<TextAlign>Center</TextAlign>
<Color>#000000</Color>
</Style>
</Textbox>
</ReportItems>
</Rectangle>
<!-- ========================= -->
<!-- BAR CHART (MODELS) -->
<!-- ========================= -->
<Chart Name="BarChart_Models">
<Top>1.9in</Top>
<Left>0.2in</Left>
<Height>2.2in</Height>
<Width>7.8in</Width>
<ZIndex>5</ZIndex>
<DataSetName>DellModelsSummary</DataSetName>
<ChartCategoryHierarchy>
<ChartMembers>
<ChartMember>
<Group Name="ModelGroup">
<GroupExpressions>
<GroupExpression>=Fields!Model.Value</GroupExpression>
</GroupExpressions>
</Group>
</ChartMember>
</ChartMembers>
</ChartCategoryHierarchy>
<ChartSeriesHierarchy>
<ChartMembers>
<ChartMember />
</ChartMembers>
</ChartSeriesHierarchy>
<ChartData>
<ChartSeriesCollection>
<ChartSeries Name="Series_ModelCount">
<ChartType>Bar</ChartType>
<Value>=Sum(Fields!ModelCount.Value)</Value>
<Category>=Fields!Model.Value</Category>
<Style>
<Color>#4472C4</Color>
</Style>
</ChartSeries>
</ChartSeriesCollection>
</ChartData>
<ChartAreas>
<ChartArea Name="Default">
<ChartCategoryAxes>
<ChartAxis Name="CategoryAxis">
<Title>
<Caption>Model</Caption>
</Title>
</ChartAxis>
</ChartCategoryAxes>
<ChartValueAxes>
<ChartAxis Name="ValueAxis">
<Title>
<Caption>Count</Caption>
</Title>
</ChartAxis>
</ChartValueAxes>
</ChartArea>
</ChartAreas>
<ChartLegends>
<ChartLegend Name="Legend1">
<Style>
<FontSize>9pt</FontSize>
</Style>
</ChartLegend>
</ChartLegends>
</Chart>
<!-- ========================= -->
<!-- PIE CHART LIST (START) -->
<!-- ========================= -->
<List Name="PieChartList">
<Top>4.3in</Top>
<Left>0.2in</Left>
<Height>2in</Height>
<Width>7.8in</Width>
<ZIndex>6</ZIndex>
<DataSetName>PieData</DataSetName>
<Grouping Name="PieCollectionGroup">
<GroupExpressions>
<GroupExpression>=Fields!CollectionID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<!-- Container for 2‑per‑row layout -->
<Rectangle Name="PieRowContainer">
<Top>0in</Top>
<Left>0in</Left>
<Height>2in</Height>
<Width>7.8in</Width>
<ZIndex>7</ZIndex>
<ReportItems>
<!-- LEFT PIE CHART SLOT -->
<Rectangle Name="PieSlotLeft">
<Top>0in</Top>
<Left>0in</Left>
<Height>2in</Height>
<Width>3.8in</Width>
<ZIndex>8</ZIndex>
<ReportItems>
<!-- Pie chart will be inserted in Part 3 -->
</ReportItems>
</Rectangle>
<!-- RIGHT PIE CHART SLOT -->
<Rectangle Name="PieSlotRight">
<Top>0in</Top>
<Left>4in</Left>
<Height>2in</Height>
<Width>3.8in</Width>
<ZIndex>9</ZIndex>
<ReportItems>
<!-- Pie chart will be inserted in Part 3 -->
</ReportItems>
</Rectangle>
</ReportItems>
</Rectangle>
</ReportItems>
</List>
<!-- ========================= -->
<!-- LEFT PIE CHART -->
<!-- ========================= -->
<Chart Name="PieChart_Left">
<DataSetName>PieData</DataSetName>
<Top>0in</Top>
<Left>0in</Left>
<Height>2in</Height>
<Width>3.8in</Width>
<ChartCategoryHierarchy>
<ChartMembers>
<ChartMember>
<Group Name="PieModelGroupLeft">
<GroupExpressions>
<GroupExpression>=Fields!Model.Value</GroupExpression>
</GroupExpressions>
</Group>
</ChartMember>
</ChartMembers>
</ChartCategoryHierarchy>
<ChartSeriesHierarchy>
<ChartMembers>
<ChartMember />
</ChartMembers>
</ChartSeriesHierarchy>
<ChartData>
<ChartSeriesCollection>
<ChartSeries Name="Series_PieLeft">
<ChartType>Pie</ChartType>
<Value>=Sum(Fields!ModelCount.Value)</Value>
<Category>=Fields!Model.Value</Category>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!ModelCount.Value)</Value>
</DataValue>
</DataValues>
<Style>
<FontSize>8pt</FontSize>
</Style>
<DataLabel>
<Value>=Fields!Model.Value & " (" & Sum(Fields!ModelCount.Value) & ")"</Value>
<Style>
<FontSize>8pt</FontSize>
</Style>
</DataLabel>
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartSeriesCollection>
</ChartData>
<ChartLegends>
<ChartLegend Name="PieLegendLeft">
<Style>
<FontSize>8pt</FontSize>
</Style>
</ChartLegend>
</ChartLegends>
</Chart>
<!-- ========================= -->
<!-- RIGHT PIE CHART -->
<!-- ========================= -->
<Chart Name="PieChart_Right">
<DataSetName>PieData</DataSetName>
<Top>0in</Top>
<Left>0in</Left>
<Height>2in</Height>
<Width>3.8in</Width>
<ChartCategoryHierarchy>
<ChartMembers>
<ChartMember>
<Group Name="PieModelGroupRight">
<GroupExpressions>
<GroupExpression>=Fields!Model.Value</GroupExpression>
</GroupExpressions>
</Group>
</ChartMember>
</ChartMembers>
</ChartCategoryHierarchy>
<ChartSeriesHierarchy>
<ChartMembers>
<ChartMember />
</ChartMembers>
</ChartSeriesHierarchy>
<ChartData>
<ChartSeriesCollection>
<ChartSeries Name="Series_PieRight">
<ChartType>Pie</ChartType>
<Value>=Sum(Fields!ModelCount.Value)</Value>
<Category>=Fields!Model.Value</Category>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!ModelCount.Value)</Value>
</DataValue>
</DataValues>
<Style>
<FontSize>8pt</FontSize>
</Style>
<DataLabel>
<Value>=Fields!Model.Value & " (" & Sum(Fields!ModelCount.Value) & ")"</Value>
<Style>
<FontSize>8pt</FontSize>
</Style>
</DataLabel>
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartSeriesCollection>
</ChartData>
<ChartLegends>
<ChartLegend Name="PieLegendRight">
<Style>
<FontSize>8pt</FontSize>
</Style>
</ChartLegend>
</ChartLegends>
</Chart>
</ReportItems>
</Rectangle>
</ReportItems>
</List>
<!-- ========================= -->
<!-- SUMMARY TABLE -->
<!-- ========================= -->
<Tablix Name="SummaryTable">
<Top>6.6in</Top>
<Left>0.2in</Left>
<Height>1in</Height>
<Width>7.8in</Width>
<ZIndex>10</ZIndex>
<DataSetName>DellModelsSummary</DataSetName>
<TablixBody>
<TablixColumns>
<TablixColumn><Width>2.5in</Width></TablixColumn>
<TablixColumn><Width>3in</Width></TablixColumn>
<TablixColumn><Width>1.3in</Width></TablixColumn>
</TablixColumns>
<TablixRows>
<!-- Header Row -->
<TablixRow>
<Height>0.3in</Height>
<TablixCells>
<TablixCell>
<CellContents>
<Textbox Name="Header_Collection">
<Value>Collection</Value>
<Style>
<FontWeight>Bold</FontWeight>
<BackgroundColor>#DDDDDD</BackgroundColor>
</Style>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="Header_Model">
<Value>Model</Value>
<Style>
<FontWeight>Bold</FontWeight>
<BackgroundColor>#DDDDDD</BackgroundColor>
</Style>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="Header_Count">
<Value>Count</Value>
<Style>
<FontWeight>Bold</FontWeight>
<BackgroundColor>#DDDDDD</BackgroundColor>
</Style>
</Textbox>
</CellContents>
</TablixCell>
</TablixCells>
</TablixRow>
<!-- Detail Row -->
<TablixRow>
<Height>0.25in</Height>
<TablixCells>
<TablixCell>
<CellContents>
<Textbox Name="Detail_Collection">
<Value>=Fields!CollectionName.Value</Value>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="Detail_Model">
<Value>=Fields!Model.Value</Value>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="Detail_Count">
<Value>=Fields!ModelCount.Value</Value>
</Textbox>
</CellContents>
</TablixCell>
</TablixCells>
</TablixRow>
</TablixRows>
</TablixBody>
<TablixColumnHierarchy>
<TablixMembers>
<TablixMember />
<TablixMember />
<TablixMember />
</TablixMembers>
</TablixColumnHierarchy>
<TablixRowHierarchy>
<TablixMembers>
<TablixMember />
<TablixMember />
</TablixMembers>
</TablixRowHierarchy>
</Tablix>
</ReportItems>
<Height>9in</Height>
<Style />
</Body>
<Width>8.5in</Width>
<Page>
<PageHeight>11in</PageHeight>
<PageWidth>8.5in</PageWidth>
<LeftMargin>0.2in</LeftMargin>
<RightMargin>0.2in</RightMargin>
<TopMargin>0.2in</TopMargin>
<BottomMargin>0.2in</BottomMargin>
</Page>
</Report>