Bug in OWC11 - Wrong Totals

Calculated measure shows incorrect totals!!!
[Measures].[Quantity (All Category)] = ([Measures].[Qty], [Category].[All Category])
let's say there are 3 categories ([Category].[Cat1], [Category].[Cat2], [Category].[Cat3]) with [Measures].[Qty] = 20 each.
The 3 categories are shown on the columns in the owc 11 pivottable and the [Measures].[Quantity (All Category)] shown as 60 for each category. But when i filtered out one of the category. The remaining 2 categories' [Measures].[Quantity (All Category)] shows as 40!!
I did not have this problem with using Crystal Analysis v10.

I'm using Analysis Services 2000. Is there a bug with pivottable services/MSOLAP8.0 driver?
Timmy

Here's my response to this question in the OLAP newgroup:

http://groups.google.com/group/microsoft.public.sqlserver.olap/msg/4b799055ad497b73

>>

This may be related to the "Visual Totals Mode" used by Excel and OWC, as explained in this past post:

http://groups.google.com/group/microsoft.public.sqlserver.olap/msg/fe7f645622c760b0

>>

From: Chris Webb
Date: Tues, Apr 19 2005 3:41 am

Well, I think the best place to start is probably the section on
VisualTotals
in this white paper:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/anserd...
What the paper calls 'Visual Totals Mode' is switched on by using the
'Default MDX Visual Mode' connection string property, and this is what
Excel
uses to calculate its subtotals. So once you know that the subtotals are
really just parent members with their values overwritten, it's clear
that
calculated measures are going to be evaluated after this has taken place
and
so aren't going to be affected - and that's why, to get the behaviour
needed
in this case, it was necessary to create a real measure and get the
desired
values into it somehow.
>>

To see the VisualTotals() effect in AS 2000, try this Foodmart query in
the MDX Sample app:

With Member [Measures].[Sales (All Category)] as
'([Measures].[Unit Sales], [Product].[All Products])'

select {[Measures].[Unit Sales], [Measures].[Sales (All Category)]} on 0,
VisualTotals(Except(DrillDownLevel({[Product].[All Products]}),
{[Product].[All Products].[Drink]})) on 1
from Sales

>>

Breakdown by category

Let's say you a 1000 records in the Employees table, who are spread over 40 different cities.
How would you get a breakdown of how many employees in each city ?

Do I have to loop with a Count(*) for each CityID, or something ?

There must be a more straightforward method.Like this:
SELECT
CityID,
COUNT(*)
FROM
Employees
GROUP BY
CityID

The key part of that is the GROUP BY. That means to collapse theresults on the CityID column, and the COUNT(*) will keep track of howmany rows were collapsed.

Brainstorming: Ideas for categories database structure please

Hi

Thanks in advance for any ideas.

What I'm trying to do, is have a category system like Yahoo or these
newsgroups where you have a parent "COMP" and multiple children "LANG"
or "DATABASES". However, these can also have children. So for
"DATABASES", you have "MS-SQLSERVER" "ACCESS" "ORACLE8" etc

My question is, what is the best way to store these in a database and
also allow me to retrieve the data

I'm not sure how to procede

Thanks

SamWhat you want to do is store a tree structure. Celko has written
extensively on Nested Sets - you can also use a modified adjacency list
(something I prefer), storing the full path to each node with each record.
Anyway, if you search on Nested Sets or Adjacency List, you will find all
the information you need.

"Samuel Hon" <noreply@.samuelhon.co.uk> wrote in message
news:c8672b7d.0309231103.21d812fb@.posting.google.c om...
> Hi
> Thanks in advance for any ideas.
> What I'm trying to do, is have a category system like Yahoo or these
> newsgroups where you have a parent "COMP" and multiple children "LANG"
> or "DATABASES". However, these can also have children. So for
> "DATABASES", you have "MS-SQLSERVER" "ACCESS" "ORACLE8" etc
> My question is, what is the best way to store these in a database and
> also allow me to retrieve the data
> I'm not sure how to procede
> Thanks
> Sam|||Thanks

"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in message news:<bkq745$9fp$1$8300dec7@.news.demon.co.uk>...
> What you want to do is store a tree structure. Celko has written
> extensively on Nested Sets - you can also use a modified adjacency list
> (something I prefer), storing the full path to each node with each record.
> Anyway, if you search on Nested Sets or Adjacency List, you will find all
> the information you need.
> "Samuel Hon" <noreply@.samuelhon.co.uk> wrote in message
> news:c8672b7d.0309231103.21d812fb@.posting.google.c om...
> > Hi
> > Thanks in advance for any ideas.
> > What I'm trying to do, is have a category system like Yahoo or these
> > newsgroups where you have a parent "COMP" and multiple children "LANG"
> > or "DATABASES". However, these can also have children. So for
> > "DATABASES", you have "MS-SQLSERVER" "ACCESS" "ORACLE8" etc
> > My question is, what is the best way to store these in a database and
> > also allow me to retrieve the data
> > I'm not sure how to procede
> > Thanks
> > Sam|||You can do it in one table.

