Model and Query Data (cont.)
Aggregate Data for a Dashboard
This lesson is part of the Developer Online Class for OutSystems 9 course.

in this session we will see how to aggregate data
to create a dashboard with the top products list
before we actually go here and implement our dashboard
and since we only have one order in our application
let me open the application so that we can create a few more orders
for our dashboard so bear with me in the next few seconds
so that we can create the data here that we will
later show in our dashboard this is actually a great point for you to
stop this video and go back to your application and create also
a few orders so that when you create a dashboard you'll be able to see
that information showing up okay so
me just go ahead and create here a few orders
and let's add a couple of products here okay let's create another one
this will be for Eric
and let's
add a product here
also a USB cable and well
actually Eric changed his mind and he
canceled this order and he created a new one
so let's go ahead and create a new one save
and for this new order we will have
three headsets and also
mouse three of these and
also three screens
and three
USB cables and let's create one final one
this will me
Tom's and
ordered a mac book okay
we're done we have these orders let's go back to our development environment
and implement our dashboard now the first thing will do is
actually create here a new web screen for our dashboard
and don't forget to tick the anonymous role
and actually I want to make the dashboard the home of my application so
I need reconnect here the home entry point there
and and now let me give a
tile here to the page Dashboard
that's good and as i said i want to show here
the top products now to get
the top products we will need to go and fetch this information from the database
so we will need to add a preparation here
and in this preparation we're going to
an aggregate which will fetch
the top products and let's
open the aggregate and since we want the top products
I want the products that are
sold the most so we'll get this information with
the OrderItem so drag and drop the OrderItem here
and there we have now to determine the top products we will need to consolidate
the products present in the orders
we will do this by grouping the results by the product name
as you can see here's some of these products like the USB cable
and the mouse and the screen they're sold more
than once they are present in more than one order so we'll need to group
by this product name and we will do this by using this option here
you can see that the screen was bought three times in one
order but the mouse is present in three different orders
so now we can go into this information
and actually what we want is to
Sum all these values for each product
and to do this we will need to use
here the Sum aggregate function
and we will do the same for the total price
so now I know for each product the quantity that was sold
and that total income for that product
I remember though that in
at least one order that order was canceled
so I want to remove those products
from the order that was canceled from these results
and to do that I'm going to add here
this StatusId from the order to my aggregate
and I'm going to filter
these status to remove
the canceled orders there so
now I have information about all the orders excluding the ones that were
and these
these blue columns here will be that output of my
aggregate and the only thing that I'm missing here is to sort
this list of products by the total
price sum column and we will do this in
a descending fashion okay
so this is the information that we want to show on the screen let's go back to
the screen
let's add here a table records
and bind it
to do that aggregate that we have on the preparation
and now
since this is not since what we're returning from the aggregate
is not an entity we will have to create here the columns
for the table records so let's go ahead and create here
three columns so the first one will be
the products we will show the product name here the second one will be the quantity
sold and the third one
will be the total income now let's add an expression here for each one of these
the first one the product this will be
the name attribute that
we have here and a second one
the quantity sold: this will be
the second attribute and finally
the total income the third
attribute in the return of my aggregate okay
one final thing that I want to do here is to only show
5 of the top products so let me
change here the line count to
5 and we're good to go let's take a look at our dashboard
in the application and here we have
it looks like Mac Book is the product that has
most income in our products list that's it