Report Studio: Is it possible to filter on a data item, not a query? 1

[ksolutions]

I need to provide data regarding orders by channel. Orders are in [Order Nr] and the channel that they come in on is defined in [Order Channel]. [Order Nr] is the customer's order number, and [Order Channel] can be Online, Phone, Mail, etc.

I can calculate the overall total count of orders as:
total(count(distinct[Order Nr]))

But how do I filter the orders to show only those for Online, Phone, etc - like the following:

if [Order Channel] = 'Online' then total(count(distinct[Order Nr]))

I need to create these calculations in the same query, so I can't filter a query - I need to filter a data item. I will need to add them together later on - that is why they can't be in separate query items.

Is this possible?

 

[Nuffsaid]

Hi,

You're so close....

Create the following calculations or data items;

[Online]

if [Order Channel] = 'Online' then total(count(distinct[Order Nr]))
else
0

[Phone]

if [Order Channel] = 'Phone' then total(count(distinct[Order Nr]))
else
0

.
.
.
ETC

Then later on..... [Online]+[Phone]= endless posibilities!!

Hope I read it right....

Nuffsaid.

 

[ksolutions]

Unfortunately it doesn't work - I keep adding a bunch of parenthesis to fix the problem and finally ended up with this:

if ([Order Channel]='Online') then (total(count(distinct[Order Nr]))) else 0

but I get an error saying "there was a parsing error before or near position: 77, text starting at position: 7 "der Channel]='Online') then (total(count(distinct[Order Nr]))) else 0

Arrrgh! Thanks for your attempt to help Got any other ideas?

 

[Nuffsaid]

I've found report studio to be "very picky" when it comes to syntax. Your formula looks correct except for the ELSE statement. Try wrapping the zero in () else (0)

Nuffsaid.

 

[ksolutions]

It works! I ended up with the following:

if ([Order Channel]='Online' ) then
(total(count( distinct [Order Nr]))) else (0)

Thank you so much for your help. You're right, report studio is very picky!

 

[Nuffsaid]

I'm glad it worked out for ya..

BTW, thanks for the *, it's my first in this forum.

Nuffsaid.