how to simplify what i'm doing (AS the solution?)

i need to do create reports that perform queries where the results are
percentages of total count divided by counts based on different
subquery where values.
a la ...
select 100 * (totalCount/ (select count where columnA value is x) as
partialCount1) as perc1,
100 * (totalCount/ (select count where columnA value is y) as
partialCount2) as perc2
etc
from myTable where (..bunch of where stuff..)
I have simplified the above example just for brevity here.
So, I hate these kinds of nested queries - and they get incredibly
complex to read. I could break them up into longer scripts to simplify
- but I am wondering if there's another way.
The data I am using is from a warehouse on SQL 2005. I have very little
experience with warehouses - and know little about analysis services
and OLAP.
My question is whether it is possible to use Analysis services to
create new tables that make this data far easier to query? That's the
path I am considering venturing down, but I don't want to hit a dead
end... and need just to know if this makes sense. That is to use
Analysis services to crunch out some new tables from the tables I am
using which would have the results neatly packaged in a table or 2 that
is fast and simple to query. Is that the idea of AS?
thx for helping a learner here. let me know if i am making enough sense
in the question.
Hi
This looks like you are trying to do a pivot, in which case the best
solutions will be at the front end although with SQL 2005 you have the new
pivot function, check out the topic "Using PIVOT and UNPIVOT " in books
online.
John
<rsphere@.gmail.com> wrote in message
news:1137856740.675188.233050@.g49g2000cwa.googlegr oups.com...
>i need to do create reports that perform queries where the results are
> percentages of total count divided by counts based on different
> subquery where values.
> a la ...
> select 100 * (totalCount/ (select count where columnA value is x) as
> partialCount1) as perc1,
> 100 * (totalCount/ (select count where columnA value is y) as
> partialCount2) as perc2
> etc
> from myTable where (..bunch of where stuff..)
> I have simplified the above example just for brevity here.
> So, I hate these kinds of nested queries - and they get incredibly
> complex to read. I could break them up into longer scripts to simplify
> - but I am wondering if there's another way.
> The data I am using is from a warehouse on SQL 2005. I have very little
> experience with warehouses - and know little about analysis services
> and OLAP.
> My question is whether it is possible to use Analysis services to
> create new tables that make this data far easier to query? That's the
> path I am considering venturing down, but I don't want to hit a dead
> end... and need just to know if this makes sense. That is to use
> Analysis services to crunch out some new tables from the tables I am
> using which would have the results neatly packaged in a table or 2 that
> is fast and simple to query. Is that the idea of AS?
> thx for helping a learner here. let me know if i am making enough sense
> in the question.
>

how to simplify what i'm doing (AS the solution?)

