Skip to Content (Press Enter)
UserImage.jpg
Roelof Wobben
0Views
16Comments
4.7 Aggregate Data for a Dashboard
Question

Hello,

If  I want to calcualte the total amount of a order. Can I do this the best on the order screen or the detail screen. If it's on the detail screen, then I need a input parameter to send it to the order screen ?

Roelof

0
0
27 Nov 2014
Copy Link
2011-09-30 16-44-42
João Fernandes
Hi Roelof,

You can calculate the total amount of an order in the screen that makes the most sense to your users.

In the list screen
To display the total amount for each order in the order list screen:
  • Change your aggregate in the Preparation to group orders by their ID
  • Then use the SUM aggregate function to sum all the order items total price for each order
  • If you want to display more information about an order, like the name of the customer that placed the order, you'll need to also group that column.

In the details screen
To display the total amount for a specific order in the order details:
  • Ensure your order details screen has an OrderId input parameter
  • Ensure your aggregate already filters by that OrderId, to only fetch order items for that specific order
  • Since you are now fetching order items for a single order, you can now apply the SUM aggregate function on the total price colum to find out the total price for that particular order. Again, if you want to display more information about the order, you'll need to group by that column.
Don't forget that the reference docs are there to help you: Hope this helps.
0
0
28 Nov 2014
Copy Link
2014-07-24 06-24-22
Manuel Antunes
Hello.

I need some help on this issue - search text filtering data in the aggregates.

I have two aggregates that, depending on the user role, are being used to access the same table. The difference is that for some roles, the user has access to all records despite he enters or not the search criteria from the database and for one specific role, the user will have access only to the records that match the search criteria - like a password to access records.

This second aggregate is working well in the "Test values" mode within but in published version, it doesn't work with the user logged in belonging to the correct role that is intended to get it work

The preparation:


"Test values" mode: 

Works. If the value is blank, it doesn't show any record because I prevented that in the filter condition, as in the refresh table preparation to.

Published version:

I'm attaching the .oml file.

Can someone help, please?


TestsOnline.oml
0
0
29 Nov 2014
Copy Link
2020-03-05 15-52-45
André Vieira
Staff
Hi Manuel,

Just looking at the images you put here I wonder what you did bind the Table Records to. You have two aggregates on the preparation but I see that you only bind to one of them in the UI...
Another thing that strikes me is why do really need two aggregates. It seems to me that they return (or should since you only have one Table Records on the screen) the same data, it is only a condition that should take into consideration if the User is a manager or not...

If you want to use the Role as part of the condition be sure to check the module 13 ahead where we cover security.

Cheers
0
0
30 Nov 2014
Copy Link
2014-07-24 06-24-22
Manuel Antunes
André Vieira wrote:
Hi Manuel,

Just looking at the images you put here I wonder what you did bind the Table Records to. You have two aggregates on the preparation but I see that you only bind to one of them in the UI...
Another thing that strikes me is why do really need two aggregates. It seems to me that they return (or should since you only have one Table Records on the screen) the same data, it is only a condition that should take into consideration if the User is a manager or not...

If you want to use the Role as part of the condition be sure to check the module 13 ahead where we cover security.

Cheers
Thank You for your answer.

Yes, I have read the chapter 13. Several times. If you have time to check my .oml file, You'll see several implementations of the techniques learned in that chapter.
 
But, for this situation I thought of using the same web screen with two different methods of filtering data; 
 
- If the user has one kind of role, he has access to all rows from the database or only to the rows that match his searching criteria for the attribute "Name".
 
- if the user has the other role, he will only have access to the row(s) that match the searching criteria for the attribute "TestPassword".
 
That's why I tryed to create two aggregates, over the same database table, each one with its filter.
 
And this works well in the "Test value" mode in the development environment but not in the published version.
 
Now, You pointed that one of the aggregates doesn't bind to the UI... Where can I check and correct that?
 
Thank you.
0
0
30 Nov 2014
Copy Link
2020-03-05 15-52-45
André Vieira
Staff
If you select the Table Records on the screen you will notice that the Source Record List property is pointing to one of the aggregates... Now, either you implement that same logic on the Source Record List and place there an if to determine what aggregate to use, or (what I think would be easier to maintain and promote code readability) you can implement the same logic with only one aggregate, after all what you want to return (the structure) is the same you just want to condition the number of rows returned and you can do this in a condition in the aggregate.

0
0
30 Nov 2014
Copy Link
2014-07-24 06-24-22
Manuel Antunes
André Vieira wrote:
If you select the Table Records on the screen you will notice that the Source Record List property is pointing to one of the aggregates... Now, either you implement that same logic on the Source Record List and place there an if to determine what aggregate to use, or (what I think would be easier to maintain and promote code readability) you can implement the same logic with only one aggregate, after all what you want to return (the structure) is the same you just want to condition the number of rows returned and you can do this in a condition in the aggregate.
 
 Yes, I would prefer the second choice but I didn't knew I could implement a If_then_else logic within the filter.

