When using a pivot table, a common piece of information needed is the count of cases that meet certain criteria and the unique Case ID’s of each case. To accommodate this need, TDA supports two data fields named “Item_CaseCount” and “CaseID” that provides the necessary information.
Item_CaseCount: Provides a count of the total number of cases returned by the query.
CaseID: Provides a unique CaseID for each case (Case Number, Center Code, and Year Code) so that counts are accurate when a single case meets search criteria more than once.
If you are interested in performing aggregate reporting using a pivot table, you will need to add these data fields to your query. Below are the steps to add these data fields.
1. Open the “Query Designer” window of the query you would like to use with a pivot table.
2. Locate and select the green [Click here to add a new column] link to add a new query column. Use the provided drop down menus to select the “Item_CaseCount” query column.
3. The “Item_CaseCount” query column is now added to the query.
4. Repeat the above steps to add the “CaseID” query column.
5. Both the “Item_CaseCount” and “CaseID” query columns are now added to the query.
6. Save the changes.