Data Item Help

[JohnnyS]

Hi, I need help with a data item....I need to display the main item's average cost in a separate column next to the Repair Items (See Column Main Item Avg Cost)....I tried a few a functions in a data item but not getting the results....how can I link the repair items to pull the Main item cost?

stock_code   whse_code   whse_avg_cost    condition_code     Main Item Avg Cost
10008                 MAIN                 13,607.88      Main                      13,607.88
10008R                 REP                 4,963.385     Repair                     13,607.88
10008R                 REP                  5,477.27    Repair                   13,607.88

[MFGF]

Hi, I need help with a data item....I need to display the main item's average cost in a separate column next to the Repair Items (See Column Main Item Avg Cost)....I tried a few a functions in a data item but not getting the results....how can I link the repair items to pull the Main item cost?

stock_code   whse_code   whse_avg_cost    condition_code     Main Item Avg Cost
10008                 MAIN                 13,607.88      Main                      13,607.88
10008R                 REP                 4,963.385     Repair                     13,607.88
10008R                 REP                  5,477.27    Repair                   13,607.88

Hi,

I just want to make sure I have the requirement clear here. Is the objective to display the average of all three rows here? Assuming it is, you'll need first of all to isolate the same stock code for all three rows (eg, have the value 10008R available on each row). You could do this with an expression such as

if ([condition_code] = 'Repair') then (substring([stock_code],1,character_length ([stock_code]) - 1)) else ([stock_code])

This would return 10008 for all three rows (assuming the repair rows always have an R appended to the stock code)

You can then use this in a summary function to get the average of the cost across all rows with the same value in this result

average([whse_avg_cost] for [your calculation above])

Hope this helps!

MF.

[JohnnyS]

Hi, Thanks I will try your suggestion...the objective is that I want the Average cost of the Main Item which is 13607.88 to display as the average cost for the 2 Repair items as well.

[MFGF]

Hi, Thanks I will try your suggestion...the objective is that I want the Average cost of the Main Item which is 13607.88 to display as the average cost for the 2 Repair items as well.

Hi,

Sorry - that's a different outcome than I assumed from the original question. The solution above will give you the average across all of the Main and Repair rows.

To have the average from just the Main row repeated across all the rows, you'll need to add a specific individual query to your report to deliver the average value for each Main row. Filter this query to only include the Main rows.

Then add a second query which has both Main and Repair rows in it. You'll need a calculation to extract the stock_code the Main rows use - so use the first calculation I included in the previous post for this. Join this calculated item to the stock_code in your first filtered query so you can then reference the average from the first query. Your list will then be based on this joined query result.

Cheers!

MF.