Hello,
If I have data such as:
col1 col2 col3 col4(date)
1 2 3 01/11/2005
1 2 3 02/11/2005
1 2 3 03/11/2005
1 1 2 04/11/2005
1 1 2 05/11/2005
1 1 2 06/11/2005
How can I select so that the results are
1 2 3 01/11/2005
1 1 2 04/11/2005
ie, cols 1 to 3 distinct are included and the earliest date
Thank you.
S.soc wrote on Tue, 23 May 2006 17:30:38 +0100:
> Hello,
> If I have data such as:
> col1 col2 col3 col4(date)
> 1 2 3 01/11/2005
> 1 2 3 02/11/2005
> 1 2 3 03/11/2005
> 1 1 2 04/11/2005
> 1 1 2 05/11/2005
> 1 1 2 06/11/2005
> How can I select so that the results are
> 1 2 3 01/11/2005
> 1 1 2 04/11/2005
> ie, cols 1 to 3 distinct are included and the earliest date
> Thank you.
> S.
How about
SELECT col1, col2, col3, MIN(col4) FROM data GROUP BY col1, col2, col3
you'll need to specify a sort order though, which you didn't in your post.
Dan|||You can do this with a correlated sub query:
select a.Col1, a.Col2, a.Col3, a.Col4
from SomeTable a
where col4 = (
select min(b.col4)
from SomeTable b
where b.Col1 = a.Col1
and b.Col2 = a.Col2
and b.Col3 = a.Col3
)
You can also use min in the main query, and group by the first 3 columns.
This will not work if you choose to select an additional (non-key) column,
but the first query above will. Performance wise you will have to try both
ways, but I would expect the above query to perform better, assuming you
have an index on (Col1, Col2, Col3, Col4).
Select Col1, Col2, Col3, min(Col4)
from SomeTable
group by Col1, Col2, Col3
"soc" <zxc0@.yahoo.com> wrote in message
news:Oh2i7YofGHA.1276@.TK2MSFTNGP03.phx.gbl...
> Hello,
> If I have data such as:
> col1 col2 col3 col4(date)
> 1 2 3 01/11/2005
> 1 2 3 02/11/2005
> 1 2 3 03/11/2005
> 1 1 2 04/11/2005
> 1 1 2 05/11/2005
> 1 1 2 06/11/2005
> How can I select so that the results are
> 1 2 3 01/11/2005
> 1 1 2 04/11/2005
> ie, cols 1 to 3 distinct are included and the earliest date
> Thank you.
> S.
>
>|||Thank you!
"soc" <zxc0@.yahoo.com> wrote in message
news:Oh2i7YofGHA.1276@.TK2MSFTNGP03.phx.gbl...
> Hello,
> If I have data such as:
> col1 col2 col3 col4(date)
> 1 2 3 01/11/2005
> 1 2 3 02/11/2005
> 1 2 3 03/11/2005
> 1 1 2 04/11/2005
> 1 1 2 05/11/2005
> 1 1 2 06/11/2005
> How can I select so that the results are
> 1 2 3 01/11/2005
> 1 1 2 04/11/2005
> ie, cols 1 to 3 distinct are included and the earliest date
> Thank you.
> S.
>
>
Showing posts with label date. Show all posts
Showing posts with label date. Show all posts
Sunday, March 25, 2012
Monday, March 19, 2012
Displaying the available values for a parameter from the database
hI,
My report has 4 report parameters: @.manufacturer , @.brand, @.Start Date,
@.EndDate .
I want the @.manufacturer , @.brand to hsow the drop down list with distinct
values from the db. Now when in these 2 report parameters I do , Available
Values as Non Queried and the Value and lable as manufacturer , i get the
following errors:
"The report parameter â'manucodeâ' has a DefaultValue or a ValidValue that
depends on the report parameter â'manucodeâ'. Forward dependencies are not
valid."
"The report parameter â'manucodeâ' has a DefaultValue or a ValidValue that
depends on the report parameter â'StartDateâ'. Forward dependencies are not
valid."
"The report parameter â'manucodeâ' has a DefaultValue or a ValidValue that
depends on the report parameter â'EndDateâ'. Forward dependencies are not
valid."
Please help.
Thanks
--
pmudHi,
I found the solution to that. I created 2 datasets: one for desplaying the
manufacture and one for displaying brand. Then in Report parameters Available
Values" I put these new datasets respectively for brand and manucode and
chose the value and label. :)
--
pmud
"pmud" wrote:
> hI,
>
> My report has 4 report parameters: @.manufacturer , @.brand, @.Start Date,
> @.EndDate .
> I want the @.manufacturer , @.brand to hsow the drop down list with distinct
> values from the db. Now when in these 2 report parameters I do , Available
> Values as Non Queried and the Value and lable as manufacturer , i get the
> following errors:
> "The report parameter â'manucodeâ' has a DefaultValue or a ValidValue that
> depends on the report parameter â'manucodeâ'. Forward dependencies are not
> valid."
> "The report parameter â'manucodeâ' has a DefaultValue or a ValidValue that
> depends on the report parameter â'StartDateâ'. Forward dependencies are not
> valid."
> "The report parameter â'manucodeâ' has a DefaultValue or a ValidValue that
> depends on the report parameter â'EndDateâ'. Forward dependencies are not
> valid."
> Please help.
> Thanks
> --
> pmud
My report has 4 report parameters: @.manufacturer , @.brand, @.Start Date,
@.EndDate .
I want the @.manufacturer , @.brand to hsow the drop down list with distinct
values from the db. Now when in these 2 report parameters I do , Available
Values as Non Queried and the Value and lable as manufacturer , i get the
following errors:
"The report parameter â'manucodeâ' has a DefaultValue or a ValidValue that
depends on the report parameter â'manucodeâ'. Forward dependencies are not
valid."
"The report parameter â'manucodeâ' has a DefaultValue or a ValidValue that
depends on the report parameter â'StartDateâ'. Forward dependencies are not
valid."
"The report parameter â'manucodeâ' has a DefaultValue or a ValidValue that
depends on the report parameter â'EndDateâ'. Forward dependencies are not
valid."
Please help.
Thanks
--
pmudHi,
I found the solution to that. I created 2 datasets: one for desplaying the
manufacture and one for displaying brand. Then in Report parameters Available
Values" I put these new datasets respectively for brand and manucode and
chose the value and label. :)
--
pmud
"pmud" wrote:
> hI,
>
> My report has 4 report parameters: @.manufacturer , @.brand, @.Start Date,
> @.EndDate .
> I want the @.manufacturer , @.brand to hsow the drop down list with distinct
> values from the db. Now when in these 2 report parameters I do , Available
> Values as Non Queried and the Value and lable as manufacturer , i get the
> following errors:
> "The report parameter â'manucodeâ' has a DefaultValue or a ValidValue that
> depends on the report parameter â'manucodeâ'. Forward dependencies are not
> valid."
> "The report parameter â'manucodeâ' has a DefaultValue or a ValidValue that
> depends on the report parameter â'StartDateâ'. Forward dependencies are not
> valid."
> "The report parameter â'manucodeâ' has a DefaultValue or a ValidValue that
> depends on the report parameter â'EndDateâ'. Forward dependencies are not
> valid."
> Please help.
> Thanks
> --
> pmud
Displaying same column name in the same column
Hi, I'm trying to accomplish something with this code:
SELECT ProductionOrder.PO, ProductionOrder.Part, Single.Length,
Single.Date, Pairs.Length, Pairs.Date FROM [ProductionOrder]
LEFT OUTER JOIN Pairs ON Pairs.PO = [ProductionOrder].PO AND
Pairs.Part = [ProductionOrder].Part
LEFT OUTER JOIN Single ON Single.PO = [ProductionOrder].PO AND
Single.Part = [ProductionOrder].Part
WHERE (Single.Broke='True' AND (Single.[BrokeFixed] = 'False' OR Single.[BrokeFixed] IS NULL))
OR (Pairs.Broke='True' AND (Pairs.[BrokeFixed] = 'False' OR Pairs.[BrokeFixed] IS NULL))
With this I get the following result:
PO | Part | Length | Date | Length | Date
-
602520 | 3 | 24000 | 2007-08-24 15:33:33.727 | NULL | NULL
602521 | 3 | NULL | NULL | 14550 | 2007-08-29 17:41:01.930
But what I want is:
PO | Part | Length | Date
602520 | 3 | 24000 | 2007-08-24 15:33:33.727
602521 | 3 | 14550 | 2007-08-29 17:41:01.930
How can I accomplish this? Please, any help would be very much appreciated.
Thanks a lot in advance
You can use ISNULL or COALESCE.
eg SELECT ISNULL(Single.Date, Pairs.Date) AS Date, ISNULL(Single.Length, Pairs.Length) AS length
Remember, this method will always use Single.Date unless its NULL so if both are populated, then Single.Date will be displayed. This also won't check a dependancy on the Date and Length ie its possible that you could have a Single.Date with a Pairs.Length unless you are 100% sure this situation could never occur. If it might, you may want to consider using a CASE statement to choose which values to display.
Check CASE/ISNULL/COALESCE out in more detail in Books Online.
HTH!
Thanks a lot! That's excatly what I needed. I'm not an expert T-SQL programmer so I was getting a hard time trying
to get this result. And no, they will never be both populated as there will never be the same PO number on both
Single and Pairs table. But both PO numbers will be on the ProductionOrder Table as all POs (ProductionOrders)
will be on the ProductionOrder table, but each PO has a corresponding table, defining its type with its details (Single, Pairs, Groups, and FinalProducts).
Thanks again. Both of you who tried to help me out.
Regards
Fábio
Displaying rows by month
Hi All,
I have a column in the table of type datetime.I need to get all the rows in the table but month wise.For Ex:
I have a column in the table of type datetime.I need to get all the rows in the table but month wise.For Ex:
Jan 2003
(Rows whose date is in Jan 2003)
Feb 2003
(Rows whose date is in Feb2003)
.
.
.
Jan 2004
(Rows whose date is in Jan 2004)
Feb 2004
(Rows whose date is in Feb2004)
.
.
so on...
Can any body give my SQL query to get the desired results.
Thanks a lot,
Kumar.For January, 2003:
SELECT * FROM DateSample
WHERE MONTH(DateColumn) = 1 AND YEAR(DateColumn) = 2003
For February, 2003:
SELECT * FROM DateSample
WHERE MONTH(DateColumn) = 2 AND YEAR(DateColumn) = 2003
and so on...|||you need to use just simple order by this datetime field
if I understand your question
Sunday, March 11, 2012
Displaying previous transaction result
Hi Guys,
I want to display a report like this
Date Balance on Date Credit Debit Current Balance
02/05/06 0 1200 0 1200
05/05/06 1200 500 0 1700
10/05/06 1700 0 200 1500
15/05/06 1500 200 0 1700
I mean for the new date the balnace of the previous day transaction should be displayed.
How can it be done?
Any help is greately appreciated.
Thanks in advanceIs the day's balance a value you calculate, or read straight from the database?
If the latter then use the 'Previous' function. See help.|||or
Create a formul having the code
{Credit}-{Debit}
and create running total based on that formula and use that in report
I want to display a report like this
Date Balance on Date Credit Debit Current Balance
02/05/06 0 1200 0 1200
05/05/06 1200 500 0 1700
10/05/06 1700 0 200 1500
15/05/06 1500 200 0 1700
I mean for the new date the balnace of the previous day transaction should be displayed.
How can it be done?
Any help is greately appreciated.
Thanks in advanceIs the day's balance a value you calculate, or read straight from the database?
If the latter then use the 'Previous' function. See help.|||or
Create a formul having the code
{Credit}-{Debit}
and create running total based on that formula and use that in report
displaying null dates
how to display NA in a date field if the date is NULL? Please Help.
select case when <Col Name> is null then 'NA'
else cast(<Col Name> as varchar(12))end <Col Name>
from <Table Name>
Mat
|||You can use the following query..
Select Isnull(Convert(Varchar,DateColumnName,101),'NA') From YourTable
Here the date value will be converted as US format..
If you need both time & date use the following query..
Select Isnull(Convert(Varchar,DateColumnName,101) + ' ' + Convert(Varchar,DateColumnName,114),'NA') From YourTable
You can find all the date format convertion on Books Online under the title CAST and CONVERT
Friday, March 9, 2012
Displaying max values of each group in SQL
Hello everyone;
I have here a code that displays the most recent date for each group of records. But the problem is, I am not able to include some fields of the table.
There are 3 tables named CUST, ACCT, and TRAN:
CUST:
CNO NAME
CN101 DAN
CN102 AAA
ACCT:
ANO CNO
AN101 CN101
AN102 CN102
TRAN:
TNO ANO TDATE BAL
TN101 AN101 01/25/2006 3,000
TN102 AN101 02/15/2006 5,000
TN103 AN102 02/01/2006 4,000
TN104 AN102 02/27/2006 8,000
TN105 AN102 03/18/2006 2,000
And the resultant table should look something like this:
ANO NAME TDATE BAL
AN101 AAA 02/15/2006 5,000
AN102 BBB 03/18/2006 2,000
Now, here's my code:
SELECT DISTINCT
B.NAME,
C.ANO,
MAX(A.TDATE)
FROM TRAN A,
CUST B,
ACCT C
WHERE B.CNO = C.CNO
AND C.ANO = A.ANO
GROUP BY B.CNO,
B.NAME,
C.ANO;
And the resultant table is:
NAME ANO MAX(TDATE)
AN101 AAA 02/15/2006
AN102 BBB 03/18/2006
The problem is, I want to add the field 'BAL' to the resultant table but when I insert 'BAL' to the 'SELECT' clause, the result will look something like this:
ANO NAME TDATE BAL
AN101 AAA 01/25/2006 3,000
AN101 AAA 02/15/2006 5,000
AN102 BBB 02/01/2006 4,000
AN102 BBB 02/27/2006 8,000
AN102 BBB 03/18/2006 2,000
I will really appreciate any help.
Thnks,
dan15phselect B.NAME
, C.ANO
, A.TDATE
, A.BAL
from TRAN A
inner
join ACCT C
on C.ANO = A.ANO
inner
join CUST B
on C.CNO = B.CNO
where A.TDATE
= ( select max(TDATE)
from TRAN
where ANO = A.ANO )|||Sorry for taking so looong to reply. But anyway, thanks for the help r937 (http://www.dbforums.com/member.php?find=lastposter&t=1606412). I finally made it. just made a couple of changes to the code. Actually, I'm still a newbie in SQL and havent used 'inner join' (just recently) and seldom in using inner queries. thanks a lot for the help.:D
I have here a code that displays the most recent date for each group of records. But the problem is, I am not able to include some fields of the table.
There are 3 tables named CUST, ACCT, and TRAN:
CUST:
CNO NAME
CN101 DAN
CN102 AAA
ACCT:
ANO CNO
AN101 CN101
AN102 CN102
TRAN:
TNO ANO TDATE BAL
TN101 AN101 01/25/2006 3,000
TN102 AN101 02/15/2006 5,000
TN103 AN102 02/01/2006 4,000
TN104 AN102 02/27/2006 8,000
TN105 AN102 03/18/2006 2,000
And the resultant table should look something like this:
ANO NAME TDATE BAL
AN101 AAA 02/15/2006 5,000
AN102 BBB 03/18/2006 2,000
Now, here's my code:
SELECT DISTINCT
B.NAME,
C.ANO,
MAX(A.TDATE)
FROM TRAN A,
CUST B,
ACCT C
WHERE B.CNO = C.CNO
AND C.ANO = A.ANO
GROUP BY B.CNO,
B.NAME,
C.ANO;
And the resultant table is:
NAME ANO MAX(TDATE)
AN101 AAA 02/15/2006
AN102 BBB 03/18/2006
The problem is, I want to add the field 'BAL' to the resultant table but when I insert 'BAL' to the 'SELECT' clause, the result will look something like this:
ANO NAME TDATE BAL
AN101 AAA 01/25/2006 3,000
AN101 AAA 02/15/2006 5,000
AN102 BBB 02/01/2006 4,000
AN102 BBB 02/27/2006 8,000
AN102 BBB 03/18/2006 2,000
I will really appreciate any help.
Thnks,
dan15phselect B.NAME
, C.ANO
, A.TDATE
, A.BAL
from TRAN A
inner
join ACCT C
on C.ANO = A.ANO
inner
join CUST B
on C.CNO = B.CNO
where A.TDATE
= ( select max(TDATE)
from TRAN
where ANO = A.ANO )|||Sorry for taking so looong to reply. But anyway, thanks for the help r937 (http://www.dbforums.com/member.php?find=lastposter&t=1606412). I finally made it. just made a couple of changes to the code. Actually, I'm still a newbie in SQL and havent used 'inner join' (just recently) and seldom in using inner queries. thanks a lot for the help.:D
Displaying difference between two dates in the HH:mm:ss format
I have a need to display the difference between two dates, a start date and a end date in the format HH:mm where the hours could be greater than 60.
For example:
Start Date - 30/01/2007 09:00:01
End Date - 01/02/2007 20:40:04
When i use the following code (=Fields!dateend.Value - Fields!DateStart.Value) i get 2.11:40:03 which i can easily understand, but the customer wants it as above!
I would like to be able to get it to be 59:40:03.
Any help would be much appreciated.I've worked it out like this:
First use DateDiff on the two fields using a hidden text box, then use the following code.
=IIF(Len(CStr(Fix(ReportItems!GrossTimeMins.Value/60)))= 1,
"0" + CStr(Fix(ReportItems!GrossTimeMins.Value/60)),
CStr(Fix(ReportItems!GrossTimeMins.Value/60)))
+ ":"
+ IIF(Len(CStr(Fix(ReportItems!GrossTimeMins.Value mod 60)))= 1,
"0" + CStr(Fix(ReportItems!GrossTimeMins.Value mod 60)),
CStr(Fix(ReportItems!GrossTimeMins.Value mod 60)))
If anyone thinks of a better way of doing it quite happy for you to comment.
displaying date ranges in the report header
I am using a table to structure my report.
Body of the report
Table header
Group Header
Details
Group Footer
Table Footer
Earlier I had put the report name with the company logo in the report
header section but now I moved it to the body of the report just above
the table. I am trying to display the date range next to the report
name. I am trying to use an expression saying that if the date
parameters are NULL, then print the date today() else print the two
date parameters with a hyphen seperating them. On running the report it
gives an error that I caanot use expressions in a text box or in the
report header. I am creating a variable in the dataset as a calculated
field and then setting the expression.
Can someone please help'
Regards
JaideepYes, specify the expression in a textbox (assume the textbox name is
textbox5 on the body and make the visibility property FALSE.
Next, in the report header, for the expression use:
=ReportItems!TextBox5.Value
=-Chris
"jai" <dbasybase@.gmail.com> wrote in message
news:1161364744.236416.98920@.k70g2000cwa.googlegroups.com...
>I am using a table to structure my report.
> Body of the report
> Table header
> Group Header
> Details
> Group Footer
> Table Footer
> Earlier I had put the report name with the company logo in the report
> header section but now I moved it to the body of the report just above
> the table. I am trying to display the date range next to the report
> name. I am trying to use an expression saying that if the date
> parameters are NULL, then print the date today() else print the two
> date parameters with a hyphen seperating them. On running the report it
> gives an error that I caanot use expressions in a text box or in the
> report header. I am creating a variable in the dataset as a calculated
> field and then setting the expression.
> Can someone please help'
> Regards
> Jaideep
>|||Chris,
Thanks for the help. I am trying to set this expressions and it keeps
telling me that dbnull cannot be used.
IIf((Parameters!date1.Value = System.DBNull And Parameters!date2.Value
= System.DBNull),Today(),(Parameters!date1.Value & "-" &
Parameters!date2.Value))
I have two parameters. I am using a stored proc in the background. If
ther parameters are null it gives me data for the previous day.
I need to show the report heading as the <Report name> <date>/<date1 -
date2>
I think it the system does not want to accept the system.DBNull
Regards
Jaideep
Chris Conner wrote:
> Yes, specify the expression in a textbox (assume the textbox name is
> textbox5 on the body and make the visibility property FALSE.
> Next, in the report header, for the expression use:
> =ReportItems!TextBox5.Value
> =-Chris
> "jai" <dbasybase@.gmail.com> wrote in message
> news:1161364744.236416.98920@.k70g2000cwa.googlegroups.com...
> >I am using a table to structure my report.
> >
> > Body of the report
> >
> > Table header
> > Group Header
> > Details
> > Group Footer
> > Table Footer
> >
> > Earlier I had put the report name with the company logo in the report
> > header section but now I moved it to the body of the report just above
> > the table. I am trying to display the date range next to the report
> > name. I am trying to use an expression saying that if the date
> > parameters are NULL, then print the date today() else print the two
> > date parameters with a hyphen seperating them. On running the report it
> > gives an error that I caanot use expressions in a text box or in the
> > report header. I am creating a variable in the dataset as a calculated
> > field and then setting the expression.
> >
> > Can someone please help'
> >
> > Regards
> >
> > Jaideep
> >|||I think I got it. I should use is Nothing instead of System.DBNull.
I will try what you had said.
Jaideep
jai wrote:
> Chris,
> Thanks for the help. I am trying to set this expressions and it keeps
> telling me that dbnull cannot be used.
> IIf((Parameters!date1.Value = System.DBNull And Parameters!date2.Value
> = System.DBNull),Today(),(Parameters!date1.Value & "-" &
> Parameters!date2.Value))
> I have two parameters. I am using a stored proc in the background. If
> ther parameters are null it gives me data for the previous day.
> I need to show the report heading as the <Report name> <date>/<date1 -
> date2>
> I think it the system does not want to accept the system.DBNull
> Regards
> Jaideep
> Chris Conner wrote:
> > Yes, specify the expression in a textbox (assume the textbox name is
> > textbox5 on the body and make the visibility property FALSE.
> >
> > Next, in the report header, for the expression use:
> > =ReportItems!TextBox5.Value
> >
> > =-Chris
> >
> > "jai" <dbasybase@.gmail.com> wrote in message
> > news:1161364744.236416.98920@.k70g2000cwa.googlegroups.com...
> > >I am using a table to structure my report.
> > >
> > > Body of the report
> > >
> > > Table header
> > > Group Header
> > > Details
> > > Group Footer
> > > Table Footer
> > >
> > > Earlier I had put the report name with the company logo in the report
> > > header section but now I moved it to the body of the report just above
> > > the table. I am trying to display the date range next to the report
> > > name. I am trying to use an expression saying that if the date
> > > parameters are NULL, then print the date today() else print the two
> > > date parameters with a hyphen seperating them. On running the report it
> > > gives an error that I caanot use expressions in a text box or in the
> > > report header. I am creating a variable in the dataset as a calculated
> > > field and then setting the expression.
> > >
> > > Can someone please help'
> > >
> > > Regards
> > >
> > > Jaideep
> > >|||Don't use that = use instead iif((Parameters!date1.Value = Nothing ...
to test for nullability.
=-Chris
"jai" <dbasybase@.gmail.com> wrote in message
news:1161371300.024539.176980@.k70g2000cwa.googlegroups.com...
> Chris,
> Thanks for the help. I am trying to set this expressions and it keeps
> telling me that dbnull cannot be used.
> IIf((Parameters!date1.Value = System.DBNull And Parameters!date2.Value
> = System.DBNull),Today(),(Parameters!date1.Value & "-" &
> Parameters!date2.Value))
> I have two parameters. I am using a stored proc in the background. If
> ther parameters are null it gives me data for the previous day.
> I need to show the report heading as the <Report name> <date>/<date1 -
> date2>
> I think it the system does not want to accept the system.DBNull
> Regards
> Jaideep
> Chris Conner wrote:
>> Yes, specify the expression in a textbox (assume the textbox name is
>> textbox5 on the body and make the visibility property FALSE.
>> Next, in the report header, for the expression use:
>> =ReportItems!TextBox5.Value
>> =-Chris
>> "jai" <dbasybase@.gmail.com> wrote in message
>> news:1161364744.236416.98920@.k70g2000cwa.googlegroups.com...
>> >I am using a table to structure my report.
>> >
>> > Body of the report
>> >
>> > Table header
>> > Group Header
>> > Details
>> > Group Footer
>> > Table Footer
>> >
>> > Earlier I had put the report name with the company logo in the report
>> > header section but now I moved it to the body of the report just above
>> > the table. I am trying to display the date range next to the report
>> > name. I am trying to use an expression saying that if the date
>> > parameters are NULL, then print the date today() else print the two
>> > date parameters with a hyphen seperating them. On running the report it
>> > gives an error that I caanot use expressions in a text box or in the
>> > report header. I am creating a variable in the dataset as a calculated
>> > field and then setting the expression.
>> >
>> > Can someone please help'
>> >
>> > Regards
>> >
>> > Jaideep
>> >
>|||Chris,
I created the text box in the body and set the expression which is
being displayed properly. Now I created another text box in the report
header section and in the expression I said
ReportItems!TextBox5.Value
It is giving me an error --
The Value expression for the textbox 'textbox17' contains an error:
The expression referenced a non-existing reportitem in the reportitems
collection.
Preview complete -- 0 errors, 1 warnings
I can understand the error but how do i add something to the
reportitems collection?
Jaideep
Chris Conner wrote:
> Don't use that = use instead iif((Parameters!date1.Value = Nothing ...
> to test for nullability.
> =-Chris
> "jai" <dbasybase@.gmail.com> wrote in message
> news:1161371300.024539.176980@.k70g2000cwa.googlegroups.com...
> > Chris,
> >
> > Thanks for the help. I am trying to set this expressions and it keeps
> > telling me that dbnull cannot be used.
> >
> > IIf((Parameters!date1.Value = System.DBNull And Parameters!date2.Value
> > = System.DBNull),Today(),(Parameters!date1.Value & "-" &
> > Parameters!date2.Value))
> >
> > I have two parameters. I am using a stored proc in the background. If
> > ther parameters are null it gives me data for the previous day.
> >
> > I need to show the report heading as the <Report name> <date>/<date1 -
> > date2>
> >
> > I think it the system does not want to accept the system.DBNull
> >
> > Regards
> >
> > Jaideep
> > Chris Conner wrote:
> >> Yes, specify the expression in a textbox (assume the textbox name is
> >> textbox5 on the body and make the visibility property FALSE.
> >>
> >> Next, in the report header, for the expression use:
> >> =ReportItems!TextBox5.Value
> >>
> >> =-Chris
> >>
> >> "jai" <dbasybase@.gmail.com> wrote in message
> >> news:1161364744.236416.98920@.k70g2000cwa.googlegroups.com...
> >> >I am using a table to structure my report.
> >> >
> >> > Body of the report
> >> >
> >> > Table header
> >> > Group Header
> >> > Details
> >> > Group Footer
> >> > Table Footer
> >> >
> >> > Earlier I had put the report name with the company logo in the report
> >> > header section but now I moved it to the body of the report just above
> >> > the table. I am trying to display the date range next to the report
> >> > name. I am trying to use an expression saying that if the date
> >> > parameters are NULL, then print the date today() else print the two
> >> > date parameters with a hyphen seperating them. On running the report it
> >> > gives an error that I caanot use expressions in a text box or in the
> >> > report header. I am creating a variable in the dataset as a calculated
> >> > field and then setting the expression.
> >> >
> >> > Can someone please help'
> >> >
> >> > Regards
> >> >
> >> > Jaideep
> >> >
> >|||I forgot to mention - it is CASE sensitive.. by default, the textbox names
are in lowercase. Sorry about that.
=-Chris
"jai" <dbasybase@.gmail.com> wrote in message
news:1161374030.963039.245760@.b28g2000cwb.googlegroups.com...
> Chris,
> I created the text box in the body and set the expression which is
> being displayed properly. Now I created another text box in the report
> header section and in the expression I said
> ReportItems!TextBox5.Value
> It is giving me an error --
> The Value expression for the textbox 'textbox17' contains an error:
> The expression referenced a non-existing reportitem in the reportitems
> collection.
> Preview complete -- 0 errors, 1 warnings
> I can understand the error but how do i add something to the
> reportitems collection?
> Jaideep
>
> Chris Conner wrote:
>> Don't use that = use instead iif((Parameters!date1.Value = Nothing ...
>> to test for nullability.
>> =-Chris
>> "jai" <dbasybase@.gmail.com> wrote in message
>> news:1161371300.024539.176980@.k70g2000cwa.googlegroups.com...
>> > Chris,
>> >
>> > Thanks for the help. I am trying to set this expressions and it keeps
>> > telling me that dbnull cannot be used.
>> >
>> > IIf((Parameters!date1.Value = System.DBNull And Parameters!date2.Value
>> > = System.DBNull),Today(),(Parameters!date1.Value & "-" &
>> > Parameters!date2.Value))
>> >
>> > I have two parameters. I am using a stored proc in the background. If
>> > ther parameters are null it gives me data for the previous day.
>> >
>> > I need to show the report heading as the <Report name> <date>/<date1 -
>> > date2>
>> >
>> > I think it the system does not want to accept the system.DBNull
>> >
>> > Regards
>> >
>> > Jaideep
>> > Chris Conner wrote:
>> >> Yes, specify the expression in a textbox (assume the textbox name is
>> >> textbox5 on the body and make the visibility property FALSE.
>> >>
>> >> Next, in the report header, for the expression use:
>> >> =ReportItems!TextBox5.Value
>> >>
>> >> =-Chris
>> >>
>> >> "jai" <dbasybase@.gmail.com> wrote in message
>> >> news:1161364744.236416.98920@.k70g2000cwa.googlegroups.com...
>> >> >I am using a table to structure my report.
>> >> >
>> >> > Body of the report
>> >> >
>> >> > Table header
>> >> > Group Header
>> >> > Details
>> >> > Group Footer
>> >> > Table Footer
>> >> >
>> >> > Earlier I had put the report name with the company logo in the
>> >> > report
>> >> > header section but now I moved it to the body of the report just
>> >> > above
>> >> > the table. I am trying to display the date range next to the report
>> >> > name. I am trying to use an expression saying that if the date
>> >> > parameters are NULL, then print the date today() else print the two
>> >> > date parameters with a hyphen seperating them. On running the report
>> >> > it
>> >> > gives an error that I caanot use expressions in a text box or in the
>> >> > report header. I am creating a variable in the dataset as a
>> >> > calculated
>> >> > field and then setting the expression.
>> >> >
>> >> > Can someone please help'
>> >> >
>> >> > Regards
>> >> >
>> >> > Jaideep
>> >> >
>> >
>|||Thanks that did the trick. But I found another problem. If the date
range is one day apart then the text box displays the correct values
but if the range is more than two days, then it does not display the
values but the report returns the data.
Any ideas?
Jaideep
Chris Conner wrote:
> I forgot to mention - it is CASE sensitive.. by default, the textbox names
> are in lowercase. Sorry about that.
> =-Chris
> "jai" <dbasybase@.gmail.com> wrote in message
> news:1161374030.963039.245760@.b28g2000cwb.googlegroups.com...
> > Chris,
> > I created the text box in the body and set the expression which is
> > being displayed properly. Now I created another text box in the report
> > header section and in the expression I said
> > ReportItems!TextBox5.Value
> >
> > It is giving me an error --
> > The Value expression for the textbox 'textbox17' contains an error:
> > The expression referenced a non-existing reportitem in the reportitems
> > collection.
> > Preview complete -- 0 errors, 1 warnings
> >
> > I can understand the error but how do i add something to the
> > reportitems collection?
> >
> > Jaideep
> >
> >
> >
> > Chris Conner wrote:
> >> Don't use that = use instead iif((Parameters!date1.Value = Nothing ...
> >>
> >> to test for nullability.
> >>
> >> =-Chris
> >>
> >> "jai" <dbasybase@.gmail.com> wrote in message
> >> news:1161371300.024539.176980@.k70g2000cwa.googlegroups.com...
> >> > Chris,
> >> >
> >> > Thanks for the help. I am trying to set this expressions and it keeps
> >> > telling me that dbnull cannot be used.
> >> >
> >> > IIf((Parameters!date1.Value = System.DBNull And Parameters!date2.Value
> >> > = System.DBNull),Today(),(Parameters!date1.Value & "-" &
> >> > Parameters!date2.Value))
> >> >
> >> > I have two parameters. I am using a stored proc in the background. If
> >> > ther parameters are null it gives me data for the previous day.
> >> >
> >> > I need to show the report heading as the <Report name> <date>/<date1 -
> >> > date2>
> >> >
> >> > I think it the system does not want to accept the system.DBNull
> >> >
> >> > Regards
> >> >
> >> > Jaideep
> >> > Chris Conner wrote:
> >> >> Yes, specify the expression in a textbox (assume the textbox name is
> >> >> textbox5 on the body and make the visibility property FALSE.
> >> >>
> >> >> Next, in the report header, for the expression use:
> >> >> =ReportItems!TextBox5.Value
> >> >>
> >> >> =-Chris
> >> >>
> >> >> "jai" <dbasybase@.gmail.com> wrote in message
> >> >> news:1161364744.236416.98920@.k70g2000cwa.googlegroups.com...
> >> >> >I am using a table to structure my report.
> >> >> >
> >> >> > Body of the report
> >> >> >
> >> >> > Table header
> >> >> > Group Header
> >> >> > Details
> >> >> > Group Footer
> >> >> > Table Footer
> >> >> >
> >> >> > Earlier I had put the report name with the company logo in the
> >> >> > report
> >> >> > header section but now I moved it to the body of the report just
> >> >> > above
> >> >> > the table. I am trying to display the date range next to the report
> >> >> > name. I am trying to use an expression saying that if the date
> >> >> > parameters are NULL, then print the date today() else print the two
> >> >> > date parameters with a hyphen seperating them. On running the report
> >> >> > it
> >> >> > gives an error that I caanot use expressions in a text box or in the
> >> >> > report header. I am creating a variable in the dataset as a
> >> >> > calculated
> >> >> > field and then setting the expression.
> >> >> >
> >> >> > Can someone please help'
> >> >> >
> >> >> > Regards
> >> >> >
> >> >> > Jaideep
> >> >> >
> >> >
> >|||Chris,
I have been seeing that if the report is more than one page, the system
prints the date range in the report header on the last page.
I checked the properties but did not find anything unusual there.
Regards
Jaideep
jai wrote:
> Thanks that did the trick. But I found another problem. If the date
> range is one day apart then the text box displays the correct values
> but if the range is more than two days, then it does not display the
> values but the report returns the data.
> Any ideas?
> Jaideep
> Chris Conner wrote:
> > I forgot to mention - it is CASE sensitive.. by default, the textbox names
> > are in lowercase. Sorry about that.
> >
> > =-Chris
> >
> > "jai" <dbasybase@.gmail.com> wrote in message
> > news:1161374030.963039.245760@.b28g2000cwb.googlegroups.com...
> > > Chris,
> > > I created the text box in the body and set the expression which is
> > > being displayed properly. Now I created another text box in the report
> > > header section and in the expression I said
> > > ReportItems!TextBox5.Value
> > >
> > > It is giving me an error --
> > > The Value expression for the textbox 'textbox17' contains an error:
> > > The expression referenced a non-existing reportitem in the reportitems
> > > collection.
> > > Preview complete -- 0 errors, 1 warnings
> > >
> > > I can understand the error but how do i add something to the
> > > reportitems collection?
> > >
> > > Jaideep
> > >
> > >
> > >
> > > Chris Conner wrote:
> > >> Don't use that = use instead iif((Parameters!date1.Value = Nothing ...
> > >>
> > >> to test for nullability.
> > >>
> > >> =-Chris
> > >>
> > >> "jai" <dbasybase@.gmail.com> wrote in message
> > >> news:1161371300.024539.176980@.k70g2000cwa.googlegroups.com...
> > >> > Chris,
> > >> >
> > >> > Thanks for the help. I am trying to set this expressions and it keeps
> > >> > telling me that dbnull cannot be used.
> > >> >
> > >> > IIf((Parameters!date1.Value = System.DBNull And Parameters!date2.Value
> > >> > = System.DBNull),Today(),(Parameters!date1.Value & "-" &
> > >> > Parameters!date2.Value))
> > >> >
> > >> > I have two parameters. I am using a stored proc in the background. If
> > >> > ther parameters are null it gives me data for the previous day.
> > >> >
> > >> > I need to show the report heading as the <Report name> <date>/<date1 -
> > >> > date2>
> > >> >
> > >> > I think it the system does not want to accept the system.DBNull
> > >> >
> > >> > Regards
> > >> >
> > >> > Jaideep
> > >> > Chris Conner wrote:
> > >> >> Yes, specify the expression in a textbox (assume the textbox name is
> > >> >> textbox5 on the body and make the visibility property FALSE.
> > >> >>
> > >> >> Next, in the report header, for the expression use:
> > >> >> =ReportItems!TextBox5.Value
> > >> >>
> > >> >> =-Chris
> > >> >>
> > >> >> "jai" <dbasybase@.gmail.com> wrote in message
> > >> >> news:1161364744.236416.98920@.k70g2000cwa.googlegroups.com...
> > >> >> >I am using a table to structure my report.
> > >> >> >
> > >> >> > Body of the report
> > >> >> >
> > >> >> > Table header
> > >> >> > Group Header
> > >> >> > Details
> > >> >> > Group Footer
> > >> >> > Table Footer
> > >> >> >
> > >> >> > Earlier I had put the report name with the company logo in the
> > >> >> > report
> > >> >> > header section but now I moved it to the body of the report just
> > >> >> > above
> > >> >> > the table. I am trying to display the date range next to the report
> > >> >> > name. I am trying to use an expression saying that if the date
> > >> >> > parameters are NULL, then print the date today() else print the two
> > >> >> > date parameters with a hyphen seperating them. On running the report
> > >> >> > it
> > >> >> > gives an error that I caanot use expressions in a text box or in the
> > >> >> > report header. I am creating a variable in the dataset as a
> > >> >> > calculated
> > >> >> > field and then setting the expression.
> > >> >> >
> > >> >> > Can someone please help'
> > >> >> >
> > >> >> > Regards
> > >> >> >
> > >> >> > Jaideep
> > >> >> >
> > >> >
> > >
Body of the report
Table header
Group Header
Details
Group Footer
Table Footer
Earlier I had put the report name with the company logo in the report
header section but now I moved it to the body of the report just above
the table. I am trying to display the date range next to the report
name. I am trying to use an expression saying that if the date
parameters are NULL, then print the date today() else print the two
date parameters with a hyphen seperating them. On running the report it
gives an error that I caanot use expressions in a text box or in the
report header. I am creating a variable in the dataset as a calculated
field and then setting the expression.
Can someone please help'
Regards
JaideepYes, specify the expression in a textbox (assume the textbox name is
textbox5 on the body and make the visibility property FALSE.
Next, in the report header, for the expression use:
=ReportItems!TextBox5.Value
=-Chris
"jai" <dbasybase@.gmail.com> wrote in message
news:1161364744.236416.98920@.k70g2000cwa.googlegroups.com...
>I am using a table to structure my report.
> Body of the report
> Table header
> Group Header
> Details
> Group Footer
> Table Footer
> Earlier I had put the report name with the company logo in the report
> header section but now I moved it to the body of the report just above
> the table. I am trying to display the date range next to the report
> name. I am trying to use an expression saying that if the date
> parameters are NULL, then print the date today() else print the two
> date parameters with a hyphen seperating them. On running the report it
> gives an error that I caanot use expressions in a text box or in the
> report header. I am creating a variable in the dataset as a calculated
> field and then setting the expression.
> Can someone please help'
> Regards
> Jaideep
>|||Chris,
Thanks for the help. I am trying to set this expressions and it keeps
telling me that dbnull cannot be used.
IIf((Parameters!date1.Value = System.DBNull And Parameters!date2.Value
= System.DBNull),Today(),(Parameters!date1.Value & "-" &
Parameters!date2.Value))
I have two parameters. I am using a stored proc in the background. If
ther parameters are null it gives me data for the previous day.
I need to show the report heading as the <Report name> <date>/<date1 -
date2>
I think it the system does not want to accept the system.DBNull
Regards
Jaideep
Chris Conner wrote:
> Yes, specify the expression in a textbox (assume the textbox name is
> textbox5 on the body and make the visibility property FALSE.
> Next, in the report header, for the expression use:
> =ReportItems!TextBox5.Value
> =-Chris
> "jai" <dbasybase@.gmail.com> wrote in message
> news:1161364744.236416.98920@.k70g2000cwa.googlegroups.com...
> >I am using a table to structure my report.
> >
> > Body of the report
> >
> > Table header
> > Group Header
> > Details
> > Group Footer
> > Table Footer
> >
> > Earlier I had put the report name with the company logo in the report
> > header section but now I moved it to the body of the report just above
> > the table. I am trying to display the date range next to the report
> > name. I am trying to use an expression saying that if the date
> > parameters are NULL, then print the date today() else print the two
> > date parameters with a hyphen seperating them. On running the report it
> > gives an error that I caanot use expressions in a text box or in the
> > report header. I am creating a variable in the dataset as a calculated
> > field and then setting the expression.
> >
> > Can someone please help'
> >
> > Regards
> >
> > Jaideep
> >|||I think I got it. I should use is Nothing instead of System.DBNull.
I will try what you had said.
Jaideep
jai wrote:
> Chris,
> Thanks for the help. I am trying to set this expressions and it keeps
> telling me that dbnull cannot be used.
> IIf((Parameters!date1.Value = System.DBNull And Parameters!date2.Value
> = System.DBNull),Today(),(Parameters!date1.Value & "-" &
> Parameters!date2.Value))
> I have two parameters. I am using a stored proc in the background. If
> ther parameters are null it gives me data for the previous day.
> I need to show the report heading as the <Report name> <date>/<date1 -
> date2>
> I think it the system does not want to accept the system.DBNull
> Regards
> Jaideep
> Chris Conner wrote:
> > Yes, specify the expression in a textbox (assume the textbox name is
> > textbox5 on the body and make the visibility property FALSE.
> >
> > Next, in the report header, for the expression use:
> > =ReportItems!TextBox5.Value
> >
> > =-Chris
> >
> > "jai" <dbasybase@.gmail.com> wrote in message
> > news:1161364744.236416.98920@.k70g2000cwa.googlegroups.com...
> > >I am using a table to structure my report.
> > >
> > > Body of the report
> > >
> > > Table header
> > > Group Header
> > > Details
> > > Group Footer
> > > Table Footer
> > >
> > > Earlier I had put the report name with the company logo in the report
> > > header section but now I moved it to the body of the report just above
> > > the table. I am trying to display the date range next to the report
> > > name. I am trying to use an expression saying that if the date
> > > parameters are NULL, then print the date today() else print the two
> > > date parameters with a hyphen seperating them. On running the report it
> > > gives an error that I caanot use expressions in a text box or in the
> > > report header. I am creating a variable in the dataset as a calculated
> > > field and then setting the expression.
> > >
> > > Can someone please help'
> > >
> > > Regards
> > >
> > > Jaideep
> > >|||Don't use that = use instead iif((Parameters!date1.Value = Nothing ...
to test for nullability.
=-Chris
"jai" <dbasybase@.gmail.com> wrote in message
news:1161371300.024539.176980@.k70g2000cwa.googlegroups.com...
> Chris,
> Thanks for the help. I am trying to set this expressions and it keeps
> telling me that dbnull cannot be used.
> IIf((Parameters!date1.Value = System.DBNull And Parameters!date2.Value
> = System.DBNull),Today(),(Parameters!date1.Value & "-" &
> Parameters!date2.Value))
> I have two parameters. I am using a stored proc in the background. If
> ther parameters are null it gives me data for the previous day.
> I need to show the report heading as the <Report name> <date>/<date1 -
> date2>
> I think it the system does not want to accept the system.DBNull
> Regards
> Jaideep
> Chris Conner wrote:
>> Yes, specify the expression in a textbox (assume the textbox name is
>> textbox5 on the body and make the visibility property FALSE.
>> Next, in the report header, for the expression use:
>> =ReportItems!TextBox5.Value
>> =-Chris
>> "jai" <dbasybase@.gmail.com> wrote in message
>> news:1161364744.236416.98920@.k70g2000cwa.googlegroups.com...
>> >I am using a table to structure my report.
>> >
>> > Body of the report
>> >
>> > Table header
>> > Group Header
>> > Details
>> > Group Footer
>> > Table Footer
>> >
>> > Earlier I had put the report name with the company logo in the report
>> > header section but now I moved it to the body of the report just above
>> > the table. I am trying to display the date range next to the report
>> > name. I am trying to use an expression saying that if the date
>> > parameters are NULL, then print the date today() else print the two
>> > date parameters with a hyphen seperating them. On running the report it
>> > gives an error that I caanot use expressions in a text box or in the
>> > report header. I am creating a variable in the dataset as a calculated
>> > field and then setting the expression.
>> >
>> > Can someone please help'
>> >
>> > Regards
>> >
>> > Jaideep
>> >
>|||Chris,
I created the text box in the body and set the expression which is
being displayed properly. Now I created another text box in the report
header section and in the expression I said
ReportItems!TextBox5.Value
It is giving me an error --
The Value expression for the textbox 'textbox17' contains an error:
The expression referenced a non-existing reportitem in the reportitems
collection.
Preview complete -- 0 errors, 1 warnings
I can understand the error but how do i add something to the
reportitems collection?
Jaideep
Chris Conner wrote:
> Don't use that = use instead iif((Parameters!date1.Value = Nothing ...
> to test for nullability.
> =-Chris
> "jai" <dbasybase@.gmail.com> wrote in message
> news:1161371300.024539.176980@.k70g2000cwa.googlegroups.com...
> > Chris,
> >
> > Thanks for the help. I am trying to set this expressions and it keeps
> > telling me that dbnull cannot be used.
> >
> > IIf((Parameters!date1.Value = System.DBNull And Parameters!date2.Value
> > = System.DBNull),Today(),(Parameters!date1.Value & "-" &
> > Parameters!date2.Value))
> >
> > I have two parameters. I am using a stored proc in the background. If
> > ther parameters are null it gives me data for the previous day.
> >
> > I need to show the report heading as the <Report name> <date>/<date1 -
> > date2>
> >
> > I think it the system does not want to accept the system.DBNull
> >
> > Regards
> >
> > Jaideep
> > Chris Conner wrote:
> >> Yes, specify the expression in a textbox (assume the textbox name is
> >> textbox5 on the body and make the visibility property FALSE.
> >>
> >> Next, in the report header, for the expression use:
> >> =ReportItems!TextBox5.Value
> >>
> >> =-Chris
> >>
> >> "jai" <dbasybase@.gmail.com> wrote in message
> >> news:1161364744.236416.98920@.k70g2000cwa.googlegroups.com...
> >> >I am using a table to structure my report.
> >> >
> >> > Body of the report
> >> >
> >> > Table header
> >> > Group Header
> >> > Details
> >> > Group Footer
> >> > Table Footer
> >> >
> >> > Earlier I had put the report name with the company logo in the report
> >> > header section but now I moved it to the body of the report just above
> >> > the table. I am trying to display the date range next to the report
> >> > name. I am trying to use an expression saying that if the date
> >> > parameters are NULL, then print the date today() else print the two
> >> > date parameters with a hyphen seperating them. On running the report it
> >> > gives an error that I caanot use expressions in a text box or in the
> >> > report header. I am creating a variable in the dataset as a calculated
> >> > field and then setting the expression.
> >> >
> >> > Can someone please help'
> >> >
> >> > Regards
> >> >
> >> > Jaideep
> >> >
> >|||I forgot to mention - it is CASE sensitive.. by default, the textbox names
are in lowercase. Sorry about that.
=-Chris
"jai" <dbasybase@.gmail.com> wrote in message
news:1161374030.963039.245760@.b28g2000cwb.googlegroups.com...
> Chris,
> I created the text box in the body and set the expression which is
> being displayed properly. Now I created another text box in the report
> header section and in the expression I said
> ReportItems!TextBox5.Value
> It is giving me an error --
> The Value expression for the textbox 'textbox17' contains an error:
> The expression referenced a non-existing reportitem in the reportitems
> collection.
> Preview complete -- 0 errors, 1 warnings
> I can understand the error but how do i add something to the
> reportitems collection?
> Jaideep
>
> Chris Conner wrote:
>> Don't use that = use instead iif((Parameters!date1.Value = Nothing ...
>> to test for nullability.
>> =-Chris
>> "jai" <dbasybase@.gmail.com> wrote in message
>> news:1161371300.024539.176980@.k70g2000cwa.googlegroups.com...
>> > Chris,
>> >
>> > Thanks for the help. I am trying to set this expressions and it keeps
>> > telling me that dbnull cannot be used.
>> >
>> > IIf((Parameters!date1.Value = System.DBNull And Parameters!date2.Value
>> > = System.DBNull),Today(),(Parameters!date1.Value & "-" &
>> > Parameters!date2.Value))
>> >
>> > I have two parameters. I am using a stored proc in the background. If
>> > ther parameters are null it gives me data for the previous day.
>> >
>> > I need to show the report heading as the <Report name> <date>/<date1 -
>> > date2>
>> >
>> > I think it the system does not want to accept the system.DBNull
>> >
>> > Regards
>> >
>> > Jaideep
>> > Chris Conner wrote:
>> >> Yes, specify the expression in a textbox (assume the textbox name is
>> >> textbox5 on the body and make the visibility property FALSE.
>> >>
>> >> Next, in the report header, for the expression use:
>> >> =ReportItems!TextBox5.Value
>> >>
>> >> =-Chris
>> >>
>> >> "jai" <dbasybase@.gmail.com> wrote in message
>> >> news:1161364744.236416.98920@.k70g2000cwa.googlegroups.com...
>> >> >I am using a table to structure my report.
>> >> >
>> >> > Body of the report
>> >> >
>> >> > Table header
>> >> > Group Header
>> >> > Details
>> >> > Group Footer
>> >> > Table Footer
>> >> >
>> >> > Earlier I had put the report name with the company logo in the
>> >> > report
>> >> > header section but now I moved it to the body of the report just
>> >> > above
>> >> > the table. I am trying to display the date range next to the report
>> >> > name. I am trying to use an expression saying that if the date
>> >> > parameters are NULL, then print the date today() else print the two
>> >> > date parameters with a hyphen seperating them. On running the report
>> >> > it
>> >> > gives an error that I caanot use expressions in a text box or in the
>> >> > report header. I am creating a variable in the dataset as a
>> >> > calculated
>> >> > field and then setting the expression.
>> >> >
>> >> > Can someone please help'
>> >> >
>> >> > Regards
>> >> >
>> >> > Jaideep
>> >> >
>> >
>|||Thanks that did the trick. But I found another problem. If the date
range is one day apart then the text box displays the correct values
but if the range is more than two days, then it does not display the
values but the report returns the data.
Any ideas?
Jaideep
Chris Conner wrote:
> I forgot to mention - it is CASE sensitive.. by default, the textbox names
> are in lowercase. Sorry about that.
> =-Chris
> "jai" <dbasybase@.gmail.com> wrote in message
> news:1161374030.963039.245760@.b28g2000cwb.googlegroups.com...
> > Chris,
> > I created the text box in the body and set the expression which is
> > being displayed properly. Now I created another text box in the report
> > header section and in the expression I said
> > ReportItems!TextBox5.Value
> >
> > It is giving me an error --
> > The Value expression for the textbox 'textbox17' contains an error:
> > The expression referenced a non-existing reportitem in the reportitems
> > collection.
> > Preview complete -- 0 errors, 1 warnings
> >
> > I can understand the error but how do i add something to the
> > reportitems collection?
> >
> > Jaideep
> >
> >
> >
> > Chris Conner wrote:
> >> Don't use that = use instead iif((Parameters!date1.Value = Nothing ...
> >>
> >> to test for nullability.
> >>
> >> =-Chris
> >>
> >> "jai" <dbasybase@.gmail.com> wrote in message
> >> news:1161371300.024539.176980@.k70g2000cwa.googlegroups.com...
> >> > Chris,
> >> >
> >> > Thanks for the help. I am trying to set this expressions and it keeps
> >> > telling me that dbnull cannot be used.
> >> >
> >> > IIf((Parameters!date1.Value = System.DBNull And Parameters!date2.Value
> >> > = System.DBNull),Today(),(Parameters!date1.Value & "-" &
> >> > Parameters!date2.Value))
> >> >
> >> > I have two parameters. I am using a stored proc in the background. If
> >> > ther parameters are null it gives me data for the previous day.
> >> >
> >> > I need to show the report heading as the <Report name> <date>/<date1 -
> >> > date2>
> >> >
> >> > I think it the system does not want to accept the system.DBNull
> >> >
> >> > Regards
> >> >
> >> > Jaideep
> >> > Chris Conner wrote:
> >> >> Yes, specify the expression in a textbox (assume the textbox name is
> >> >> textbox5 on the body and make the visibility property FALSE.
> >> >>
> >> >> Next, in the report header, for the expression use:
> >> >> =ReportItems!TextBox5.Value
> >> >>
> >> >> =-Chris
> >> >>
> >> >> "jai" <dbasybase@.gmail.com> wrote in message
> >> >> news:1161364744.236416.98920@.k70g2000cwa.googlegroups.com...
> >> >> >I am using a table to structure my report.
> >> >> >
> >> >> > Body of the report
> >> >> >
> >> >> > Table header
> >> >> > Group Header
> >> >> > Details
> >> >> > Group Footer
> >> >> > Table Footer
> >> >> >
> >> >> > Earlier I had put the report name with the company logo in the
> >> >> > report
> >> >> > header section but now I moved it to the body of the report just
> >> >> > above
> >> >> > the table. I am trying to display the date range next to the report
> >> >> > name. I am trying to use an expression saying that if the date
> >> >> > parameters are NULL, then print the date today() else print the two
> >> >> > date parameters with a hyphen seperating them. On running the report
> >> >> > it
> >> >> > gives an error that I caanot use expressions in a text box or in the
> >> >> > report header. I am creating a variable in the dataset as a
> >> >> > calculated
> >> >> > field and then setting the expression.
> >> >> >
> >> >> > Can someone please help'
> >> >> >
> >> >> > Regards
> >> >> >
> >> >> > Jaideep
> >> >> >
> >> >
> >|||Chris,
I have been seeing that if the report is more than one page, the system
prints the date range in the report header on the last page.
I checked the properties but did not find anything unusual there.
Regards
Jaideep
jai wrote:
> Thanks that did the trick. But I found another problem. If the date
> range is one day apart then the text box displays the correct values
> but if the range is more than two days, then it does not display the
> values but the report returns the data.
> Any ideas?
> Jaideep
> Chris Conner wrote:
> > I forgot to mention - it is CASE sensitive.. by default, the textbox names
> > are in lowercase. Sorry about that.
> >
> > =-Chris
> >
> > "jai" <dbasybase@.gmail.com> wrote in message
> > news:1161374030.963039.245760@.b28g2000cwb.googlegroups.com...
> > > Chris,
> > > I created the text box in the body and set the expression which is
> > > being displayed properly. Now I created another text box in the report
> > > header section and in the expression I said
> > > ReportItems!TextBox5.Value
> > >
> > > It is giving me an error --
> > > The Value expression for the textbox 'textbox17' contains an error:
> > > The expression referenced a non-existing reportitem in the reportitems
> > > collection.
> > > Preview complete -- 0 errors, 1 warnings
> > >
> > > I can understand the error but how do i add something to the
> > > reportitems collection?
> > >
> > > Jaideep
> > >
> > >
> > >
> > > Chris Conner wrote:
> > >> Don't use that = use instead iif((Parameters!date1.Value = Nothing ...
> > >>
> > >> to test for nullability.
> > >>
> > >> =-Chris
> > >>
> > >> "jai" <dbasybase@.gmail.com> wrote in message
> > >> news:1161371300.024539.176980@.k70g2000cwa.googlegroups.com...
> > >> > Chris,
> > >> >
> > >> > Thanks for the help. I am trying to set this expressions and it keeps
> > >> > telling me that dbnull cannot be used.
> > >> >
> > >> > IIf((Parameters!date1.Value = System.DBNull And Parameters!date2.Value
> > >> > = System.DBNull),Today(),(Parameters!date1.Value & "-" &
> > >> > Parameters!date2.Value))
> > >> >
> > >> > I have two parameters. I am using a stored proc in the background. If
> > >> > ther parameters are null it gives me data for the previous day.
> > >> >
> > >> > I need to show the report heading as the <Report name> <date>/<date1 -
> > >> > date2>
> > >> >
> > >> > I think it the system does not want to accept the system.DBNull
> > >> >
> > >> > Regards
> > >> >
> > >> > Jaideep
> > >> > Chris Conner wrote:
> > >> >> Yes, specify the expression in a textbox (assume the textbox name is
> > >> >> textbox5 on the body and make the visibility property FALSE.
> > >> >>
> > >> >> Next, in the report header, for the expression use:
> > >> >> =ReportItems!TextBox5.Value
> > >> >>
> > >> >> =-Chris
> > >> >>
> > >> >> "jai" <dbasybase@.gmail.com> wrote in message
> > >> >> news:1161364744.236416.98920@.k70g2000cwa.googlegroups.com...
> > >> >> >I am using a table to structure my report.
> > >> >> >
> > >> >> > Body of the report
> > >> >> >
> > >> >> > Table header
> > >> >> > Group Header
> > >> >> > Details
> > >> >> > Group Footer
> > >> >> > Table Footer
> > >> >> >
> > >> >> > Earlier I had put the report name with the company logo in the
> > >> >> > report
> > >> >> > header section but now I moved it to the body of the report just
> > >> >> > above
> > >> >> > the table. I am trying to display the date range next to the report
> > >> >> > name. I am trying to use an expression saying that if the date
> > >> >> > parameters are NULL, then print the date today() else print the two
> > >> >> > date parameters with a hyphen seperating them. On running the report
> > >> >> > it
> > >> >> > gives an error that I caanot use expressions in a text box or in the
> > >> >> > report header. I am creating a variable in the dataset as a
> > >> >> > calculated
> > >> >> > field and then setting the expression.
> > >> >> >
> > >> >> > Can someone please help'
> > >> >> >
> > >> >> > Regards
> > >> >> >
> > >> >> > Jaideep
> > >> >> >
> > >> >
> > >
Wednesday, March 7, 2012
Displaying Date in SQL View
Hi,
I am a preety newbie to SQL and was wondering how to get the date from the SQL to show up as Format(Date, "dd/mm/yyyy").
TIA.
RohitIt depends on your DBMS. For Oracle, it is TO_CHAR( date, 'DD/MM/YYYY' )|||Thanks a lot sir,
My problem was to get this value somehow.
Thanks a lot for your help.
rohit|||if it is SQL Server you can use Convert function to convert the date into any format for ex: Select Convert(Varchar(10), GetDate() ,101)
will give you the date in mm/dd/yyyy format.
it depends upon the No you provide in the convert function, 101 stands for "mm/dd/yyyy" format
Balaji
:)
I am a preety newbie to SQL and was wondering how to get the date from the SQL to show up as Format(Date, "dd/mm/yyyy").
TIA.
RohitIt depends on your DBMS. For Oracle, it is TO_CHAR( date, 'DD/MM/YYYY' )|||Thanks a lot sir,
My problem was to get this value somehow.
Thanks a lot for your help.
rohit|||if it is SQL Server you can use Convert function to convert the date into any format for ex: Select Convert(Varchar(10), GetDate() ,101)
will give you the date in mm/dd/yyyy format.
it depends upon the No you provide in the convert function, 101 stands for "mm/dd/yyyy" format
Balaji
:)
Displaying data from a certain date range
Hey all, hopefully this question is in the right spot. I'm writing a .NET app talking to a MS SQL 2000 DB. I have two date range input boxes, and I want to display the data (probably in a dataGrid) from those 2 certain dates. How do I go about this with my SQL server??
My DB table has a date field that I would use to search for the data between those two user specified dates. Any tips, examples, etc. would be greatly appreciated!!!Hows about...
USE Northwind
GO
-- Your text boxes
DECLARE @.x datetime, @.y datetime SELECT @.x = '1996-09-01', @.y = '1996-09-30'
SELECT OrderDate, DATEDIFF(d,@.x,OrderDate), DATEDIFF(d,OrderDate,@.y)
FROM Orders
WHERE DATEDIFF(d,@.x,OrderDate) > = 0 AND DATEDIFF(d,OrderDate,@.y) > = 0|||Okay, now whats up with this DECLARE @.x datetime, @.y datetime SELECT @.x = '1996-09-01', @.y = '1996-09-30'
What am I doing with my Date1.text and Date2.text input boxes.. sorry, I'm having trouble converting the code some.|||That's TSQL
The DECALRE and SELECT is to mimic the values in your text box...
Do you have sql server client tools installed?
Query Analyzer?
Your best bet would be to call a stored procedure...|||Yes, I have those tools installed, but I really don't know how to use them to my benefit.... :(|||Puttin this code in the Query analyzer shows the DATEDIFF in pink, and some of the code after that grey... I'm guessing something else needs to be used here?|||Hows about...
USE Northwind
GO
-- Your text boxes
DECLARE @.x datetime, @.y datetime SELECT @.x = '1996-09-01', @.y = '1996-09-30'
SELECT OrderDate, DATEDIFF(d,@.x,OrderDate), DATEDIFF(d,OrderDate,@.y)
FROM Orders
WHERE DATEDIFF(d,@.x,OrderDate) > = 0 AND DATEDIFF(d,OrderDate,@.y) > = 0
Brett, I am having a similar problem...can't seem to use datetime parameters with default values in a stored procedure.
This is the code I used. Basically, trying to default dates on the Begin and End Dates for the query...but when I run it, always get this error
'Syntax error converting datetime from character string.'
Is the problem in the default values or the datediff functions? Then is the solution to format the dates differently or use a cast/convert function?
Thanks for the help
Alex
CREATE PROCEDURE dbo.usp_TempTest
@.BeginDate DateTime = '11/1/2004',
@.EndDate DateTime = getdate
AS
SELECT dbo.vw_BasicAuditDetails_Complete.*
FROM dbo.vw_BasicAuditDetails_Complete
WHERE (DATEDIFF(d, @.BeginDate, Audit_TM) > 0)
AND (DATEDIFF(d, Audit_TM, @.EndDate) > 0)
GO|||Has anyone cut and pasted my code in to query analyzer and executed it?|||That's what I was trying to do earlier.... :confused: I'm not a pro with the analyzer though..|||Well, if you highlighted the text, and pasted it in to a QA (Query Analyzer) window, and the typed [CTRL]+E, the code would execut and give you this
OrderDate
---------------- ---- ----
1996-09-02 00:00:00.000 1 28
1996-09-03 00:00:00.000 2 27
1996-09-04 00:00:00.000 3 26
1996-09-05 00:00:00.000 4 25
1996-09-06 00:00:00.000 5 24
1996-09-09 00:00:00.000 8 21
1996-09-09 00:00:00.000 8 21
1996-09-10 00:00:00.000 9 20
1996-09-11 00:00:00.000 10 19
1996-09-12 00:00:00.000 11 18
1996-09-13 00:00:00.000 12 17
1996-09-16 00:00:00.000 15 14
1996-09-17 00:00:00.000 16 13
1996-09-18 00:00:00.000 17 12
1996-09-19 00:00:00.000 18 11
1996-09-20 00:00:00.000 19 10
1996-09-20 00:00:00.000 19 10
1996-09-23 00:00:00.000 22 7
1996-09-24 00:00:00.000 23 6
1996-09-25 00:00:00.000 24 5
1996-09-26 00:00:00.000 25 4
1996-09-27 00:00:00.000 26 3
1996-09-30 00:00:00.000 29 0
Which is the range of data from the sample database Northwinds Tables Orders...The USE Statement should have brought you there.
Isn't that what you want?|||OK Ok, thanks! It is working now in Northwind... :)
Some suggested I use a Stored Procedure for doing this...
""You'll want to create a Stored Procedure in the SQL Server and then pass it's result into a DataSet to use a the Source for your DataGrid.
Try a Stored Procedure like this, Changing table and field names appropriately:
Code:
------------------------
CREATE PROCEDURE sp_BetweenDates ( @.StartDate DATETIME, @.EndDate DATETIME ) AS SELECT * FROM YOURTABLENAMEHERE WHERE [YOURDATEFIELDNAMEHERE] Between @.StartDate And @.EndDate;GO
------------------------
Then you would just pass the two dates as parameters to the Stored Procedure.""
What do you think of something like this?|||Okay, i have contrusted this query that gives me what I want...
USE billing1SQL2
GO
SELECT Hours.Employ#, Hours.Purchord, Hours.Datewrk, Hours.Hourswrk, Hours.typewrk, Hours.formwrk, Hours.class, Hours.brate, PurchaseOrder.Descr, Employee.Lastname, Employee.Firstname, Employee.[Employ#] AS Expr1, Hours.[Ticket#], PurchaseOrder.Purchord AS Expr2 FROM Hours As Hours INNER JOIN PurchaseOrder As PurchaseOrder ON Hours.Purchord = PurchaseOrder.Purchord INNER JOIN Employee As Employee ON Hours.[Employ#] = Employee.[Employ#] WHERE Hours.Datewrk between '6/15/2004' And '6/28/2004' ORDER BY Hours.Datewrk, Employee.Lastname
I just need to figure out how to use it with VB.Net the best way. Suggestions?|||Long answer:
I would start from:
System.Data.SqlClient Namespace (help is available with VB.Net)
Then I would take a class or read a book about T-SQL
Same thing for SQL server.
Short answer:
Public Sub ReadMyData(myConnString As String)
Dim mySelectQuery As String = "SELECT OrderID, Customer FROM Orders"
Dim myConnection As New SqlConnection(myConnString)
Dim myCommand As New SqlCommand(mySelectQuery, myConnection)
myConnection.Open()
Dim myReader As SqlDataReader = myCommand.ExecuteReader()
Try
While myReader.Read()
Console.WriteLine((myReader.GetInt32(0).ToString & ", " & myReader.GetString(1)))
End While
Finally
' always call Close when done reading.
myReader.Close()
' always call Close when done reading.
myConnection.Close()
End Try
End Sub 'ReadMyData
(source: VB.Net help)
good luck!|||In your VB code, you can create your query like this
dim str1 as string
str1 = "exec sp_between_dates '" & Format(Text1.text, "mm/dd/yy") & _
"','" & Format(Text2.text, "mm/dd/yy") & "'"
Roshmi Choudhury|||Okay, thank you! soo I have this stored procedure:
CREATE PROCEDURE sp_BetweenDates (@.StartDate DATETIME, @.EndDate DATETIME)
AS
SELECT *
FROM Hours WHERE [DateWrk] Between @.StartDate And @.EndDate;
GO
..and this in VB.net...
Dim str1 As String
str1 = "exec sp_BetweenDates '" & Format(txtDate1.Text, "mm/dd/yy") & _
"','" & Format(txtDate2.Text, "mm/dd/yy") & "'"
Do I need to put my txtDates.text into a variable/string "StartDate" and "EndDate" and put that in my str call, like this... ??
str1 = "exec sp_BetweenDates '" & Format(StartDate, "mm/dd/yy")
I'm just unclear how the best way to get the actual date from my text box, to the stored procedure works...
My DB table has a date field that I would use to search for the data between those two user specified dates. Any tips, examples, etc. would be greatly appreciated!!!Hows about...
USE Northwind
GO
-- Your text boxes
DECLARE @.x datetime, @.y datetime SELECT @.x = '1996-09-01', @.y = '1996-09-30'
SELECT OrderDate, DATEDIFF(d,@.x,OrderDate), DATEDIFF(d,OrderDate,@.y)
FROM Orders
WHERE DATEDIFF(d,@.x,OrderDate) > = 0 AND DATEDIFF(d,OrderDate,@.y) > = 0|||Okay, now whats up with this DECLARE @.x datetime, @.y datetime SELECT @.x = '1996-09-01', @.y = '1996-09-30'
What am I doing with my Date1.text and Date2.text input boxes.. sorry, I'm having trouble converting the code some.|||That's TSQL
The DECALRE and SELECT is to mimic the values in your text box...
Do you have sql server client tools installed?
Query Analyzer?
Your best bet would be to call a stored procedure...|||Yes, I have those tools installed, but I really don't know how to use them to my benefit.... :(|||Puttin this code in the Query analyzer shows the DATEDIFF in pink, and some of the code after that grey... I'm guessing something else needs to be used here?|||Hows about...
USE Northwind
GO
-- Your text boxes
DECLARE @.x datetime, @.y datetime SELECT @.x = '1996-09-01', @.y = '1996-09-30'
SELECT OrderDate, DATEDIFF(d,@.x,OrderDate), DATEDIFF(d,OrderDate,@.y)
FROM Orders
WHERE DATEDIFF(d,@.x,OrderDate) > = 0 AND DATEDIFF(d,OrderDate,@.y) > = 0
Brett, I am having a similar problem...can't seem to use datetime parameters with default values in a stored procedure.
This is the code I used. Basically, trying to default dates on the Begin and End Dates for the query...but when I run it, always get this error
'Syntax error converting datetime from character string.'
Is the problem in the default values or the datediff functions? Then is the solution to format the dates differently or use a cast/convert function?
Thanks for the help
Alex
CREATE PROCEDURE dbo.usp_TempTest
@.BeginDate DateTime = '11/1/2004',
@.EndDate DateTime = getdate
AS
SELECT dbo.vw_BasicAuditDetails_Complete.*
FROM dbo.vw_BasicAuditDetails_Complete
WHERE (DATEDIFF(d, @.BeginDate, Audit_TM) > 0)
AND (DATEDIFF(d, Audit_TM, @.EndDate) > 0)
GO|||Has anyone cut and pasted my code in to query analyzer and executed it?|||That's what I was trying to do earlier.... :confused: I'm not a pro with the analyzer though..|||Well, if you highlighted the text, and pasted it in to a QA (Query Analyzer) window, and the typed [CTRL]+E, the code would execut and give you this
OrderDate
---------------- ---- ----
1996-09-02 00:00:00.000 1 28
1996-09-03 00:00:00.000 2 27
1996-09-04 00:00:00.000 3 26
1996-09-05 00:00:00.000 4 25
1996-09-06 00:00:00.000 5 24
1996-09-09 00:00:00.000 8 21
1996-09-09 00:00:00.000 8 21
1996-09-10 00:00:00.000 9 20
1996-09-11 00:00:00.000 10 19
1996-09-12 00:00:00.000 11 18
1996-09-13 00:00:00.000 12 17
1996-09-16 00:00:00.000 15 14
1996-09-17 00:00:00.000 16 13
1996-09-18 00:00:00.000 17 12
1996-09-19 00:00:00.000 18 11
1996-09-20 00:00:00.000 19 10
1996-09-20 00:00:00.000 19 10
1996-09-23 00:00:00.000 22 7
1996-09-24 00:00:00.000 23 6
1996-09-25 00:00:00.000 24 5
1996-09-26 00:00:00.000 25 4
1996-09-27 00:00:00.000 26 3
1996-09-30 00:00:00.000 29 0
Which is the range of data from the sample database Northwinds Tables Orders...The USE Statement should have brought you there.
Isn't that what you want?|||OK Ok, thanks! It is working now in Northwind... :)
Some suggested I use a Stored Procedure for doing this...
""You'll want to create a Stored Procedure in the SQL Server and then pass it's result into a DataSet to use a the Source for your DataGrid.
Try a Stored Procedure like this, Changing table and field names appropriately:
Code:
------------------------
CREATE PROCEDURE sp_BetweenDates ( @.StartDate DATETIME, @.EndDate DATETIME ) AS SELECT * FROM YOURTABLENAMEHERE WHERE [YOURDATEFIELDNAMEHERE] Between @.StartDate And @.EndDate;GO
------------------------
Then you would just pass the two dates as parameters to the Stored Procedure.""
What do you think of something like this?|||Okay, i have contrusted this query that gives me what I want...
USE billing1SQL2
GO
SELECT Hours.Employ#, Hours.Purchord, Hours.Datewrk, Hours.Hourswrk, Hours.typewrk, Hours.formwrk, Hours.class, Hours.brate, PurchaseOrder.Descr, Employee.Lastname, Employee.Firstname, Employee.[Employ#] AS Expr1, Hours.[Ticket#], PurchaseOrder.Purchord AS Expr2 FROM Hours As Hours INNER JOIN PurchaseOrder As PurchaseOrder ON Hours.Purchord = PurchaseOrder.Purchord INNER JOIN Employee As Employee ON Hours.[Employ#] = Employee.[Employ#] WHERE Hours.Datewrk between '6/15/2004' And '6/28/2004' ORDER BY Hours.Datewrk, Employee.Lastname
I just need to figure out how to use it with VB.Net the best way. Suggestions?|||Long answer:
I would start from:
System.Data.SqlClient Namespace (help is available with VB.Net)
Then I would take a class or read a book about T-SQL
Same thing for SQL server.
Short answer:
Public Sub ReadMyData(myConnString As String)
Dim mySelectQuery As String = "SELECT OrderID, Customer FROM Orders"
Dim myConnection As New SqlConnection(myConnString)
Dim myCommand As New SqlCommand(mySelectQuery, myConnection)
myConnection.Open()
Dim myReader As SqlDataReader = myCommand.ExecuteReader()
Try
While myReader.Read()
Console.WriteLine((myReader.GetInt32(0).ToString & ", " & myReader.GetString(1)))
End While
Finally
' always call Close when done reading.
myReader.Close()
' always call Close when done reading.
myConnection.Close()
End Try
End Sub 'ReadMyData
(source: VB.Net help)
good luck!|||In your VB code, you can create your query like this
dim str1 as string
str1 = "exec sp_between_dates '" & Format(Text1.text, "mm/dd/yy") & _
"','" & Format(Text2.text, "mm/dd/yy") & "'"
Roshmi Choudhury|||Okay, thank you! soo I have this stored procedure:
CREATE PROCEDURE sp_BetweenDates (@.StartDate DATETIME, @.EndDate DATETIME)
AS
SELECT *
FROM Hours WHERE [DateWrk] Between @.StartDate And @.EndDate;
GO
..and this in VB.net...
Dim str1 As String
str1 = "exec sp_BetweenDates '" & Format(txtDate1.Text, "mm/dd/yy") & _
"','" & Format(txtDate2.Text, "mm/dd/yy") & "'"
Do I need to put my txtDates.text into a variable/string "StartDate" and "EndDate" and put that in my str call, like this... ??
str1 = "exec sp_BetweenDates '" & Format(StartDate, "mm/dd/yy")
I'm just unclear how the best way to get the actual date from my text box, to the stored procedure works...
Saturday, February 25, 2012
Displaying A Date Prompt (Range) In Page Header
I inserted a date prompt (range) into a "Page Header" of a "Crystal Reports", but when I refreshed the report, the date prompt did not display in the "Page Header". What am I doing wrong ?Never mind, I found a solution. Below is the solution . . .
"From: " + ToText ( Minimum ( {?Prompt_Date} ) ) +
" To: " + ToText ( Maximum ( {?Prompt_Date} ) )
"From: " + ToText ( Minimum ( {?Prompt_Date} ) ) +
" To: " + ToText ( Maximum ( {?Prompt_Date} ) )
Friday, February 24, 2012
display summary week total rows from sql database
(I moved this thread from datagrid area)
I have a sql database that has individual records consisting of name, date, hours worked among other fields.
Date and name is part of a unique identifier, so there can NOT be two records for the same person for the same date.
My users need a grid view that displays days worked in ONE LINE per user. I have gotten close, but can't quite get the last part. Ive tried group by, distinct, and with rollup and no luck.
TABLE:
CODE:
Select distinct(name),
(select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Sunday')as Sunday,
(select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Monday')as Monday,
(select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Tuesday')as Tuesday,
(select(y.hours) from dbo.testtime y where y.name=YT.name AND y.hours =YT.hours and datename(dw, date)='Wednesday')as Wednesday,
(select(y.hours) from dbo.testtime y where y.name=YT.name AND y.hours =YT.hours and datename(dw, date)='Thursday')as Thursday,
(select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Friday')as Friday,
(select(y.hours) from dbo.testtime y where y.name=YT.name AND y.hours =YT.hours and datename(dw, date)='Saturday')as Saturday,
(select sum(hours)from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours) as Total
from dbo.testtime YT
group by date, name, hours
RESULTS:
cara NULL NULL NULL 4.222 NULL NULL NULL 4.222
cara NULL 2 NULL NULL NULL NULL NULL 2
cara 3.3333 NULL NULL NULL NULL NULL NULL 3.3333
dan NULL NULL NULL NULL NULL 3.123123 NULL 3.123123
dan NULL NULL NULL NULL 9.123 NULL NULL 9.123
Like I said, I am SO close, I just need it to look like;
NAME SUN MIN TU WED TH FR SA TOTAL
cara 3.333 2 4.222 9.555
dan 9.125 3.125 12.5
TIA
dan
You need to create a table variable with columns of name, Sun-Sat columns to accumulate the data into. You can then select from that table the required summary.
|||OK, sounds good.
Um, what is a table variable?
Is that like a view?
can you give me psudo-code to work with?
thanks MUCH!
Dan
||| With table create script of SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TimeData](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[Date] [datetime] NOT NULL,
[Hours] [decimal](8, 6) NOT NULL,
CONSTRAINT [PK_TimeData] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Table insert script of
INSERT INTO TimeData(Name, Date, Hours)
VALUES ('dan', CONVERT(DATETIME,'13/Dec/2012 12:00:00 AM'), 9.123) -- I am in the UK, hence the change of date format
INSERT INTO TimeData(Name, Date, Hours)
VALUES ('dan', CONVERT(DATETIME,'14/Dec/2012 12:00:00 AM'), 3.123123)
INSERT INTO TimeData(Name, Date, Hours)
VALUES ('cara', CONVERT(DATETIME,'12/Dec/2012 12:00:00 AM'), 4.222)
INSERT INTO TimeData(Name, Date, Hours)
VALUES ('cara', CONVERT(DATETIME,'16/Dec/2012 12:00:00 AM'), 3.3333)
INSERT INTO TimeData(Name, Date, Hours)
VALUES ('cara', CONVERT(DATETIME,'17/Dec/2012 12:00:00 AM'), 2)
The TSQL
DECLARE @.MyTableVar table(
[Name] VARCHAR(5) NOT NULL,
Sun [decimal](8, 6) NOT NULL DEFAULT ((0)),
Mon [decimal](8, 6) NOT NULL DEFAULT ((0)),
Tue [decimal](8, 6) NOT NULL DEFAULT ((0)),
Wed [decimal](8, 6) NOT NULL DEFAULT ((0)),
Thu [decimal](8, 6) NOT NULL DEFAULT ((0)),
Fri [decimal](8, 6) NOT NULL DEFAULT ((0)),
Sat [decimal](8, 6) NOT NULL DEFAULT ((0)),
Total [decimal](8, 6) NOT NULL DEFAULT ((0))
);
DECLARE @.NAME VARCHAR(50)
DECLARE @.DATE DATETIME
DECLARE @.HOURS decimal(8, 6)
DECLARE xCURSOR CURSOR FOR
SELECT Name, Date, Hours FROM TimeData
OPEN xCURSOR
FETCH xCURSOR INTO @.NAME, @.DATE, @.HOURS
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF NOT EXISTS(SELECT * FROM @.MyTableVar WHERE [Name] = @.NAME)
INSERT INTO @.MyTableVar([Name]) VALUES (@.NAME)
IF datename(dw, @.DATE)='Sunday'
UPDATE @.MyTableVar SET Sun = Sun + @.HOURS, Total = Total + @.HOURS WHERE [Name] = @.NAME
IF datename(dw, @.DATE)='Monday'
UPDATE @.MyTableVar SET Mon = Mon + @.HOURS, Total = Total + @.HOURS WHERE [Name] = @.NAME
IF datename(dw, @.DATE)='Tuesday'
UPDATE @.MyTableVar SET Tue = Tue + @.HOURS, Total = Total + @.HOURS WHERE [Name] = @.NAME
IF datename(dw, @.DATE)='Wednesday'
UPDATE @.MyTableVar SET Wed = Wed + @.HOURS, Total = Total + @.HOURS WHERE [Name] = @.NAME
IF datename(dw, @.DATE)='Thursday'
UPDATE @.MyTableVar SET Thu = Thu + @.HOURS, Total = Total + @.HOURS WHERE [Name] = @.NAME
IF datename(dw, @.DATE)='Friday'
UPDATE @.MyTableVar SET Fri = Fri + @.HOURS, Total = Total + @.HOURS WHERE [Name] = @.NAME
IF datename(dw, @.DATE)='Saturday'
UPDATE @.MyTableVar SET Sat = Sat + @.HOURS, Total = Total + @.HOURS WHERE [Name] = @.NAME
FETCH xCURSOR INTO @.NAME, @.DATE, @.HOURS
END
SELECT * FROM @.MyTableVar
CLOSE xCURSOR
DEALLOCATE xCURSOR
gives
Name Sun Mon Tue Wed Thu Fri Sat Total
-- --- --- --- --- --- --- --- ---
dan 0.000000 0.000000 0.000000 0.000000 9.123000 3.123123 0.000000 12.246123
cara 3.333300 2.000000 0.000000 4.222000 0.000000 0.000000 0.000000 9.555300
Obviously a Cursor is not particularly efficient and needs to be eliminated. Also the Name column would need to be indexed (if possible) if there are more than 10 rows.
TAT~
THat is awsome!
Thank you SO much for your efforts.
I ventured out on my own and came up with the following (I actually changed it to look at a test/prod table, so name is UserName)
But the code actually WORKEd!
Here it is, if youd care to comment:
--make var tqable
Declare @.tempweek TABLE
(UserName nvarchar(50), Sunday DECIMAL(8,6), Monday DECIMAL(8,6), Tuesday DECIMAL(8,6), Wednesday DECIMAL(8,6), Thursday DECIMAL(8,6), Friday DECIMAL(8,6), Saturday DECIMAL(8,6), Total DECIMAL(8,6))
--fill table
INSERT INTO @.tempweek
SELECT UserName,
(SELECT (y.HoursWorked) from db_owner.PS_HR_Hrs y WHERE y.UserName=YT.UserName AND y.DateWorked=YT.DateWorked AND datename(dw, DateWorked)='Sunday')AS Sunday,
(SELECT (y.HoursWorked) from db_owner.PS_HR_Hrs y WHERE y.UserName=YT.UserName AND y.DateWorked=YT.DateWorked AND datename(dw, DateWorked)='Monday')AS Monday,
(SELECT (y.HoursWorked) from db_owner.PS_HR_Hrs y WHERE y.UserName=YT.UserName AND y.DateWorked=YT.DateWorked AND datename(dw, DateWorked)='Tuesday')AS Tuesday,
(SELECT (y.HoursWorked) from db_owner.PS_HR_Hrs y WHERE y.UserName=YT.UserName AND y.DateWorked=YT.DateWorked AND datename(dw, DateWorked)='Wednesday')AS Wednesday,
(SELECT (y.HoursWorked) from db_owner.PS_HR_Hrs y WHERE y.UserName=YT.UserName AND y.DateWorked=YT.DateWorked AND datename(dw, DateWorked)='Thursday')AS Thursday,
(SELECT (y.HoursWorked) from db_owner.PS_HR_Hrs y WHERE y.UserName=YT.UserName AND y.DateWorked=YT.DateWorked AND datename(dw, DateWorked)='Friday')AS Friday,
(SELECT (y.HoursWorked) from db_owner.PS_HR_Hrs y WHERE y.UserName=YT.UserName AND y.DateWorked=YT.DateWorked AND datename(dw, DateWorked)='Saturday')AS Saturday,
(SELECT SUM(HoursWorked)from db_owner.PS_HR_Hrs y WHERE y.UserName=YT.UserName AND y.HoursWorked = YT.HoursWorked) AS Total
from db_owner.PS_HR_Hrs YT
--select data
select UserName , sum(sunday)as Sunday, sum(monday) as Monday, sum(tuesday)as Tuesday, sum(wednesday)asWednesday, sum(thursday)as Thursday, sum(friday)as Friday, sum(saturday)as Saturday, sum(total) as Total
from @.tempweek
group by UserName
Your solution will probably be faster as you do not use a CURSOR! Both solutions will be gluttons for memory for the few milliseconds they run, so as always, never stint on the RAM for a server hosting SQL Server.
display short datetime
Hi
I need to set the default value of an "End Date" parameter to the date of today.
Now I use the expression =Now(), but then the timestamp is also displayed. That I don't want to happen!
I tried formatting the datetime but then it becomes a string, and converting it back to a date leaves also a timestamp but with midnight time.
Is there a way to format a date parameter?
Hi,
Did you try : Format(mydate.Value,"dd/MM/yyyy") or Cdate(Format(mydate.Value,"dd/MM/yyyy").ToString) ?
Regards
Ayzan
|||Both expressions above don't work because my parameter is of the type datetime and the expressions return a string. So I get an error that the parameter has another type than it expected.
Still thanks for pointing me the CDate function. It has alot of functions and that's the function that got me the wanted result.
To display only the date part of a datetime value retrieved by the function Now() you need the following expression:
=CDate(Now()).Today
Display results like this using SQL
Using an SQL query I am getting results as follows:
Date Name Amount
-- -- --
1-Nov-2007 Susan 1000
1-Nov-2007 Derek 2000
1-Nov-2007 Mike J 1050
2-Nov-2007 Susan 2500
2-Nov-2007 Mike J 7289
I want to show the results like this using SQL query:
1-Nov-2007 Susan 1000
Derek 2000
Mike J 1050
2-Nov-2007 Susan 2500
Mike J 7289"RP" <rpk.general@.gmail.com> wrote in message
news:1194500368.511146.82040@.z24g2000prh.googlegroups.com...
> Using an SQL query I am getting results as follows:
> Date Name Amount
> -- -- --
> 1-Nov-2007 Susan 1000
> 1-Nov-2007 Derek 2000
> 1-Nov-2007 Mike J 1050
> 2-Nov-2007 Susan 2500
> 2-Nov-2007 Mike J 7289
>
> I want to show the results like this using SQL query:
> 1-Nov-2007 Susan 1000
> Derek 2000
> Mike J 1050
> 2-Nov-2007 Susan 2500
> Mike J 7289
>
For what purpose? This looks more like a report than a result set, ie: fine
for display and printing but useless for anything in SQL. Your reporting
tool will be able to do it using "groups" or "bands" on the page.
--
David Portas|||RP
I colmpetely agree with David. Do that on the cclient side
--SQL Server 2005
WITH cte
AS
(
SELECT CustomerID,OrderID,ROW_NUMBER()
OVER(PARTITION BY CustomerID ORDER BY CustomerID) AS rn FROM Orders
) SELECT CASE WHEN rn>1 THEN REPLACE(CustomerID,CustomerID,'') ELSE
CustomerID END AS CustomerID
,OrderID FROM cte
"RP" <rpk.general@.gmail.com> wrote in message
news:1194500368.511146.82040@.z24g2000prh.googlegroups.com...
> Using an SQL query I am getting results as follows:
> Date Name Amount
> -- -- --
> 1-Nov-2007 Susan 1000
> 1-Nov-2007 Derek 2000
> 1-Nov-2007 Mike J 1050
> 2-Nov-2007 Susan 2500
> 2-Nov-2007 Mike J 7289
>
> I want to show the results like this using SQL query:
> 1-Nov-2007 Susan 1000
> Derek 2000
> Mike J 1050
> 2-Nov-2007 Susan 2500
> Mike J 7289
>|||Oracle has SQL analytics which does this through SQL. The situation is
that I want it by SQL alone.
On Nov 8, 2:14 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> RP
> I colmpetely agree with David. Do that on the cclient side
> --SQL Server 2005
> WITH cte
> AS
> (
> SELECT CustomerID,OrderID,ROW_NUMBER()
> OVER(PARTITION BY CustomerID ORDER BY CustomerID) AS rn FROM Orders
> ) SELECT CASE WHEN rn>1 THEN REPLACE(CustomerID,CustomerID,'') ELSE
> CustomerID END AS CustomerID
> ,OrderID FROM cte
>|||SQL Server is NOT Oracle :-)))
"RP" <rpk.general@.gmail.com> wrote in message
news:1194518385.233199.140630@.v29g2000prd.googlegroups.com...
> Oracle has SQL analytics which does this through SQL. The situation is
> that I want it by SQL alone.
> On Nov 8, 2:14 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> RP
>> I colmpetely agree with David. Do that on the cclient side
>> --SQL Server 2005
>> WITH cte
>> AS
>> (
>> SELECT CustomerID,OrderID,ROW_NUMBER()
>> OVER(PARTITION BY CustomerID ORDER BY CustomerID) AS rn FROM Orders
>> ) SELECT CASE WHEN rn>1 THEN REPLACE(CustomerID,CustomerID,'') ELSE
>> CustomerID END AS CustomerID
>> ,OrderID FROM cte
>|||From my Googling of "SQL Analytics" it seems to be pretty much the OVER clause what we now have in
2005, which you seem to already have a suggestion for.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"RP" <rpk.general@.gmail.com> wrote in message
news:1194518385.233199.140630@.v29g2000prd.googlegroups.com...
> Oracle has SQL analytics which does this through SQL. The situation is
> that I want it by SQL alone.
> On Nov 8, 2:14 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> RP
>> I colmpetely agree with David. Do that on the cclient side
>> --SQL Server 2005
>> WITH cte
>> AS
>> (
>> SELECT CustomerID,OrderID,ROW_NUMBER()
>> OVER(PARTITION BY CustomerID ORDER BY CustomerID) AS rn FROM Orders
>> ) SELECT CASE WHEN rn>1 THEN REPLACE(CustomerID,CustomerID,'') ELSE
>> CustomerID END AS CustomerID
>> ,OrderID FROM cte
>|||Just because something CAN be done, does not mean it is CORRECT to do it
that way! ;-)
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"RP" <rpk.general@.gmail.com> wrote in message
news:1194518385.233199.140630@.v29g2000prd.googlegroups.com...
> Oracle has SQL analytics which does this through SQL. The situation is
> that I want it by SQL alone.
> On Nov 8, 2:14 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> RP
>> I colmpetely agree with David. Do that on the cclient side
>> --SQL Server 2005
>> WITH cte
>> AS
>> (
>> SELECT CustomerID,OrderID,ROW_NUMBER()
>> OVER(PARTITION BY CustomerID ORDER BY CustomerID) AS rn FROM Orders
>> ) SELECT CASE WHEN rn>1 THEN REPLACE(CustomerID,CustomerID,'') ELSE
>> CustomerID END AS CustomerID
>> ,OrderID FROM cte
>|||"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13j64g4c7sp9k1a@.corp.supernews.com...
> Just because something CAN be done, does not mean it is CORRECT to do it
> that way! ;-)
Does that thought also cover:
http://www.sommarskog.se/arrays-in-sql-2005.html
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/08/05/passing-an-array-csv-to-a-stored-procedure-with-data-validation-no-loops-no-self-joins-just-replace.aspx
? :)
www.beyondsql.blogspot.com|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uRlI6OdIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>.
> For what purpose? This looks more like a report than a result set, ie:
> fine for display and printing but useless for anything in SQL. Your
> reporting tool will be able to do it using "groups" or "bands" on the
> page.
>
So it's the end that justify the means? That's rather expedient don't
don't you think. So it's just expediency that justifies:
http://www.sommarskog.se/arrays-in-sql-2005.html
www.beyondsql.blogspot.com
Date Name Amount
-- -- --
1-Nov-2007 Susan 1000
1-Nov-2007 Derek 2000
1-Nov-2007 Mike J 1050
2-Nov-2007 Susan 2500
2-Nov-2007 Mike J 7289
I want to show the results like this using SQL query:
1-Nov-2007 Susan 1000
Derek 2000
Mike J 1050
2-Nov-2007 Susan 2500
Mike J 7289"RP" <rpk.general@.gmail.com> wrote in message
news:1194500368.511146.82040@.z24g2000prh.googlegroups.com...
> Using an SQL query I am getting results as follows:
> Date Name Amount
> -- -- --
> 1-Nov-2007 Susan 1000
> 1-Nov-2007 Derek 2000
> 1-Nov-2007 Mike J 1050
> 2-Nov-2007 Susan 2500
> 2-Nov-2007 Mike J 7289
>
> I want to show the results like this using SQL query:
> 1-Nov-2007 Susan 1000
> Derek 2000
> Mike J 1050
> 2-Nov-2007 Susan 2500
> Mike J 7289
>
For what purpose? This looks more like a report than a result set, ie: fine
for display and printing but useless for anything in SQL. Your reporting
tool will be able to do it using "groups" or "bands" on the page.
--
David Portas|||RP
I colmpetely agree with David. Do that on the cclient side
--SQL Server 2005
WITH cte
AS
(
SELECT CustomerID,OrderID,ROW_NUMBER()
OVER(PARTITION BY CustomerID ORDER BY CustomerID) AS rn FROM Orders
) SELECT CASE WHEN rn>1 THEN REPLACE(CustomerID,CustomerID,'') ELSE
CustomerID END AS CustomerID
,OrderID FROM cte
"RP" <rpk.general@.gmail.com> wrote in message
news:1194500368.511146.82040@.z24g2000prh.googlegroups.com...
> Using an SQL query I am getting results as follows:
> Date Name Amount
> -- -- --
> 1-Nov-2007 Susan 1000
> 1-Nov-2007 Derek 2000
> 1-Nov-2007 Mike J 1050
> 2-Nov-2007 Susan 2500
> 2-Nov-2007 Mike J 7289
>
> I want to show the results like this using SQL query:
> 1-Nov-2007 Susan 1000
> Derek 2000
> Mike J 1050
> 2-Nov-2007 Susan 2500
> Mike J 7289
>|||Oracle has SQL analytics which does this through SQL. The situation is
that I want it by SQL alone.
On Nov 8, 2:14 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> RP
> I colmpetely agree with David. Do that on the cclient side
> --SQL Server 2005
> WITH cte
> AS
> (
> SELECT CustomerID,OrderID,ROW_NUMBER()
> OVER(PARTITION BY CustomerID ORDER BY CustomerID) AS rn FROM Orders
> ) SELECT CASE WHEN rn>1 THEN REPLACE(CustomerID,CustomerID,'') ELSE
> CustomerID END AS CustomerID
> ,OrderID FROM cte
>|||SQL Server is NOT Oracle :-)))
"RP" <rpk.general@.gmail.com> wrote in message
news:1194518385.233199.140630@.v29g2000prd.googlegroups.com...
> Oracle has SQL analytics which does this through SQL. The situation is
> that I want it by SQL alone.
> On Nov 8, 2:14 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> RP
>> I colmpetely agree with David. Do that on the cclient side
>> --SQL Server 2005
>> WITH cte
>> AS
>> (
>> SELECT CustomerID,OrderID,ROW_NUMBER()
>> OVER(PARTITION BY CustomerID ORDER BY CustomerID) AS rn FROM Orders
>> ) SELECT CASE WHEN rn>1 THEN REPLACE(CustomerID,CustomerID,'') ELSE
>> CustomerID END AS CustomerID
>> ,OrderID FROM cte
>|||From my Googling of "SQL Analytics" it seems to be pretty much the OVER clause what we now have in
2005, which you seem to already have a suggestion for.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"RP" <rpk.general@.gmail.com> wrote in message
news:1194518385.233199.140630@.v29g2000prd.googlegroups.com...
> Oracle has SQL analytics which does this through SQL. The situation is
> that I want it by SQL alone.
> On Nov 8, 2:14 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> RP
>> I colmpetely agree with David. Do that on the cclient side
>> --SQL Server 2005
>> WITH cte
>> AS
>> (
>> SELECT CustomerID,OrderID,ROW_NUMBER()
>> OVER(PARTITION BY CustomerID ORDER BY CustomerID) AS rn FROM Orders
>> ) SELECT CASE WHEN rn>1 THEN REPLACE(CustomerID,CustomerID,'') ELSE
>> CustomerID END AS CustomerID
>> ,OrderID FROM cte
>|||Just because something CAN be done, does not mean it is CORRECT to do it
that way! ;-)
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"RP" <rpk.general@.gmail.com> wrote in message
news:1194518385.233199.140630@.v29g2000prd.googlegroups.com...
> Oracle has SQL analytics which does this through SQL. The situation is
> that I want it by SQL alone.
> On Nov 8, 2:14 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> RP
>> I colmpetely agree with David. Do that on the cclient side
>> --SQL Server 2005
>> WITH cte
>> AS
>> (
>> SELECT CustomerID,OrderID,ROW_NUMBER()
>> OVER(PARTITION BY CustomerID ORDER BY CustomerID) AS rn FROM Orders
>> ) SELECT CASE WHEN rn>1 THEN REPLACE(CustomerID,CustomerID,'') ELSE
>> CustomerID END AS CustomerID
>> ,OrderID FROM cte
>|||"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13j64g4c7sp9k1a@.corp.supernews.com...
> Just because something CAN be done, does not mean it is CORRECT to do it
> that way! ;-)
Does that thought also cover:
http://www.sommarskog.se/arrays-in-sql-2005.html
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/08/05/passing-an-array-csv-to-a-stored-procedure-with-data-validation-no-loops-no-self-joins-just-replace.aspx
? :)
www.beyondsql.blogspot.com|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uRlI6OdIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>.
> For what purpose? This looks more like a report than a result set, ie:
> fine for display and printing but useless for anything in SQL. Your
> reporting tool will be able to do it using "groups" or "bands" on the
> page.
>
So it's the end that justify the means? That's rather expedient don't
don't you think. So it's just expediency that justifies:
http://www.sommarskog.se/arrays-in-sql-2005.html
www.beyondsql.blogspot.com
Sunday, February 19, 2012
display records row wise
hello
I am facing a problem in the crystal reports 8.5
In the database table one column is date ,
so here in my report i want to display the records in such a way that
the for every date there should be one colum like for 31 days there should the date in all 31 day
for example please refer the attachment
or see below the requirement is
in my example the records with the days like 21,22
which is diplaying in next to row of the 21 day but i want to show in the same row i.e side by side not in the next row
please any query please pass it so that i can explain you in more detail
urgentplz any one help me|||Maybe you can do this with a crosstab - no idea as I've never used them.
So, although there may be better ways the one I instantly thought of is:
Assuming you are grouping by month:
Create/initialise an array of 31 elements in the group header.
For each detail, add the value into the appropriate array element.
Display each element in its appropriate position in the footer, under its day number.
For completeness you could also suppress any day number heading if > number of days in the month.|||thanks for reply
But i had created with standard report.
but one more issue in this if i take in footer it will display only one record
how can i resolve this|||Don't know what you mean by "But i had created with standard report."
Anyway, if I understand correctly what you really want is for each detail line to be printed but for the 'RT' etc. values to be under the correct day number heading.
Maybe you could left-pad the 'RT' string with a number of spaces dependent on the day number, e.g.
ReplicateString (' ', ({day}-1) * n ) + {table.column}
where n makes the 'RT' text line up underneath the day number heading for any given day number assuming you have n characters between the start of each day number.
(Best to use Courier font or similar, rather than Ariel...)|||Dont use standard report. Use Cross tab option. See help file for more information
I am facing a problem in the crystal reports 8.5
In the database table one column is date ,
so here in my report i want to display the records in such a way that
the for every date there should be one colum like for 31 days there should the date in all 31 day
for example please refer the attachment
or see below the requirement is
in my example the records with the days like 21,22
which is diplaying in next to row of the 21 day but i want to show in the same row i.e side by side not in the next row
please any query please pass it so that i can explain you in more detail
urgentplz any one help me|||Maybe you can do this with a crosstab - no idea as I've never used them.
So, although there may be better ways the one I instantly thought of is:
Assuming you are grouping by month:
Create/initialise an array of 31 elements in the group header.
For each detail, add the value into the appropriate array element.
Display each element in its appropriate position in the footer, under its day number.
For completeness you could also suppress any day number heading if > number of days in the month.|||thanks for reply
But i had created with standard report.
but one more issue in this if i take in footer it will display only one record
how can i resolve this|||Don't know what you mean by "But i had created with standard report."
Anyway, if I understand correctly what you really want is for each detail line to be printed but for the 'RT' etc. values to be under the correct day number heading.
Maybe you could left-pad the 'RT' string with a number of spaces dependent on the day number, e.g.
ReplicateString (' ', ({day}-1) * n ) + {table.column}
where n makes the 'RT' text line up underneath the day number heading for any given day number assuming you have n characters between the start of each day number.
(Best to use Courier font or similar, rather than Ariel...)|||Dont use standard report. Use Cross tab option. See help file for more information
Display only the latest two consecutive years?
Hi all,
I have a Matrix with several static rows of financial information with a column group that groups the information by date:
01.02.04 12.09.03
Dividend 455,789 346,098
Profit 100,787 264,687
Deposits 89,078 76,003
Currently I have a filter on the column group that limits the information to the top two dates (Fields!dateField.Value Top N =2). What I also need is to filer the data so that only consecutive years are displayed. I have tried the filter (Year(Fields!dateField.Value) >= Year(Max(Fields!dateField.Value)) - 1), but this doesn't stop non-consecutive years being added to the Matrix. What am I missing here?
Any help you have to offer is much appreciated!
Thank you,
Stephen.
stephen,
i think that this task seems to be "database work" - just evoke the needed data by an appropriate sql-statement.
cheers
markus
Thank you for your reply.
Is there any way that I can do this without altering the underlying dataset? Unfortunately, the query already returns all of the data that is required and nothing more, so limiting any of the data there would cause problems elsewhere in the report.
Stephen.
|||How about creating a second dataset with a modified query to get only the data you need. So then you have one dataset with all the dataand one with the last two years.|||That's a good idea! I will have a play around with it and will let you know how I get on.Thanks!
Regards,
Stephen.|||
That seems to have done the trick!
Thanks!
Stephen.
Display of date time inforamtion - some columns are NULL some are not
I have a column in a table that is a datetime data type. Some columns
are NULL some are not.
So, a sampling of data could include:
NULL
2005-06-06 12:32:53.000
2005-04-12 11:32:53.000
NULL
NULL
2005-12-22 12:32:53.000
When I select from this column, if the value is NULL, I need to replace
NULL with the word 'No'. If the value is not NULL, then I need to
display the date - so my output needs to look like this:
No
06/06/2005
04/12/2005
No
No
12/22/2005
I know how to convert the date - the problem I am having is converting
the NULL datetime to characters and including the logic to account for
NULLs in the first place.
I suspect that I need a CASE statement, but I am not sure how to
accomplish this.
Thanks-SELECT COALESCE(CONVERT(CHAR(10), datecolumn, 101), 'No')
FROM table
However, I recommend against using ambiguous formats like m/d/y for display.
<wxbuff@.aol.com> wrote in message
news:1138713642.280962.50970@.g43g2000cwa.googlegroups.com...
>I have a column in a table that is a datetime data type. Some columns
> are NULL some are not.
> So, a sampling of data could include:
> NULL
> 2005-06-06 12:32:53.000
> 2005-04-12 11:32:53.000
> NULL
> NULL
> 2005-12-22 12:32:53.000
> When I select from this column, if the value is NULL, I need to replace
> NULL with the word 'No'. If the value is not NULL, then I need to
> display the date - so my output needs to look like this:
> No
> 06/06/2005
> 04/12/2005
> No
> No
> 12/22/2005
> I know how to convert the date - the problem I am having is converting
> the NULL datetime to characters and including the logic to account for
> NULLs in the first place.
> I suspect that I need a CASE statement, but I am not sure how to
> accomplish this.
> Thanks-
>|||
select coalesce(convert(varchar(10),thedatecolu
mn,101),'No')
However, you may be better off doing the formatting in your client|||Aaron -
Perfect! I am still pretty new at this and was unfamiliar with the
COALESCE function. I see from the BOL that it replaces more
complex CASE statements so I am gratified to know that I was thinking
down the right path. Thank you for helping - it will save
me a great deal of time.
Danielle
are NULL some are not.
So, a sampling of data could include:
NULL
2005-06-06 12:32:53.000
2005-04-12 11:32:53.000
NULL
NULL
2005-12-22 12:32:53.000
When I select from this column, if the value is NULL, I need to replace
NULL with the word 'No'. If the value is not NULL, then I need to
display the date - so my output needs to look like this:
No
06/06/2005
04/12/2005
No
No
12/22/2005
I know how to convert the date - the problem I am having is converting
the NULL datetime to characters and including the logic to account for
NULLs in the first place.
I suspect that I need a CASE statement, but I am not sure how to
accomplish this.
Thanks-SELECT COALESCE(CONVERT(CHAR(10), datecolumn, 101), 'No')
FROM table
However, I recommend against using ambiguous formats like m/d/y for display.
<wxbuff@.aol.com> wrote in message
news:1138713642.280962.50970@.g43g2000cwa.googlegroups.com...
>I have a column in a table that is a datetime data type. Some columns
> are NULL some are not.
> So, a sampling of data could include:
> NULL
> 2005-06-06 12:32:53.000
> 2005-04-12 11:32:53.000
> NULL
> NULL
> 2005-12-22 12:32:53.000
> When I select from this column, if the value is NULL, I need to replace
> NULL with the word 'No'. If the value is not NULL, then I need to
> display the date - so my output needs to look like this:
> No
> 06/06/2005
> 04/12/2005
> No
> No
> 12/22/2005
> I know how to convert the date - the problem I am having is converting
> the NULL datetime to characters and including the logic to account for
> NULLs in the first place.
> I suspect that I need a CASE statement, but I am not sure how to
> accomplish this.
> Thanks-
>|||
select coalesce(convert(varchar(10),thedatecolu
mn,101),'No')
However, you may be better off doing the formatting in your client|||Aaron -
Perfect! I am still pretty new at this and was unfamiliar with the
COALESCE function. I see from the BOL that it replaces more
complex CASE statements so I am gratified to know that I was thinking
down the right path. Thank you for helping - it will save
me a great deal of time.
Danielle
Display of date time inforamtion - some columns are NULL some are
Do the formatting where it belongs - on the client. In SQL you could use
CONVERT but then you loose the domain.
Another way would be to use sql_variant which retains the datatype for later
use, but that's just another complication that can be successfully avoided b
y
formatting the data on the client.
ML
http://milambda.blogspot.com/BCP doesn't have any formatting control logic built in.
Before making definitive statements you need to know more about what the
poster is trying to achieve.
I've done formatting lots of time in the SQL Server because thats been the
best place for that particular problem, for instance creating the output
file for a data feed, its very easy (a couple of lines of T-SQL) to create a
table and use BCP to output it to a file that can then be sent out to
another part of the company/supplier.
A statement like 'do the formatting where it belongs - on the client' is
meaningless in this instance.
Its more true to say 'Do the formatting where it makes sense and is most
efficient for the problem you are trying to solve'.
Sorry if the post sounds harsh, I'm a bit fed up with this do all formatting
on the client debarkle.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"ML" <ML@.discussions.microsoft.com> wrote in message
news:A73D446B-49C0-429A-B30C-EB11A035B5E7@.microsoft.com...
> Do the formatting where it belongs - on the client. In SQL you could use
> CONVERT but then you loose the domain.
> Another way would be to use sql_variant which retains the datatype for
> later
> use, but that's just another complication that can be successfully avoided
> by
> formatting the data on the client.
>
> ML
> --
> http://milambda.blogspot.com/
CONVERT but then you loose the domain.
Another way would be to use sql_variant which retains the datatype for later
use, but that's just another complication that can be successfully avoided b
y
formatting the data on the client.
ML
http://milambda.blogspot.com/BCP doesn't have any formatting control logic built in.
Before making definitive statements you need to know more about what the
poster is trying to achieve.
I've done formatting lots of time in the SQL Server because thats been the
best place for that particular problem, for instance creating the output
file for a data feed, its very easy (a couple of lines of T-SQL) to create a
table and use BCP to output it to a file that can then be sent out to
another part of the company/supplier.
A statement like 'do the formatting where it belongs - on the client' is
meaningless in this instance.
Its more true to say 'Do the formatting where it makes sense and is most
efficient for the problem you are trying to solve'.
Sorry if the post sounds harsh, I'm a bit fed up with this do all formatting
on the client debarkle.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"ML" <ML@.discussions.microsoft.com> wrote in message
news:A73D446B-49C0-429A-B30C-EB11A035B5E7@.microsoft.com...
> Do the formatting where it belongs - on the client. In SQL you could use
> CONVERT but then you loose the domain.
> Another way would be to use sql_variant which retains the datatype for
> later
> use, but that's just another complication that can be successfully avoided
> by
> formatting the data on the client.
>
> ML
> --
> http://milambda.blogspot.com/
Friday, February 17, 2012
display multi value parameters in textbox
Hello,
I have the following problem.
i have a report where you need to fill in a few parameters
start date, end date and subject selection.
the subject selection is a multivalue paramter (select all, param1, param2, param3)
the multivalue parameter is based on another dataset with paramid as value field and paramdescription as value label.
in my report i want to display something like this :
from startdate to enddate
summary for campaign(s) : selected parameters.
in the selected parameters is can display a =join(Parameter!campaig.value) but this only returns the 32bit guid.
instead i would like it to display the parameterdescription label.
anybody has some ideas ?
greetings
vince
Hi Vince,
I think using the expression =Join(Parameters!campaign.Label) should give you the desired result.
-Aayush
Subscribe to:
Posts (Atom)