9 Replies Latest reply on Dec 16, 2014 1:09 PM by Brent Lewis

    How do you display multiple variables in a report

    Cory Engdahl

      I am attempting to write a SQL query to generate a report pertaining to a form we circulate with workflow.

       

      The form is an Excel document and has many variables liked to it an ideal report header would look something like this:

       

      File Name   Created By  Quote Number  Customer Name Status 

       

       

      I am wondering how I take columns from the Variable.VariableName table in the database, and turn them into tables containing ValueText.  I can successfully do this with one variable; however, in order to do this with multiple variables, it seems I would have to create a second instance of table in the query.

       

      For example, this results in a list of only one variable, where I want several:

       

      SELECT     D.Filename

                       ,Vv.ValueText AS 'name of variable'               --Lists text values of variable

      FROM        Documents AS D

                       ,VariableValue AS Vv

                       ,Variable AS V

       

      WHERE     DocumentId = Vv.DocumentId

                       AND V.VariableId = Vv.VariableId

                       AND V.VariableName = 'variable name'          --Limits query to desired variable

                       AND D.Filename LIKE '%name of form%'

       

      Thanks

       

      -Cory

        • Re: How do you display multiple variables in a report
          Michael Dekoning

          A SQL guru might be able to tell you how to do this. For myself the best option would be to create a Search with a Result column set containing the desired variables and use the Export Result button to a CSV file that you could open in Excel. The other option would be to use the API to run a search and generate the Excel file.

          • Re: How do you display multiple variables in a report
            Lee CS Young

            Have a look at the Report Examples crp file found in C:\Program Files\SolidWorks Enterprise PDM. There is an example there that has what you're looking for.

            • Re: How do you display multiple variables in a report
              Brent Lewis

              I had a time with this myself, I am still learning the structure of EPDM. I will share the approach I took when working on a report.

              Notice the Outer Apply. I will use as a template in other reports. I just have to identify my Variable ID and then give it any name I choose.

              I hope this helps someone else in the future. The variable table is a tower table and they can be tough to crack sometimes.

              SELECT       

              S.Name AS 'Current State'

              ,D.DocumentID AS Document_Num

              ,WF.Name AS WorkFlow_Name

              ,TH.TransitionNr As TranNR

              ,CONVERT(VARCHAR,CAST(TH.Date AS DATETIME), 101) As 'DateCreated'

              ,D.Filename AS File_Name

               

                                   ---Need to Order States so looks in line with work flow

                            ,Case When S.Name = 'NPD Request Created' Then '01-NPD Request Created'

                                     When S.Name = 'NPD Request Submitted' Then '02-NPD Request Submitted'

                                     When S.Name = 'Awaiting Frame Design Review' Then '03-Awaiting Frame Design Review'

                                     When S.Name = 'NPD Frame Design Sample One' Then '04-NPD Frame Design Sample One'

                                     When S.Name = 'NPD UPH Design Sample One' Then '05-NPD UPH Design Sample One'

                                     When S.Name = 'Next Sample Frame Design' Then '06-Next Sample Frame Design'

                                     When S.Name = 'Next Sample UPH Design' Then '07-Next Sample UPH Design'

                                     When S.Name = 'Final Frame Costing' Then '08-Final Frame Costing'

                                     When S.Name = 'Final UPH Costing' Then '09-Final UPH Costing'

                                     When S.Name = 'Group ready For Trial Run' Then '10-Group ready For Trial Run'

                                     When S.Name = 'Group Ready For Production' Then '11-Group Ready For Production'

                                                                                  Else S.Name End As 'State Sort'

               

              ,tba.[Project Manager]

              ,tba.[Style Name]

              ,tba.[FrameType]

              ,tba.[ProductLine]

              ,tba.[Project Type]

              ,tba.[Market Year]

              ,tba.[Market Period]

              ,tba.[NPDSource]

               

               

               

              FROM    Status AS S

              INNER JOIN

              Documents AS D ON S.StatusID = D.CurrentStatusID

              INNER JOIN

              Workflows AS WF ON S.WorkflowID = WF.WorkflowID

              INNER JOIN

              TransitionHistory As TH ON D.DocumentID = TH.DocumentID

               

                                      

                    Outer Apply

                    (Select

                  

                            Max (Case When VV.VariableID = '84' Then VV.ValueText End) as [Project Manager]

                            ,Max (Case When VV.VariableID = '95' Then VV.ValueText End) as [Style Name]

                            ,Max (Case When VV.VariableID = '79' Then VV.ValueText End) as [FrameType]

                            ,Max (Case When VV.VariableID = '82' Then VV.ValueText End) as [ProductLine]

                            ,Max (Case When VV.VariableID = '117' Then VV.ValueText End) as [Project Type]

                            ,Max (Case When VV.VariableID = '118' Then VV.ValueText End) as [Market Year]

                            ,Max (Case When VV.VariableID = '119' Then VV.ValueText End) as [Market Period]

                            ,Max (Case When VV.VariableID = '121' Then VV.ValueText End) as [NPDSource]

                           

                            From VariableValue As VV

                            Join Variable AS VA ON VV.VariableID = VA.VariableID

                            Where D.DocumentID = VV.DocumentID

               

                             And         Vv.RevisionNo = ( Select Max( Vv1.RevisionNo )
                                        From VariableValue Vv1
                                        Where Vv1.DocumentID = D.DocumentID And
                                              Vv1.VariableID = Vv.VariableID And
                                              Vv1.ValueText is not null )

               

               

                                                      ) tba

                           

                                  

              WHERE       

              (WF.WorkflowID IN (N'24','N'25') AND (D.Deleted = 'False')AND TH.TransitionNr ='1' AND (D.DocumentID NOT IN ('107784'))

               

               

               

              ORDER BY [State Sort]

                • Re: How do you display multiple variables in a report
                  Cory Engdahl

                  Thanks for sharing!  I took the same approach that the examples showed, using variable tables.  I like your method much better.

                  • Re: How do you display multiple variables in a report
                    Tim Webb

                    Brent,

                     

                    Nice query. Where does the [State Sort] field come from?

                     

                    Tim

                      • Re: How do you display multiple variables in a report
                        Brent Lewis

                        Tim

                        I am creating that field with a case statement, In the outer apply. It doesn't need to be there though, that's me being speedy.

                        I will move out next time I am in that sp. The better place for that Case statement is before the first From.

                        It worked so I didn't notice the fine detail of its placement. << Got my From's mixed up......

                        I use them a lot when the data itself does not come out in a meaningful manner.

                         

                        Example, Data gets coded a 40899. I know what that is but the manufacturing floor might not. So I would case it.

                         

                        Case When ( Table.Field ) = '40899' Then '4x8 5 PLY CD'

                         

                        Else Table.Field  End As 'Material Description'

                         

                        They don't know what 40899 is but they know what a 4x8 5ply grade is.

                         

                        I did the State sort because this  was created for a Chart for BIDS. When I just used the ( names of the states ) they scrambled the Chart order from A to Z.

                        So I wanted to add 01-02-03 Order so beginning to end would go left to right and look more like the flow its traveling.

                    • Re: How do you display multiple variables in a report
                      Brent Lewis

                      Ran into an issue with variable revisions on something else, thought I would update the code in the outer apply.

                      Its more or less what is needed from the (report example.crp) to get the latest one if your variables do have a chance to change.