You can use the Microsoft Query Wizard in Excel to help you build the SQL query for an Excel Pivot Table. If you select columns from more than one table in the Query Wizard screen and click Next, the following message appears:
"The Query Wizard can not continue because it can not join the tables in your query. You must join the tables manually in Microsoft Query by dragging the field to the join between the tables."
This is not an error; this is a message letting you know you need to join the tables selected for the query. Click OK on the message to continue to the Microsoft Query screen and join the tables. If you are unsure of which fields to join on, visit the Data Model to learn more about the tables and see how they should be linked together.
After identifying the tables to be used in the Pivot Table, fields are added to various sections of the table. Below is a definition of each section and how the fields are used:
- Report Filter - Fields added here appear at the top of the Pivot Table as a drop-down filter. You can select one or multiple values.
- Column Labels - Fields added here appear across the top of the Pivot Table. Only distinct values are listed.
- Row Labels - Fields added here display in the first column of the Pivot Table. Only distinct values are listed.
- Values - Fields added here are summarized for each row/column. By default, the calculation is a sum, but this can be changed to a different type of calculation (count, average, etc.).
Excel automatically creates totals for each row and column. These totals can be turned off if desired.
Comments
0 comments
Please sign in to leave a comment.