none
List error: How to make subreports always display?

    שאלה

  • I designed an RDL file which contains several subreports within a Tablix.  What I want this report to do is always display the subreports, regardless if the main report has any data.  These subreports are not subsets of the output data from the main report, but rather they use the same input parameters as the main report.  That is why there are cases where the subreports contain data and main reports do not.  How do I do this in SSRS 2008?

    I tried implementing this and I get an out of memory exception error.  What I did was I placed the subreports outside of the tablix and encapsulated both the tablix and subreports into a List.  Next I fed the same input parameters to these subreports as before and I created this dataset for the List control:

    ALTER Procedure [dbo].[rpt_rd_Lookup_Clients2_FC]
    (
    	@IncludeAllOption bit = 0,
    	@FundingSource varchar(MAX) = null
    )
    As
    
    SET NOCOUNT ON
    
    If @IncludeAllOption = 1
    BEGIN
    	Select
    		Null As [people_id],
    		'-All-' As [insured_name]
    	UNION ALL
    	SELECT DISTINCT
    		bac.[people_id],
    		bac.[insured_name]
        FROM benefits_assignments_clients_view bac    
        left join enrollment_info_expanded_view eiev on eiev.people_id = bac.people_id
    	WHERE payer_name like 'GA-%' and program_code = 'INT'
    	AND (@FundingSource Is Null OR (@FundingSource like bac.payer_name + '%' and @FundingSource like '%' + plan_name))
    	Order By [insured_name]
    END
    Else
    BEGIN
    	SELECT DISTINCT
    		bac.[people_id],
    		bac.[insured_name]
        FROM benefits_assignments_clients_view bac    
        left join enrollment_info_expanded_view eiev on eiev.people_id = bac.people_id
    	WHERE payer_name like 'GA-%' and program_code = 'INT'
    	AND (@FundingSource Is Null OR (@FundingSource like bac.payer_name + '%' and @FundingSource like '%' + plan_name))
    	Order By [insured_name]
    END

    And I filtered it on:

    =Parameters!ClientID.Value(0)

    as Expression and

    "=" as operator and

    "[people_id]" as the Value

    One more trick is that the ClientID parameter is a multi-select.  How can I make these subreports run off of all selected clients?


    Ryan D

    יום שלישי 13 מרץ 2012 21:41

כל התגובות

  • I designed an RDL file which contains several subreports within a Tablix.  What I want this report to do is always display the subreports, regardless if the main report has any data.  These subreports are not subsets of the output data from the main report, but rather they use the same input parameters as the main report.  That is why there are cases where the subreports contain data and main reports do not.  How do I do this in SSRS 2008?

    Ryan D

    יום שלישי 13 מרץ 2012 15:48
  • If the subreports are not dependent on the main report's dataset, place the subreports outside of the Tablix and not within the Tablix.

    Best Regards,
    Datta
    ----------------------------------------------------------------------------------------------------
    Dattatrey Sindol
    My BlogDatta's Ramblings on Business Intelligence 'N' Life
    The information provided here is "AS IS" with no warranties, and confers no rights.
    Please mark the post as answered if it solves your problem.

    • הוצע כתשובה על-ידי i_h יום שלישי 13 מרץ 2012 16:41
    יום שלישי 13 מרץ 2012 16:06
  • Thanks this was helpful.  However, I tried implementing this and I get an out of memory exception error.  What I did was I placed the subreports outside of the tablix and encapsulated both the tablix and subreports into a List.  Next I fed the same input parameters to these subreports as before and I created this dataset for the List control:

    ALTER Procedure [dbo].[rpt_rd_Lookup_Clients2_FC]
    (
    	@IncludeAllOption bit = 0,
    	@FundingSource varchar(MAX) = null
    )
    As
    
    SET NOCOUNT ON
    
    If @IncludeAllOption = 1
    BEGIN
    	Select
    		Null As [people_id],
    		'-All-' As [insured_name]
    	UNION ALL
    	SELECT DISTINCT
    		bac.[people_id],
    		bac.[insured_name]
        FROM benefits_assignments_clients_view bac    
        left join enrollment_info_expanded_view eiev on eiev.people_id = bac.people_id
    	WHERE payer_name like 'GA-%' and program_code = 'INT'
    	AND (@FundingSource Is Null OR (@FundingSource like bac.payer_name + '%' and @FundingSource like '%' + plan_name))
    	Order By [insured_name]
    END
    Else
    BEGIN
    	SELECT DISTINCT
    		bac.[people_id],
    		bac.[insured_name]
        FROM benefits_assignments_clients_view bac    
        left join enrollment_info_expanded_view eiev on eiev.people_id = bac.people_id
    	WHERE payer_name like 'GA-%' and program_code = 'INT'
    	AND (@FundingSource Is Null OR (@FundingSource like bac.payer_name + '%' and @FundingSource like '%' + plan_name))
    	Order By [insured_name]
    END
    

    And I filtered it on:

    =Parameters!ClientID.Value(0)

    as Expression and

    "=" as operator and

    "[people_id]" as the Value

    One more trick is that the ClientID parameter is a multi-select.  How can I make these subreports run off of all selected clients?


    Ryan D

    יום שלישי 13 מרץ 2012 16:49
  • Hi ironyan77,

    You should create a multiple-value parameter for the report, and then define the filter like this:
    Expression: Parameters!ClientID.Value
    Operator: In
    Value:[people_ID]

    For more information, please see:
    Adding Parameters to Select Multiple Values in a List: http://technet.microsoft.com/en-us/library/aa337396.aspx
    Adding a Subreport and Parameters: http://technet.microsoft.com/en-us/library/ms160348(v=sql.100).aspx

    Regards,
    Bin Long

    TechNet Subscriber Support
    If you are
    TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Bin Long

    TechNet Community Support

    יום רביעי 14 מרץ 2012 07:30
  • Are you sure about this?  I am getting this error:

    and I configured this dataset exactly the way u described.


    Ryan D

    יום רביעי 14 מרץ 2012 12:48
  • Take a look at the below thread. You might find it useful.

    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/5dc24b8e-a7a3-48b4-816b-65ac6ce86887/


    Best Regards,
    Datta
    ----------------------------------------------------------------------------------------------------
    Dattatrey Sindol
    My BlogDatta's Ramblings on Business Intelligence 'N' Life
    The information provided here is "AS IS" with no warranties, and confers no rights.
    Please mark the post as answered if it solves your problem.

    שבת 17 מרץ 2012 10:49
  • Datta, can you please look at my other post regarding this same issue?

    my other post


    Ryan D

    יום שלישי 20 מרץ 2012 22:26