Change categories top level charts of the manager portal

 
Author
Message
th
SysAid Mod
250
 
I am using some of the fancy animated graphs of the management portal. Since we have a couple of category of which only one is critical ("support") I would like to restrict the data source of the graphs (like "Active service requests") to that specific category only. Can this be done, if yes how?
SysAid R&D
32
 
hi th,

first of all i want to say that it is possible to do so but it is not simple, you need to stop the service then open the dashboardDef.xml
inside SysAidServer\root\WEB-INF\conf folder in notepad or another editor as you like.
after that you need to look at the sql's sentences inside each chart and modify it.

for example to change the sql of the Active Service Requests chart (the first one) - you need to modify the sql and the script (PaintCode) like this:

import com.sysaid.server.DBConnection; // added after the import java.io.IOException;

String categoryFilter = "support";
String sqlStr = "SELECT count(*) counter " +
" from service_req r " +
" where r.status in(" + openStatus + ") and r.problem_type = " + DBConnection.dbPrefix + "'" + categoryFilter + "'";

i added a filter for the category "support" in order to get only this category.
the DBConnection.dbPrefix is imported here to support unicode languages and we need to import this library first in order to use it.

this will change the first chart, try it and try to change the other charts ... if you need another help just ask

Note: remember to restart the service back and look at the sysaid.log if you got errors.


This message was edited 2 times. Last update was at Sep. 01, 2008 08:30 AM

th
SysAid Mod
250
 
That works great, thank you!

If I want to change it in the "Service Quality by Timer" chart would I have to change the code as follows?

String sql1 = "SELECT "+fieldName+" fld, r.insert_time , r.close_time "+(timer>0 ? " , r.timer"+timer : " ")+
" from service_req r "+moreJoins+
" where "+isNotNull+" and "+
" r.insert_time between ? and ? "+moreWhere+
" order by "+fieldName;

into

String categoryFilter = "support";
String sql1 = "SELECT "+fieldName+" fld, r.insert_time , r.close_time "+(timer>0 ? " , r.timer"+timer : " ")+
" from service_req r "+moreJoins+
" where "+isNotNull+" and "+
" r.insert_time between ? and ? "+moreWhere+
" order by "+fieldName and
r.problem_type = " + DBConnection.dbPrefix + "'" + categoryFilter + "'"
;


This message was edited 1 time. Last update was at Sep. 02, 2008 04:02 AM

SysAid R&D
32
 
hi th,

if you look at the sysaid.log you can find two errors :
1. Script6.groovy: 225: The current scope does already contain a variable of the name categoryFilter @ line 225, column 16.
2. java.sql.SQLException: Syntax error: Encountered "and" at line 1, column 235.

solutions:
1. try another name of categoryFilter ex. catFilter or find this row: categoryFilter=""; and replace it with categoryFilter="support";
2. you cant write the filter after the order by :
" order by "+fieldName and
r.problem_type = " + DBConnection.dbPrefix + "'" + categoryFilter + "'";

the sql sentece should be like this:
String sql1 = "SELECT "+fieldName+" fld, r.insert_time , r.close_time "+(timer>0 ? " , r.timer"+timer : " ")+
" from service_req r "+moreJoins+
" where "+isNotNull+" and "+
" r.insert_time between ? and ? "+moreWhere+" and r.problem_type = " + DBConnection.dbPrefix + "'" + categoryFilter + "'"+
" order by "+fieldName;

note: you have to be carful to add "+ where is should be , because every string that you want to keep in the sql should be in Quotation marks: " bla bla "

th
SysAid Mod
250
 
That works, thank you.
Super SysAider
50
 
It's possible to do any similar modification for take a bar graph of cake graph of active service records grouped by 3r level category of a concrete subcategory?