Navigate SSRS reports from a web application

I’m on a continuing quest to deliver SSRS reports through a public-facing web application. My user can authenticate, and I can forward their credentials to Report Server. Now I need a branded menu of reports.

imageGenerate a proxy
We’re going to get the list of reports from the Report Server SOAP API. So first, generate a proxy to call this API. The API is an old-fashioned asmx web service, so you can’t use WCF. Instead, we have to generate a web reference.

Create a new class library called “SQLServerReportingServices”. Right-click and select “Add Service Reference…”. Hit the “Advanced..” button, and then hit the “Add Web Reference” button.

Enter the URL of your report service, followed by “ReportService2010.asmx?wsdl”. You can get this URL by opening Reporting Services Configuration Manager and clicking Web Service URL. I entered “http://dit3074lt2:8080/ReportServer_SQL2008R2/ReportService2010.asmx?wsdl”. Click “Go”.

Enter a meaningful web reference name, like “ReportServer”. This is appended to the class library name to give the namespace of the proxy. So the proxy class is “SQLServerReportingServices.ReportServer.ReportingService2010”. Redundant, I know.

Experiment with the proxy
Now create a new unit test project. Not because calling an external system like SSRS is a good unit test, but because it’s more convenient than experimenting in a web project. Add a reference to the SQLServerReportingServices class library that you just created. Also, copy the app.config file from SQLServerReportingServices . Finally, add a reference to “System.Web.Services”. Now we can experiment.

Let’s begin by calling the ListChildren method. This method gives you a list of CatalogItem objects in a report folder. CatalogItem’s properties give you information about each item, including a Path that you can use in another call to ListChildren. You’re actual number of items will vary, but you should get back an array.

[TestClass]
public class ReportNavigationTest
{
    public TestContext TestContext { get; set; }

    private ReportingService2010 _client;

    [TestInitialize]
    public void Initialize()
    {
        _client = new ReportingService2010();
        _client.Credentials = CredentialCache.DefaultCredentials;
    }

    [TestCleanup]
    public void Cleanup()
    {
        _client.Dispose();
    }

    [TestMethod]
    public void GetTopLevelItems()
    {
        CatalogItem[] children = _client.ListChildren("/", false);

        Assert.AreEqual(6, children.Length);
    }
}

If you get the exception “The request failed with HTTP status 401: Unauthorized”, be sure to initialize Credentials. Since you are running the unit test under your own account, the default credentials will be your own. Within the web application, we’ll need to forward the credentials of the logged-in user just like we did in the last post.

After experimenting in the unit test for a bit, we can move to the actual web application.

Walk the folder structure

In a modern web application, user’s don’t want to click on a folder and wait for a postback to show its contents. If the folder structure is not too deep or too populated, we can display the entire contents on one page. If it is a bit much for one page, then users expect drop-down menus. In either scenario, we need to get the entire folder structure from a single call.

Fortunately, the reporting service lets us get all of the items in one call. Unfortunately, it returns these as a flat array. We have to render it as the folder structure that it truly is. To help with that, we’ll transform the array into a set of classes. See the Composite pattern in your handy Gang of Four book.

public abstract class Item
{
    private string _name;

    public Item(string name)
    {
        _name = name;
    }

    public string Name
    {
        get { return _name; }
    }

    public abstract void Update();
}

public class Folder : Item
{
    private List<Item> _items = new List<Item>();
    private bool _containsAnyReport;

    public Folder(string name)
        : base(name)
    {
    }

    public void AddItem(Item item)
    {
        _items.Add(item);
    }

    public override void Update()
    {
        // Recursively update the items.
        foreach (Item item in _items)
            item.Update();

        // This folder contains a report if any of its items is a report,
        // or if any of its sub folders contain a report.
        _containsAnyReport =
            _items
                .OfType<Report>()
                .Any() ||
            _items
                .OfType<Folder>()
                .Any(folder => folder.ContainsAnyReport);
    }

    public bool ContainsAnyReport
    {
        get { return _containsAnyReport; }
    }

    public IEnumerable<Item> Items
    {
        get { return _items; }
    }
}

public class Report : Item
{
    private string _path;

    public Report(string name, string path)
        : base(name)
    {
        _path = path;
    }

    public override void Update()
    {
    }
}

