Having subtotal after specific number of rows/columns

Hi
I'm working on a Matrix report in which i have a column grouping. I
have added a subtotal for the column group. so this total comes as he
last column i.e. after all the values in that column grouping. I want
to have a subtotal after lets say 10 columns and then again a total at
the end.
Can somebody tell me how to have a subtotal in the middle of a group as
well as in the end of the group?
Ankur MehtaAnkur,
The only way I know how to do this is to add another group which breaks down
your columns into sets of X number, depending on what other factor you use to
group on. Example using the Adventure Works sample database: Group on the
main Products, then do a group on Accessories under the products. Then you
can have a group2 footer which subtotals on the second group and a group1
footer which subtotals on the entire Products line.
Sorry I don't know of another way.
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"Ankur Mehta" wrote:
> Hi
> I'm working on a Matrix report in which i have a column grouping. I
> have added a subtotal for the column group. so this total comes as he
> last column i.e. after all the values in that column grouping. I want
> to have a subtotal after lets say 10 columns and then again a total at
> the end.
> Can somebody tell me how to have a subtotal in the middle of a group as
> well as in the end of the group?
> Ankur Mehta
>

HAVING a problem

Hi NG,
I have a problem with a query. I have three columns with relations from
A to B and the number of eg. Orders:
C1 | C2 | Number
A B 17
A C 4
A E 23
B A 22
B G 19
B J 21
What I want is to get from each C1 element (A, B) the relation to the
C2 with the lowest number, in this example:
C1 | C2 | Number
A C 4
B G 19
How can I do this?
Thank you very much,
RudiSELECT c1, c2, number
FROM your_table AS T
WHERE number =
(SELECT MIN(number)
FROM your_table
WHERE c1 = T.c1) ;
David Portas
SQL Server MVP
--

Have 100+ columns, or reduce it down to about 3?

Basically, what I'm doing is storing answers to questions in a survey. I have two ways I can organize the table:

1. Just having a table with lots of columns - one for each question

2. A table with only about maybe 3 columns:

SurveyID
QuestionID
QuestionAnswer

In this second case, the the primary key would be both SurveyID and QuestionID combined, of course.

I don't fully know the pros and cons of the two approaches, and both look like they would work. Right now, I'm using option 1, but I keep wondering if option 2 might be better. Whenever I change the questions, I currently have to drop and recreate the table (altering it is too much effort), and I know option 2 would be a way of avoiding that. By the way, the questions themselves are stored in an xml file, if it means anything. Anyhow, once the survey is being used, there shouldn't be any further changing of questions. And there's also just too much I don't know about (how is performance affected, for example?).

Any ideas which is better and why?if you're currently doing it like option 1, then presumably you are able to write some sql for it?

okay, a couple of sample problems, may we see your sql please

1) which surveys had more than half the questions answered correctly
2) which surveys had the same number of answers as survey 23|||None of the surveys have been answered already. I'm still writing the code that will store the answers given. The questions themselves aren't final yet, but the xml file is getting constantly updated as we decided what to ask and not to ask. So when the xml file is changed, I generate a new (rather large) Create Table statement, drop the old table, and create the new one so things can be tested out. It's just extra work while developing the survey.

When a new survey is started, a new record is created for it right from the beginning, with all columns set to null. As questions get answered, values get inserted. This way, it's possible to come back later for whatever reason.

I don't see how posting the entire Create Table statement helps anything. The questions' answers are stored as bits, ints, reals, datetimes, or varchars, depending on the kinds of questions. Unanswered questions remain null, of course. It looks like there are about 160 columns in the table right now that are specifically for answers to the questions.

Really, my question simply is am I going to be ok with having a large number of columns in the table, or am I better off using what I mentioned above as "option 2", reducing the number of columns in the table.|||Think about R937's question a little more. Suppose you inherited a system like your option 1. Further, suppose you were given requirements to write reports based on R937's questions. How would you go about it?

Design questions need to take into account how you store the data AND how you get it back out. Unless of course, you are designing a black hole, in which case, it does not matter how to get data out.|||didn't want to see the CREATE TABLE statement

wanted you to think about the SQL that you would have to write against the humungous table to get meaningful stats out of it

my advice: use whichever structure you feel most comfortab le with|||oh...so in your opinion, it doesn't really matter? I was wondering if maybe one way was technically superior and/or more efficient for some reason that I may not have already seen.|||oh...so in your opinion, it doesn't really matter? no, i didn't really say that :)

