**Download source files here: RM_HistoricalAgedTrialBalance_UPSZone.zip
1. Verify the Dynamics SSRS reports have been deployed. If they have not been deployed, please see this article: http://dyndeveloper.com/articleview.aspx?ArticleID=194
2. Open the report. I used SQL Server Report Builder. Find the main dataset of the report. I am modifying the Historical Aged Trial Balance report so the main dataset is "dsGP10Proc". Right click on that dataset and open Dataset Properties. The stored procedure for this dataset is "seermHATBSRSWrapper".
3. Open up this stored procedure in the SQL Server Management Studio to Modify. The time consuming part of the modification will be getting data types for all the returned fields of the report. The report data returned will be stored in a temporary table. The data types and field count MUST be exactly as the report data being returned.
4. Once you have the field count and data type correct, create a new stored procedure. My SP is named "dd_seermHATBSRSWrapper". The temporary table is loaded with the report returned data. You can now join this temporary table with other tables to return additional fields, filtering, etc.
insert into @Out <---Temp Table
EXEC seermHATBSRSWrapper
--Input report parameters
@I_dAgingDate
,@I_cStartCustomerNumber
,@I_cEndCustomerNumber
,@I_cStartCustomerName
,@I_cEndCustomerNumber
,@I_cStartClassID
,@I_cEndClassID
,@I_cStartSalesPersonID
,@I_cEndSalesPersonID
,@I_cStartSalesTerritory
,@I_cEndSalesTerritory....
Select (Temp Table Fields), (New fields if any)
FROM @Out o <---Temp Table
JOIN RM00101 rm ON o.CUSTNMBR = rm.CUSTNMBR
WHERE rm.UPSZONE = @I_cUPSZone
5. Now that your new stored procedure has been created, you have to reset the name in the SSRS dataset property and select "Refresh Fields". This should reset all the fields from the new stored procedure and add any new fields you may have added.
6. All that's left is modifying the report to accept the new fields as parameters, report fields, etc.