Rename Query Columns for Better Exports
Published
Do you have Queries that you run regularly? Do you rename the columns when you export to Microsoft Excel? Here's a tip to save you some time!
We can rename columns directly within the Query's Output so that they actually reflect what they are reporting. Let's walk through an example together.
Let's say you've crafted a Query in Accounts Payable to show you all 1099 Vendors who received more than $600.00 in calendar year 2012. Your Output column based on the Criteria field Summary > Total payment amount will have an underscore and a number in its heading. You'll see it on the Results tab, and if you use the Export to Excel feature, you'll see it in your new spreadsheet too.
That's not exactly clear, is it? It doesn't reflect what the column is actually showing us. We can do better!
On the Output tab of the Query, right-click on the field in the list and choose "Column Heading."
Then choose the title you'd actually like to display instead. In our example, I've chosen "2012 Total Payments."
You'll see the newly chosen title in parentheses next to the original field name on the Output tab.
If you take a look at the Results tab, you'll see that it no longer reads "Total payment amount_1" but the far preferable "2012 Total Payments." And, what's more, if you Export to Excel, the changes follow.
You can save your Query with these changes, and any subsequent runs will already be labeled correctly. This can save you a bit of time relabeling things in Excel and enable you to generate simple reports in a speedy fashion.
Just think, now you can use the same field multiple times within the same query's output, applying different filters to each instance, and changing the output column headers appropriately. Doing it this way, you get the information you were looking for and the final labeling will actually make sense to anyone who glances at it.
Much better, no?
We can rename columns directly within the Query's Output so that they actually reflect what they are reporting. Let's walk through an example together.
Let's say you've crafted a Query in Accounts Payable to show you all 1099 Vendors who received more than $600.00 in calendar year 2012. Your Output column based on the Criteria field Summary > Total payment amount will have an underscore and a number in its heading. You'll see it on the Results tab, and if you use the Export to Excel feature, you'll see it in your new spreadsheet too.
That's not exactly clear, is it? It doesn't reflect what the column is actually showing us. We can do better!
On the Output tab of the Query, right-click on the field in the list and choose "Column Heading."
Then choose the title you'd actually like to display instead. In our example, I've chosen "2012 Total Payments."
You'll see the newly chosen title in parentheses next to the original field name on the Output tab.
If you take a look at the Results tab, you'll see that it no longer reads "Total payment amount_1" but the far preferable "2012 Total Payments." And, what's more, if you Export to Excel, the changes follow.
You can save your Query with these changes, and any subsequent runs will already be labeled correctly. This can save you a bit of time relabeling things in Excel and enable you to generate simple reports in a speedy fashion.
Just think, now you can use the same field multiple times within the same query's output, applying different filters to each instance, and changing the output column headers appropriately. Doing it this way, you get the information you were looking for and the final labeling will actually make sense to anyone who glances at it.
Much better, no?
News
ARCHIVED | Financial Edge® Tips and Tricks
06/24/2013 6:56am EDT
Leave a Comment