Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need to create report using ssrs. for that i create the main table contains the values from nine other tables which is the Source tables. Now the new source tables which are used for main table are in another server.So using transactional replication I got all the source tables from another server to my datawarehouse.Now I need the main table values are get automatically updated whenever the transactions(updates) made in the nine source tables.

I am new to replication So please help me.

Thanks in advance

share|improve this question
    
Thanks for the response but triggers are not used in replication because whenever transactions are happening its automatically fires the triggers from the table –  user3137222 Dec 27 '13 at 13:21
add comment

2 Answers

If you need your combined table updated automatically you would need to put triggers on the 9 tables which have been replicated to your server.

A better option would be to put an indexed view on top of the 9 tables (I'm assuming that your table is created with a single query). This way there's no need to handle updating your table as the indexed view would always be up to date and you can simply just query it. As it is an indexed view you can add any non-clustered indexes to it as needed to make performance nice and fast.

If the query to combine the 9 tables together into your schema a normal view should be fine. Just have your report query the view, and the data will always be up to date. The big difference between a normal view and an indexed view (above) is that with a normal view the indexes go on the 9 replicated tables where with an indexed view the indexes can be placed on the view directly.

What it comes down to, is that there isn't any need for an additional table.

share|improve this answer
add comment

I'm not SSRS expert but from replication point of view if you setup it as continues process and once you insert, update or delete data on source all changes will be automatically pull/push to your subscribers. Now from my point of view your question should be: when report should be refreshed to show fresh data. Think you can add trigger on your main table to trigger your report on SSRS.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.