how to concat Ids of an entity in aggregate with Group By

I am grouping user entity with gender and finding the total. so in aggregate this 2 columns should be the output. But i need Ids of both Male and Female users. 


Like this

Female    4     5558,5559,5562,5573

Male        2     5557,5560

Naveen Raj wrote:

I am grouping user entity with gender and finding the total. so in aggregate this 2 columns should be the output. But i need Ids of both Male and Female users. 


Like this

Female    4     5558,5559,5562,5573

Male        2     5557,5560


Hi Naveen,

Check the attached oml

Use a list in a webblock

In the aggregation of source of list ,add a filter (userextended.genderid = genderid(input varible of genderid datatype))

use this webblock inside your list in a webscreen and pass the genderid of current row.

hope it helps


Seema Pandey wrote:

Naveen Raj wrote:

I am grouping user entity with gender and finding the total. so in aggregate this 2 columns should be the output. But i need Ids of both Male and Female users. 


Like this

Female    4     5558,5559,5562,5573

Male        2     5557,5560


Hi Naveen,

Check the attached oml

Use a list in a webblock

In the aggregation of source of list ,add a filter (userextended.genderid = genderid(input varible of genderid datatype))

use this webblock inside your list in a webscreen and pass the genderid of current row.

hope it helps



Hi Seema,

         Thanks for the Quick reply. 

         Actually I dont want to display the Ids of Male and Female. I want to pass Ids to somewhere else. Your Idea is WebBlock, correct but in parent screen aggregate lot of conditions i added and filtering male and female count.  If i created webblock, there also i have to filter with that all conditions. So what i need is 

Output Like this in aggregate

Female    4     5558,5559,5562,5573

Male        2     5557,5560


Hi Naveen,

It will not be possible in the same aggregate. You have to create another entity and create 4 column resp. Gender, Count, extendedID. Use logic to store the same category value in a list. then input that into the Entity.

In this way each time data will be updated as per the Parent Entity table.

I hope this will help you,

Thank you,

Sudip

Hi Naveen,

You can also use a server action (as a function), with an output of datatype (list of UserId) and input parameter genderId.

Use filter in the aggregation with genderId and You will get List of UserId.

Regards

Seema

Naveen Raj wrote:

I am grouping user entity with gender and finding the total. so in aggregate this 2 columns should be the output. But i need Ids of both Male and Female users. 


Like this

Female    4     5558,5559,5562,5573

Male        2     5557,5560

Hi Naveen,

In OS aggregate not possible to use all function of sql , try Adv.SQL for your query...

I have provide solution for your similar type of output . 

SyntaxEditor Code Snippet

Select  {Gender}.[Label] AS B ,substring((
SELECT ',' + CAST(Name AS VARCHAR) FROM  {EMp} where {EMp}.[GenderId] IN (select {Gender}.[Id] from {Gender} Where {Gender}.[Label]='Female')
FOR XML PATH('')),2,1000) as AAA ,  COUNT ({Gender}.[Label]) from {EMp} 
inner join {Gender} on {Gender}.[Id] = {EMp}.[GenderId] where {Gender}.[Label] = 'Female' 
group by {Gender}.[Label]

UNION

Select  {Gender}.[Label] AS B ,substring((
SELECT ',' + CAST(Name AS VARCHAR) FROM  {EMp} where {EMp}.[GenderId] IN (select {Gender}.[Id] from {Gender} Where {Gender}.[Label]='Male')
FOR XML PATH('')),2,1000) as AAA ,  COUNT ({Gender}.[Label]) from {EMp} 
inner join {Gender} on {Gender}.[Id] = {EMp}.[GenderId] where {Gender}.[Label] = 'Male' 
group by {Gender}.[Label]

  Above sql query show the output like this....

NOTE: Add structure with three attribute

 

Hope this solution is helpeful for you....

Thanks

Nikhil

Sudip Dey wrote:

Hi Naveen,

It will not be possible in the same aggregate. You have to create another entity and create 4 column resp. Gender, Count, extendedID. Use logic to store the same category value in a list. then input that into the Entity.

In this way each time data will be updated as per the Parent Entity table.

I hope this will help you,

Thank you,

Sudip

Hi Thanks for your response