Help with query -SQL Express and ASP.net

I am having trouble with the below query. This is attached to a SQLDataAdapter which in turn is connected to a grid view.

@.pram5 is a dropdownlist
all other perameters such as @.nw in the big OR statement are check boxes.

My tables look similar to this:

Company TblComodity TblRegion

PK CompanyID PK CommodityID PK RegionID
CompanyName FK CompanyID FK CompanyID
CommodityName North
South, East, etc

What I would am trying to do is have a user slect a commodity which is a distinct value from the comodity table. Then select by tick boxes locations, then in the grid view companies with possible locations and commoditys appear. My problem is even when I select a commodity and leave all tick boxes blank (false) the records still display - like its only filltering on commodity name. Can anyone help ? I can provide more info if needed

Here is my query:

SELECT TblCompany.CompanyID, TblCompany.CompanyName, TblRegion.NorthWest, TblRegion.NorthEast, TblRegion.SouthEast, TblRegion.SouthWest,
TblRegion.Scotland, TblRegion.Wales, TblRegion.Midlands, TblRegion.UKNational, TblRegion.EuropOotherThanUK, TblComodity.ComName
FROM TblCompany INNER JOIN
TblRegion ON TblCompany.CompanyID = TblRegion.CompanyID INNER JOIN
TblComodity ON TblCompany.CompanyID = TblComodity.CompanyID AND TblComodity.ComName = @.pram5
WHERE (TblRegion.NorthWest = @.nw) OR
(TblRegion.NorthEast = @.NE) OR
(TblRegion.SouthEast = @.se) OR
(TblRegion.SouthWest = @.sw) OR
(TblRegion.Scotland = @.scot) OR
(TblRegion.Wales = @.wal) OR
(TblRegion.Midlands = @.mid) OR
(TblRegion.EuropOotherThanUK = @.EU) AND (TblRegion.UKNational = @.UKN)More info needed: Are the ticks mapped to the appropiate parameters like @.sw ? What do you pass if the ticks are not selected ? Probably Null ? because of doing an OR, you will get all values which habe in one of the filtered columns the value null then.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Help with Query #2

Hi. All

I am having trouble quering #2. No problem with #1(there is product & sub product code) & data was taken from 1 tble for #1.. Data is also avail on another table as well.

Cust has many product, but 1 cust_id.

Need your help guys..

JK

1) Select customers with Free Checking, Gold Checking,ect... with combined balance $11,000 - $25,000.

2) Select all customers who have an IXI(Total Relationship Balance) value at least twice the amount of their combined balance of $11K - $25. For example, if a customer has $11,000 in combined balance, their IXI must be $22,000+.

3) How many customers fall into this bucket?

How about offering us the table DDL, your attempted queries so far, and perhaps some sample data in the form of INSERT statements.

|||

Hi Arnie,

This is what I did for step one:

step #1
select cust_id, acct_num, sys_prod_cd, sys_sub_prod_cd, cur_book_bal into lp.dbo.RTP_JK_IXIDepBal_19939
from RTP_Cust_Data_Final
where sys_prod_cd+sys_sub_prod_cd in ('CDA43','CDA49','DDAU7',
'RSV20','DDA86') and cur_book_bal >= 11000and cur_book_bal <= 25000

Step1.1

select count(distinct cust_id), sum(cur_book_bal)from RTP_temp_JK_IXIDepBal_19939
group by cust_id
order by Cust_id

Step 2 is where I am having problem. What I am going to do is to run them individually to get a waterfall count of potential cust count.

Thanks

JK(john)

|||

With my interpretation of #1, I don't think your query will give the correct result, but then I don't know the definitions of your fields.

So, here's a rewrite of #1 and guess at #2 since i didn't know where the IXI-relationship-balance comes from.

Code Snippet

--#1

select cust_id,

sum(cur_book_bal)as combined_balance

from RTP_Cust_Data_Final

where sys_prod_cd+sys_sub_prod_cd in('CDA43','CDA49','DDAU7','RSV20','DDA86')

groupby cust_id

havingsum(cur_book_bal)between 11000 and 25000

