I have an SSRS 2012 report that contains a sub-report within a tablix. I do this to provide a copy of the sub-report for each of the customers selected in the main report's multi-value @CustomerID parameter.
I have a data-set called Customers that is linked to the tablix. From this the tablix passes the field called CustomerID to the sub-report's @CustomerID parameter (in the sub-report the @CustomerID parameter is not multi-value).
I then have 3 other multi-value parameters (@Assignedto, @OperationalArea and @SiteClass) that I need to pass through to the sub-report. I am passing these through using the Parameters matching on the sub-report properties (for instance Assignedto in the sub-report is matched to Parameters!Assignedto.Value in the main report).
My problem is that if I have customers 1 and 2 selected and the three parameters only have items selected that exist for both customers then the sub-reports run fine. However, if I have selected an item in the parameters that only exists for customer 1, then the sub-report will not run for customer 2 (even if there are valid items selected for customer 2 also). This problem is occurring for all three multi-value parameters that are passed through to the sub-report.
For instance:
- Customer 1 and 2 selected alongside @Assignedto selections of Engineer 1 and Engineer 2 who both are valid selections for Customer 1 and Customer 2. In this instance sub-reports for Customer 1 and 2 will both be displayed.
- Customer 1 and 2 selected alongside selections of Engineers 1, 2, 3. Engineers 1 and 2 are valid for both customers. Engineer 3 is only valid for customer 1. In this instance only the sub-report for customer 1 will be displayed, even though there is data for Engineers 1 and 2 for customer 2. I get an error message saying Error: Subreport could not be shown.
When I run the sub-report itself this runs fine, but the values for the multi-value parameters are determined by the value of the @CustomerID parameter (using data-sets), so I cannot select values outside of the valid values for that customer.
I have run the SQL for all the data-sets in the sub-report in SSMS using values for the parameters that are outside of values that exist for the customers. These all ran fine.
I have also tried switching between using and not using the join function with the data-set parameters in the sub-report, but this does not do anything.
I have also tried to pass the multi-valued parameters values from the main report using a comma delimited list via the below query, that limits the list to valid values for the customer. This did not solve the problem.
I am at a lost and any help would be much appreciated.
IF OBJECT_ID('tempdb..#Data_Sub') IS NOT NULL DROP TABLE #Data_Sub;
Select distinct
CustomerID
,[AssignedToUser]
,null as OperationalArea
,null as SiteClass
Into
#Data_Sub
from
[FAMSDB].[dbo].[Incidents_Full_PI] as IFPI
inner join
(
select
item
from
[FAMSDB].[dbo].SplitToTable
(@CustomerID,',')
) as CID on IFPI.CustomerID = CID.item
where
ReportedFaultCode in ('SPD','SPO','SRD','SRO','SEO','SED')
and CASE when [AssignedToUser] in (select item from
FAMSDB.dbo.SplitToTable(@Assignedto,',')) then 1
else 0
end = 1
Union
Select distinct
CustomerID
,null
,OperationalArea
,null
from
[FAMSDB].[dbo].[Incidents_Full_PI] as IFPI
inner join
(
select
item
from
[FAMSDB].[dbo].SplitToTable
(@CustomerID,',')
) as CID on IFPI.CustomerID = CID.item
where
ReportedFaultCode in ('SPD','SPO','SRD','SRO','SEO','SED')
and CASE when [OperationalArea] in (select item from
FAMSDB.dbo.SplitToTable(@OperationalArea,',')) then 1
else 0
end = 1
Union
Select distinct
CustomerID
,null
,null
,SiteClass
from
[FAMSDB].[dbo].[Incidents_Full_PI] as IFPI
inner join
(
select
item
from
[FAMSDB].[dbo].SplitToTable
(@CustomerID,',')
) as CID on IFPI.CustomerID = CID.item
where
ReportedFaultCode in ('SPD','SPO','SRD','SRO','SEO','SED')
and CASE when [SiteClass] in (select item from
FAMSDB.dbo.SplitToTable(@SiteClass,',')) then 1
else 0
end = 1
union
select
item
,case when 'Unassigned' in (select item from
FAMSDB.dbo.SplitToTable(@Assignedto,',')) then 'Unassigned'
else null
end
,case when 'Unassigned' in (select item from
FAMSDB.dbo.SplitToTable(@OperationalArea,',')) then 'Unassigned'
else null
end
,case when 'Unassigned' in (select item from
FAMSDB.dbo.SplitToTable(@SiteClass,',')) then 'Unassigned'
else null
end
from
[FAMSDB].[dbo].SplitToTable (@CustomerID,',')
union
select
item
,case when 'N/A' in (select item from
FAMSDB.dbo.SplitToTable(@Assignedto,',')) then 'N/A'
else null
end
,case when 'N/A' in (select item from
FAMSDB.dbo.SplitToTable(@OperationalArea,',')) then 'N/A'
else null
end
,case when 'N/A' in (select item from
FAMSDB.dbo.SplitToTable(@SiteClass,',')) then 'N/A'
else null
end
from
[FAMSDB].[dbo].SplitToTable (@CustomerID,',')
Select
CustomerID
,AssignedToUser
,OperationalArea
,SiteClass
From
(
Select distinct
CustomerID
,case when len((Select [AssignedToUser]+',' From(Select
Distinct CustomerID,[AssignedToUser] From #Data_Sub) as b Where b.CustomerID
= a.CustomerID For XML Path('')))=0 then null
else LEft((Select
[AssignedToUser]+','
From
(
Select Distinct
CustomerID
,[AssignedToUser]
From
#Data_Sub
) as b
Where
b.CustomerID = a.CustomerID
For XML Path(''))
,len((Select
[AssignedToUser]+','
From
(
Select Distinct
CustomerID
,[AssignedToUser]
From
#Data_Sub
) as b
Where
b.CustomerID = a.CustomerID
For XML Path('')))-1
) end as AssignedToUser
,case when len((Select [OperationalArea]+',' From(Select
Distinct CustomerID,[OperationalArea] From #Data_Sub) as b Where
b.CustomerID = a.CustomerID For XML Path('')))=0 then null
else left((Select
[OperationalArea]+','
From
(
Select Distinct
CustomerID
,[OperationalArea]
From
#Data_Sub
) as b
Where
b.CustomerID = a.CustomerID
For XML Path('')),
lEn((Select
[OperationalArea]+','
From
(
Select Distinct
CustomerID
,[OperationalArea]
From
#Data_Sub
) as b
Where
b.CustomerID = a.CustomerID
For XML Path('')))-1
) end as OperationalArea
,case when len((Select [SiteClass]+',' From(Select
Distinct CustomerID,[SiteClass] From #Data_Sub) as b Where b.CustomerID =
a.CustomerID For XML Path('')))=0 then null
else Left((Select
[SiteClass]+','
From
(
Select Distinct
CustomerID
,[SiteClass]
From
#Data_Sub
) as b
Where
b.CustomerID = a.CustomerID
For XML Path(''))
,len((Select
[SiteClass]+','
From
(
Select Distinct
CustomerID
,[SiteClass]
From
#Data_Sub
) as b
Where
b.CustomerID = a.CustomerID
For XML Path('')))-1
) end as SiteClass
From
#Data_Sub as a
) as ConcatSub
where
AssignedToUser is not null
and OperationalArea is not null
and SiteClass is not null
Edit: I have not tested the sub-report by removing the CustomerID filter on the dataset that returns the values for the multi-value parameters. So they can now have parameter values selected that are not valid for the Customer selection. The sub-report runs fine still. Therefore the problem must be in how the parameters are being passed to the sub-report.
