Question to index (SQL 2000)

I have got a question concerning multiple indices on one table.
We have an table where two colums combined build a unique index. Does this
index also help to speed up links where only one of the columns is involved,
or does it make senses to define a separate index for that column?
Example:
Fields a and b build an unique index.
Field b is involved in links to other tables.
Index on field b feasible?
TIA,
NorbertNorbert Meiss wrote:
> I have got a question concerning multiple indices on one table.
> We have an table where two colums combined build a unique index. Does this
> index also help to speed up links where only one of the columns is involve
d,
> or does it make senses to define a separate index for that column?
> Example:
> Fields a and b build an unique index.
> Field b is involved in links to other tables.
> Index on field b feasible?
> TIA,
> Norbert
The combined index on a+b will speed up lookups on field a, but not
necessarily on b. I say "not necessarily" because any lookup on b will
perform a scan, but that scan can now be done on the index instead of
the table.
It is certainly feasible to create a second index that uses b+a.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Norbert Meiss" <NorbertMeiss@.discussions.microsoft.com> wrote in message
news:99ACEA45-2216-4B07-B5FF-0C4C82A028A4@.microsoft.com...
> I have got a question concerning multiple indices on one table.
> We have an table where two colums combined build a unique index. Does this
> index also help to speed up links where only one of the columns is
involved,
> or does it make senses to define a separate index for that column?
> Example:
> Fields a and b build an unique index.
> Field b is involved in links to other tables.
> Index on field b feasible?
Yes and no.
If you can do an index on B+A that should cover both B and A.
(though if A has a higher exclusivity than B, A+B and then a separate index
for B MIGHT be better.)

> TIA,
> Norbert

Question to index (SQL 2000)

I have got a question concerning multiple indices on one table.
We have an table where two colums combined build a unique index. Does this
index also help to speed up links where only one of the columns is involved,
or does it make senses to define a separate index for that column?
Example:
Fields a and b build an unique index.
Field b is involved in links to other tables.
Index on field b feasible?
TIA,
NorbertNorbert Meiss wrote:
> I have got a question concerning multiple indices on one table.
> We have an table where two colums combined build a unique index. Does this
> index also help to speed up links where only one of the columns is involved,
> or does it make senses to define a separate index for that column?
> Example:
> Fields a and b build an unique index.
> Field b is involved in links to other tables.
> Index on field b feasible?
> TIA,
> Norbert
The combined index on a+b will speed up lookups on field a, but not
necessarily on b. I say "not necessarily" because any lookup on b will
perform a scan, but that scan can now be done on the index instead of
the table.
It is certainly feasible to create a second index that uses b+a.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Norbert Meiss" <NorbertMeiss@.discussions.microsoft.com> wrote in message
news:99ACEA45-2216-4B07-B5FF-0C4C82A028A4@.microsoft.com...
> I have got a question concerning multiple indices on one table.
> We have an table where two colums combined build a unique index. Does this
> index also help to speed up links where only one of the columns is
involved,
> or does it make senses to define a separate index for that column?
> Example:
> Fields a and b build an unique index.
> Field b is involved in links to other tables.
> Index on field b feasible?
Yes and no.
If you can do an index on B+A that should cover both B and A.
(though if A has a higher exclusivity than B, A+B and then a separate index
for B MIGHT be better.)
> TIA,
> Norbert

Question regarding use of a join to the same table

I have a table, BOOK, which uses BU and Asset and BOOK as a combined unique
identifier. A BU/Asset combination can have up to 4 rows, with different
BOOK values (CORP, FED, AMT - all of which are required, and LOCAL which is
situational).
Due to bad data entry, some of the Assets were not given an AMT row. I have
to identify those BU/Asset combinations.
I know I need to do a join back to the BOOK table, but I can't seem to get
the right statement. Here is my SQL:
select A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
from PS_BOOK A
right outer join PS_BOOK B
on A.BUSINESS_UNIT = B.BUSINESS_UNIT and
A.ASSET_ID = B.ASSET_ID
where B.BOOK <> 'AMT'
order by A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
This gives me a double lisitng of rows (of course) and I can visually scan
for those missing 'AMT', but that's way too clunky. Where am I shooting
myself in the foot? What is the correct statement to use to get just a list
of the BU/Asset combinations that are missing 'AMT'?
TIA,
jej1216I tried a simpler stetment:
select A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
from PS_BOOK A
where EXISTS (select 'x'
from PS_BOOK B
where B.BOOK <> 'AMT')
order by A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
But I still get both the BU/Asset combination that has 'AMT' as well as the
combination that does not have 'AMT'.
- jej1216
"Joe" wrote:
> I have a table, BOOK, which uses BU and Asset and BOOK as a combined unique
> identifier. A BU/Asset combination can have up to 4 rows, with different
> BOOK values (CORP, FED, AMT - all of which are required, and LOCAL which is
> situational).
> Due to bad data entry, some of the Assets were not given an AMT row. I have
> to identify those BU/Asset combinations.
> I know I need to do a join back to the BOOK table, but I can't seem to get
> the right statement. Here is my SQL:
> select A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
> from PS_BOOK A
> right outer join PS_BOOK B
> on A.BUSINESS_UNIT = B.BUSINESS_UNIT and
> A.ASSET_ID = B.ASSET_ID
> where B.BOOK <> 'AMT'
> order by A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
> This gives me a double lisitng of rows (of course) and I can visually scan
> for those missing 'AMT', but that's way too clunky. Where am I shooting
> myself in the foot? What is the correct statement to use to get just a list
> of the BU/Asset combinations that are missing 'AMT'?
> TIA,
> jej1216
>|||On Tue, 16 Oct 2007 08:42:02 -0700, Joe wrote:
>I tried a simpler stetment:
>select A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
>from PS_BOOK A
>where EXISTS (select 'x'
> from PS_BOOK B
> where B.BOOK <> 'AMT')
>order by A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
>But I still get both the BU/Asset combination that has 'AMT' as well as the
>combination that does not have 'AMT'.
Hi Joe,
I think you want this one:
SELECT a.BusinessUnit, a.AssetID, a.Book
FROM PS_Book AS a
WHERE NOT EXISTS
(SELECT *
FROM PS_Book AS b
WHERE b.BusienssUnit = a.BusinessUnit
AND b.AssetID = a.AssetID
AND b.Book <> 'AMT');
If that is not it, then see www.aspfaq.com/5006 to find the information
you need to post in order to make it possible for us to help you.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Question on joins

