I am looking some query stats and have a query.
Query 1 Plan Cost: 5.312 -- Execution 20.497 seconds -- total time 23.848
seconds --Physical Reads 1,404, Logical Reads, 927,701,Scans 621, Read Ahead
Reads - 3,976
Query 2 Plan Cost 9.469 -- Exection 00.143 seconds -- total time 02.016
seconds -- physical reads 0, logical reads 7146, sans 622 , read ahead reads
100
Query 2 obviously runs around 95% faster and it would seem difference
between plan cost is very little.
What is the downside of having the plan cost go up? Under heavier load
would this start to perform poorly because of that? At what point does the
plan cost become more important than the other items? Or are the logical
reads a better indiciator of which way to go.
Sorry for so many questions -- really starting to use the tools to tweak
queries and want to make sure I am going down right paths.
Thanks!!!Execution plan costs are worth looking at, but must be taken with a
bit of skepticism. The cost in the execution plan is just an
estimate. The actual results can be quite different. You can even
get completely different execution plans sometimes just by updating
statistics.
Roy Harvey
Beacon Falls, CT
On Tue, 28 Feb 2006 10:46:07 -0500, "Brian" <brian@.nospam.com> wrote:
>I am looking some query stats and have a query.
>Query 1 Plan Cost: 5.312 -- Execution 20.497 seconds -- total time 23.848
>seconds --Physical Reads 1,404, Logical Reads, 927,701,Scans 621, Read Ahea
d
>Reads - 3,976
>Query 2 Plan Cost 9.469 -- Exection 00.143 seconds -- total time 02.016
>seconds -- physical reads 0, logical reads 7146, sans 622 , read ahead read
s
>100
>
>Query 2 obviously runs around 95% faster and it would seem difference
>between plan cost is very little.
>What is the downside of having the plan cost go up? Under heavier load
>would this start to perform poorly because of that? At what point does the
>plan cost become more important than the other items? Or are the logical
>reads a better indiciator of which way to go.
>Sorry for so many questions -- really starting to use the tools to tweak
>queries and want to make sure I am going down right paths.
>Thanks!!!
>
Showing posts with label total. Show all posts
Showing posts with label total. Show all posts
Friday, March 9, 2012
Monday, February 20, 2012
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
> >>
> >>
> >
> >
> >.
> >
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
> >>
> >>
> >
> >
> >.
> >
Percentage of total column in Report Builder
Hello,
We are the using the end-user tool Report Builder. I want to create a
'Percentage of total' column to a certain table. For example:
Value Percentage of total
Region A 200 20%
Region B 500 50%
Region C 300 30%
--
Total 1000 100%
How can I achieve that?
Regards,
Jaap MosselmanHello Jaap,
I sugget you add a new field in the Report Build which calculate the
Percentage of the total.
And you could format the column to show as the percentage.
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Ok, new field is not problem, but how can I retrieve the actual (sub)total
value (in this example 1000) to use as denominator?
Value Percentage of total
Region A 200 20%
Region B 500 50%
Region C 300 30%
--
Total 1000 100%
Regards, Jaap
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> schreef in bericht
news:J3V2QNplHHA.1140@.TK2MSFTNGHUB02.phx.gbl...
> Hello Jaap,
> I sugget you add a new field in the Report Build which calculate the
> Percentage of the total.
> And you could format the column to show as the percentage.
> Hope this helps.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||You should be able to get the sum by doing something like this:
=IIF(sum(Fields!Yourvalue.value), "yourdataset")>0, fields!YourValue.Value /
sum(Fields!YourValue.Value, "yourdataset"))
Or you could do the summing in your dataset, and just use it as a normal
field.
Kaisa M. Lindahl Lervik
"Jaap" <Jaap@.newsgroups.nospam> wrote in message
news:u3lwBX6lHHA.4960@.TK2MSFTNGP02.phx.gbl...
> Ok, new field is not problem, but how can I retrieve the actual (sub)total
> value (in this example 1000) to use as denominator?
> Value Percentage of total
> Region A 200 20%
> Region B 500 50%
> Region C 300 30%
> --
> Total 1000 100%
> Regards, Jaap
> "Wei Lu [MSFT]" <weilu@.online.microsoft.com> schreef in bericht
> news:J3V2QNplHHA.1140@.TK2MSFTNGHUB02.phx.gbl...
>> Hello Jaap,
>> I sugget you add a new field in the Report Build which calculate the
>> Percentage of the total.
>> And you could format the column to show as the percentage.
>> Hope this helps.
>> Sincerely,
>> Wei Lu
>> Microsoft Online Community Support
>> ==================================================>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> ==================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Is that possible in de Report Builder end user tool?
Or only in the VS Report Editor?
Jaap
"Kaisa M. Lindahl Lervik" <kaisaml@.hotmail.com> schreef in bericht
news:%23I4ML46lHHA.3484@.TK2MSFTNGP02.phx.gbl...
> You should be able to get the sum by doing something like this:
> =IIF(sum(Fields!Yourvalue.value), "yourdataset")>0, fields!YourValue.Value
> / sum(Fields!YourValue.Value, "yourdataset"))
> Or you could do the summing in your dataset, and just use it as a normal
> field.
> Kaisa M. Lindahl Lervik
> "Jaap" <Jaap@.newsgroups.nospam> wrote in message
> news:u3lwBX6lHHA.4960@.TK2MSFTNGP02.phx.gbl...
>> Ok, new field is not problem, but how can I retrieve the actual
>> (sub)total value (in this example 1000) to use as denominator?
>> Value Percentage of total
>> Region A 200 20%
>> Region B 500 50%
>> Region C 300 30%
>> --
>> Total 1000 100%
>> Regards, Jaap
>> "Wei Lu [MSFT]" <weilu@.online.microsoft.com> schreef in bericht
>> news:J3V2QNplHHA.1140@.TK2MSFTNGHUB02.phx.gbl...
>> Hello Jaap,
>> I sugget you add a new field in the Report Build which calculate the
>> Percentage of the total.
>> And you could format the column to show as the percentage.
>> Hope this helps.
>> Sincerely,
>> Wei Lu
>> Microsoft Online Community Support
>> ==================================================>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> ==================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>|||Hello Jaap,
Yes, you could use, but with some modification.
In the Report Builder, you may Edit the Formula of a cell. And you could
use the IF function and SUM function.
Hope it helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Jaap,
I reproduce this issue.
The SUM(Total Area) only sum the field in the record.
You may need to aggregate the filed in the raw data and regenerate the
report model.
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||But in general, you don't know what the 100% value is, the denominator,
because you don't know which selections and filters the end user is
creating.
Please, place it on the wishlist to have an option to do a 'percent of
total' formatting.
OK, for some specific situations, you can do it the hardcodes way you
suggested.
Regards,
Jaap
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> schreef in bericht
news:qyvcwjqnHHA.1144@.TK2MSFTNGHUB02.phx.gbl...
> Hello Jaap,
> I reproduce this issue.
> The SUM(Total Area) only sum the field in the record.
> You may need to aggregate the filed in the raw data and regenerate the
> report model.
> Hope this helps.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Jaap,
The Report Builder is a User end report generate tool. It did not include
all the function which Report Designer has.
If you have any funcional request, please send your feedback to the
following url:
http://connect.microsoft.com/sqlserver.
Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
We are the using the end-user tool Report Builder. I want to create a
'Percentage of total' column to a certain table. For example:
Value Percentage of total
Region A 200 20%
Region B 500 50%
Region C 300 30%
--
Total 1000 100%
How can I achieve that?
Regards,
Jaap MosselmanHello Jaap,
I sugget you add a new field in the Report Build which calculate the
Percentage of the total.
And you could format the column to show as the percentage.
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Ok, new field is not problem, but how can I retrieve the actual (sub)total
value (in this example 1000) to use as denominator?
Value Percentage of total
Region A 200 20%
Region B 500 50%
Region C 300 30%
--
Total 1000 100%
Regards, Jaap
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> schreef in bericht
news:J3V2QNplHHA.1140@.TK2MSFTNGHUB02.phx.gbl...
> Hello Jaap,
> I sugget you add a new field in the Report Build which calculate the
> Percentage of the total.
> And you could format the column to show as the percentage.
> Hope this helps.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||You should be able to get the sum by doing something like this:
=IIF(sum(Fields!Yourvalue.value), "yourdataset")>0, fields!YourValue.Value /
sum(Fields!YourValue.Value, "yourdataset"))
Or you could do the summing in your dataset, and just use it as a normal
field.
Kaisa M. Lindahl Lervik
"Jaap" <Jaap@.newsgroups.nospam> wrote in message
news:u3lwBX6lHHA.4960@.TK2MSFTNGP02.phx.gbl...
> Ok, new field is not problem, but how can I retrieve the actual (sub)total
> value (in this example 1000) to use as denominator?
> Value Percentage of total
> Region A 200 20%
> Region B 500 50%
> Region C 300 30%
> --
> Total 1000 100%
> Regards, Jaap
> "Wei Lu [MSFT]" <weilu@.online.microsoft.com> schreef in bericht
> news:J3V2QNplHHA.1140@.TK2MSFTNGHUB02.phx.gbl...
>> Hello Jaap,
>> I sugget you add a new field in the Report Build which calculate the
>> Percentage of the total.
>> And you could format the column to show as the percentage.
>> Hope this helps.
>> Sincerely,
>> Wei Lu
>> Microsoft Online Community Support
>> ==================================================>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> ==================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Is that possible in de Report Builder end user tool?
Or only in the VS Report Editor?
Jaap
"Kaisa M. Lindahl Lervik" <kaisaml@.hotmail.com> schreef in bericht
news:%23I4ML46lHHA.3484@.TK2MSFTNGP02.phx.gbl...
> You should be able to get the sum by doing something like this:
> =IIF(sum(Fields!Yourvalue.value), "yourdataset")>0, fields!YourValue.Value
> / sum(Fields!YourValue.Value, "yourdataset"))
> Or you could do the summing in your dataset, and just use it as a normal
> field.
> Kaisa M. Lindahl Lervik
> "Jaap" <Jaap@.newsgroups.nospam> wrote in message
> news:u3lwBX6lHHA.4960@.TK2MSFTNGP02.phx.gbl...
>> Ok, new field is not problem, but how can I retrieve the actual
>> (sub)total value (in this example 1000) to use as denominator?
>> Value Percentage of total
>> Region A 200 20%
>> Region B 500 50%
>> Region C 300 30%
>> --
>> Total 1000 100%
>> Regards, Jaap
>> "Wei Lu [MSFT]" <weilu@.online.microsoft.com> schreef in bericht
>> news:J3V2QNplHHA.1140@.TK2MSFTNGHUB02.phx.gbl...
>> Hello Jaap,
>> I sugget you add a new field in the Report Build which calculate the
>> Percentage of the total.
>> And you could format the column to show as the percentage.
>> Hope this helps.
>> Sincerely,
>> Wei Lu
>> Microsoft Online Community Support
>> ==================================================>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> ==================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>|||Hello Jaap,
Yes, you could use, but with some modification.
In the Report Builder, you may Edit the Formula of a cell. And you could
use the IF function and SUM function.
Hope it helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Jaap,
I reproduce this issue.
The SUM(Total Area) only sum the field in the record.
You may need to aggregate the filed in the raw data and regenerate the
report model.
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||But in general, you don't know what the 100% value is, the denominator,
because you don't know which selections and filters the end user is
creating.
Please, place it on the wishlist to have an option to do a 'percent of
total' formatting.
OK, for some specific situations, you can do it the hardcodes way you
suggested.
Regards,
Jaap
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> schreef in bericht
news:qyvcwjqnHHA.1144@.TK2MSFTNGHUB02.phx.gbl...
> Hello Jaap,
> I reproduce this issue.
> The SUM(Total Area) only sum the field in the record.
> You may need to aggregate the filed in the raw data and regenerate the
> report model.
> Hope this helps.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Jaap,
The Report Builder is a User end report generate tool. It did not include
all the function which Report Designer has.
If you have any funcional request, please send your feedback to the
following url:
http://connect.microsoft.com/sqlserver.
Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
Percentage of Total
Hi Friends,
I have a Cube wherein i have dimensions with following Heirachy:-
Activity
PartNumber
Now if the person selects Activity and Quantity, like as below i want output as
Activity Part Quantity %of Quantity
Activity1 Part1 75 37.5%
Part2 125 62.5%
Activity1 Total 200 40%
Activity2 Part3 90 90%
Part4 10 10%
Activity2 Total 100 20%
Activity3 Part5 100 50%
Part6 100 50%
Activity3 Total 200 40%
This is really urgent...
Regards,
Kaushal
Here's a sample Adventure Works query which may help:
With
Member [Measures].[OrderPercentOfParent] as
[Measures].[Order Quantity] /
([Measures].[Order Quantity],
[Promotion].[Promotions].Parent),
FORMAT_STRING = 'Percent'
select
{[Measures].[Order Quantity],
[Measures].[OrderPercentOfParent]} on 0,
NON EMPTY DrillDownLevel([Promotion].[Promotions].[All Promotions].Children) on 1
from [Adventure Works]
Order Quantity OrderPercentOfParent
No Discount 238,806 86.91%
Reseller 35,970 13.09%
Discontinued Product 838 2.33%
Excess Inventory 304 0.85%
New Product 2,356 6.55%
Seasonal Discount 1,172 3.26%
Volume Discount 31,300 87.02%
Deepak thanks for the help, but i have used following formula...
IIF([Activity].CurrentMember.Parent is null, 100,(Measures.[Net OI EUR - 2006] / (Measures.[Net OI EUR - 2006],[Activity].CurrentMember.Parent))*100)
Activity Value - 2006 Value - 2007 Var% Delta Mix(Value2007 - Value2006) * Var%
Act1 100 75 5% -0.125
Act2 50 80 10% 3
Act3 50 80 3% 0.9
Grand Total 200 235 4% 1.4(But i want 3.775 Sum of Act1+Act2+Act3)
How can i achieve this, really urgent...
Regards,
Kaushal
Percentage of Total
Hi Friends,
I have a Cube wherein i have dimensions with following Heirachy:-
Activity
PartNumber
Now if the person selects Activity and Quantity, like as below i want output as
Activity Part Quantity %of Quantity
Activity1 Part1 75 37.5%
Part2 125 62.5%
Activity1 Total 200 40%
Activity2 Part3 90 90%
Part4 10 10%
Activity2 Total 100 20%
Activity3 Part5 100 50%
Part6 100 50%
Activity3 Total 200 40%
This is really urgent...
Regards,
Kaushal
Here's a sample Adventure Works query which may help:
With
Member [Measures].[OrderPercentOfParent] as
[Measures].[Order Quantity] /
([Measures].[Order Quantity],
[Promotion].[Promotions].Parent),
FORMAT_STRING = 'Percent'
select
{[Measures].[Order Quantity],
[Measures].[OrderPercentOfParent]} on 0,
NONEMPTYDrillDownLevel([Promotion].[Promotions].[All Promotions].Children) on 1
from [Adventure Works]
Order Quantity OrderPercentOfParent
No Discount 238,806 86.91%
Reseller 35,970 13.09%
Discontinued Product 838 2.33%
Excess Inventory 304 0.85%
New Product 2,356 6.55%
Seasonal Discount 1,172 3.26%
Volume Discount 31,300 87.02%
Deepak thanks for the help, but i have used following formula...
IIF([Activity].CurrentMember.Parentisnull, 100,(Measures.[Net OI EUR - 2006] / (Measures.[Net OI EUR - 2006],[Activity].CurrentMember.Parent))*100)
Activity Value - 2006 Value - 2007 Var% Delta Mix(Value2007 - Value2006) * Var%
Act1 100 75 5% -0.125
Act2 50 80 10% 3
Act3 50 80 3% 0.9
Grand Total 200 235 4% 1.4(But i want 3.775 Sum of Act1+Act2+Act3)
How can i achieve this, really urgent...
Regards,
Kaushal
Percentage of total
This is a multi-part message in MIME format.
--=_NextPart_000_0181_01C87662.85ABCAC0
Content-Type: text/plain;
charset="windows-1255"
Content-Transfer-Encoding: quoted-printable
I want to create such a report:
Dep. Sales %T1 %T2
Division A
=3D=3D=3D=3D=3D=3D=3D
Department1 10 10% 2%
Department2 40 40% 8%
Department3 50 50% 10%
---
Total A 100 100% 20%
Division B
=3D=3D=3D=3D=3D=3D=3D
Department4 100 25% 20% Department5 300 75% 60%
---
Total A 400 100% 80%
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Total 500 100%
What is the right way to calculate the Percentage for each Department?
Should I calculate it in the SQL or could I use the abilities of SSRS?
--=_NextPart_000_0181_01C87662.85ABCAC0
Content-Type: text/html;
charset="windows-1255"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I want to create such a report:
Dep. = Sales %T1 %T2
Division A
=3D=3D=3D=3D=3D=3D=3D
Department1 = 10 10% 2%
Department2 = 40 40% 8%
Department3 = 50 50% 10%
---
Total A 100 &nb= sp; 100% 20%
Division B
=3D=3D=3D=3D=3D=3D=3D
Department4 100 25% 20%
Department5 300 75% 60%
---
Total A 400 &nb= sp; 100% 80%
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D
Total = 500 100%
What is the right way to calculate the = Percentage for each Department?
Should I calculate it in the SQL or = could I use the abilities of SSRS?
--=_NextPart_000_0181_01C87662.85ABCAC0--On Feb 23, 1:24 pm, "Geri Reshef" <gershon_res...@.recanati-
alum.tau.ac.il> wrote:
> I want to create such a report:
> Dep. Sales %T1 %T2
> Division A
> =======> Department1 10 10% 2%
> Department2 40 40% 8%
> Department3 50 50% 10%
> ---
> Total A 100 100% 20%
> Division B
> =======> Department4 100 25% 20%
> Department5 300 75% 60%
> ---
> Total A 400 100% 80%
> ========================> Total 500 100%
> What is the right way to calculate the Percentage for each Department?
> Should I calculate it in the SQL or could I use the abilities of SSRS?
You can do it either way. In SSRS, you could use an expression similar
to this to get the percentages per department.
=(Fields!Sales.Value/Sum(Fields!Sales.Value)) * 100
And then in the format section of the table column properties for the
T1 column, set the format to: #.00%
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant'
--=_NextPart_000_0181_01C87662.85ABCAC0
Content-Type: text/plain;
charset="windows-1255"
Content-Transfer-Encoding: quoted-printable
I want to create such a report:
Dep. Sales %T1 %T2
Division A
=3D=3D=3D=3D=3D=3D=3D
Department1 10 10% 2%
Department2 40 40% 8%
Department3 50 50% 10%
---
Total A 100 100% 20%
Division B
=3D=3D=3D=3D=3D=3D=3D
Department4 100 25% 20% Department5 300 75% 60%
---
Total A 400 100% 80%
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Total 500 100%
What is the right way to calculate the Percentage for each Department?
Should I calculate it in the SQL or could I use the abilities of SSRS?
--=_NextPart_000_0181_01C87662.85ABCAC0
Content-Type: text/html;
charset="windows-1255"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I want to create such a report:
Dep. = Sales %T1 %T2
Division A
=3D=3D=3D=3D=3D=3D=3D
Department1 = 10 10% 2%
Department2 = 40 40% 8%
Department3 = 50 50% 10%
---
Total A 100 &nb= sp; 100% 20%
Division B
=3D=3D=3D=3D=3D=3D=3D
Department4 100 25% 20%
Department5 300 75% 60%
---
Total A 400 &nb= sp; 100% 80%
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D
Total = 500 100%
What is the right way to calculate the = Percentage for each Department?
Should I calculate it in the SQL or = could I use the abilities of SSRS?
--=_NextPart_000_0181_01C87662.85ABCAC0--On Feb 23, 1:24 pm, "Geri Reshef" <gershon_res...@.recanati-
alum.tau.ac.il> wrote:
> I want to create such a report:
> Dep. Sales %T1 %T2
> Division A
> =======> Department1 10 10% 2%
> Department2 40 40% 8%
> Department3 50 50% 10%
> ---
> Total A 100 100% 20%
> Division B
> =======> Department4 100 25% 20%
> Department5 300 75% 60%
> ---
> Total A 400 100% 80%
> ========================> Total 500 100%
> What is the right way to calculate the Percentage for each Department?
> Should I calculate it in the SQL or could I use the abilities of SSRS?
You can do it either way. In SSRS, you could use an expression similar
to this to get the percentages per department.
=(Fields!Sales.Value/Sum(Fields!Sales.Value)) * 100
And then in the format section of the table column properties for the
T1 column, set the format to: #.00%
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant'
Percentage Column AND Row
Hello,
I have a matrix that looks as follows:
Name Jan Feb Total
John 5 6 11
Mary 3 4 7
Total 8 10 18
I want to add a percent column to the RIGHT of the total, and also on the bottom row. I can't find any clear examples of how to do this. If I had a new column, it adds additional headers beneath my top row. Or, my columns appear to the LEFT of the data, not the right. Can some please post some simple instructions that will make my simple matrix look like this:
Name Jan Feb Total %
John 5 6 11 60%
Mary 3 4 7 40%
Total 8 10 18 100%
% 40% 60% 100% 100%
I am so stuck on this I can pull my hair out.
Thanks!
Michael
p.s. I really hope the next version of SSRS has a simple "Sub-total %" option that you can enable just like the sub-total column.
I know the feeling...I am having the same issues
The only answer I came up with is do ALL calculations in a Stored Procedure or in SQL and some kind of way arrange the data in a way that will display your info right in your report.
For example, I had to do a Variance (orginally it was a percent like yours, but they settled on a difference between the last two columns) and instead of doing it in Reporting Services, I created a cursor in SQL and then just did a select top (1) * SQL as dataset. Cheesy I know what what else to do with so little built in stuff within Reporting Services?
Hope that helps...
|||My simple example on this page is only a small portion of the types of matrices I need to generate. Most are dynamic, have dynamic columns, and various sub-groupings. The code to generate all the sub-totals and the primary totals would be miserable. I've done this before, and one of the reasons I moved to SSRS was because it made it so easy to create pivot tables out of my data and not have to write SQL to handle every possible situation. Your suggestion is probably reasonable, but I hate the idea of doing it manually, and manually adding the various columns and rows to display the data, rather than letting the matrix do all the work (which was my initial goal). I am still hopeful I can figure this out. If anyone else has a suggestion, please respond. I just can't believe that this is so difficult with SSRS.
Mike
|||If you find a better way using SSRS please post...I am sure all would like to know!
Thanks...
Subscribe to:
Posts (Atom)