• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 580
  • Last Modified:

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
0
keiffer7
Asked:
keiffer7
  • 6
  • 4
1 Solution
 
frodomanCommented:
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?
0
 
frodomanCommented:
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.
0
 
keiffer7Author Commented:
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
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
frodomanCommented:
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.
0
 
keiffer7Author Commented:
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
0
 
frodomanCommented:
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" ?
0
 
keiffer7Author Commented:
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".
0
 
keiffer7Author Commented:
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?
0
 
keiffer7Author Commented:
No one?
0
 
keiffer7Author Commented:
I fixed the issue myself using a stored procedure to re-order the records. How do I close this?
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now