col_value_get() and col_value_set() use in a Crosstab Report question

(5 posts) (2 voices)
  • Started 3 years ago by ajdjackson
  • Latest reply from ajdjackson
  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 $$

    Posted 3 years ago #
  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

    Posted 3 years ago #
  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

    Posted 3 years ago #
  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

    Posted 3 years ago #
  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

    Posted 3 years ago #

Reply

You must log in to post.