Solved

Grouping

Posted on 2013-05-15
32
231 Views
Last Modified: 2013-05-31
Hi -
I'm learning something new at work.  Joy.  =)   In all seriousness, I like crystal reports... just can't figure a group by issue.  I'm hitting two SQL tables.  Here's my command code:
SELECT         A.AccountName,
                    A.DisplayName,
                    A.DistinguishedName,
                    A.Type,
                   B.ID,
                   B.MEMBEROF,
                   B.USERNAME,
                   B.USERSID
FROM         ADUsersAndGroups A,
                   GROUPS B
WHERE     A.DistinguishedName = B.MEMBEROF

In the crystal reports grouping feature, I am using the follwing:
Group by AccountName (this column has users and groups).
In the detail I would like to list the users to each group,
so I added B.Username (which is like the ID) and the A.DisplayName (description or full name of group/user)

My output:
Group:  Groupname
                    Username(ID)   DisplayName

But here's my problem... the display name used on the detail gives me the group name again and not the full name for the user Id.      I currently do not have any of the tables linked.   Any suggestions would be greatly appreciated.... im in the 23rd hour and it's all starting to run together.   I just know I'm close... just cant quite get there from here.  Thanks!!
0
Comment
Question by:mojeaux
  • 11
  • 9
  • 7
  • +1
32 Comments
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Have you tried using the UserName field from B?

mlmcc
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
I think the issue here is that it's not the sql itself that will achieve the desired output. SQL will "repeat" data in a column and that's just the way it works.

I think you are trying to "suppress" the Groupname when you list each member of that group - this is should be done in Crystal (somehow, afraid I don't know precisely how).

In short, the query is doing what it does, but it does not produce "the report".

In Crystal look for control properties that 'hide' things
(I think, been a century or two since I used similar tools)
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
oh, should have asked, what type of database is it? Oracle/SQL Server/???

we might come up with some "sql way" but it would help to know which database
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
this might help in the meantime (i.e. the Crystal way)

http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=3982
0
 

Author Comment

by:mojeaux
Comment Utility
Database is SQL server.  I'm running crystal reports client side.  Set up my odbc connections to connect to SQL server.

Sorry but not trying to suppress group name...need it to show.  I just need the user ID display names to appear in the detail of the report and not the group names .  The amount name field lists both users and groups.  How do I use it to group by and the to also list the detail?

Thx!
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
afraid I don't follow:

>>not trying to suppress group name...need it to show. ...  not the group names
from this I am a bit confused

but: let's assume you do want the groupname, then

>>I ... need the user ... display names to appear
the only field shown to us here that looks like a user's name is B.USERNAME and that is already in your query
??are there other fields in B like "FirstName" "LastName" -- we don't know this

>>The amount [account] name field lists both users and groups
OK, think I get that bit

>>How do I use it [the account name] to group by and the to also list the detail?
mmm

First I guess you must order the information, so it like something this:

select account_name group_name username...
a1 g1 u1
a1 g1 u2
a1 g1 u3
a1 g2 u44
a2 g3 u555

In Crystal, "suppress" account name if repeated
a1 g1 u1
     g1 u2
     g1 u3
     g2 u44
a2 g3 u555

and, in Crystal, "suppress" group name if repeated (optional?)
a1 g1 u1
            u2
            u3
     g2 u44
a2 g3 u555

paraphrasing from the URL I provided earlier:
1.  Group by account name.  Put the account name in the group header and the username in the details.  If the data must be lined up ..., go to the Section Expert and turn on "Underlay Following" for the account name header section.
 
2.  Right-click on the account name in your report and select "Format Field".  On the Common tab there should be an option to "Suppress if duplicated".
I did mention: I'm not a Crystal expert
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Did you try my idea?

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
I'm not sure what's going on, but I have a few questions.

 You said that you don't have the tables linked.  Why not?  They obviously should be linked, and it may be required for the report to work properly.


 You said:
 > Group by AccountName (this column has users and groups).

 That field contains the names for groups _and_ users?  If so, and if you're not somehow limiting the report data to include only groups in that field (not users), then your report will include a group for each user and group name.


  When you say "the display name used on the detail gives me the group name again", do you mean that the detail section shows the group name?  That's presumably because the name field that you're using in the detail section is used for users and groups (as mentioned above).

 Are you seeing _only_ the group name in the detail section, or are you seeing the group name in addition to the user names (eg. one line has the group name and the other lines have the user names)?


 My best guess at this point is that you have a table that includes records for users and groups, and because you're not distinguishing which is which, you're getting both users and groups in the details.  But that's just a guess, based on my interpretation of some of the things you've said.

 James
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
The tables are linked just not in the traditional sense.  The where clause does the linking and it is an INNER JOIN

He needs to define what the fields represent then we can determine how to get what he wants displayed.

mlmcc
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
ahh
>>The tables are linked in the traditional way (via the where cause).
ANSI joins come later
:)
I'd happily see that join style capability removed, but there's legacy code to support I guess
0
 

