05-31-13, 14:57 #1Registered User
- Join Date
- May 2013
Unanswered: Reporting automation using SQL SERVER 2008 R2 Reporting Services
I am a relatively new user of SQL SERVER 2008 R2 Reporting services and am in the process of automating (subscriptions) much of our reporting needs.
I have a batch of reports that follow this scenario that I would like to automate from the current manual process.
I have two separate databases on two different servers. I use ODBC connections to query both servers.
ODBC1 on the first server returns the following data fields:
This is saved as query1
ODBC2 on the second server returns the following data fields:
WHERE LN_EXTRA.doc_type = 'COLLATERAL'
This is saved as query 2
Both queries are imported into MS ACCESS as new tables
Left Outer Join query1.acn = query2.acct
Left Outer Join query1.ln_sfx = query2.ln_sfx
Query run to return the following:
Where query2.doc_type does not equal 'COLLATERAL'
I would love to be able to automate this process into SSRS. I understand there may be a way to "link" these two tables and design a report in SSRS that would be able to accomplish my goal.
Any help would be greatly appreciated....
05-31-13, 16:01 #2Annie's Dog Walker
Provided Answers: 6
- Join Date
- Nov 2004
- on the wrong server
Link one server to the other and then only execute one procedure.
sp_addlinkedserver (Transact-SQL)If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry. Earnest Hemingway, A Farewell To Arms.