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 Thursday, October 10, 2013 10:35 AM
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
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)

Post #1503694
Posted Thursday, October 10, 2013 11:16 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:37 AM
Points: 3,127, Visits: 1,355
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
Post #1503708
Posted Thursday, October 10, 2013 11:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:14 AM
Points: 2,717, Visits: 3,839
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 you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
Post #1503725
Posted Thursday, October 10, 2013 12:55 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:37 AM
Points: 3,127, Visits: 1,355
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
Post #1503750
Posted Thursday, October 10, 2013 1:19 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:14 AM
Points: 2,717, Visits: 3,839
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 you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
Post #1503762
Posted Thursday, October 10, 2013 1:50 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:37 AM
Points: 3,127, Visits: 1,355
Jason Selburg (10/10/2013)
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.


Sorry I was agreeing with you.

Fitz
Post #1503775
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse