Question to blindman

I posted a question yesterday about text field compare and I can't open the thread. Not sure why. Can you repost your reply to this thread regarding how to compare two text strings (i.e. two company names)? Much appreciated.hmm, odd, I run into the same problem, also in other threads.|||dbforums is experiencing technical difficulties...

Is the choice of an RDBMS a technical difficulty?|||Long live OPEN SOURCE!

Question regarding size of varchar field

Hi,
I am using MSDE together with Enterprise Manager.
I have a table with a field nameddescription.
This field will be filled by a web forms's textbox web control.
The textbox'smaxsize attribute is set to "3000" characters.
What size do I have to adjust for my DB fielddescription?
Is the size of3000 in Enterpise Manager equal to3000 characters for the textbox?
I am just trying to avoid errors if MSDE cuts off the string that comes from the textbox webcontrol.Yes, you should set the width of your varchar column to 3000. This unit of measurement is actually bytes, but each character takes 1byte to store, so in effect the column can hold 3000 characters..
|||

I'm answering a question you didn't ask, but maxsize doesn't work if your textbox is multi-line. I just assumed it would be if you allowed that much in it. If you want to limit a multi-line textbox, you need to use a regular expression validator to do it.

|||Thanks for letting me know - and you are right... the texbox is indeed multi-line.
Maybe you can answer my question I have asked in another thread addressing a regular expression issue I am currently faced with - I am still waiting for some helpers there ...
This is the thread:
http://forums.asp.net/937464/ShowPost.aspx|||One more point is if you will use unicode (nvarchar or nchar data type), then physical size for a character will be doubled which means 2 bytes for a character.
If you run
sp_help TableName
you will see "Length" column which keeps physical length of column in bytes

Question regarding Identitiy field

Guys,
I have a identity flag set on a column. When i am testing the system,
inserting records the identity goes all the way to 60 and so on. I need to
put the table in production system now. However i need to reset the identity
field to start from 1. How can i clear the identity field and make it start
from the begining.
Thanks
MannyUse this
DBCC CheckIdent('TableName')
"Manny Chohan" wrote:

> Guys,
> I have a identity flag set on a column. When i am testing the system,
> inserting records the identity goes all the way to 60 and so on. I need to
> put the table in production system now. However i need to reset the identi
ty
> field to start from 1. How can i clear the identity field and make it star
t
> from the begining.
> Thanks
> Manny|||I'm not sure why you care about the starting value but you can either
truncate the table or execute DBCC CHECKIDENT with the RESEED option.
Hope this helps.
Dan Guzman
SQL Server MVP
"Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
news:6C4D0EBE-7AA5-4F18-9EAA-ED2547B1BD23@.microsoft.com...
> Guys,
> I have a identity flag set on a column. When i am testing the system,
> inserting records the identity goes all the way to 60 and so on. I need to
> put the table in production system now. However i need to reset the
> identity
> field to start from 1. How can i clear the identity field and make it
> start
> from the begining.
> Thanks
> Manny|||Dan,
Just fyi, dbcc checkident defaults to reseed... so,
checkident ('TableName')
is equivilent to
checkident ('TableName', RESEED)
"Dan Guzman" wrote:

> I'm not sure why you care about the starting value but you can either
> truncate the table or execute DBCC CHECKIDENT with the RESEED option.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
> news:6C4D0EBE-7AA5-4F18-9EAA-ED2547B1BD23@.microsoft.com...
>
>|||Thanks All
"CBretana" wrote:
> Dan,
> Just fyi, dbcc checkident defaults to reseed... so,
> checkident ('TableName')
> is equivilent to
> checkident ('TableName', RESEED)
> "Dan Guzman" wrote:
>|||I am with Dan all the way on this one. Why do you care about the value of
the identity? I admit that I would probably want to reset it myself when
going into production, just because it looks more "tidy." It is however,
always concerning when someone states that they want to know how to do this
because it often means they are using these values in some manner where the
user will care about the value, and identities are pretty bad for this sort
of thing (users HATE gaps, and gaps are just generally part of the identity
experience, since row with the identity property cannot be updated (ever).)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
news:6C4D0EBE-7AA5-4F18-9EAA-ED2547B1BD23@.microsoft.com...
> Guys,
> I have a identity flag set on a column. When i am testing the system,
> inserting records the identity goes all the way to 60 and so on. I need to
> put the table in production system now. However i need to reset the
> identity
> field to start from 1. How can i clear the identity field and make it
> start
> from the begining.
> Thanks
> Manny|||In theory, I agree 100% with you and Dan on this... I do not believe the
value of any surrogate key, much less an Identoty, should be significant to
anyone...
However, in practice, simply out ofa sense of esthetics, I find myself doing
exactly the same thing this gentleman asked about...
In theory, if we really didn't care about the actual values, we wouldn't
always set the seed value for Identity columns = 1, we'd set it to the lowes
t
legal value for the underlying datatype, (TinyInt, SmallInt, Int).
(Either zero, (0), -32,768, or -2,147,483,648, respectively)
But practically, we do care what the value is, because in coding, and
debugging, and manipulating the data in Query Analyzer, we use these values,
and so they do matter.
"Louis Davidson" wrote:

