1. Home
  2. Docs
  3. Ajelix BI
  4. Get Started
  5. Full Guide On Creating A Report From A To Z (Version 2)

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. Build your first report with Ajelix
  5. 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 Google Sheets. You can also paste your data, formatting rules don’t apply. We will add more data sources in the future–so stay tuned.

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.

start data from cell a1 for successful bi reporting - ajelix guide
Example on how to format Excel file correctly. Credit: Ajelix

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 👇

bad example of data file formatting in excel with several columns
Example on how to format Excel file correctly and exclude several tables. Credit: Ajelix

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

change cells to data format - ajelix bi upload
Excel screenshot on how to format data as date. Credit: Ajelix

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.

Format your Google Sheets file

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

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 Ajelix platform. To do that, follow the steps:

1.  Go to Ajelix Data

2. Click on Upload Dataset

Upload your data file into ajelix system, step 1 screenshot from platform
Screenshot from Ajelix portal datasets page

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

Select from the available data sources list screenshot from Ajelix BI
Screenshot from Ajelix portal data source page

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

Data source upload box screenshot from ajelix bi
Screenshot from Ajelix portal with datasets settings page

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

Import the sheets from excel or google sheets file, screenshot from ajelix bi
Screenshot from Ajelix BI with sheet selection 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.

After sheet import select the next step on ajelix bi platform

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 👇

Dashboard editor screenshot from ajelix bi
Report building dashboard editor from Ajelix BI

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

Step 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.
Dashboard editor settings and layout with steps -screenshot from platform
Report editor view on Ajelix BI

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

Step 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.

Add data to your charts with steps from ajelix bi portal
Ajelix BI element editing – add data to your element screenshot

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 better understand the guides.

Chart customization settings with steps from portal
Ajelix BI editor and report preview view

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.

Step 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.

Add element settings in ajelix bi editor
Ajelix BI editor add element selection

Choose from the element list and edit the next element.

Step 4: Save, Preview, Share

Once you’re report is ready you can save it, preview it to see how it looks in a full width by pressing on the View button at the bottom of editor. You can also share the report with link to others or give editing option by adding other users with email.

Ajelix report editing settings, reset, view, save, share buttons
Ajelix BI editing buttons

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

4. How to share a report with others?

Once you’ve created a report Ajelix BI offers functionality to share it with others. There are different ways how you can access sharing settings. If you’ve upgraded to Ajelix business plan you have unlocked different sharing options:

  1. You can share a report using link: anyone with a link will be able to view the report
  2. Add viewers via email: the report will show up on their dashboard
  3. Add editors using email: they will see the report in their profile

Access the sharing settings

There are several ways how you can share the report, this section will walk you through all the available options.

Option 1: Report sharing through editor

You can share the report while in editing mode. This is a straight-forward process how you can access sharing settings. You will see the Share button under the editor on the left side.

Access sharing settings on ajelix bi editor
Ajelix BI editor dashboard with sharing settings

Once you’ve clicked on the share button you will be forwarded to sharing settings.

Option 2: Report sharing through Report section

Another way to access sharing settings is through Report section, where you can see all the reports you’ve created. Each report will have a Share button that will take you to sharing settings.

Access sharing settings through report section list
Ajelix BI report section with Share button

Once you click on the Share button, again, you’ll be forwarded to sharing settings page.

Set up sharing settings

Once you have accessed sharing settings, let’s walk you through the steps on how to set up sharing settings correctly.

1. Anyone with a link sharing

The most popular feature is sharing report via link. Enabling this option will allow anyone who has a link see the report.

Share report via anyone with a link option on Ajelix BI
Ajelix BI sharing settings dashboard

Any report has a default settings – the report is private. To share the report, simply click on the dropdown and pick Anyone with a link. Now you have a sharable link, that you can even change after some time.

2. Share a report with email

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

Share reports with email address
Ajelix BI dashboard sharing settings via email address

To share the report with specific users, insert email, pick the role and click Share. Once you share it you will see added users list that you can edit anytime. However, the email that you added will see the shared report on his BI dashboard.

3. Add editors via email

All editors have to have a registered business plan. Once you add editors to your report they will be asked to subscribe in order to make corrections. We have a good news, if you want to add more than 2 users to Ajelix BI contact us to get a bulk discount.

Share report with other editors via email
Ajelix BI dashboard share the report with other editors

The sharing process is straight-forward, once you add the email select the Editor role instead of a Viewer.

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