Excel Extendable Detail Area Features and Capabilities

 

Introduction

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.

Steps

1. Start with an Excel worksheet with a linked query that returns data when refreshed.
    
2. 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.
      
3. The formula can be applied to all rows of data returned using the Excel Fill Down function.
   1. Highlight the entire column and choose Edit Fill Down. 
   2. The formula should then apply to all of the cells highlighted in your result column.
       
4. After verifying the result column contains the correct data, highlight all cells in the result column containing data.
   1. From the main menu choose: ShowCase Query References
   2. Click the Add button.
   3. Use the drop down on Data Category to choose 'Extendable Detail Area' and click 'OK'.
   4. The Data References window should now contain an Extendable reference that lists the exact cells highlighted in the result column. 
   5. Click 'OK' and save the Excel Worksheet.
       
5. 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.
      
6. Upon completing the prompt the query will execute and return the resulting data directly into the worksheet.
    
7. The calculated column should contain the exact number of rows that the query returned and the calculation should have been applied to each row.

Ref#: 1481516