Kbase P131153: How to create a Microsoft SQL Server Reporting Services Report Model on an OpenEdge database
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
5/2/2008 |
|
Status: Unverified
GOAL:
How to create a Microsoft SQL Server Reporting Services Report Model on an OpenEdge database
FACT(s) (Environment):
Windows
OpenEdge 10.1B
FIX:
1. Make the OpenEdge database a linked server in Microsoft SQL Server. This can be done from SQL Server Management Studio as follows:
a. Set up a System DSN in ODBC Data Source Administrator.
b. Within SQL Server Management Studio, from Object Explorer, expand Server Objects. Right click Linked Servers and select New Linked Server... The New Linked Server dialog box
will appear.
c. For Linked server, specify any name; this will be the name of the linked server that will be seen in Object Explorer.
d. For Server Type, select Other data source. This will show additional options.
e. For Provider, select Microsoft OLE DB Provider for ODBC Drivers.
f. For Product Name, specify any name you choose, such as Progress or OpenEdge.
g. For Data source, use the System DSN name that was specified in ODBC Data Source Administrator.
h. The rest of the fields can be left blank.
i. Click Security in the "Select a page" frame at the left of the dialog box.
j. Click "Be made using this security context".
k. For "Remote login" and "With password", use the login and password that was supplied in the System DSN to connect to the OpenEdge database.
l. Click OK.
The above steps can also be done programmatically with the SQL Server system stored procedure master.dbo.sp_addlinkedserver. Supply all the above information as parameters. See SQL Server Books Online for more information on sp_addlinkedserver.)
2. Create views in the ReportServer database from the tables on the linked server. This can be done using OPENQUERY statements as in the following example:
USE ReportServer
GO
CREATE VIEW vCustomer AS SELECT * FROM OPENQUERY(myLinkedServerName,
'SELECT * FROM PUB.CUSTOMER')
3. Create the report model using the ReportServer database as the data source. Do this from a Visual Studio Report Model project as follows:
a. Right click Data Sources and choose Add New Data Source.
b. Verify that "Create a data source based on an existing or new connection" is selected, and click the "New" button.
c. The Provider is pre-selected as SqlClient Data Provider; it is grayed out and cannot be changed. This is correct because the data source is now the ReportServer database, not the OpenEdge database.
d. For Server name, choose the server on which SQL Server resides, which may or may not be the server on which the OpenEdge database resides.
e. Choose the authentication mode based on how SQL Server is set up.
f. Verify that "Select or enter a database name" is selected, and choose ReportServer as the database name.
g. Verify that "Test Connection" works, then click OK.
4. At this point a usable Data Source exists. Proceed to create a Data Source View and then a Report Model using the views created in the ReportServer database. For information on how to do this, see SQL Server Books Online.