i think 1NF might be applicable here

actually, i would almost always implement option 2

but since you were asking for advice, and since you seem to have settled on option 1 already, i figured it was important to let you know that being comfortable with a technique also has some importance|||Whenever I change the questions, I currently have to drop and recreate the table (altering it is too much effort)Learn ALTER TABLE syntax - it is substantially less effort than the method you describe - especially once you have data in your tables.|||You have to go option 2. There is no other way...Don't even consider option 1 - total train wreck in the making.

(sorry to diffuse the suspense created by r937 but it was driving me nuts!)|||my current project is both 1 and 2 because we are processing gigs of claims every week. 1 for OLAP and 2 for OLTP. So my answer is both.sql

Has anyone successfully called a table from a chart control?

The chart control likes to call one data source.
We have a table where there are three columns and each one is a different
data source.
Want to base the chart on all three columns.
Dundas has said that you can't do this out of the box, but in yukon there
are new controls.
What can be done in SQL Server 2000 Reporting services to address this issue?
Regards,Create a view on one data source that includes tables from the other two data
sources. In your dataset query in RS, just reference the one view.
Charles Kangai, MCDBA, MCT
"Smartbiz" wrote:
> The chart control likes to call one data source.
> We have a table where there are three columns and each one is a different
> data source.
> Want to base the chart on all three columns.
> Dundas has said that you can't do this out of the box, but in yukon there
> are new controls.
> What can be done in SQL Server 2000 Reporting services to address this issue?
> Regards,sql

Has anyone ever seen this error before?

A google search turned up nothing when I searched for this.
Is it saying that if my total VARCHARs for all columns in a row add up
to more than 8060, then it can't do it?
I've never heard of such a limitation before!
ALTER TABLE dat_table ADD some_stuff VARCHAR(4000) DEFAULT NULL NULL;
Warning: The table 'dat_table' has been created but its maximum row
size (17500) exceeds the maximum number of bytes per row (8060). INSERT
or UPDATE of a row in this table will fail if the resulting row length
exceeds 8060 bytes.
Does any guru have knowledge of this?
Thanks,
Stewart>A google search turned up nothing when I searched for this.
You should have gotten many hits. Perhaps you inadvertently included the
table name or maximum row size in the search. This is also well documented
in the SQL Server Books Online.
> Is it saying that if my total VARCHARs for all columns in a row add up
> to more than 8060, then it can't do it?
Not exactly. The ALTER TABLE succeeds but the warning message indicates
that the total row size has the *potential* to exceed the 8060 max. If you
later try to insert a row that requires more than 8060 bytes of actual data,
the insert will fail. Other inserts will succeed.
BTW, SQL 2005 provides some relief. When the combined widths exceed the
8060, overflow data are stored separately. However, there more I/O is
required since more pages need to be accessed.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Stewart" <stewart.cambridge@.gmail.com> wrote in message
news:1154604543.119450.193230@.75g2000cwc.googlegroups.com...
>A google search turned up nothing when I searched for this.
> Is it saying that if my total VARCHARs for all columns in a row add up
> to more than 8060, then it can't do it?
> I've never heard of such a limitation before!
> ALTER TABLE dat_table ADD some_stuff VARCHAR(4000) DEFAULT NULL NULL;
> Warning: The table 'dat_table' has been created but its maximum row
> size (17500) exceeds the maximum number of bytes per row (8060). INSERT
> or UPDATE of a row in this table will fail if the resulting row length
> exceeds 8060 bytes.
> Does any guru have knowledge of this?
> Thanks,
> Stewart
>|||Thank you Dan.
Useful to know.
Dan Guzman wrote:
> >A google search turned up nothing when I searched for this.
> You should have gotten many hits. Perhaps you inadvertently included the
> table name or maximum row size in the search. This is also well documented
> in the SQL Server Books Online.
> > Is it saying that if my total VARCHARs for all columns in a row add up
> > to more than 8060, then it can't do it?
> Not exactly. The ALTER TABLE succeeds but the warning message indicates
> that the total row size has the *potential* to exceed the 8060 max. If you
> later try to insert a row that requires more than 8060 bytes of actual data,
> the insert will fail. Other inserts will succeed.
> BTW, SQL 2005 provides some relief. When the combined widths exceed the
> 8060, overflow data are stored separately. However, there more I/O is
> required since more pages need to be accessed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>|||From: S Karthikeyan
Hi,
SQL Server can only have 8060 bytes in a row. Incase you want to exceed
this
limit try using image, text etc as the data types.
This is because a SQL data page is 8k in size i.e. 8192 bytes...
Approximately 132 bytes are used for storing information about the rows
like
headers, offset etc.
But if you try to insert/update with more than 8060 bytes in a row, SQL
will
automatically truncate the row to fit within 8060 bytes.
Thank you.
Regards,
Karthik|||> But if you try to insert/update with more than 8060 bytes in a row, SQL
> will
> automatically truncate the row to fit within 8060 bytes.
No, this is not the case. The insert will fail if you try to insert a row
with more than 8060 bytes (including overhead):
CREATE TABLE dbo.BigRowTable
(
Col1 varchar(8000),
Col2 varchar(8000)
)
--no problem
INSERT INTO dbo.BigRowTable (col1, col2)
VALUES(REPLICATE('x', 8000), REPLICATE('x', 47))
--fails
INSERT INTO dbo.BigRowTable (col1, col2)
VALUES(REPLICATE('x', 8000), REPLICATE('x', 48))
SELECT COUNT(*) FROM dbo.BigRowTable
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Stewart" <stewart.cambridge@.gmail.com> wrote in message
news:1154686180.641004.144020@.b28g2000cwb.googlegroups.com...
> From: S Karthikeyan
> Hi,
> SQL Server can only have 8060 bytes in a row. Incase you want to exceed
> this
> limit try using image, text etc as the data types.
> This is because a SQL data page is 8k in size i.e. 8192 bytes...
> Approximately 132 bytes are used for storing information about the rows
> like
> headers, offset etc.
> But if you try to insert/update with more than 8060 bytes in a row, SQL
> will
> automatically truncate the row to fit within 8060 bytes.
> Thank you.
> Regards,
> Karthik
>

