6 Replies Latest reply: Dec 5, 2008 6:15 PM by 690399 RSS

    Report Headers

    690399
      I have a report in the Change Order Module that shows COs
      with a certain status from crvw table. (It also pulls info
      from chmt_table and pomt and vnmt).
      <br
      />The header of the report pulls information from the
      proj table.

      When there is no
      information in the crvw table then nothing shows on the
      report which is ok for the detail section.<br
      />
      However, I would like the report to still
      pull the project title in and project number from the proj
      table in the header.

      Is this
      possible? I tried making the join between the proj and
      crvw table to be crvw.project_name = proj.project_name and
      rows from proj that have no crvw. But that did not
      work.

      I'd appreciate any other
      ideas that you might have.

      Thanks.
        • 1. Report Headers
          690399
          I bet that the remaining restriction is with the
          retrieval arguments. You probably have
          crvw.project_name = :project_name in the Where clause.
          Make this proj.project_name = :project_name instead.
          You will also need to make sure that the join between
          crvw and chmt_table is crvw.master_key =
          chmt_table.master_key and rows from crvw that have no
          chmt_table.

          If this still does
          not work, post your report file to the Yahoo group,
          http://tech.groups.yahoo.com/group/expusers/<br
          />
          Rick
          • 2. Report Headers
            690399
            I tried the above but still am not getting anything in
            the header when there is nothing to pull in the detail
            section.

            I've uploaded the
            R_CHANGE.PBL to the expusers group. The report
            originally was r_change_wcpss_02. The one with the
            changes you suggested is r_change_wcpss_02test. Thanks.
            • 3. Report Headers
              690399
              I could not view your SQL graphically without adding
              the custom field, so I rewrote the syntax. Copy this
              into your report and test it (go into SQL, then select
              Design | Convert to Syntax). You were close - I think
              that the line in the WHERE clause, "crvw.status =
              'ENC'" was still causing no rows to appear. I added
              this as part of the outer join.<br
              /><strong><em><br
              />SELECT     crvw.project_name
              ,           <br
              />               crvw.item_type
              ,           <br
              />               crvw.approved_date
              ,           <br
              />               crvw.change_number
              ,           <br
              />               crvw.cost
              ,           <br
              />               crvw.from_vendor
              ,           <br
              />               crvw.status
              ,           <br
              />               crvw.title
              ,           <br
              />               crvw.to_vendor
              ,           <br
              />               proj.company_name
              ,           <br
              />               proj.job_number
              ,           <br
              />               proj.project_number
              ,           <br
              />               proj.project_title
              ,           <br
              />               pomt.contract_type
              ,           <br
              />               crvw.time_change
              ,           <br
              />               pomt.wcpss_no
              ,           <br
              />               pomt.description
              ,           <br
              />               chmt_table.approved_date
              ,           <br
              />               chmt_table.approved_date2
              ,           <br
              />               chmt_table.approved_date3
              ,           <br
              />               crvw.change_date

              <br
              />FROM          proj     LEFT
              OUTER JOIN crvw ON proj.project_name =
              crvw.project_name AND <br
              />                                                                      (
              crvw.status = 'ENC' )
              <br
              />                         LEFT
              OUTER JOIN vnmt ON proj.company_abbrev =
              vnmt.vendor_abbrev AND<br
              />                                                                      vnmt.project_name
              = :project_name
              <br
              />                         LEFT
              OUTER JOIN pomt ON crvw.cntr_master_key =
              pomt.master_key
              <br
              />                         LEFT
              OUTER JOIN chmt_table ON crvw.master_key =
              chmt_table.master_key

              WHERE
                   proj.project_name
              = :project_name</strong></em>
              • 4. Report Headers
                690399
                Sorry - noticed an error in my statement:<br
                />vnmt.project_name = :project_name <br
                />should be
                vnmt.project_name =
                <u>:vendor_type</u>
                • 5. Report Headers
                  690399
                  Thank you for the help. That syntax worked and now I
                  have a report just like I wanted.
                  <br
                  />I do have a question about the statement:<br
                  />LEFT OUTER JOIN crvw ON proj.project_name =
                  crvw.project_name AND (crvw.status = 'ENC' ) <br
                  />
                  Is there a way for InfoMaker to do
                  this sort of Outer Join Graphically? You said that you
                  couldn't work graphically b/c you do not have the ENC
                  in your database. So if you had it in your database
                  would you have been able to create the same result in
                  graphics mode? If so, how?
                  <br
                  />Thanks.
                  • 6. Report Headers
                    690399
                    The graphic mode in InfoMaker only allows for simple
                    outer joins, for example, you could reproduce this
                    "LEFT OUTER JOIN crvw ON proj.project_name =
                    crvw.project_name" graphically by joining these
                    columns, then clicking the "=" between them and
                    choosing the correct option. However, anything beyond
                    the basics is not handled graphically.