i need to do create reports that perform queries where the results are
percentages of total count divided by counts based on different
subquery where values.
a la ...
select 100 * (totalCount/ (select count where columnA value is x) as
partialCount1) as perc1,
100 * (totalCount/ (select count where columnA value is y) as
partialCount2) as perc2
etc
from myTable where (..bunch of where stuff..)
I have simplified the above example just for brevity here.
So, I hate these kinds of nested queries - and they get incredibly
complex to read. I could break them up into longer scripts to simplify
- but I am wondering if there's another way.
The data I am using is from a warehouse on SQL 2005. I have very little
experience with warehouses - and know little about analysis services
and OLAP.
My question is whether it is possible to use Analysis services to
create new tables that make this data far easier to query? That's the
path I am considering venturing down, but I don't want to hit a dead
end... and need just to know if this makes sense. That is to use
Analysis services to crunch out some new tables from the tables I am
using which would have the results neatly packaged in a table or 2 that
is fast and simple to query. Is that the idea of AS?
thx for helping a learner here. let me know if i am making enough sense
in the question.Hi
This looks like you are trying to do a pivot, in which case the best
solutions will be at the front end although with SQL 2005 you have the new
pivot function, check out the topic "Using PIVOT and UNPIVOT " in books
online.
John
<rsphere@.gmail.com> wrote in message
news:1137856740.675188.233050@.g49g2000cwa.googlegroups.com...
>i need to do create reports that perform queries where the results are
> percentages of total count divided by counts based on different
> subquery where values.
> a la ...
> select 100 * (totalCount/ (select count where columnA value is x) as
> partialCount1) as perc1,
> 100 * (totalCount/ (select count where columnA value is y) as
> partialCount2) as perc2
> etc
> from myTable where (..bunch of where stuff..)
> I have simplified the above example just for brevity here.
> So, I hate these kinds of nested queries - and they get incredibly
> complex to read. I could break them up into longer scripts to simplify
> - but I am wondering if there's another way.
> The data I am using is from a warehouse on SQL 2005. I have very little
> experience with warehouses - and know little about analysis services
> and OLAP.
> My question is whether it is possible to use Analysis services to
> create new tables that make this data far easier to query? That's the
> path I am considering venturing down, but I don't want to hit a dead
> end... and need just to know if this makes sense. That is to use
> Analysis services to crunch out some new tables from the tables I am
> using which would have the results neatly packaged in a table or 2 that
> is fast and simple to query. Is that the idea of AS?
> thx for helping a learner here. let me know if i am making enough sense
> in the question.
>

how to simplify what i'm doing (AS the solution?)

i need to do create reports that perform queries where the results are
percentages of total count divided by counts based on different
subquery where values.
a la ...
select 100 * (totalCount/ (select count where columnA value is x) as
partialCount1) as perc1,
100 * (totalCount/ (select count where columnA value is y) as
partialCount2) as perc2
etc
from myTable where (..bunch of where stuff..)
I have simplified the above example just for brevity here.
So, I hate these kinds of nested queries - and they get incredibly
complex to read. I could break them up into longer scripts to simplify
- but I am wondering if there's another way.
The data I am using is from a warehouse on SQL 2005. I have very little
experience with warehouses - and know little about analysis services
and OLAP.
My question is whether it is possible to use Analysis services to
create new tables that make this data far easier to query? That's the
path I am considering venturing down, but I don't want to hit a dead
end... and need just to know if this makes sense. That is to use
Analysis services to crunch out some new tables from the tables I am
using which would have the results neatly packaged in a table or 2 that
is fast and simple to query. Is that the idea of AS?
thx for helping a learner here. let me know if i am making enough sense
in the question.Hi
This looks like you are trying to do a pivot, in which case the best
solutions will be at the front end although with SQL 2005 you have the new
pivot function, check out the topic "Using PIVOT and UNPIVOT " in books
online.
John
<rsphere@.gmail.com> wrote in message
news:1137856740.675188.233050@.g49g2000cwa.googlegroups.com...
>i need to do create reports that perform queries where the results are
> percentages of total count divided by counts based on different
> subquery where values.
> a la ...
> select 100 * (totalCount/ (select count where columnA value is x) as
> partialCount1) as perc1,
> 100 * (totalCount/ (select count where columnA value is y) as
> partialCount2) as perc2
> etc
> from myTable where (..bunch of where stuff..)
> I have simplified the above example just for brevity here.
> So, I hate these kinds of nested queries - and they get incredibly
> complex to read. I could break them up into longer scripts to simplify
> - but I am wondering if there's another way.
> The data I am using is from a warehouse on SQL 2005. I have very little
> experience with warehouses - and know little about analysis services
> and OLAP.
> My question is whether it is possible to use Analysis services to
> create new tables that make this data far easier to query? That's the
> path I am considering venturing down, but I don't want to hit a dead
> end... and need just to know if this makes sense. That is to use
> Analysis services to crunch out some new tables from the tables I am
> using which would have the results neatly packaged in a table or 2 that
> is fast and simple to query. Is that the idea of AS?
> thx for helping a learner here. let me know if i am making enough sense
> in the question.
>

