1. 

   ajdjackson, Member

   Hi

   I've been looking at this for hours now and can't figure out what I'm doing wrong.

   I've created a crosstab report and it's working fine except I can't get columns sorted.

   The crosstab report reports 36 quarters ending in the last day of quarter. The columns are jumbled.

   select 'dbr.title','Cash Flow based on Budgets for Years 2015 - 2017'; select 'dbr.crosstab',4; select 'dbr.summary.text',2, 'Grand Total'; select 'dbr.hsum',5; select 'dbr.hdr',1; select 'dbr.hdr',2; select 'dbr.sum',5; select 'dbr.colstyle',1,'[font-weight:bold;color:blue;]'; select 'dbr.colstyle',2,'[font-weight:bold;]'; select 'dbr.colstyle',5,'%0.0F;-;[color:red;border-bottom-style:none;](%0.0F)'; select 'dbr.footer.colstyle', 5,'[font-weight:bold]%0.0F;-;[color:red;font-weight:bold;border-color: black;](%0.0F);'; select 'dbr.css', '.summary_level2 {background-color: #E8E3E3; border-top-style: solid;border-bottom-style: solid;border-width: 1px;border-color: black;}'; select 'dbr.css', '.summary_level1 {background-color: #C5C6C9; border-top-style: solid;border-bottom-style: solid;border-width: 3px;border-color: black; color: blue;font-weight:bold}'; select 'dbr.css', '.summary_level0 {background-color: #E0F7FF; border-top-style: double;border-bottom-style: double;border-width: 3px; border-color: black;color: green;font-weight:bold;}';

   select c.tblCustomer_Name as 'Company[Comp]', b.Site_Name as 'Site[Site]', a.Cat as '[Cat]', date_format(MAKEDATE(YEAR("2015-01-01"), 1) + INTERVAL a.CQTR QUARTER - INTERVAL 1 DAY, '%b-%y') as 'Quarter[QTR]', ifnull(a.CValue,0) as '£[CValue]' from cashflow_tmp a join hilmark.tblsitesjake b on a.CSiteID = b.Site_ID join hilmark.tblcustomers_j c on b.Site_Company = c.tblCustomer_ID order by c.tblCustomer_Name,b.Site_Name,a.CatID,a.CQTR;

   As you can see I would like to sort on a.CQTR which is the a number representing the quarters and runs from 1 to 12 ie 3 years.

   Another thing is it possible to get total of the individual categories in the summary levels? What I mean is that summary for column 1 is it possible to see the totals for each of the categories (column 3)?

   Thanks

   Jake
2. 

   myDBR Team, Key Master

   myDBR, by default, displays the crosstab columns in the order they appear in the data. As you are sorting first by customer, site, the quarters do not come in the order you would wish. For this reason, myDBR has a command dbr.crosstab.col. See more info and examples from documentation.

   To see total of individual categories, set header level for category.

   Btw, for easier maintenance and readability, you can use column references instead of column numbers. So, instead of "select 'dbr.crosstab',4", you can use "select 'dbr.crosstab','QTR'".
   --
   myDBR Team
3. 

   ajdjackson, Member

   Hi

   Thanks for getting back. I wasn't aware the that order by statement had no effect on the crosstab columns.

   I had considered using 'dbr.crosstab.col', and now am, but that means I have had to hard code the Quarter descriptions eg Mar-15, Sep-15 etc for the rolling 12 quarters. The data returns the 12 quarters as 1,2,...12 and I convert this using date_format(MAKEDATE(YEAR("2015-01-01"), 1) + INTERVAL a.CQTR QUARTER - INTERVAL 1 DAY, '%b-%y').
   Is there a way I can do this without hard-coding?

   Not sure what you mean by setting the header level for the category? I added 'dbr.hdr',3 but that didn't produce what I wanted and it didn't add the total to the .summary_level1 (I think that the right level). See below. I want the totals for the 'Category' column to appear where I've indicated.

   

   You can see another issue I'm having. Sorry. When I export to Excel instead of getting numbers I get the Printf formatting strings. Any idea how I fix that?

   Thanks

   Jake
