Grouping

Have you tried using the UserName field from B?

mlmcc

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)

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

this might help in the meantime (i.e. the Crystal way)

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

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!

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

Did you try my idea?

mlmcc

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

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

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

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.

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

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

> 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

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

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

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

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

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

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')

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

Can you build a query in the database?

mlmcc

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?

Which is the group field for both queries in the union?

mlmcc

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)).

DOes the query return all the records you want?

Does it return extra records?

mlmcc

It returns extra records...

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

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

Thank you for the help.  It was exceptional.  I gave up and went with a linked subreport.  Thx, Mojeaux

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