1. 

   ales85, Member

   Hello,

   please advise on how to create a report when a table is like this:
   Model, Color, Size, Value1, Value2, Value3, WorkOrder

   And it should look like this:
   Size(s) 37, 38, 39
   Model, Color, WorkOrder, Value1
   Value2
   Value3

   As you can see, Value's are aggregated in condition of Model, Color, WorkOrder and Size.
2. 

   myDBR Team, Key Master

   Hi,
   We could not quite get the idea you were looking for. Most likely our forum software reformatted your output. Try to use the code tag to skip formatting.

   --
   myDBR Team
3. 

   ales85, Member

   Sorry, I didn't notice.
   ________________________ Size(s) 37, 38, 39 Model, Color, WorkOrder, Value1 ________________________ Value2 ________________________ Value3

   Or If I put example with real values:
   ________________________ , 37, 38, 39 F1420, 07, D10112, Produced , 20, 30, 20 __________________ Sold , 14, 27, 20 __________________ Forecast , 25, 35, 15

   The table represents information about shoe production, sales and future sales.

   However the table information from database stands correct.

   EDIT: Code tag doesn't help.
4. 

   myDBR Team, Key Master

   OK, our forum software seems to disregard the spaces even in code.

   Assuming the Value1='Produced', Value2='Sold' and Value3='Forecast'

   You've got two options to use the crosstab in your data.

   In first option you make crosstab over size and let the value1-3 be data categories. In this setup you get one line per Model, Color, WorkOrder.

   select 'dbr.crosstab', 4;
   
   select Model,
   
          Color,
   
          WorkOrder,
   
          Size,
   
          Value1 as 'Produced',
   
          Value2 as 'Sold',
   
          Value3 as 'Forecast'
   
   from mydata;
   
   

   In a second option If you wish to split the each value category to own row :

   select 'dbr.crosstab', 5;
   
   select Model, Color, WorkOrder, 'Produced', Size, Value1
   
   from mydata
   
   union
   
   select Model, Color, WorkOrder, 'Sold', Size, Value2
   
   from mydata
   
   union
   
   select Model, Color, WorkOrder, 'Forecast', Size, Value3
   
   from mydata;
   
   

   Optionally you can set header levels to hide repetitive Model, Color and WorkOrder.

   HTH,
   --
   myDBR Team

Reply

You must log in to post.