actual calc result not the same as Reporting Service calc result

I am using a table and at the grouping level, I am dividing the sum of one
field (oputut format 1 decimal position) by the sum of another field (output
format 1 decimal position). The output appears as follows .6 / 1.4 . If you
do the math on a calculator, the result wound be arounded .43. However
Visual Studio is calculating .45 (output format 2 decimals) . I suspect this
is because the detail (4 decimal positions) is summarized and then rounded.
My calculation is sum(a) / sum(b) with an output format mask of 2 decimal
places.
Is there any way to reference the actual data from the report summary line
(the .6 and the 1.4), or Can I construct my equation in such a way that when
the data on the report is calcualted manually it agrees with the results from
Visual Studio? The users will not trust the data if they manually try to
verify the report and it doesn't match.On Mar 23, 12:55 pm, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> I am using a table and at the grouping level, I am dividing the sum of one
> field (oputut format 1 decimal position) by the sum of another field (output
> format 1 decimal position). The output appears as follows .6 / 1.4 . If you
> do the math on a calculator, the result wound be arounded .43. However
> Visual Studio is calculating .45 (output format 2 decimals) . I suspect this
> is because the detail (4 decimal positions) is summarized and then rounded.
> My calculation is sum(a) / sum(b) with an output format mask of 2 decimal
> places.
> Is there any way to reference the actual data from the report summary line
> (the .6 and the 1.4), or Can I construct my equation in such a way that when
> the data on the report is calcualted manually it agrees with the results from
> Visual Studio? The users will not trust the data if they manually try to
> verify the report and it doesn't match.
If I'm understanding you correctly, you can either do the calculation
as part of the stored procedure/query that is sourcing the report
(i.e., CAST(SUM(A)/SUM(B) AS DECIMAL(10,2)) or similar) or call a
stored procedure that includes this type of calculation in the VS
application. Also, you could use something like this as part of a
stored procedure: CAST(SUM(CAST(A AS DECIMAL(10,2)))/SUM(CAST(B AS
DECIMAL(10,2))) AS DECIMAL(20,2)). Hope this is helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Enrique,
What does "CAST" mean, and is there somewhere that I can get a list of all
the function codes (ie. cast, sum, etc.) and their descriptions?
Thank you,
Patti
"EMartinez" wrote:
> On Mar 23, 12:55 pm, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > I am using a table and at the grouping level, I am dividing the sum of one
> > field (oputut format 1 decimal position) by the sum of another field (output
> > format 1 decimal position). The output appears as follows .6 / 1.4 . If you
> > do the math on a calculator, the result wound be arounded .43. However
> > Visual Studio is calculating .45 (output format 2 decimals) . I suspect this
> > is because the detail (4 decimal positions) is summarized and then rounded.
> > My calculation is sum(a) / sum(b) with an output format mask of 2 decimal
> > places.
> >
> > Is there any way to reference the actual data from the report summary line
> > (the .6 and the 1.4), or Can I construct my equation in such a way that when
> > the data on the report is calcualted manually it agrees with the results from
> > Visual Studio? The users will not trust the data if they manually try to
> > verify the report and it doesn't match.
> If I'm understanding you correctly, you can either do the calculation
> as part of the stored procedure/query that is sourcing the report
> (i.e., CAST(SUM(A)/SUM(B) AS DECIMAL(10,2)) or similar) or call a
> stored procedure that includes this type of calculation in the VS
> application. Also, you could use something like this as part of a
> stored procedure: CAST(SUM(CAST(A AS DECIMAL(10,2)))/SUM(CAST(B AS
> DECIMAL(10,2))) AS DECIMAL(20,2)). Hope this is helpful.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||Enrique,
Here is a simplified sample of my expression statement with the "CAST" added:
=CAST(Sum( Fields!Measures_M1Vol_N.Value) as decimal(13,1) ) /
CAST(Sum( Fields!Measures_M2BudVol_N.Value) as decimal(13,1) )
When I go to preview my report is says that "Name CAST is not declared".
I did the calculations manually and my probelm is defitely the fact that the
calcualations are performed on unrounded data, therefore giving me a larger
result. If possible I need to be able to round the numerator and the
denominator before the division actually takes place. Is there a different
way to format the expression so it will do this?
Thank you,
Patti
"ppbedz" wrote:
> I am using a table and at the grouping level, I am dividing the sum of one
> field (oputut format 1 decimal position) by the sum of another field (output
> format 1 decimal position). The output appears as follows .6 / 1.4 . If you
> do the math on a calculator, the result wound be arounded .43. However
> Visual Studio is calculating .45 (output format 2 decimals) . I suspect this
> is because the detail (4 decimal positions) is summarized and then rounded.
> My calculation is sum(a) / sum(b) with an output format mask of 2 decimal
> places.
> Is there any way to reference the actual data from the report summary line
> (the .6 and the 1.4), or Can I construct my equation in such a way that when
> the data on the report is calcualted manually it agrees with the results from
> Visual Studio? The users will not trust the data if they manually try to
> verify the report and it doesn't match.