Full Guide On Creating A Report From A To Z

This guide walks you through all the steps to build a dashboard on Ajelix BI’s, we will cover all the steps from file formatting to sharing a report.

1. Format files before data upload

Pick the right file formats. Currently, it’s possible to upload Excel and CSV or connect with an SQL database or Google Sheets. Discover all available data sources here.

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 the 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 them in charts.

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

4. Format Copied Data

To ensure your pasted data is processed correctly, please verify that your copied data meets these criteria:

• Data is in a supported format: Excel, CSV, or JSON
• For Text: Data is formatted in columns and rows
• For Text: Column names are present in the first row
• For Text: You can paste data directly from Excel into the text field
• For JSON: Data is in an array of objects, each representing a row; keys are column names
• Avoid special characters in column names (use only alphanumeric characters A-Z, 0-9, and underscore)

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 these steps:

1.  Sign up on Ajelix. Go to data

2. Click on Add new data

3. Choose the data file you want to add from the list:

• Google Sheets
• Excel
• CSV file
• MySQL
• PostgreSQL
• Microsft SQL
• Pasted data
• Custom API

4. Depending on which data source you want to add, there will be a different setup. You can find more detailed guides on how to upload an Excel file or add a Google Sheets file. Let’s take Excel file upload as an example, the first step will be file upload.

5. Once you have entered the first step details, the next step for files will be information about custom date settings.

If you have dates in your data, then please enter the customer date type you’re using. If your date formatting is standard, like yy/mm/yyyy or mm/dd/yyyy, then, again, you can skip this step.

If you don’t have any date columns in your data source, then you can simply skip this step.

6. The next step will ask you to select the sheets or tables you want to import. Here, you can select all the data you want to see on your dashboard.

Please note that in a free plan, you will be able to add only one sheet to get an unlimited sheet selection upgrade to business.

7. In the final step, you can select:

• Data preparation if you want to calculate KPIs; clean your data with SQL functions
• Add dashboard to start building charts

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.

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 data source and select any column to add it to the chart. This feature enables you to build a dashboard from several data sources.
2. Element setup: In this section, you can arrange the Y and X axes. You can also drag and drop any column here, apply filters, and sort chart data.
3. Report preview: here you can arrange, and resize all charts using drag and drop functionality. This is the preview of all elements.
4. Customization settings: this toolbar lets you change colors, labels, legends, set number formatting, and customize chart visual appearance.

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

1. Rearrange and rename the X and Y axis by drag and drop functionality.
2. Sort columns ascending or 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 step by step.

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 represents all the data sources you have added to the platform. It can be different Excel files, Google Sheets files or even tables from SQL database.
2. Sheet1 indicates the name of the data table; in this case, the data source is Excel and we have opened the first sheet.
3. Dimensions are column titles here, you can see all data you had in the data source ready for visualization.
   • For example, you want to add the “Month” as a label on the axis and “Profit” as a calculation. There are 2 ways to do that: click on both columns and chart will automatically update with this data, or click and drag the values to X and Y fields.
4. Metrics are KPIs and calculations you can create to track ROI or other vital metrics. You can do that in the data preparation step or by clicking on the “Add metric” button. To learn more about creating KPIs and data models visit the data modeling guide.
5. Once you have made changes to the chart, click the “Update” button to see the changes in your charts.
6. In this field, you can see data that is visualized on the chart. You can change calculations or rearrange them by using the drag-and-drop functionality. You can also rename the column titles or change calculations SUM, AVERAGE, etc.
7. The sorting field lets you sort ascending or descending by any label. 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.
8. 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.
9. Data Filters will filter any column from your data source. For example, if you have visualized a “Month” column but want to filter based on a specific “Project” that is not visualized.
10. Visualization Filters will filter the result that is visualized on the chart. For example, if you have added a “Month” column, you can enter a specific month name you don’t want to see in the chart, for example, March, and the filter will remove it.
11. Don’t forget to click “Update” 🎯

2.2. Customize elements

Once you have added data 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 the X or Y axis.
7. You can set your number formatting in the next field. Set the 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 using links, set passwords, or add other users via email.

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 drop-down settings menu 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 a password to your reports. Anyone who wants to view a report will have to insert the password.

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