SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Combine columns under category group - Pie chart


Combine columns under category group - Pie chart

Author
Message
Papil
Papil
SSC Eights!
SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)

Group: General Forum Members
Points: 998 Visits: 362
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.
pietlinden
pietlinden
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51441 Visits: 16478
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
Papil
SSC Eights!
SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)

Group: General Forum Members
Points: 998 Visits: 362
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-

ACBDEFG
TestEx-1TestEx-4
TestEx-2TestEx-5
TestEx-3TestEx-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
pietlinden
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51441 Visits: 16478
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
Papil
SSC Eights!
SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)

Group: General Forum Members
Points: 998 Visits: 362
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
ACBDEFG
TestEx-1TestEx-4
TestEx-2TestEx-5
TestEx-3TestEx-5
Test1Ex-2Test1Ex-5
Test1Ex-2Test1Ex-5
Test1Ex-1Test1Ex-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
pietlinden
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51441 Visits: 16478
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
Papil
SSC Eights!
SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)

Group: General Forum Members
Points: 998 Visits: 362
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-
AC

TestEx-1

TestEx-2

TestEx-3

Test Ex-4
Test Ex-5
Test Ex-5
Test1Ex-2

Test1Ex-2

Test1Ex-1

Test1 Ex-5
Test1 Ex-5
Tes1 Ex-5
pietlinden
pietlinden
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51441 Visits: 16478
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search