combining two data views (VB.NET 2003)

Topic

New Reply

I’m trying to do a SQL Reporting Service report where we want a star to show up under two conditions:

1. if the quantity ordered is more than the quantity on hand
2. if the total quantity ordered for the item (several orders are on the report) is more than the quantity on hand.

I’ve tried RunningValue, but this cummulates through the report, instead of totalling up the item. Although technically correct, this isn’t what the users want to see.

I did create a separate data view that summarizes the quantity ordered by item but I can’t figure out how to get this data to coordinate between the two views. I wish there was something similar to Access DLookup, but I can’t find anything. Any suggestions?

Reply | Quote

Replies

Well, I finally was able to answer my own question, but I wanted to post my answer in case someone else runs up against this problems (or I forget what I did last time).

I ended up creating two views on the SQL database – one with the orders, and the other with the order summary based on the first view. Then I went into the dataview on the SQL report, linked the two views, did a little

CASE WHEN (myview1.OrderQty)
> myview1.InventoryQty THEN ‘*’ ELSE CASE WHEN myview2.qtyShort < 0 THEN '*' ELSE ' ' END END AS star

where the myview2.qtyShort is calculated as MIN(myview1.InventoryQty) – SUM(myview2.OrderQty)

and the stars showed up on the correct items.

I'll probably due a bit more tweaking but at least it works.

Reply | Quote