Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
How to Create Report Linking two Datasets

Respondido How to Create Report Linking two Datasets

  • Monday, February 18, 2013 2:50 PM
     
     
    Sign In to Vote
    0

    Hi,

    I'm using Report Builder.


    I have a Stock Table:

    Item StockDate Qty
    A 01/01/2013     3
    C 02/01/2013     4
    A 02/01/2013     1
    A 02/01/2013     3
    D 02/01/2013     7


    and Sales Table with the following fields:

    Item SalesDate Qty
    D 01/12/2012     1
    D 02/12/2012     3
    C 02/12/2012     3
    D 02/12/2012     1
    A 02/12/2012     7


    Now I want a report that links the 2 and shows the Stock Qty and Sales Qty by Item

    Item    StockQty    SalesQty

    Simple enough...but...I need parameters so that the user can restrict the stockqty and salesqty on receipt date and sales date.

    So for each Item I want to show the StockQty with a StockDate between 01/01/2013 and 02/01/2013 but want to see the SalesQty for that Item that have a SaleDate 01/12/2012 and 02/12/2012!


    Any ideas on how I do this?


    Thanks for any help in advance!





    • Edited by Tom Jenkin Monday, February 18, 2013 3:22 PM
    • Edited by Tom Jenkin Monday, February 18, 2013 3:23 PM
    • Edited by Tom Jenkin Monday, February 18, 2013 3:24 PM
    • Edited by Tom Jenkin Monday, February 18, 2013 3:25 PM
    •  
     

All Replies

  • Monday, February 18, 2013 3:41 PM
     
     Answered
    Sign In to Vote
    1

    Hi,

    there might be serveral ways to do this.

    One way could be to have 2 Datasets

    Your Dataset StockDate which will be filtered by the two Paramters by concept like this:

     StockdateStart <= Fields!Stockdate.value < StockdateEnd

    in SQL or MDX or the Dataset filter

    and a Dataset SalesDate with a similar filtering:

    SalesDateStart <= Fields!Salesdate.value < SalesdateEnd

    Your Table would be based on the StockDate Dataset (Could be the over way around too... but in this case let´s assume it this way)

    1. Fields!ItemCode.Value

    2. Fields!StockQty.Value

    3. Code.ObjectSum(LookupSet(Fields!ItemCode.Value, Fields!ItemCode.Value, Fields!SalesQty.Value, "SalesDate"))

    You need to have a custom code aggregate because lookupset returns an Object Type which SUM() can not handle....

    Function ObjectSum(ByVal items As Object()) As Decimal
      If items Is Nothing Then
        Return Nothing
      End If

      Dim s As Decimal = New Decimal()
      s = 0

      For Each item As Object In items
        s += Convert.ToDecimal(item)
      Next

      Return s
    End Function

    • Marked As Answer by Tom Jenkin Monday, February 25, 2013 3:48 PM
    •  
     
  • Monday, February 18, 2013 3:44 PM
     
     Answered
    Sign In to Vote
    1

    It all depends on what you want to do.

    • You can easily combine these two tables into a single one and then use it as a dataset for your table. You can apply some filter that will follow your logic with dates.
    • You also can use subreports. Create a main report with the first table, and a subreport with the second table as dataset. Pass the Item as a parameter to the subreport. Implement your date filtering logic here.
    • Marked As Answer by Tom Jenkin Monday, February 25, 2013 3:48 PM
    •  
     
  • Monday, February 18, 2013 4:30 PM
     
     
    Sign In to Vote
    0

    Thanks for the replies:

    Sven - I had just found my way to the Lookup function and began implementing it, only to find I do not have a version that can use this function!

    I was going to have  Dataset2 Grouped By Item (Summing Qty) with the parameters referenced in the Query text so that I could just use Lookup instead of LookupSet and the Custom Function.

    Irusul - I cannot combine these tables into a single one, as far as I can understand.

    I'm looking into the subreport solution now, or to see if I can have an upgrade so that I can use the Lookup or LookupSet functions.

     
  • Monday, February 18, 2013 4:35 PM
     
     
    Sign In to Vote
    0

    How do I check what version I have?

    At the moment when I try to use the Lookup function i get the error:

    "The Value expression for the textrun ‘Textbox22.Paragraphs[0].TextRuns[0]’ contains an error: [BC30451] Name 'Lookup' is not declared. (rsCompilerErrorInExpression)"

     
  • Monday, February 18, 2013 5:11 PM
     
     
    Sign In to Vote
    0

    I experimented with subreport and seem to be getting results, however one problem is that it is really slow. I presume this is because it is having to run the report for every row?

    Both tables will contain many thousands of rows and the sales table is evergrowing.

    Any ideas on how to make this more efficient?

    At the moment I have a subreport with one textbox only (sum of sales) it also contains 3 parameters, item no and salesdate from and to which are populated from the main report.

    Would the lookup provide faster results? if so I might have to see if we can upgrade if it isn't a major deal.

     
  • Tuesday, February 19, 2013 3:33 PM
     
     
    Sign In to Vote
    0

    If you feel that using subreports is slow, then you can do something else.

    You should join these two tables into a single one (not with UNION but with JOIN). Then you can implement drilldown there, but this requires more work and more knowledge on SSRS report design.

     
  • Wednesday, February 20, 2013 2:28 PM
     
     
    Sign In to Vote
    0

    I don't think I can join the 2 tables in this scenario. The report should show:

    Item | Total Stock for Dates X | Total Sales for Dates Y

      A                        200                                      30

    As the user is able to pick dates I must have the complete stock and sales tables involved and separate.

    For example Item A could have 10 records in the stock table, and 500 records in the sales table, trying to join here would result in 10*500 records just for this 1 Item?

     
  • Monday, February 25, 2013 3:28 PM
     
     
    Sign In to Vote
    0
    Sometimes you can get better results with 5000 rows, compared to using subreports.