> I am with Dan all the way on this one. Why do you care about the value of
> the identity? I admit that I would probably want to reset it myself when
> going into production, just because it looks more "tidy." It is however,
> always concerning when someone states that they want to know how to do thi
s
> because it often means they are using these values in some manner where th
e
> user will care about the value, and identities are pretty bad for this sor
t
> of thing (users HATE gaps, and gaps are just generally part of the identit
y
> experience, since row with the identity property cannot be updated (ever).
)
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
> news:6C4D0EBE-7AA5-4F18-9EAA-ED2547B1BD23@.microsoft.com...
>
>|||That was pretty much what I meant too. It is really a weird thing too.
Sure when we start out programming it is easier to have the value start out
at 1, since it is easier to type while doing initial programming, but by all
logic if we really mean that we don't care about the value then starting
at -minvalue would be better (if we are lucky our values will get that big
anyhow!)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:7EF6C6ED-EE40-4AA2-8173-8A99226E0CEA@.microsoft.com...
> In theory, I agree 100% with you and Dan on this... I do not believe the
> value of any surrogate key, much less an Identoty, should be significant
> to
> anyone...
> However, in practice, simply out ofa sense of esthetics, I find myself
> doing
> exactly the same thing this gentleman asked about...
> In theory, if we really didn't care about the actual values, we wouldn't
> always set the seed value for Identity columns = 1, we'd set it to the
> lowest
> legal value for the underlying datatype, (TinyInt, SmallInt, Int).
> (Either zero, (0), -32,768, or -2,147,483,648, respectively)
> But practically, we do care what the value is, because in coding, and
> debugging, and manipulating the data in Query Analyzer, we use these
> values,
> and so they do matter.
>
> "Louis Davidson" wrote:
>|||Manny,
you have two options:
1. use DBCC CHECKIDENT statement with RESEED option (see Books OnLine),
2. use TRUNCATE TABLE on your table (however THIS WILL DELETE ALL ROWS IN A
TABLE WITHOUT ROLLBACK OPTION!)
Pawel
"Manny Chohan" wrote:

> Guys,
> I have a identity flag set on a column. When i am testing the system,
> inserting records the identity goes all the way to 60 and so on. I need to
> put the table in production system now. However i need to reset the identi
ty
> field to start from 1. How can i clear the identity field and make it star
t
> from the begining.
> Thanks
> Manny|||> 2. use TRUNCATE TABLE on your table (however THIS WILL DELETE ALL ROWS IN
> A
> TABLE WITHOUT ROLLBACK OPTION!)
TRUNCATE does allow a ROLLBACK. However, like other SQL data modification
statements, the transaction will need to be started explicitly unless
IMPLICIT_TRANSACTIONS is on.
CREATE TABLE MyTable(Col1 int NOT NULL)
INSERT INTO MyTable VALUES(1)
BEGIN TRAN
TRUNCATE TABLE MyTable
SELECT Col1 FROM MyTable
ROLLBACK
SELECT Col1 FROM MyTable
Hope this helps.
Dan Guzman
SQL Server MVP
"Pawel Potasinski" <PawePotasiski@.discussions.microsoft.com> wrote in
message news:11BF931F-D7BC-41A3-B0FD-E2F85943B14B@.microsoft.com...
> Manny,
> you have two options:
> 1. use DBCC CHECKIDENT statement with RESEED option (see Books OnLine),
> 2. use TRUNCATE TABLE on your table (however THIS WILL DELETE ALL ROWS IN
> A
> TABLE WITHOUT ROLLBACK OPTION!)
> Pawel
> "Manny Chohan" wrote:
>

Question on SQL Update behaviour

