I was asked where in 3E the Crystal Report rpt files were located. As far as I know, they are not stored in the filesystem (FUNC) but are stored base64 encoded in a table. This notebook will show how to extract from the database and saved as file locally.
For the purposes of this Notebook we'll store the credentials in a credentials.py
file in the same folder as this one. It'll look like:
elite_db_server = 'dbserver.at.yourdomain' elite_db = 'te_3e_yourinstance' elite_user = 'sql_user_name_you_can_use' elite_pass = 'password_for_that_user'
Not the most elequent but will get the job done for this example. See [Interacting with 3E data using SQL](Interacting%20with%20SQL.ipynb) for a way to store credentials in the environment, although arguably not a much better medhod for holding the credentials. Be careful with putting the credentials in a .py file as they may get inadvertantly included in a git repo and land in a public repository somewhere.
import base64
import pyodbc
from IPython.display import HTML
import credentials
elite_db_server = credentials.elite_db_server
elite_db = credentials.elite_db
elite_user = credentials.elite_user
elite_pass = credentials.elite_pass
# connection string from a Windows machine
conn_str = (f'DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={elite_db_server};'
f'PORT=1433;DATABASE={elite_db};UID={elite_user};'
f'PWD={elite_pass};TDS_Version=8.0;ClientCharset=UTF-8')
conn_3e = pyodbc.connect(conn_str)
cursor = conn_3e.cursor()
# sql to get the list of templates of a particular set of types
# for this demo, we only want the Crystal Reports
templates_sql = """
select
NxPrinterTemplate.DCSTemplateId,
NxPrinterTemplate.TemplateCategory,
NxPrinterTemplate.Name,
NxPrinterTemplate.Description,
NxPrinterTemplateType.Name 'Type',
max(NxPrinterTemplateVersion.VersionNumber) 'LatestVersion',
cast(max(NxPrinterTemplateVersion.CreateDate) as date) 'LastChange'
from NxPrinterTemplate
join NxPrinterTemplateType on NxPrinterTemplateType.TypeId = NxPrinterTemplate.TemplateType
join NxPrinterTemplateVersion on NxPrinterTemplateVersion.Template = NxPrinterTemplate.NxPrinterTemplateID
where NxPrinterTemplate.IsActive = 1
/* Types: 1-Word Designer; 2-Crystal Reports; 3-Design Gallery; 4-Design Gallery 3.0 */
and NxPrinterTemplate.TemplateType in (2)
group by
NxPrinterTemplate.DCSTemplateId,
NxPrinterTemplate.TemplateCategory,
NxPrinterTemplate.Name,
NxPrinterTemplate.Description,
NxPrinterTemplateType.Name
order by NxPrinterTemplate.TemplateCategory, NxPrinterTemplateType.Name
"""
templates_results = cursor.execute(templates_sql)
templates = [{'template_id': template.DCSTemplateId,
'template_category': template.TemplateCategory,
'name': template.Name,
'description': template.Description,
'type': template.Type,
'latest_version': template.LatestVersion,
'last_change': template.LastChange} for template in templates_results]
# let's take a look at the first few
# pretty ugly, but we'll format as an html table for presentation in this notebook
templates[:3]
def list_as_html(template_list):
# function purposely written to convert
# the templates to a pretty list for this notebook
# completely unecessary
html = list()
html.append("<table>")
# header
html.append("<tr><th>")
html.append('</th><th>'.join(template_list[0].keys()))
html.append("</th></tr>")
# body
for t in template_list:
html.append("<tr><td>")
html.append('</td><td>'.join([str(v) for k,v in t.items()]))
html.append("</td></tr>")
# end
html.append("</table>")
return HTML(''.join(html))
list_as_html(templates)
So we should see a list of templates above. By template_id
pick the one wanted as an .rpt file. You are able to pick any version, but picking the latest is probably the right choice. Take a look at NxPrinterTemplateVersion
if you want to see other available versions (exercise left up to the reader).
# picked from the table above
extract_template = 311
extract_version = 20
b64template_sql = """
select top 1
NxPrinterTemplate.Name,
NxPrinterTemplateVersion.FileData
from NxPrinterTemplate
join NxPrinterTemplateVersion on NxPrinterTemplate.NxPrinterTemplateID = NxPrinterTemplateVersion.Template
where NxPrinterTemplate.DCSTemplateId = ?
and NxPrinterTemplateVersion.VersionNumber = ?
"""
parameters = (extract_template, extract_version)
get_b64template = cursor.execute(b64template_sql, parameters)
b64template = cursor.fetchone()
# we create a file named with the name from the 'name' column plus .rpt
# which will be saved in the same directory as this notebook
if b64template:
with open(f"{b64template.Name.replace(' ', '')}.rpt", "wb") as rpt_file:
rpt_file.write(base64.decodebytes(b64template.FileData.encode('utf-8')))
else:
print("Template not found")