October 8, 2013 at 10:24 am
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?
October 8, 2013 at 12:38 pm
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);October 9, 2013 at 7:03 am
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.
October 9, 2013 at 12:24 pm
Create one Report and add two data sets.
when you click on details > it will prompt you to choose the dataset.
October 9, 2013 at 1:24 pm
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")
)
October 9, 2013 at 4:05 pm
I am planning on using two different paramters. Thus the sql would not be using the same fields.
October 9, 2013 at 5:09 pm
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.
October 10, 2013 at 8:03 am
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.
October 10, 2013 at 8:08 am
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);October 10, 2013 at 8:24 am
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.
October 10, 2013 at 10:35 am
To the best of my knowledge it is not possible to conditionally select the dataset that you'd like to use for a single tablix.
Another solution (to avoid the expression based text boxes) would be to duplicate the summary line and then conditionally show/hide the tablix (so that only one shows, dependent on the parameter that is selected).
The expression for the visibility of the tablix could be something along the lines of
Conditional Statement for Tablix1 (which you want to show if Parameter1 is selected):
=IIF(IsNothing(@Parameter1), false, true)
Conditional Statement for Tablix2 (which you want to show if Parameter2 is selected):
=IIF(IsNothing(@Parameter2), false, true)
October 10, 2013 at 11:16 am
Assuming that the datasets are the same but come from different tables then you can combine the two queries into a single dataset.
Change the dataset query to be like the demo below:
Declare @sql varchar(2000)
Set @sql = case
when @param =1
then 'select a,b,c from dbo.table1'
else
then 'select a,b,c from dbo.table2'
End
Exec(@sql)
As long as the two result sets return the same column names and types this will work.
Fitz
October 10, 2013 at 11:54 am
Personally, I'd move the decision logic to the procedure. have it accept both parameters as nullable, then handle your logic there.
The Report then only needs one dataset.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 10, 2013 at 12:55 pm
Jason Selburg (10/10/2013)
Personally, I'd move the decision logic to the procedure. have it accept both parameters as nullable, then handle your logic there.The Report then only needs one dataset.
No problem with that idea, just hated the idea of returning two complete datasets then choosing which to show as the number of rows in the tablix would be dictated by the tablix linked dataset.
Fitz
October 10, 2013 at 1:19 pm
Mark Fitzgerald-331224 (10/10/2013)
Jason Selburg (10/10/2013)
Personally, I'd move the decision logic to the procedure. have it accept both parameters as nullable, then handle your logic there.The Report then only needs one dataset.
No problem with that idea, just hated the idea of returning two complete datasets then choosing which to show as the number of rows in the tablix would be dictated by the tablix linked dataset.
Fitz
NO! Don't return two diff. result sets. Use the proc to determine which set to return. ** this assumes they have the same columns of course.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply