Conditional dbr.hnull

(7 posts) (2 voices)

Tags:

  1. ajdjackson, Member

    Hi

    I'm creating a crosstab Profit and Loss Account and within this crosstab I have ratios based on total sales.

    All sales have mcat = 20.

    select 'dbr.hsum','Val','bud','bvar','tsales'; select 'dbr.calc','rat','[mcat]<>20 ? ([Val]/[tsales])*-100:0'; select 'dbr.hnull','rat';

    What I'm getting is:

    As you can see I'm getting ratios for the Sales in the hsum columns. Is there a way to avoid this?

    Also in the above dbr.calc how do I do a simple If - not the ternary shorthand? I've tried

    select 'dbr.calc','rat','[mcat]<>20 ?: ([Val]/[tsales])*-100';

    select 'dbr.calc','rat','if([mcat]<>20) ([Val]/[tsales])*-100';

    select 'dbr.calc','rat','[mcat]<>20 ? {([Val]/[tsales])*-100}';

    Thanks

    Jake

  2. myDBR Team, Key Master

    You could show the full query.

    Where in the query do you have the 'mcat'-column? You probably want it before the crosstab column so that it is automatically used in the horizontal summary calculation. If you have if as a crosstab data column (one for each crosstab set), you need to use select 'dbr.hmax','mcat'; so that it is available for the dbr.calc.

    The syntax

    select 'dbr.calc','rat','[mcat]<>20 ? ([Val]/[tsales])*-100:0';

    is correct:

    --
    myDBR Team

  3. ajdjackson, Member

    Good morning

    Yes - I did have mcat (plus a few others hidden - for use ass parameters for linked reports) in the crosstab columns. I have now moved these to before the crosstab and all is now working as expected.

    Before I did that I tried the dbr.hmax solution and that worked also - although I do not understand why that should work!

    My second question was - how do I do something similar to:

    <?php if ($a > $b) echo "a is bigger than b"; ?>

    that is - a simple IF with no ELSE.

    No biggie as I removed the 0 with dbr.colstyle.

    Here is the full query:

    select 'dbr.subtitle','click a value for analysis'; select 'dbr.crosstab','Comp'; select 'dbr.crosstab.title','Operational Companies'; select 'dbr.hdr','MCat'; select 'dbr.hdr','SCat'; select 'dbr.hsum','Val','bud','bvar','tsales'; select 'dbr.sum','Val','bud','rat'; select 'dbr.summary.options','limit_summary_level','=1'; select 'dbr.summary.options','skip_single_line_summary'; select 'dbr.colstyle','MCat','[font-weight:bold;min-width:75px;max-width:175px;font-size:1.2em;]'; select 'dbr.colstyle','SCat','[font-weight:bold;min-width:150px;max-width:250px;text-align:right;border-right:1px solid #a9a9a9;]'; select 'dbr.colstyle','Val','bud','bvar','[min-width:70px;text-align:right;]%0.0f;[min-width:70px;text-align:right;]-;[min-width:70px;color:red;text-align:right;](%0.0N);'; select 'dbr.colstyle','rat','[width:35px;text-align:right;]%0.1f;[width:35px;text-align:right;] ;[width:35px;color:red;text-align:right;](%0.1N);'; select 'dbr.css','th[rowspan="2"].cell_ct_top.align_c {background: url("../user/images/MASTER1.png") center center no-repeat;}'; select 'dbr.css','.rbold {border-top:2px solid black;border-bottom:2px solid black;font-weight:bold;font-size:1.2em;}'; select 'dbr.css','.bcolor td {background-color:#e6e6e6;}'; select 'dbr.css','td.summary_level1 {font-weight:bold;border-top:1px solid black;border-bottom:2px solid black;text-align:right;}'; select 'dbr.hidecolumn','bdate','scat','mcat','rType','bcomp','tsales'; select 'dbr.calc','bvar','[mcat]<>20 ? [bud]-[Val] : ([bud]-[Val])*-1'; select 'dbr.calc','rat','[mcat]<>20 ? ([Val]/[tsales])*-100:0'; select 'dbr.hnull','rat'; select 'dbr.report','sp_DBR_PL_SubCat_Det','[Val]','popup','inComp=bcomp','inSCat=scat','inDate=bdate','inType=rType', 'show_link=[mcat]!=50&&[mcat]!=60&&[mcat]!=35';

    with cte_psum (pl_company,pl_maincat,pl_subcat,pl_period,pl_val) as (select pl_company,pl_maincat,pl_subcat,pl_period, sum(pl_val) from dbo.tmp_pandl where iuser=@inLogin group by pl_company,pl_maincat,pl_subcat,pl_period), cte_tsales (pl_company,tsales) as (select pl_company,sum(pl_val) from dbo.tmp_pandl where pl_maincat=20 and iuser=@inLogin group by pl_company) select b.Rep_Desc as "[MCat]", c.Rep_Desc as "[SCat]", a.pl_period as "[bdate]", a.pl_subcat as "[scat]", a.pl_maincat as "[mcat]", @inRType as "[rType]", d.tblcompanies_comp as "Company[Comp]", iif(b.Rep_ID in (20),isnull(a.pl_val,0)*-1,isnull(a.pl_val,0)) as "Value, $[Val]", null as "Ratio,%[rat]", f.bVal as "Budget[bud]", null as "Variance[bvar]", a.pl_company as "[bcomp]", g.tsales as "[tsales]" from cte_psum a join dbo.tblfin_rep_grp b on a.pl_maincat=b.rep_id left outer join dbo.tblfin_repmaincat c on a.pl_subcat=c.rep_id join dbo.tblcompanies d on a.pl_company=d.tblcompanies_ID join (SELECT pl_subcat as pcat FROM dbo.tmp_pandl where iuser=@inLogin group by pl_subcat HAVING sum(pl_val)<>0) e on a.pl_subcat=e.pcat left join #budgets f on b.rep_id=f.bGrp and c.rep_id=f.bCat and a.pl_company=f.bComp join cte_tsales g on a.pl_company=g.pl_company where d.Ops=1 order by d.tblcompanies_ID,b.rep_sortorder,c.rep_desc;

    One other thing I forgot to mention was the summarylevel_1 total of the Variance column has the wrong sign. Can you see why that is?

    Many thanks

    Jake

  4. myDBR Team, Key Master

    My second question was - how do I do something similar to:

    <?php
    if ($a > $b)
    echo "a is bigger than b";
    ?>

    that is - a simple IF with no ELSE.

    No biggie as I removed the 0 with dbr.colstyle.

    You could do:

    select 'dbr.calc','rat','[mcat]<>20 ? ([Val]/[tsales])*-100 : null';

    One other thing I forgot to mention was the summarylevel_1 total of the Variance column has the wrong sign. Can you see why that is?

    You could open a support ticket with SQL export of the report (export as SQL from export menu).

    --
    myDBR Team

  5. ajdjackson, Member

    Thanks - just sent the sql export.

    Jake

  6. myDBR Team, Key Master

    One other thing I forgot to mention was the summarylevel_1 total of the Variance column has the wrong sign. Can you see why that is?

    Your Variance-column is calculated based on formula:

    select 'dbr.calc', 'bvar', '[mcat]<>20 ? [bud]-[Val] : ([bud]-[Val])*-1';

    Meaning if mcat-column is different from 20 then use [bud]-[Val] otherwise use negative of that. You do not have any value defined for mcat-column to the summary level, meaning the value to be used would be [bud]-[Val]. In the rows you do have the mcat-column defined, so the result is negative of that.

    Easiest way of fixing it would be to define the value for the summary level. You can use:

    select 'dbr.max', 'mcat';

    You can see the logic yourself if you do not hide the mcat-column.
    --
    myDBR Team

  7. ajdjackson, Member

    Great - this works.

    And by making visible the mcat column I can see that there is no value assigned to the summary rows.

    Thanks for the tip.

    Jake


Reply

You must log in to post.