Has anyone ever seen this error before?

A google search turned up nothing when I searched for this.
Is it saying that if my total VARCHARs for all columns in a row add up
to more than 8060, then it can't do it?
I've never heard of such a limitation before!
ALTER TABLE dat_table ADD some_stuff VARCHAR(4000) DEFAULT NULL NULL;
Warning: The table 'dat_table' has been created but its maximum row
size (17500) exceeds the maximum number of bytes per row (8060). INSERT
or UPDATE of a row in this table will fail if the resulting row length
exceeds 8060 bytes.
Does any guru have knowledge of this?
Thanks,
Stewart>A google search turned up nothing when I searched for this.
You should have gotten many hits. Perhaps you inadvertently included the
table name or maximum row size in the search. This is also well documented
in the SQL Server Books Online.

> Is it saying that if my total VARCHARs for all columns in a row add up
> to more than 8060, then it can't do it?
Not exactly. The ALTER TABLE succeeds but the warning message indicates
that the total row size has the *potential* to exceed the 8060 max. If you
later try to insert a row that requires more than 8060 bytes of actual data,
the insert will fail. Other inserts will succeed.
BTW, SQL 2005 provides some relief. When the combined widths exceed the
8060, overflow data are stored separately. However, there more I/O is
required since more pages need to be accessed.
Hope this helps.
Dan Guzman
SQL Server MVP
"Stewart" <stewart.cambridge@.gmail.com> wrote in message
news:1154604543.119450.193230@.75g2000cwc.googlegroups.com...
>A google search turned up nothing when I searched for this.
> Is it saying that if my total VARCHARs for all columns in a row add up
> to more than 8060, then it can't do it?
> I've never heard of such a limitation before!
> ALTER TABLE dat_table ADD some_stuff VARCHAR(4000) DEFAULT NULL NULL;
> Warning: The table 'dat_table' has been created but its maximum row
> size (17500) exceeds the maximum number of bytes per row (8060). INSERT
> or UPDATE of a row in this table will fail if the resulting row length
> exceeds 8060 bytes.
> Does any guru have knowledge of this?
> Thanks,
> Stewart
>|||Thank you Dan.
Useful to know.
Dan Guzman wrote:
> You should have gotten many hits. Perhaps you inadvertently included the
> table name or maximum row size in the search. This is also well documente
d
> in the SQL Server Books Online.
>
> Not exactly. The ALTER TABLE succeeds but the warning message indicates
> that the total row size has the *potential* to exceed the 8060 max. If yo
u
> later try to insert a row that requires more than 8060 bytes of actual dat
a,
> the insert will fail. Other inserts will succeed.
> BTW, SQL 2005 provides some relief. When the combined widths exceed the
> 8060, overflow data are stored separately. However, there more I/O is
> required since more pages need to be accessed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>|||From: S Karthikeyan
Hi,
SQL Server can only have 8060 bytes in a row. Incase you want to exceed
this
limit try using image, text etc as the data types.
This is because a SQL data page is 8k in size i.e. 8192 bytes...
Approximately 132 bytes are used for storing information about the rows
like
headers, offset etc.
But if you try to insert/update with more than 8060 bytes in a row, SQL
will
automatically truncate the row to fit within 8060 bytes.
Thank you.
Regards,
Karthik|||> But if you try to insert/update with more than 8060 bytes in a row, SQL
> will
> automatically truncate the row to fit within 8060 bytes.
No, this is not the case. The insert will fail if you try to insert a row
with more than 8060 bytes (including overhead):
CREATE TABLE dbo.BigRowTable
(
Col1 varchar(8000),
Col2 varchar(8000)
)
--no problem
INSERT INTO dbo.BigRowTable (col1, col2)
VALUES(REPLICATE('x', 8000), REPLICATE('x', 47))
--fails
INSERT INTO dbo.BigRowTable (col1, col2)
VALUES(REPLICATE('x', 8000), REPLICATE('x', 48))
SELECT COUNT(*) FROM dbo.BigRowTable
Hope this helps.
Dan Guzman
SQL Server MVP
"Stewart" <stewart.cambridge@.gmail.com> wrote in message
news:1154686180.641004.144020@.b28g2000cwb.googlegroups.com...
> From: S Karthikeyan
> Hi,
> SQL Server can only have 8060 bytes in a row. Incase you want to exceed
> this
> limit try using image, text etc as the data types.
> This is because a SQL data page is 8k in size i.e. 8192 bytes...
> Approximately 132 bytes are used for storing information about the rows
> like
> headers, offset etc.
> But if you try to insert/update with more than 8060 bytes in a row, SQL
> will
> automatically truncate the row to fit within 8060 bytes.
> Thank you.
> Regards,
> Karthik
>

