Percentages of total

This probably is easy, but I haven't seen a good way to
do it yet.
I have a stored procedure that returns a numer or rows
that get grouped. Details are available if the person
expands the tree. I would also like to add the
percentage of total records as part of the display, but
can not figure out how to get a "total record count" to
work within the grouping to give me the appropraite
percentage.
What I am trying to do is this:
Custom Num Sales Percentage of Total Volume
+Cust A 25 25%
+Cust B 35 35%
+Cust C 40 40%
How can I keep and access a static count of the number of
records being returned by the dataset without having to
pass that as a calculated field in the stored procedure?
Thanks=Sum(Fields!NumSales.Value)/Sum(Fields!NumSales.Value,"Dataset1")
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:38c101c46a9d$60568870$3a01280a@.phx.gbl...
> This probably is easy, but I haven't seen a good way to
> do it yet.
> I have a stored procedure that returns a numer or rows
> that get grouped. Details are available if the person
> expands the tree. I would also like to add the
> percentage of total records as part of the display, but
> can not figure out how to get a "total record count" to
> work within the grouping to give me the appropraite
> percentage.
> What I am trying to do is this:
> Custom Num Sales Percentage of Total Volume
> +Cust A 25 25%
> +Cust B 35 35%
> +Cust C 40 40%
> How can I keep and access a static count of the number of
> records being returned by the dataset without having to
> pass that as a calculated field in the stored procedure?
> Thanks
>|||Hi Chris,
Thanks for the response, and I guess my example is not as
clear as it could be through over simplification.
I am trying to do percentage of groups of records based
on not the sales/or data values, but number of records.
So if you look at my example, consider it as number of
sales is the number of records or detail items, and I
want to do that as a percentage of total records
returned, so nothing that I have in the dataset as a
field value, but the overall record count.
A more specific example. I have a record set returning
634 records. One group that gets rolled up has 14
records, So I am trying to do at the group footer =RecordCount()/TotalRecordCount but I don't know where to
capture this.
A work around is just adding a column with 1 in it to the
record set and doing you method, but I am trying to avoid
that.
>--Original Message--
>=Sum(Fields!NumSales.Value)/Sum(Fields!
NumSales.Value,"Dataset1")
>--
>This post is provided 'AS IS' with no warranties, and
confers no rights. All
>rights reserved. Some assembly required. Batteries not
included. Your
>mileage may vary. Objects in mirror may be closer than
they appear. No user
>serviceable parts inside. Opening cover voids warranty.
Keep out of reach of
>children under 3.
>"David" <anonymous@.discussions.microsoft.com> wrote in
message
>news:38c101c46a9d$60568870$3a01280a@.phx.gbl...
>> This probably is easy, but I haven't seen a good way to
>> do it yet.
>> I have a stored procedure that returns a numer or rows
>> that get grouped. Details are available if the person
>> expands the tree. I would also like to add the
>> percentage of total records as part of the display, but
>> can not figure out how to get a "total record count" to
>> work within the grouping to give me the appropraite
>> percentage.
>> What I am trying to do is this:
>> Custom Num Sales Percentage of Total Volume
>> +Cust A 25 25%
>> +Cust B 35 35%
>> +Cust C 40 40%
>> How can I keep and access a static count of the number
of
>> records being returned by the dataset without having to
>> pass that as a calculated field in the stored
procedure?
>> Thanks
>>
>
>.
>|||If you're just after a count of rows, you could do this:
=CountRows()/CountRows("Dataset1")
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Dave" <anonymous@.discussions.microsoft.com> wrote in message
news:398701c46b2d$f5b4e570$3a01280a@.phx.gbl...
> Hi Chris,
> Thanks for the response, and I guess my example is not as
> clear as it could be through over simplification.
> I am trying to do percentage of groups of records based
> on not the sales/or data values, but number of records.
> So if you look at my example, consider it as number of
> sales is the number of records or detail items, and I
> want to do that as a percentage of total records
> returned, so nothing that I have in the dataset as a
> field value, but the overall record count.
> A more specific example. I have a record set returning
> 634 records. One group that gets rolled up has 14
> records, So I am trying to do at the group footer => RecordCount()/TotalRecordCount but I don't know where to
> capture this.
> A work around is just adding a column with 1 in it to the
> record set and doing you method, but I am trying to avoid
> that.
>
> >--Original Message--
> >=Sum(Fields!NumSales.Value)/Sum(Fields!
> NumSales.Value,"Dataset1")
> >
> >--
> >This post is provided 'AS IS' with no warranties, and
> confers no rights. All
> >rights reserved. Some assembly required. Batteries not
> included. Your
> >mileage may vary. Objects in mirror may be closer than
> they appear. No user
> >serviceable parts inside. Opening cover voids warranty.
> Keep out of reach of
> >children under 3.
> >"David" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:38c101c46a9d$60568870$3a01280a@.phx.gbl...
> >> This probably is easy, but I haven't seen a good way to
> >> do it yet.
> >>
> >> I have a stored procedure that returns a numer or rows
> >> that get grouped. Details are available if the person
> >> expands the tree. I would also like to add the
> >> percentage of total records as part of the display, but
> >> can not figure out how to get a "total record count" to
> >> work within the grouping to give me the appropraite
> >> percentage.
> >>
> >> What I am trying to do is this:
> >>
> >> Custom Num Sales Percentage of Total Volume
> >> +Cust A 25 25%
> >> +Cust B 35 35%
> >> +Cust C 40 40%
> >>
> >> How can I keep and access a static count of the number
> of
> >> records being returned by the dataset without having to
> >> pass that as a calculated field in the stored
> procedure?
> >>
> >> Thanks
> >>
> >>
> >
> >
> >.
> >

