on 01-08-2015 9:05 AM
Hi,
We have hit this issue where we want to show the Percent of Total in design studio crosstab.
Say i have data in below format which is attached to a cross tab. All this data is coming from the Data Set.
Company Sales | Sales | % Total |
---|---|---|
A | 30 | 30% |
B | 20 | 20% |
C | 40 | 40% |
D | 10 | 10% |
Total | 100 | 100% |
Now when i filter the data it shows up as below, see the percentage it is not showing now % of total
Company Sales | Sales | % Total |
---|---|---|
A | 30 | 30% |
B | 20 | 20% |
Total | 50 | 50% |
We actually want it to be shown as below
Company Sales | Sales | % Total |
---|---|---|
A | 30 | 60% |
B | 20 | 40% |
Total | 50 | 100% |
Is there a way to do this in Design Studio? Any help is much appreciated.
Regards,
vipul
Hi,
Design Studio Crosstab Component does not provide option to calculate new Column in it .
You can try following .
1) Create Custom Component for Tabular data which can provide you this facility.
Or
2) Calculate it at Data Source Level .
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Nikhil,
If i have understood correctly, I don't think the requirement is to add a calculated column to a crosstab. It is already being calculated in the data source. The issue seems to be that when a filter is applied, the calculated column in the data source is not recalculated based on the filtered rows.
Regards,
Mustafa.
there is no solution as of now, you have to build it in universe or bex query.
Regards,
vipul
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Just to refresh the thread
I have the same issue, did you guys find some solution for that ?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
BOE 4.1 SP2 Patch 1
DS 1.3 SP2
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
This is a universe based report.
If i have to write the query in universe then it will be like below
SELECT a1.State, a1.Sales, a1.Sales/(SELECT SUM(Sales) FROM Total_Sales) Pct_To_Total
FROM Total_Sales a1
But the problem comes when there is a where clause as seen below. I cant propagate it to both the select in the below query
SELECT a1.State, a1.Sales, a1.Sales/(SELECT SUM(Sales) FROM Total_Sales where State IN ("DC","TX")) Pct_To_Total
FROM Total_Sales a1
where State IN ("DC","TX")
So i assume Design Studio cant be used to represent such data.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Agree with Nikhil - calculate this at the BEx Query level
Design Studio does not support local calculations
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
83 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.