How to Dynamically set the width of report body.

I have a report where we display a certain number of columns based on
some condition. If I display all the columns then the report looks okay
but if I display fewer columns then there is empty space on each row
which would have otherwise been occupied by the hidden columns of the
table. Is there any way to dynamically set the width of the table and
the report body.
Thanks and I appreciate you taking the time.
S Girase
sgirase@.gmail.comyou could try building the report with a matrix.. it'll adjust the
number of columns based on the results.

How to do this in Stored Procedure

Hi,
I want to do something like this is my SP. If ' ' emplty string parameters are sent, I want the SP to use certain default values and use them in the query. code below is a rough idea..

CREATE PROCEDURE CabsSchedule_ViewLatest
(
 @.SiteCode smallint,
 @.YearMonth int
)
AS
 IF @.YearMonth = ' ' OR @.SiteCode = ' '
BEGIN
@.YearMonth = DateTime.Now()
@.SiteCode = 'GN'
END
 SELECT JulianDate, CalendarDay, BillPeriod, WorkDay,CalDayBillRcvd, Remarks
 FROM CabsSchedule WHERE YearMonth = @.YearMonth AND SiteCode = @.SiteCode
GO

CREATE PROCEDURE CabsSchedule_ViewLatest
(
 @.SiteCode smallint = NULL,
 @.YearMonth int = NULL
)
AS
SET NOCOUNT ON
IF @.YearMonth IS NULL OR @.SiteCode IS NULL
 BEGIN
 SET @.YearMonth = Getdate()
 SET @.SiteCode = 'GN'
 END
SELECT...
SET NOCOUNT OFF

hth|||You have numeric parameters, so checking for ' ' is not meaningful. Try using default values for the SP:

CREATE PROCEDURE CabsSchedule_ViewLatest
(
 @.SiteCode smallint = -1,
 @.YearMonth int = -1
)

This presumes -1 is a reasonable default (probably not)|||Hi Dinakar,
Thanks for the reply. I want to assign the latest YearMonth available in the my table to the @.YearMonth field. The format of YearMonth would be 200408 representing Year=2004 and Month=08 respectively, could this formatting be done using T-SQL functions??

Thanks,|||yeah there are some datefunctions available in SQL Server. check out BOL. in your case you wud prbly need Month(Getdate()).

hth|||REPLACE(CONVERT(nvarchar(10),DATEPART(yy,GetDate())) + Str(DATEPART(mm,GetDate()),2),' ','0')

You could do it more easily using Month() and Year() if you always want to use todays date.|||Douglas and Dinakar,
Thanks for the replies but, What I'm really looking for is the the latest YearMonth value available in the Table field YearMonth. I need this b'cos I need to extract all the rows corresponding to the YearMonth field as all the rows in a particular schedule could be classified by this field. What this means is for the Year 2004 and Month 08 I've got the number of rows corresponding to the number of working days in month represented by 08.
So, what I need is to pull all the rows with latest YearMonth field. Any ideas on how to pull the latest group of rows classified by the YearMonth field??

Thanks|||SELECT * FROM mytable WHERE YearMonth=(SELECT MAX(YearMonth) FROM myTable)|||Thanks for the reply Doug. I'm wiht another problem now. What I'm trying to do is to pass values from a SP as alias. ie. if I come across a value -1 in one of my rows in Column Called WorkDay, Then I want to pass string 'HOLIDAY' instead. I've been playing around with the CAST and CASE statements in my SP but couldn't make them to work. Any help with this??

The check syntax throws an error saying that the syntax is incorrect near '='.

Thanks,

CREATE PROCEDURE CabsSchedule_ViewLatest
(
@.SiteCode smallint = 0,
@.YearMonth int = NULL
)
AS
IF @.YearMonth IS NULL OR @.YearMonth = 0
BEGIN
SET @.YearMonth = (SELECT MAX(YearMonth) FROM CabsSchedule)
END
SELECT CAST(BillPeriod AS VARCHAR(7)) AS NewBillPeriod=
CASE NewBillBeriod
WHEN 32 THEN 'NB'
WHEN 33 THEN 'HOLIDAY'
ELSE BillPeriod
END,
JulianDate, CalendarDay, WorkDay, CalDayBillRcvd, Remarks
FROM CabsSchedule WHERE YearMonth = @.YearMonth AND SiteCode = @.SiteCode
GO

|||What is NewBillPeriod?

SELECT
CASE NewBillBeriod
WHEN 32 THEN 'NB'
WHEN 33 THEN 'HOLIDAY'
ELSE BillPeriod
END as SomeOtherFieldName,
JulianDate, CalendarDay, WorkDay, CalDayBillRcvd, Remarks
FROM CabsSchedule WHERE YearMonth = @.YearMonth AND SiteCode = @.SiteCode

BTW, What you are doing (overloading values to mean something "special" other than what they normally mean) is a bad thing...|||Thanks Doug,
But I really neeed to use this for storing optimal data in the table. I mean just to store just 2 different kinds of strings that too which can occur only a cpl of times, I thought of having alias values. Yes it does mean that a value being stored into the table means something special, but I thought that I'm better of storing special values instead of storing strings which have few occurances.
Also, before getting to your solution, I tried this and got it working but was having a few problems accessing it from my program. The error said that a string could not be implicitly converted into a smallint. Why is it saying that??

Thanks,|||It is saying that because you are using a string where the system expects a smallint. If you have a smallint field, it cannot store "FRED" or even "123", but can store 123. A string is a string, and a smallint is a smallint, and if you are converting from one to the other, you need to do it explicitly, using a cast.|||Doug,
I had tried using CAST on the fields in which I wanted to have alias values. I did domething like this but it didn't work and was saying that there was some incorrect syntax at the '=' on the CAST line in the below code. Could this be corrected to get what I'm looking for?? Also, the error says that there is not function called VARCHAR, but this is not a function.
What is going wrong here?

CREATE PROCEDURE CabsSchedule_ViewSchedule
(
@.SiteCode smallint = 0,
@.YearMonth int = NULL
)
AS
IF @.YearMonth IS NULL OR @.YearMonth = 0
BEGIN
SET @.YearMonth = (SELECT MAX(YearMonth) FROM CabsSchedule)
END
SELECT CAST(BillPeriod AS VARCHAR(7)) =
CASE
WHEN BillPeriod = '32' THEN 'NB'
WHEN BillPeriod = '33' THEN 'Holiday'
ELSE BillPeriod
END,
CAST(WorkDay AS VARCHAR(7))=
CASE
WHEN WorkDay = '-1' THEN ''
WHEN WorkDay = '0' THEN 'Holiday'
ELSE WorkDay
END,
JulianDate, CalendarDay, CalDayBillRcvd, Remarks
FROMCabsSchedule
WHERE YearMonth = @.YearMonth AND SiteCode = @.SiteCode
GO

Thanks|||In a previous post, I gave an example of how to do what you wish (I added a CONVERT call here, since that might be required, depending upon your data):

SELECT
CASE NewBillBeriod
WHEN 32 THEN 'NB'
WHEN 33 THEN 'HOLIDAY'
ELSE CONVERT(nvarchar(20),BillPeriod)
END as SomeOtherFieldName,
JulianDate, CalendarDay, WorkDay, CalDayBillRcvd, Remarks
FROM CabsSchedule WHERE YearMonth = @.YearMonth AND SiteCode = @.SiteCode

The syntax you are continuing to try to use will NEVER work.|||Thanks Doug,
The above change in my SP worked, but I was wondering why it wouldn't work if I CAST the BillPeriod in the CASE statement itself. Since, I CAST it to VarChar, the options for assignment it would have would still be the same and I did not care about CASTING the field BillPeriod in the ELSE statement (as you did) as smallint to varchar and Vice versa conversion needs no explicit CASTING(From what I read in BOL). What am I missing in my learning here?

Thanks again!

How to do certain task in SQL?

