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:
- How to format your files before data upload?
- How to upload data for report creation?
- How to create a report on Ajelix BI?
- Build your first report with Ajelix
- 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.
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.
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
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.
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.
- 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.
- Element setup – in this box you can arrange the Y, and X axes, apply filters, and sort chart data.
- Customization settings – this toolbar lets you change colors, labels, legends, set number formatting, and customize chart visual appearance.
- 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:
- Rearrange and rename X and Y axis, by drag and drop functionality.
- Sort columns ascending, descending or set your own criteria
- Set “Top N” value
- Apply filters to a chart or any data
- 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.
To create charts you first have to understand how to add data to your charts.
- 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.
- Dimensions are non-numeric column titles. For example, you want to add the “Campgain name” as label on the axis.
- 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.
- Measures are metrics that you created in the data preparation step. For more on creating KPIs and data models visit the data modeling guide.
- Once you have clicked the columns you want to visualize, click the “Save & Recalculate” button to see updates in your charts.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
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.
- 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.
- In the next dropdown, you can change the chart type to any other, such as, pie chart, line chart or other.
- Select the colors for each category to meet your brand guidelines.
- Customize label settings. Here you can hide labels, change the font size and set colors.
- Legend settings. This feature lets you hide or unhide labels.
- In axis settings you can hide X or Y axis.
- 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.
- Resize any element by dragging the right bottom corner.
- 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.
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.
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:
- You can share a report using link: anyone with a link will be able to view the report
- Add viewers via email: the report will show up on their dashboard
- 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.
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.
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.
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.
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.
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