Emerson Exchange 365

• Not Answered

How to Filter in an Excel Template

We are trying to customize a DeltaV InSight report and need to have a filter in the Excel Template (.XLTX) that is populated by an HML file to create the report as an Excel Workbook (.XLT).  We can write the filter in the Workbook but not in the Template.  We need the filter in the Template so it will automatically generate with the desired information.  

An example of the filter in a Workbook is shown in the attached file.  A InSight Overview Template (zipped) is also attached. 

Google says this can't be done but I'll bet one (or more) of you can figure it out!  Thanks in advance!

Template_Filter Picture.docxOverviewTemplate_20210521.zip

James Beall

• 21 May 2021 12:20 PM
• Cancel

4 Replies

• James Beall

  

  • 21 May 2021 1:28 PM

  Correction - It is an "XML" file instead of "HML" file.

  • Cancel
• RobPerry

  

  • 21 May 2021 5:15 PM

  In reply to James Beall:

  You might be able to use a Data Query or FILTERXML function in Excel XLTX or do the filtering in VB code if the template can be saved as an XLTM.
  The query depends a bit on the xml file structure. The function does not work in all Excel versions, only does simple filtering and has limiting conditions on format and prior knowledge of the number of results, etc. The VB code would be more flexible and allow more complex filtering.
  If you post an example xml file I am sure someone can post a solution.

  • Cancel
• James Beall

  

  • 21 May 2021 5:37 PM

  In reply to RobPerry:

  Thanks Rob! We'll give it a try! FYI, what we are trying to do is create a custom InSight report (overview and area report) that only lists the loops that have exceeded (in a bad way) any one of the performance metrics. The standard report shows all the loops being reported and a color notation of those that are bad. So, you might have 500-1000 loops on the report and you have to pick out all the loops that need attention.

  • Cancel
• James Beall

  

  • 21 May 2021 6:15 PM

  In reply to James Beall:

  TemplateOutput.xlsxAreaPerformanceTemplate - Copy1.zipArea Performance Report - Copy_xsd.zipMore info:

  Running the InSight Reports doesn’t export a raw xml, but it looks like it directly imports the xml into the workbook.

  Attached is the excel template (Area Performance template provided in DeltaV/DVData/InSight/Reports/Templates/AreaPerformanceTemplate-Copy1.xltx) with it’s xsd definition. The ultimate goal is to show the rows in the table that have %Time exceed their % Time Limits. We could achieve this by either (1) filtering the rows by the red color text that shows up in Columns W, X, Y, Z, AC, or AD because there is Conditional Formatting rules applied to them, or (2) filter the rows with the binary equation output in column V (I wrote a very simple equation for now for testing purposes). Either goal is desired, and ultimately we want to be able to filter this without human intervention of some kind (i.e. pushing a button that runs a macro that will filter the rows in the table).

  With (1), I’ve ran into problems with the template; first off, the template grays out the “Filter by Color”, and it only show “Text Filters” versus “Number Filters”. With (2), whenever you run the Performance Reports on InSight for the report output (i.e. TemplateOutput.xlsx), the result does not show up properly. You have to un-filter and filter Column V again for the results to show up properly, which still involves human intervention still.    

  • Cancel