×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Header and Details showing horizontally

Header and Details showing horizontally

Header and Details showing horizontally

(OP)
I have a report that will have a dynamic list of security group names/numbers across the top (as headers for columns) then have a list of users who are in those groups. For the users, they will be in rows and if they are in the header group, then the group number will appear in the 'cell' below the group name. If they are not in the group, then an 'x' or blank will appear.

So, currently, I have a subreport pulling in the group headers:

GroupA – 11 GroupB – 15 GroupC – 28 GroupD – 44

And that works fine. The main report works, too, but I'm not able to get everything to display the way I'd like. I have three formulas (head, detail, footer) to create a comma delimited string with the groups the user is in and displays them. That is fine.

So, the main report looks like this:

User1 15,28,44
User2 11
User3 28,44
User4 11,44

I’d like the overall result to be this (and hopefully be able to export to XLSX, to boot):
GroupA – 11 GroupB – 15 GroupC – 28 GroupD – 44
User1 X 15 28 44
User2 11 X X X
User3 X X 28 44
User4 11 X X 44


The subreport (headers) is using one table with only the group name and the number. The main report is using tables including the group number and user names.

Can anyone tell me if this can even be done? Right now, I've got the headers hardcoded and under each heading, in the detail section, I have a formula that tests the comma delimited string. If 'group#' is instr, then 'group#' else 'x'. Seems simple enough, but there are over 100 groups - so over one hundred hardcoded headers and the same amount of formula fields. The group names will be changing soon, so I'd really love to have this all be dynamic and without the possibility of 'user error'.

Thank you, in advance!

~ lahddah

RE: Header and Details showing horizontally

Hi, lahddah, Programmer,

You've been around since 2003 and have not learned to post data examples using TGML codes?

What you want is a Pivot or Transform. Your "main report" looks like THAT, REALLY? If you had a proper pivot query, or even an Excel PivotTable, you would not have this issue. Where is your source data?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Header and Details showing horizontally

(OP)
Thank you for your response. I was wondering when I'd be made to feel "less than" on these boards. So, now i can cross that off my list.

I've "been around since 2003" but, recently, I've taken on the opportunity to work more with Crystal Reports. I don't post much, and I'm not familiar with "TGML•" Forgive me.

I felt CR would be the way to go for this project, but perhaps it is not.

The data is in an sql db. I will just keep trying in CR or maybe I will look at creating a pivot query or pivottable. The end users at the company are far more comfortable with simply running quick Crystal Reports, but if I can get something else to work, they'll adapt.

Thanks, again.

~ lahddah

RE: Header and Details showing horizontally

Concerning your comments regarding TGML, here's what your first example should have displayed having TGML Post Option checked in the Reply To This Thread and 1) selecting your example table, then 2) clicking the Pre icon displayed above the Reply window...
User1		15	28	44
User2	11			
User3			28	44
User4	11			44
 

It will indicate that you care that your post will be better understood as intended. Hey, it's what programmers do.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Header and Details showing horizontally

(OP)
Thank you, again, SkipVought. I looked further into Cross-Tab queries and it looks like it should be exactly what I'm looking for. I tried this early on, but was actually told by the person training me that it wouldn't work. So I moved on to something else, far more difficult, time consuming, less dynamic and more frustrating. Maybe they didn't understand what I wanted to do, either. I have got it running exactly the way I mentioned above, but I actually need two more 'set' columns per row (user title and user department, I've only listed UserTitle below but need department, as well). Plus, I need to group by User or UserTitle (depending on end user's parameter input) I will go back to the drawing board and work with the Cross-Tab expert to see how I can get this to work w/ the extra columns.

Existing:
UserName     Group 10    Group 5     Group 8     Group 12
User              10          x           8          12
User              10          5           x          12
User              x           5           8          12
User              x           x           x           x 

Desired Output: (alpha by user)

UserName       UserTitle        Group 10    Group 5     Group 8     Group 12
User             Title             10          x           8          12
User             Title             10          5           x          12
User             Title             x           5           8          12
User             Title             x           x           x           x 

Desired Output: (group by title)

UserName         UserTitle        Group 10    Group 5     Group 8     Group 12

TITLE1
   User            Title1           10          x           8          12
   User            Title1           10          5           x          12
TITLE2
   User            Title2           x           5           8          12
   User            Title2           x           x           x           x 

Thanks, again!

~ lahddah

RE: Header and Details showing horizontally

(OP)
I ended up getting it to display the way I wanted by adding the other rows and customizing their style. Thanks, again, for the help here on this board!

~ lahddah

RE: Header and Details showing horizontally

Great!

If you do a Search here for Crystal Reports, there are several forums devoted to CR.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close