How to show data at the bottom with multiple section?

I have three report footer setion. The first two section has a subreport in it. The last section has the grand total of a amount to both of the subreports.

I want to be able to show all three of these report footer section at the bottom of the page.

I have try the PrintAtBottomOfPage properties. I set it to TRUE to all of the report footer section. When I run the report, each section just shown on it own page.

Is there is a way to have all three section onto one page and on top of each other, not overlaying each other or showing on it own page?HI,
PrintAtBottomOfPage prints on the each pages bottom, so when this works
it still shows inside the Subreports bottm, if u want to get a value from sub report to main report use

1. Have a SHARED Variable(s) in a formula(s) for each of u r subreport(s)
2. Place those above the subreport(s) in the main Rpt, preferabblyy in the main reports header
3. in side the Subreports use a formula to sum u r values using
WhilePrintingRecords function to a varible u have declared 4 that Rpt.
(do this to u r all sub reports), place this formula in u r detail line of records printing (supress it, coz u need only the final answer).
4. in the main report sum the variable of sub reports declared in the main
or show seperatly as u wish in u r footer of main.

Favaz.|||I just want all three of the report footer section to be shown on the bottom of the same report of the first page and the subreport has the ability to GROW if there is a lot of data.

I just want all of the three report footer section to be all in the same page with my DETAILS section and just one page will be printed out.

I will not be pulling any variable from the subreport.|||Ummm .... I'm also having the same problem as his. Does anyone know the answer to his question?

please reply!

How to set the decimal places

