A Versioning system for SSRS

  • DavidKBennett

    Right there with Babe

    Points: 752

    Comments posted to this topic are about the item A Versioning system for SSRS

  • Steph Locke

    SSCrazy

    Points: 2857

    Downloaded the pack and I'm going to give it a whirl this evening - if it works, it'll be fabulous 🙂 Great stuff!

  • shahzadalam

    SSC Rookie

    Points: 27

    It seems very good solution according to the requirements you mentioned and there can be one more enhancements that is to replace trigger with Change Tracking, that will reduce the impact of trigger as well. But I would recommend this if there are lot of users and lot of reports are being frequently modified or created.

  • Alex Friedman

    Right there with Babe

    Points: 755

    This is amazing, thanks!

  • Joffrey V

    SSC Enthusiast

    Points: 134

    Looks very interesting this, definitely going to have look at this. Well done!

    ===============================
    = Ignorance is the choice not to know =
    ===============================

  • sherifffruitfly

    Ten Centuries

    Points: 1198

    Cool - I hadn't considered source control for report builder content.

  • Dana Medley

    SSCertifiable

    Points: 6764

    Nicely done. I'm going to try it out as soon as I get a chance.



    Everything is awesome!

  • rud

    SSC Veteran

    Points: 296

    i've had a few issues where the xml conversion throws error "Illegal XML character. Had to adjust the query to this:

    INSERT INTO ReportServer.dbo.VersionStore

    ( ItemId ,

    Name ,

    ModifiedDate ,

    ModifiedBy ,

    InsertedDate ,

    Def

    )

    SELECT ItemID ,

    Name ,

    ModifiedDate ,

    u.UserName ,

    GETDATE() InsertedDate ,

    CONVERT(XML, ContentFinal) AS ContentXML

    FROM dbo.Catalog c

    INNER JOIN Users U ON u.UserID = c.ModifiedByID

    OUTER APPLY ( SELECT CONVERT(VARBINARY(MAX), [Content]) AS CONTENTVar

    ) t1

    OUTER APPLY ( SELECT CASE WHEN LEFT(ContentVar, 3) = 0xEFBBBF

    THEN CONVERT(VARBINARY(MAX), SUBSTRING(ContentVar,

    4,

    LEN(ContentVar)))

    ELSE ContentVar

    END AS Content2

    ) t2

    OUTER APPLY ( SELECT CASE WHEN RIGHT(Content2, 1) = 0x00

    THEN CONVERT(VARBINARY(MAX), LEFT(Content2,

    LEN(Content2)

    - 1))

    ELSE Content2

    END AS ContentFinal

    ) t3

    WHERE Content IS NOT NULL

    AND Type != 3

    AND ItemID IN (

    SELECT c.ItemID

    FROM Catalog C

    LEFT OUTER JOIN ( SELECT v1.ItemID ,

    MAX(v1.modifiedDate) modifiedDate

    FROM VersionStore V1

    GROUP BY ItemID

    ) V ON C.ItemID = v.ItemID

    WHERE Content IS NOT NULL

    AND ( v.itemID IS NULL

    OR v.modifiedDate != c.ModifiedDate

    ) )

  • draggs

    Valued Member

    Points: 59

    Great work; this is a great real-time tracking system. I did something similar but to avoid touching the SSRS system I created a type two reporting dimension. Not real-time, but only a one day lag.

  • Clifton Collins

    Default port

    Points: 1433

    Using Visual Studio with VSS, TFS or Visual SVN to design your reports provides a method of versioning individual reports or an entire project/solution of reports. Couple this with rsbuild for package and deployment and it is simple to deploy a new or previous version of a report or entire project/solution of reports.

    If you are on a budget, check out VS Express 2010 or 2012 with TFS express.

    and rsbuild.

    [/url]

    [font="Arial"]Clifton G. Collins III[/font]

  • twgage

    SSC Journeyman

    Points: 82

    BRILLIANT.

    It does exactly what it should, nothing more.

    Clean, and quiet. NICELY done.

    And it makes so much more possible, by freeing the report writers from the "impending doom" of breaking something that they have no way of fixing.

    This should be integrated into the product, really. Or officially supported.

    Thank you for sharing!

    Bill

  • draggs

    Valued Member

    Points: 59

    The issue we faced was that more then 50 Analyst over several dozen locations created/modified reports. The check -in/out works well with developers, but not business users. We have seen a rise in adopting document management/version control over the years from business users, but most business still believe in the idea "Business at the speed of thought" not "Business at the speed of IT process." Most companies that have adopted this methodology have version control systems (SharePoint :-)).

  • kashyap10387 14496

    SSC Rookie

    Points: 36

    Great work. I am going to try it as soon as I get the chance. Thanks so much for sharing this..

  • mtassin

    SSC-Insane

    Points: 23096

    2008R2 and later support Sharepoint 2010 quite handily. Turn on the Document Version control for the site and you have each saved change in the history.

    Not with check-in comments like you get with TFS, but it's generally good enough to be able to click on the sharepoint drop down and see the history.

    Especially with SQL 2012 and PowerView, running SSRS in native report server mode is so SQL 2005. 🙂

    However, this is a nice work around if you can't put in Sharepoint 2010.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • Koen Verbeeck

    SSC Guru

    Points: 258928

    Very interesting, thanks for the article.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 15 posts - 1 through 15 (of 50 total)

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