I have a question on how SQL Server updates records. Say assuming if I have
a table with 50000 records.. and i need to update a particular field C in
this table, based on field A and field B on the same table. I can either
write a set based SP or a cursor based SP to do that.
If I update it using cursors, I can see the records being updated 1 by 1. If
I update the whole thing using a set based SP, am I right to say that I will
not see any records updated till the process is over? Does SQL Server does
the transaction commitment itself if I write it using the set-based
approach?
Thanks.
Hi,
Update with set will do a batch updat based on the condition you are gving
in the where clause. So until the transaction is commited
you will not be able to see change. Where as Cursor does a row by row
operation. So after each record update you could see the
change.
Have a look into ISOLATION LEVEL topic in books online.
Thanks
Hari
SQL Server MVP
"Nestor" <n3570r@.yahoo.com> wrote in message
news:uncViVfgFHA.2904@.tk2msftngp13.phx.gbl...
>I have a question on how SQL Server updates records. Say assuming if I have
>a table with 50000 records.. and i need to update a particular field C in
>this table, based on field A and field B on the same table. I can either
>write a set based SP or a cursor based SP to do that.
> If I update it using cursors, I can see the records being updated 1 by 1.
> If I update the whole thing using a set based SP, am I right to say that I
> will not see any records updated till the process is over? Does SQL Server
> does the transaction commitment itself if I write it using the set-based
> approach?
> Thanks.
>
|||thanks for the prompt response... i'm really curious about how the table
will be locked up since i'm updating the table based on conditions from
fields that are in the same table as well...
will cursors work better in this case than batch insert due to the lockups?
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uGQThcfgFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Update with set will do a batch updat based on the condition you are gving
> in the where clause. So until the transaction is commited
> you will not be able to see change. Where as Cursor does a row by row
> operation. So after each record update you could see the
> change.
> Have a look into ISOLATION LEVEL topic in books online.
> Thanks
> Hari
> SQL Server MVP
> "Nestor" <n3570r@.yahoo.com> wrote in message
> news:uncViVfgFHA.2904@.tk2msftngp13.phx.gbl...
>
|||you can actually see the changes taking place by running a query with the
table hint nolock as in: select count(*) from mytable with (nolock) where...
I use this approach at times to see how far in a large insert or update is so
I can calculate how much longer the statement will take.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Nestor" wrote:

> thanks for the prompt response... i'm really curious about how the table
> will be locked up since i'm updating the table based on conditions from
> fields that are in the same table as well...
> will cursors work better in this case than batch insert due to the lockups?
>
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:uGQThcfgFHA.576@.TK2MSFTNGP15.phx.gbl...
>
>

Question on SQL Update behaviour

I have a question on how SQL Server updates records. Say assuming if I have
a table with 50000 records.. and i need to update a particular field C in
this table, based on field A and field B on the same table. I can either
write a set based SP or a cursor based SP to do that.
If I update it using cursors, I can see the records being updated 1 by 1. If
I update the whole thing using a set based SP, am I right to say that I will
not see any records updated till the process is over? Does SQL Server does
the transaction commitment itself if I write it using the set-based
approach?
Thanks.Hi,
Update with set will do a batch updat based on the condition you are gving
in the where clause. So until the transaction is commited
you will not be able to see change. Where as Cursor does a row by row
operation. So after each record update you could see the
change.
Have a look into ISOLATION LEVEL topic in books online.
Thanks
Hari
SQL Server MVP
"Nestor" <n3570r@.yahoo.com> wrote in message
news:uncViVfgFHA.2904@.tk2msftngp13.phx.gbl...
>I have a question on how SQL Server updates records. Say assuming if I have
>a table with 50000 records.. and i need to update a particular field C in
>this table, based on field A and field B on the same table. I can either
>write a set based SP or a cursor based SP to do that.
> If I update it using cursors, I can see the records being updated 1 by 1.
> If I update the whole thing using a set based SP, am I right to say that I
> will not see any records updated till the process is over? Does SQL Server
> does the transaction commitment itself if I write it using the set-based
> approach?
> Thanks.
>|||thanks for the prompt response... i'm really curious about how the table
will be locked up since i'm updating the table based on conditions from
fields that are in the same table as well...
will cursors work better in this case than batch insert due to the lockups?
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uGQThcfgFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Update with set will do a batch updat based on the condition you are gving
> in the where clause. So until the transaction is commited
> you will not be able to see change. Where as Cursor does a row by row
> operation. So after each record update you could see the
> change.
> Have a look into ISOLATION LEVEL topic in books online.
> Thanks
> Hari
> SQL Server MVP
> "Nestor" <n3570r@.yahoo.com> wrote in message
> news:uncViVfgFHA.2904@.tk2msftngp13.phx.gbl...
>|||you can actually see the changes taking place by running a query with the
table hint nolock as in: select count(*) from mytable with (nolock) where...
I use this approach at times to see how far in a large insert or update is s
o
I can calculate how much longer the statement will take.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Nestor" wrote:

