topic Re: Add Sum on column based on customer_id which cannot be used as a Group in Tablix in Report Server

Add Sum on column based on customer_id which cannot be used as a Group in Tablix

Anonymous — Sat, 29 Oct 2022 08:43:46 GMT

Hello,

I have a requirement where i want to check the number of customer who have transaction amt greater then 0, As Customer is a detail row it cannot be used in the tablix as i just need the number of customer's with positive transaction,

famillie	# customer with positve transaction
A	10
B	12
C	13

for example For famillie A there are 10 Customers with different transaction as positive as well as negative but their sum is positive.

i am trying to use an expression as

=Sum(IIf(Sum(Fields!CAAnne.Value,Fields!CUSTOMER_ID.Value)>0,1,nothing))

This gives me error as it is not in scope.

I hope i am able to explain my situation, please let me know if you have further queries.

Regards,

Arif

Re: Add Sum on column based on customer_id which cannot be used as a Group in Tablix

Anonymous — Mon, 31 Oct 2022 01:50:29 GMT

Hi Anonymous ,

In order to better understand your demands and give the right solution, could you please provide some more specific information? such as your desensitized example data and a screenshot of your desired results?

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_ Binbin Yu

Re: Add Sum on column based on customer_id which cannot be used as a Group in Tablix

Anonymous — Mon, 31 Oct 2022 05:19:27 GMT

Hello,

for a product family there are N customers who would have bought the product, There could be negative transaction as well. so for a customer who has 2 trnasction one is positive "10" and other is negative "(-20)" and if i sum them it would be -10, so i just want to count the distinct customer who has positive transaction after sum.

As my report tablix is at Product family level i cannot add customer as it very detail data, so i would like to have acondition to check the sum of Amount at customer and check if it is greater then 0 and if it greater then 0 then include the customer in the count.

Re: Add Sum on column based on customer_id which cannot be used as a Group in Tablix

Anonymous — Mon, 31 Oct 2022 06:00:53 GMT

Hi Anonymous ,

Could you please provide desensitized example data and a screenshot of your desired results?

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_ Binbin Yu

Re: Add Sum on column based on customer_id which cannot be used as a Group in Tablix

Anonymous — Mon, 31 Oct 2022 14:49:21 GMT

Hello Team,

I am currently getting below records when i use count distinct and check the records to be greater then 0

Famillie product	Cnt customer with Positive transaction
A	10
B	20
C	30

Now if you check the detail records for famille product A

Familllie	CAnne	Customer_id
A	10	A11
A	-9	A11
A	10	A12
A	15	A12
A	10	A13
A	16	A13
A	-33	A13
A	10	A14
A	20	A15
A	30	A15
A	40	A16
A	20	A17
A	10	A17
A	10	A18
A	70	A19
A	80	A19
A	90	A19
A	100	A19
A	10	A20

I should only get the customer count as 9 for Famillie product A as for customer "A13" the sum of CAnne will be negative.

Famillie product	CAnne	Customer_id	Material
A	10	A11	MAT_A
A	-9	A11	MAT_B
A	10	A12	MAT_A
A	15	A12	MAT_B
A	10	A13	MAT_A
A	16	A13	MAT_B
A	-33	A13	MAT_C
A	10	A14	MAT_A
A	20	A15	MAT_B
A	30	A15	MAT_C
A	40	A16	MAT_A
A	20	A17	MAT_B
A	10	A17	MAT_C
A	10	A18	MAT_A
A	70	A19	MAT_B
A	80	A19	MAT_C
A	90	A19	MAT_D
A	100	A19	MAT_E
A	10	A20	MAT_F

Can you please help!

REgards,

Arif

Re: Add Sum on column based on customer_id which cannot be used as a Group in Tablix

Anonymous — Wed, 02 Nov 2022 16:00:47 GMT

Can someone please help!