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.
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.
Thanks, I got it working now. Table Variables are very useful.
Cory, what Lee said.
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.
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'
FROM Status AS S
Documents AS D ON S.StatusID = D.CurrentStatusID
Workflows AS WF ON S.WorkflowID = WF.WorkflowID
TransitionHistory As TH ON D.DocumentID = TH.DocumentID
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 )
(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]
Thanks for sharing! I took the same approach that the examples showed, using variable tables. I like your method much better.
Nice query. Where does the [State Sort] field come from?
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.
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.