3 Replies Latest reply on Apr 11, 2017 11:51 AM by Joy Garon

    How to use LIKE operator for .crp file

    Naveen Saini

      Hi,, I am a dot net developer and well known to SQL Server but new to EPDM Reports. I am using LIKE operator in query on .crp file.

       

      I am taking an Argument <ProjectName> whic is geting Set in variable @ProjectName containing the String value, but when I'm comparing the @ProjectName with some column using Like, its returning no result, but geting result when run same query in SQL Server.

      Event I have checked the string I'm getting in @ProjectName

       

      I believe syntax near LIKE is not working. Please help me out.

       

       

      @[Project Status]
      §Name [Project Status Generator]

       

      §Arguments
      [
      String    ProjectName[1] [Enter Project Name]
      ]

       

      §Sql
      [          
      Set @ProjectName = {ProjectName}

       

      Select    S.Name, P.ProjectID, D.DocumentID, Filename, P.Path
      From    Projects P, DocumentsInProjects DP, Documents D, Status S
      Where    P.Path LIKE {@ProjectName}
      And        P.ProjectID = DP.ProjectID
      And        D.DocumentID = DP.DocumentID
      And        D.CurrentStatusID = S.StatusID
      Order By S.Name DESC
      ]

        • Re: How to use LIKE operator for .crp file
          Joy Garon

          Hi Naveen,

           

          I misunderstood your post the first time I read it.

          Attached you will find a PDF to help you understand the structure of the EPDM Reports.

           

          Cheers,

          Joy

           

          p.s. remove the set command from the query. The value is set at runtime when you obtain the variable listed in the argument section.

           

          @[Project Status]
          §Name [Project Status Generator]

          §Company [SolidWorks]

          §Description
          [This query lists all documents at the specified status]

          §Version [1.4]


          §Arguments
          [
          String ProjectName[N] [Enter Project Name]
          ]


          §Sql
          [         

          Select S.Name, P.ProjectID, D.DocumentID, D.Filename, P.Path
          From Projects P, DocumentsInProjects DP, Documents D, Status S
            Where P.Path LIKE {ProjectName}
          And  P.ProjectID = DP.ProjectID
          And  D.DocumentID = DP.DocumentID
          And  D.CurrentStatusID = S.StatusID
          Order By S.Name DESC
          ]

            • Re: How to use LIKE operator for .crp file
              Aaron Larson

              This is a fantastic brief tutorial.  I've been wading through CRP for a few weeks now, after getting a hold on the SQL structure itself.  I was hoping it would be possible to bake in a preceding and trailing wildcard into the user input for an extended multi-variable substitution.  The report generator doesn't seem to like it.  It imports okay, but it doesn't run.  I was attempting something like this:

               

              §Arguments

              [

              String   str [N] [Enter the string to find. Enter text or a full or partial part number. You may search multiple strings at a time.

              ]

               

              §Sql

              [

              ..... STUFF.....

                  WHERE  {#B.[Description],'%' + str '%',LIKE,OR}

              ]

               

              It doesn't seem to like the concantenation in the extended substitution.  For the time being I've resorted to indicating in the input box to use the '%' wildcard if desired.

                • Re: How to use LIKE operator for .crp file
                  Joy Garon

                  Okay - NOW I know what you are trying to do.... I think I mislead you before - look at the attached sample for ideas.

                   

                  ---- sample below ----

                  @[ShowRevisionHistoryForAllFiles]

                  §Name [Show the revision history for all files in vault]

                  §Company [SolidWorks]

                  §Description

                  [This report will show the EPDM revision history for all files in the vault.]

                  §Version [1.1]

                  §Arguments

                  [

                  ProjectID argProjectID [1] [Select starting folder. E.g "$\Projects", or browse for folder.]

                  String argFilename [1] [Enter file name pattern to list revision for. You can use % as wildcard, for example : %.slddrw]

                  ]

                  §Sql

                  [

                  --Query to show revision history for all files in the vault

                  -----------

                  DECLARE @FilePath nvarchar(255)

                  DECLARE @FileName nvarchar(255)

                   

                  SET @FilePath = (Select Path from Projects where ProjectID = {argProjectID} )

                  SET @FileName = {argFilename}

                  -----------

                  Declare @Docs Table( DocumentID Int )

                      ----- add all normal files into the @docs table

                    Insert Into

                      @Docs( DocumentID )

                      Select DocumentID

                      from Documents

                      Where ObjectTypeID = 1

                      and Deleted != 1

                      and Filename like @FileName

                         ----- Get revision information for all documents in @docs table  

                    Select

                     p.Path + d.Filename As [File path],

                     convert(nvarchar(2000),ur.Comment) As [Revision],

                        ur.RevNr As [Version],

                        case when u.FullName IS NOT NULL then (u.FullName + ' ('+ u.Username + ')')

                      else u.Username

                     end As [User],

                     DATEADD(minute,DATEDIFF(minute,GETUTCDATE(),GETDATE()),ur.Date) as [Date],

                     convert(nvarchar(2000),ur.UserComment) As [Comment]

                    From

                     UserRevs ur

                     Join @Docs do on ur.DocumentID = do.DocumentID

                     Join Documents d on d.DocumentID = do.DocumentID

                     Join Users u on U.UserID = ur.UserID

                     Join DocumentsInProjects dip on dip.DocumentID = do.DocumentID

                        Join Projects p on dip.ProjectID = p.ProjectID

                      Where

                        p.Path like @FilePath + '%'

                      Order By  

                    [File path],

                    [Version] DESC,

                    ur.UserRevID DESC

                  ]