Hi Everyone,
I come from a Foxpro/dBase background (almost 20 yrs of DBF files) and
I'm new to SQL. I've been experimenting with VB.Net and MSDE for a few
months now and I'm very impressed. I got the go ahead to convert a
major xBase application to SQL/VB.Net.
I am not sure how to do certain tasks in SQL. I need to know how to do
the following...
Move to the last record in a table (in xBase, it is "go bottom")
Move to the first record in a table (in xBase, it is "go top")
Append a new blank record to a table (in xBase, it is "append blank")
Move to the next record in a table
In the DBF world, some tasks require filtering a file (say all records
belonging to invoice <n>) and then processing each record in a loop.
This involved moving a record pointer with either a "goto" or "skip"
command. I suspect the SQL equivalent is to use the "SELECT" statement
to filter the records and then to use the "UPDATE" or "DELETE" commands
to edit the table. Do I have the right idea?
Thanks.
Richard
Hi Richard,
Don't take this personally, but from the questions you are asking, you have
a bit of a learning curve ahead of you. First you need to stop looking at
SQL data as a list of sequential records. SQL data is retrieved and
manipulated as sets of data. There really is no equivalent to "move to last
record" or first record. If you need to update or select a particular record
then you need to define your records with either a primary key or some other
unique contraint on the data and provide the SQL statement that will extract
a distinct record. I suggest that you find a good general book on SQL and
study up on relational database theory. As a recommendation "Data &
Databases: Concepts In Practice" by Joe Celko is a very good book that lays
down the basics of relational database design.
Jim
"Richard Fagen" <no_spam@.my_isp.com> wrote in message
news:u%23WzFEO$EHA.4072@.TK2MSFTNGP10.phx.gbl...
> Hi Everyone,
> I come from a Foxpro/dBase background (almost 20 yrs of DBF files) and I'm
> new to SQL. I've been experimenting with VB.Net and MSDE for a few months
> now and I'm very impressed. I got the go ahead to convert a major xBase
> application to SQL/VB.Net.
> I am not sure how to do certain tasks in SQL. I need to know how to do
> the following...
> Move to the last record in a table (in xBase, it is "go bottom")
> Move to the first record in a table (in xBase, it is "go top")
> Append a new blank record to a table (in xBase, it is "append blank")
> Move to the next record in a table
> In the DBF world, some tasks require filtering a file (say all records
> belonging to invoice <n>) and then processing each record in a loop. This
> involved moving a record pointer with either a "goto" or "skip" command.
> I suspect the SQL equivalent is to use the "SELECT" statement to filter
> the records and then to use the "UPDATE" or "DELETE" commands to edit the
> table. Do I have the right idea?
> Thanks.
> Richard
|||Hi Jim,
I'm not taking it personally, I appreciate you comments
I had a feeling that I'd have to use the "select" command to filter the
record(s) that I want to work with and then to think in terms of 'a set
of records'.
I have already defined primary keys for my imported DBF files. I know
how to 'update' information in existing records, but I was browsing the
'SQL Book Online' and couldn't find any references on how to combine
records from multiple tables. I know about the 'join' command, but my
request is a bit different. Say I had a existing table with 1000
records and I had the user input information into a similar table with
15 new records (exact same layout) and I wanted to merge the two tables
into one table with 1015 records, how would I do this?
Thanks for the recommendation, I'll check it out. Is it a general book
or one specific to MS SQL?
Richard
Jim Young wrote:
> Hi Richard,
> Don't take this personally, but from the questions you are asking, you have
> a bit of a learning curve ahead of you. First you need to stop looking at
> SQL data as a list of sequential records. SQL data is retrieved and
> manipulated as sets of data. There really is no equivalent to "move to last
> record" or first record. If you need to update or select a particular record
> then you need to define your records with either a primary key or some other
> unique contraint on the data and provide the SQL statement that will extract
> a distinct record. I suggest that you find a good general book on SQL and
> study up on relational database theory. As a recommendation "Data &
> Databases: Concepts In Practice" by Joe Celko is a very good book that lays
> down the basics of relational database design.
> Jim
|||I do not question why you have the extra table with exactly the same layout
(columns, I guess). It is very simple the do what you want. Assume, tableA
contains the 15 (or any number of) rows of user inputs and you want to add
all of them of some of them into tableB. You could use this SQL statement:
INERT INTO tableB (Col1,Col2,Col3...)
SELECT Filed1,Field2,Field3...
FROM tableA
WHERE ... /*here the WHERE clause allows you to choose what rows in tableA
being transfered to tableB.
You also can see from above SQL statement, tableA does not have to be the
same structure as tableB. You only need to make sure the fields selected in
SELECT...clause match the fields (field count and data type) those in INSERT
INTO clause.
Since you just moved to SQL Server/MSDE, I'd sit down for a couple of days
to stduy/investigete T-SQL, rather than browse SQL Book on-line for
particular processing.
"Richard Fagen" <no_spam@.my_isp.com> wrote in message
news:e6Ad#LQ$EHA.2112@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Hi Jim,
> I'm not taking it personally, I appreciate you comments
> I had a feeling that I'd have to use the "select" command to filter the
> record(s) that I want to work with and then to think in terms of 'a set
> of records'.
> I have already defined primary keys for my imported DBF files. I know
> how to 'update' information in existing records, but I was browsing the
> 'SQL Book Online' and couldn't find any references on how to combine
> records from multiple tables. I know about the 'join' command, but my
> request is a bit different. Say I had a existing table with 1000
> records and I had the user input information into a similar table with
> 15 new records (exact same layout) and I wanted to merge the two tables
> into one table with 1015 records, how would I do this?
> Thanks for the recommendation, I'll check it out. Is it a general book
> or one specific to MS SQL?
> Richard
>
>
> Jim Young wrote:
have[vbcol=seagreen]
at[vbcol=seagreen]
last[vbcol=seagreen]
record[vbcol=seagreen]
other[vbcol=seagreen]
extract[vbcol=seagreen]
and[vbcol=seagreen]
lays[vbcol=seagreen]
|||The Celko book is about general relational database theory (very little
SQL). A good book about T-SQL, the SQL variant that SQL Server uses, is "The
Guru's Guide to Transact-SQL" by Ken Henderson. Also Microsoft Press's
"Inside SQL Server 2000" is an essential book for anyone that is in the
business of working with SQL Server.
Jim
"Richard Fagen" <no_spam@.my_isp.com> wrote in message
news:e6Ad%23LQ$EHA.2112@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Hi Jim,
> I'm not taking it personally, I appreciate you comments
> I had a feeling that I'd have to use the "select" command to filter the
> record(s) that I want to work with and then to think in terms of 'a set of
> records'.
> I have already defined primary keys for my imported DBF files. I know how
> to 'update' information in existing records, but I was browsing the 'SQL
> Book Online' and couldn't find any references on how to combine records
> from multiple tables. I know about the 'join' command, but my request is
> a bit different. Say I had a existing table with 1000 records and I had
> the user input information into a similar table with 15 new records (exact
> same layout) and I wanted to merge the two tables into one table with 1015
> records, how would I do this?
> Thanks for the recommendation, I'll check it out. Is it a general book or
> one specific to MS SQL?
> Richard
>
>
> Jim Young wrote:
|||Hi Norman,
That's exact what I was looking for, thanks!
I moved over all the DBF files into SQL. That extra table was the
template user entered data into before 'posting' the transaction.
I think I'll take yours (any others) advice and read a T-SQL specific
book. The book on-line I see are a great reference AFTER one becomes
more familiar with T-SQL.
I was intending to spend at least a week or two experimenting with
various database operations using small scale example, but I think I'll
order the book first
Thanks again.
Richard
Norman Yuan wrote:
> I do not question why you have the extra table with exactly the same layout
> (columns, I guess). It is very simple the do what you want. Assume, tableA
> contains the 15 (or any number of) rows of user inputs and you want to add
> all of them of some of them into tableB. You could use this SQL statement:
> INERT INTO tableB (Col1,Col2,Col3...)
> SELECT Filed1,Field2,Field3...
> FROM tableA
> WHERE ... /*here the WHERE clause allows you to choose what rows in tableA
> being transfered to tableB.
> You also can see from above SQL statement, tableA does not have to be the
> same structure as tableB. You only need to make sure the fields selected in
> SELECT...clause match the fields (field count and data type) those in INSERT
> INTO clause.
> Since you just moved to SQL Server/MSDE, I'd sit down for a couple of days
> to stduy/investigete T-SQL, rather than browse SQL Book on-line for
> particular processing.
>
|||Hi Jim,
Thanks for the recommendations. Those two sound more like what I am
looking for. I've been using relational databases (dBase, Clipper,
Paradox, FoxPro, etc) for years, but I agree that I should get one of
the T-SQL books.
My clients are all small businesses and use SBS 2000. In otherwords, I
don't want to spend time learning about larger enterprise systems, just
small, single server systems with 5-15 users.
I went to some seminars and they gave out SQL books but they were for
large organizations (multiple servers, load balancing, forest, trees,
advance security. etc).
Are either of these books geared for the small business? Is there one
you'd think suites my needs better?
Richard
p.s. Is the MS Press author is "Delaney"? I'd like to double check.
Jim Young wrote:

> The Celko book is about general relational database theory (very little
> SQL). A good book about T-SQL, the SQL variant that SQL Server uses, is "The
> Guru's Guide to Transact-SQL" by Ken Henderson. Also Microsoft Press's
> "Inside SQL Server 2000" is an essential book for anyone that is in the
> business of working with SQL Server.
> Jim
>
|||None of these books are written to address a specific implementation. They
will serve you well, no matter what your deployment size is. There have been
a couple of books written to address MSDE specifically. One that I have is
"MSDE Bible" by IDG Books. But, MSDE is so much like SQL Server that any
book that is useful for SQL Server will do for MSDE also. Most, if not all,
of the MSDE specific information can be found in the Books Online.
Jim
"Richard Fagen" <no_spam@.my_isp.com> wrote in message
news:%23VBz2KZ$EHA.1260@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Hi Jim,
> Thanks for the recommendations. Those two sound more like what I am
> looking for. I've been using relational databases (dBase, Clipper,
> Paradox, FoxPro, etc) for years, but I agree that I should get one of the
> T-SQL books.
> My clients are all small businesses and use SBS 2000. In otherwords, I
> don't want to spend time learning about larger enterprise systems, just
> small, single server systems with 5-15 users.
> I went to some seminars and they gave out SQL books but they were for
> large organizations (multiple servers, load balancing, forest, trees,
> advance security. etc).
> Are either of these books geared for the small business? Is there one
> you'd think suites my needs better?
> Richard
> p.s. Is the MS Press author is "Delaney"? I'd like to double check.
> Jim Young wrote: