When a report needs to cover one group of devices, start with its collection ID. Here's a small query to get the membership and names before adding anything else.

Run it against the site database

Open a query window connected to your ConfigMgr site database in SQL Server Management Studio, using an account with approved read access to the reporting views. Replace the example collection ID.

This is T-SQL for SQL Server. The collection query editor in the ConfigMgr console uses WQL, so this query doesn't belong there. Direct database access also has its own permissions; don't assume it applies your console's role-based scope.

SQL
DECLARE @CollectionID char(8) = 'ABC00042';

SELECT
    device.ResourceID,
    device.Netbios_Name0 AS DeviceName,
    membership.CollectionID
FROM dbo.v_R_System AS device
INNER JOIN dbo.v_FullCollectionMembership AS membership
    ON membership.ResourceID = device.ResourceID
WHERE membership.CollectionID = @CollectionID
ORDER BY device.Netbios_Name0, device.ResourceID;

The query reads two views and changes nothing in the database.

Why this join?

v_FullCollectionMembership relates resources to collections. The collection filter limits it to one group. v_R_System supplies the discovered system name, joined by ResourceID. Keeping that ID in the output makes it easier to investigate records that happen to share a name.

Microsoft documents both the collection membership views and joining membership to discovery data.

Before making it bigger

An empty result can mean the collection is empty, the ID is wrong, or you're connected to the wrong site's database. Check those first. The result reflects the database's recorded membership; it isn't a fresh poll of the devices.

Once this matches the expected collection, you can join the inventory needed for your report. Check each added view's row count per resource: one device can have many disks, addresses, or installed applications. Those joins can legitimately produce several rows per device.

Draft review: check the query and expected membership against your lab before publishing. This draft has not been executed against a live site database.