Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    combining two data views (VB.NET 2003)

    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?
    Carpy Diem, it's .

  2. #2
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combining two data views (VB.NET 2003)

    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.
    Carpy Diem, it&#39;s .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •