4 Replies Latest reply on Apr 11, 2017 12:33 PM by Aaron Larson

    report generator help

    Greg Rupp

      I could use some help with creating a report. I have the report setup to work on a single "built-in" file, but I want the code to point to the Selected files area in the report generator. Any help is appreciated as I don't know SQL.

       

      @[RevisionDataOfSelectedFiles]

      §Name [RevisionData of the selected files]

      §Company [SolidWorks]

      §Description

      [This query will list the revision data of the selected files.]

      §Version [1.0]

      §Arguments

      [

      ]

      §Sql

      [

      SELECT     TOP (1) Documents.DocumentID , Documents.Filename, Documents.LatestRevisionNo AS 'LatestVersion', UserRevs.RevNr AS 'RevisionVersion', UserRevs.Comment AS 'Revision'

      FROM         Documents INNER JOIN

                            UserRevs ON Documents.DocumentID = UserRevs.DocumentID

      WHERE     (Documents.Filename LIKE '%9200-004141%')

      ORDER BY UserRevs.RevNr DESC

      ]

        • Re: report generator help
          Joy Garon

          Hi Greg -

          You will want to use an argument - look at the online help :

          2016 SOLIDWORKS PDM Help - Report Query Arguments

           

          If you look at the sample file provided (Report Examples 1.crp)

          Usually found in: C:\Program Files\SOLIDWORKS Corp\SOLIDWORKS PDM

          you can see how arguments are used.

           

          Regards,

          Joy

            • Re: report generator help
              Aaron Larson

              Joy - you are no doubt the person who can answer this.  I have a report configured that allows variable input from the user.  In my query I'd like to prefix and suffix these input strings with '%' wild cards.  The argument is used in an expanded search.  I've tried turning the user input an SQL variable (@SomeVar) and adding the '%' there and using the '@SomeVar' in the expanded variable search.  I have also tried adding '%' + userVar + '%' in the expanded search with no success.  Is it possible to achieve this or am I stuck allowing only a single input and adding wildcards in SQL using simple substitution or allowing mulitple inputs and forcing the user to enter the wildcards and using expanded substitution?

                • Re: report generator help
                  Joy Garon

                  Hi Aaron - Sorry, I did not see this sooner. I'm not quite sure what you are trying to do. Can you send me a crp sample and and explanation? Joy

                    • Re: report generator help
                      Aaron Larson

                      Hi Joy - no issue on the delay.  I appreciate the response!  Attached is the CRP file I am using currently (same code also pasted into response).  It prompts the user to enter desired search values and then looks into the database within certain fields for them.  It should also be mentioned that I am self taught in SQL so redundancies, inefficiencies, or poor coding practices are likely  

                       

                      The report accepts multiple arguments, and stores them into "str".  My original design was to use extended substitution as outlined here, except that I would prefix and postfix the argument with wildcards.  This kept making the query puke so I had to ask the user to add the wildcards into the input.  My previous post lists the coded methods I attempted to add the prefix and postfix.

                       

                      The goal is essentially to make it so the user can enter multiple input values but not have to enter the wildcards in the input box.  Any input (pun intended) is helpful.  Thanks!

                       

                       

                      @[EcnPowerSearch]
                      §Name [ECN Power Search]
                      
                      
                      §Company [Solidworks]
                      
                      
                      §Description
                      [This query will list all active ECNs that contain the user
                      specified text string within the folliwing fields:
                      Suggested Change Description
                      Justification
                      Where Used
                      Affected Part Numbers
                      Actual Changes
                      Cost Analysis]
                      
                      
                      §Version [1.2]
                      
                      
                      §Arguments
                      [
                      String   str [N] [Enter the string to find enclosed by the '%' (e.g. %12345%). Enter text or a full or partial part number. You may search multiple strings at a time.]
                      ]
                      
                      
                      §Sql
                      [
                      
                      
                      -- Get all results from the VariableValues for ECN documents
                      SELECT Vv.DocumentID,D.Filename, ISNULL(Vv.ValueText, '') AS ValueText, V.VariableName
                      INTO #A
                      FROM     VariableValue AS Vv INNER JOIN
                                        Variable AS V ON Vv.VariableID = V.VariableID INNER JOIN
                        Documents AS D ON Vv.DocumentID = D.DocumentID
                         
                      WHERE  D.Filename LIKE 'ECN 1%.doc%'
                        AND D.Deleted = 0
                      
                      
                      
                      
                      --Pivots the previous result based on the values of the variable names
                      SELECT * 
                      INTO #B 
                      FROM #A
                      PIVOT (MAX(ValueText) FOR VariableName IN([ECN Number],[Description],[Where Used],[Cost],[Justification],[Change Details],[AffectedPartNumbers])) as pvt
                      
                      
                      SELECT [ECN Number]
                      INTO #C
                      FROM #A
                      PIVOT (MAX(ValueText) FOR VariableName IN([ECN Number])) as pvt2
                      
                      
                      
                      
                      --Create result that joins the first pivot with the second to show the ECNs & values along with state
                      SELECT #B.[ECN Number],
                        ISNULL(#B.[Description],'') AS [Suggested Change],
                        ISNULL(#B.[Where Used],'') AS [Where Used],
                        ISNULL(#B.Cost,'') AS [Cost Analysis], 
                        ISNULL(#B.[Justification],'') AS [Justification],
                        ISNULL(#B.[Change Details],'') AS [Actual Changes],
                        ISNULL(#B.[AffectedPartNumbers],'') AS [Affected Part Numbers]
                      
                      
                      FROM #B LEFT JOIN
                        #C ON #B.[ECN Number]=#C.[ECN Number]
                      
                      --Only get values where the ECN number is entered and the input is found
                      WHERE #B.[ECN Number]<>'' AND
                        {#B.[Description],str,LIKE,OR} OR
                        {#B.[Where Used],str,LIKE,OR} OR
                        {#B.Cost,str,LIKE,OR} OR
                        {#B.[Justification],str,LIKE,OR} OR
                        {#B.[Change Details],str,LIKE,OR} OR
                        {#B.[AffectedPartNumbers],str,LIKE,OR}
                      
                      ORDER BY #B.[ECN Number]
                      ]