Add a query for the cards of the board

We have now successfully added our first query to the report. In this section, we’ll add a query for the cards:

• Hover over CardDataSource and click on the Add query button.
• In the Query Name field, type Cards.
• Click Run Query Builder.

In the Available Tables and Views section, use the search box to find dimWorkItems and double-click it to add it to the query builder.

Repeat the same process for:

• dimWorkItemCategories
• dimResources

1. Set up the Cards table and field aliases

Select OC.DIMWORKITEMS.. In Table Properties, type Cards in the Alias field. 

Check the following fields and update their Alias accordingly:

• ID → CardId
• Title → CardTitle
• Scroll down and find FullReferenceNumber → CardFullReferenceNumber

• Make sure that the BoardId is selected (No need to add alias):

• Scroll up and select CreateOn and type inside the Alias field: CardCreatedOn 
• Set the Sort Type to Descending for this field. 

• Move to the next table: OC.DIMWORKITEMCATEGORIES and name it CardCategories inside alias field.
• Open Title and add CardCategoriesTitle inside the alias field.

• Move to the next table: OC.DIMRESOURCES and name it Resources inside alias field.
• Open DisplayName and add ResourceDisplayName inside the alias field.

2. Join the Resources table with Cards

• Click on the Resources table header and drag it next to the Cards table.
• Locate the CreatedByResourceID field in the Cards table.
• Click and drag the ID field from the Resources table and drop it onto CreatedByResourceID in the Cards table.

This action establishes a join between the two tables.

3. Add the BoardId parameter to the Cards query

• In the Parameters section of the side panel, click the + button to add a new parameter.
• Change the name from Parameter1 to BoardId.
• Open the Type dropdown and select Expression.
• Set the Value to Number (32-bit integer).
• Set the value to the BoardId parameter.

4. Filter the Cards query by BoardId

• In the Side Panel, open Query Properties.
• Click the three dots next to the Filter field to open the Filter Editor.
• Hover over And and click the + button to Add condition.
• In the empty blue box, select Cards, then choose BoardID.
• In the grey dropdown box, select Parameter.
• Click OK to close the Filter Editor.
• Click OK at the bottom of the Query Builder to save your query.
• Click Finish to close the wizard and return to the main report designer.