1. 

   mhennahane, Member

   I have a crosstab report where I tally up cluster usage by login name by date. My crosstab column is the DATE part of a timestamp, and the report looks great, except...

   The crosstab columns do not come out in chronological order. Is there some way that I can tell myDBR to sort my crosstab columns in order?

   My report code:

   CREATE PROCEDURE


   _

   ()
   BEGIN

   select 'dbr.crosstab', 4;
   select 'dbr.crosstab.title', 'Total';
   select 'dbr.hdr', 2;
   select 'dbr.sum', 5;
   select 'dbr.hsum', 5;
   select 'dbr.nosort';

   select o.olive_host_ip as host, o.olive_cluster as cluster,
   o.login_name as login, DATE(o.create_date) as date, count(*) as minutes
   from olive_stats.olive_stats o
   where DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= o.create_date
   group by host, cluster, login, date;

   END
2. 

   myDBR Team, Key Master

   If you do know that all columns you wish to have in the report exists in data, just add an order by-clause to the query. myDBR will fill the crosstabulation columns based on occurrance on result set.

   If your data does not include all the columns you wish to have (some dates are missing in data). You can use the 'dbr.crosstab.col' to predefine the columns which you want to appear in the report.

   --
   myDBR Team
3. 

   mhennahane, Member

   The GROUP BY portion of my query precludes using ORDER BY, so I will see about using the dbr.crosstab.col command. Can I use the result of a SELECT statement to define my crosstab columns? For example, I can use the same WHERE clause to get a sorted distinct list of what the columns will be:

   select 'dbr.crosstab.col', SELECT distinct date (o.create_date) from olive_stats.olive_stats o where DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= o.create_date;

   But, this does not seem to be allowed.
4. 

   myDBR Team, Key Master

   GROUP BY and ORDER BY are not mutually exclusive. You can use them in same query.

   Also, using query with 'dbr.crosstab.col' is certainly possible, you just have two selects in same query. As said though, dbr.crosstab.col is used when the original data does not include the columns you wish to include in the report.

   --
   myDBR Team

Reply

You must log in to post.