Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

use 2 datasets on detail line of SSRS 2008 r2 Expand / Collapse
Author
Message
Posted Tuesday, October 8, 2013 10:24 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 9:33 AM
Points: 379, Visits: 451
In a SSRS 2008 r2 report, there is currently one dataset that is used for the detail line. However, now I would like to use 2 different datasets on the detail line that is displayed. The code would know what dataset to use based upon a new parameter that would be passed to the rdl.

Thus can you tell me if what I am thinking is possibly? If so, how would you code the solution?

If not, can you tell me how you would solve this problem and how to code the solution?
Post #1502709
Posted Tuesday, October 8, 2013 12:38 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:37 PM
Points: 1,817, Visits: 5,919
I would probably have two reports, one for each dataset, then a master report which displays one or other of the reports as sub-reports, maybe...

Or have two detail lines whose visibility is dependent on the value in the report parameter...

Not tried these though...


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1502766
    Posted Wednesday, October 9, 2013 7:03 AM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Tuesday, November 5, 2013 10:40 AM
    Points: 3, Visits: 38
    Wendy what kind of report are you creating? Why would you need two rdls? I do know that if you have two datasets, you can create a detail line that contains both datasets as long as each dataset has a value that is shared between the two of them.

    To give you an example, I created an open close report where I had a dataset of all of the projects and the number of defects opened. I created another dataset with all of the projects and the values that were closed. The final result was a single report where the detail line was a combination of those two datasets. Each column in the report, was a combination of those two datasets. Does that help any? I am somewhat unclear what the goal of your report is.
    Post #1503076
    Posted Wednesday, October 9, 2013 12:24 PM
    SSC Veteran

    SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

    Group: General Forum Members
    Last Login: Thursday, December 18, 2014 8:52 AM
    Points: 253, Visits: 761
    Create one Report and add two data sets.
    when you click on details > it will prompt you to choose the dataset.
    Post #1503255
    Posted Wednesday, October 9, 2013 1:24 PM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Thursday, December 12, 2013 11:18 AM
    Points: 4, Visits: 28
    How much detail is included in the details summary?
    Would it be feasible to use conditional expressions to select which dataset values are actually pulled from?
    You can specify explicitly the scope (of the Dataset) from which the Fields are pulled by.
    You would put a TextBox in your Details section with an expression which indicates which dataset (and scope) to choose from.

    Assuming the following:
    - You have a parameter named @WhichDataSetParameter which contains the indication to which dataset you'd like to use in the details section.
    - You have two datasets that you're choosing from named DataSet1 and DataSet2

    Your TextBox expression can be the following:
    =Switch
    (
    @WhichDataSetParameter = "Use Dataset 1",
    Sum(Fields!DetailsFirstValueField.Value, "DataSet1"),

    @WhichDataSetParameter = "Use Dataset 2",
    Sum(Fields!DetailsFirstValueField.Value, "DataSet2")
    )

    Post #1503277
    Posted Wednesday, October 9, 2013 4:05 PM
    Old Hand

    Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

    Group: General Forum Members
    Last Login: Wednesday, December 17, 2014 9:33 AM
    Points: 379, Visits: 451
    I am planning on using two different paramters. Thus the sql would not be using the same fields.
    Post #1503319
    Posted Wednesday, October 9, 2013 5:09 PM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Thursday, December 12, 2013 11:18 AM
    Points: 4, Visits: 28
    Without further clarification, I am having a hard time understanding what your exact application will be and so I am not sure if I am providing you with the information you need.

    The Switch expression would still work regardless of whether or not you use different fields.

    Example:
    =Switch
    (
    @WhichDataSetParameter = "Use Dataset 1",
    Sum(Fields!DetailsFirstValueField.Value, "DataSet1"),

    @WhichDataSetParameter = "Use Dataset 2",
    Sum(Fields!SomeOtherField.Value, "DataSet2")
    )

    Or if you meant that the parameters would be different based on whichever dataset was used, then you can can extend the boolean expression to determine the dataset:
    =Switch
    (
    Not IsNothing(@DataSet1Parameter) OR Not IsNothing(@DataSet2Parameter),
    Sum(Fields!DetailsFirstValueField.Value, "SummaryDetailsDataSet"),

    IsNothing(@DataSet1Parameter) AND IsNothing(@DataSet2Parameter),
    Sum(Fields!DetailsFirstValueField.Value, "BackupSummaryDetailsDataSet")
    )

    NB. The above would really be best as an IIF statement, but I kept the Switch simply as that's what we'd been using so far. No matter how you go about the solution, conditional expressions will allow you to select between different datasets which is dependent on the logic you provide.

    I would be happy to provide more details if you are able to provide a more inclusive breakdown of how the report logic works to determine which dataset you would elect to use.
    Post #1503329
    Posted Thursday, October 10, 2013 8:03 AM
    Old Hand

    Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

    Group: General Forum Members
    Last Login: Wednesday, December 17, 2014 9:33 AM
    Points: 379, Visits: 451
    I am basically working with a dashboard. The sql I am referring is for the detail line of the main rdl for the dashboard. I am trying to use only one rdl.

    Basically when the report runs, I know if the user has picked parameter 1 or parameter 2. If the user picks parameter 1, I want to use dataset #1. If the user picks parameter 2, I want to use dataset #2.

    Thus how can I use the switch statement to say what dataset to use? The data displayed in the report is the same. What is displayed is dependent upon what the user selects.
    Post #1503594
    Posted Thursday, October 10, 2013 8:08 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 6:37 PM
    Points: 1,817, Visits: 5,919
    What is your "detail line" contained in ?
    Is it a table?
    Do you just have one row in the dataset?

    Is it like this?

    Select Parameter 1 ,then display:

    Foo Foo Foo

    Select Parameter 2, then display:

    Bar Bar Bar


    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1503597
    Posted Thursday, October 10, 2013 8:24 AM
    Old Hand

    Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

    Group: General Forum Members
    Last Login: Wednesday, December 17, 2014 9:33 AM
    Points: 379, Visits: 451
    I could do as you suggested by placing a conditional expression on each 'TextBox in your Details section'. However, I would prefer to determine what dataset to use all at one time if that is possible.
    Post #1503618
    « Prev Topic | Next Topic »

    Add to briefcase 12»»

    Permissions Expand / Collapse