The Update method walks the structure and updates the ContainsAnyReport property. We can later use this property while rendering the HTML. If the folder does not contain any report, we don’t need to render it. This will hide any folders that the admin created just for data sources or some other resource.

Next we need to turn the flat array into a hierarchy. For this, we use a stack.

CatalogItem[] children = _client.ListChildren("/", true);

// Keep the current folders on a stack.
Folder root = new Folder("");
Stack<Folder> folderStack = new Stack<Folder>();
folderStack.Push(root);

// Convert the flat array into a folder structure.
string currentPath = "/";
Array.Sort(children, (a, b) => a.Path.CompareTo(b.Path));
foreach (CatalogItem item in children)
{
    // Get the path up to and including the final slash.
    string parentPath = item.Path.Substring(0, item.Path.LastIndexOf("/") + 1);
    while (parentPath != currentPath)
    {
        // Unwind the stack to get back to the parent folder.
        folderStack.Pop();
        currentPath = currentPath.Substring(0, currentPath.LastIndexOf("/", currentPath.Length - 2) + 1);
    }

    if (item.TypeName == "Folder")
    {
        // Push the new folder to the stack.
        Folder folder = new Folder(item.Name);
        folderStack.Peek().AddItem(folder);
        folderStack.Push(folder);
        currentPath = item.Path + "/";
    }
    else if (item.TypeName == "Report")
    {
        // Add the report to the current folder.
        folderStack.Peek().AddItem(new Report(item.Name, item.Path));
    }
}

// Figure out which folders contain reports.
root.Update();

We have to sort the array by the path to ensure that everything in a folder is clumped together. Then we add folders and reports to the current folder. If the path deviates, we pop the stack until we get back on track.

Now you can recursively walk the folder structure to output <div> tags or <ul> lists. That’s an exercise left to the reader, since it will depend upon how you want to render your menu.

Next steps

As it turns out, we have some applications that don’t authenticate against Active Directory. We would like to use SSRS for those applications as well. For that, we will have to set up a shadow user, which has access to SSRS. Our next step is to implement authentication and authorization for a logged-in user, even though the application accesses SSRS via the shadow user.

6 Responses to “Navigate SSRS reports from a web application”

  1. Andrew Says:

    Hi

    I'm trying to add web reference to ReportSerive2010.asmx web service with VS2010, but receive the following error message:

    "Reporting Services Error The path of the item 'wsdl' is not valid." If I remove "?wsdl" from the end of url, I receive the html page which displays the ReportServer content, and "Add Reference" button is disabled. Can you help with it? I use SSRS in SharePoint Integrated mode, SSRS 2008 R2, SharePoint 2010.

    Thanks, Andrew

  2. Michael L Perry Says:

    Andrew,

    I can reproduce that problem if I leave off the ReportService2010.asmx part of the URL. Please make sure that you didn't drop that.

    Here's how I get to the Report Server content:
    http://dit3074lt2:8080/ReportServer_SQL2008R2

    This gets me to the wsdl:
    http://dit3074lt2:8080/ReportServer_SQL2008R2/ReportService2010.asmx?wsdl

    As does this:
    http://dit3074lt2:8080/ReportServer_SQL2008R2/ReportService2010.asmx

    This gets me the "The path of the item 'wsdl' is not valid" error:
    http://dit3074lt2:8080/ReportServer_SQL2008R2?wsdl

  3. Andrew Says:

    Thank you Michael for the response.

    Dut in my case it looks different for some reason:

    These get me the Report Server content:
    http://:/
    http://://ReportService2010.asmx

    And this gets me the "The path of the item 'wsdl' is not valid" error:
    http://:/ReportService2010.asmx?wsdl

    But in the same time I can get ReportService2006.asmx web service....

  4. Matt Says:

    Looks like you might be missing ReportServer in your url ... /ReportServer/ReportService2010.asmx

  5. Michael L Perry Says:

    If ReportService2010.asmx isn't working for you, try ReportService2005.asmx instead. You might not be on R2.

  6. RB Cohen Says:

    We're writing an SDK in PHP for the 2010 SOAP API. We are able to call ListChildren, etc. Does anybody know what method to call to get an actual report? In the 2005 version it was "Render", but this no longer exists in 2010?

Leave a Reply

You must be logged in to post a comment.