Combine columns under category group - Pie chart

Question

Combine columns under category group - Pie chart

Papil

SSCommitted

Points: 1983
More actions
February 9, 2018 at 1:50 pm

#333453

Hi,
I have a pie chart thats taking data from one of the dataset. My dataset has around 7 columns. Columns-A,B,C,D,E,F,G
In Pie chart data under values- I am taking the count of two columns under values(= Count(A)+Count(B)). But for Category group i want to combine the two columns data as well column C and Column D. Can i combine the two columns data?
Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

Papil - Friday, February 9, 2018 1:50 PM
Hi,
I have a pie chart thats taking data from one of the dataset. My dataset has around 7 columns. Columns-A,B,C,D,E,F,G
In Pie chart data under values- I am taking the count of two columns under values(= Count(A)+Count(B)). But for Category group i want to combine the two columns data as well column C and Column D. Can i combine the two columns data?
Thanks.

maybe create a calculated field in your dataset = Fields!A.Value + Fields!B.Value + Fields!C.Value + Fields!D.Value, and then use that calculated field instead of Fields A-D. (I do this if I want to lump a bunch of the fields together (say if they make up a really tiny percentage of the whole).

Papil SSCommitted Points: 1983 More actions · Answer 2

pietlinden - Friday, February 9, 2018 10:52 PM
Papil - Friday, February 9, 2018 1:50 PM
Hi,
I have a pie chart thats taking data from one of the dataset. My dataset has around 7 columns. Columns-A,B,C,D,E,F,G
In Pie chart data under values- I am taking the count of two columns under values(= Count(A)+Count(B)). But for Category group i want to combine the two columns data as well column C and Column D. Can i combine the two columns data?
Thanks.
maybe create a calculated field in your dataset = Fields!A.Value + Fields!B.Value + Fields!C.Value + Fields!D.Value, and then use that calculated field instead of Fields A-D. (I do this if I want to lump a bunch of the fields together (say if they make up a really tiny percentage of the whole).

I tried calculated field but it does not work for my case.

My data is like below-

A	C	B	D	E	F	G
Test	Ex-1	Test	Ex-4
Test	Ex-2	Test	Ex-5
Test	Ex-3	Test	Ex-5

i am doing count of A and B on the value property of pie chart. In the category group i want to combine C and D columns.

So the slices of pie should show count- 1 for Ex-1, 1-Ex-2,1 for Ex-3,1-Ex-4,2 for Ex-5 on every slice.

pietlinden SSC Guru Points: 63503 More actions · Answer 3

That looks more like the underlying table is set up wrong. Either that or your example is misleading.
If your table is set up wrong, a query like this might fix it:
SELECT A, C FROM Src UNION ALL SELECT B, D FROM Src

Papil SSCommitted Points: 1983 More actions · Answer 4

pietlinden - Monday, February 12, 2018 7:52 AM
That looks more like the underlying table is set up wrong. Either that or your example is misleading.
If your table is set up wrong, a query like this might fix it:
SELECT A, C FROM Src UNION ALL SELECT B, D FROM Src

A	C	B	D	E	F	G
Test	Ex-1	Test	Ex-4
Test	Ex-2	Test	Ex-5
Test	Ex-3	Test	Ex-5

Test1	Ex-2	Test1	Ex-5
Test1	Ex-2	Test1	Ex-5
Test1	Ex-1	Test1	Ex-5

Data is correct in the table.I am using a Matrix in my report so it groups on every value of column A. Can i do something at the ssrs side?

pietlinden SSC Guru Points: 63503 More actions · Answer 5

What does the raw data look like? The results of a Matrix are useless if you're trying to create a chart - you need the unpivoted data.

Papil SSCommitted Points: 1983 More actions · Answer 6

pietlinden - Monday, February 12, 2018 12:01 PM
What does the raw data look like? The results of a Matrix are useless if you're trying to create a chart - you need the unpivoted data.

Looks Like below-

A	C
Test	Ex-1
Test	Ex-2
Test	Ex-3

Test Ex-4
Test Ex-5
Test Ex-5

Test1	Ex-2
Test1	Ex-2
Test1	Ex-1

Test1 Ex-5
Test1 Ex-5
Tes1 Ex-5

pietlinden SSC Guru Points: 63503 More actions · Answer 7

Can't you take my union query (or similar) as the source, and then use COUNT() as the aggregate at the intersection? Something like A would be on Columns, C would be on Rows, and then COUNT(*) would be at the intersection.

Any chance you could post some realistic (but not necessarily real) data? I don't want to know your data - I just need to figure out from a reasonable example what's going on, because without it, I don't think I can help.