AnsweredAssumed Answered

report generator help

Question asked by Greg Rupp on Jun 15, 2016
Latest reply on Apr 11, 2017 by Aaron Larson

Like • Show 0 Likes0
Comment • 4

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

]

No one else has this question

Outcomes

4 Replies

Joy Garon Jun 15, 2016 1:13 PM
Mark Correct
Correct Answer
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
Like • Show 1 Like1
Actions
- Aaron Larson @ Joy Garon on Mar 15, 2017 10:10 AM
  Mark Correct
  Correct Answer
  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?
  Like • Show 0 Likes0
  Actions
  - Joy Garon @ Aaron Larson on Apr 11, 2017 11:35 AM
    Mark Correct
    Correct Answer
    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
    Like • Show 0 Likes0
    Actions
    - Aaron Larson @ Joy Garon on Apr 11, 2017 12:33 PM
      Mark Correct
      Correct Answer
      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] ]
      Attachments
      ECN Power Search Cleaned.crp.zip1.1 KB
      Like • Show 0 Likes0
      Actions

report generator help

Outcomes

Attachments

Related Content

Recommended Content