Archive

Posts Tagged ‘Reporting’

Using WCF RIA Services to allow projections that support Lookups – Part II

August 30, 2011 Leave a comment

Now that we are fairly comfortable with the idea of using a generic class to act as a property bag, we can now take this implementation a little further and provide some cool advancements.

One area where I have found this to be a useful solution is dealing with my reporting architecture. Here is a screen in my application that uses this advancement. I basically have a dynamic report driver screen. It allows me to evaluate any uploaded report and provide parameters if necessary.

Running the report gives us the following screen:

We use ComponentOne for our reporting because they have a very nice and mature independent report designer that allows our clients to define their reports without the need for Visual Studio. Typically our user base is not IT or technically advanced and trying to teach them to use Visual Studio for just doing reports doesn’t make sense. The nice thing about the reports is that they are just like Microsoft Reporting Services, in that the definition of the report is just XML. The end-user creates the report and then uses my application to upload the report definition to the database where it is stored. This makes for a very beefy record and can really bog down your application if you want to test your reports. The one catch with reporting is that they can also have parameters. This calls for a data structure that supports one-to-many.

Let’s look quickly at the two tables involved in handling reporting.

AS you can see I have a reporting table and a report parameter table. If I wanted to use the approach that I used from my last post, I wouldn’t be able to accomplish this. We will need to modify the data object to support this type of hierarchy.

public class ResultDTO
{
    [Key]
    public int Value { get; set; }
    public string Name { get; set; }
    public string FriendlyName { get; set; }
    public int ParentValue { get; set; }
};

public class ResultWithCollectionDTO
{
    [Key]
    public int Value { get; set; }
    public string Name { get; set; }
    public string FriendlyName { get; set; }
    public int ScreenId { get; set; }
    [Include]
    [Association("ResultWithCollectionDTO_ResultDTO", "Value", "ParentValue")]
    [Composition]
    public IEnumerable<ResultDTO> Children { get; set; }
};

If we look at the ResultDTO object, only one change has been made. It now has a int property labeled, “ParentValue”. Although not required from an ObjectOriented perspective, this is required from a WCF RIA Services navigation perspective.

Let’s look at the newly introduced table. We see that it has the same properties as the ResultDTO as well as an int property labeled, “ScreenId”. This is necessary because the underlying architecture has an identifier for each screen and depending on what screen you are on you could have one or more reports associated with it. By having this metadata I can then automatically show a default report for a screen or group reports together with the same ScreenId and use that grouping for hydrating a ComboBox for selecting which report to render.

The last property is what gives us the hierarchy we need to support parent child relationships. Using WCF RIA Services and eager loading the whole object is what I am trying to avoid by providing this solution. Because the report table has the report XML definition, it is extremely large and I don’t want to download this information when all I am trying to do is bind to a report generically. This property is just an IEnumerable collection of ResultDTO objects. I have to have the following attributes in order for WCF RIA Services to honor it correctly. The “Include” attribute basically tells WCF RIA Services to eagerly load this collection of child objects. The “Association” attribute requires a name for the relationship, the parent value and the associated child value. Finally, we have the “Composition” attribute which indicates that this member represents an association taht is part of a compositional hierarchy.

Now let’s turn our attention to how we would write a query to take advantage of this new generic model.

[Query()]
public IQueryable<ResultWithCollectionDTO> ReturnRSReportDTOIncludingParameters()
{
    var resultSet = this.ObjectContext.rs_r_Report
        .Include("rs_rp_ReportParameter")
        .OrderBy(x => x.rs_r_FriendlyName);
    var result = from c in resultSet
                 select new ResultWithCollectionDTO()
                 {
                     Value = c.rs_r_ReportIdent,
                     Name = c.rs_r_ReportName,
                     FriendlyName = c.rs_r_FriendlyName,
                     ScreenId = c.rs_r_ScreenId ?? 0,
                     Children = (from d in c.rs_rp_ReportParameter
                                 select new ResultDTO()
                                 {
                                     ParentValue = c.rs_r_ReportIdent,
                                     Value = d.rs_rp_ReportParameterIdent,
                                     Name = d.rs_rp_ParameterName,
                                     FriendlyName = d.rs_rp_DefaultValue
                                 })
                 };
    return result;
}