--#2

select cust_id,

sum(cur_book_bal)as combined_balance,

sum(ixi_balance)as ixi_balance

from RTP_Cust_Data_Final

groupby cust_id

havingsum(cur_book_bal)between 11000 and 25000

andsum(ixi_balance)=>(sum(cur_book_bal)*2)

|||

Hi Dale.J

IXI-relationship Balance mean the total sum bal of All (each) account cur_bal the cust has with us.

(sum(cur_book_bal) as IXI_balance),

then if that balance fall B/ween 11000 & 25000

multiply those bal by 2 & fetch those cust_id that fall within that bal, as the result set.

Im trying out ur query as well & twek it as I go. But this is what I am trying to get.

Thank U.

JK

|||

You're welcome.

If you need more help with the tweaking, just post a little more DDL, etc. and some sample (insert) data.

BTW, does the RTP prefix stand for Research Triangle Park?

|||

RTP=Name of project team.

JK

|||

Ah, yes.

I thought maybe you were here in the Triangle.

Help with NOT EXISTS query

I am having trouble with what will surely be a simple query for you experts.

I have 2 tables with inventory data.
IMITMIDX contains the master item info
IMINVLOC contains location specific data such as quantity on hand at that
location.

These tables have 2 commons fields, ITEM_NO and LOC

I need to search the IMINVLOC table for any records where ITEM_NO and LOC do
not match that in the IMITMIDX table.

The following query give me zero records even though I can manually find
some records:

SELECT *
FROM IMINVLOC_SQL INNER JOIN
IMITMIDX_SQL ON IMITMIDX_SQL.item_no = IMINVLOC_SQL.item_no
where not exists (select loc from iminvloc_sql where IMITMIDX_SQL.loc =
IMINVLOC_SQL.loc)

Any ideas?
Thanks.Hi

It is better to post DDL ( CREATE TABLE statements etc...) and example data
( as Insert statements ) than a description of pseudo code.

Either

SELECT L.*
FROM IMINVLOC L
WHERE NOT EXISTS ( SELECT * FROM IMITMIDX M WHERE M.ITEM_NO = L.ITEM_NO
AND M.LOC = L.LOC )

OR

SELECT L.*
FROM IMINVLOC L LEFT JOIN IMITMIDX M ON M.ITEM_NO = L.ITEM_NO
AND M.LOC = L.LOC
WHERE M.ITEM_NO IS NULL AND M.LOC IS NULL

John

"RDRaider" <rdraider@.sbcglobal.net> wrote in message
news:AQXEc.7015$qG.6055@.newssvr27.news.prodigy.com ...
> I am having trouble with what will surely be a simple query for you
experts.
> I have 2 tables with inventory data.
> IMITMIDX contains the master item info
> IMINVLOC contains location specific data such as quantity on hand at that
> location.
> These tables have 2 commons fields, ITEM_NO and LOC
> I need to search the IMINVLOC table for any records where ITEM_NO and LOC
do
> not match that in the IMITMIDX table.
> The following query give me zero records even though I can manually find
> some records:
> SELECT *
> FROM IMINVLOC_SQL INNER JOIN
> IMITMIDX_SQL ON IMITMIDX_SQL.item_no = IMINVLOC_SQL.item_no
> where not exists (select loc from iminvloc_sql where IMITMIDX_SQL.loc =
> IMINVLOC_SQL.loc)
>
> Any ideas?
> Thanks.|||Thank you very much for your help. I'm getting closer, let me try to state
my problem more clearly.
Every record in IMITMIDX must have a matching record in IMINVLOC with the
same ITEM_NO and LOC. IMINVLOC can have multiple records for the same item
in IMITMIDX (each location has a record). The query you provided gives me
records with item_no and loc that don't match that in imitmidx.

Example data:
Table: IMITMIDX
Item_no Loc
BRONZE SD

Table: IMINVLOC
Item_no Loc
BRONZE GSN
BRONZE RMN
BRONZE NS
BRONZE SA
BRONZE SD
BRONZE VIS
BRONZE WSD
BRONZE RAW

