nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Help with crosstab

bsaders

Hi All: 
 
I'm trying to understand Crosstabs, but having difficulty doing something pretty basic and would like some help. 
 
I have a very basic table, that list orders and returns. For instance, the appropriate fields are ORDER_DATE, IS_RETURN, QTY. 
 
So, we could have a listing such as this: 
 
1-1-2011, false, 10 
1-2-2011, false, 5 
1-2-2011, true, 1 
1-3-2011, false, 20 
1-3-2011, true, 2 
 
I want a crosstab report that will mimic something we are already doing in Excel, which is to display horizontally each day as a column and then the number of orders in returns. 
 
I have built the Data Sets and the Data Cubes that correctly display this information, but I cannot get each distinct item to display on a new row (instead it splits the column). 
 
What I want is: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
Day 1/1/2011 1/2/2011 1/3/2011
Ordered 10 5 20
Returned 0 1 2
</pre>
 
However, instead it winds up breaking the column into a multi-column. 
 
I feel I'm missing something pretty basic, but cant find a way around it? 
 
Regards.

Find more posts tagged with

Comments

thuston

The Date group should be on the Column section.
The Ordered/Returned group should be the Rows.
The Integer Measure in the Data area.

Can you post a screenshot of what it is doing that you don't want?

bsaders

Thanks for the reply.

Here is a screen cap of both the design screen and the result.

Additionally, he is a mock-up in a spreadsheet of want I'm trying to achieve.

Regards

bsaders

Ok, I have followed your advice and it works - but it resulting in a vertical listing (ie. each date is a new row).

I want each day to go across as a new column (like a calendar format). Perhaps crosstab is wrong way to go?

thuston

In your DataSet, do you have separate rows for ordered and returned, or can one data row have both values?

For the crosstab they should be separate rows. Then you can group on the two values of a single data column.

From the screenshot it looks like they are simply aggregates. In this case a Listing with Inline enabled may be a better option.