When using ShowCase Query Add-ins to return data to a worksheet it is often desirable to perform calculations on the returning data. It is possible to create a calculated column that will grow and shrink with differing number of rows returned using an Extendable Detail Area provided by the ShowCase Excel Add-in.
- Start with an Excel worksheet with a linked query that returns data when refreshed.
- Create a formula for your planned calculation in the first cell of the column you want it to appear in.
- When the cursor leaves the cell you should receive the resulting calculation.
- The formula can be applied to all rows of data returned using the Excel Fill Down function.
- Highlight the entire column and choose Edit Fill Down.
- The formula should then apply to all of the cells highlighted in your result column.
- After verifying the result column contains the correct data, highlight all cells in the result column containing data.
- From the main menu choose: ShowCase Query References
- Click the Add button.
- Use the drop down on Data Category to choose 'Extendable Detail Area' and click 'OK'.
- The Data References window should now contain an Extendable reference that lists the exact cells highlighted in the result column.
- Click 'OK' and save the Excel Worksheet.
- Run the attached query by choosing ShowCase Refresh Query or Refresh All Queries.
- If the Query application is not currently open a log in screen will appear.
- If Query is open or after the log in your query will run. If the query has a prompt it will appear on the Excel worksheet.
- Upon completing the prompt the query will execute and return the resulting data directly into the worksheet.
- The calculated column should contain the exact number of rows that the query returned and the calculation should have been applied to each row.
Last Modified On:
No, open a new Support Case
You don't have the appropriate permissions.