Your query returns the following: (record with LOC = SD is not
returned)
BRONZE GSN
BRONZE RMN
BRONZE NS
BRONZE SA
BRONZE VIS
BRONZE WSD
BRONZE RAW

I need a query that will tell me when the IMINVLOC table does not contain
the same Item_no/Loc combination as the Imitmidx table.

Thanks again for the help.

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:_rYEc.1174$rR4.10041557@.news-text.cableinet.net...
> Hi
> It is better to post DDL ( CREATE TABLE statements etc...) and example
data
> ( as Insert statements ) than a description of pseudo code.
> Either
> SELECT L.*
> FROM IMINVLOC L
> WHERE NOT EXISTS ( SELECT * FROM IMITMIDX M WHERE M.ITEM_NO = L.ITEM_NO
> AND M.LOC = L.LOC )
> OR
> SELECT L.*
> FROM IMINVLOC L LEFT JOIN IMITMIDX M ON M.ITEM_NO = L.ITEM_NO
> AND M.LOC = L.LOC
> WHERE M.ITEM_NO IS NULL AND M.LOC IS NULL
> John
> "RDRaider" <rdraider@.sbcglobal.net> wrote in message
> news:AQXEc.7015$qG.6055@.newssvr27.news.prodigy.com ...
> > I am having trouble with what will surely be a simple query for you
> experts.
> > I have 2 tables with inventory data.
> > IMITMIDX contains the master item info
> > IMINVLOC contains location specific data such as quantity on hand at
that
> > location.
> > These tables have 2 commons fields, ITEM_NO and LOC
> > I need to search the IMINVLOC table for any records where ITEM_NO and
LOC
> do
> > not match that in the IMITMIDX table.
> > The following query give me zero records even though I can manually find
> > some records:
> > SELECT *
> > FROM IMINVLOC_SQL INNER JOIN
> > IMITMIDX_SQL ON IMITMIDX_SQL.item_no = IMINVLOC_SQL.item_no
> > where not exists (select loc from iminvloc_sql where IMITMIDX_SQL.loc =
> > IMINVLOC_SQL.loc)
> > Any ideas?
> > Thanks.|||Hi

Maybe this way around?

SELECT M.*
FROM IMITMIDX M
WHERE NOT EXISTS ( SELECT * FROM IMINVLOC L WHERE M.ITEM_NO = L.ITEM_NO
AND M.LOC = L.LOC )

John

