Header and Details showing horizontally

[lahddah]

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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[lahddah]

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

 

[SkipVought]

Check out thread149-1205638

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

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

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


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[lahddah]

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:
[pre]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[/pre]

Desired Output: (alpha by user)

[pre]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[/pre]

Desired Output: (group by title)

[pre]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[/pre]

Thanks, again!

~ lahddah

 

[lahddah]

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

 

[SkipVought]

Great!

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]