none
VBA macro for XML RRS feed

  • Question

  • Hi Experts,

    We need to daily develop 4-5 ssrs reports and it takes a lot of efforts,time and even effects the quality. I was going through some manual and discovered that RDL (SSRS) language is based totally on xml. So i need your help for automating in developing a VBA macro when executed automatically generates a formatted XML  like we have in rdl file..

    The excel spreadsheet will contain 5 sheets :

    1. For defining the report parameters.

    2. Report Data Set and SQL Query.

    3. Defining the tablix

    4. Setting the properties of tablix. Like size,location and color.

    Exec of macro and rdl file ready.

    Kindly let me know if its possible to achieve this requirement or not.

    Thanks

    Priya


    • Edited by Priya Bange Monday, November 7, 2016 4:27 AM .
    Monday, November 7, 2016 4:25 AM

All replies

  • Hi,

    It is impossible for us to generate RDL file by using Excel Object model.

    We could use Excel to create an XML file, please visit Overview of XML in Excel.

    We need to create the RDL XSD, then import the data.

     For more information, please visit

    Creating an XML Mapping Schema in Excel 2010

    Part 1: Automating the XML Data Mapping Process in Excel 2003

    Importing XML Data into a Mapped List in Excel 2010

     

    According to Report Definition Language (SSRS):

    One of the easiest ways to generate RDL from an application is to use the Microsoft .NET Framework classes of the System.Xml namespace and System.Linq namespace. One class in particular, the XmlTextWriter class, can be used to write RDL. With XmlTextWriter, you can generate a complete report definition from start to finish in any .NET Framework application. Developers can also extend RDL by adding custom report items with custom properties. For more on the XmlTextWriter class and the System.Xml namespace, see the Microsoft .NET Framework Developer's Guide. For more information about Language-Integrated Query (LINQ), search for "LINQ to XML" on MSDN.


    I would suggest you use C# or VB.NET to generate RDL and get data for each field from Excel using Excel interop.

    Regards,

    Celeste



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, November 9, 2016 6:57 AM
    Moderator