"RDRaider" <rdraider@.sbcglobal.net> wrote in message
news:EnZEc.7044$Ul1.576@.newssvr27.news.prodigy.com ...
> Thank you very much for your help. I'm getting closer, let me try to
state
> my problem more clearly.
> Every record in IMITMIDX must have a matching record in IMINVLOC with the
> same ITEM_NO and LOC. IMINVLOC can have multiple records for the same
item
> in IMITMIDX (each location has a record). The query you provided gives me
> records with item_no and loc that don't match that in imitmidx.
> Example data:
> Table: IMITMIDX
> Item_no Loc
> BRONZE SD
> Table: IMINVLOC
> Item_no Loc
> BRONZE GSN
> BRONZE RMN
> BRONZE NS
> BRONZE SA
> BRONZE SD
> BRONZE VIS
> BRONZE WSD
> BRONZE RAW
>
> Your query returns the following: (record with LOC = SD is not
> returned)
> BRONZE GSN
> BRONZE RMN
> BRONZE NS
> BRONZE SA
> BRONZE VIS
> BRONZE WSD
> BRONZE RAW
>
> I need a query that will tell me when the IMINVLOC table does not contain
> the same Item_no/Loc combination as the Imitmidx table.
> Thanks again for the help.
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:_rYEc.1174$rR4.10041557@.news-text.cableinet.net...
> > Hi
> > It is better to post DDL ( CREATE TABLE statements etc...) and example
> data
> > ( as Insert statements ) than a description of pseudo code.
> > Either
> > SELECT L.*
> > FROM IMINVLOC L
> > WHERE NOT EXISTS ( SELECT * FROM IMITMIDX M WHERE M.ITEM_NO = L.ITEM_NO
> > AND M.LOC = L.LOC )
> > OR
> > SELECT L.*
> > FROM IMINVLOC L LEFT JOIN IMITMIDX M ON M.ITEM_NO = L.ITEM_NO
> > AND M.LOC = L.LOC
> > WHERE M.ITEM_NO IS NULL AND M.LOC IS NULL
> > John
> > "RDRaider" <rdraider@.sbcglobal.net> wrote in message
> > news:AQXEc.7015$qG.6055@.newssvr27.news.prodigy.com ...
> > > I am having trouble with what will surely be a simple query for you
> > experts.
> > > > I have 2 tables with inventory data.
> > > IMITMIDX contains the master item info
> > > IMINVLOC contains location specific data such as quantity on hand at
> that
> > > location.
> > > > These tables have 2 commons fields, ITEM_NO and LOC
> > > > I need to search the IMINVLOC table for any records where ITEM_NO and
> LOC
> > do
> > > not match that in the IMITMIDX table.
> > > > The following query give me zero records even though I can manually
find
> > > some records:
> > > > SELECT *
> > > FROM IMINVLOC_SQL INNER JOIN
> > > IMITMIDX_SQL ON IMITMIDX_SQL.item_no = IMINVLOC_SQL.item_no
> > > where not exists (select loc from iminvloc_sql where IMITMIDX_SQL.loc
=
> > > IMINVLOC_SQL.loc)
> > > > > Any ideas?
> > > Thanks.
> >|||Thank you, that works!

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:iwZEc.1256$Dv5.10834047@.news-text.cableinet.net...
> Hi
> Maybe this way around?
> SELECT M.*
> FROM IMITMIDX M
> WHERE NOT EXISTS ( SELECT * FROM IMINVLOC L WHERE M.ITEM_NO = L.ITEM_NO
> AND M.LOC = L.LOC )
> John
> "RDRaider" <rdraider@.sbcglobal.net> wrote in message
> news:EnZEc.7044$Ul1.576@.newssvr27.news.prodigy.com ...
> > Thank you very much for your help. I'm getting closer, let me try to
> state
> > my problem more clearly.
> > Every record in IMITMIDX must have a matching record in IMINVLOC with
the
> > same ITEM_NO and LOC. IMINVLOC can have multiple records for the same
> item
> > in IMITMIDX (each location has a record). The query you provided gives
me
> > records with item_no and loc that don't match that in imitmidx.
> > Example data:
> > Table: IMITMIDX
> > Item_no Loc
> > BRONZE SD
> > Table: IMINVLOC
> > Item_no Loc
> > BRONZE GSN
> > BRONZE RMN
> > BRONZE NS
> > BRONZE SA
> > BRONZE SD
> > BRONZE VIS
> > BRONZE WSD
> > BRONZE RAW
> > Your query returns the following: (record with LOC = SD is not
> > returned)
> > BRONZE GSN
> > BRONZE RMN
> > BRONZE NS
> > BRONZE SA
> > BRONZE VIS
> > BRONZE WSD
> > BRONZE RAW
> > I need a query that will tell me when the IMINVLOC table does not
contain
> > the same Item_no/Loc combination as the Imitmidx table.
> > Thanks again for the help.
> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > news:_rYEc.1174$rR4.10041557@.news-text.cableinet.net...
> > > Hi
> > > > It is better to post DDL ( CREATE TABLE statements etc...) and example
> > data
> > > ( as Insert statements ) than a description of pseudo code.
> > > > Either
> > > > SELECT L.*
> > > FROM IMINVLOC L
> > > WHERE NOT EXISTS ( SELECT * FROM IMITMIDX M WHERE M.ITEM_NO =
L.ITEM_NO
> > > AND M.LOC = L.LOC )
> > > > OR
> > > > SELECT L.*
> > > FROM IMINVLOC L LEFT JOIN IMITMIDX M ON M.ITEM_NO = L.ITEM_NO
> > > AND M.LOC = L.LOC
> > > WHERE M.ITEM_NO IS NULL AND M.LOC IS NULL
> > > > John
> > > > "RDRaider" <rdraider@.sbcglobal.net> wrote in message
> > > news:AQXEc.7015$qG.6055@.newssvr27.news.prodigy.com ...
> > > > I am having trouble with what will surely be a simple query for you
> > > experts.
> > > > > > I have 2 tables with inventory data.
> > > > IMITMIDX contains the master item info
> > > > IMINVLOC contains location specific data such as quantity on hand at
> > that
> > > > location.
> > > > > > These tables have 2 commons fields, ITEM_NO and LOC
> > > > > > I need to search the IMINVLOC table for any records where ITEM_NO
and
> > LOC
> > > do
> > > > not match that in the IMITMIDX table.
> > > > > > The following query give me zero records even though I can manually
> find
> > > > some records:
> > > > > > SELECT *
> > > > FROM IMINVLOC_SQL INNER JOIN
> > > > IMITMIDX_SQL ON IMITMIDX_SQL.item_no = IMINVLOC_SQL.item_no
> > > > where not exists (select loc from iminvloc_sql where
IMITMIDX_SQL.loc
> =
> > > > IMINVLOC_SQL.loc)
> > > > > > > > Any ideas?
> > > > Thanks.
> > > > > > >|||> I have 2 tables with inventory data. <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