Handling large data values in Textboxes

Hi, Ive got a report using a List item that is vertically displaying the columns from a table. The problem I run into, is that some of the fields in this table contain large blocks of text where the users have entered comments and such.

I am using Textboxes to display this data.

So my report will look something like
--
Field label 1 Field value 1
Field label 2 Field value 2
Field label 3

<white space>

<page break>

Field value 3 > this is a big block of text
Field label 4 Field value 4
etc

It appears as though the report attempts to keep the contents of each textbox together even if that means breaking onto an entirely new page to do this. I would prefer for the data to flow more natrually instead where the page breaks in the middle of the data being displayed should it be too large to fit on the page it started on.

--
Field label 1 Field value 1
Field label 2 Field value 2
Field label 3 Field value 3 As much as can fit on this page

<page break>

Field value 3 > remaining data that broke over the page
Field label 4 Field value 4
etc

Any suggestions would be apprecaited.

Try setting KeepTogether property of your List to true so that both label and value of field3 may go to next page.

Shyam

|||

Thanks for the reply Shyam

While it would be nice to keep the label and value together, my major problem is that I want to have the report display whatever it can fit on each page of my large comment blocks, instead of breaking the entire comment onto a second page leaving me with a huge amount of white space. (see my example above for my desired output).

|||

I guess your requirement may not be possible though you can resort to some work around like the one I suggested above or even RepeatWith property of the label textbox and set to the value field name.

Shyam

Handling columns with Nvarchar value

Hai All,
can any one out there help me in the following scenario.
I have a Remarks column in a table which is NVARCHAR field and length set
4000. This column value is set by a SP. Case is that when the remarks grows
beyond 4000 , how will i handle it. The requirement is, that I should not
ignore any remarks value.
Such Larger values how will i handle.
Thanks,
V.Boomessh
Hi
Look at NTEXT datatype in BOL. After NVARCHAR(4000), with SQL Server 2000,
your only option in NTEXT.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Boomessh" <Boomessh@.discussions.microsoft.com> wrote in message
news:61F38371-3BEF-436B-9421-FF37E6C30CF0@.microsoft.com...
> Hai All,
> can any one out there help me in the following scenario.
> I have a Remarks column in a table which is NVARCHAR field and length set
> 4000. This column value is set by a SP. Case is that when the remarks
> grows
> beyond 4000 , how will i handle it. The requirement is, that I should not
> ignore any remarks value.
> Such Larger values how will i handle.
> Thanks,
> V.Boomessh
>
>