4. 

   myDBR Team, Key Master

   I wasn't aware the that order by statement had no effect on the crosstab columns.

   myDBR does, by default, make the column order the same as in your data i.e "order by"-order. In your query the order by is "c.tblCustomer_Name,b.Site_Name,a.CatID,a.CQTR".

   I had considered using 'dbr.crosstab.col', and now am, but that means I have had to hard code the Quarter descriptions eg Mar-15, Sep-15 etc for the rolling 12 quarters. The data returns the 12 quarters as 1,2,...12 and I convert this using date_format(MAKEDATE(YEAR("2015-01-01"), 1) + INTERVAL a.CQTR QUARTER - INTERVAL 1 DAY, '%b-%y'). Is there a way I can do this without hard-coding?
   

   Create a while loop which will loop through the quarters and which will select each quarter at a time: select 'dbr.crosstab.col', v_quarter'. You do not need to hard code it as you you use same parameters as your original query.

   I want the totals for the 'Category' column to appear where I've indicated.

   Do not quite understand what you are trying to do. The Category column in your example seems contain strings ("Construction Costs", "Fees @ Sales" etc). What does "totals for the 'Category' column" mean? Total of strings?

   You can see another issue I'm having. Sorry. When I export to Excel instead of getting numbers I get the Printf formatting strings. Any idea how I fix that?
   

   Use "%0.0f" instead of "%0.0F".

   --
   myDBR Team
5. 

   ajdjackson, Member

   Hi

   Use "%0.0f" instead of "%0.0F"

   
   Worked great!

   Do not quite understand what you are trying to do. The Category column in your example seems contain strings ("Construction Costs", "Fees @ Sales" etc). What does "totals for the 'Category' column" mean? Total of strings?

   See image below. What I'm after is a total of the values per Category to appear in the summary level for that Company and also in the Grand Total level. For example -

   

   myDBR does, by default, make the column order the same as in your data i.e "order by"-order. In your query the order by is "c.tblCustomer_Name,b.Site_Name,a.CatID,a.CQTR"

   I'm a bit confused here. With the a.CQTR in the order by statement the quarters are not in order. Is it because there is data for all the Categories in all the Quarters? Should I fill the missing data with zeroes?

   Thanks again

   Anthony
6. 

   myDBR Team, Key Master

   See image below. What I'm after is a total of the values per Category to appear in the summary level for that Company and also in the Grand Total level.

   There is no such functionality which would pick unique data in one column (Category) and would produce a summary row from that. You can display that in a separate table.

   I'm a bit confused here. With the a.CQTR in the order by statement the quarters are not in order. Is it because there is data for all the Categories in all the Quarters? Should I fill the missing data with zeroes?

   The missing values is the reason. If you have data on all quarters, the quarter order will be logical. Take a look at documentation ("Predefine data columns"). It as exactly the same situation explained and how you do it with 'dbr.crosstab.col' command.

   Do not try to fill in the data with nulls/zeros, as this will only make your report more complex. The use of 'dbr.crosstab.col' is very simple.

   If you have multiple reports that use that kind of quarterly crosstab column ('Jan-15', 'Apr-15'), just make it a helper procedure and you can use it in multiple reports:

   call sp_quarters(in_start, in_end);

   The sp_quarters would then return the quarters between given dates with dbr.crosstab.col-command.

   --
   myDBR Team
7. 

   ajdjackson, Member

   Hi

   Thanks for help with this.

   I think I have it sorted regarding the QTR order.

   period_loop: loop

   set i=i+1;

   select 'dbr.crosstab.col',(date_format(MAKEDATE(YEAR(curdate()), 1) + INTERVAL i QUARTER - INTERVAL 1 DAY, '%b-%y'));

   IF i=12 THEN LEAVE period_loop; END IF;

   END LOOP period_loop;

   There is no such functionality which would pick unique data in one column (Category) and would produce a summary row from that. You can display that in a separate table.

   Pity but I thought as much. I could create the summary report but I would have liked to insert it as above. I'll look at a different layout.

   Cheers

   Jake

Reply

You must log in to post.