7 Replies Latest reply on Feb 12, 2014 11:22 AM by Joy Garon

    Writing a Report that pulls Project Number and Description

    Andrew Whiting

      Hello,

       

      I need to run a report that pulls all the Project Numbers and their Description out of our vault. I have not been able to write a query that has actually worked since I'm so new with this stuff. I've copied the code I've written in below.

       

      @[ListAllProjects]

      §Name [All currently defined Projects]

       

      §Company [SolidWorks]

       

      §Description

      [This query will list all Project numbers and their description.]

       

      §Version [1.1]

       

      §Arguments

      [

      ]

       

      §Sql

      [

      SELECT P.Name as 'Project Name', W.Description as 'Project Description', P.Name as 'Project Number'

      FROM Projects P, Projects P

      WHERE W.InitialTransitionID = T.TransitionID And

            W.WorkflowID != 1                                  /* Only include real WF  */

      ]

       

       

      /*******************                        ********************/

       

      Can anyone help me out?

       

      Thanks, Andrew

        • Re: Writing a Report that pulls Project Number and Description
          Charley Saint

          Andrew,

           

          Try this, you may need to adjust the description or the number to match your variable names:

           

          SELECT P.Name as 'Project Name', vv1.ValueText as 'Project Description', vv2.ValueText as 'Project Number'

          FROM Projects P

          left outer join (select vv1.* from VariableValue vv1

          inner join Variable v1 on v1.VariableID = vv1.VariableID and v1.VariableName = 'Description') vv1 on vv1.ProjectID = p.ProjectID

          left outer join (select vv2.* from VariableValue vv2

          inner join Variable v2 on v2.VariableID = vv2.VariableID and v2.VariableName = 'Number') vv2 on vv2.ProjectID = p.ProjectID

          where p.ProjectID <> 2

            • Re: Writing a Report that pulls Project Number and Description
              Andrew Whiting

              Charley,

               

              Thanks for the reply. So when I typed that out, and ran it, its looking like what I need. The issue is, its not pulling the description. Also, Its showing all the projects and the subfolders within the project. I just want it to pull the project number...the name of the root folder of each project...not the subfolders. I've attached the source code, and a screen shot of what the report looks like. Thanks again!

               

              Andrew.

               

              @[ListAllWorkflows]

              §Name [All currently defined Workflows]

               

              §Company [SolidWorks]

               

              §Description

              [This query will list all Workflows and the name of the first tranistion in each.]

               

              §Version [1.1]

               

              §Arguments

              [

              ]

               

              §Sql

              [

              SELECT P.Name as 'Project Name', vv1.ValueText as 'Project Description', vv2.ValueText as 'Project Number'

              FROM Projects P

              left outer join (select vv1.* from VariableValue vv1 inner join Variable v1 on v1.VariableID = vv1.VariableID and v1.VariableName = 'Description') vv1 on vv1.ProjectID = p.ProjectID

              left outer join (select vv2.* from VariableValue vv2 inner join Variable v2 on v2.VariableID = vv2.VariableID and v2.VariableName = 'Number') vv2 on vv2.ProjectID = p.ProjectID

              WHERE p.ProjectID <> 2                            

              ]

               

              Here is what it looks like after its generated.

              report.PNG

              I only need project number or the "Name" of each. We have each project numbered, or in other words, each root folder is a number for each project.

               

              Thanks