I have 4 tbls , TBL1, TBLB, TBLC and TBLD... All these 4 tbls have an accoutnnumber field, which should be unique between all these 4 btls.

How, do I check to see if any acctnumbers are present in more than 1 of these 4 tbls?

I used an inner join , but that ONLY Looks for accts, that are repeated in ALL These 4 tbls. Heres the qry I used:

What I need to see is if a acct is repeated Between ANY Of these tbls, it should return that acct as a duplicate.

Pl advise how I can do that:

select * FROM TblA a
inner join TblB cr
on cr.acctno=a.acctno
inner JOIN TblC fc
on fc.acctno=a.acctno
inner join TblD r
on r.acct#=a.acctno

Code Snippet

select AcctNo,count(*)

from (

select AcctNo, 'A' as Table from TableA

union all

select AcctNo, 'B' as Table from TableB

union all

select AcctNo, 'C' as Table from TableC

union all

select AcctNo, 'D' as Table from TableD

) as [SubQuery]

group by AcctNo

having count(*) > 1

|||

May be this..

Code Snippet

Create Table #tab1 (

[id] int

);

Insert Into #tab1 Values('1');

Insert Into #tab1 Values('3');

Insert Into #tab1 Values('4');

Create Table #tab2 (

[id] int

);

Insert Into #tab2 Values('1');

Insert Into #tab2 Values('3');

Create Table #tab3 (

[id] int

);

Insert Into #tab3 Values('4');

Insert Into #tab3 Values('7');

Insert Into #tab3 Values('8');

Insert Into #tab3 Values('9');

Create Table #tab4 (

[id] int

);

Insert Into #tab4 Values('1');

Insert Into #tab4 Values('5');

Insert Into #tab4 Values('9');

Code Snippet

Select

Id,

Case When DuplicateAt & 1 = 1 Then 'Y' Else '' End [With tab1],

Case When DuplicateAt & 2 = 2 Then 'Y' Else '' End [With tab2],

Case When DuplicateAt & 4 = 4 Then 'Y' Else '' End [With tab3],

Case When DuplicateAt & 8 = 8 Then 'Y' Else '' End [With tab4]

From

(

Select

Id,

Sum(TabId) DuplicateAt

from

(

Select Id,1 TabId from #tab1

union all

Select Id,2 from #tab2

union all

Select Id,4 from #tab3

union all

Select Id,8 from #tab4

) as data

group By Id

having Count(Id) >1

) as data

|||

Hi,

Whenever I am faced with little problems like these, I tend to go back to absolute basics. If you think of each one of your tables as a data set (not a table).

Even better, imagine your have 4 quarter decks of playing cards (13 cards each). To answer the question of which playing cards are duplicated in any of the 4 decks. You would have to mix the cards together and group them and then look for duplicates. If you can think of a better way to do this in real life then let me know!

Luckily, the SQL Server engine does most of the work for us. That's the beauty of set-based logic. So to implement a solution we know we need to combine our 4 sets of playing cards. T-SQL gives us the UNION ALL option to combine similar data sets.

set A

UNION ALL

set B

UNION ALL

set C

UNION ALL

set D

To locate the duplicates in this example, we would group by the card number. If we find more than one then we know we have a duplicate.

Manivannan query follows a similar logic, only his code eliminates unique rows by using the having clause to filter on the grouped data. He also introduces a CASE statement for presenting the result in a more meaningful way but his logic is essentially the same. The bitwise operator used in the case statement is useful here to mark each of your tables. This is useful to answer the question of which table contained the duplicate

Hope this helps.

Question on how to display data in this unique situation.....

Hi guys,

Man I do come up with strange scenarios, but that is the joy of working in software field right ? ;-)
First off, thanks to anyone taking their time to read this, and Ihope this post paints a clearer picture better than my previous posts.
I have an old stored procedure (which I didn't create) that produces a dataset of the following:-

((All names and values had been changed to protect confidentiality))

region agent_type mailpackage1 mailpackage2 mailpackage3
New York Agenttype1 2000 2300 0
New York Agenttype2 0 0 5
New York Agenttype3 150 2 4000
Central Agenttype2 1234 5678 9
Central Agenttype4 435 1 0
MidWest Agenttype1 555 0 0
West Agenttype1 1 45 0
West Agenttype2 0 2 3

A little bit of explanation:-
Each region can have any type of agents, specified by the number to distinguish different agent types. these agent types mail specific packages to their customers depending on the situation and what the customers asked for. the numbers in each mail package indicate the total that had been sent out by a particular type of an agent. So in this case we are not dealing wtih how many agents are there, just how many packages had been sent out by a specific type of an agent in a region.

Previously the report was produced like you would see in the above dataset. However the client would want it the other way around. Though I didn't show it here, there are plenty of other packages but I am picking three for clarity sake.

So the "new" Report would have to look something like this.

Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 2000 0 150 0
Package2 2300 0 2 0
Package3 0 5 4000 0

break page

Region: Central
AgentType1 AgentType2 AgentType3 AgentType4
Package1 0 1234 0 435
Package2 0 5678 0 1
Package3 0 9 0 0

- break Page -- and so on

I had created a table in the RS that looked like the above with expressions written into the each cell that holds a value. The expression is

=IIF(Fields!agent_type = "AgentType1", mailpackage1.Value, Cint(0)) in the first row, first column of the table.
=IIF(Fields!agent_type = "AgentType1", mailpackage2.Value, Cint(0)) in the second row, first column of the table.
=IIF(Fields!agent_type = "AgentType1", mailpackage3.Value, Cint(0)) in the third row, First column of the table.
And so on....... alternating between agent_type and mailpackage for each cell.

Grouping1: Group by Region, insert page after each group.

What happened was the following:- ((I am putting the first region, because it is also happening for the other regions too)

Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 2000 0 0 0
Package2 2300 0 0 0
Package3 0 0 0 0

break page
Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 0 0 0 0
Package2 0 0 0 0
Package3 0 5 0 0

break page

Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 0 0 150 0
Package2 0 0 2 0
Package3 0 0 4000 0

break page
(on a side note, this region didn't print out AgentType4 because there were no data associated with it)

The question is, is there anything else I could have done to prevent this ? as you can see, the data is correct and placed in their right cells but somehow, they won't join together. I got a feelin that it has something to do with the expression that I had put in each cell.

Can someone help or point me in the right direction ? This is really bothering me and I couldn't figure out why it was doing this. Couldnt find any links or maybe i am putting in the wrong keywords in the search. Thanks muchly !

Bernard Ong

May it be that yor "new" Report is a changed copy of the old one and you didn't remove the grouping by AgentType?|||

Hi there Markus.

Thanks for the reply.

Actually, The old report was html generated. I am basically creating the new report with the new requirements/design using SSRS 2000 now.

At first I thought ti was the agent type too. But when I checked my groupings again, it only has the region grouping.

I even recreated the whole table, start fresh again and made certain that it is only grouped by Region. Same problem...

Thanks for pointing out the agent type grouping though!

Bernard Ong

|||Well it certainly looks more like a Matrix than a Table layout. If you switch from the table to a matrix, you do not need all the expressions at all. If you do not want to change your report design, try to do it in the query instead then by joining a sub-query for each agent. HTH|||

Thanks for the reply BYU

I am looking up on how to do a matrix, but it seems that I still need a recursive or repetitive row, in this case from what I had seen in the dataset, would be the agent-type. Unless there was another way to do it ?

However, due to the many packages that an agent could mail out, (even in landscape) it would be way too long for the printer or even the preview to look.

Thanks for taking a look into this. I appreciate any more tips or hints with this scenario.

Sincerely,

Bernard Ong

|||

Hello all again,

I am trying to figure out how to use the matrix (never really use them before, always with tables) and I am a little frustrated on how to get it to work or even trying to understand it.
Any references or help which I can refer to, to get me going ? my so called "Matrix" looks like some daVinci code that needs to be broken so that it could be interpreted ! :)

Thanks !

Bernard Ong

|||

Hi,

Could you please check if your initial SQL query has a 'order by region,agenttype' in it ?

If you clients are not really concerned about the excel export of this report - you can consider having a table with a grouping - 'region' and a matrix inside the group displaying data for each region repetitively.

so say table 1

create a group1 header -based on 'region'

add another header row for this group

In this second header row insert a matrix

row field- Agent type

column field- mailpackage types

let me know if you need more explanation. however, this report cannot be exported to excel since export of nested data regions is not supported.

Thanks,

PB

|||

Hi PB

Thanks for the reply !
I am going to try your suggestion and see whether that works. I never thought of using a matrix inside a table, and that might just work.
To answer your question:- Yes the query has a group by region AND agent_Type.

Thanks to the post by BYU, I am currently trying to wrestle with Matrix ("the blue or red pill" :) ) and it is slowly doing what I "think" it is doing.
It is just confusing because the matrix controls work VERY differently with the table. I guess I have to "Add Row" in the cell instead of highlighting the whole row.

I am not sure about this, but with the way I am doing it right now, sounds similar to what you saying except without the header. is that right ?

Sincerely,
Bernard Ong

|||

Hi,

I was talking more about an ORDER BY clause after the Group BY.

select region,etc,cetc

GROUP BY region, agent type,etc

ORDER BY Region,Agent Type.

That probably will make your original table layout also work.

Good luck,

PB

|||

Sorry ORDER BY REgion , Package , Agent Type

|||

Hi PB,

Sorry, must have read the post wrongly. and yes, i do have an order by clause, but with the package in there, it won't work.

The way it is as the dataset is shown, package1, package2 and so on, are the names of the packages(mailpackages column), and the data associated with them are counts or totals.

region agent_type mailpackage1 mailpackage2 mailpackage3
New York Agenttype1 2000 2300 0
New York Agenttype2 0 0 5
New York Agenttype3 150 2 4000
Central Agenttype2 1234 5678 9
Central Agenttype4 435 1 0
MidWest Agenttype1 555 0 0
West Agenttype1 1 45 0
West Agenttype2 0 2 3

Only region and agent_type are string characters, so using the package name in the order by won't work.

Thanks though !

Bernard Ong

|||

Hi Bernard,

I'm still a little unsure of your problem ....could you please explain how the data is stored...I mean how do you get the mailpackage1,mailpackage2 in the stored procedure ....is this a column indicating the type/category of package ?

In that case even if it is a number field , you should order by that before agent type

say you have a table with following columns

region id, mail_package_count, agent_type, packageNo.

newyork, 200, 1 45

new york 500 2 45

new york 30 3 500

This indicates that 200 counts of package45 were delivered by agent 1 , 500 counts of package45 were delivered by agent2 .....

IS that how it is?

I think you may have to make a small change in your stored procedure itself...returning the dataset ordered by region and packaget instead of agent type.

Please ignore this post if I got your problem wrong and Sorry if I confused you even more...

Thanks,

PB

|||

Hi Pbala,

Yeah ! that is exactly how it is without the packageNo. I am sorry that it wasn't clear enough, but in my situation, the numbers represent the count of the packages delivered. I still need the agent type because the client wants to see how many packages were sent out by a particular agent type.

Such as an Elite Agent type, or a basic agent, and they would make a decision out of it. To quote, this is how it is going to look like.

region id, mail_package_1_count, mail_package_2_count, mail_package_3_count, agent_type

newyork, 200 123 456 1

new york 500 342 688 2

new york 30 0 0 3

Hope this makes more sense now.

Still working on the problem but I am getting somewhere though. Right now, with using the matrix, i am able to get the right totals and how it would look like on the report with a "specific" region.

however, when I tried to have the all regions, it just clumped everything into one matrix without showing the other regions. this meaning that i got the total for ALL regions in one page, instead of them breaking out in different pages with each region.

Is there an expression or something am I missing to do ?

Thanks.
Bernard Ong

|||

Hello all once again :)

