| Author |
Message |
Throbbin Rods
Newbie
Joined: 21 Sep 2010
Location: United States
Online Status: Offline
Posts: 7
|
  
Topic: Grouping
Posted: 21 Sep 2010 at 8:06am |
|
Good afternoon all
This is my first time on the board in a long time - so long I had to re-register!! I am looking for some advice. I think the answer is probably in grouping but I am apparently too rusty to figure it out. I have this data:
PO Line Number Item Number Qty Ordered Qty received.
1234 1 123c44 115 17
1234 1 123c44 115 15
1234 1 123c44 115 93
1234 2 123c77 100 17
1234 2 123c77 100 15
1234 2 123c77 100 68
What I am trying to do is get the report to run and tell me how many total have been received for Line 1 on PO1234, and on line 2 on PO1234, and all the other PO's in the system. I keep running into the same problem. It will not calculate a total, just picks up the first and third lines if they exist, other wise only the first. I have done all of the Link work and am satisfied that the linking is correct based on the number of open lines per PO. I proved that out by creating a formula field for Qty ordered mi nus Qty shipped and comparing ti to known reality. I have to believe that grouping or sub reports will do this but I was never good with sub-reports and I have forgotten an awful lot ab out grouping. I hope this is enough info to get some help.
Thanks
Bill - Throbbin Rods
|
|
When this you see, Remember me
And keep me in your mind
Let all the world say what they may
Think of me as you find
|
 IP Logged |
|
|
|
Memoli28
Groupie
Joined: 10 Apr 2009
Online Status: Offline
Posts: 58
|
Posted: 21 Sep 2010 at 8:31am |
Hi Bill,
assuming 1 line is always the same item number, the following should help:
first group by PO, second group by Line and make a summary on field QTY received on second group level.
rg.
Memoli
|
| IP Logged |
|
Throbbin Rods
Newbie
Joined: 21 Sep 2010
Location: United States
Online Status: Offline
Posts: 7
|
Posted: 21 Sep 2010 at 8:57am |
Thanks much Mermoli - this should get me where I need to get the project moving. I was grouping by 4 fields and getting multiple lines showing up for my summary. Now I can get this thing finished - Thanks very much  Bill
|
|
When this you see, Remember me
And keep me in your mind
Let all the world say what they may
Think of me as you find
|
| IP Logged |
|
Throbbin Rods
Newbie
Joined: 21 Sep 2010
Location: United States
Online Status: Offline
Posts: 7
|
Posted: 22 Sep 2010 at 2:38am |
OK, I have made a fair bit of progress on this since yesterday in my "spare" time. I have run into one issue. I am doing a calculation for Quantity ordered - Quantity Received = Quantity still open. I have summarized both the ordered qty and the received qty, going as follows Summary is Ordered qty (or Received qty depending on the summary)grouped by Line number then item number. In many cases I have the same item number in more than one line number on the same purchase order. I am likely to have multiple partial receipts until any one particular line is complete. I seem to be having a problem getting my receipts to sum to the right total, and in getting the receipts for line item 5 to line up with the ordered quantity for line item 5. this makes my Quantity still open totally unbelievable. Any thoughts? ![]() Thanks much bill
|
|
When this you see, Remember me
And keep me in your mind
Let all the world say what they may
Think of me as you find
|
| IP Logged |
|
Memoli28
Groupie
Joined: 10 Apr 2009
Online Status: Offline
Posts: 58
|
Posted: 22 Sep 2010 at 7:49am |
Hi Bill,
you can also create a formula like:
Po&'-'&Linenumber&'-'&Item number
put this in the detail section and group by this new field and make your summaries.
if this is not helping, just put a more data as example and show how you exactly want the outcome and I can have a look it Crystal.
rg.
Memoli
|
| IP Logged |
|
Throbbin Rods
Newbie
Joined: 21 Sep 2010
Location: United States
Online Status: Offline
Posts: 7
|
Posted: 27 Sep 2010 at 4:08am |
|
I was home for two days last week with my daughter as she was suffering with a severe migraine, so I worked on this and did research when she was asleep. I got to where I thought I had it, and I was right except for orders where the PO has the same Item on different lines, such as blanket PO's. Then it just TARFU's up the "on order" and "received" numbers, which of course hoses my calculated "still open field". I am going back to my grouping logic and reading up on it as I suppose I should have grouped something differently. Any thoughts anyone? I really need to get this whole grouping thing down somehow...
Thanks
bill
|
|
When this you see, Remember me
And keep me in your mind
Let all the world say what they may
Think of me as you find
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 27 Sep 2010 at 4:25am |
YOu need a summary like this?
PO Line Number Item Number Qty Ordered Qty received.
1234 1 123c44 115 17
1234 1 123c44 115 15
1234 1 123c44 115 93
115 125
1234 2 123c77 100 17
1234 2 123c77 100 15
1234 2 123c77 100 68
100 100
group on PO
Group on Line Number
Create 2 Running totals
First one
Name=QTY_Ordered
Field to summarize=Quantity Ordered
Type=Maximum
Evaluate=for each record
reset=on group level 2
place in group footer 2
second one
Name=QTY_Rcved
Field to summarize=Quantity Recieved
Type=Sum
Evaluate=for each record
reset=on group level 2
place in group footer 2
If you need to comapare the 2 create a formula field as
{#QTY_Ordered}-{#QTY_Rcved}
place on group footer 2
|
| IP Logged |
|
Throbbin Rods
Newbie
Joined: 21 Sep 2010
Location: United States
Online Status: Offline
Posts: 7
|
Posted: 29 Sep 2010 at 7:36am |
Thanks much for all the help - I thought I had it whipped today. Everything looked great when I started paging through the data. then I got in about 4 pages and it was time for the Uh Oh Squad!! On line items where nothing had yet been received, sometimes the qty received showed as 0 and sometimes it was a blank field?  I tried pulling raw data from the database straight into Excel and got no blank fields, so I don't think it is the data unless there is something fluky in Dynamics - who am I kidding - everything is fluky in Dynamics!! Anyway, does anyone have any ideas? I am SOOOO close to being done with this and have learned way more about grouping than I ever did before thanks to all of the help here. Thanks Bill
|
|
When this you see, Remember me
And keep me in your mind
Let all the world say what they may
Think of me as you find
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 29 Sep 2010 at 7:46am |
try and go into File>Report Options and make the
'Convert Database NULL Values to Default' as TRUE Edited by DBlank - 29 Sep 2010 at 7:46am
|
| IP Logged |
|
Throbbin Rods
Newbie
Joined: 21 Sep 2010
Location: United States
Online Status: Offline
Posts: 7
|
Posted: 29 Sep 2010 at 7:50am |
|
Dang - I knew about that one too!!
Thanks very much DBlank - that finished up my project. Now I just need to convince the accountants that my numbers are correct.
Thanks
bill
|
|
When this you see, Remember me
And keep me in your mind
Let all the world say what they may
Think of me as you find
|
| IP Logged |
|
|
|
