Building an automated reporting solution with Datylon Report Server

Written by Peter Coppens - Founder & CTO | September 04, 2023

Closing the gap between engaging report designs and automated delivery of personalized information is not trivial. Given the right tools and a structured approach, things do become manageable. The benefits of such efforts are substantial. Getting the right information delivered at the right time packaged as a captivating data story, makes all the difference between actionable knowledge and data lost in the information overload we all deal with on a daily basis.

In what follows Datylon Report Studio and Datylon Report Server are used to set up a simple yet ‘realistic’ and fully functional report automation scenario. The report was designed using Datylon for Illustrator and uploaded as templates to Datylon Report Studio. The look & feel of the templates can be changed at any time, without needing to edit anything in the scripts. The creative process, while crucial for an impactful end-to-end report delivery flow, is not further discussed here.

The complete script is available in a public bitbucket repo.
The README information stored in the repository should give you enough information to run the report renderings and play around with it.

1. Report data

2. The report

3. Automating report rendering

4. Automating report rendering - code snippets

Configure the script
Reading the data
Filtering the data
Converting the data to JSON
Invoking the render server
Concatenating the pages

5. Conclusion

6. Further reading

Report data

The (fictive) example data from which the report will be created summarizes survey responses covering certain traits and skills of employees. It is stored in an Excel workbook with four sheets:

Personality: per employee survey results on the personality traits
Communication: per employee survey results with respect to an employee’s communication style
Skill: per employee communication skills
Benchmarks: benchmark metrics to compare the employee’s score with

The report data is available here.

The report

The three-page report was created using Datylon for Illustrator and exported to Datylon Report Studio after which each of the pages was downloaded as SVG. These are available at the following URLs:

Page 1 summarizes responses to ‘personality’ questions
Page 2 summarizes responses to ‘communication style’ questions
Page 3 summarizes responses to ‘interpersonal skill’ questions

You can also download the Adobe Illustrator file containing Datylon charts. If you choose to do so, make sure you also register, download, and install the Datylon for Illustrator plugin (a 14-day free trial is available here).

Automating report rendering

The demo database contains information about 20 different survey respondents. To automate the creation of a three-page report summarizing each of the respondent's data, a Python script was created. The following steps were implemented

The Excel workbook is read using the Python Pandas library.
The data is converted into the JSON structures expected by the different Datylon designs. To find out the structure to use, open the design in Datylon Report Studio and download the ‘attached data’ as JSON.
For each of the three pages, and for each of the respondents, the Datylon render server is invoked using Python’s request package in order to render the Datylon design as a PDF file with the respondent’s specific data.
For each of the respondents, the three PDF files are merged into one document using the pypdf2 library.

The final reports (PDF files) are stored on the local disc but can easily be transferred or distributed using any delivery channel that supports PDF documents.

Automating report rendering - code snippets

The complete Python script is available from the Datylon public bitbucket repository. A few representative snippets are provided below.

Configure the script

(src/config.py)

datylon_url = "https://insights.datylon.com/datyrender/api/render"
# Get Datylon user from environment variable.
# IMPORTANT: User has to be registered with a password. Google signin is not supported for this app
datylon_username = os.getenv("DATYLON_USER")
# Get Datylon password from environment
datylon_password = os.getenv("DATYLON_PASSWORD")
source_path = Path(__file__).resolve()
source_dir = source_path.parent
data_file = f"{source_dir}/../data/survey.xlsx"
# The UUID's of the three Datylon designs stored at 'datylon_url'
document_pages = ['', '', '']

Note that the Datylon Report Studio user and password are taken from environment variables and should be set before attempting to run the script.

Reading the data

(src/gen_reports.py)

def read_data(file_path):
data = {}
data['personality'] = pd.read_excel(file_path, sheet_name='Personality')
data['communication'] = pd.read_excel(file_path, sheet_name='Communication')
data['skill'] = pd.read_excel(file_path, sheet_name='Skill')
data['benchmarks'] = pd.read_excel(file_path, sheet_name='Benchmarks')
return data

Filtering the data

Each of the reports should only contain the data for one survey respondent. As such the data has to be filtered using the respondent’s name for which the report is being created.

(src/data_binding.py)

...
def p1(personality, benchmarks, respondent,json):
# Personality properties to extract from workbook sheet (matches column header)
p1_dot_plot_props = [ ... ]
p1_dot_plot = {
"p1-dot-plot": [
["Personality Trait", "color", respondent],
]
}
# Filter data for 'current' respondent (name of person)
data = personality[personality['Person'] == respondent]
...

Converting the data to JSON

The biggest part of the code deals with transforming the data from its input structure (the workbook sheets) to the data the designs expect when rendering with the Datylon Render Server.

For each Datylon workbook sheet, a JSON object is created. The code creates these objects ‘sheet’ per ‘sheet’ and then concatenates the results.

Below is a code snippet to illustrate the process.

(src/data_binding.py)

def p1(personality, benchmarks, respondent,json):
# Personality properties to extract from workbook sheet (matches column header)
p1_dot_plot_props = [ "Assertive", "Resilient", "Adaptable",
"Empathetic", "Creative", "Proactive", "Analytical", "Meticulous",
"Emotionally intelligent", "Reliable", "Organized", "Diplomatic",
"Inspirational", "Ethical", "Inclusive" ]
p1_dot_plot = {
"p1-dot-plot": [
["Personality Trait", "color", respondent],
]
}
# Filter data for 'current' respondent (name of person)
data = personality[personality['Person'] == respondent]
# Fill in personality scores for current respondent in json structure
for prop in p1_dot_plot_props:
score_value = filter_data(data,respondent)[prop].values[0]
score_obj = [prop, 'score', str(score_value)]
p1_dot_plot["p1-dot-plot"].append(score_obj)

# Fill in benchmark values in json structure
for prop in p1_dot_plot_props:
bm_value = benchmarks.loc[(benchmarks['Group'] == 'Personality')
& (benchmarks['Characteristic'] == prop)]["Benchmark"].values[0]
bm_obj = [prop, 'BM', bm_value]
p1_dot_plot["p1-dot-plot"].append([prop, "BM", str(bm_value)])

return {**json, **p1_dot_plot}

After all JSON objects are created, they are merged into one JSON object.

def build_json(data, respondent):
json_dict = {}
json_dict = p1(data['personality'],data['benchmarks'],respondent, json_dict)
json_dict = p2(data['communication'],respondent, json_dict)
json_dict = p3(data['skill'],data['benchmarks'],respondent, json_dict)
json_dict = pText(data['communication'],data['skill'],respondent, json_dict)
json_data = {
"main template datasheet container" : { **json_dict}
}
return json_data

Invoking the render server

Once the JSON has been constructed using data extracted from the excel workbook, the Datylon Render server can be invoked, one invocation for each of the three pages of the design and for each of the respondents’ survey data.

A single invocation looks like this:

auth_string = f"{username}:{password}"
encoded_auth_string = base64.b64encode(auth_string.encode()).decode()
headers = {}
headers["Authorization"] = f"Basic {encoded_auth_string}"
headers["Content-Type"] = "application/json"
headers["Accept"] = "application/pdf"
headers["x-graph-template-id"] = page

response = requests.post(url, data=json_data, headers=headers)
if response.status_code == 200:
file_name = f"page_{i}.pdf"
with open(file_name, "wb") as pdf_file:
pdf_file.write(response.content)
else:
print(f"Request failed with status code: {response.status_code}")
print(response.text)

The constructed JSON is used as the POST request payload (a complete example of such JSON can be found here. The UUID of the Datylon design is passed in as the x-graph-template-id header.

Concatenating the pages

Finally, once the three pages of the design for a given survey respondent have been rendered, they can easily be concatenated to create one final PDF report. The file is named using the respondent name (selected_person_name.pdf).

pdfs = PyPDF2.PdfMerger()
for i,page in enumerate(pages):
with open(f"page_{i}.pdf", "rb") as file:
pdfs.append(file)
output_file = f"{selected_person_name}.pdf"

The complete script is available in a public bitbucket repo. The README information stored in the repository should give you enough information to run the report renderings and play around with it.

Conclusion

This paper illustrates a simple approach to setting up a report automation system based on the Datylon platform. The code fragments are designed to be easily adaptable and can serve as inspiration for the reader to create context-specific workflows.

By synergizing the creative capabilities of Adobe Illustrator with the power of the Datylon Illustrator Plugin and the developer-friendly Datylon Render Server API, a versatile Report Automation workflow emerges, requiring minimal development efforts.