I have a calculation in a stored procedure that returns a percentage like (3
2
* 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as float.
I need to set or convert the result to have 2 decimal places like 1.86,
rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
variations (examples from the internet), nothing is working for me. Is ther
e
a way to achieve this in SQL? I would really appreciate the help."HLong" <HLong@.discussions.microsoft.com> wrote in message
news:C7A3278F-FB47-451C-855E-8B0978DC5930@.microsoft.com...
>I have a calculation in a stored procedure that returns a percentage like
>(32
> * 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as
> float.
> I need to set or convert the result to have 2 decimal places like 1.86,
> rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
> variations (examples from the internet), nothing is working for me. Is
> there
> a way to achieve this in SQL? I would really appreciate the help.
Is this what ou want - SELECT CAST(32.0 * 100/1722 AS decimal(5,2)) ?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Thanks Dejan. I tried that same function yesterday so many times but I
always got
1.00, which was not good. However, now I tried 32*100.00 and it worked
fine. I don't know why it worked. May be because the 100.00 is taken as a
decimal type, instead of 32*100 where both are int. Could you explain this
a
bit more?
"Dejan Sarka" wrote:

> "HLong" <HLong@.discussions.microsoft.com> wrote in message
> news:C7A3278F-FB47-451C-855E-8B0978DC5930@.microsoft.com...
> Is this what ou want - SELECT CAST(32.0 * 100/1722 AS decimal(5,2)) ?
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>|||> Thanks Dejan. I tried that same function yesterday so many times but I
> always got
> 1.00, which was not good. However, now I tried 32*100.00 and it worked
> fine. I don't know why it worked. May be because the 100.00 is taken as
> a
> decimal type, instead of 32*100 where both are int. Could you explain
> this a
> bit more?
In T-SQL we do not denote data types for literal values like, for example,
in C#. So SQL Server uses it's own logic, and takes 32 and 100 as integers.
First operand data type is then used for result as well. You can also check
topics on data types precendence in Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

How to set the decimal places

I have a calculation in a stored procedure that returns a percentage like (32
* 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as float.
I need to set or convert the result to have 2 decimal places like 1.86,
rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
variations (examples from the internet), nothing is working for me. Is there
a way to achieve this in SQL? I would really appreciate the help.
"HLong" <HLong@.discussions.microsoft.com> wrote in message
news:C7A3278F-FB47-451C-855E-8B0978DC5930@.microsoft.com...
>I have a calculation in a stored procedure that returns a percentage like
>(32
> * 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as
> float.
> I need to set or convert the result to have 2 decimal places like 1.86,
> rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
> variations (examples from the internet), nothing is working for me. Is
> there
> a way to achieve this in SQL? I would really appreciate the help.
Is this what ou want - SELECT CAST(32.0 * 100/1722 AS decimal(5,2)) ?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Thanks Dejan. I tried that same function yesterday so many times but I
always got
1.00, which was not good. However, now I tried 32*100.00 and it worked
fine. I don't know why it worked. May be because the 100.00 is taken as a
decimal type, instead of 32*100 where both are int. Could you explain this a
bit more?
"Dejan Sarka" wrote:

> "HLong" <HLong@.discussions.microsoft.com> wrote in message
> news:C7A3278F-FB47-451C-855E-8B0978DC5930@.microsoft.com...
> Is this what ou want - SELECT CAST(32.0 * 100/1722 AS decimal(5,2)) ?
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>
|||> Thanks Dejan. I tried that same function yesterday so many times but I
> always got
> 1.00, which was not good. However, now I tried 32*100.00 and it worked
> fine. I don't know why it worked. May be because the 100.00 is taken as
> a
> decimal type, instead of 32*100 where both are int. Could you explain
> this a
> bit more?
In T-SQL we do not denote data types for literal values like, for example,
in C#. So SQL Server uses it's own logic, and takes 32 and 100 as integers.
First operand data type is then used for result as well. You can also check
topics on data types precendence in Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

How to set the decimal places

I have a calculation in a stored procedure that returns a percentage like (32
* 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as float.
I need to set or convert the result to have 2 decimal places like 1.86,
rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
variations (examples from the internet), nothing is working for me. Is there
a way to achieve this in SQL? I would really appreciate the help."HLong" <HLong@.discussions.microsoft.com> wrote in message
news:C7A3278F-FB47-451C-855E-8B0978DC5930@.microsoft.com...
>I have a calculation in a stored procedure that returns a percentage like
>(32
> * 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as
> float.
> I need to set or convert the result to have 2 decimal places like 1.86,
> rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
> variations (examples from the internet), nothing is working for me. Is
> there
> a way to achieve this in SQL? I would really appreciate the help.
Is this what ou want - SELECT CAST(32.0 * 100/1722 AS decimal(5,2)) ?
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Thanks Dejan. I tried that same function yesterday so many times but I
always got
1.00, which was not good. However, now I tried 32*100.00 and it worked
fine. I don't know why it worked. May be because the 100.00 is taken as a
decimal type, instead of 32*100 where both are int. Could you explain this a
bit more?
"Dejan Sarka" wrote:
> "HLong" <HLong@.discussions.microsoft.com> wrote in message
> news:C7A3278F-FB47-451C-855E-8B0978DC5930@.microsoft.com...
> >I have a calculation in a stored procedure that returns a percentage like
> >(32
> > * 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as
> > float.
> > I need to set or convert the result to have 2 decimal places like 1.86,
> > rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
> > variations (examples from the internet), nothing is working for me. Is
> > there
> > a way to achieve this in SQL? I would really appreciate the help.
> Is this what ou want - SELECT CAST(32.0 * 100/1722 AS decimal(5,2)) ?
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>|||> Thanks Dejan. I tried that same function yesterday so many times but I
> always got
> 1.00, which was not good. However, now I tried 32*100.00 and it worked
> fine. I don't know why it worked. May be because the 100.00 is taken as
> a
> decimal type, instead of 32*100 where both are int. Could you explain
> this a
> bit more?
In T-SQL we do not denote data types for literal values like, for example,
in C#. So SQL Server uses it's own logic, and takes 32 and 100 as integers.
First operand data type is then used for result as well. You can also check
topics on data types precendence in Books OnLine.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com