>> IMITMIDX contains the master item info;
IMINVLOC contains location specific data such as quantity on hand at
that
location. These tables have 2 common fields [sic], ITEM_NO and LOC <<

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. I would assume from this
narrative that IMITMIDX should not have a location at all, but only
information about the items -- UPC, size, weight, color, etc. and that
it would be referenced by the
IMINVLOC table for the quantity at each location (warehouses?,
stores?).

>> I need to search the IMINVLOC table for any records [sic] where
ITEM_NO and LOC do not match that in the IMITMIDX table. <<

>> The following query give me zero records [sic]though I can manually
find some records [sic] <<

Why did you put "_SQL" postfixes on the names in the query? Never use
SELECT * in production code; I have no choice because I have no DDL:

SELECT I1.*, L1.*
FROM Imitmidx AS I1
LERFT OUTER JOIN
IminvLoc AS L1
ON I1.item_no = L1.item_no
AND I1.loc = L1.loc;

This will give you NULLs for the unmatched rows.

Never use uppercase letters for names (it is unreadable; that is why
newspapers and books are mixed case). Get a copy of ISO-11179 and
starting using the standards for data element names, too.

Help With Nested Query

I am having trouble with the following query.

Important Tables:
Product (table of products)
--ProductID
--ProductName

ProductCategories (Associates a Product with one or more categories)
--ProductID
--CategoryID

Category (table of categories that a product may fall under)
--CategoryID
--CategoryName

Information:

Basically I have a product that falls into two categories. Therefore there are two records in the ProcuctCategories Table. I am trying to create a query that will find all products that are in categories 1 & 2.

Attempted Solution:
SELECT * FROM Product
WHERE (ProductID IN (SELECT CategoryID FROM ProductCategories WHERE CategoryID =1))
AND
(ProductID IN (SELECT CategoryID FROM ProductCategories WHERE CategoryID =2))

This returned zero records though it should have returned the product that is in categories 1&2.

I would appreciate any help available.

Thank you,
-PatrickI am trying to create a query that will find all products that are in categories 1 & 2.do a regular many-to-many join, but use GROUP BY on the product, and HAVING to retain only those products which were in more than one category
select ProductName
from Category C
inner
join ProductCategories PC
on C.CategoryID = PC.CategoryID
inner
join Product P
on PC.ProductID = P.ProductID
where C.CategoryID in (1,2)
group
by ProductName
having count(*) > 1|||You are going to kick yourself, but the reason your query failed to return records is because you were trying to compare outer "ProductID"s to inner "CategoryID"s.

