1. 

   jjr, Member

   Hi, I wonder if anyone could help clarify something for me please:

   According to the documentation (bottom of the page here: http://mydbr.com/doc/content/cmd.crosstab.html)

   "The 'no_null_data' option allows suppressing null data in case where crosstab columns are defined with dbr.crosstab.col"

   I was expecting this to mean that if there was no data in that column in the resulting output table, then the column would not appear.

   Have I misunderstood? As using this option seems to make no difference and columns with no data still appear in the output table.

   (e.g. I specify "select 'dbr.crosstab.col', value1, value2;" but if there is no data for value2 that column still appears, empty)

   I notice that despite the description in the documentation, the example given on the page ref above doesn't even use the dbr.crosstab.col parameter, so seems to contradict the description.....

   Any help gratefully appreciated.

   Thanks.
2. 

   myDBR Team, Key Master

   Hi,
   the documentation has got it wrong. The no_null_data options suppresses a null crosstable column and can be used in cases where a null column is generated for example from left join. It has nothing to do with the dbr.crosstab.col. We'll fix the documentation, thanks for pointing it out.

   The dbr.crosstab.col can be used for two purposes: defining the order of the crosstab columns and for making sure that columns exists in the report even if it does not exist in the data.

   To define the column order and not to show nonexistent columns, just use dbr.crosstab.col with a join against your data.

   --
   myDBR Team
3. 

   jjr, Member

   Hi,

   Thanks for your quick response and that clears up the inconsistency and how dbr.crosstab.col should be used.

   The suggestion in your last sentence sounds like exactly what I'm after, but I'm still struggling to get it to work.

   Perhaps I've misunderstood how you are suggesting I use it.

   Are you saying that I should use a select statement to generate the column headings? This would be fine, but I can't get it to work and I can't find an example in the documentation about how to use select statements to feed variables into the "select 'dbr.crosstab.col', headings;" statement.

   Please can you give me an example of what you're suggesting (or point me to one in the documentation as I can't find it)?

   Thanks. I really appreciate the help and sorry if I've misunderstood.

   Justin.
4. 

   myDBR Team, Key Master

   You can use the dbr.crosstab.col in different ways:

   if you have a fixed set of column headings you wish to show, you can simply list them:

   select 'dbr.crosstab.col', '2016/Q1', '2016/Q2', '2016/Q3', '2016/Q4';
   
   

   
   If you wish to fetch them from the data, you can use a query:

   select 'dbr.crosstab.col', col_heading
   
   from data
   
   order by whatever;
   
   

   
   or you can combine both of them:

   select 'dbr.crosstab.col', col_heading
   
   from (
   
     select '2016/Q1' as col_heading
   
     union
   
     select '2016/Q2'
   
     union
   
     select '2016/Q3'
   
     union
   
     select '2016/Q4'
   
   ) h
   
   where h.col_heading in (
   
     select year_quarter
   
     from yourdata
   
   );
   
   

   --
   myDBR Team
5. 

   jjr, Member

   That's brilliant, thanks.

   In the end I needed to use a temporary table to select distinct values for the column headings and then used your example #2 on the temporary table, as I couldn't get 'DISTINCT' to work as part of the example directly (unless you can suggest otherwise).

   But I have it working now, so thanks very much.
6. 

   myDBR Team, Key Master

   To use distinct, you use:

   select distinct 'dbr.crosstab.col', col_heading
   
   from data
   
   order by whatever;
   
   

   --
   myDBR Team
7. 

   jjr, Member

   Thanks once again. This all works great.
   However, even now I know the syntax I still can't find explanation or examples in the documentation - so although my answer is completely answered and I'm very happy, I suggest that this detail might be very useful if it were described on the crosstab.col page/description.
   In any case tanks again and regards.
8. 

   myDBR Team, Key Master

   There are examples in the documentation. See Commads → Cross tabulation and search for dbr.crosstab.col.

   --
   myDBR Team
9. 

   jjr, Member

   Yes, this is the page I was looking at. However, with respect, the examples on this page are all just using constant values for setting crosstab.dbr and one that is set dynamically using a while loop, but there are no examples of using select statements to set crosstab.dbr from the data.
   No matter, it was only a suggestion and thanks for helping me find the answer I was looking for.

Reply

You must log in to post.