Create charts using imported excel file

Hello everybody,

i am an OutSystems newbie.

I'd like to build a web app which is based on an excel file.


I've imported this file and got 3 entities now. For each entity i built 2 screens (1 with a table record and 1 detail) by drag and drop. Now i have the screens for my 3 business units (BU like in the excel file) So far so good that wasn't difficult.

I'd like to build a dashboard screen for each business unit containing a column chart and a pie chart too. Here's my problem. I don't know how to do that...

In the column chart i want to show the cost centers (KST) and the staff (Mitarbeiter) belonging to each cost center.

In the pie chart i want to show the staff (Mitarbeiter) which end of working ends within 1 Month.


Would be nice if anybody could help me!!!

Greetings from Germany


Best regards


Tobias             

Not knowing exactly what your data model looks like or what charts you're looking for, the gist of it is that you'll need to query your data in order to obtain the results you're looking for and then feed those query results to Charts widgets that you have on your screen. There's a very good walkthrough of chart types in the Documentation:

https://success.outsystems.com/Documentation/11/Reference/OutSystems_APIs/Charts_API/Create_Pie_and_Donut_Charts

https://success.outsystems.com/Documentation/11/Reference/OutSystems_APIs/Charts_API/Create_Column_and_Bar_Charts

The screenshots are mobile development, but the process is similar for web applications.

Hey Afonso,

thank you for your reply.

Does it mean that i need to build a data model inside the app?

Like i said, i've just imported my excel file which gave me the 3 entities with all of the attributes like in the excel file. Here's a screenshot of my excel and the data in the app: 

At the bottom of the excel you can see the three business units...

Data in the app:

DataModel is still empty:

Did you mean this data model?


I'm sorry for any dumb questions. Like i said i'm an absolute beginner with basic experience in programming or modeling data.

Kind regards 

Tobias


Hi Tobias,

There are no dumb questions!

What I meant with a data model was the collection of your Entities (that you have already created) and the data within (that you'll still need to import since your Entities are empty).

Your first step will be to decide how to get your data into the application. Since you already have a set of structured Excels, you could consider the platform's generated bootstrap:

https://success.outsystems.com/Documentation/11/Developing_an_Application/Use_Data/Bootstrap_an_Entity_Using_an_Excel_File

You could also create a couple of pages to list/edit the data that you've bootstrapped, to make your life easier if you require any changes.

Once you have the data in place, you'll have to query it with an Aggregate or an Advanced Query. With the results of your query, you can then feed a Charts widget and display them on your dashboard screen.

Tobias Ihlein wrote:

Hey Afonso,

thank you for your reply.

Does it mean that i need to build a data model inside the app?

Like i said, i've just imported my excel file which gave me the 3 entities with all of the attributes like in the excel file. Here's a screenshot of my excel and the data in the app: 

At the bottom of the excel you can see the three business units...

Data in the app:

DataModel is still empty:

Did you mean this data model?


I'm sorry for any dumb questions. Like i said i'm an absolute beginner with basic experience in programming or modeling data.

Kind regards 

Tobias


Hello Tobias,

the screenshot of that you showed above is just the entity diagram, which is used to visually show the relationships between the data tables.

Since you can already see the data on your screens, your next step would be:

1.Create an aggregate of the data table that you want to show the chart for.

2.Set the "SourceDataPointList" to use the aggregate that was previously created.

3.Set the "Label" and "Value" fields on the chart property.


Hope this helps! 

Hello Afonso and Ian.

I thank you very much for your replies. Sorry for my late answer but we had a legal holiday yesterday.

I tried a little bit getting my requiered charts but i still have some questions...

The column chart:

I created the aggregate, grouped by KST (short for Kostenstelle, means cost center) and counted by Mitarbeiter (means employees).

 

I set the source data point list to GetColumnData, labeled "Kostenstelle" and valued the count.

 

The result looks like this:

But how can i set different notations for the Kostenstelle-columns like Kostenstelle 123 for column 1, Kostenstelle 456 and so on. And how do i set different colours for each column?

For example i want it to look like this:

I created this chart with fixed values, see here:

But i want that the data for me to do. Unfortunately i don't know how....



The same with the pie chart. I created the aggregate, grouped by Einsatzende (means end of working) and counted by Mitarbeiter (means employees).

Here i need to filter the employees whose end of working ends in 30, 60 and 90 days. How can i set this filter? I know it's possible to filter in the aggregate but i don't know how to write the appropriate expressions.


I know these are a lot of questions, would be very kind to get help again.


Best regards


Tobias

Hi Tobias,

You've already found your solution: you know you can change those values by changing the Label. All you need to do is change the Label value from a hardcoded value of "Kostenstelle" to the current value in your query (GetColumnDataList.List.Current.KST). 

The colors are a bit more tricky, it's going to depend on what criteria you have for each color. If you're only going to have a couple of columns, you could write If(GetColumnDataList.List.CurrentRowNumber = 0, "red") and then chain these Ifs for different row numbers and  colors, but this won't be very pretty. You could also create a new attribute in your Aggregate (GetColumnDataList) called "color", populate a color there using logic of your choice, and then feed the Color input directly with your new attribute.

Hi Afonso,

thank you for your fast answer!

I changed the value of the lable and got this result:


What else can i do? Have you got any idea?

Meanwhile i'm very desperate about creating charts :(


Best regards Tobias

Can you try to set the label to "Kostenstelle " + GetColumnDataList.List.Current.KST

It's being parsed as a number by the Charts widget, that's why the axis looks weird.

Ok that worked :)

Coloring of the columns worked too.

Have you got an idea how i could filter my aggregate for the pie chart?

Here i need to filter the employees whose end of working ends in 30, 60 and 90 days. How can i set this filter? I know it's possible to filter in the aggregate but i don't know how to write the appropriate expressions.

Thank you in advance

Tobias

I'd write a filter with something like (assuming "Einsatzende" is your end of working date):

AddDays(CurrDate(), 30) = Einsatzende

or

AddDays(CurrDate(), 60) = Einsatzende

or

AddDays(CurrDate(), 90) = Einsatzende

And this would give you the Employee counts, but then you would need to distinguish them for the Chart. I would either create a new attribute in the aggregate to save the data series name, or distinguish it in the Charts widget (sort of like what you did what the colors).


Hey Afonso,

thank you so much.

You really helped me a lot


Best regards


Tobias