Custom Drillthrough action and resulting decimal places

Topics: Developer Forum, User Forum
Nov 1, 2010 at 2:47 PM

We have developed a number of custom drillthroughs action using ASSP. Typically these drillthrough actions either execute a stored procedure or direct sql against the underlying SQL Server datawarehouse. Despite the fact that the underlying tables may contain columns defined as decimal(24,5), the rowsets returned are always formatted with 4 decimal places in Excel 2007. For example, we have a price column in a datawarehouse table with a content 5.03658, Excel will display this as 5.0366 i.e. it will round to 4 decimal places. Now if we use the default drillthrough action using 'Show Details', it returns 5 decimal places. If we simply execute the stored procedure as a query in SQL Server and copy and paste the resulting rows into Excel, it will display 5 decimal places. So the problem is not in Excel, or in the default drillthrough but only manifests itself using the custom drillthrough built using ASSP.

I would be grateful if anyone could shed light on this problem.

Our action content type is always defined as rowset in these custom drillthroughs.

In a previous post, I supplied an example using Adventure Works, but I am hoping someone may have some insight on this before I need to do that. 

Thanks in advance.

Nov 1, 2010 at 3:24 PM

I should have added that we are typically using the following to return the resulting datatable for the custom drillthrough action. Consider the following VB code fragment:

        Dim dt As New DataTable("Results")

        Dim sqlCommand As New SqlCommand

        sqlCommand.CommandType = "exec [usp_BuyerAccountsFullTradingHistory]"      'stored procedure

        Dim resultAdapter As New SqlClient.SqlDataAdapter
        resultAdapter.SelectCommand = sqlCommand
        resultAdapter.Fill(dt)
        Return dt

So I should first rule out that this code is not responsible for truncating my decimal places.

 

 

Nov 1, 2010 at 4:03 PM

Apologies for the multiple posts.

I can confirm that the above resulting datatable contains the correct number of decimal places for my decimal columns.

So it looks like the decimal rounding truncation is caused by the custom drillthrough action rowset populating Excel.

This is certainly  a very undesirable 'feature' for our business users where decimal precision is crucial.

 

Dec 1, 2010 at 11:04 AM

A final word on this. I reported this issue to MS. They admitted that it is a problem. It looks like AS maps decimal data types to currency data type for custom drillthroughs resulting in rounding to 4 decimal places of precision. MS have no plans to fix this. I would have thought that changing user data would be serious enough to warrant  a fix. This bug does not happen when using the default drillthrough.