1. 

   bushraj, Member

   Hey,

   I want to know how I can use style (css)for a stored procedure. I m calling a stored procedure in a report. And that SP calculates and generated the records. I want to make all the records as 'Red' where the entry is 0.

   For example the result set is as follows :

   Project Management Office 0.00 0.00 0.00 7.00 6.25 6.50

   The stored procedure have queries which is saved in mysql. And from myDBR i call that stored procedure like :

   Call timesheets.testSPY(Month,year);

   How i can apply the color in this scenario pls help.

   Secondly how i can show current month in my filter as default where parameter query is as follows :

   select 'Jan'
   union
   select 'Feb'
   union
   select 'Mar'
   union
   select 'Apr'
   union
   select 'May'
   union
   select 'Jun'
   union
   select 'Jul'
   union
   select 'Aug'
   union
   select 'Sep'
   union
   select 'Oct'
   union
   select 'Nov'
   union
   select 'Dec';

   Awaiting for your reply. Thanks

   Regards,
   Bushra
2. 

   myDBR Team, Key Master

   To format column values use dbr.colstyle-command. The command allows separate styles for positive, zero and nagetive values. See examples from the documentation.

   As for the second question, parameters have separate options for possible values (as your list of months) and the default value. To set the default value, make a query that returns the current month and set it as a default value. Again, see examples from the documentation.

   --
   myDBR Team
3. 

   bushraj, Member

   But how can i call dbr.colstyle-command from the stored procedure save in DB. As mySql stored procedure wont recognize mydbr commands. Calling the SP from Reports. Not calling queries from Report.Like from

   Call timesheets.testSPY(Month,year)

   How can I tell that i want to perform color on this columns? As columns are generated when SP is executed.

   Do you get the scenario I'm talking about?
4. 

   myDBR Team, Key Master

   So you have a separate stored procedure that fetches the data and your report calls the proceudure?

   If your timesheets.testSPY-procedure returns just one resultset you can call myDBR commands in your report before you call the testSPY-procedure.

   Alternatively you can modify the timesheets.testSPY-procedure to include the myDBR commands or create similar queries in your report.

   --
   myDBR Team
5. 

   bushraj, Member

   Following is my procedure how can I apply color to duration when its 0

   DROP PROCEDURE IF EXISTS sp_DBR_timesheetoveralls
   $$
   CREATE PROCEDURE sp_DBR_timesheetoveralls(months varchar(30),years varchar(14))
   begin

   declare finish int default 0;
   declare cdate date;

   declare dateformat varchar(10000);
   declare name varchar(10000);
   declare team varchar(10000);

   declare str varchar(10000) default "SELECT distinct lower((u.USR_USERNAME)) as Users , te.Team_name as 'Team',";

   declare curs cursor for select distinct te.Team_name,DATE_FORMAT(date(t.Start_Date),'%e-%b-%Y-(%a)') , date(t.Start_Date),u.USR_FIRSTNAME FROM wf_workflow.users u,timesheets.tps_trans_effort_timeline t,
   wf_workflow.tps_lut_teams te right join
   wf_workflow.tps_lut_user_supervisor s

   on te.Team_ID=s.Team_ID

   where u.USR_UID=t.USR_UID
   and u.usr_uid=s.usr_uid
   and te.Team_ID=s.Team_ID
   and month(t.Start_Date)= 3
   and year(t.Start_Date)= years
   group by date(t.Start_Date)
   order by date(t.Start_Date) desc;

   declare continue handler for not found set finish = 1;
   open curs;
   my_loop:loop

   fetch curs into team,dateformat,cdate,name;
   if finish = 1 then
   leave my_loop;
   end if;

   set str = concat(str,"round(sum(case when date(Start_Date) = '",cdate,"' then duration/60 else '0' end),2) as ",dateformat,",");

   end loop;
   close curs;

   set str = substr(str,1,char_length(str)-1);
   set @str = concat(str,", round(sum(duration/60),2) as 'Total Duraion' FROM wf_workflow.users u,timesheets.tps_trans_effort_timeline t,
   wf_workflow.tps_lut_teams te
   right join
   wf_workflow.tps_lut_user_supervisor s

   on te.Team_ID=s.Team_ID
   where u.USR_UID=t.USR_UID
   and u.usr_uid=s.usr_uid
   and month(t.Start_Date)= '3'
   and year(t.Start_Date)= '",years,"'
   group by u.USR_USERNAME ");

   prepare stmt from @str;
   execute stmt;
   deallocate prepare stmt;

   end
   $$
6. 

   myDBR Team, Key Master

   Bushraj,
   the dbr.colstyle has two parameters. First one is the column you will format the second is the formatting string. The formatting string can contain sections for separate positive, zero and negative formatting. Sections are separated with semicolon. Each section has CSS and prinf part to format the data.

   To format a column with zero's in red you would use formatting:

   select 'dbr.colstyle', 'mycolumn', ';[color:red]';

   Where the first section of the formatting being empty means that the positive numbers are formatted using default formatting. The second section ([color:red]) contains just the CSS formatting for zeros.

   Btw. your report seems to be overly complicated as you seem to manually do crosstabs using a dynamic SQL. It would be much easier to use myDBR's crosstab functionality so you would not need to use dynamic SQL and your report would contain just one query. Take a look at the manual and examples how to utilize crosstabs in myDBR.

   --
   myDBR Team

Reply

You must log in to post.