Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tjwent69
Starting Member
30 Posts |
 Posted - 2008-01-24 : 10:35:51
|
| I have a report which is a list of items and I display everything about the item. It is great. My report table in the layout tab is simple. Header,Detail,Footer. Each Item has 65 columns. The number of items (rows) vary upon what you want to see. Example data.Item#, Description, CaseSalePrice, Cost, BottleSalePrice, Discount123, Grenadine, 100.00, 75.00, 15.50, 2.00456, Lime Juice, 120.00, 81.00, 17.25, 2.00There could be 1 item or 4000 items.What I want to see is.Item # - 123, 456Description - Grenadine, Lime JuiceCaseSalePrice - 100.00, 120.00Cost - 75.00, 81.00BottleSalePrice - 15.50, 17.25Discount - 2.00, 2.00What I am actually doing is running this the top example and saving to excel. Then copying the sheet. Creating a new sheet then doing a paste special transpose and this gives the users what they want to see.I want to grab that table object in the report layout tab and twist it 90degrees so the header is on the left, detail is in the middle and the footer is on the right. It would be perfect. The dynamic column need is really the problem here. I never know how many items will be in the report. They all have the same basic information like description and pricing. I am all out of creative ideas, any help would be appreciated. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-01-24 : 11:07:04
|
| It looks as if you can get the required format by using a matrix. set the column group of matrix to value of dynamic column. I guess that will provide you with output in required layout. |
 |
|
|
tjwent69
Starting Member
30 Posts |
Posted - 2008-01-24 : 13:25:28
|
| That's what I thought. In the Matrix you have column (item number) Rows and Details. The rows need to just be a label. The detail is the Value like the cost, and the case sell. It is not a sum or a count it is just the value returned by the query. I can get the value to display eventually I just can't get the 65 rows down. They keep listing horizontally. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-01-25 : 09:23:01
|
quote:
Originally posted by tjwent69
That's what I thought. In the Matrix you have column (item number) Rows and Details. The rows need to just be a label. The detail is the Value like the cost, and the case sell. It is not a sum or a count it is just the value returned by the query. I can get the value to display eventually I just can't get the 65 rows down. They keep listing horizontally.
thats ok. You can keep a dummy group on unique column and get those individual values. the question is how many values will you have for each item. WIll it be always 2? what determines it? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-01-25 : 09:26:40
|
| Also you may try using PIVOT operator and display it in report using matrix. |
|
|
|
tjwent69
Starting Member
30 Posts |
Posted - 2008-01-25 : 15:00:32
|
| The cost and sell price are only two of the 65 different values associated for each item. That's what the users require. They need to know, for buisness planning, how much each item costs, what the different discount breakdowns they have, and how much money we make on it. Then they want to change an element like the sale price and see how it effects everything about the item. (Totally different project but that is where their mind is) Our items are in family groups so each manager works on a group. The groups can have from 1 to 4000 items in it. This is a huge yearly task. Last year the administrative assistants ran 4 different reports and re-entered the numbers into excel. I figured since the data came out of the dB in the first place, lets just do this right and take all the manual labor out of this process. But..... While I can get the data in columns I cannot seem to flip it into rows. I looked into pivot tables but.. the data specified in the IN column list must include all the values that will be pivoted into columns to be named explicitly. Since my items ( columns ) vary from group to group I cannot explicitly name them. Unless.... I could write a sub-query to create a list of the items and string them together in to a dynamic select statment. Which I can't even begin to imagine how to do that. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-01-26 : 11:39:09
|
| Are you using SQL 2005? then its rather easy. If you are using SQL 2005 you can build the string using FOR XML PATH clause. And you can build a SQL string using PIVOT including this generated string of column values and execute this using Exec. |
|
|
|
|
|
|
|
|
Turn Columns into rows and rows into columns