Author Comment

by:mojeaux
Comment Utility
Hi - thank you for the quick responses.   I will be on later tonight to read through these.    

Mlmcc:  I will try to give you a better description of the tables....

Table1
AccountName: Can be an alias for User or group
DisplayName: Description of AccountName
Type: User or Group
DistinguishedName:  String data that lists groups that the Users belong to

Table2
MemberOf:  String data that lists groups the Users belong to
Username: Aslias for User

Output:
AccountName (for group only)
                   Username         Displayname (for User only)

How do I get there from here within Crystal reporting?  The output should group on Accountname if its a group type that begins with the letter H.   The Detail should list all users that belong to that accountName.

Thanks!   Hope this clears up what I was asking earlier.
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
So, Table1.Type tells you whether the entry is a group or a user?

 If so, you may want to include Table1 twice.  CR will let you do that.  By default, it will add "_1" (something like that) to the alias for the second table, so that you can reference each of them in the report.  In your record selection formula, you'd have something like:

{Table1.Type} = "Group" and {Table1_1.Type} = "User"

 So the first instance of Table1 would only include groups and the second would only include users.

 But you'd need to link them together (to get the users that belong to each group), and I'm not sure about that part.

 Table1.AccountName is the group name (when Type = "Group")?

 Normally you'd have a separate entry somewhere for each user and group.  Something like:
User1   Group1
User1   Group2
User2   Group3
User3   Group1
User3   Group4

 Do you have anything like that?

 Is DistinguishedName a single field for a user that contains the names for all of the groups they belong to?  Using the above example, something like:
User1   Group1,Group2
User2   Group3
User3   Group1,Group4


 When you said "group type that begins with the letter H", what's a "group type"?  Is that another field?  Or did you maybe mean "group name"?  Or was I wrong about what Table1.Type is used for?

 James
0
 

Author Comment

by:mojeaux
Comment Utility
Hi James,
Thank you for posting... and I'm sorry about the delay in my response.   I was called out of town unexpectedly.   Back at work now.   =)  

I will try to answer all your questions in the the order they were asked.... If I dont clear anything up, let me know.   I will be happy to re-address.   Did not know about creating two commands for the same table... but like where you were going with that.   Great concept.   I'm going to give this a try today!

"So, Table1.Type tells you whether the entry is a group or a user?" - Yes, it has values of either U or G which determine if it's a user or group.

"Table1.AccountName is the group name (when Type = "Group")?" - Yes, this is true.

"Do you have anything like that?"  - No, that's where the other table comes in... When the MemberOf (Table 2) and Distinguished Name fields match, then I know they are a member of that group.

"Is DistinguishedName a single field for a user that contains the names for all of the groups they belong to? " - No, not exactly... it's more like a line of code that has codenames of all the usergroups associated with a user ID.    Not so easy to identify groups within this field.

"When you said "group type that begins with the letter H", what's a "group type"?  Is that another field?  Or did you maybe mean "group name"?  Or was I wrong about what Table1.Type is used for?"  - Sorry for the confusion... I should have said account name value that is a group that does begin with the letter H.  No you were spot on with your assessment of Type in table 1.  

Hope this helps... Thank you for the command recommendation... will let you know if it works and if I'm able to join the two queries.   Mojeaux
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
> Did not know about creating two commands for the same table...
 > but like where you were going with that.

 If by "command" you mean a CR Command (where you create the query manually in CR), that wasn't really what I meant.  I was thinking of when you add tables to a report and let CR create the query automatically.  You can add the same table more than once.

 If you're using a Command, then there shouldn't any need to create two commands (I don't know if that's possible).  You'd just change your query so that it used the table twice, but only included the user (U) rows from one instance, and the group (G) rows from the other instance.  That would, in effect, give you separate "User" and "Group" tables.  Then you would just use the names from the "Group" table to group the report and the names from the "User" table would be your details.

 It seems straightforward enough, as long as you can link the users to the groups.

 James
0
 

Author Comment

