Handling large numbers in reports with nHibernate queries

[AbleMods]

I recently ran into a situation with a client where the report query simply wasn't designed to handle larger installs. This is something to consider when you specifically total the Quantity field. So I thought I would share the issue and the solution with the community.

In a larger store, it's quite easy for a nHibernate SUM command to exceed the max value for Int16 values. Since most report queries don't specifically cast the quantity total, nHibernate is defaulting to Int16 which limits the value to 32,767. Any total that results in a larger number throws an exception.

For example, this is what happens in ReportDataSource.GetProductBreakdownSummary():

Server Error in '/' Application.

Value was either too large or too small for an Int16.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.OverflowException: Value was either too large or too small for an Int16.

Source Error:


Line 1419: .SetResultTransformer(Transformers.AliasToBean(typeof(ProductBreakdownSummary)));
Line 1420:
Line 1421: IList<ProductBreakdownSummary> summaryList = criteria.List<ProductBreakdownSummary>();
Line 1422: IProductRepository productRepository = AbleContext.Resolve<IProductRepository>();
Line 1423:

The solution is to cast the total column into something larger. So this:

Code: Select all

.Add(Projections.Sum("OI.Quantity"), "Quantity")

becomes this:

Code: Select all

.Add(Projections.Sum(Projections.Cast(NHibernateUtil.Int32, Projections.Property("OI.Quantity"))), "Quantity")

It's a pretty easy fix but you have to remember it when you're first designing your reports. So far I only ever have to do this when I'm doing a SUM on the Quantity column.

[Katie]

Thanks for providing this information Joe. We'll have this fixed in our next release.

[ForumsAdmin]

Joe I guess you fixed this in R5 ... Because in R6 we already fixed most of these issues using the same technique described above.