handling columns with multiple values

I am writing a stored procedure that needs a access individual entries in a column with multiple entries delimited by a comma(yeah i know, not 1st NF) . Like this:

Key

NotANormalizedCol

1

1324, 5124, 5435,5467

2

423, 23, 5345

3

52334, 53443, 1224

4

12, 4, 1243,66

is there a function that returns a substring given a delimiter character? the only substring returning function that i found are the LEFT and RIGHT that returns fixed length substring.

I am pretty new to this, so I apologize if this is a trivial questions

Look at http://www.sommarskog.se/arrays-in-sql.html|||Hi,

I once wrote a function for that which can be found here in this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=320221&SiteID=1

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

H

How do i replace that character in a derived column ?

Some rows have that character in one or more columns. If i just write this

column == "" ? Unknown : column

where the character is inside "" (can't write it here) the task will just succes without ever doing anything. The output says something like "the dataflow task had no tasks.....", which seems like a bug.

Use a conditional statement.

If [column] contains this value, replace it with another, else leave the value alone:

[column] == "A" ? "B" : [column]|||

i know but it's not a common character. Look at the Subject of this question!!!

It's a square character -> <-

It's a non XML valid character

|||

I think thats a new line character.

If you are using an OLEDB source for your data, you can just do a trim() on the column to get rid of it.

|||

If you know the Unicode character value for it, you can use an escape sequence:

"\xhhhh"

where hhhh is the Unicode character value.

Thank
Mark

|||trim() doesn't works. It's doesn't remove the character!|||

hmm and if it's not a unicode character ?

Try putting this in a derived comlumn

REPLACE(TRIM(TXT)," ","")

the task will then complete with this in the output.

Warning: 0x80047034 at Data Flow Task, DTS.Pipeline: The DataFlow task has no components. Add components or remove the task.

|||

Use the REPLACE function, and the Unicode escape sequenece syntax Mark described.

It is a unicode character, as all comparisons are done as Unicode inside the SSIS expression parser, it cannot be anything else as far as SSIS is concerned. You need to find out what that is, and specify it in the REPLACE.

|||

Well but if the character > < is used in a replace within a dataflowtask in ssis, it automatic removes whatever flow you might have build inside that dataflow. In my opinion that seems like a bug..

if you want to do a replace in a sql task, you can't use a direct input (the sql task will then complete as if nothing was typed inside the task). You have to use a file connection for the query, so it seems like this character is the character from hell... :-)

So if you don't have the escape sequenece for this character (can't find it, since you can't search for it :-) ) you'll have to load the entire table to a temp table and do an ordinary replace in a sql query

|||

jam281 wrote:

it automatic removes whatever flow you might have build inside that dataflow.

Can you elaborate on exactly what you mean by "whatever flow you might have build".

-Jamie

|||Download any one of the many free hex editors on the Internet and open up a line of your source in it. Use the hex editor to find the hex value of the character in question. Go from there in your replace function.|||

"Can you elaborate on exactly what you mean by "whatever flow you might have build".

Allright. Try to create a new package - Add a dataflow and open it. Inside the dataflow create an oledb source - point to a table and map it.

Put in a derived column and a do a replace on one of the column like:

CENTRE == " " ? "Unknown" : CENTRE

put in a ole db destination - connect all tree

try to run it and it completes without doing anything. save the package and close the project.

Open the project again and your dataflow is now suddently empty.

The same problem apply if you have copied a sql query inside a sql task and it contains somewhere. The sql task will the execute witout doing anything...

|||

hmm found out that its a char(2) character.

select char(2)

gives that character. Now how do you replace char(2) in a derived column

|||

Let me know if using Unicode escape sequence as follows works for you:

CENTRE == "\x0002" ? "Unknown" : CENTRE

Thanks
Mark

|||

Mark Durley wrote:

Let me know if using Unicode escape sequence as follows works for you:

CENTRE == "\x0002" ? "Unknown" : CENTRE

Thanks
Mark

It worked thanks!!!