Appian Community
Site
Search
Sign In/Register
Site
Search
User
DISCUSS
LEARN
SUCCESS
SUPPORT
Documentation
AppMarket
More
Cancel
I'm looking for ...
State
Not Answered
Replies
10 replies
Subscribers
7 subscribers
Views
4673 views
Users
0 members are here
Share
More
Cancel
Related Discussions
Home
»
Discussions
»
General
want to display data on group by in grid.
Srikanth Reddy P
A Score Level 1
over 7 years ago
Hi All ,
I have requirement, where i want to display data in below format based on group by (Group By on Vendor, Region , Country, REquest Type , SLA breached) bet ween two particular dates.
Where No. of requests = Count of requests which are complete after deadline
Total No. of request = Count of requests which are complete after deadline + Count of requests which are complete on time
% SLA breached = % of requests which are complete after deadline in total no. of requests.
My challenge here is getting *% SAL Breached and no. of requests and total no. of requests between two dates using query entity.
Can't we achieve this with query entity.
please suggest any other approach.
Thanks
OriginalPostID-254415
Discussion posts and replies are publicly visible
0
Srikanth Reddy P
A Score Level 1
over 7 years ago
DB is Mysql
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Omkar K
over 7 years ago
You could use following to filter data between 2 dates:
a!queryFilter(
field: "dateField",
operator: "between",
value: {date1, date2}
)
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Ramesh Gudipati
over 7 years ago
You can refer to a!queryAggregationColumn() to achive the above. For more details please refer to the below link:
forum.appian.com/.../System_Functions.html
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
sikhivahans
over 7 years ago
@srikanthreddyp To the best of my knowledge, your challenge is to how to group and here are few inputs for you:
So build a database view, which has all the details and also additionally the following fields which will help you in aggregating the results:
1. is_completed_on_time Define the value of this as 0 or 1. 0 indicates that the corresponding record isn't completed on time whereas 1 indicates that the record is on time. I believe you have a value to compare against in order to arrive at this conclusion.
2. is_sla_breached Define the value of this as 0 or 1. 0 indicates that the corresponding record hasn't breached SLA whereas 1 indicates that the record has breached SLA. I believe you have a value to compare against in order to arrive at this conclusion.
Now when you apply the filters, summation of the is_completed_on_time or is_sla_breached should drive the number of requests or total requests.
When it comes to % SLA Breached, (SUM(is_sla_breached)/COUNT(total no. of records)) * 100 should drive the results.
Not sure if there is much more simpler way, but I guess the above might be a good starting point. Let us know if you have any follow up questions or scenarios that are offering you challenges.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
shivakanthr
over 7 years ago
@sikhivahans,
First two points are we have handled in view.
My challenge here is calculating the% in QueryEntity is Possible?
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
shivakanthr
over 7 years ago
calculating % from Appian side (In grid) might lead to performance issues.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
sikhivahans
over 7 years ago
@shivakanthr Please find attached the text file in which I have mentioned few points as far as my knowledge is considered and do let me know if you have any concerns/follow-up questions.
254415.txt
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Srikanth Reddy P
A Score Level 1
over 7 years ago
Thanks @sikhivahans, We had this approach but thought about performance issue, as i mentioned in above comment. Do you think that there will be any performance issue having calculation in SAIL?
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
sikhivahans
over 7 years ago
@srikanthreddyp It should be fine as long as the following are taken into consideration:
1. Calculations are simple
2. Calculations are minimal in count
3. Calculations/formatting is a one-time activity as opposed to invoking on the fly(As shown in my text file earlier, you can do it one time or do it every time in the gridTextField)
4. On-demand query as opposed to querying each time when a SAIL component(that has saveInto) is interacted with. (Using load vs with)
At the same time, you can push everything into database provided if
1. Grouping is fixed as opposed to giving options to user to perform group by any field
2. Database view you have created is only for a single report as opposed to the usage of the same database view in the other areas for different purposes. (Simply speaking, limiting the view only for the current report)
On top of the above-mentioned points, consider the following options as well and these will give you much clarity as to which approach you should stick with:
1. Measure the SAIL interface performance
2. Load test(based on the amount of data that you expect down the line) the report
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
harrys
over 7 years ago
I would try to do as much as possible via the database, for performance reasons. A stored function (www.mysqltutorial.org/mysql-stored-function/) might help with the calculations, depending on how you write the view.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel