Post Closed
35
Views
3
Comments
Solved
Retrieve number of users per application
Application Type
Traditional Web

Hi everyone!


I need help with this issue,


I'm trying to retrieve the number of users that exists per application in a specific environment.

I don't know if its the right way, but I'm trying via existing roles on the same application.

The problem is, if I group by application (in this example, name), what happens is that if a user as two different roles it will give me a wrong information.


Ex.:


Can anyone help me with this please?

Best regards!

Diogo

mvp_badge
MVP
Solution

You are close, but it isn't possible this way because you can't distinct count in an Aggregate; so you need to go to a SQL node. (see this Idea)

Also, there is the App_Definition_Module entity missing, because the Roles could be defined in another module than the Entry_eSpace_Id. The system data model is looking this way:


So the SQL statement should look something like this:

SELECT          {APPLICATION]}.[NAME]
,               COUNT( DISTINCT {User}.[Id] )
FROM            {Application}
Inner JOIN      {App_Definition_Module} 
ON              {Application}.[Id] = {App_Definition_Module}.[Application_Id]
AND             {Application}.[Is_Active] = 1
Inner JOIN      {Module} 
ON              {App_Definition_Module}.[Module_Id] = {Module}.[Id]
Inner JOIN      {Role} 
ON              {Module}.[Espace_Id] = {Role}.[Espace_Id]
AND             {Role}.[Is_Active] = 1
Inner JOIN      {User_Effective_Role} 
ON              {Role}.[Id] = {User_Effective_Role}.[Role_Id]
Inner JOIN      {User} 
ON              {User_Effective_Role}.[User_Id] = {User}.[Id]
AND             {User}.[Is_Active] = 1
GROUP BY        {Application}.[Name]

Hallo Diogo , 

                    try to select users with max rule code per application you will get the user distance per application so the count will be more accurate  if you  can attach sample of you application data will make query more easy to define 


mvp_badge
MVP
Solution

You are close, but it isn't possible this way because you can't distinct count in an Aggregate; so you need to go to a SQL node. (see this Idea)

Also, there is the App_Definition_Module entity missing, because the Roles could be defined in another module than the Entry_eSpace_Id. The system data model is looking this way:


So the SQL statement should look something like this:

SELECT          {APPLICATION]}.[NAME]
,               COUNT( DISTINCT {User}.[Id] )
FROM            {Application}
Inner JOIN      {App_Definition_Module} 
ON              {Application}.[Id] = {App_Definition_Module}.[Application_Id]
AND             {Application}.[Is_Active] = 1
Inner JOIN      {Module} 
ON              {App_Definition_Module}.[Module_Id] = {Module}.[Id]
Inner JOIN      {Role} 
ON              {Module}.[Espace_Id] = {Role}.[Espace_Id]
AND             {Role}.[Is_Active] = 1
Inner JOIN      {User_Effective_Role} 
ON              {Role}.[Id] = {User_Effective_Role}.[Role_Id]
Inner JOIN      {User} 
ON              {User_Effective_Role}.[User_Id] = {User}.[Id]
AND             {User}.[Is_Active] = 1
GROUP BY        {Application}.[Name]

Thanks everyone for the help!

Its resolved!