A couple of Crosstab questions

(4 posts) (2 voices)
  • Started 1 year ago by ajdjackson
  • Latest reply from myDBR Team

Tags:

  1. ajdjackson, Member

    Hi

    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

    Jake

    Posted 1 year ago #
  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

    Posted 1 year ago #
  3. ajdjackson, Member

    Hi

    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?

    Thanks

    Jake

    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;

    Posted 1 year ago #
  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

    Posted 1 year ago #

Reply

You must log in to post.