Skip to main content
1-Visitor
May 27, 2021
Question

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

 

2 replies

14-Alexandrite
June 7, 2021

Hi,

 

I've tried Base64 decoding on the string:

rO0ABXQAPjxwPkluaXRpYWwgUmVsZWFzZSBTcGFyZXMgRHJhd2luZ3MgZm9yIEludGVybmF0aW9uYWwgU2FsZXM8L3A+

and it decodes into: 

�t><p>Initial Release Spares Drawings for International Sales</p>

 

It looks like the original Description has something added in front of the text, so I'm not sure if it can be reliably decoded.

 

You may try the native SQL Server Base64 decoding and see how it goes. Example: https://dba.stackexchange.com/questions/191273/decode-base64-string-natively-in-sql-server

 

Kind regards,

Dmitry.

 

1-Visitor
June 8, 2021

Ohhh so close!  I swapped out my column/field name for the one they called "BASE64_COLUMN" and I get the following for every record ¬í .  I wonder if I need to CAST  as a string....maybe it doesn't like the way its coming string from SQL?

1-Visitor
June 8, 2021

Well this is what ended up working

 

binaryDescription=
REPLACE(
REPLACE(
REPLACE(SUBSTRING(CONVERT(VARCHAR(800),cast('' as xml).value(
'xs:base64Binary(sql:column("A1.formattedTextA8"))',
'varbinary(max)')),8,8000),'<p>',''),'</p>',CHAR(13)+CHAR(10)),'<br/>',CHAR(13)+CHAR(10)),