...WHERE (ProductID IN (SELECT CategoryID...???

You can rewrite your query more simply like this:

select Product.*
from Product
inner join ProductCategories Cat1 on Product.ProductID = Cat1.ProductID
inner join ProductCategories Cat2 on Product.ProductID = Cat2.ProductID
where Cat1.CategoryID = 1 and Cat2.CategoryID = 2

Use the DISTINCT keywork if the query returns multiple records.

Help with measure aggregation functions

Hi, I will rewrite my question.
I'm having trouble to show averages of a measure in a cube, where the normal
aggregation function for a measure is SUM.
I see no AVG aggregation function for measures (I see Min, Max, Count,
Distinct Count and SUM).
If I hide the measure (cost), and create a calculated member based on that
measure, ie Avg(cost), I have te problem of how to average it, since the
cube has two dimensions in the row axis, like:
Time
Product |
Customer | avg of cost
If I use avg(nonemptycrossjoin(product.currentmember.childr en,
customer.currentmember.children), measures.cost) I get the same average for
every cell in the cube, what's not corrrect...
Sorry to bother you all, but this thing is becoming a nightmare.
Hope you can help
Michael Prendergast
Averages are usually handled by summing and counting...and then
dividing the sub by the count in a calculated member.
MPS wrote:
> Hi, I will rewrite my question.
> I'm having trouble to show averages of a measure in a cube, where the
normal
> aggregation function for a measure is SUM.
> I see no AVG aggregation function for measures (I see Min, Max,
Count,
> Distinct Count and SUM).
> If I hide the measure (cost), and create a calculated member based on
that
> measure, ie Avg(cost), I have te problem of how to average it, since
the
> cube has two dimensions in the row axis, like:
> Time
> Product |
> Customer | avg of cost
> If I use avg(nonemptycrossjoin(product.currentmember.childr en,
> customer.currentmember.children), measures.cost) I get the same
average for
> every cell in the cube, what's not corrrect...
> Sorry to bother you all, but this thing is becoming a nightmare.
> Hope you can help
> Michael Prendergast
|||Sometimes, getting back to basics gets the job done
Thank yo very much, problem solved
Michael
"OLAPMonkey" <jjanke@.spss.com> escribi en el mensaje
news:1112202692.231562.128020@.g14g2000cwa.googlegr oups.com...
> Averages are usually handled by summing and counting...and then
> dividing the sub by the count in a calculated member.
> MPS wrote:
> normal
> Count,
> that
> the
> average for
>

Help with measure aggregation functions

Hi, I will rewrite my question.
I'm having trouble to show averages of a measure in a cube, where the normal
aggregation function for a measure is SUM.
I see no AVG aggregation function for measures (I see Min, Max, Count,
Distinct Count and SUM).
If I hide the measure (cost), and create a calculated member based on that
measure, ie Avg(cost), I have te problem of how to average it, since the
cube has two dimensions in the row axis, like:
Time
---
Product |
Customer | avg of cost
If I use avg(nonemptycrossjoin(product.currentmember.children,
customer.currentmember.children), measures.cost) I get the same average for
every cell in the cube, what's not corrrect...
Sorry to bother you all, but this thing is becoming a nightmare.
Hope you can help
Michael PrendergastAverages are usually handled by summing and counting...and then
dividing the sub by the count in a calculated member.
MPS wrote:
> Hi, I will rewrite my question.
> I'm having trouble to show averages of a measure in a cube, where the
normal
> aggregation function for a measure is SUM.
> I see no AVG aggregation function for measures (I see Min, Max,
Count,
> Distinct Count and SUM).
> If I hide the measure (cost), and create a calculated member based on
that
> measure, ie Avg(cost), I have te problem of how to average it, since
the
> cube has two dimensions in the row axis, like:
> Time
> ---
> Product |
> Customer | avg of cost
> If I use avg(nonemptycrossjoin(product.currentmember.children,
> customer.currentmember.children), measures.cost) I get the same
average for
> every cell in the cube, what's not corrrect...
> Sorry to bother you all, but this thing is becoming a nightmare.
> Hope you can help
> Michael Prendergast|||Sometimes, getting back to basics gets the job done
Thank yo very much, problem solved
Michael
"OLAPMonkey" <jjanke@.spss.com> escribi en el mensaje
news:1112202692.231562.128020@.g14g2000cwa.googlegroups.com...
> Averages are usually handled by summing and counting...and then
> dividing the sub by the count in a calculated member.
> MPS wrote:
> normal
> Count,
> that
> the
> average for
>

Help with Matrix

Ok, I am having a little trouble figuring out if this is possible...

At first it looked easy (It always does), but as I started messing with matrix groups, it seems that i cant be done in 1 matrix.

Here is the display I want:

Value 1 Value 2 Total

Period 1 Period 2 Var % Period 1 Period 2 Var % Period 1 Period 2 Var %

Row

Row

Row

Row

I was thinking of having 2 groups, and then add a column to the 2nd group to calculate the variance column, and have that total. I cannot seem to get it to do that.

This is the first time I have really pushed the matrix for more than the basics.

Any ideas?

Thanks!!

you can have multiple column groups

e.g. sales region, sales person

but these will always relate to the same data

e.g. amount of sales in $

so if you want to report different things

e.g. percentages, sales, volts, centigrage, apples, bananas

you need to create a pseudo grouping

e.g. group on "1"

and then hide the grouping's borders/cells

and then you will need to use the inscope function to determine which column the report is being against

--

so in your example you will have the initial column grouping "period" and then another grouping called "variance"

"period" will be grouped on the actual period, but "variance" will be grouped on "1"

then in the data cell you just put

carnt remember the syntax but its something like....

=iif( inscope("matrixname_variance", myvariancedata, myperioddata)

you can also do this for rows

|||

Great, thanks! I now have the variance column... However I am assuming that I cannot use the calculations within the matrix to calculate the variance, is this correct? I see no way of referencing Period 1 and Period 2 from the Variance cell.

Thanks again!

BobP

|||

there should only be one cell in the 'data' area

this is the contents of my only 'data' cell in a particular report

=

IIF(

InScope("TradeCount"),

IIF(InScope("NotionalToBaseCurrency"),

switch(

Parameters!RevenueDisplayType.Value = 1,cdbl(SUM(Fields!RevenueAmountToBaseCurrency.Value)),

Parameters!RevenueDisplayType.Value = 2,cdbl(SUM(Fields!RevenueBPA.Value*Fields!RevenueAmountToBaseCurrency.Value)/ iif(Sum(Fields!RevenueAmountToBaseCurrency.Value)=0, 1, Sum(Fields!RevenueAmountToBaseCurrency.Value))),

Parameters!RevenueDisplayType.Value = 3,cdbl(Sum(Fields!RevenuePIPS.Value*10000*Fields!RevenueAmountToBaseCurrency.Value)/ iif(Sum(Fields!RevenueAmountToBaseCurrency.Value)=0, 1, Sum(Fields!RevenueAmountToBaseCurrency.Value))),

true=true,cdbl(Fields!RevenueAmountToBaseCurrency.Value)

)

,

iif(inscope("ParentGroup")

,cdbl(SUM(Fields!NotionalToBaseCurrency.Value)/countdistinct(Fields!RevenueTypeID.Value))

,cdbl(Code.CalculateSum(Fields!DealID.Value,Fields!NotionalToBaseCurrency.Value))

)

)

,

cdbl(CountDistinct(Fields!DealID.Value))

)

Where "parent group" is a row grouping and the top 2 are column groupings

so....

trade count shows as a single column with a numeric integer

whereas the "notional to base currency" displays some other data

in your scenario, trade count = variance, n2bc = period

sorry it is a bit of complex expression but i don't have any others to hand

|||

Ok, here is what I wound up doing.

First of all, thanks for the great ideas above! They were invaluable.

I created the matrix with 1 column as indicated, then I created 3 groups.

1. International/Domestic/Total

2. 1 (Called Variance)

3. Period 1/Period2

Then I added a sub total to the Variance group.

In the data cell, I used the InScope to display either the sum(data) or 1-Sum(first(data))/Sum(Last(data)), which gave me the variance %.

In the SQL, I had 2 SQL statements unioned. The first one grouped on International/Domestic and the Period (using a case statement on the data parameters passed in)

This worked like a charm.

Thanks again for all of the help and the direction!!

BobP