This is a migrated thread and some comments may be shown as answers.

counting with condition

3 Answers 175 Views
Report Designer (standalone)
This is a migrated thread and some comments may be shown as answers.
Pinkesh
Top achievements
Rank 1
Pinkesh asked on 12 Jan 2017, 08:54 PM

sample query result:

 

ID       level          [...]

--------------------------------

00a     level 1,      [...]

00a     level 1,      [...]

00b     level 3,      [...]

00b     level 3,      [...]

00d     level 2,      [...]

00e     level 1,      [...]

00f     level 2,      [...]

00e     level 1,      [...]

 

it should display: (PLEASE refer to the attached screenshot)

level 1: 2     level 2: 2     level 3: 1

 

So I'm trying to count the number of unique ID for each level.

 

 

My attempt:

Group them by level, then do a CountDistinct on the ID:

For level1 [=CountDistinct(IFF(Fields.level = "level 1", Fields.ID, 0)) - 1]

For level2 [=CountDistinct(IFF(Fields.level = "level 2", Fields.ID, 0)) - 1]  

-1 is for all the 'false' count

However this didnt work, it will always return 0.

 

Any advice?

 

 

3 Answers, 1 is accepted

Sort by
0
Pinkesh
Top achievements
Rank 1
answered on 12 Jan 2017, 08:55 PM
sorry forgot the image
0
Pinkesh
Top achievements
Rank 1
answered on 12 Jan 2017, 08:56 PM

Cant seem to attach image:

http://imgur.com/7U6ESSI

0
Katia
Telerik team
answered on 13 Jan 2017, 10:08 AM
Hi Pinkesh,

The described scenario can be achieved with a  item - check the attached report demonstrating the above scenario (can be opened with Standalone Designer R3 2016 and later).

The CrossTab in the sample has column groups which allow the aggregate function CountDistinct() to be executed against the set of data (all ID values in the current group).

Hope this will help.


Regards,
Katia
Telerik by Progress
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
Tags
Report Designer (standalone)
Asked by
Pinkesh
Top achievements
Rank 1
Answers by
Pinkesh
Top achievements
Rank 1
Katia
Telerik team
Share this question
or