Just an update on the situation here.
First off, want to thank Pbala, BYU, and Mark for helping out with this thread.
After much reading and trying to figure out the matrix, I got the report working.
I was confused with how to use the Matrix which in the end did more harm than good and finally got the hang of it.
It was all about the groupings and how the Matrix does not act like a table. I resolve this issue by doing the following groupings.

For the Row Groupings:
I only needed to use ONE row Grouping and I ended up putting alot of row groupings thinking that they acted like rows from the table.
Boy, was I dead wrong. I found out that you have to select a cell (Row Section specifically) in the matrix to add a row in the column that would extend to the end.
BY clicking on the row header, it only allows you to add a row grouping and not a row. Either way. I grouped only the region in the
Matrix Properties->Groupings. NExt I keep adding the amount of rows needed to represent a package and drag their fields into the Data section.

For the Column Groupings:
Essentially the same thing, BUT I put the field!agent_type in the column section. Next I entered the the agent_type grouping in the column
grouping of the matrix.

The trouble i was having was not understanding the difference between the Row Groupings and Rows. I keep adding the row groupings which ended up not printing
What i needed for the report. After realizing that problem, I did a bit more research, and figured out that only the cell of the matrix allows Rows.

After that, I put the Subtotal field and it works ! My report printed exactly like the scenario.

Couldn't have done this without this forum and without the ideas and suggestions presented by the posters of this thread ! Thanks alot !

However, I do have another question concerning about the matrix, Is it possible to have an actual total of everything at the bottom of the screen like how the Table Footer would have ? Or the Matrix is only limited to having a subtotal ? The reason why I ask this because I have a option called "All Regions" which should give me the totals of all the regions including each individual region printed out on every page. I achieved this by having a table at the bottom of the matrix to collect the totals. I made the table into a table footer so that it encapsulates everything, and using the Visibility feature, Turn it off if the user only selected a specific region, but turn it on if the user selected "All region". was wondering whether the matrix could also achieve this but in an easier way like how the table would ?

thanks !

Bernard Ong

Question on how to display data in this unique situation.....

Hi guys,