> thanks for the prompt response... i'm really curious about how the table
> will be locked up since i'm updating the table based on conditions from
> fields that are in the same table as well...
> will cursors work better in this case than batch insert due to the lockups
?
>
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:uGQThcfgFHA.576@.TK2MSFTNGP15.phx.gbl...
>
>

Question on SQL Update behaviour

I have a question on how SQL Server updates records. Say assuming if I have
a table with 50000 records.. and i need to update a particular field C in
this table, based on field A and field B on the same table. I can either
write a set based SP or a cursor based SP to do that.
If I update it using cursors, I can see the records being updated 1 by 1. If
I update the whole thing using a set based SP, am I right to say that I will
not see any records updated till the process is over? Does SQL Server does
the transaction commitment itself if I write it using the set-based
approach?
Thanks.Hi,
Update with set will do a batch updat based on the condition you are gving
in the where clause. So until the transaction is commited
you will not be able to see change. Where as Cursor does a row by row
operation. So after each record update you could see the
change.
Have a look into ISOLATION LEVEL topic in books online.
Thanks
Hari
SQL Server MVP
"Nestor" <n3570r@.yahoo.com> wrote in message
news:uncViVfgFHA.2904@.tk2msftngp13.phx.gbl...
>I have a question on how SQL Server updates records. Say assuming if I have
>a table with 50000 records.. and i need to update a particular field C in
>this table, based on field A and field B on the same table. I can either
>write a set based SP or a cursor based SP to do that.
> If I update it using cursors, I can see the records being updated 1 by 1.
> If I update the whole thing using a set based SP, am I right to say that I
> will not see any records updated till the process is over? Does SQL Server
> does the transaction commitment itself if I write it using the set-based
> approach?
> Thanks.
>|||thanks for the prompt response... i'm really curious about how the table
will be locked up since i'm updating the table based on conditions from
fields that are in the same table as well...
will cursors work better in this case than batch insert due to the lockups?
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uGQThcfgFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Update with set will do a batch updat based on the condition you are gving
> in the where clause. So until the transaction is commited
> you will not be able to see change. Where as Cursor does a row by row
> operation. So after each record update you could see the
> change.
> Have a look into ISOLATION LEVEL topic in books online.
> Thanks
> Hari
> SQL Server MVP
> "Nestor" <n3570r@.yahoo.com> wrote in message
> news:uncViVfgFHA.2904@.tk2msftngp13.phx.gbl...
>>I have a question on how SQL Server updates records. Say assuming if I
>>have a table with 50000 records.. and i need to update a particular field
>>C in this table, based on field A and field B on the same table. I can
>>either write a set based SP or a cursor based SP to do that.
>> If I update it using cursors, I can see the records being updated 1 by 1.
>> If I update the whole thing using a set based SP, am I right to say that
>> I will not see any records updated till the process is over? Does SQL
>> Server does the transaction commitment itself if I write it using the
>> set-based approach?
>> Thanks.
>|||you can actually see the changes taking place by running a query with the
table hint nolock as in: select count(*) from mytable with (nolock) where...
I use this approach at times to see how far in a large insert or update is so
I can calculate how much longer the statement will take.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Nestor" wrote:
> thanks for the prompt response... i'm really curious about how the table
> will be locked up since i'm updating the table based on conditions from
> fields that are in the same table as well...
> will cursors work better in this case than batch insert due to the lockups?
>
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:uGQThcfgFHA.576@.TK2MSFTNGP15.phx.gbl...
> > Hi,
> >
> > Update with set will do a batch updat based on the condition you are gving
> > in the where clause. So until the transaction is commited
> > you will not be able to see change. Where as Cursor does a row by row
> > operation. So after each record update you could see the
> > change.
> >
> > Have a look into ISOLATION LEVEL topic in books online.
> >
> > Thanks
> > Hari
> > SQL Server MVP
> >
> > "Nestor" <n3570r@.yahoo.com> wrote in message
> > news:uncViVfgFHA.2904@.tk2msftngp13.phx.gbl...
> >>I have a question on how SQL Server updates records. Say assuming if I
> >>have a table with 50000 records.. and i need to update a particular field
> >>C in this table, based on field A and field B on the same table. I can
> >>either write a set based SP or a cursor based SP to do that.
> >>
> >> If I update it using cursors, I can see the records being updated 1 by 1.
> >> If I update the whole thing using a set based SP, am I right to say that
> >> I will not see any records updated till the process is over? Does SQL
> >> Server does the transaction commitment itself if I write it using the
> >> set-based approach?
> >>
> >> Thanks.
> >>
> >
> >
>
>

