I have a legacy SSRS report that works as designed - returns a ton of data.
I created a derivation of it that is targeted toward using a specific value.
IOW, in the legacy report, the value is selected by the user, whereas in the derived report, it is "baked in."
The derived report, after much hourglassing and hamster cage rattling, finally throws up its hands and says, "An error occurred during local report processing. The size necessary to buffer the XML content exceeded the buffer quota."
A related question is here.
The only difference between the two reports is:
1) I removed one of the parameters
2) Where reference is made to that parameter, I replaced the code in the .rdl with a literal value.
Why would that change cause the report to go on strike/go AWOL?
To be more specific, here are the only differences between the legacy report and the derived report:
1) Legacy:
<DataSetName>CPSData</DataSetName>
New:
<DataSetName>DataSetOldChicago</DataSetName>
2) Legacy:
<Value>=Parameters!Unit.value+", For Price Period: "+Parameters!BegDate.value+" - "+Parameters!EndDate.value</Value>
New:
<Value>="For Price Period: "+Parameters!BegDate.value+" - "+Parameters!EndDate.value</Value>
3) Legacy:
<DataSet Name="CPSData">
<Query>
<DataSourceName>CPSData</DataSourceName>
<QueryParameters>
<QueryParameter Name="@Unit">
<Value>=Parameters!Unit.Value</Value>
</QueryParameter>
<QueryParameter Name="@BegDate">
<Value>=Parameters!BegDate.Value</Value>
</QueryParameter>
<QueryParameter Name="@EndDate">
<Value>=Parameters!EndDate.Value</Value>
</QueryParameter>
<QueryParameter Name="@SortBy">
<Value>="Products"</Value>
</QueryParameter>
</QueryParameters>
<CommandType>StoredProcedure</CommandType>
<CommandText>sp_ViewPriceMatrix_Variance_RockBottom</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
New:
<DataSet Name="DataSetOldChicago">
<Query>
<DataSourceName>CPSData</DataSourceName>
<QueryParameters>
<QueryParameter Name="@BegDate">
<Value>=Parameters!BegDate.Value</Value>
</QueryParameter>
<QueryParameter Name="@EndDate">
<Value>=Parameters!EndDate.Value</Value>
</QueryParameter>
<QueryParameter Name="@SortBy">
<Value>=Parameters!SortBy.Value</Value>
</QueryParameter>
</QueryParameters>
<CommandType>StoredProcedure</CommandType>
<CommandText>ViewPriceMatrix_CraftworksRollup_OldChicago</CommandText>
</Query>
- so the new one lacks the "Unit" parameter; after that, the fields are shown, which are identical except
in slightly different order for some reason - I don't know why that would be, but can't imagine that would cause a problem (which order the fields are listed in).
4) Legacy:
<DataSet Name="UnitDS">
<Query>
<DataSourceName>CPSData</DataSourceName>
<CommandText>select Unit from MasterUnits order by Unit</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="Unit">
<DataField>Unit</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
</DataSet>
New:
Missing, as user no longer selects the Unit
5) Legacy:
<ReportParameter Name="Unit">
<DataType>String</DataType>
<AllowBlank>true</AllowBlank>
<Prompt>Unit</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>UnitDS</DataSetName>
<ValueField>Unit</ValueField>
<LabelField>Unit</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
New:
Missing - now provided literally.
6) Legacy:
[ Nothing here ]
New:
<ReportParameter Name="SortBy">
<DataType>String</DataType>
<Prompt>Sort By</Prompt>
</ReportParameter>
-- so the new/non-working report has this "SortBy" param, which is hidden and provided a default value. But another derived report has the same parameter set up that way, and it causes no problems.
Any ideas on why this problem would raise its ogre-like visage?
UPDATE
The differences between the old (working) and derived (non-working) SPs are:
1)
Old:
ALTER Procedure [dbo].[sp_ViewPriceMatrix_Variance_test2]
@Unit varchar(4000),
@BegDate datetime,
@EndDate datetime,
@SortBy varchar(20)
New:
IF OBJECT_ID ( 'ViewPriceMatrix_CraftworksRollup_OldChicago', 'P' ) IS NOT NULL
DROP PROCEDURE ViewPriceMatrix_CraftworksRollup_OldChicago;
GO
CREATE PROCEDURE [dbo].[ViewPriceMatrix_CraftworksRollup_OldChicago]
@BegDate datetime,
@EndDate datetime
2)
Old:
where up.Unit = @Unit
New:
where up.Unit = 'OLD CHICAGO'
3)
-same as 2)
4)
Old:
Select @Statement = ('Update #TempContract Set [' + @PriceWeek + ']=''' + IsNull(@Price,'0.00') + ''' where ItemCode=''' + @ItemCode + ''' and Unit=''' + @Unit + ''' and [ShortName]=''' + @ShortName +'''')
New:
Select @Statement = ('Update #TempContract Set [' + @PriceWeek + ']=''' + IsNull(@Price,'0.00') + ''' where ItemCode=''' + @ItemCode + ''' and Unit=''''OLD CHICAGO'''' and [ShortName]=''' + @ShortName +'''')
5)
Old:
fetch next from SetPriceWeekSQL into @PriceWeek, @BegDate
while @@fetch_status = 0
begin
SET @Week = @Week + 1
IF(@SQLstring='')
Begin
SET @SQLstring = @SQLstring + 'Insert Into #Temp Select Unit, ShortName, ItemCode, Description, regionorder, Contractprice, IsNull('+
'['+@PriceWeek+'],''0.00'') as Price, (convert(decimal(10,3),''-0.001'')) as Variance,
'''+@PriceWeek+''' as PriceWeek, ''WK'+convert(varchar(2),@Week)+''' as Week From #TempContract'+@WherePriceWeek
IF(@SortBy='Members')
Begin
SET @SQLstring = @SQLstring + ' UNION Select Unit, ShortName, '''', ''zzzz'', '''', '''', ''0'' as Price, ''-0.001'' as Variance, '''' as PriceWeek, ''WK'+convert(varchar(2),@Week)+''' as Week From #TempContract'+@WherePriceWeek
End
Else
Begin
SET @SQLstring = @SQLstring + ' UNION Select Unit, '''', ItemCode, Description, ''1000'', Contractprice, ''0'' as Price, ''-0.001'' as Variance, '''' as PriceWeek, ''WK'+convert(varchar(2),@Week)+''' as Week From #TempContract'+@WherePriceWeek
End
End
ELSE
Begin
SET @SQLstring = @SQLstring + ' UNION '
SET @SQLstring = @SQLstring + 'Select Unit, ShortName, ItemCode, Description, regionorder, Contractprice, IsNull('+
'['+@PriceWeek+'],''0.00'') as Price, IsNull(convert(decimal(10,2),['+@PriceWeek+'])-convert(decimal(10,2),['+@LastPriceWeek+']),''0.00'') as Variance,
'''+@PriceWeek+''' as PriceWeek, ''WK'+convert(varchar(2),@Week)+''' as Week From #TempContract'+@WherePriceWeek
IF(@SortBy='Members')
Begin
SET @SQLstring = @SQLstring + ' UNION Select Unit, ShortName, '''', ''zzzz'', '''', '''', ''0'' as Price, ''0'' as Variance, '''' as PriceWeek, ''WK'+convert(varchar(2),@Week)+''' as Week From #TempContract Where IsNull(['+@LastPriceWeek+'],''0.00'')
<> ''0.00'' or IsNull(['+@PriceWeek+'],''0.00'') <> ''0.00'' '
End
Else
Begin
SET @SQLstring = @SQLstring + ' UNION Select Unit, '''', ItemCode, Description, ''1000'', Contractprice, ''0'' as Price, ''0'' as Variance, '''' as PriceWeek, ''WK'+convert(varchar(2),@Week)+''' as Week From #TempContract Where IsNull(['+@LastPriceWeek+'],''0.00'') <> ''0.00'' or IsNull(['+@PriceWeek+'],''0.00'') <> ''0.00'' '
End
End
New:
fetch next from SetPriceWeekSQL into @PriceWeek, @BegDate
while @@fetch_status = 0
begin
SET @Week = @Week + 1
IF(@SQLstring='')
Begin
SET @SQLstring = @SQLstring + 'Insert Into #Temp Select Unit, ShortName, ItemCode, Description, regionorder, Contractprice, IsNull('+
'['+@PriceWeek+'],''0.00'') as Price, (convert(decimal(10,3),''-0.001'')) as Variance,
'''+@PriceWeek+''' as PriceWeek, ''WK'+convert(varchar(2),@Week)+''' as Week From #TempContract'+@WherePriceWeek
SET @SQLstring = @SQLstring + ' UNION Select Unit, '''', ItemCode, Description, ''1000'', Contractprice, ''0'' as Price, ''-0.001'' as Variance, '''' as PriceWeek, ''WK'+convert(varchar(2),@Week)+''' as Week From #TempContract'+@WherePriceWeek
End
ELSE
Begin
SET @SQLstring = @SQLstring + ' UNION '
SET @SQLstring = @SQLstring + 'Select Unit, ShortName, ItemCode, Description, regionorder, Contractprice, IsNull('+
'['+@PriceWeek+'],''0.00'') as Price, IsNull(convert(decimal(10,2),['+@PriceWeek+'])-convert(decimal(10,2),['+@LastPriceWeek+']),''0.00'') as Variance,
'''+@PriceWeek+''' as PriceWeek, ''WK'+convert(varchar(2),@Week)+''' as Week From #TempContract'+@WherePriceWeek
SET @SQLstring = @SQLstring + ' UNION Select Unit, '''', ItemCode, Description, ''1000'', Contractprice, ''0'' as Price, ''0'' as Variance, '''' as PriceWeek, ''WK'+convert(varchar(2),@Week)+''' as Week From #TempContract Where IsNull(['+@LastPriceWeek+'],''0.00'') <> ''0.00'' or IsNull(['+@PriceWeek+'],''0.00'') <> ''0.00'' '
End
SET @LastPriceWeek = @PriceWeek
fetch next from SetPriceWeekSQL into @PriceWeek, @BegDate
end
6)
Old:
IF(@SortBy='Members')
Begin
Select
Unit,
ShortName,
ItemCode,
Description,
regionorder,
Contractprice,
convert(varchar(20),convert(decimal(10,2),Price)) as Price,
sum(convert(money,Variance)) as Variance,
VarianceAverage = convert(varchar(25),convert(decimal(10,2),(Select sum(convert(money,Variance)) From #Temp Where ShortName=T.ShortName and Week=T.Week) / Replace(((Select count(regionorder) From #Temp Where ShortName=T.ShortName and Week=T.Week)-count(
Variance)),'0','1'))),
PriceWeek,Week
From #Temp T
Group By
Unit,
ShortName,
ItemCode,
Description,
regionorder,
Contractprice,
Price,
PriceWeek,Week
Order By Week,ShortName,Description
End
ELSE
Begin
Select
Unit,
ShortName,
ItemCode,
Description,
regionorder,
Contractprice,
convert(varchar(20),convert(decimal(10,2),Price)) as Price,
sum(convert(money,Variance)) as Variance,
VarianceAverage = convert(varchar(25),convert(decimal(10,2),(Select sum(convert(money,Variance)) From #Temp Where ItemCode=T.ItemCode and Week=T.Week) / Replace(((Select count(regionorder) From #Temp Where ItemCode=T.ItemCode and Week=T.Week)-count(Variance)),'0','1'))),
PriceWeek,Week
From #Temp T
Group By
Unit,
ShortName,
ItemCode,
Description,
regionorder,
Contractprice,
Price,
PriceWeek,Week
Order By Week,Description,regionorder
End
New:
Select
Unit,
ShortName,
ItemCode,
Description,
regionorder,
Contractprice,
convert(varchar(20),convert(decimal(10,2),Price)) as Price,
sum(convert(money,Variance)) as Variance,
VarianceAverage = convert(varchar(25),convert(decimal(10,2),(Select sum(convert(money,Variance)) From #Temp Where ItemCode=T.ItemCode and Week=T.Week) / Replace(((Select count(regionorder) From #Temp Where ItemCode=T.ItemCode and Week=T.Week)-count(Variance)),'0','1'))),
PriceWeek,Week
From #Temp T
Group By
Unit,
ShortName,
ItemCode,
Description,
regionorder,
Contractprice,
Price,
PriceWeek,Week
Order By Week,Description,regionorder
