hi all
i am working on sql reporting 2005.
i have 3 reports say A, B ,C
i want to display sum of values in column of A & sum of values in column of B in report C
How can i do this?
plz help me.
report c would have to have the same data as in reports A and B. Then you can sum them.
Showing posts with label sum. Show all posts
Showing posts with label sum. Show all posts
Thursday, March 22, 2012
Monday, March 19, 2012
differnce between 2 aggregate columns
Hi,
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJ
Please post the exact DDL. Are these views that contain aggregates or are
you aggregating the data from the views?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<aj70000@.hotmail.com> wrote in message
news:1142034715.639722.315230@.u72g2000cwu.googlegr oups.com...
Hi,
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJ
|||they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ
|||Both of the views - revenue and expenses - are invalid in SQL Server. You
have a combination of aggregate and non-aggregate columns in the SELECT
lists, without having a GROUP BY. How about giving us the exact scripts
for those views?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<aj70000@.hotmail.com> wrote in message
news:1142040048.264954.327010@.i39g2000cwa.googlegr oups.com...
they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ
|||Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ
|||If I cut and paste those statements, they don't work.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<aj70000@.hotmail.com> wrote in message
news:1142046177.298720.116610@.i39g2000cwa.googlegr oups.com...
Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ
|||On 10 Mar 2006 15:51:55 -0800, aj70000@.hotmail.com wrote:
>Hi,
>-I have 2 views 1)revenue, 2)expenses
>-Columns are Client,Year,sum(Amount),Business unit. on both of them
>Need some help on writing the query.
>I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
>Thanks
>AJ
Hi AJ,
Try if this works for you. If not, see www.aspfaq.com.5006 to find out
how to post CREATE TABLE and CREATE VIEW statements for the table and
view structures, INSERT statements for sample data, and required output.
SELECT Client, Year, BU, SUM(Amount)
FROM (SELECT Client, Year, BU, Amount
FROM Revenue
UNION ALL
SELECT Client, Year, BU, -Amount
FROM Expenses) AS D
GROUP BY Client, Year, BU
Hugo Kornelis, SQL Server MVP
|||dept (BU) is a funny name for a column.
the logic is going to be tough to fgiure out, and even tougher to
maintain over time. How about you combine table one and table two,
have a dollar amount, include the account or a field that indicates
whether a row is expense or revenue?
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJ
Please post the exact DDL. Are these views that contain aggregates or are
you aggregating the data from the views?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<aj70000@.hotmail.com> wrote in message
news:1142034715.639722.315230@.u72g2000cwu.googlegr oups.com...
Hi,
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJ
|||they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ
|||Both of the views - revenue and expenses - are invalid in SQL Server. You
have a combination of aggregate and non-aggregate columns in the SELECT
lists, without having a GROUP BY. How about giving us the exact scripts
for those views?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<aj70000@.hotmail.com> wrote in message
news:1142040048.264954.327010@.i39g2000cwa.googlegr oups.com...
they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ
|||Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ
|||If I cut and paste those statements, they don't work.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<aj70000@.hotmail.com> wrote in message
news:1142046177.298720.116610@.i39g2000cwa.googlegr oups.com...
Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ
|||On 10 Mar 2006 15:51:55 -0800, aj70000@.hotmail.com wrote:
>Hi,
>-I have 2 views 1)revenue, 2)expenses
>-Columns are Client,Year,sum(Amount),Business unit. on both of them
>Need some help on writing the query.
>I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
>Thanks
>AJ
Hi AJ,
Try if this works for you. If not, see www.aspfaq.com.5006 to find out
how to post CREATE TABLE and CREATE VIEW statements for the table and
view structures, INSERT statements for sample data, and required output.
SELECT Client, Year, BU, SUM(Amount)
FROM (SELECT Client, Year, BU, Amount
FROM Revenue
UNION ALL
SELECT Client, Year, BU, -Amount
FROM Expenses) AS D
GROUP BY Client, Year, BU
Hugo Kornelis, SQL Server MVP
|||dept (BU) is a funny name for a column.
the logic is going to be tough to fgiure out, and even tougher to
maintain over time. How about you combine table one and table two,
have a dollar amount, include the account or a field that indicates
whether a row is expense or revenue?
differnce between 2 aggregate columns
Hi,
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJPlease post the exact DDL. Are these views that contain aggregates or are
you aggregating the data from the views?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142034715.639722.315230@.u72g2000cwu.googlegroups.com...
Hi,
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJ|||they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ|||Both of the views - revenue and expenses - are invalid in SQL Server. You
have a combination of aggregate and non-aggregate columns in the SELECT
lists, without having a GROUP BY. How about giving us the exact scripts
for those views?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142040048.264954.327010@.i39g2000cwa.googlegroups.com...
they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ|||Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ|||If I cut and paste those statements, they don't work.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142046177.298720.116610@.i39g2000cwa.googlegroups.com...
Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ|||On 10 Mar 2006 15:51:55 -0800, aj70000@.hotmail.com wrote:
>Hi,
>-I have 2 views 1)revenue, 2)expenses
>-Columns are Client,Year,sum(Amount),Business unit. on both of them
>Need some help on writing the query.
>I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
>Thanks
>AJ
Hi AJ,
Try if this works for you. If not, see www.aspfaq.com.5006 to find out
how to post CREATE TABLE and CREATE VIEW statements for the table and
view structures, INSERT statements for sample data, and required output.
SELECT Client, Year, BU, SUM(Amount)
FROM (SELECT Client, Year, BU, Amount
FROM Revenue
UNION ALL
SELECT Client, Year, BU, -Amount
FROM Expenses) AS D
GROUP BY Client, Year, BU
Hugo Kornelis, SQL Server MVP|||dept (BU) is a funny name for a column.
the logic is going to be tough to fgiure out, and even tougher to
maintain over time. How about you combine table one and table two,
have a dollar amount, include the account or a field that indicates
whether a row is expense or revenue?
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJPlease post the exact DDL. Are these views that contain aggregates or are
you aggregating the data from the views?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142034715.639722.315230@.u72g2000cwu.googlegroups.com...
Hi,
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJ|||they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ|||Both of the views - revenue and expenses - are invalid in SQL Server. You
have a combination of aggregate and non-aggregate columns in the SELECT
lists, without having a GROUP BY. How about giving us the exact scripts
for those views?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142040048.264954.327010@.i39g2000cwa.googlegroups.com...
they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ|||Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ|||If I cut and paste those statements, they don't work.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142046177.298720.116610@.i39g2000cwa.googlegroups.com...
Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ|||On 10 Mar 2006 15:51:55 -0800, aj70000@.hotmail.com wrote:
>Hi,
>-I have 2 views 1)revenue, 2)expenses
>-Columns are Client,Year,sum(Amount),Business unit. on both of them
>Need some help on writing the query.
>I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
>Thanks
>AJ
Hi AJ,
Try if this works for you. If not, see www.aspfaq.com.5006 to find out
how to post CREATE TABLE and CREATE VIEW statements for the table and
view structures, INSERT statements for sample data, and required output.
SELECT Client, Year, BU, SUM(Amount)
FROM (SELECT Client, Year, BU, Amount
FROM Revenue
UNION ALL
SELECT Client, Year, BU, -Amount
FROM Expenses) AS D
GROUP BY Client, Year, BU
Hugo Kornelis, SQL Server MVP|||dept (BU) is a funny name for a column.
the logic is going to be tough to fgiure out, and even tougher to
maintain over time. How about you combine table one and table two,
have a dollar amount, include the account or a field that indicates
whether a row is expense or revenue?
differnce between 2 aggregate columns
Hi,
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJPlease post the exact DDL. Are these views that contain aggregates or are
you aggregating the data from the views?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142034715.639722.315230@.u72g2000cwu.googlegroups.com...
Hi,
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJ|||they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ|||Both of the views - revenue and expenses - are invalid in SQL Server. You
have a combination of aggregate and non-aggregate columns in the SELECT
lists, without having a GROUP BY. How about giving us the exact scripts
for those views?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142040048.264954.327010@.i39g2000cwa.googlegroups.com...
they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ|||Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ|||If I cut and paste those statements, they don't work.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142046177.298720.116610@.i39g2000cwa.googlegroups.com...
Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ|||On 10 Mar 2006 15:51:55 -0800, aj70000@.hotmail.com wrote:
>Hi,
>-I have 2 views 1)revenue, 2)expenses
>-Columns are Client,Year,sum(Amount),Business unit. on both of them
>Need some help on writing the query.
>I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
>Thanks
>AJ
Hi AJ,
Try if this works for you. If not, see www.aspfaq.com.5006 to find out
how to post CREATE TABLE and CREATE VIEW statements for the table and
view structures, INSERT statements for sample data, and required output.
SELECT Client, Year, BU, SUM(Amount)
FROM (SELECT Client, Year, BU, Amount
FROM Revenue
UNION ALL
SELECT Client, Year, BU, -Amount
FROM Expenses) AS D
GROUP BY Client, Year, BU
--
Hugo Kornelis, SQL Server MVP|||dept (BU) is a funny name for a column.
the logic is going to be tough to fgiure out, and even tougher to
maintain over time. How about you combine table one and table two,
have a dollar amount, include the account or a field that indicates
whether a row is expense or revenue?
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJPlease post the exact DDL. Are these views that contain aggregates or are
you aggregating the data from the views?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142034715.639722.315230@.u72g2000cwu.googlegroups.com...
Hi,
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJ|||they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ|||Both of the views - revenue and expenses - are invalid in SQL Server. You
have a combination of aggregate and non-aggregate columns in the SELECT
lists, without having a GROUP BY. How about giving us the exact scripts
for those views?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142040048.264954.327010@.i39g2000cwa.googlegroups.com...
they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ|||Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ|||If I cut and paste those statements, they don't work.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142046177.298720.116610@.i39g2000cwa.googlegroups.com...
Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ|||On 10 Mar 2006 15:51:55 -0800, aj70000@.hotmail.com wrote:
>Hi,
>-I have 2 views 1)revenue, 2)expenses
>-Columns are Client,Year,sum(Amount),Business unit. on both of them
>Need some help on writing the query.
>I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
>Thanks
>AJ
Hi AJ,
Try if this works for you. If not, see www.aspfaq.com.5006 to find out
how to post CREATE TABLE and CREATE VIEW statements for the table and
view structures, INSERT statements for sample data, and required output.
SELECT Client, Year, BU, SUM(Amount)
FROM (SELECT Client, Year, BU, Amount
FROM Revenue
UNION ALL
SELECT Client, Year, BU, -Amount
FROM Expenses) AS D
GROUP BY Client, Year, BU
--
Hugo Kornelis, SQL Server MVP|||dept (BU) is a funny name for a column.
the logic is going to be tough to fgiure out, and even tougher to
maintain over time. How about you combine table one and table two,
have a dollar amount, include the account or a field that indicates
whether a row is expense or revenue?
Friday, March 9, 2012
different values between Relational and MOLAP in a sum measure... BUG?
Hello,
I'm working in a Windows 2003 Server, SQL Server 2000 and Analysis Services 2000.
I have created two models in Analysis Services (MOLAP):
- MODEL 1 gets info directly from the relational FACT table: "FCT_SALES"
- MODEL 2 gets info from a view with: "SELECT * FROM FCT_SALES"
FCT_SALES has a measure (sum and double - the column in the relational table is float).
This measure has 10 rows in the FCT_SALES.
Problem:
When we agregate the measure rows in Query Analyzer, MODEL 1 and MODEL 2 return 20. --> IT'S OK!
When I use Analysis Services (and ProClarity), MODEL 1 returns 20. --> IT'S OK!
When I use Analysis Services (and ProClarity), MODEL 2 returns 17. -->WRONG!!
WHY?
When I make Drill Through (Drill to Detail) in Analysis Services (or ProClarity) to see the rows that compose the value, and export them to Excel, I get the 20. --> IT'S OK!
So, WHY does Analysis Services (and ProClarity) give me 17 ?!?
If the SUM(rows) give me 20, WHY does Analysis Services give me 17 ?!?
Thanks.
Hi guys,
Yesterday nigth, I found the problem: something that shouldn't happen in the source info, happened! Murphy's Law
)))
Analysis Services wasn't causing the info inconsistent.
Thanks anyway.
I'm working in a Windows 2003 Server, SQL Server 2000 and Analysis Services 2000.
I have created two models in Analysis Services (MOLAP):
- MODEL 1 gets info directly from the relational FACT table: "FCT_SALES"
- MODEL 2 gets info from a view with: "SELECT * FROM FCT_SALES"
FCT_SALES has a measure (sum and double - the column in the relational table is float).
This measure has 10 rows in the FCT_SALES.
Problem:
When we agregate the measure rows in Query Analyzer, MODEL 1 and MODEL 2 return 20. --> IT'S OK!
When I use Analysis Services (and ProClarity), MODEL 1 returns 20. --> IT'S OK!
When I use Analysis Services (and ProClarity), MODEL 2 returns 17. -->WRONG!!
WHY?
When I make Drill Through (Drill to Detail) in Analysis Services (or ProClarity) to see the rows that compose the value, and export them to Excel, I get the 20. --> IT'S OK!
So, WHY does Analysis Services (and ProClarity) give me 17 ?!?
If the SUM(rows) give me 20, WHY does Analysis Services give me 17 ?!?
Thanks.
Hi guys,
Yesterday nigth, I found the problem: something that shouldn't happen in the source info, happened! Murphy's Law
)))Analysis Services wasn't causing the info inconsistent.
Thanks anyway.
different values between Relational and MOLAP in a sum measure... BUG?
Hello,
I'm working in a Windows 2003 Server, SQL Server 2000 and Analysis Services
2000.
I have created two models in Analysis Services (MOLAP):
- MODEL 1 gets info directly from the relational FACT table: "FCT_SALES"
- MODEL 2 gets info from a view with: "SELECT * FROM FCT_SALES"
FCT_SALES has a measure (sum and double - the column in the relational table
is float).
This measure has 10 rows in the FCT_SALES.
Problem:
When we agregate the measure rows in Query Analyzer, MODEL 1 and MODEL 2 ret
urn 20. --> IT'S OK!
When I use Analysis Services (and ProClarity), MODEL 1 returns 20. --> IT'S
OK!
When I use Analysis Services (and ProClarity), MODEL 2 returns 17. -->WRONG!
!
WHY?
When I make Drill Through (Drill to Detail) in Analysis Services (or ProClar
ity) to see the rows that compose the value, and export them to Excel, I get
the 20. --> IT'S OK!
So, WHY does Analysis Services (and ProClarity) give me 17 ?!?
If the SUM(rows) give me 20, WHY does Analysis Services give me 17 ?!?
Thanks.Hi guys,
Yesterday nigth, I found the problem: something that shouldn't happen in the
source info, happened! Murphy's Law)))
Analysis Services wasn't causing the info inconsistent.
Thanks anyway.
I'm working in a Windows 2003 Server, SQL Server 2000 and Analysis Services
2000.
I have created two models in Analysis Services (MOLAP):
- MODEL 1 gets info directly from the relational FACT table: "FCT_SALES"
- MODEL 2 gets info from a view with: "SELECT * FROM FCT_SALES"
FCT_SALES has a measure (sum and double - the column in the relational table
is float).
This measure has 10 rows in the FCT_SALES.
Problem:
When we agregate the measure rows in Query Analyzer, MODEL 1 and MODEL 2 ret
urn 20. --> IT'S OK!
When I use Analysis Services (and ProClarity), MODEL 1 returns 20. --> IT'S
OK!
When I use Analysis Services (and ProClarity), MODEL 2 returns 17. -->WRONG!
!
WHY?
When I make Drill Through (Drill to Detail) in Analysis Services (or ProClar
ity) to see the rows that compose the value, and export them to Excel, I get
the 20. --> IT'S OK!
So, WHY does Analysis Services (and ProClarity) give me 17 ?!?
If the SUM(rows) give me 20, WHY does Analysis Services give me 17 ?!?
Thanks.Hi guys,
Yesterday nigth, I found the problem: something that shouldn't happen in the
source info, happened! Murphy's Law
)))Analysis Services wasn't causing the info inconsistent.
Thanks anyway.
Wednesday, March 7, 2012
Different sums from table
Could someone explain to me, how I can get sum from row which I have values in 2 colums and I want the realtime sum to third column. Fourth colum is for item.
Also can someone tell me how to sum these third colums where the item is same so I have real time values for the item sum.
Thanks!
AD
Hi,
regardless that this makes no sense at all, this could be an example (as far as I understood your problem):
Select OrderId, Sum(Unitprice) + Sum (Quantity) AS ThirdColumn
from [Order Details]
Group by OrderId
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
Different Results using Aggregate and Sum
I'm having trouble understanding the results I am getting from a query. The goal is to get the sum of a measure from the beginning of time (at the month level) through the current month.
Here is my query:
WITH
MEMBER [Date].[Fiscal].[ThruNow] AS
AGGREGATE([Date].[Fiscal].[Fiscal Period].Members(0) : ANCESTOR([Date].[Fiscal].CurrentMember, [Date].[Fiscal].[Fiscal Period]))
MEMBER Temp AS ([Date].[Fiscal].[ThruNow], [OO Units])
MEMBER Temp2 AS SUM([Date].[Fiscal].[Fiscal Period].Members(0) : ANCESTOR([Date].[Fiscal].CurrentMember, [Date].[Fiscal].[Fiscal Period]), [OO Units])
SELECT
{ Temp, Temp2 } ON 0
,{ [Product].[Products].[Division].&[R-B-D].Children } ON 1
FROM [Merchandising]
WHERE ([Date].[Fiscal].[Fiscal Week].&[2007 16])
The Temp2 member is giving me the correct results. However the Temp member gives me the sum of the measure across all time. Can anyone explain this to me?
MEMBER [Date].[Fiscal].[ThruNow] AS
AGGREGATE([Date].[Fiscal].[Fiscal Period].Members(0) : ANCESTOR([Date].[Fiscal].CurrentMember, [Date].[Fiscal].[Fiscal Period]))
Since [ThruNow] is defined on the [Date].[Fiscal] hierarchy, [Date].[Fiscal].CurrentMember is [Date].[Fiscal].[ThruNow] when the latter is computed, which may not be what you intended. On the other hand, this may work fine:
MEMBER Temp AS AGGREGATE([Date].[Fiscal].[Fiscal Period].Members(0) : ANCESTOR([Date].[Fiscal].CurrentMember, [Date].[Fiscal].[Fiscal Period]), [OO Units])
Subscribe to:
Posts (Atom)