Crosstab sum the values of irrelevant columns into one column

Hi all, 
 
I have the following problem.. I have a crosstab let say sales by country and product: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>Product1 Product2 Product3 Product4 Product5 Product6 .. ProductN
Country1 260 230 120 10 6 1 0
Country2 180 145 89 4 1 0 0
Country3 125 123 53 1 0 0 0
Country4 70 30 12 2 0 0 0
Country5 10 5 6 3 0 0 0
..
CountryN 1 2 1 0 0 0 0
Sum Country 646 535 281 20 7 1 0</pre>
 
I would like to sum the values of all columns that sum country is =< 20 and put them into a new column called Others: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>Product1 Product2 Product3 Others
Country1 260 230 120 17
Country2 180 145 89 5
Country3 125 123 53 1
Country4 70 30 12 2
Country5 10 5 6 3
..
CountryN 1 2 1 0
Sum Country 646 535 281 28</pre>
 
I have no clue how to achive that. Any tip would be appreciated. 
Thanks

Find more posts tagged with

Comments

Yogesh Maharwade

hi
To achieve this ,better to do it on query side.

mmarto

<blockquote class='ipsBlockquote' data-author="'piwya'" data-cid="83938" data-time="1318330666" data-date="11 October 2011 - 03:57 AM">
hi 
To achieve this ,better to do it on query side. </blockquote>
 
The problem is that on the query level I don't have the sum for all countries. It is done later by the crosstab. So beforehand I don't have a way of knowing which product generated less than 20 sales for all countries

mwilliams

If you aren't able to get your query to give you the correct data, you could always store the values from a grouped table and dataSet or from your crosstab into arrays and then use this data to create a new dataSet that changes the "product" to "other" if the totals you've gathered aren't above 20. Then you could use this scripted dataSet as your cube source.

b_ralu_83

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="84192" data-time="1318869034" data-date="17 October 2011 - 09:30 AM">
If you aren't able to get your query to give you the correct data, you could always store the values from a grouped table and dataSet or from your crosstab into arrays and then use this data to create a new dataSet that changes the "product" to "other" if the totals you've gathered aren't above 20. Then you could use this scripted dataSet as your cube source. </blockquote>
 
Hello, 
I am into a similar situation, i need to display a subtotal (with some conditions). I can do this directly in my query, but in this case my report will become very slow and will take too much time. So your method sounds good. 
Can you detail please how can be possible to store the values from a crosstab into an array? And after that i can create a new data set using this array? 
Thank you

Hans_vd

Hi Mmarto, 
 
If your database supports analytical functions you could write a query like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT country,
CASE
WHEN sum(sales) over (PARTITION BY country, product) < 20 THEN 'Others'
ELSE product
END AS product,
sales
FROM your_table</pre>
 
No extra work needed, you'll get the "Others" column automatically in the crosstab results 
 
Regards 
Hans