A couple of Crosstab questions

(4 posts) (2 voices)


  1. ajdjackson, Member


    I've a couple of crosstab questions.

    1. I have created a crosstab with a query with a series of unions. The first row is Sales, the second row is Cost of Sales and the third row is the percentage of Cost of Sales to Sales. I'm using hsum to sum the rows but obviously I can't sum the % row. I have even tried creating a separate crosstab for the percentage row and hide the header etc but I still have the same issue in how to handle percentages in the Total column. Any advice would be most appreciated.

    2. Following on from the above is it possible to numerically format rows differently in a crosstab. The first two rows above would be formatted with no decimal places but the percentage row I would like 2 decimal places. Is this possible?

    Cheers and thanks


  2. myDBR Team, Key Master

    For 1. you can use dbr.calc to calculate the percentage. The dbr.calc will do the calculation per row so it will do the calculation based on the summary values.

    And for 2., yes you can format the rows differently. Just use dbr.cellformat, which will allow for you to set format of the individual cell.

    myDBR Team

  3. ajdjackson, Member


    Thanks for that.

    I've another issue regarding cross-tabs that I can't figure out.

    Any reason why the below doesn't give a cross-tab?



    select 'dbr.resultclass','vaddclock'; select 'dbr.text','Value Added by £ Labour','head'; select 'dbr.crosstab','pDate','Mts'; set daymon = 1; while (daymon <= day(maxrevdate)) do if dayname(date_add(date_format(concat(inYear,"-",inMon,"-01"),"%Y-%m-%d"), interval daymon-1 day)) not in ("Friday","Saturday") then select 'dbr.crosstab.col',date_format(date_add(date_format(concat(inYear,"-",inMon,"-01"),"%Y-%m-%d"), interval daymon-1 day),"%d-%a"); end if; set daymon=daymon+1; end while; select 'dbr.colstyle','Mts','[min-width:70px;max-width:70px;]%0.2f'; select 'dbr.havg','Mts'; select 'dbr.colstyle','mType','[min-width:200px;max-width:200px;white-space:nowrap;overflow:hidden;text-overflow: ellipsis;text-align:right;font-weight:bold;]'; select 'dbr.colstyle','mType1','[min-width:200px;max-width:200px;white-space:nowrap;overflow:hidden;text-overflow: ellipsis;text-align:left;font-weight:bold;]'; select 'dbr.css','.vaddclock tr.summary_row {display:none;}'; select 'dbr.css','.vaddclock thead.tableFloatingHeaderOriginal {display:none;}'; select 'dbr.css','table.vaddclock {margin-bottom:0;}'; select 'dbr.css','.vaddclock td:last-child {font-weight:bold;}'; select 'dbr.css','.vaddclock .title {margin-left:10%;}'; select 'dbr.search',0; select 'dbr.calc','[Mts]','([Rev]-[mats])/[lCost]'; select 'dbr.hidecolumns','Rev';

    select "Value Added per £ Labour" as "[mType]", date_format(a.lDate,"%d-%a") as "Date[pDate]", null as "£[Mts]", "Value Added per £ Labour" as "[mType1]", b.Rev as "[Rev]", sum(a.dCost) as "[lCost]", b.mCost as "[mats]" from daily_lab a join dailyprodtsum b on a.lDate=b.dpDate where a.lDate <= maxrevdate group by a.lDate order by a.lDate;

  4. myDBR Team, Key Master

    If probably will. Depends on your data though how the output will look.

    You could put the CSS under one dbr.css above the result set as the CSS will be report wide definitions. It won't change how the report will look.

    To see what the problem is, run the report with SQL export and send the output to support email so we can replicate the issue. Also, take a screenshot and point out what is the problem with it.

    myDBR Team


You must log in to post.