1. 

   ajdjackson, Member

   Hi

   Many happy holidays to you all.

   I've a query on how to use if possible col_value_get() and col_value_set() in a crosstab report.

   The report is working well and by using options={'onblur':'', 'callback':autosum_int}" I can get the summary calcs to update when I do inline editing.

   My problem is that one of the columns in the crosstab is a calculated field. I can't see how to have this field update when I edit either of the two fields which are used to calculate this field.

   I had a look at col_value_get() and col_value_set() functions and I think I understand how they work but my issue is how to address the other field in the calculation that wasn't edited as the column offset will be different as I edit values for different Quarters (if youfollow me). I tried amending the mycallback function using this+1 var A = col_value_get( this, this+1 ); etc but with no luck.

   Is it possible to do what I wish?

   Cheers

   Jake

   DROP PROCEDURE IF EXISTS sp_DBR_Sales_Budget_Maintenance $$ CREATE PROCEDURE sp_DBR_Sales_Budget_Maintenance(inYear int) BEGIN

   select 'dbr.javascript', 'function mycallback() {

   // Value of A in edited row var A = col_value_get( this, this+1 );

   // Value of B in edited cell var B = col_value_get( this );

   // Set the value of 4th A+B column col_value_set( this, this+2, 0, A*B);

   // Calculate sum of edited column autosum_int(this);

   // Calculate sum of 4th A+B column autosum_int(this,this+2);

   }';

   select 'dbr.parameters.show';

   select 'dbr.editable','SalesNum','sp_DBR_Edit_Sales_Budget_Nums','inSiteID=SiteID','inSDate=BDate',"options={'onblur':'', 'callback':mycallback}"; select 'dbr.editable','AvgSalesPrice','sp_DBR_Edit_Sales_Budget_Price','inSiteID=SiteID','inSDate=BDate',"options={'onblur':'', 'callback':autosum_int}"; select 'dbr.crosstab','Qtr'; select 'dbr.calc','SValue','[SalesNum]*[AvgSalesPrice]'; select 'dbr.hidecolumn',6; select 'dbr.hidecolumn',7; select 'dbr.colstyle', 'SValue', '%.0f'; select 'dbr.hsum','SalesNum','SValue'; select 'dbr.sum','SalesNum','SValue';

   select b.Site_Name as 'Site[Site]', quarter(a.Bgt_date) as 'Qtr[Qtr]', ifnull(a.SalesNum,0) as 'Sales#[SalesNum]', ifnull(a.AvgSalesPrice,0) as 'Avg Sales £[AvgSalesPrice]', null as 'Sales[SValue]', b.Site_ID as 'SiteID[SiteID]', a.Bgt_Date as 'BDate[BDate]'

   from hilmark.tblsalesbudgets a join hilmark.tblsitesjake b on a.Site_ID = b.Site_ID where year(a.Bgt_Date) = inYear order by b.Site_Name;

   select 'dbr.embed_object','createx'; select 'dbr.button', 'Enter New Sales Budget'; select 'dbr.report', 'sp_DBR_New_Sales_Budget_Input','in_Year=Year','createx'; select inYear as 'Year[Year]';

   END $$
2. 

   myDBR Team, Key Master

   The callback's this variable is a JavaScript object pointing to the edited cell. To get the next cell, you can first determine the cell's index (this.cellIndex), where index starts from zero and then use that value as the second parameter to the col_value_get-function (which takes column number starting from 1). So to get the value for next column you would use:

   var next_col_value = col_value_get( this, this.cellIndex+2);

   Same applies to functions col_value_get, autosum_int (and autosum_float).

   --
   myDBR Team
3. 

   ajdjackson, Member

   Hi

   Thanks for getting back.

   Almost there ....

   I've created to callback functions as follows:

   select 'dbr.javascript', 'function mycallback() {

   // Value of A in edited row var A = col_value_get( this, this.cellIndex+2 );

   // Value of B in edited cell var B = col_value_get( this );

   // Set the value of 4th A+B column col_value_set( this, this.cellIndex+3, 0, A*B);

   // Calculate sum of edited column autosum_int(this);

   // Calculate sum of A*B column autosum_int(this,this.cellIndex+3);

   }';

   select 'dbr.javascript', 'function mycallback1() {

   // Value of A in edited row var A = col_value_get( this, this.cellIndex );

   // Value of B in edited cell var B = col_value_get( this );

   // Set the value of 4th A+B column col_value_set( this, this.cellIndex+2,0, A*B);

   // Calculate sum of A*B column autosum_int(this,this.cellIndex+2);

   }';

   These work great except for 1 issue.

   The HSUM total for the SValue column does not get updated although the HSUM for the SalesNum column does.

   Can you see what I'm doing wrong

   Thanks

   Jake
4. 

   myDBR Team, Key Master

   You can use ct_total-function to do the horizontal calculation:

   select 'dbr.javascript', 'function mycallback(in_value)
   
   {
   
   var num, avg;
   
   // Value of SalesNum in edited cell
   
   num = col_value_get( this, this.cellIndex+1 );
   
   // Value of AvgSalesPrice
   
   avg = col_value_get( this, this.cellIndex+2 );
   
   // Set the value of SValue = num*avg
   
   col_value_set( this, this.cellIndex+3,0, num*avg);
   
   // Calculate vertical sum of SValue column
   
   autosum_int(this,this.cellIndex+3);
   
   // Refresh calculation for horizontal sum of SValue
   
   ct_total( $(this).parent().children()[3] );
   
   }';

   --
   myDBR Team
5. 

   ajdjackson, Member

   Thanks - working now.

   Is there a list of buil-in functions such as ct_total etc available?

   I definitely must get a primer on jquery/javascript :)

   Cheers

   Jake

Reply

You must log in to post.