AnsweredAssumed Answered

SWE Custom PLC IO Report

Question asked by Vitaliy Kozlov on May 4, 2018
Latest reply on May 10, 2018 by Daniel Myers

I'm working on creating an Excel report that creates a table of all the components in a project sheets that contain any kind of IO. this is what i got so far:

 

SELECT DISTINCT

      tew_component.com_id AS id

      ,  tew_component.com_type  AS type

      ,  tew_component_parent.com_tag AS TagParent

      ,  tew_component.com_tag AS TagText

      ,  tew_component.com_tagpath AS Tagpath

      ,  tew_component.com_tagroot AS TagRoot

      ,  tew_component.com_tagorderno AS TagOrderNo

      ,  tew_component.com_loc_id AS LocId

      ,  tew_translatedtext.tra_0 AS tra_0

      ,  tew_location.loc_text AS LocText

      ,  tew_componentelement.cel_group AS cel_group

, tew_symbol.sym_posx AS symbol_posX

, tew_symbol.sym_posY AS symbol_posY

, tew_file.fil_title AS page_number

, tew_file.fil_tit_linenumber AS page_number_rows

, tew_file.fil_tit_columnnumber AS page_number_columns

, tew_file.fil_tit_linemargin AS page_yvalue_at_top_row

, tew_file.fil_tit_columnmargin AS page_left_margin

, tew_file.fil_tit_columnsize AS page_column_width

, tew_file.fil_tit_linesize AS page_row_height

, tew_component.com_type AS type

 

 

      , 1 AS  %ELEMENT_COUNT%

      , tew_translatedtext.tra_0 AS tra_0

 

 

FROM (((((( tew_component

      LEFT JOIN tew_component AS tew_component_parent ON tew_component.com_com_id=tew_component_parent.com_id)

      LEFT JOIN tew_componentelement ON tew_component.com_id = tew_componentelement.cel_com_id)

      LEFT JOIN tew_translatedtext ON tew_component.com_id = tew_translatedtext.tra_objectid)          

      LEFT JOIN tew_location ON  tew_component.com_loc_id =  tew_location.loc_id)

      LEFT JOIN tew_symbol ON tew_symbol.sym_com_id = tew_component.com_id) /* Table containing information on the symbol */

      LEFT JOIN tew_file ON tew_file.fil_id = tew_symbol.sym_fil_id) /* Table containing information on the page the symbol is located on*/

 

 

      WHERE tew_component.com_type IN (-1,0, 1,2)

                                  ORDER BY   tew_location.loc_text ,  tew_componentelement.cel_group

 

 

My issue is adding another condition with "WHERE" so that the sheet name contains the words "IN" or "OUT". I'm able to get the sheet number, but not the name. I looked at the Table of Contents report that uses "tew_file" "tew_translatedtext.tra_0 AS tra_0" as sheet name. I'm not sure how to create a UNION with existing SELECT statement so as to filter out any components not in IO sheets.@

Outcomes