locked
SSRS List with multi datasets RRS feed

  • Question

  • Hi,

    I have three tables, Sales, SalesDetail1, SalesDetail2, to show in an SSRS report.  I want the report format to be grouped like this:

    Sales......

           SalesDetail1......

           SalesDetail2......

    There is relationship between Sales and SalesDetail1, Sales and SalesDetail2, but NOT SalesDetail1 and SalesDetail2.  I can create 3 datasets each maps to a table, or two datasets each maps to an Inner Join relationship.  However, SSRS List only allows one dataset, how would I achieve the report format?  Thanks in advance.

    Thursday, July 31, 2014 7:44 PM

All replies

  • Hi,

    You could link two datasets in SSRS using a lookup function - like master detail tables.

    http://msdn.microsoft.com/en-us/library/ee210531.aspx

    Regards,

    Jithesh

    Friday, August 1, 2014 1:46 AM
  • Hi,

    First try to build one dataset using Join Query.

    If not possible then it that case where we need to bind more than one dataset,We use Lookup funcation.

    Syntax :Lookup(source_expression, destination_expression, result_expression, dataset)

    Example:  = Lookup(Fields!ProdID.Value., Fields!ProductID.Value, Fields!ProductName.Value, "Products")

    source_expression (Variant) : An expression that is evaluated in the current scope and that specifies the name or key to look up. For example, =Fields!ProdID.Value.

    destination_expression(Variant) : An expression that is evaluated for each row in a dataset and that specifies the name or key to match on. For example, =Fields!ProductID.Value.

    result_expression(Variant) : An expression that is evaluated for the row in the dataset where source_expression = destination_expression, and that specifies the value to retrieve. For example, =Fields!ProductName.Value.

    dataset  : A constant that specifies the name of a dataset in the report. For example, "Products".


    Let me know if you need more explanation.

    thanks

    Friday, August 1, 2014 5:48 AM
  • If you don't need SalesDetail1 and SalesDetail2 on the same record, you could join Sales and SalesDetails1 together with an inner join and then use a union statement to append on Sales and SalesDetails2 (as long as the columns are the same).
    Friday, August 1, 2014 11:37 AM
  • Hi,

    Thanks everyone.  I created Left Join Query to build one big dataset.  Instead of using List in SSRS, I used nested Tables.  Each Table is grouped by an attribute.  In this way, I can nest more than two tables to achieve multi-level report.  The report looks like this:

    Sales......

           SalesDetail1......

                 SalesLines1......

                 SalesLines2......

           SalesDetail2......

    Tuesday, August 19, 2014 2:51 PM
  • Make one main report and add details as sub reports. In sub report pass the id of salesorder as parameter.

    Regards Faisal

    Tuesday, August 19, 2014 3:16 PM