Man I do come up with strange scenarios, but that is the joy of working in software field right ? ;-)
First off, thanks to anyone taking their time to read this, and Ihope this post paints a clearer picture better than my previous posts.
I have an old stored procedure (which I didn't create) that produces a dataset of the following:-

((All names and values had been changed to protect confidentiality))

region agent_type mailpackage1 mailpackage2 mailpackage3
New York Agenttype1 2000 2300 0
New York Agenttype2 0 0 5
New York Agenttype3 150 2 4000
Central Agenttype2 1234 5678 9
Central Agenttype4 435 1 0
MidWest Agenttype1 555 0 0
West Agenttype1 1 45 0
West Agenttype2 0 2 3

A little bit of explanation:-
Each region can have any type of agents, specified by the number to distinguish different agent types. these agent types mail specific packages to their customers depending on the situation and what the customers asked for. the numbers in each mail package indicate the total that had been sent out by a particular type of an agent. So in this case we are not dealing wtih how many agents are there, just how many packages had been sent out by a specific type of an agent in a region.

Previously the report was produced like you would see in the above dataset. However the client would want it the other way around. Though I didn't show it here, there are plenty of other packages but I am picking three for clarity sake.

So the "new" Report would have to look something like this.

Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 2000 0 150 0
Package2 2300 0 2 0
Package3 0 5 4000 0

break page

Region: Central
AgentType1 AgentType2 AgentType3 AgentType4
Package1 0 1234 0 435
Package2 0 5678 0 1
Package3 0 9 0 0

- break Page -- and so on

I had created a table in the RS that looked like the above with expressions written into the each cell that holds a value. The expression is

=IIF(Fields!agent_type = "AgentType1", mailpackage1.Value, Cint(0)) in the first row, first column of the table.
=IIF(Fields!agent_type = "AgentType1", mailpackage2.Value, Cint(0)) in the second row, first column of the table.
=IIF(Fields!agent_type = "AgentType1", mailpackage3.Value, Cint(0)) in the third row, First column of the table.
And so on....... alternating between agent_type and mailpackage for each cell.

Grouping1: Group by Region, insert page after each group.

What happened was the following:- ((I am putting the first region, because it is also happening for the other regions too)

Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 2000 0 0 0
Package2 2300 0 0 0
Package3 0 0 0 0

break page
Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 0 0 0 0
Package2 0 0 0 0
Package3 0 5 0 0

break page

Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 0 0 150 0
Package2 0 0 2 0
Package3 0 0 4000 0

break page
(on a side note, this region didn't print out AgentType4 because there were no data associated with it)

The question is, is there anything else I could have done to prevent this ? as you can see, the data is correct and placed in their right cells but somehow, they won't join together. I got a feelin that it has something to do with the expression that I had put in each cell.

Can someone help or point me in the right direction ? This is really bothering me and I couldn't figure out why it was doing this. Couldnt find any links or maybe i am putting in the wrong keywords in the search. Thanks muchly !

Bernard Ong

May it be that yor "new" Report is a changed copy of the old one and you didn't remove the grouping by AgentType?|||

Hi there Markus.

Thanks for the reply.

Actually, The old report was html generated. I am basically creating the new report with the new requirements/design using SSRS 2000 now.

At first I thought ti was the agent type too. But when I checked my groupings again, it only has the region grouping.

I even recreated the whole table, start fresh again and made certain that it is only grouped by Region. Same problem...

Thanks for pointing out the agent type grouping though!

Bernard Ong

|||Well it certainly looks more like a Matrix than a Table layout. If you switch from the table to a matrix, you do not need all the expressions at all. If you do not want to change your report design, try to do it in the query instead then by joining a sub-query for each agent. HTH|||

Thanks for the reply BYU

I am looking up on how to do a matrix, but it seems that I still need a recursive or repetitive row, in this case from what I had seen in the dataset, would be the agent-type. Unless there was another way to do it ?

However, due to the many packages that an agent could mail out, (even in landscape) it would be way too long for the printer or even the preview to look.

Thanks for taking a look into this. I appreciate any more tips or hints with this scenario.

Sincerely,

Bernard Ong

|||

Hello all again,

I am trying to figure out how to use the matrix (never really use them before, always with tables) and I am a little frustrated on how to get it to work or even trying to understand it.
Any references or help which I can refer to, to get me going ? my so called "Matrix" looks like some daVinci code that needs to be broken so that it could be interpreted ! :)

Thanks !

Bernard Ong

|||

Hi,

Could you please check if your initial SQL query has a 'order by region,agenttype' in it ?

If you clients are not really concerned about the excel export of this report - you can consider having a table with a grouping - 'region' and a matrix inside the group displaying data for each region repetitively.

so say table 1

create a group1 header -based on 'region'

add another header row for this group

In this second header row insert a matrix

row field- Agent type

column field- mailpackage types

let me know if you need more explanation. however, this report cannot be exported to excel since export of nested data regions is not supported.

Thanks,

PB

|||

Hi PB

Thanks for the reply !
I am going to try your suggestion and see whether that works. I never thought of using a matrix inside a table, and that might just work.
To answer your question:- Yes the query has a group by region AND agent_Type.

Thanks to the post by BYU, I am currently trying to wrestle with Matrix ("the blue or red pill" :) ) and it is slowly doing what I "think" it is doing.
It is just confusing because the matrix controls work VERY differently with the table. I guess I have to "Add Row" in the cell instead of highlighting the whole row.

I am not sure about this, but with the way I am doing it right now, sounds similar to what you saying except without the header. is that right ?

Sincerely,
Bernard Ong

|||

Hi,

I was talking more about an ORDER BY clause after the Group BY.

select region,etc,cetc

GROUP BY region, agent type,etc

ORDER BY Region,Agent Type.

That probably will make your original table layout also work.

Good luck,

PB

|||

Sorry ORDER BY REgion , Package , Agent Type

|||

Hi PB,

Sorry, must have read the post wrongly. and yes, i do have an order by clause, but with the package in there, it won't work.

The way it is as the dataset is shown, package1, package2 and so on, are the names of the packages(mailpackages column), and the data associated with them are counts or totals.

region agent_type mailpackage1 mailpackage2 mailpackage3
New York Agenttype1 2000 2300 0
New York Agenttype2 0 0 5
New York Agenttype3 150 2 4000
Central Agenttype2 1234 5678 9
Central Agenttype4 435 1 0
MidWest Agenttype1 555 0 0
West Agenttype1 1 45 0
West Agenttype2 0 2 3

Only region and agent_type are string characters, so using the package name in the order by won't work.

Thanks though !

Bernard Ong

|||

Hi Bernard,

I'm still a little unsure of your problem ....could you please explain how the data is stored...I mean how do you get the mailpackage1,mailpackage2 in the stored procedure ....is this a column indicating the type/category of package ?

In that case even if it is a number field , you should order by that before agent type

say you have a table with following columns

region id, mail_package_count, agent_type, packageNo.

newyork, 200, 1 45

new york 500 2 45

new york 30 3 500

This indicates that 200 counts of package45 were delivered by agent 1 , 500 counts of package45 were delivered by agent2 .....

IS that how it is?

I think you may have to make a small change in your stored procedure itself...returning the dataset ordered by region and packaget instead of agent type.

Please ignore this post if I got your problem wrong and Sorry if I confused you even more...

Thanks,

PB

|||

Hi Pbala,

Yeah ! that is exactly how it is without the packageNo. I am sorry that it wasn't clear enough, but in my situation, the numbers represent the count of the packages delivered. I still need the agent type because the client wants to see how many packages were sent out by a particular agent type.

Such as an Elite Agent type, or a basic agent, and they would make a decision out of it. To quote, this is how it is going to look like.

region id, mail_package_1_count, mail_package_2_count, mail_package_3_count, agent_type

newyork, 200 123 456 1

new york 500 342 688 2

new york 30 0 0 3

Hope this makes more sense now.

Still working on the problem but I am getting somewhere though. Right now, with using the matrix, i am able to get the right totals and how it would look like on the report with a "specific" region.

however, when I tried to have the all regions, it just clumped everything into one matrix without showing the other regions. this meaning that i got the total for ALL regions in one page, instead of them breaking out in different pages with each region.

Is there an expression or something am I missing to do ?

Thanks.
Bernard Ong

|||

Hello all once again :)