by:mojeaux
Comment Utility
It does but haven't made the link yet.  You are correct about the command...CR really didn't like reference to multiple commands.  I will work on unions tomorrow.  Didn't have much time this afternoon to do much research on two unions within the same table.  Thx, Michelle
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
Something I'm not still not sure about is whether you can link individual users and groups.

 You said earlier that DistinguishedName and MemberOf are both "String data that lists groups the Users belong to".  To me, that sounds like a single string that contains a list of group names/ID's, like "Group 1,Group 2,Group 4".  You said that it wasn't that, and was "more like a line of code that has codenames of all the usergroups associated with a user ID".  I don't know what "line of code" means in this context, but that still sounds like something like "Group 1,Group 2,Group 4" to me.


 Getting back to your latest post, "union", in SQL, means combining the results from two separate queries.  Maybe you weren't talking about that kind of Union, but, FWIW, that's not what I meant.  I was talking about one query that used Table1 (ADUsersAndGroups in your original post) twice.  In one case it only reads the User rows and in the other it only reads the Group rows, so it's like you have separate User and Group tables.  But you need to be able to connect those two, so that for each Group in ADUsersAndGroups, you can read all of the Users in ADUsersAndGroups that are in that Group.


 Or Table2 (GROUPS in your original post) may be the key to this.  I'm not sure exactly what's in that table.  Given the GROUPS name, I was automatically thinking of it as being basically a list of groups, but if it actually has an entry for each user, with the groups that they belong to, then maybe that should be your "detail" table, and you just want to get the names from ADUsersAndGroups.

 James
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Can you give us some sample data?

The solution is in how you link the tables.

FROM         ADUsersAndGroups A INNER JOIN  GROUPS B
                    ON A.DistinguishedName = B.MEMBEROF AND A.Type = "GROUPS"
                     INNER JOIN ????

mlmcc
0
 

Author Comment

by:mojeaux
Comment Utility
Hi James/mlmcc -

James, in your comment, "I was talking about one query that used Table1 (ADUsersAndGroups in your original post) twice.  In one case it only reads the User rows and in the other it only reads the Group rows, so it's like you have separate User and Group tables."... YES!!   That's exactly what I'm looking todo but do not know how to do this in CR.  
Question... would you have to link/join these two tables together or could you link them to the third table, Groups?

mlmcc - So my SQL server code would look like:
SELECT         A.AccountName,
                    A.DisplayName,
                    A.DistinguishedName,
                    A.Type,
                   B.ID,
                   B.MEMBEROF,
                   B.USERNAME,
                   B.USERSID
FROM         ADUsersAndGroups A INNER JOIN GROUPS B
                   ON A.DistinguishedName = B.MEMBEROF and A.Type = 'Groups'

By setting the type to Groups, am I eliminating all possible User types in the Query?

Thanks, Michelle
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Yes you are eliminating the users.

Now you need to analyze the tables to see how to get the users back by adding one or both of the tables back

This query will get the users

SELECT         A.AccountName,
                    A.DisplayName,
                    A.DistinguishedName,
                    A.Type,
                   B.ID,
                   B.MEMBEROF,
                   B.USERNAME,
                   B.USERSID
FROM         ADUsersAndGroups A INNER JOIN GROUPS B
                   ON A.DistinguishedName = B.MEMBEROF and A.Type = 'User"

Now you need to link them together probably on Distinguished Name

mlmcc
0
 

Author Comment

by:mojeaux
Comment Utility
For more clarification... I would like to do this in one select statement... is it possible in CR?   Rather than run it for just one group i would like to select all groups that begin with "H_" in the AccountName and list all USERNAMES (group table) AND DisplayNames (UserandGroups Table) for that Group

ADUsersAndGroups is a table for keys and values –

SELECT      DistinguishedName
FROM         ADUsersAndGroups
WHERE     (AccountName = 'Help_desk_Priv_DB')


SELECT     TOP (200) USERSID, MEMBEROF, USERNAME, GROUPSID, ID
FROM         GROUPS
WHERE     (MEMBEROF = 'CN='Help_desk_Priv_DB',CN=Users,DC=dnanico1,DC=aniconet,DC=com')
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
I am trying to help you do it in a single select statement.  However since I don't fully understand the structure or the values it is difficult.

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Can you build a query in the database?

mlmcc
0
 

Author Comment

by:mojeaux
Comment Utility
SELECT AccountName, DisplayName, DistinguishedName, Type
FROM ADUsersAndGroups
JOIN GROUPS on USERNAME = AccountName
UNION
SELECT AccountName, DisplayName, DistinguishedName, Type
FROM ADUsersAndGroups
JOIN GROUPS on MEMBEROF = DistinguishedName

This works... It returns both groups and users with display names from both tables.

Now one last question... Within CR, how dow I group so that the users will list under their respective groups?
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Which is the group field for both queries in the union?

mlmcc
0
 

Author Comment

by:mojeaux
Comment Utility
The group field is the AccountName (if type is = Group and AccountName starts with "H_").   Detail should be all AccountNames (if type is = User and AccountName = USERNAME(from Group Table) and DistinguishedName = MEMBEROF(from Group Table)).
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
DOes the query return all the records you want?