Question on MDX

I am trying to write a calculated field. I need to calculate the Sum of a Item Sold over a period of time where 2 measures called BV value and Unit Price are zero. Can someone suggest what functions I should use to get this done

Thanks

Ann

Hello Ann,

Did you mean to use a calculated member? If yes, please, try the following:

WITH MEMBER [Measures].SumOfSoldItems AS 'sum ( filter ( [Date].[Fiscal Year].members, [Measures].[BV Value] = 0 AND [Measures].[Unit Price] = 0 ) , [Measures].[Item Sold] ) '
SELECT [Measures].SumOfSoldItems on 0
FROM [Adventure Works]

In the first argument of the filter function you should put a set representing the period of time that you are interested in.

I hope this helps

Greg

|||

Hi Greg

Thanks for your post. Thats exactly what I am trying to achieve. I tried using the above method, but it gives me a syntax error . It says, Syntax for "WITH" is incorrect.

Thanks

Ann

|||

Could you tell me what client application you are using? I wrote 2 more MDX queries for you so you can use them as a small tutorial. They both work with Adventure Works.

1. This one doesn't use calculated member but should give you the right results. After you try it, you can change the query to work with your own cube. Change the time dimension members ( [Date].[Fiscal Year].members ) to a set representing the time period you need. The two other measures in the filter you can change to [Measures].[BV value]=0 and [Measures].[Unit Price] = 0. The measure in the WHERE clause you may want to change to [Measures].[Item Sold]

SELECT filter ( [Date].[Fiscal Year].members, [Measures].[Amount] > 3500000 AND [Measures].[Order Quantity] > 90000 ) on 0
FROM [Adventure Works]
WHERE [Measures].[Sales Amount]

2. This is the query that I showed you yesterday but I changed it back slightly so now it works with Adventure Works. Try it first against AW and then change the time period and measures for your own needs as I explained in the first paragraph:

WITH MEMBER [Measures].x AS 'sum ( filter ( [Date].[Fiscal Year].members, [Measures].[Amount] > 0 AND [Measures].[Order Quantity] > 0 ) , [Measures].[Sales Amount] ) '
SELECT [Measures].x on 0
FROM [Adventure Works]

I hope this works for you. If it doesn't or you have more questions, feel free to ask :)

- Greg

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

Question on Formula

I have a computed field that I want to change the
formula. How do I do this in SQL ?
thanks in advance
You have to drop and recreate the computed column to change the formula:
CREATE TABLE a (b INT, c AS b*b)
GO
ALTER TABLE a DROP COLUMN c
GO
ALTER TABLE a ADD c AS b*2
Jacco Schalkwijk
SQL Server MVP
"Deck" <d@.d.com> wrote in message
news:uxX%23d5AYFHA.4000@.TK2MSFTNGP10.phx.gbl...
>I have a computed field that I want to change the
> formula. How do I do this in SQL ?
> thanks in advance
>
|||"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:O1caxQEYFHA.3280@.TK2MSFTNGP09.phx.gbl...
> You have to drop and recreate the computed column to change the formula:
> CREATE TABLE a (b INT, c AS b*b)
> GO
> ALTER TABLE a DROP COLUMN c
> GO
> ALTER TABLE a ADD c AS b*2
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Deck" <d@.d.com> wrote in message
> news:uxX%23d5AYFHA.4000@.TK2MSFTNGP10.phx.gbl...
>
thanks, i was also thinking of the same way. but dropping and re-creating
the field will place the field on the last position. is there anyway to
maintain
its position to where it was?
|||hi,
Deck wrote:
> thanks, i was also thinking of the same way. but dropping and
> re-creating the field will place the field on the last position. is
> there anyway to maintain
> its position to where it was?
in a relational database the column position is insignificant... and should
be the same for client applications as you should not use SELECT * but a
well defined select list... anyway, you can achieve the desired result
creating a temporary table with all the columns in the desired order,
migrate data to the new table, drop the old one and renaming the new one...
and of course re-setting all idxs, fks, triggers, constraints, extended
properties...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

question on crystal reports

Hi,
there is a requirement of making Login name field just 4 characters. This is Group name type field which shows login names. We have to decrease the length of login names to save space. How shd I proceed?
ThanksSorry, not clear on the question, are you trying to reduce the length in the report, or in the database ?