Drill Down reports over SQL Server OLTP tables


  • Can we create a drill down reports in SSRS by directly connectign to OLTP SQL Server database? I understand that we can it do w/ SSAS cubes but need to know if something like connecting directly to sql server is possible for ad-hoc reporting if need be.

    Please share your experience.

    Tuesday, June 25, 2013 4:56 PM


All replies

  • Hi Powerpivotq

    The standard connection types are SQL Server (Any database on a specific instance) & SSAS Analysis services.  There are others, but from a microsoft perspective, these are the 2 main connections you will mostly use / setup.

    From a best practice perspective I believe Business Intelligence infrastructure would best support your particular request. First question would be how often will users be interacting with the data?  If you have a 5 - 10 minute window, then I would suggest Business Intelligence infrastructure.  

    Setup a Datamart (E.g. FACT_Product_Sales) in a Warehouse and move the data at regular intervals to the warehouse.  Design your reporting to run against the warehouse.  This way your data collection is well controlled and regular querying happens outside of the OLTP db.

    On the other hand if you HAVE to query the OLTP database directly you can (if the users need "live" time infromation).  I would just suggest that you implement sound stored procedures that are well tested and that your supporting tables are well indexed (to maximise performance).  This you can setup with a normal SQL server connection for your data source.

    Let me know if this is helpful, or if you have any further questions.


    Jason Vollaire

    Please mark this as "Answered" if this post answered your question or "Helpful" if this post was helpful.

    Whatever you do, do it with all your strength.

    Tuesday, June 25, 2013 9:38 PM
  • Hi Powerpivotq,

    From your description, you want to know if we can create a drill down reports in SSRS by directly connect to OLTP SQL Server database, right? Create drill down report has nothing to do with which type of data base we are using.

    Reference:Lesson 16: Drilldown Matrix Reports

    If I have anything misunderstood, please point it out.


    Charlie Liao
    TechNet Community Support

    Friday, June 28, 2013 3:27 AM