Percentages of subtotals

I have a matrix report with subtotals on the end of each row. I want to put
in brackets the percentage of the subtotal that each value is in the row. I
am not sure how to do this. Does anyone have any ideas?On Sep 27, 4:00 pm, "Fresno Bob" <nos...@.nospam.com> wrote:
> I have a matrix report with subtotals on the end of each row. I want to put
> in brackets the percentage of the subtotal that each value is in the row. I
> am not sure how to do this. Does anyone have any ideas?
You should be able to use an expression similar to the following:
=(Fields!SomeField.Value/Sum(Fields!SomeField.Value) ) * 100
-or- if this doesn't work due to automatic summing in the matrix
control, you might need to do something like this (I think):
=(Sum(Fields!SomeField.Value)/Total) * 100
For the brackets, you will need to select F4 (Properties Window) once
you have selected the field. In the Format section enter an expression
similar to the following:
="[#,.00%]"
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Percentages are rounding and I don't want them to

Using SQLRS 2005
No matter how I format the percentage (using FormatPercent or "P" format
option, my text box in a table control rounds the percentage to a whole
percentage with zeroes in the decimal columns.
I don't want it to round off at all.
Any pointers?
--
John ShahanOn Jan 31, 12:49 pm, jp <j...@.discussions.microsoft.com> wrote:
> Using SQLRS 2005
> No matter how I format the percentage (using FormatPercent or "P" format
> option, my text box in a table control rounds the percentage to a whole
> percentage with zeroes in the decimal columns.
> I don't want it to round off at all.
> Any pointers?
> --
> John Shahan
I am not sure this will help but...
Leave the formatting alone and just use the expression builder
In database column (lets call is percentField), number is like this...
123.456789 then in expression builder '=Fields!percentField.Value +
"%" ' - field should look like 123.456789% on the report.
.123456789 then in expression builder '=(Fields!percentField.Value
*100) + "%" ' - field should look like 123.456789% on the report.
I hope that is what you were talking about and I hope that helps you.
Thanks and have a great day,
Kerrie|||Thank you very much for taking the time to respond.
I tried eliminating the formating as you suggested. And I now have
=Fields!JulPercentage.Value * 100 & "%" as the expression but it is still
taking a value like .026436346 (confirmed by looking at the result set in the
Data tab) and making it 3.00% in the table control on the report.
--
John Shahan
"Kerrie" wrote:
> On Jan 31, 12:49 pm, jp <j...@.discussions.microsoft.com> wrote:
> > Using SQLRS 2005
> > No matter how I format the percentage (using FormatPercent or "P" format
> > option, my text box in a table control rounds the percentage to a whole
> > percentage with zeroes in the decimal columns.
> >
> > I don't want it to round off at all.
> >
> > Any pointers?
> > --
> > John Shahan
> I am not sure this will help but...
> Leave the formatting alone and just use the expression builder
> In database column (lets call is percentField), number is like this...
> 123.456789 then in expression builder '=Fields!percentField.Value +
> "%" ' - field should look like 123.456789% on the report.
> ..123456789 then in expression builder '=(Fields!percentField.Value
> *100) + "%" ' - field should look like 123.456789% on the report.
> I hope that is what you were talking about and I hope that helps you.
> Thanks and have a great day,
> Kerrie
>|||On Feb 1, 4:49 am, jp <j...@.discussions.microsoft.com> wrote:
> Using SQLRS 2005
> No matter how I format the percentage (using FormatPercent or "P" format
> option, my text box in a table control rounds the percentage to a whole
> percentage with zeroes in the decimal columns.
> I don't want it to round off at all.
> Any pointers?
> --
> John Shahan
You can specify the number of decimal places you would like to see in
the percentage, using the
FormatPercent(number, DecimalPlaces) function. However, this will show
zeros if the percent is a whole number. If you want sometimes to show
decimal places, but to show no trailing zeros, then you probably need
to construct the number manually (division, then multiply by 100) and
add the % sign on the end as a string.
Rowen|||On Jan 31, 6:33 pm, "Rowen" <rowen...@.gmail.com> wrote:
> On Feb 1, 4:49 am, jp <j...@.discussions.microsoft.com> wrote:
> > Using SQLRS 2005
> > No matter how I format the percentage (using FormatPercent or "P" format
> > option, my text box in a table control rounds the percentage to a whole
> > percentage with zeroes in the decimal columns.
> > I don't want it to round off at all.
> > Any pointers?
> > --
> > John Shahan
> You can specify the number of decimal places you would like to see in
> the percentage, using the
> FormatPercent(number, DecimalPlaces) function. However, this will show
> zeros if the percent is a whole number. If you want sometimes to show
> decimal places, but to show no trailing zeros, then you probably need
> to construct the number manually (division, then multiply by 100) and
> add the % sign on the end as a string.
> Rowen
I would definitly try Rowan's suggestions, but what do you have the
format as on the table for that field? Something is set and it needs
to be turned off. I would leave it on 'General' or 'g' on the format
property.
Kerrie

percentage values storage and retrieval

I am told that percentages should be stored in a raw format like 0.12 and
displayed as 12.00% on the front end. That means, I should multiply by 100
when I am pulling the data from the table and divide by 100 when I am storin
g
the data in the table.
This does not make sense to me. How about I store the data as 12.00 to begin
with in the table? And just pull it as it is in the table and do proper
processing when the values gets stored in the table right in the beginning.
Does that make sense?
Since this is a very simple issue, I thought I did not need to post sample
table and data.
Please let me know.
TIA...To me, it makes better sense to store the percentage value as a number less
than one (e.g., 0.12). In addition to just representing a percentage (12%),
you could use the percentage to calculate a discount for a price or other
operation. For that use,
it's better to keep the percentage like 0.12 :
--pecentage like 0.12
discount = price x percentage
--percentage like 12
dicount = price x percentage / 100 --harder to keep track of, IMHO
"sqlster" wrote:

> I am told that percentages should be stored in a raw format like 0.12 and
> displayed as 12.00% on the front end. That means, I should multiply by 100
> when I am pulling the data from the table and divide by 100 when I am stor
ing
> the data in the table.
> This does not make sense to me. How about I store the data as 12.00 to beg
in
> with in the table? And just pull it as it is in the table and do proper
> processing when the values gets stored in the table right in the beginning
.
> Does that make sense?
> Since this is a very simple issue, I thought I did not need to post sample
> table and data.
> Please let me know.
> TIA...
>|||Percentages are decimals, and are used as decimals. The data should be
stored in a format according to what it means and how it is used, not how it
is displayed. As a rule, databases are built around the data you work with,
applications are built around how you display that data. These rules should
only be broken as a last resort, and wanting an easier way to display a
value does not qualify.
Yes, storing it as an integer rather than the actual decimal value may save
you some keystrokes now, but it will result in calculation errors later on,
and will be a nightmare for the next person who has to manage the code.
"sqlster" <nospam@.nospam.com> wrote in message
news:B40FDAC5-D090-4F0D-ACE7-FCA12CB50570@.microsoft.com...
> I am told that percentages should be stored in a raw format like 0.12 and
> displayed as 12.00% on the front end. That means, I should multiply by 100
> when I am pulling the data from the table and divide by 100 when I am
storing
> the data in the table.
> This does not make sense to me. How about I store the data as 12.00 to
begin
> with in the table? And just pull it as it is in the table and do proper
> processing when the values gets stored in the table right in the
beginning.
> Does that make sense?
> Since this is a very simple issue, I thought I did not need to post sample
> table and data.
> Please let me know.
> TIA...
>|||A percentage is a value derived from a numerator and denominator, and if the
x and y are contained in the table, then there is no reason to add the
additional percentage column. If you have only the percentage, then you are
potentially losing information, becuase the question may be asked later how
the value was derived. Therefore, the best method of storing a percentage
may be perhaps:
x smallint,
y smallint
"sqlster" <nospam@.nospam.com> wrote in message
news:B40FDAC5-D090-4F0D-ACE7-FCA12CB50570@.microsoft.com...
>I am told that percentages should be stored in a raw format like 0.12 and
> displayed as 12.00% on the front end. That means, I should multiply by 100
> when I am pulling the data from the table and divide by 100 when I am
> storing
> the data in the table.
> This does not make sense to me. How about I store the data as 12.00 to
> begin
> with in the table? And just pull it as it is in the table and do proper
> processing when the values gets stored in the table right in the
> beginning.
> Does that make sense?
> Since this is a very simple issue, I thought I did not need to post sample
> table and data.
> Please let me know.
> TIA...
>