Convert Access Reports to SQL Server 2008

Question

Convert Access Reports to SQL Server 2008

irebhahn

SSC Enthusiast

Points: 119
More actions
June 20, 2013 at 8:59 am

#395008

First post- please forgive me if I'm not following protacal for posting a question.
We are in the process of migrating MS Access reports to SQL Server 2008 R2 Report Builder 3.0, and I'm having trouble doing so with a few reports.
In Access we have reports that run/reference mutiple queries in order to excute the final report. My question is how to convert a report that runs mutiple queries over to SQL, if possible?
I attached my queries from Access as a visual to see what I'm trying to achieve here. The first sql report within my attachment is the 'final report.
Any help or suggestions to point me in the right direction would be very much appericated!
Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Chrissy321 SSCoach Points: 15763 More actions · Answer 1

In Access we have reports that run/reference mutiple queries in order to excute the final report. My question is how to convert a report that runs mutiple queries over to SQL, if possible?

Are your multiple queries dependent on each other or are they independent?

If the former you can create a stored procedure that creates your preliminary and intermediate results. Then your final select can access the intermediate results.

If the latter you want multiple datasets in your report each bound to a different query or procedure.

irebhahn SSC Enthusiast Points: 119 More actions · Answer 2

Thank you for the reply Chrissy321.

My queries are dependent on one another. I will search the forum on how to create a stored procedure- unless you know a good link/site to check out.

Thanks again- is there anything I need to do to give credit for the response? I Want to make sure I follow the etiquette here in the forum(s).

Chrissy321 SSCoach Points: 15763 More actions · Answer 3

I would definitely read up on stored procedures. Even if you do not need to use intermediate result sets there are numerous advantages to using stored procedures in reports.

Below is an example of a stored procedure using table variables. You can also use temporary tables.

What the procedure does is non-sensical and just as an example.

CREATE PROCEDURE Test1

AS

--Create a interim table

DECLARE @Table1 TABLE (Column1 int)

--Populate the interim table

INSERT INTO @Table1 SELECT 1

INSERT INTO @Table1 SELECT 5

INSERT INTO @Table1 SELECT 6

--uncomment for testing

--SELECT * FROM @Table1

--Create a interim table

DECLARE @Table2 TABLE (Column1 int)

--Populate the interim table from the first table

INSERT INTO @Table2

SELECT

SUM(Column1)

FROM

@Table1

--uncomment for testing

--SELECT * FROM @Table2

--Final select

SELECT

'MyData' as Label, --add some text

Column1 * 100 AS MyData --calculate something

FROM

@Table2

--Execute the procedure

--EXECUTE Test1

As far as forum etiquette just let everyone know that your question has been answered and you do not need any further assistance with your post.

Chrissy321 SSCoach Points: 15763 More actions · Answer 4

http://www.mssqltips.com/sqlservertutorial/160/sql-server-stored-procedure/

This looks like a good tutorial.

irebhahn SSC Enthusiast Points: 119 More actions · Answer 5

Thank you for your help to resolve my question.

Have a good day!