Full Guide On Creating A Report From A To Z (Version 2)

This guide walks you through all the steps to build a dashboard on Ajelix BI’s version 2 which launched in May. We will cover all the steps from file formatting to sharing a report, here are all the topics:

1. How to format your files before data upload?
2. How to upload data for report creation?
3. How to create a report on Ajelix BI?
4. How to share a report with others?

1. Format files before data upload

Pick the right file formats. Currently, it’s possible to upload Excel, CSV, or connect with SQL database or Google Sheets. Discover all available data sources. You can also paste your data, formatting rules don’t apply. We will add more data sources in the future–so stay tuned.

1. Format your Excel file

Before you venture into Ajelix BI, prepare your Excel data. Here are a few things to keep in mind:

1. You can choose which sheets you want to import into the system, so format all sheets accordingly.

2. All data should start from cell A1. If your data table starts from cell C2 or other cells move it to A1.

3. Your Excel file can have unlimited rows and columns.

4. Make sure your file has one data table in a sheet. Check the example below to avoid blanks in your charts 👇

5. Format cells as “Date” to create a time series chart that summarizes big date data. Here’s how 👇

6. Remove unnecessary formatting (except for the date format)

Remove currency formatting if you want to visualize data as numbers. The system won’t be able to recognize currency as a number.

Remove any other custom format that you have set for the data. As it could cause an error and you won’t be able to upload your file.

7. Format your headers correctly:

• start with a letter (never start your headings with numbers, e.g. “21Data”),
• make sure your headers are formatted as plain text, avoid date formats or any other custom formatting,
• consisting of letters, numbers, and underscores (_) symbols only. Don’t include symbols such as %,$,&,|, etc.

8. Your data table should be vertical and headers should be on row A1.

2. Format your Google Sheets file

Google Sheets files are subject to the same formatting conditions as Excel.

3. Format your CSV file

CSV file import is pretty easy and it doesn’t have as many restrictions as Excel.

1. Similar to Excel your data should start at cell A1 and you should avoid adding blank columns or rows unless you want to see that in charts.

2. Format dates with “Date” formatting to do that follow the instructions above.

You can view the full article on how to format your files here.

2. Upload your data

The next step is to upload data on the Ajelix platform. To do that, follow the steps:

1.  Go to Ajelix Data

2. Click on Upload Dataset

3. Choose the correct data file type from the available list

4. Click on any data source type and you’ll have the data upload/input box. Select the file from your computer storage

5. For Excel and Google Sheets users, the next step includes selecting sheets to import

6. The next screen will provide a selection of the next steps. You can create a report instantly or prepare your data to transform or clean a file. More on this visit guide about data modeling. We advise new users to select “Add Report” and visit the data preparation step later for advanced analytics.

Screenshot from BI platform with the next steps

3. Build your first report with Ajelix

We will walk you through the elements, setup, and how to create your first report. Make sure you’ve done the previous steps listed in this article.

Once you have completed the setup that we covered in the previous guides you should have a blank editor, like this 👇

To understand the functions for each element read the article about available ajelix elements.

1. Understanding editing

Pick the first element you want to see in your report. Once you click on any element it will appear on your report and you will see the editor.

1. Your data – this is where you can see all your files. Click on any file to add them to the chart. This feature enables you to build a dashboard from several data sources.
2. Element setup – in this box you can arrange the Y, and X axes, apply filters, and sort chart data.
3. Customization settings – this toolbar lets you change colors, labels, legends, set number formatting, and customize chart visual appearance.
4. Report preview – here you can arrange, and resize all charts using drag and drop functionality. This is the preview of all elements.

Click on any element to open the element settings. Each element has its element settings where you can:

1. Rearrange and rename X and Y axis, by drag and drop functionality.
2. Sort columns ascending, descending or set your own criteria
3. Set “Top N” value
4. Apply filters to a chart or any data
5. Add explanations or comments

2. Set up the first element

2.1. Add data to your chart

Now let’s set up the first element settings. We picked the column chart element that we will start editing, look at the picture below to track the editing.

To create charts you first have to understand how to add data to your charts.