Does it return extra records?

mlmcc
0
 

Author Comment

by:mojeaux
Comment Utility
It returns extra records...
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
Going back to one of your earlier posts, you had asked how to include Table1 (ADUsersAndGroups) twice in the same query in CR.  It depends on your datasource.  If you're using a CR Command, then you're writing the query, so you just change the query as necessary (it really has nothing to do with using CR, since CR just passes that query to the server).  If you're adding the tables to the report and letting CR generate the query, then you'd just add that table twice, but then you'd need to control which records (Users or Groups) were read from each instance of the table.  If you couldn't do it by changing the links between the tables, you'd have to add something to the record selection formula that restricted which records were included from each table.


 Looking at the last query that you posted, the problem there is that you appear to have separate rows for the groups and users.  If you group the report on any of those fields, you're going to get separate report groups for the users and groups, rather than a report group for each group, which contains the users in that group.  You really need one row for each group and user combination.

 Based on that query, it seems that joining to GROUPS with USERNAME = AccountName gives you the groups, and joining to GROUPS with MEMBEROF = DistinguishedName gives you the users (and you're not checking the Type at all).  Is that correct?

 If so, I think maybe you want to do something like read ADUsersAndGroups and GROUPS using the MEMBEROF = DistinguishedName join, to get the users, and then join GROUPS back to a second instance of ADUsersAndGroups using USERNAME = AccountName, to get the info for the group.  I could definitely be wrong, but based on your query, it seems like that would work.

 James
0
 

Author Comment

by:mojeaux
Comment Utility
Hi James,
Thank you for taking the time explain all that you have.   It has been a big help.   Please see if the code I have below is what you had in mind:

SELECT * FROM (
SELECT U.USERNAME AS USER_ID
            U.MEMBEROF AS GROUP_BELONG1
            G.AccountName AS
            G.DisplayName AS NAME
            G.DistinguishedName AS GROUP_BELONG2
            FROM GROUPS U, ADUsersAndGroups G
            INNER JOIN ON U.MEMBEROF = G.DistinguishedName
            UNION ALL
            SELECT U.USERNAME AS USER_ID
                        U.MEMBEROF AS GROUP_BELONG1
                        G.AccountName AS
                        G.DisplayName AS NAME
                        G.DistinguishedName AS GROUP_BELONG2
                        FROM GROUPS U, ADUsersAndGroups_1 G
                        INNER JOIN ON U.USERNAME=G.AccountName
           )AS WPS_Security
ORDER BY G.AccountName
0
 
LVL 34

Accepted Solution

by:
James0628 earned 500 total points
Comment Utility
Just for the record, there are a number of syntax or other errors in that query, but ignoring those ...

 No, that's not what I meant.  You've still got two results sets, combined with a UNION.
 The first Select has a Join with GROUPS using U.MEMBEROF = G.DistinguishedName.
 The second Select has a Join with GROUPS using U.USERNAME = G.AccountName.

 I am guessing that the Join in the first Select is supposed to give you the Groups, and the Join in the second Select is supposed to give you the Users (you didn't mention if that guess from my last post was correct).

 Assuming that that's correct, that will put the Users and Groups in separate records.  Something like this:
Group1
Group2
Group3
User1
User2
User3
User4

 If you group that data on AccountName, for example, then the report would presumably have a separate group for each Group _and_ for each User (each User would be seen as a Group).


 I think what you want is one record for each User and Group combination.  I got the impression before that a User could be in more than one Group.  Is that correct?  Also, I still don't know how, or if, the Users and Groups are connected in your data.

 Assuming that a User can be in more than one Group, and that there is some way to connect the Users and Groups, then using the above list, let's say that User1 was in Group1 and Group3, User2 was in Group1, User3 was in Group2 and Group3, and User4 was in Group3.  Given that situation, you'd want your data to be something like:
User1   Group1
User1   Group3
User2   Group1
User3   Group2
User3   Group3
User4   Group3

 Then if you group the report on the Group field, you'd get:

Group1
   User1
   User2

Group2
   User3

Group3
   User1
   User3
   User4


 But at this point I don't know if your data even looks like that (Users in multiple Groups), much less if your Users and Groups can be connected like that.  Some sample data might help.

 James
0
 

Author Closing Comment

by:mojeaux
Comment Utility
Thank you for the help.  It was exceptional.  I gave up and went with a linked subreport.  Thx, Mojeaux
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
Sorry I couldn't be of more help, but, like I said, I still don't really understand how your users and groups are connected in the tables.  Subreports aren't very efficient, but if that gives you what you need and the performance is acceptable, great.

 James
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now