SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


use 2 datasets on detail line of SSRS 2008 r2


use 2 datasets on detail line of SSRS 2008 r2

Author
Message
wendy elizabeth
wendy elizabeth
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3764 Visits: 824
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?
mister.magoo
mister.magoo
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10759 Visits: 7891
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


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • awworrell
    awworrell
    Grasshopper
    Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

    Group: General Forum Members
    Points: 11 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.
    sharonsql2013
    sharonsql2013
    Ten Centuries
    Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

    Group: General Forum Members
    Points: 1302 Visits: 1265
    Create one Report and add two data sets.
    when you click on details > it will prompt you to choose the dataset.
    brent.singleton
    brent.singleton
    Forum Newbie
    Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

    Group: General Forum Members
    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")
    )


    wendy elizabeth
    wendy elizabeth
    Hall of Fame
    Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

    Group: General Forum Members
    Points: 3764 Visits: 824
    I am planning on using two different paramters. Thus the sql would not be using the same fields.
    brent.singleton
    brent.singleton
    Forum Newbie
    Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

    Group: General Forum Members
    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.
    wendy elizabeth
    wendy elizabeth
    Hall of Fame
    Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

    Group: General Forum Members
    Points: 3764 Visits: 824
    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.
    mister.magoo
    mister.magoo
    SSChampion
    SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

    Group: General Forum Members
    Points: 10759 Visits: 7891
    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


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • wendy elizabeth
    wendy elizabeth
    Hall of Fame
    Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

    Group: General Forum Members
    Points: 3764 Visits: 824
    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.
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search