Get an old copy of the report or run the report out of MS Access (as the basis of making a SSRS report)
Open the Report Properties and find the Record Source the Report is using: qry_Intermediary_Summary
Goto the Queries tab and right click the Query and choose Design View:
Right click and choose SQL View
Copy the MS Access SQL into SQL Management Studio
Edit the MS Access SQL so it is SQL Server compliant:
Escaped column names that are reserved SQL Keywords (eg GROUP)
Replace double quotes with single quotes
Make sure Table/Views exist
Remove Dollar signs
Convert Trim(...) to LTrim(RTrim(...)))
etc
When a Query uses nested queries we need to convert them to stored procedures and load the data in Temporary tables. eg
This SQL uses 3 nested queries:
qryTopStocks
qryTopStocksBuys
qryTopStocksSells
Notes:
We cannot make the queries Functions that return Tables because Functions don't support ORDER BY
We cannot turn the queries into Views because Views do not accept parameters
So we have to convert the queries into stored procedures:
Then in our DataSets we execute the stored procedures into Temporary tables that we can join:
Once you have the Query and it is returning the exact results as MS Access (view the old report to check), then we can create a new report.
I have used the MS Access to SSRS conversion tool. It managed to get the MS Access report designs but couldn't extract data. These SSRS2005 version reports are in directory AAA. Copy the Report you are converting from the AAA folder into the BBB project folder.
Import the old SSRS2005 report into BIDS/SSRS2016:
Select all the controls and copy them onto a new SSRS2016 report. Then delete the SSRS2005 report from the project. You only need it to copy the controls retaining the design, fonts and styles.
In BIDS map all the controls to their field in the DataSet.
1条答案
按热度按时间bweufnob1#
This SQL uses 3 nested queries:
Notes:
So we have to convert the queries into stored procedures:
Then in our DataSets we execute the stored procedures into Temporary tables that we can join:
I have used the MS Access to SSRS conversion tool. It managed to get the MS Access report designs but couldn't extract data. These SSRS2005 version reports are in directory AAA. Copy the Report you are converting from the AAA folder into the BBB project folder.
Import the old SSRS2005 report into BIDS/SSRS2016:
Select all the controls and copy them onto a new SSRS2016 report. Then delete the SSRS2005 report from the project. You only need it to copy the controls retaining the design, fonts and styles.
UPDATE: I just found this, it's quite helpful: https://www.databasejournal.com/features/msaccess/article.php/3705151/Converting-Access-Queries-to-SQL-Server.htm
And this is a really good explanation of MS Access queries vs SQL Server queries for linked DBs
https://www.mssqltips.com/sqlservertip/1482/microsoft-access-pass-through-queries-to-sql-server/