asked on

Make Across then Down Report appear as Down then Across report

Crystal Reports XI / SQL Server / Stored Procedure

I need to display the result of a stored procedure in a multi-column format in the report. The reports has 3 columns and is set to "across then down". However, I need the records to appear in the order of down then across. The report has 5 groups on it and I do not want the groups to be in columns.

Here is what I have now:
Product Group #1
Sub Group #1
Item #1 Price Item #2 Price Item #3 Price
Item #4 Price Item #5 Price Item #6 Price
Item #7 Price Item #8 Price Item #9 Price

Product Group #2
Sub Group #2
Item #1 Price Item #2 Price Item #3 Price
Item #4 Price Item #5 Price Item #6 Price
Item #7 Price Item #8 Price Item #9 Price

This is What I need it to be:

Product Group #1
Sub Group #1
Item #1 Price Item #4 Price Item #7 Price
Item #2 Price Item #5 Price Item #8 Price
Item #3 Price Item #6 Price Item #9 Price

Product Group #2
Sub Group #2
Item #1 Price Item #4 Price Item #7 Price
Item #2 Price Item #5 Price Item #8 Price
Item #3 Price Item #6 Price Item #9 Price

Basically I am trying to use the "Across then down" direction but make it look like "Down then across". If I have 15 records returned for this group, then I want 5 rows of 3 columns. The only way I can get that is by using "across then down" direction. However, people will be more liekly to read down then across. I have had no luck finding any way of accomplishing this in Crystal Reports. I have also tried finding a way to add a new row to the query results that I could sort on. I have had little luck finding any way to a make this work.

Any one have any ideas how I could accomplish this? Any suggestions would be appreciated.

Thanks

frodoman

If you change the format to 'Down Then Across', crystal will still preserve your groups exactly as you've shown you want the report to be. Have you tried this and are getting different results?

frodoman

Make sure you do NOT have "Format Groups With Multiple Columns" checked on the Layout tab - that will mess up the columns and give you undesired results.

keiffer7

ASKER

frodoman,

Thanks for the reply. I have tried that but it does not accomplish what I am looking to do. Doing what you said comes out looking like this:

Product Group #1
Sub Group #1
Item #1 Price
Item #2 Price
Item #3 Price
Item #4 Price
Item #5 Price
Item #6 Price
Item #7 Price
Item #8 Price
Item #9 Price

Product Group #2
Sub Group #2
Item #1 Price
Item #2 Price
Item #3 Price
Item #4 Price
Item #5 Price
Item #6 Price
Item #7 Price
Item #8 Price
Item #9 Price

It never uses the 2nd or 3rd columns. Unless I am doing something wrong.

Thanks,
Keith

frodoman

When you check the box for 'Format With Multiple Columns' a new 'Layout' tab should appear. On this tab you need to set the column widths and padding. For example, set column with to 2" and padding to 0.5" should get you 3 columns.

keiffer7

ASKER

Yeah. I have it set to

width: 2.500
horizontal: 0.200

Perhaps images will help explain.

This is what I have right now: http://www.prioritywire.com/images/picture1.jpg
This has the look I want but the items are in the wrong order. They are going across then down. I need them to go down then across.

This is what I get when I try what you mentioned earlier: http://www.prioritywire.com/images/picture2.jpg
When I try this it only gives me one column even know it is set up for multiple (3) columns.

Thanks

frodoman

So to get from picture1 to picture2, the only thing you changed is selecting Down Then Across instead of Across Then Down ?

Do you have anything checked in the group headers / footers other Than "Keep together" and "free form placement" ?

keiffer7

ASKER

That is correct.

There are actually 5 groups to this report. Most of them are hidden. Here are what is checked in each group in the section expert:

Group #1:
- Free-Form Placement
- Suppress (No Drill-Down)
- Keep Together
Group #2:
- Free-Form Placement
- Suppress (No Drill-Down)
- Keep Together
Group #3:
- Free-Form Placement
- Keep Together
Group #4:
- Free-Form Placement
- Keep Together
- Suppress Blank Section
Group #5:
- Free-Form Placement
- Suppress (No Drill-Down)
- Keep Together
Details Section:
- Free-Form Placement
- Keep Together
- Format with Multiple Columns

All of them have this in the section expert for the Group Footers:
- Free-Form Placement
- Suppress (No Drill-Down)
- Keep Together
Except for Group #1 Footer has "New Page After" set with a formula "Not OnLastRecord".

keiffer7

ASKER

Perhaps there is a way to create a new data field that I can sort on. I have added a section to my stored procedure that loops through all the records in the table and assigns them a "sort order" number. I am trying to come up with a calculation that will return the correct number for each record that will order them the way I would like. For example - if I use:

SET @NewOrder = @Count + ((@Count - 1) * 2)

** @Count being an INT that adds 1 every time through the loop.

This will return the correct numbers for everything that should be in the first column. However, I can not figure out how to calculate the numbers for columns 2 and 3.

Basically, if I have 15 records, I need to to change record #2 to #4, #3 to #7, #4 to #10, and so on. Here is a list showing the mapping from 1 to 15:

@Count      @NewOrder
1      1
2      4
3      7
4      10
5      13
6      2
7      5
8      8
9      11
10      14
11      3
12      6
13      9
14      12
15      15

Of course it may not always be 15 so this calculation would have to work no matter how many total records there were. Anyone have ideas on this route?

keiffer7

ASKER

No one?

ASKER CERTIFIED SOLUTION

keiffer7

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial