locked
Joining same data from two different tables RRS feed

  • Question

  • Hello,

    I am using Visual studio 2005 and have such a problem. I have a report that takes most of its date from “ordersview” that is linked to lots of other fields. The final report has fields such as product name, number, Date, quantity and actual quantity etc. This works fine, but the program that I get the data from can’t keep all the data in itself, because it slows the time of its work enormously. So we made it so that all the data of orders that are completed ant older that 2 days would be copied to another table “OrdersArchive” and deleted from “Orders” table.

    Now I need to make a report that takes data from “ordersArchive” if its older that two days, and from “orders” if it is new. The fields are the same in both cases and I would like the data to appear in the same table so it could be grouped, sorted and evaluated by the same criteria. Is it even possible and if it is how should I do it?

    For now I only managed to create 2 separate tables in the same report that take date from different datasets.

    Regards

    Darius

     

    Monday, October 8, 2007 1:09 PM

Answers

  •  

    Two ways to handle something like.

     

    First and the best and my only recommendation: Create a stored proc that passes the parameters in and runs either two select's with a union or one select and one temp with a join. Then have the data set call the proc which will return all of the data that you are looking for as one table.

     

    Second way inside of report, I do not recommend:

    This could be done several ways.

    1. Create a single data set with multiple select's. One select to pull new data and one to pull old data with a union

    2. Create a single data set and use a temp table with old data and inner join on select of new data

     

    I would recommend using a proc for this. Trying to do all of this inside a rdl will result in a sluggish report.

     

     

     

    Monday, October 8, 2007 2:44 PM

All replies

  •  

    Two ways to handle something like.

     

    First and the best and my only recommendation: Create a stored proc that passes the parameters in and runs either two select's with a union or one select and one temp with a join. Then have the data set call the proc which will return all of the data that you are looking for as one table.

     

    Second way inside of report, I do not recommend:

    This could be done several ways.

    1. Create a single data set with multiple select's. One select to pull new data and one to pull old data with a union

    2. Create a single data set and use a temp table with old data and inner join on select of new data

     

    I would recommend using a proc for this. Trying to do all of this inside a rdl will result in a sluggish report.

     

     

     

    Monday, October 8, 2007 2:44 PM
  • Is it not possible to place a CASE statment in the SQL View that selects the appropriate field from the tables given the datediff(d,-2,Getdate()) criteria and the fields as its potential options?
    Monday, October 8, 2007 2:45 PM
  •  

    I think I understand the question. But I think the question you are asking is can you call multiple data sets within a single table/matrix/feild. The answer is yes but once again I do not recommend doing this.

     

    this would result in your report running a ton of statements just to figure out what data set the data should come from.

     

    Try combining your data into one table, normalized the data, add a date range min, max, etc.. and then run the report as one.

     

    Leaving the data seperate will kill the rdl file when published.

    Monday, October 8, 2007 5:22 PM