1. Click on any file icon on the left side, the number 1 visualizes the data source name, your Excel, CSV, or Google Sheets file. The next icon titled “Marketing Data Q1” is the name of the imported sheet.
2. Dimensions are non-numeric column titles. For example, you want to add the “Campgain name” as label on the axis.
3. Aggregations are numeric column titles, by clicking on any column you will calculate these values. For example, you want to understand how many clicks each campaign got. You would pick “campaign” from dimensions and “clicks” from aggregations.
4. Measures are metrics that you created in the data preparation step. For more on creating KPIs and data models visit the data modeling guide.
5. Once you have clicked the columns you want to visualize, click the “Save & Recalculate” button to see updates in your charts.
6. In this field, you can see data that is visualized on the chart. You can change calculations or rearrange by drag and drop functionality. You can also rename the column titles.
7. The sorting field lets you sort ascending or descending by any label.
8. Sorting Custom field gives you the option to set your sorting criteria. For example, you want to rearrange month names in chronic order by separating each label with a comma. Example: Jan, Feb, Mar, etc.
9. Top N lets you sort and visualize only top or bottom values. Select the column, for example, “Clicks” and top 10 or 5. Depends on how much you want to visualize.
10. Data Filters will filter any column from your data source. For example, if you have visualized a “Campaign name” column but want to filter based on a specific “Location” that is not visualized.
11. Visualization Filters will filter the result that is visualized on the chart. For example, if you have added a “Capmaigin name” column, you can enter a specific campaign you don’t want to see in the chart and the filter will remove it.
12. Don’t forget to click “Save & Recalculate” 🎯

2.2. Customize elements

Once you have added dat to your chart, it’s time to customize charts to meet your needs. Follow the numbering in the picture to understand the guides better.

To meet your dashboard needs there are several customization settings you can use. All customization settings for specific element can be found on top toolbar.

1. In the first field, you can rename the chart title. If you don’t need a title, delete all text and the title will disappear from the chart.
2. In the next dropdown, you can change the chart type to any other, such as, pie chart, line chart or other.
3. Select the colors for each category to meet your brand guidelines.
4. Customize label settings. Here you can hide labels, change the font size and set colors.
5. Legend settings. This feature lets you hide or unhide labels.
6. In axis settings you can hide X or Y axis.
7. You can set your number formatting in the next field. Set percentage, currency, or any other formatting you need, we have summarized the most popular data types, however, you can visit the custom number formatting guide to create more customized data types.
8. Resize any element by dragging the right bottom corner.
9. Once you have the charts you can either share a dashboard via link or email or download the chart to insert it in emails or presentations.

Click Save and add the next element. If you enjoy our tool consider subscribing for a monthly or yearly plan (this way you will save 30%) to unlock the premium features.

3. Add another element

Once you’re done with the first element you can click on Add element in the editor and pick the next element.

Choose from the element list and edit the next element following the same guides as described above.

4. Save, Preview, Share

Once your report is ready you can save it, and preview it to see how it looks in full width by pressing on the Preview button at the top right corner. You can also share the report with links, set passwords, or add other users via email.

Important: Always save your report manually as Ajelix BI doesn’t have anautomatic saving feature.

You can follow these steps in a separate article on how to build the first report.

4. How to share a report with others?

Once you’ve upgraded to the Ajelix business plan you have unlocked different sharing options:

1. You can share a report using a link: anyone with a link will be able to view the report
2. Secure your report with a password
3. Add viewers via email: the report will show up on their dashboard and they will receive an email

There is only one way how you can access sharing settings. This section will walk you through all the steps.

You can share the report while in editing mode. So to access these settings, first you have to create a dashboard. Once you’re in the editor, you will see the Share button in the top right corner.

Once you’ve clicked the share button you’ll open a dropdown settings where you can set up different sharing options.

Anyone with a link sharing

The most popular feature is sharing reports via links. Enabling this option will allow anyone with a link to see the report. To set the report from private to public, click on a dropdown, select, and press the “Copy link” button.

You don’t have to save the settings as report settings change automatically. Keep in mind that every report is private by default.

Secure dashboard with a password

For more safety, you can set the password to your reports. Anyone who wants to view a report will have to insert the password.

Screenshot from Ajelix BI with password notification

To set the password insert your password in the input field and click “Save”.

Screenshot from Ajelix BI with password settings

You can remove the password anytime by clicking “Clear” and “Save”.

Share a report via email

If you don’t want to share the report via link you can add report viewers via email. Before viewing the report they will be asked to log in. They can create a free account with the same email address and access the report. They don’t have to be subscribers.

To share a report with the user insert their email, select if you want to send a notification email, and click Share. Once you share it you will see added users list that you can edit anytime.

Didn’t find the answer to your question? Contact us via: support@ajelix.com