If I understood it well it would be something like in the screenshot below but, how can I overcome the sintax error I'm getting (the help text didn't help me)



Thank you! And many thanks for trying to help me ...on a weekend. :)



 
0
0
30 Nov 2014
Copy Link
2020-03-05 15-52-45
André Vieira
Staff
Try something like:

(User.Name like "%" + Session.Cabecalhos_SearchKeyword + "%" and CheckTestOnlineManagerRole(UserId:) = True) or (Cabecalho.TestPassword = Session.Cabecalhos_SearchKeyword and Cabecalho.TestPassword <> " " and CheckTestOnlineManagerRole(UserId:) = False)


0
0
30 Nov 2014
Copy Link
2014-07-24 06-24-22
Manuel Antunes
André Vieira wrote:
Try something like:

(User.Name like "%" + Session.Cabecalhos_SearchKeyword + "%" and CheckTestOnlineManagerRole(UserId:) = True) or (Cabecalho.TestPassword = Session.Cabecalhos_SearchKeyword and Cabecalho.TestPassword <> " " and CheckTestOnlineManagerRole(UserId:) = False)

 
 In fact, there are three roles involved but ...it works! Thank you very much, especially for helping me on a weekend.  :) 

The code


The result :)



Now, I can move to the next problem...




 
0
0
30 Nov 2014
Copy Link
2019-06-17 11-18-07
Dmitrii Lubenskii
How to set Date test value? 
If I enter any date, it says: " unexpected '#' in expression ". And studio adds # automatically.

Thank you.
0
0
10 Dec 2014
Copy Link
2020-03-05 15-52-45
André Vieira
Staff
Dmitry Lubenskiy wrote:
How to set Date test value? 
If I enter any date, it says: " unexpected '#' in expression ". And studio adds # automatically.

Thank you.
 
Hi Dmitry,

For what are you trying to set a test value? Can you share a screenshot? 
0
0
10 Dec 2014
Copy Link
2019-06-17 11-18-07
Dmitrii Lubenskii
André Vieira wrote:
Dmitry Lubenskiy wrote:
How to set Date test value? 
If I enter any date, it says: " unexpected '#' in expression ". And studio adds # automatically.

Thank you.
 
Hi Dmitry,

For what are you trying to set a test value? Can you share a screenshot? 
 Thank you for your reply.
I attached screenshot.
 
d0zqzWs[1].png
0
0
10 Dec 2014
Copy Link
2020-03-05 15-52-45
André Vieira
Staff
Dmitry Lubenskiy wrote:
 Thank you for your reply.
I attached screenshot.
 
 
 So two things come to mind:
  1. What is the data type of Search_endDate? Is it Date?
  2. Assuming it is a date try using #2014-12-03# instead of #03-12-2014#

0
0
10 Dec 2014
Copy Link
2019-06-17 11-18-07
Dmitrii Lubenskii
André Vieira wrote:
Dmitry Lubenskiy wrote:
 Thank you for your reply.
I attached screenshot.
 
 
 So two things come to mind:
  1. What is the data type of Search_endDate? Is it Date?
  2. Assuming it is a date try using #2014-12-03# instead of #03-12-2014#
 Yes, you were right. Thank you. 
But it is strange that Service Studio inserts test value "#DD-MM-YYYY#" by default. Perhaps it depends on my locale. And error message in this case is really confusing. It should be "wrong format" or something like that.
 
BR,
Dmitry.
0
0
10 Dec 2014
Copy Link
2011-09-30 16-44-42
João Fernandes
Yes Dmitri, it's a bit confusing at first.
But just like for text, you need to use double quotes (e.g. "dmitri"), for dates you need to use hash signs (e.g. #2014-01-01#).

If you write:
  • dmitri - the platform won't know this is a text string
  • 2014-01-01 - the platform won't know this is a date.
You can read more about the available data types and their literal values in the reference docs.
0
0
10 Dec 2014
Copy Link
2020-03-05 15-52-45
André Vieira
Staff
Hi Dmitry,

Regarding the default value I think there might be a bug there. Thanks for the heads up, I already forwarded it to the team so that they can investigate.
0
0
11 Dec 2014
Copy Link
UserImage.jpg
Ilham Rania
Hello

As you w'll see in this attachement i have Two Agregates,in the first i calculated the SumTotal of the 'IntrantEntretien' and in the second one i calculated the SumTotal of "Main Oeuvre" so now i want to make the Total between the both of them SumTotal1+SumTotal2 if there is a way to resolve this prob please

Thanks
Total.png
0
0
19 Jun 2015
Copy Link
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.