As you can see, this query very similar to the one we already used in the previous post but we need to do some projection shaping so that the data fits into our newly defined models. We first need to ensure that the data from our ObjectContext eagerly loads our report parameters with our report object. Next, we just go through the steps of shaping the ResultWithCollectionDTO object and then perform a sub select statement for the report parameters. We make sure that we also bring in the report identity as the “ParentValue” property.

You would think that we are done but I need to do one more thing if my screen that I show your earlier is to work. When we create our models, WCF RIA Services will treat them as read-only unless we provide a little more implementation. This means that if I try to “TwoWay” databind to my parameters, an exception will be thrown. I want to be able to set values for the parameters but I don’t care about saving this since I am really treating these objects as non-persisted. It is only on the client-side that I care about binding and sending these values to my report engine.

In order to get this accomplished, you provide the following in your DomainService:

[Delete]
public void DeleteResultDTO(ResultDTO dto)
{
    // Do nothing....
}
[Insert]
public void InsertResultDTO(ResultDTO dto)
{
    // Do nothing....
}
[Update]
public void UpdateResultDTO(ResultDTO dto)
{
    // Do nothing....
}

[Delete]
public void DeleteResultWithCollectionDTO(ResultWithCollectionDTO dto)
{
    // Do nothing....
}
[Insert]
public void InsertResultWithCollectionDTO(ResultWithCollectionDTO dto)
{
    // Do nothing....
}
[Update]
public void UpdateResultWithCollectionDTO(ResultWithCollectionDTO dto)
{
    // Do nothing....
}

I typically put this code in a partial class so as I won’t lose it when I need to update my DomainService. Now that I have the Delete, Insert, and Update methods in place, my databinding on the client-side will work just fine.

Again, remember that you will only need to do this once or twice depending on how you want to implement your generic objects. The nice thing about this is you have full access to your complete object but you also have access to a “lite” version that you can use for your lookups. Couple this with the built-in data paging and filtering that you get for free from WCF RIA Services and you have a pretty flexible architecture.

Hope this helps…

Simple Tabular Report Generation in WPF

May 11, 2011 4 comments

This post is on using the power of the ReportViewer in WPF to build simple tabular reports on the fly. I did a presentation on this a while back and I noticed that there is some interest in building reports and having the ability to query your database on the fly without the need for SQL Server Management Studio or MS Access.

The sample included in this post is a simple mock up that can grow with time. It gives you the ability to connect to any SQL Server backend. You can then write your SQL in the editor. Finally, you can preview your data, make any changes you want to ensure that it is ready for a generating a report. You then click on the Generate Report button and the Preview Report tab becomes selected rendering a dynamic report based on your query. With very little code, you get a simple query editor, code generator, and local report viewer.

In this first post we will just take a look at the user interface but in a following post, we will cover the code necessary to get this working.

My example is using the AdventureWorks database but you can use whatever database you like. When the application first starts, are presented with the following window:

Click on the button as indicated in the following screen shot:

Setting up the Connection

Follow the dialog and enter in your information for creating a connection.

Dialog for creating a database connection.

You can test your connection and verify that it is correct.

Dialog for testing the connection.

Click the Connect button to create the connection. Type in the query you wish to perform:

Write in your SQL query.

You can now either execute the query or parse it. The following is a screen shot of the query executed:

Query and metadata results displayed.

As you can see, we get our results but we also get a grid in the middle that provides us with a bit of metadata for formatting our report. You can also provide a title for the report.

Metadata set for report generation.

Then by clicking the Generate Report button, we see the following:

Report rendered in the ReportViewer control.

By now you can see that it is very easy to create some dynamic reports in very little time. In the next post we will go over the code and architecture required to get this to work. I have included the slides, source code, and a walk-through from my presentation.

Hope this helps….