Use a few fields like child-id (unique index), name, parent_id

so your table might look something like

CHILD_ID NAME PARENT_ID
1 COMP 0
2 LANG 1
3 DATABASES 1
4 MS_SQLSERVER 3
5 ACCESS 3
6 ORACLE 3
7 VB 5 2
8 COBOL 2
9 ORACLE FORMS 2
10 ASP 2
11 HTML 2
12 JSCRIPT 2

So you can immediately , by knowing the parent ID, which would be a hard
coded static reference code (so languages will always have a parent code of
2), find all child codes and therefore types

???????

"Samuel Hon" <noreply@.samuelhon.co.uk> wrote in message
news:c8672b7d.0309240054.14d3f324@.posting.google.c om...
> Thanks
> "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:<bkq745$9fp$1$8300dec7@.news.demon.co.uk>...
> > What you want to do is store a tree structure. Celko has written
> > extensively on Nested Sets - you can also use a modified adjacency list
> > (something I prefer), storing the full path to each node with each
record.
> > Anyway, if you search on Nested Sets or Adjacency List, you will find
all
> > the information you need.
> > "Samuel Hon" <noreply@.samuelhon.co.uk> wrote in message
> > news:c8672b7d.0309231103.21d812fb@.posting.google.c om...
> > > Hi
> > > > Thanks in advance for any ideas.
> > > > What I'm trying to do, is have a category system like Yahoo or these
> > > newsgroups where you have a parent "COMP" and multiple children "LANG"
> > > or "DATABASES". However, these can also have children. So for
> > > "DATABASES", you have "MS-SQLSERVER" "ACCESS" "ORACLE8" etc
> > > > My question is, what is the best way to store these in a database and
> > > also allow me to retrieve the data
> > > > I'm not sure how to procede
> > > > Thanks
> > > > Sam|||You have here an adjacency list. Pretty standard way of doing things BUT
very difficult to write queries about subtrees and relations that aren't
immediate parent/child. You might also store the full path with each
record, as I do (which is usually efficient enough) to allow easy subtree
queries using "LIKE" operator.

"cor_blimey" <no_spam@.no_spam.com> wrote in message
news:1Jdcb.523$_N1.384673@.newsfep1-win.server.ntli.net...
> You can do it in one table.
> Use a few fields like child-id (unique index), name, parent_id
> so your table might look something like
> CHILD_ID NAME PARENT_ID
> 1 COMP 0
> 2 LANG 1
> 3 DATABASES 1
> 4 MS_SQLSERVER 3
> 5 ACCESS 3
> 6 ORACLE 3
> 7 VB 5 2
> 8 COBOL 2
> 9 ORACLE FORMS 2
> 10 ASP 2
> 11 HTML 2
> 12 JSCRIPT 2
>
> So you can immediately , by knowing the parent ID, which would be a hard
> coded static reference code (so languages will always have a parent code
of
> 2), find all child codes and therefore types
> ???????
>
> "Samuel Hon" <noreply@.samuelhon.co.uk> wrote in message
> news:c8672b7d.0309240054.14d3f324@.posting.google.c om...
> > Thanks
> > "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
> message news:<bkq745$9fp$1$8300dec7@.news.demon.co.uk>...
> > > What you want to do is store a tree structure. Celko has written
> > > extensively on Nested Sets - you can also use a modified adjacency
list
> > > (something I prefer), storing the full path to each node with each
> record.
> > > Anyway, if you search on Nested Sets or Adjacency List, you will find
> all
> > > the information you need.
> > > > "Samuel Hon" <noreply@.samuelhon.co.uk> wrote in message
> > > news:c8672b7d.0309231103.21d812fb@.posting.google.c om...
> > > > Hi
> > > > > > Thanks in advance for any ideas.
> > > > > > What I'm trying to do, is have a category system like Yahoo or these
> > > > newsgroups where you have a parent "COMP" and multiple children
"LANG"
> > > > or "DATABASES". However, these can also have children. So for
> > > > "DATABASES", you have "MS-SQLSERVER" "ACCESS" "ORACLE8" etc
> > > > > > My question is, what is the best way to store these in a database
and
> > > > also allow me to retrieve the data
> > > > > > I'm not sure how to procede
> > > > > > Thanks
> > > > > > Sam