1. 

   spyhunter88, Member

   Hi, I have problem with a 'Toal Column' while using crosstab with crosstab.col. My Purpose is calculate the total_percent by the SUM(Sale)/SUM(GP), not sum by individual percent, Let me show example:

   select 'dbr.sum', 'Sale', 'GP'; select 'dbr.calc', 'percent', '[Sale]/[GP]*100';

   select 'dbr.crosstab', 'xMonth'; select 'dbr.hnull', 'total_percent'; select 'dbr.calc', 'total_percent', '[Sale]/[GP]*100';

   select 'dbr.hidedatacolumn', 'total_percent';

   select concat(Year, '.M', Month) as xMonth, SUM(Sale) as 'Sale[Sale]', SUM(GP) as 'GP[GP]', null as 'percent' from DB group by Year, Month where Date_Fixed BETWEEN fromDate and toDate;

   This works alright, but the 'total_percent' column is blank, no value is calculated while I add the order crosstab column.
   set @iMonth = Month(fromDate); while( concat(Year(fromDate),right(concat('0',@iMonth),2)) <= concat(Year(toDate),right(concat('0',Month(toDate)),2)) ) do select 'dbr.crosstab.col', concat('Y',Year(fromDate),'.M',right(concat('0',@iMonth),2)); set @iMonth = @iMonth + 1; end while;

   I have checked this by remove the 2nd block of code, the values of percent total appear.

   I am using 4.2.3 build (2434).
2. 

   myDBR Team, Key Master

   Hi,
   to what is the 'total_percent' referring to?

   --
   myDBR Team
3. 

   spyhunter88, Member

   1. Sorry, copy - paste mistake. I had edit the code below. But I check the other, it works ok. Don't know what exact problem. But remove the code block make it works. I post the real query with 2 calculat columns:

   select 'dbr.crosstab', 'xMonth';

   select 'dbr.sum', 'Qty', 'Sale', 'GP'; select 'dbr.hsum', 'Qty', 'Sale', 'GP';

   select 'dbr.hidecolumn', 'GP'; select 'dbr.hnull', 'gp_percent'; select 'dbr.hnull', 'total_percent';

   select 'dbr.calc', 'percent', '[GP]/[Sale]*100'; select 'dbr.calc', 'gp_percent', '[Sale]/[Qty]'; select 'dbr.calc', 'total_percent', '[GP]/[Sale]*100';

   select 'dbr.hidedatacolumn', 'gp_percent'; select 'dbr.hidedatacolumn', 'total_percent';

   select concat(Year, '.M', Month) as xMonth, SUM(Sale) as 'Sale[Sale]', SUM(GP) as 'GP[GP]', null as 'percent', null as 'gp_percent', null as 'total_percent' from DB group by Year, Month where Date_Fixed BETWEEN fromDate and toDate;

   Maybe problem only occurs with 2 columns?

   2. And I found something with crosstab. myDBR automatic add a null column (no title, no value) into the right of crosstab. Like this image, beside Wed 20/8.
   
   Link: http://imagizer.imageshack.us/v2/150x100q90/537/CHmv93.jpg
   But the colum only appear when we have an empty row (no value in all crosstab column - the second row) in result set, if we dont have, the null column does not exist.
   And why we have empty row because we use JOIN (this will join to the right side of CrossTab) and need some value of this.

   Thanks,
4. 

   myDBR Team, Key Master

   Now you seem to have a mystery column 'Qty'.

   It's easier if you just post the output of the report with '&export=sql' added to the URL. This way we get the whole report and can easily re-run your report. If you have lot of data, use LIMIT clause to grab first few rows.

   The attached image is bit too small to see what is going on.
   --
   myDBR Team
