In the summary you need to add your fields, presumably Part Number and Month as Key Fields with the Quantity as a Measure.
Then you need to double click on the Month field in the Key Area to access the properties window. There you will see a tab Display where you can set the Orientation, set this to across. When you exit from here and the summary definition you should have all of you part numbers down the side and the month across the top.
You will encounter one problem where the months will display in alphabetical order. You can overcome this by using the month number. You will need to set up a calculated lookup field in the table to convert the month name to number.
This should then give you the result you are after.
Let me know if you need any further help.
Thanks for your prompt reply Nigel.
I have one column in my summary now titled "Transaction Date". This column has dates ranging from 1/1/2007 to 4/30/2007.
I need to divide this data into months so I need to add 4 calculated fields, I assume. I just can't fiqure out how to get the formula to work. (If I'm on the right track that is...)
but I get operand error messages.
The finished report should look like kind of like this:
____________________ Jan07 | Feb07 | Mar07
StockNum1 | Description| Qty | Qty | Qty
StockNum2 | Description| Qty | Qty | Qty
Thanks so much for your help and patience!
In the Summary you need to work with a single field (not 4) that uses the value of the month (extracted in a suitable format from the date field and ignoring the day) and display the field 'Across' sorted into the order you require.
Now, this can be slightly easier if you can use the Month as its numeric value in the calculated field as it saves a few steps but no big deal in the long run. To star with let's stick to numerics - we can change it later if you need to.
I will refer to your date source field as "DateField".
In the Table window add a calculated field for MonthYear (name it whatever you want to).
Lets make it a character field (could be numeric if you want to play with getting the value correct for sorting) and use the formula:
Use the calculated field in your summary as an ACROSS display sorted by value and you will be getting close.
If you need to control the display sort order (especially if you want to use the abbreviated month names) you can specify that for the summary key field.
In the summary right click on the key field MonthYear, go into properties and the "Matching" tab and set up the list of Specified Values. There can only be 12 presumably.
Make sure you enter them in the SORT ORDER you wish to see later.
Leave that Tab and go to the "Sorting" Tab in properties. Check the "Order By: Position in Specified Values list" radio button.
Set the field to display ACROSS using the "Display" tab.
Unless I have missed something out that should get you close.
If you want to see Jan 2007, Feb 2007 and so on you will need to adapt the formula above to interpret Month 1 as Jan, month value 2 as Feb and so on. It is possible to do this using an IF statement or 12 (Nested or sequential) but probably easier to use an internal lookup table to substitute the month name for the numeric value returned by the Month() function. Then adjust the specified values list to suit the order you need.
I'm not sure how much of that you may be familiar with so let us start with the month number approach and get that working then we can go through the quick conversion to Month name to tidy things up afterwards if you need it.
See if you can make sense of what I have suggested.
The Display, Orientation choice of "Across" is grayed out so I can't select it.
Renata /b[/quote]How many Key fields in your Summary? You must have at least one key field set to Down.
I'm trying to recall any other constraints that apply ...
I have 9 and they are all set to Down.
Are any of the fields set up as ITEM fields rather than KEY fields? Item fields and Across display of Key fields are incompatible.
Ah Ha! I finally notice this handy message at the bottom:
"Across option is not available when an item field is defined."
So, I had 3 fields as "Items". I removed them and was able to choose "Across". At first glance the report looks good.
One more question and I'll give it a rest...for today anyway:
Since I want the fields I had entered as Items in my report, do I add them as Key fields? I already have 9 Key fields and didn't know if I should keep that to a minimum??
Thanks so much for your time and help!
If you really need the fields it will certainly make the drill up and down feature something to play with!
It is unlikely that you will hit any technical constraints (See the section on SPECIFICATIONS in the HELP file) but obviously the more running totals Monarch has to maintain for combinations of keys and measures the greater the likelihood of slower processing. Unless you are dealing with hundreds of thousands of records you may not notice a practical difference in performance.
Under the circumstances I think you should just add the fields back in as key fields and see it the process speeds and results are still acceptable to you. My guess is they will be.
[size="1"][ October 09, 2007, 05:35 PM: Message edited by: Grant Perkins ][/size]