Multiple column in "Grand total column"

Hi everyone, 
I need your help to work with crosstab. 
I have a dataset that retrieve publications with a year, a category and a score. 
 
Example : 
<pre class='_prettyXprint _lang-auto _linenums:0'>
Publication ID | Year | Category | Score
123456789 | 1995 | A | 5
123456788 | 1996 | B | 1
123456878 | 1996 | D | 8
</pre>
 
Then I created a datacube with two dimensions : 
- Year 
- Category 
And two measures : 
- number of publications (COUNT) 
- score (SUM) 
 
Then I create the crosstab with Year for rows, and Category for columns and display the number of publications for each intersection. 
I generate grand total, and get the total number of publications for each rows, but I would like to display, in another "grand total" colums, the sum of the score for each rows. 
 
In the first image, it's the crosstab that I expect. 
In the second image, I have the expected "grand total" column, but I also have some undesirable columns in details columns. 
In the third image, I have th expected result, but the two totals are in the same column =(. 
 
How can I do this ? 
 
Best regards, 
PommePote 
 
PS : Sorry for my bad english.

Find more posts tagged with

Comments

mcremer

<blockquote class='ipsBlockquote' data-author="'PommePote'" data-cid="82083" data-time="1314708129" data-date="30 August 2011 - 05:42 AM">
Hi everyone, 
I need your help to work with crosstab. 
I have a dataset that retrieve publications with a year, a category and a score. 
 
Example : 
<pre class='_prettyXprint _lang-auto _linenums:0'>
Publication ID | Year | Category | Score
123456789 | 1995 | A | 5
123456788 | 1996 | B | 1
123456878 | 1996 | D | 8
</pre>
 
Then I created a datacube with two dimensions : 
- Year 
- Category 
And two measures : 
- number of publications (COUNT) 
- score (SUM) 
 
Then I create the crosstab with Year for rows, and Category for columns and display the number of publications for each intersection. 
I generate grand total, and get the total number of publications for each rows, but I would like to display, in another "grand total" colums, the sum of the score for each rows. 
 
In the first image, it's the crosstab that I expect. 
In the second image, I have the expected "grand total" column, but I also have some undesirable columns in details columns. 
In the third image, I have th expected result, but the two totals are in the same column =(. 
 
How can I do this ? 
 
Best regards, 
PommePote 
 
PS : Sorry for my bad english. </blockquote>
 
If I understand it correctly you created the Score demantion, rite? If posible could you add the report so we can see what you are atempting.

PommePote

<blockquote class='ipsBlockquote' data-author="'mcremer'" data-cid="82085" data-time="1314708693" data-date="30 August 2011 - 05:51 AM">
If I understand it correctly you created the Score demantion, rite? If posible could you add the report so we can see what you are atempting. </blockquote>
 
Hi mcremer, 
 
Not really, I think the score It's a measure. 
 
Here a better explanation : 
For each intersection between a year and a category, I have 2 measures : 
- The number of publications for the designated year and for the designated category 
(i.e. : in 1995, in the A category there are 2 publications, and in 1995, in the B category, there are 2 publications) 
- The score depending on the year, the category and the number of publications 
(i.e. : in 1995, in the A category there are 2 publications so the score is 2 * 4 points, and in 1995, in the B category, there are 2 publications so the score is 2 * 3 points) 
 
But I need to display only the number of publications. 
 
Then, in the first last column, I need to display the total number of publications for the year (here It would be 4 publications) and in the second last column, I need to display the score for the year (here It would be 14 points). 
 
Thanks you. 
 
PS : I'll post my try as soon as possible. 
 
PommePote

PommePote

<blockquote class='ipsBlockquote' data-author="'mcremer'" data-cid="82085" data-time="1314708693" data-date="30 August 2011 - 05:51 AM">
If I understand it correctly you created the Score demantion, rite? If posible could you add the report so we can see what you are atempting. </blockquote>
 
Ok here an example with the SampleDB : 
I would like to display the number of orders by customer and year, and display the total number of orders and also the total payment amont by customer in the "grand total" column. 
 
So my first try (the first crosstab) : I add the "grand total" column with the total number of orders and then I add an aggregation in the just-created column that computed the payment amont. 
 
My second try (the second crosstab) : I add the two measures (number of orders and payment amont), I then can generate the two "grand total" columns and I finally hide (Using the Visibility Tab) the "payment amont" measure. 
 
PommePote 
 
#EDIT : I just find a little workaround, I just set the width of the undesirable column to "0px" (to hide it definitely), but It's not the best way to do this.

mcremer

<blockquote class='ipsBlockquote' data-author="'PommePote'" data-cid="82089" data-time="1314711622" data-date="30 August 2011 - 06:40 AM">
Ok here an example with the SampleDB : 
I would like to display the number of orders by customer and year, and display the total number of orders and also the total payment amont by customer in the "grand total" column. 
 
So my first try (the first crosstab) : I add the "grand total" column with the total number of orders and then I add an aggregation in the just-created column that computed the payment amont. 
 
My second try (the second crosstab) : I add the two measures (number of orders and payment amont), I then can generate the two "grand total" columns and I finally hide (Using the Visibility Tab) the "payment amont" measure. 
 
PommePote 
 
#EDIT : I just find a little workaround, I just set the width of the undesirable column to "0px" (to hide it definitely), but It's not the best way to do this. </blockquote>
 
Ok now I get it. 
 
What I sugest is NOT to use a cube. 
 
In your case smart querying and a smart query together with a Table with grouping will give you a way better controlled result and also a nicer layout.

PommePote

<blockquote class='ipsBlockquote' data-author="'mcremer'" data-cid="82090" data-time="1314712838" data-date="30 August 2011 - 07:00 AM">
Ok now I get it. 
 
What I sugest is NOT to use a cube. 
 
In your case smart querying and a smart query together with a Table with grouping will give you a way better controlled result and also a nicer layout. </blockquote>
 
Okay I'll try, but, if we use my last example, how can I generate the header columns (dimension year) with all years that appears in the DataSet ? 
 
Thanks you, PommePote

mcremer

Pompe,

You can do this by example trough dynamic DataSet Scripting (i'm writing a blog post about that). The bigger problem is in the fact of making a table work in a manner you want (eg give it a some wat Cube like options). When I finished making the blog post ill add the link here. And ill think about how to do the dynamic thing in the table.