5. 

   spyhunter88, Member

   I get the code her (with crosstab.col)

   select 'dbr.crosstab.col', 'Y2014.W30'; select 'dbr.crosstab.col', 'Y2014.W31'; select 'dbr.crosstab.col', 'Y2014.W32'; select 'dbr.crosstab.col', 'Y2014.W33';

   select 'dbr.subtitle', 'Unit Sales/Price/Cost: mil VND';

   select 'dbr.column.filter', 'Colour', 'select'; select 'dbr.column.filter', 'Storage', 'select';

   select 'dbr.crosstab', 'Week';

   select 'dbr.hdr', 'Colour';

   select 'dbr.count', 'Storage'; select 'dbr.sum', 'Qty', 'Sales', 'Gross Profit'; select 'dbr.avg', 'Avg. Price'; select 'dbr.summary.text', 'Colour', 'Total';

   select 'dbr.crosstab.title', 'Total'; select 'dbr.hsum', 'Qty', 'Sales', 'Gross Profit';

   select 'dbr.footerstyle', 'background-color: #F7F2E0;font-weight: normal;color:#0101DF;';

   select 'dbr.summary.options', 'skip_single_line_summary'; select 'dbr.summary.options', 'limit_summary_level', '3';

   select 'dbr.hidecolumn', 'Gross Profit';

   select 'dbr.hnull', 'Avg_TPrice'; select 'dbr.hnull', 'GP_total_percent';

   select 'dbr.calc', 'percent', '([Gross Profit])/[Sales]*100'; select 'dbr.calc', 'Avg_TPrice', '[Sales]/[Qty]'; select 'dbr.calc', 'GP_total_percent', '[Gross Profit]/[Sales]*100';

   select 'dbr.hidedatacolumn', 'Avg_TPrice'; select 'dbr.hidedatacolumn', 'GP_total_percent';

   select 'dbr.colstyle', 'percent', '[color: green]%.2f; ;[color: red;]%.2f'; select 'dbr.colstyle', 'GP_total_percent', '[color: green]%.2f; ;[color: red;]%.2f'; select 'dbr.colstyle', 'Qty', '[background-color: yellow;color: red;]%.0f'; select 'dbr.colstyle', 'Sales', '[background-color: #CCEEFF;font-weight: bold;]%.0f'; select 'dbr.colstyle', 'Gross Profit', '[color: green]%.0f; ;[color: red;]%.0f'; select 'dbr.colstyle', 'Avg. Price', '%.3f'; select 'dbr.colstyle', 'Avg_TPrice', '%.3f';

   select 'WHITE' as 'Colour', '8GB' as 'Storage', 'Y2014.W30' as 'Week', 100 as 'Qty', 1000.010000 as 'Sales', 10.000000 as 'Avg. Price', -25.000000 as 'Gross Profit', null as '%GP[percent]', null as 'Avg. Price[Avg_TPrice]', null as 'Total %GP[GP_total_percent]' union all select 'WHITE', '8GB', 'Y2014.W31', 555, 5555.000000, 10.009009, -121.000000, null, null, null union all select 'WHITE', '8GB', 'Y2014.W32', 111, 5656.000000, 50.954954, -212.000000, null, null, null union all select 'WHITE', '8GB', 'Y2014.W33', 888, 5566.000000, 6.268018, -222.000000, null, null, null;

   And the image link:
   

   Thanks,
6. 

   myDBR Team, Key Master

   There was a problem with calculations when crosstab columns were predefined. This has now beed fixed. Thanks for reporting the issue.

   As for the report. You do not need to replicate the Avg. Price and Percent columns for Total as myDBR will calculate them automatically as the calculation is the same for data and the Total.

   --
   myDBR Team
7. 

   spyhunter88, Member

   That's not work for my purpose, the total column will be SUM of Average, not Average of SUM, but I want the Average of SUM.
8. 

   myDBR Team, Key Master

   You know better what you want to calculate.

   With the example code above, your total Qty = 1,654 and Sales = 17,777 giving average price of 10.748. This would be the same with or without the extra columns. The total average column would be calculated with the formula sum(Sales)/sum(Qty).

   --
   myDBR Team
9. 

   spyhunter88, Member

   But how do I do that? If I add select 'dbr.hsum', 'Avg. Price'; to show Avg Price column in Total column, it'll calculate the SUM of AVERAGE, or I miss other command? I am using version 4.2.3 build 2435.

   I follow this example but the percent in Total column does not show up?

   select 'dbr.sum', 'ok', 'all';
   select 'dbr.hsum', 'ok', 'all';
   select 'dbr.hdr', 'group';
   select 'dbr.crosstab', 'session;
   select 'dbr.colstyle', 'percent', '%.2f %';
   select 'dbr.summary.text', 'group', 'Total';
   select 'dbr.calc', 'percent', '[ok]/[all]*100';
   
   select Group[group],
          Session[session],
          OK[ok],
          allresults as 'All[all]',
          null as '%[percent]'
   from group_sessions;
10. 

    myDBR Team, Key Master

    Hi,
    See here.

    --
    myDBR
11. 

    spyhunter88, Member

    Oh thanks, I see my problem is calculate right from sql before use dbr.calc. Thanks, it works.

Reply

You must log in to post.