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

Select item characteristic to pull the correct pricing

The goal is to show an inventory report where our raw materials pull from a cost dataset, but the finished goods pull from the pricing dataset.  I have been trying to say that if the itemclass is anything other than F (for Finished Goods) then use the FGPrice dataset field Price for the value, otherwise use InventorySet dataset field TotCost for the value.  I just can't get it to do that.  HELP! :-)

One dataset is for the cost with this query -
SELECT        ItemNumber, ItemDescription, qtysum, Val, ItemClass4, CostType, TotalRolledCost
FROM            Mcc_InventoryDailyDetail
ORDER BY ItemClass4, ItemNumber

The other dataset is for the pricing with this query -
SELECT DISTINCT MAX(InvoiceDate) AS InvDate, LineItemNumber, MAX(InvoiceLocalUnitPrice) AS Price
FROM         dbo.MCC_vwSalesTotalsWithInvoices
GROUP BY LineItemNumber
HAVING      (LineItemNumber <> 'FREIGHT')
oRDER BY LineItemNumber
1 Solution
You can't associate a single object with multiple dataset. The mapping is one to one.
ValentinoVBI ConsultantCommented:
I have the impression that something in your logic is missing.  How are you matching the items?  Using the ItemNumber?  So you'd have each ItemNumber in both datasets?

As TempDBA said: you can only bind one dataset to a tablix.  Unless you can somehow get all your data in one query, perhaps the new lookup functions can help you out?  Check out the following article on how to use them: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/A_3433-Looking-Up-Data-On-Different-Sources.html
tgfo4927Author Commented:
this provided some good insight into the lookup feature
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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