Just an update on the situation here.
First off, want to thank Pbala, BYU, and Mark for helping out with this thread.
After much reading and trying to figure out the matrix, I got the report working.
I was confused with how to use the Matrix which in the end did more harm than good and finally got the hang of it.
It was all about the groupings and how the Matrix does not act like a table. I resolve this issue by doing the following groupings.

For the Row Groupings:
I only needed to use ONE row Grouping and I ended up putting alot of row groupings thinking that they acted like rows from the table.
Boy, was I dead wrong. I found out that you have to select a cell (Row Section specifically) in the matrix to add a row in the column that would extend to the end.
BY clicking on the row header, it only allows you to add a row grouping and not a row. Either way. I grouped only the region in the
Matrix Properties->Groupings. NExt I keep adding the amount of rows needed to represent a package and drag their fields into the Data section.

For the Column Groupings:
Essentially the same thing, BUT I put the field!agent_type in the column section. Next I entered the the agent_type grouping in the column
grouping of the matrix.

The trouble i was having was not understanding the difference between the Row Groupings and Rows. I keep adding the row groupings which ended up not printing
What i needed for the report. After realizing that problem, I did a bit more research, and figured out that only the cell of the matrix allows Rows.

After that, I put the Subtotal field and it works ! My report printed exactly like the scenario.

Couldn't have done this without this forum and without the ideas and suggestions presented by the posters of this thread ! Thanks alot !

However, I do have another question concerning about the matrix, Is it possible to have an actual total of everything at the bottom of the screen like how the Table Footer would have ? Or the Matrix is only limited to having a subtotal ? The reason why I ask this because I have a option called "All Regions" which should give me the totals of all the regions including each individual region printed out on every page. I achieved this by having a table at the bottom of the matrix to collect the totals. I made the table into a table footer so that it encapsulates everything, and using the Visibility feature, Turn it off if the user only selected a specific region, but turn it on if the user selected "All region". was wondering whether the matrix could also achieve this but in an easier way like how the table would ?

thanks !

Bernard Ong

Question on how to display data in this unique situation using Matrix.....

Hi guys,

