Converting pdmlink.WTChangeOrder2.formattedTextA8 for SSRS
- May 27, 2021
- 2 replies
- 3005 views
I want to create a SSRS report that any user (Licensed or not) can run completely outside of the Windchill environment. I was able to get the SQL I needed generated from the Windchill report builder. It runs without issue in SSMS. The only issue I have is that the Descriptions being entered in the change orders is now Rich Text Format (RTF) rather than plain text. This appears to be stored in the following Table: pdmlink.WTChangeOrder2 in the following Field:formattedTextA8. In SQL this is an example of what I see stored in formattedTextA8 which is VARCHAR(8000) DATA TYPE:rO0ABXQAPjxwPkluaXRpYWwgUmVsZWFzZSBTcGFyZXMgRHJhd2luZ3MgZm9yIEludGVybmF0aW9uYWwgU2FsZXM8L3A+.
Viewed in Windchill you see this: "Initial Release Spares Drawings for International Sales". This is the query.
USE pdmlink
SELECT
A1B.name AS Name,
--A1B.idA2A2,
--A0.completedBy,
USERS.fullName,
--A0.idA3A2ownership,
A1.description AS Description,
A1.formattedTextA8 AS Description,
--A0.description,
--A1.statestate as State,
--A0.classnameA2A2 as ClassName,
--A0.idA2A2 as ID,
A1B.createStampA2,
A0.createStampA2,
A0.modifyStampA2,
A0.updateStampA2,
CONVERT(varchar,A1.needDate,120) AS NeedDate,
CONVERT(bigint,(DATEDIFF(ss,A1.createStampA2,GETDATE()))/(86400.0)) as DaysOpen,
CONVERT(bigint,(DATEDIFF(ss,A0.updateStampA2,GETDATE()))/(86400.0)) as DaysFromUpdate
FROM
pdmlink.WorkItem A0, pdmlink.WTUser USERS, pdmlink.WTChangeOrder2 A1
INNER JOIN pdmlink.WTChangeOrder2Master A1B ON (A1.idA3masterReference = A1B.idA2A2)
--LEFT JOIN pdmlink.WTUser USERS ON (A0.idA3A2ownership = USERS.idA2A2)
WHERE (NOT ((A0.status ='COMPLETED'))) AND
(A0.classnamekeyB4 = CASE A1.latestiterationInfo WHEN 0 THEN CONCAT('OR:',A1.classnameA2A2,':',CONVERT(varchar,A1.idA2A2))
WHEN 1 THEN CONCAT('VR:',A1.classnameA2A2,':',CONVERT(varchar,A1.branchIditerationInfo)) END )
I was hoping someone knew how to convert it to something meaningful in SQL. I did some looking and it does not appear to be stored in an RTF format. I would expect to see HTLM tags and such. To me it looks like its hashed/encrypted. I also attached the results from the SQL query