Man I do come up with strange scenarios, but that is the joy of working in software field right ? ;-)
First off, thanks to anyone taking their time to read this, and Ihope this post paints a clearer picture better than my previous posts.
I have an old stored procedure (which I didn't create) that produces a dataset of the following:-

((All names and values had been changed to protect confidentiality))

region agent_type mailpackage1 mailpackage2 mailpackage3
New York Agenttype1 2000 2300 0
New York Agenttype2 0 0 5
New York Agenttype3 150 2 4000
Central Agenttype2 1234 5678 9
Central Agenttype4 435 1 0
MidWest Agenttype1 555 0 0
West Agenttype1 1 45 0
West Agenttype2 0 2 3

A little bit of explanation:-
Each region can have any type of agents, specified by the number to distinguish different agent types. these agent types mail specific packages to their customers depending on the situation and what the customers asked for. the numbers in each mail package indicate the total that had been sent out by a particular type of an agent. So in this case we are not dealing wtih how many agents are there, just how many packages had been sent out by a specific type of an agent in a region.

Previously the report was produced like you would see in the above dataset. However the client would want it the other way around. Though I didn't show it here, there are plenty of other packages but I am picking three for clarity sake.

So the "new" Report would have to look something like this.

Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 2000 0 150 0
Package2 2300 0 2 0
Package3 0 5 4000 0

break page

Region: Central
AgentType1 AgentType2 AgentType3 AgentType4
Package1 0 1234 0 435
Package2 0 5678 0 1
Package3 0 9 0 0

- break Page -- and so on

I had created a table in the RS that looked like the above with expressions written into the each cell that holds a value. The expression is

=IIF(Fields!agent_type = "AgentType1", mailpackage1.Value, Cint(0)) in the first row, first column of the table.
=IIF(Fields!agent_type = "AgentType1", mailpackage2.Value, Cint(0)) in the second row, first column of the table.
=IIF(Fields!agent_type = "AgentType1", mailpackage3.Value, Cint(0)) in the third row, First column of the table.
And so on....... alternating between agent_type and mailpackage for each cell.

Grouping1: Group by Region, insert page after each group.

What happened was the following:- ((I am putting the first region, because it is also happening for the other regions too)

Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 2000 0 0 0
Package2 2300 0 0 0
Package3 0 0 0 0

break page
Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 0 0 0 0
Package2 0 0 0 0
Package3 0 5 0 0

break page

Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 0 0 150 0
Package2 0 0 2 0
Package3 0 0 4000 0

break page
(on a side note, this region didn't print out AgentType4 because there were no data associated with it)

The question is, is there anything else I could have done to prevent this ? as you can see, the data is correct and placed in their right cells but somehow, they won't join together. I got a feelin that it has something to do with the expression that I had put in each cell.

Can someone help or point me in the right direction ? This is really bothering me and I couldn't figure out why it was doing this. Couldnt find any links or maybe i am putting in the wrong keywords in the search. Thanks muchly !

Bernard Ong

May it be that yor "new" Report is a changed copy of the old one and you didn't remove the grouping by AgentType?|||

Hi there Markus.

Thanks for the reply.

Actually, The old report was html generated. I am basically creating the new report with the new requirements/design using SSRS 2000 now.

At first I thought ti was the agent type too. But when I checked my groupings again, it only has the region grouping.

I even recreated the whole table, start fresh again and made certain that it is only grouped by Region. Same problem...

Thanks for pointing out the agent type grouping though!

Bernard Ong

|||Well it certainly looks more like a Matrix than a Table layout. If you switch from the table to a matrix, you do not need all the expressions at all. If you do not want to change your report design, try to do it in the query instead then by joining a sub-query for each agent. HTH|||

Thanks for the reply BYU

I am looking up on how to do a matrix, but it seems that I still need a recursive or repetitive row, in this case from what I had seen in the dataset, would be the agent-type. Unless there was another way to do it ?

However, due to the many packages that an agent could mail out, (even in landscape) it would be way too long for the printer or even the preview to look.

Thanks for taking a look into this. I appreciate any more tips or hints with this scenario.

Sincerely,

Bernard Ong

|||

Hello all again,

I am trying to figure out how to use the matrix (never really use them before, always with tables) and I am a little frustrated on how to get it to work or even trying to understand it.
Any references or help which I can refer to, to get me going ? my so called "Matrix" looks like some daVinci code that needs to be broken so that it could be interpreted ! :)

Thanks !

Bernard Ong

|||

Hi,

Could you please check if your initial SQL query has a 'order by region,agenttype' in it ?

If you clients are not really concerned about the excel export of this report - you can consider having a table with a grouping - 'region' and a matrix inside the group displaying data for each region repetitively.

so say table 1

create a group1 header -based on 'region'

add another header row for this group

In this second header row insert a matrix

row field- Agent type

column field- mailpackage types

let me know if you need more explanation. however, this report cannot be exported to excel since export of nested data regions is not supported.

Thanks,

PB

|||

Hi PB

Thanks for the reply !
I am going to try your suggestion and see whether that works. I never thought of using a matrix inside a table, and that might just work.
To answer your question:- Yes the query has a group by region AND agent_Type.

Thanks to the post by BYU, I am currently trying to wrestle with Matrix ("the blue or red pill" :) ) and it is slowly doing what I "think" it is doing.
It is just confusing because the matrix controls work VERY differently with the table. I guess I have to "Add Row" in the cell instead of highlighting the whole row.

I am not sure about this, but with the way I am doing it right now, sounds similar to what you saying except without the header. is that right ?

Sincerely,
Bernard Ong

|||

Hi,

I was talking more about an ORDER BY clause after the Group BY.

select region,etc,cetc

GROUP BY region, agent type,etc

ORDER BY Region,Agent Type.

That probably will make your original table layout also work.

Good luck,

PB

|||

Sorry ORDER BY REgion , Package , Agent Type

|||

Hi PB,

Sorry, must have read the post wrongly. and yes, i do have an order by clause, but with the package in there, it won't work.

The way it is as the dataset is shown, package1, package2 and so on, are the names of the packages(mailpackages column), and the data associated with them are counts or totals.

region agent_type mailpackage1 mailpackage2 mailpackage3
New York Agenttype1 2000 2300 0
New York Agenttype2 0 0 5
New York Agenttype3 150 2 4000
Central Agenttype2 1234 5678 9
Central Agenttype4 435 1 0
MidWest Agenttype1 555 0 0
West Agenttype1 1 45 0
West Agenttype2 0 2 3

Only region and agent_type are string characters, so using the package name in the order by won't work.

Thanks though !

Bernard Ong

|||

Hi Bernard,

I'm still a little unsure of your problem ....could you please explain how the data is stored...I mean how do you get the mailpackage1,mailpackage2 in the stored procedure ....is this a column indicating the type/category of package ?

In that case even if it is a number field , you should order by that before agent type

say you have a table with following columns

region id, mail_package_count, agent_type, packageNo.

newyork, 200, 1 45

new york 500 2 45

new york 30 3 500

This indicates that 200 counts of package45 were delivered by agent 1 , 500 counts of package45 were delivered by agent2 .....

IS that how it is?

I think you may have to make a small change in your stored procedure itself...returning the dataset ordered by region and packaget instead of agent type.

Please ignore this post if I got your problem wrong and Sorry if I confused you even more...

Thanks,

PB

|||

Hi Pbala,

Yeah ! that is exactly how it is without the packageNo. I am sorry that it wasn't clear enough, but in my situation, the numbers represent the count of the packages delivered. I still need the agent type because the client wants to see how many packages were sent out by a particular agent type.

Such as an Elite Agent type, or a basic agent, and they would make a decision out of it. To quote, this is how it is going to look like.

region id, mail_package_1_count, mail_package_2_count, mail_package_3_count, agent_type

newyork, 200 123 456 1

new york 500 342 688 2

new york 30 0 0 3

Hope this makes more sense now.

Still working on the problem but I am getting somewhere though. Right now, with using the matrix, i am able to get the right totals and how it would look like on the report with a "specific" region.

however, when I tried to have the all regions, it just clumped everything into one matrix without showing the other regions. this meaning that i got the total for ALL regions in one page, instead of them breaking out in different pages with each region.

Is there an expression or something am I missing to do ?

Thanks.
Bernard Ong

|||

Hello all once again :)

Just an update on the situation here.
First off, want to thank Pbala, BYU, and Mark for helping out with this thread.
After much reading and trying to figure out the matrix, I got the report working.
I was confused with how to use the Matrix which in the end did more harm than good and finally got the hang of it.
It was all about the groupings and how the Matrix does not act like a table. I resolve this issue by doing the following groupings.

For the Row Groupings:
I only needed to use ONE row Grouping and I ended up putting alot of row groupings thinking that they acted like rows from the table.
Boy, was I dead wrong. I found out that you have to select a cell (Row Section specifically) in the matrix to add a row in the column that would extend to the end.
BY clicking on the row header, it only allows you to add a row grouping and not a row. Either way. I grouped only the region in the
Matrix Properties->Groupings. NExt I keep adding the amount of rows needed to represent a package and drag their fields into the Data section.

For the Column Groupings:
Essentially the same thing, BUT I put the field!agent_type in the column section. Next I entered the the agent_type grouping in the column
grouping of the matrix.

The trouble i was having was not understanding the difference between the Row Groupings and Rows. I keep adding the row groupings which ended up not printing
What i needed for the report. After realizing that problem, I did a bit more research, and figured out that only the cell of the matrix allows Rows.

After that, I put the Subtotal field and it works ! My report printed exactly like the scenario.

Couldn't have done this without this forum and without the ideas and suggestions presented by the posters of this thread ! Thanks alot !

However, I do have another question concerning about the matrix, Is it possible to have an actual total of everything at the bottom of the screen like how the Table Footer would have ? Or the Matrix is only limited to having a subtotal ? The reason why I ask this because I have a option called "All Regions" which should give me the totals of all the regions including each individual region printed out on every page. I achieved this by having a table at the bottom of the matrix to collect the totals. I made the table into a table footer so that it encapsulates everything, and using the Visibility feature, Turn it off if the user only selected a specific region, but turn it on if the user selected "All region". was wondering whether the matrix could also achieve this but in an easier way like how the table would ?

thanks !

Bernard Ong