More problems with updateText

Hi,

Basically I am trying to add 2 ntext fields together sandwhiched by a literal ( '<BR /><BR />' ) in a SP for a report I will be running.

First step is to add <BR /><BR />, which I have done with some help from this forum, using a cursor and temp tables.

The last step is add the second ntext column (if it exists for the case only though). So like the 1st step I am using cursor and updatetext to amend the temporary table. The problem is that rather than update the temp table, the query section I have highlighted is run and nothing at all gets added to temptable. Since the column I am trying to add is a ntext I can't create a local variable. Does anyone know where I am going wrong?

Thanks in advance

Declare @.value2 varbinary(16)
DECLARE TEXTPTR_CURSOR2 CURSOR FOR
SELECT TEXTPTR(RT.Status) FROM #TempReport RT
DECLARE @.CurrentCase int

OPEN TEXTPTR_CURSOR2
FETCH NEXT FROM TEXTPTR_CURSOR2 INTO @.value2

WHILE @.@.FETCH_STATUS = 0
BEGIN

SELECT @.CurrentCase = caseid from #tempreport rt
where TEXTPTR(RT.Status) = @.value2

If ( select count(*) from tbl_memo m
where caseid = @.CurrentCase
and memotypeid = 8) > 0
BEGIN

UPDATETEXT #tempreport.Status @.value2 null 0
(select IsNull(thevalue,'')
from tbl_memo m
where caseid = @.CurrentCase
and memotypeid = 8)

END

FETCH NEXT FROM TEXTPTR_CURSOR2 INTO @.value2

END

CLOSE TEXTPTR_CURSOR2
DEALLOCATE TEXTPTR_CURSOR2

For those interested, I managed to crack this by using another text pointer:

Declare @.value2 varbinary(16)
DECLARE TEXTPTR_CURSOR2 CURSOR FOR
SELECT TEXTPTR(RT.Status) FROM #TempReport RT
DECLARE @.CurrentCase int

OPEN TEXTPTR_CURSOR2
FETCH NEXT FROM TEXTPTR_CURSOR2 INTO @.value2

WHILE @.@.FETCH_STATUS = 0
BEGIN

SELECT @.CurrentCase = caseid from #tempreport rt
where TEXTPTR(RT.Status) = @.value2

If ( select count(*) from tbl_memo m
where caseid = @.CurrentCase
and memotypeid = 8) > 0
BEGIN
declare @.value3 varbinary(16)
select @.value3 = textptr(thevalue) from tbl_memo m where caseid = @.currentcase and memotypeid = 8

UPDATETEXT #tempreport.Status @.value2 null 0 tbl_memo.thevalue @.value3

END

FETCH NEXT FROM TEXTPTR_CURSOR2 INTO @.value2
END

CLOSE TEXTPTR_CURSOR2
DEALLOCATE TEXTPTR_CURSOR2

More Mulitvalue parameter issues...

I have a URL action on a report item that opens a new window to a different
report (using a piece of javascript). I need to pass the selected multivalue
parameter from the first report to this other report. But I cant figure out
how to pass the multivalue paramaters in the url, I have tried just
Parameters!Region.Value and also split(Parameters!Region.Value,", ") and
join(Parameters!Region.Value,", ") but none of them work.
the url actions is...
="javascript:void(window.open('http://server/Reportserver2005?/Report1®ion="
& Parameters!Region.Value &
rs:Command=Render&rs:ClearSession=true&rc:Parameters=false','_blank','location=no,resizable,toolbar=yes,left=200,top=60,height=590,width=760'))"
Any ideas?I haven't done this and don't have a sample, but just for S&G (S and
giggles), try using the encoded equivalent of the "," to join the values...
Call it a SWAG.
"NH" <NH@.discussions.microsoft.com> wrote in message
news:D3528E3E-CF89-4EEB-BA9E-DA8C88A3066B@.microsoft.com...
>I have a URL action on a report item that opens a new window to a different
> report (using a piece of javascript). I need to pass the selected
> multivalue
> parameter from the first report to this other report. But I cant figure
> out
> how to pass the multivalue paramaters in the url, I have tried just
> Parameters!Region.Value and also split(Parameters!Region.Value,", ") and
> join(Parameters!Region.Value,", ") but none of them work.
> the url actions is...
> ="javascript:void(window.open('http://server/Reportserver2005?/Report1®ion="
> & Parameters!Region.Value &
> rs:Command=Render&rs:ClearSession=true&rc:Parameters=false','_blank','location=no,resizable,toolbar=yes,left=200,top=60,height=590,width=760'))"
> Any ideas?sql

More Charts.......

I know, I know there are several problems with charts already out
there. I have read them all.... 4 times. I have a report that has a
page header made up of simple labels and the body of the report is made
up of a chart. I am performing the following.
result = rs.Render(reportPath, format, historyID, devinfo, parameters,
Nothing, showHide, Nothing, mimeType, Nothing, Nothing, streamIDs)
For Each streamID In streamIDs
result1 = rs.RenderStream(reportPath, format, _
streamID, Nothing, devinfo, parameters, Nothing, Nothing)
***This is written to the location of the report not neccessarily
***the application. I have tried writing to the application home
***also with no success
Dim stream As System.IO.FileStream = _
System.IO.File.OpenWrite
("C:\Inetpub\wwwroot\ReportsServices\eRisc\" & streamID)
stream.Write(result1, 0, CInt(result1.Length))
stream.Close()
Next
Response.Clear()
***Now how do I display this to the page? I have tried a couple
options.
***Option 1 - This displays the header and a bunch of binary ***output
to the bottom
Response.Write(New
System.Text.UTF8Encoding().GetString(result).Replace("{SessionId}",
rs.SessionHeaderValue.SessionId))
Response.ContentType = "image/JPEG"
Response.BinaryWrite(result1)
***Option 2 - This also displays the header and a bunch of binary
***output to the bottom
Response.ContentType = "image/JPEG"
Response.BinaryWrite(result)
Response.BinaryWrite(result1)
***Option 3 - This displays the header and just a red X for the image.
Response.BinaryWrite(result)I should have added option #4
***Option 4 - This also displays the chart only
Response.ContentType = "image/JPEG"
Response.BinaryWrite(result1)|||First of all, have you considered using URL access for your reports? That
would be the easiest solution.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"UW_Hoops" <kdschmidt@.charter.net> wrote in message
news:1103144483.726468.325490@.f14g2000cwb.googlegroups.com...
>I know, I know there are several problems with charts already out
> there. I have read them all.... 4 times. I have a report that has a
> page header made up of simple labels and the body of the report is made
> up of a chart. I am performing the following.
> result = rs.Render(reportPath, format, historyID, devinfo, parameters,
> Nothing, showHide, Nothing, mimeType, Nothing, Nothing, streamIDs)
> For Each streamID In streamIDs
> result1 = rs.RenderStream(reportPath, format, _
> streamID, Nothing, devinfo, parameters, Nothing, Nothing)
> ***This is written to the location of the report not neccessarily
> ***the application. I have tried writing to the application home
> ***also with no success
> Dim stream As System.IO.FileStream = _
> System.IO.File.OpenWrite
> ("C:\Inetpub\wwwroot\ReportsServices\eRisc\" & streamID)
> stream.Write(result1, 0, CInt(result1.Length))
> stream.Close()
> Next
>
> Response.Clear()
> ***Now how do I display this to the page? I have tried a couple
> options.
> ***Option 1 - This displays the header and a bunch of binary ***output
> to the bottom
> Response.Write(New
> System.Text.UTF8Encoding().GetString(result).Replace("{SessionId}",
> rs.SessionHeaderValue.SessionId))
> Response.ContentType = "image/JPEG"
> Response.BinaryWrite(result1)
> ***Option 2 - This also displays the header and a bunch of binary
> ***output to the bottom
> Response.ContentType = "image/JPEG"
> Response.BinaryWrite(result)
> Response.BinaryWrite(result1)
> ***Option 3 - This displays the header and just a red X for the image.
> Response.BinaryWrite(result)
>

More browser support for Safari and Firefox?

When I try to run a simple table report using Safari or Firefox the experience is less than stellar.

Using Safari the report never returns, basically the pag body is blank.

Using Firefox the table layout is very compressed.

Anything changes in the works to allow the reporting server to be used by non IE browser.

We run in a multi-platform environment and need the multiple browser support.

Thanks.

--sean

SQL Server 2005 Reporting Services

Hi sean

try adding this into ReportingServices.css file ( in C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager\Styles)

/* Fix report IFRAME height for Firefox */
.DocMapAndReportFrame
{
min-height: 860px;
}

After adding this

Some changes has to be done in report as well

Add the table into Rectangle

Deploy the report.

Hope this solves the problem..

safari browser Not sure even i am facing lots of problem

if u find any solution please let me know

More browser support for Safari and Firefox?

When I try to run a simple table report using Safari or Firefox the experience is less than stellar.

Using Safari the report never returns, basically the pag body is blank.

Using Firefox the table layout is very compressed.

Anything changes in the works to allow the reporting server to be used by non IE browser.

We run in a multi-platform environment and need the multiple browser support.

Thanks.

--sean

SQL Server 2005 Reporting Services

Hi sean

try adding this into ReportingServices.css file ( in C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager\Styles)

/* Fix report IFRAME height for Firefox */
.DocMapAndReportFrame
{
min-height: 860px;
}

After adding this

Some changes has to be done in report as well

Add the table into Rectangle

Deploy the report.

Hope this solves the problem..

safari browser Not sure even i am facing lots of problem

if u find any solution please let me know

More Blank Page Trouble

I've got a report that is printing a blank page between every page. I've
always set all my margins to zero so I don't experience this problem.
However, on this report, it is a landscape report (11 x 8.5 in), and the
report width is less than 9.5 inches. The margins are set to zero. For some
reason, I'm getting the blank page problem. I think this began to occur when
I added a group.
People have mentioned that I need to see what the printer's printing area
is. I have looked through all the printer properties and they only specify
page size (11 x 8.5). There is nothing specified about the size of the
printing area.
Any ideas?On May 25, 12:16 pm, Liz <L...@.discussions.microsoft.com> wrote:
> I've got a report that is printing a blank page between every page. I've
> always set all my margins to zero so I don't experience this problem.
> However, on this report, it is a landscape report (11 x 8.5 in), and the
> report width is less than 9.5 inches. The margins are set to zero. For some
> reason, I'm getting the blank page problem. I think this began to occur when
> I added a group.
> People have mentioned that I need to see what the printer's printing area
> is. I have looked through all the printer properties and they only specify
> page size (11 x 8.5). There is nothing specified about the size of the
> printing area.
> Any ideas?
If you are having this problem when exporting to Excel, make sure that
if you have multiple table/matrix controls on the report, they are all
the exact same width. If you are exporting to PDF, put two or more
table/matrix controls (that are vertically adjacent) inside rectangle
controls. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Month-Wise Report.

Hi All,
I have a table with the following structure
EMP_ID VARCHAR(5)
START_DATE DATETIME
END_DATE DATETIME
NO_OF_DAYS INT
This table tracks the leave taken by employees.
Say, I have a record like this
emp_id start_date end_date no_of_days
---
10005 Jan 26, 2005 Feb 02, 2005 8 (total
number of days on leave inclusive of start_date and end_date)
I want a report like this
10005 Jan - 6 Days Feb - 2 Days
Is there any way I can split a date range like this using SQL?
Thanks in advance...
Raju JosephTry this and let me know if it does what you require:
-- BEGIN SCRIPT
declare @.tbl table
(emp_id varchar(5)
, start_date datetime
, end_date datetime
, no_of_days int
)
insert into @.tbl
values (10005, 'Jan 26, 2005', 'Feb 02, 2005', 8)
insert into @.tbl
values (10006, 'Feb 26, 2000', 'March 02, 2000', 6)
select emp_id
, substring(datename(mm, start_date), 1, 3) +' - '+ convert(varchar(2),
day((dateadd(mm, 1, start_date)- day(dateadd(mm, 1, start_date)))) -
day(start_date)+1) vacation
, substring(datename(mm,end_date),1,3) +' - '+ convert(varchar(2),
day(end_date))
, no_of_days
from @.tbl
where month(start_date)< month(end_date)
-- END SCRIPT
"Raju Joseph" wrote:

> Hi All,
> I have a table with the following structure
> EMP_ID VARCHAR(5)
> START_DATE DATETIME
> END_DATE DATETIME
> NO_OF_DAYS INT
> This table tracks the leave taken by employees.
> Say, I have a record like this
> emp_id start_date end_date no_of_day
s
> ---
> 10005 Jan 26, 2005 Feb 02, 2005 8 (total
> number of days on leave inclusive of start_date and end_date)
> I want a report like this
> 10005 Jan - 6 Days Feb - 2 Days
> Is there any way I can split a date range like this using SQL?
> Thanks in advance...
> Raju Joseph
>
>|||That's what I need.
Thanks a lot Valdez
"Edgardo Valdez, MCSD, MCDBA"
<EdgardoValdezMCSDMCDBA@.discussions.microsoft.com> wrote in message
news:0F266F38-6CCA-4654-9CB4-1E28C75D653E@.microsoft.com...
> Try this and let me know if it does what you require:
> -- BEGIN SCRIPT
> declare @.tbl table
> (emp_id varchar(5)
> , start_date datetime
> , end_date datetime
> , no_of_days int
> )
> insert into @.tbl
> values (10005, 'Jan 26, 2005', 'Feb 02, 2005', 8)
> insert into @.tbl
> values (10006, 'Feb 26, 2000', 'March 02, 2000', 6)
> select emp_id
> , substring(datename(mm, start_date), 1, 3) +' - '+ convert(varchar(2),
> day((dateadd(mm, 1, start_date)- day(dateadd(mm, 1, start_date)))) -
> day(start_date)+1) vacation
> , substring(datename(mm,end_date),1,3) +' - '+ convert(varchar(2),
> day(end_date))
> , no_of_days
> from @.tbl
> where month(start_date)< month(end_date)
> -- END SCRIPT
> "Raju Joseph" wrote:
>|||Hi Valdez,
Is there any way I can make the sql more generic
ie. if an employee has been on leave for say more than 2 months (Jun 23,
2005 to Aug 5, 2005), I would like all the 3 months to show up. Can I do
this using just a single sql?
Thanks in advance
Raju Joseph
"Edgardo Valdez, MCSD, MCDBA"
<EdgardoValdezMCSDMCDBA@.discussions.microsoft.com> wrote in message
news:0F266F38-6CCA-4654-9CB4-1E28C75D653E@.microsoft.com...
> Try this and let me know if it does what you require:
> -- BEGIN SCRIPT
> declare @.tbl table
> (emp_id varchar(5)
> , start_date datetime
> , end_date datetime
> , no_of_days int
> )
> insert into @.tbl
> values (10005, 'Jan 26, 2005', 'Feb 02, 2005', 8)
> insert into @.tbl
> values (10006, 'Feb 26, 2000', 'March 02, 2000', 6)
> select emp_id
> , substring(datename(mm, start_date), 1, 3) +' - '+ convert(varchar(2),
> day((dateadd(mm, 1, start_date)- day(dateadd(mm, 1, start_date)))) -
> day(start_date)+1) vacation
> , substring(datename(mm,end_date),1,3) +' - '+ convert(varchar(2),
> day(end_date))
> , no_of_days
> from @.tbl
> where month(start_date)< month(end_date)
> -- END SCRIPT
> "Raju Joseph" wrote:
>|||Hi There,
You can have one calendar table which will store all the dates from say
'1-jan-2000' to '31-dec-2009'
then your query would become as easy as this
Select Month(caldate),Year(caldate),count(*) Days From Calendar C ,
(Select * from yourtable where empid=@.empid) EMP Where caldate between
EMP.startdate and
emp.enddate group by Year(caldate),Month(caldate)
With Warm regards
Jatinder Singh|||Sorry to ask again.
Any way to display the result like this
emp_id Jan Feb Mar Apr
.... Dec
----
--
101 2 1
102 3
.
.
.
Thanks in advance...
"Edgardo Valdez, MCSD, MCDBA"
<EdgardoValdezMCSDMCDBA@.discussions.microsoft.com> wrote in message
news:0F266F38-6CCA-4654-9CB4-1E28C75D653E@.microsoft.com...
> Try this and let me know if it does what you require:
> -- BEGIN SCRIPT
> declare @.tbl table
> (emp_id varchar(5)
> , start_date datetime
> , end_date datetime
> , no_of_days int
> )
> insert into @.tbl
> values (10005, 'Jan 26, 2005', 'Feb 02, 2005', 8)
> insert into @.tbl
> values (10006, 'Feb 26, 2000', 'March 02, 2000', 6)
> select emp_id
> , substring(datename(mm, start_date), 1, 3) +' - '+ convert(varchar(2),
> day((dateadd(mm, 1, start_date)- day(dateadd(mm, 1, start_date)))) -
> day(start_date)+1) vacation
> , substring(datename(mm,end_date),1,3) +' - '+ convert(varchar(2),
> day(end_date))
> , no_of_days
> from @.tbl
> where month(start_date)< month(end_date)
> -- END SCRIPT
> "Raju Joseph" wrote:
>|||Hi there,
I am bit worried abut the leave extending from one year to another like
'31-Dec' to '4-Jan' .How would you like to show this '
If it is OK to ignore Year then You can try this .
Select empid,
Count(Case When Month(caldate) = 1 Then 1 else null ENd ) Jan,
Count(Case When Month(caldate) = 2 Then 1 else null ENd ) Feb,... And
SoON
>From Emp,calendar where caldate between startdate and enddate
Group by empid;
With Warm regards
Jatinder Singh|||Thanks a lot Jatinder and Valdez...
Coola Boola...
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1144239010.621694.145890@.u72g2000cwu.googlegroups.com...
> Hi there,
> I am bit worried abut the leave extending from one year to another like
> '31-Dec' to '4-Jan' .How would you like to show this '
> If it is OK to ignore Year then You can try this .
> Select empid,
> Count(Case When Month(caldate) = 1 Then 1 else null ENd ) Jan,
> Count(Case When Month(caldate) = 2 Then 1 else null ENd ) Feb,... And
> SoON
> Group by empid;
> With Warm regards
> Jatinder Singh
>

monthtodate and yeartodate

I am creating an application with a report in VB .NET. I have all the data showing up and now I need to create a summary at the bottom of the report to show current, Month To Date, and Year To Date information for formulas and database entries. I have it working to a point. My question is what is the format to write a formula for these two fields to keep the data current even if I am not working in the current month. If I am doing November data it seems to work, but if my date is in October I am not getting the data for October, I am still getting November data. I looked in the Crystal Help file and they suggest to change the date on the computer in the control panel. hmmm.... Not very professional if you ask me. Any help would be great.Look under Report menu in Crystal - you can change the date (last option is "Set Report Print Date/Time").
Change that date and MonthToDate etc will use it for their dependent calculations.
Check the object model under .NET and hopefully that property is exposed and is able to be modified by your .NET code.

dave|||Dave,

Thank you for the reply. I have tried that, but I am still having problems. I can get the current data to show up in the MTD formula but it is not adding it the the previous data for the month. I am sure it is a placement problem but I could be worng there. I am also not getting an accurate sum for a formula that is a division problem. I have ordered a new book to help, but any input you may have would be great. Thank you.

Julie|||Hi Julie,

Here's what I would do:
You know you can create more than one group based on the same report field? Well, you can.
So, say you already have a group to show details date by date, create a group above this based on the same field, but in the "Change Group" options, set the "for each section" option to select 'for each year'.
That will give you a section that will show summary data based on the whole year.

Add another group based on the date, and in the "Change Group" options, set the "for each section" option to select 'for each month'.

The groups may not be in the order you want, so use Report menu, and then Change Group Expert, to define the order:
Should be Group 1 : year
Group 2 : month
Group 3 : day

then create your summaries.

Now in the case of one of my reports, I want data from the current month, even though it's incomplete, so in my report formula for selection by line (Report menu, Edit Selection Formula, Record...)
I use something like this:
(({DATEFIELD} > DateAdd("m", -12, {?Pm-?ReportDate})
and
{DATEFIELD} <= {?Pm-?ReportDate}).
Just bear in mind that if date is 15/12/04, report then goes back to 16/12/03.

IF you want completed months then you need a formula field first, something like this (we'll call it EndDate):

' get first day of current month.
formula = DateValue(Year(CurrentDate), Month(CurrentDate), 1

Then in my record selection formula I'd do this:
(({DATEFIELD} >= DateAdd("m", -12, {@.EndDate})
and
{DATEFIELD} < {@.EndDate}). ' note less than, because must be less than first of current month.........

Hope this helps

dave

Monthly rollups, tricky SQL question?

I'm trying to build a report that shows any user's purchases per month, alon
g
with the resulting balance. That is proving tricky, and to add to the
confusion I have to add rows even where there was no purchases/sales, as lon
g
as the remaining balance was not zero.
Doing the monthly rollup is easy enough with a group by on MONTH(trandate).
Adding rows for "empty" months was also fairly easy, I did a outer join on a
table of dates (is there an easier way to do this?).
What's got me stumped is the "everything up to now", and I think I might be
doing it entirely the wrong way. Normally I would make two subqueries, A and
B, which are the month-by-month rollups. I then have a WHERE that joins them
B.trandate <= A.trandate. But since the data has been grouped by month, the
actual date is no longer available (it's been "grouped out" of the results).
So I'm trying to come up with a way to compare these.
1) MONTH(B.date) < MONTH(A.data) AND YEAR(B.date) < YEAR(A.data) does not
work, because if you have something like 6/2005 it will consider that to fai
l
against the date 7/2004, because 7 > 6. Is there a way to fix this?
2) I tried making up a "fake date" with YEAR(date) + MONTH(date) and
comparing those, but SQL Server uses months with no leading zero, so you end
up with 200412 being smaller than 20043, and if you cast them to numbers the
n
200412 is bigger than 20053. Is there some way to make this work?!
3) maybe I'm doing it all wrong! Is there some way I could get the raw data
without grouping, join on the original dates (I have a dbo.FirstDayOfMonth
that would help here) and then do the grouping? It seems this should work,
but when I try it it seems to be difficult to fold the outer join back in, i
t
needs to refer to data in the A subquery, and if there is a way to do this I
can't figure out the syntax (at least not in an outer join, maybe a WHERE is
the way to go?)
MauryMaury Markowitz wrote:
> I'm trying to build a report that shows any user's purchases per month, al
ong
> with the resulting balance. That is proving tricky, and to add to the
> confusion I have to add rows even where there was no purchases/sales, as l
ong
> as the remaining balance was not zero.
> Doing the monthly rollup is easy enough with a group by on MONTH(trandate)
.
> Adding rows for "empty" months was also fairly easy, I did a outer join on
a
> table of dates (is there an easier way to do this?).
> What's got me stumped is the "everything up to now", and I think I might b
e
> doing it entirely the wrong way. Normally I would make two subqueries, A a
nd
> B, which are the month-by-month rollups. I then have a WHERE that joins th
em
> B.trandate <= A.trandate. But since the data has been grouped by month, th
e
> actual date is no longer available (it's been "grouped out" of the results
).
> So I'm trying to come up with a way to compare these.
> 1) MONTH(B.date) < MONTH(A.data) AND YEAR(B.date) < YEAR(A.data) does not
> work, because if you have something like 6/2005 it will consider that to f
ail
> against the date 7/2004, because 7 > 6. Is there a way to fix this?
> 2) I tried making up a "fake date" with YEAR(date) + MONTH(date) and
> comparing those, but SQL Server uses months with no leading zero, so you e
nd
> up with 200412 being smaller than 20043, and if you cast them to numbers t
hen
> 200412 is bigger than 20053. Is there some way to make this work?!
> 3) maybe I'm doing it all wrong! Is there some way I could get the raw dat
a
> without grouping, join on the original dates (I have a dbo.FirstDayOfMonth
> that would help here) and then do the grouping? It seems this should work,
> but when I try it it seems to be difficult to fold the outer join back in,
it
> needs to refer to data in the A subquery, and if there is a way to do this
I
> can't figure out the syntax (at least not in an outer join, maybe a WHERE
is
> the way to go?)
> Maury
Take a look at CUBE / ROLLUP in Books Online.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On Tue, 5 Sep 2006 12:31:02 -0700, Maury Markowitz wrote:
(snip)
>What's got me stumped is the "everything up to now", and I think I might be
>doing it entirely the wrong way. Normally I would make two subqueries, A an
d
>B, which are the month-by-month rollups. I then have a WHERE that joins the
m
>B.trandate <= A.trandate. But since the data has been grouped by month, the
>actual date is no longer available (it's been "grouped out" of the results)
.
>So I'm trying to come up with a way to compare these.
>1) MONTH(B.date) < MONTH(A.data) AND YEAR(B.date) < YEAR(A.data) does not
>work, because if you have something like 6/2005 it will consider that to fa
il
>against the date 7/2004, because 7 > 6. Is there a way to fix this?
>2) I tried making up a "fake date" with YEAR(date) + MONTH(date) and
>comparing those, but SQL Server uses months with no leading zero, so you en
d
>up with 200412 being smaller than 20043, and if you cast them to numbers th
en
>200412 is bigger than 20053. Is there some way to make this work?!
Hi Maury,
I suppose your current queries do the grouping by month with something
like this:
GROUP BY YEAR(TheDate), MONTH(TheDate)
Right?
If you change it to
GROUP BY DATEDIFF(month, '19000101', TheDate)
you can easily compare dates
WHERE DATEDIFF(month, '19000101', Date1) >
DATEDIFF(month, '19000101', Date2)
or get back to datetime format (at first day of the month):
SELECT DATEADD(month, DATEDIFF(month, '19000101', Date1), '19000101')
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" wrote:

> If you change it to
> GROUP BY DATEDIFF(month, '19000101', TheDate)
> you can easily compare dates
> WHERE DATEDIFF(month, '19000101', Date1) >
> DATEDIFF(month, '19000101', Date2)
> or get back to datetime format (at first day of the month):
> SELECT DATEADD(month, DATEDIFF(month, '19000101', Date1), '19000101')
Thanks, I'll try that!
Maurysql

Monthly rollups, tricky SQL question?

I'm trying to build a report that shows any user's purchases per month, along
with the resulting balance. That is proving tricky, and to add to the
confusion I have to add rows even where there was no purchases/sales, as long
as the remaining balance was not zero.
Doing the monthly rollup is easy enough with a group by on MONTH(trandate).
Adding rows for "empty" months was also fairly easy, I did a outer join on a
table of dates (is there an easier way to do this?).
What's got me stumped is the "everything up to now", and I think I might be
doing it entirely the wrong way. Normally I would make two subqueries, A and
B, which are the month-by-month rollups. I then have a WHERE that joins them
B.trandate <= A.trandate. But since the data has been grouped by month, the
actual date is no longer available (it's been "grouped out" of the results).
So I'm trying to come up with a way to compare these.
1) MONTH(B.date) < MONTH(A.data) AND YEAR(B.date) < YEAR(A.data) does not
work, because if you have something like 6/2005 it will consider that to fail
against the date 7/2004, because 7 > 6. Is there a way to fix this?
2) I tried making up a "fake date" with YEAR(date) + MONTH(date) and
comparing those, but SQL Server uses months with no leading zero, so you end
up with 200412 being smaller than 20043, and if you cast them to numbers then
200412 is bigger than 20053. Is there some way to make this work?!
3) maybe I'm doing it all wrong! Is there some way I could get the raw data
without grouping, join on the original dates (I have a dbo.FirstDayOfMonth
that would help here) and then do the grouping? It seems this should work,
but when I try it it seems to be difficult to fold the outer join back in, it
needs to refer to data in the A subquery, and if there is a way to do this I
can't figure out the syntax (at least not in an outer join, maybe a WHERE is
the way to go?)
MauryMaury Markowitz wrote:
> I'm trying to build a report that shows any user's purchases per month, along
> with the resulting balance. That is proving tricky, and to add to the
> confusion I have to add rows even where there was no purchases/sales, as long
> as the remaining balance was not zero.
> Doing the monthly rollup is easy enough with a group by on MONTH(trandate).
> Adding rows for "empty" months was also fairly easy, I did a outer join on a
> table of dates (is there an easier way to do this?).
> What's got me stumped is the "everything up to now", and I think I might be
> doing it entirely the wrong way. Normally I would make two subqueries, A and
> B, which are the month-by-month rollups. I then have a WHERE that joins them
> B.trandate <= A.trandate. But since the data has been grouped by month, the
> actual date is no longer available (it's been "grouped out" of the results).
> So I'm trying to come up with a way to compare these.
> 1) MONTH(B.date) < MONTH(A.data) AND YEAR(B.date) < YEAR(A.data) does not
> work, because if you have something like 6/2005 it will consider that to fail
> against the date 7/2004, because 7 > 6. Is there a way to fix this?
> 2) I tried making up a "fake date" with YEAR(date) + MONTH(date) and
> comparing those, but SQL Server uses months with no leading zero, so you end
> up with 200412 being smaller than 20043, and if you cast them to numbers then
> 200412 is bigger than 20053. Is there some way to make this work?!
> 3) maybe I'm doing it all wrong! Is there some way I could get the raw data
> without grouping, join on the original dates (I have a dbo.FirstDayOfMonth
> that would help here) and then do the grouping? It seems this should work,
> but when I try it it seems to be difficult to fold the outer join back in, it
> needs to refer to data in the A subquery, and if there is a way to do this I
> can't figure out the syntax (at least not in an outer join, maybe a WHERE is
> the way to go?)
> Maury
Take a look at CUBE / ROLLUP in Books Online.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On Tue, 5 Sep 2006 12:31:02 -0700, Maury Markowitz wrote:
(snip)
>What's got me stumped is the "everything up to now", and I think I might be
>doing it entirely the wrong way. Normally I would make two subqueries, A and
>B, which are the month-by-month rollups. I then have a WHERE that joins them
>B.trandate <= A.trandate. But since the data has been grouped by month, the
>actual date is no longer available (it's been "grouped out" of the results).
>So I'm trying to come up with a way to compare these.
>1) MONTH(B.date) < MONTH(A.data) AND YEAR(B.date) < YEAR(A.data) does not
>work, because if you have something like 6/2005 it will consider that to fail
>against the date 7/2004, because 7 > 6. Is there a way to fix this?
>2) I tried making up a "fake date" with YEAR(date) + MONTH(date) and
>comparing those, but SQL Server uses months with no leading zero, so you end
>up with 200412 being smaller than 20043, and if you cast them to numbers then
>200412 is bigger than 20053. Is there some way to make this work?!
Hi Maury,
I suppose your current queries do the grouping by month with something
like this:
GROUP BY YEAR(TheDate), MONTH(TheDate)
Right?
If you change it to
GROUP BY DATEDIFF(month, '19000101', TheDate)
you can easily compare dates
WHERE DATEDIFF(month, '19000101', Date1) >
DATEDIFF(month, '19000101', Date2)
or get back to datetime format (at first day of the month):
SELECT DATEADD(month, DATEDIFF(month, '19000101', Date1), '19000101')
--
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" wrote:
> If you change it to
> GROUP BY DATEDIFF(month, '19000101', TheDate)
> you can easily compare dates
> WHERE DATEDIFF(month, '19000101', Date1) >
> DATEDIFF(month, '19000101', Date2)
> or get back to datetime format (at first day of the month):
> SELECT DATEADD(month, DATEDIFF(month, '19000101', Date1), '19000101')
Thanks, I'll try that!
Maury

Monthly Reports - Date Format

I have a report that charts our weekly sales and monthly sales. I use the
following SQL statement to reflect 7 days or 30 days, which works but not
properly. I'd like for my report to run from Sunday to Sunday for the 7-day
report, and from the 1st of the month to the end of the month, regardless of
the number of days in the month. How do I modfify my statement to do this?
Here's my SQL statement for the 30-day or monthly report:
SELECT receipt_date, SUM(total_amt) AS Total_AMT, receipt_no, stamp, COUNT
(*) AS TOT
FROM Sysadm.receipt
WHERE (receipt_date BETWEEN CONVERT(datetime, CONVERT(varchar, DATEADD(day,
-30, GETDATE()), 101), 101) AND CONVERT(varchar, DATEADD(day, 0, GETDATE()),
101), 101))
GROUP BY receipt_date, receipt_no, stamp
ORDER BY receipt_date
--
EnchantnetI used the following statement to run a report for the previous week, from
Saturday to Friday:
CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw, GETDATE()),
101), 101) AS StartDt,
CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 1 - DATEPART(dw, GETDATE()),
101), 101) AS EndDt
You could probably use a variation of this for your monthly report. I hope
this helps!
"Enchantnet" wrote:
> I have a report that charts our weekly sales and monthly sales. I use the
> following SQL statement to reflect 7 days or 30 days, which works but not
> properly. I'd like for my report to run from Sunday to Sunday for the 7-day
> report, and from the 1st of the month to the end of the month, regardless of
> the number of days in the month. How do I modfify my statement to do this?
> Here's my SQL statement for the 30-day or monthly report:
> SELECT receipt_date, SUM(total_amt) AS Total_AMT, receipt_no, stamp, COUNT
> (*) AS TOT
> FROM Sysadm.receipt
> WHERE (receipt_date BETWEEN CONVERT(datetime, CONVERT(varchar, DATEADD(day,
> -30, GETDATE()), 101), 101) AND CONVERT(varchar, DATEADD(day, 0, GETDATE()),
> 101), 101))
> GROUP BY receipt_date, receipt_no, stamp
> ORDER BY receipt_date
> --
> Enchantnet|||Hey DAW,
Thanks for the code. However, I'm a newbie at this sort of stuff and I get
the following error when I entered your code: "ADO error: Incorrect syntax
near the keyword 'AS'. Statement(s) could not be prepared. Deferred prepare
could not be completed." Any idea? Do I need to include or define the
StartDt and EndDt elsewhere?
--
Enchantnet
"daw" wrote:
> I used the following statement to run a report for the previous week, from
> Saturday to Friday:
> CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw, GETDATE()),
> 101), 101) AS StartDt,
> CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 1 - DATEPART(dw, GETDATE()),
> 101), 101) AS EndDt
> You could probably use a variation of this for your monthly report. I hope
> this helps!
> "Enchantnet" wrote:
> > I have a report that charts our weekly sales and monthly sales. I use the
> > following SQL statement to reflect 7 days or 30 days, which works but not
> > properly. I'd like for my report to run from Sunday to Sunday for the 7-day
> > report, and from the 1st of the month to the end of the month, regardless of
> > the number of days in the month. How do I modfify my statement to do this?
> > Here's my SQL statement for the 30-day or monthly report:
> >
> > SELECT receipt_date, SUM(total_amt) AS Total_AMT, receipt_no, stamp, COUNT
> > (*) AS TOT
> > FROM Sysadm.receipt
> > WHERE (receipt_date BETWEEN CONVERT(datetime, CONVERT(varchar, DATEADD(day,
> > -30, GETDATE()), 101), 101) AND CONVERT(varchar, DATEADD(day, 0, GETDATE()),
> > 101), 101))
> > GROUP BY receipt_date, receipt_no, stamp
> > ORDER BY receipt_date
> > --
> > Enchantnet|||I'm not sure what that particular error means, but your statement should look
something like:
select CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw,
GETDATE()), 101), 101) AS StartDt, CONVERT(datetime, CONVERT(nvarchar,
GETDATE() - 1 - DATEPART(dw, GETDATE()), 101), 101) AS EndDt
from ....
"Enchantnet" wrote:
> Hey DAW,
> Thanks for the code. However, I'm a newbie at this sort of stuff and I get
> the following error when I entered your code: "ADO error: Incorrect syntax
> near the keyword 'AS'. Statement(s) could not be prepared. Deferred prepare
> could not be completed." Any idea? Do I need to include or define the
> StartDt and EndDt elsewhere?
> --
> Enchantnet
>
> "daw" wrote:
> > I used the following statement to run a report for the previous week, from
> > Saturday to Friday:
> > CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw, GETDATE()),
> > 101), 101) AS StartDt,
> > CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 1 - DATEPART(dw, GETDATE()),
> > 101), 101) AS EndDt
> >
> > You could probably use a variation of this for your monthly report. I hope
> > this helps!
> >
> > "Enchantnet" wrote:
> >
> > > I have a report that charts our weekly sales and monthly sales. I use the
> > > following SQL statement to reflect 7 days or 30 days, which works but not
> > > properly. I'd like for my report to run from Sunday to Sunday for the 7-day
> > > report, and from the 1st of the month to the end of the month, regardless of
> > > the number of days in the month. How do I modfify my statement to do this?
> > > Here's my SQL statement for the 30-day or monthly report:
> > >
> > > SELECT receipt_date, SUM(total_amt) AS Total_AMT, receipt_no, stamp, COUNT
> > > (*) AS TOT
> > > FROM Sysadm.receipt
> > > WHERE (receipt_date BETWEEN CONVERT(datetime, CONVERT(varchar, DATEADD(day,
> > > -30, GETDATE()), 101), 101) AND CONVERT(varchar, DATEADD(day, 0, GETDATE()),
> > > 101), 101))
> > > GROUP BY receipt_date, receipt_no, stamp
> > > ORDER BY receipt_date
> > > --
> > > Enchantnet|||Sorry, I had that wrong as far as what you need. Try this:
select ....
from...
where date between CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 -
DATEPART(dw, GETDATE()), 101), 101) AND CONVERT(datetime, CONVERT(nvarchar,
GETDATE() - 1 - DATEPART(dw, GETDATE()), 101), 101)
"daw" wrote:
> I'm not sure what that particular error means, but your statement should look
> something like:
> select CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw,
> GETDATE()), 101), 101) AS StartDt, CONVERT(datetime, CONVERT(nvarchar,
> GETDATE() - 1 - DATEPART(dw, GETDATE()), 101), 101) AS EndDt
> from ....
> "Enchantnet" wrote:
> > Hey DAW,
> >
> > Thanks for the code. However, I'm a newbie at this sort of stuff and I get
> > the following error when I entered your code: "ADO error: Incorrect syntax
> > near the keyword 'AS'. Statement(s) could not be prepared. Deferred prepare
> > could not be completed." Any idea? Do I need to include or define the
> > StartDt and EndDt elsewhere?
> > --
> > Enchantnet
> >
> >
> > "daw" wrote:
> >
> > > I used the following statement to run a report for the previous week, from
> > > Saturday to Friday:
> > > CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw, GETDATE()),
> > > 101), 101) AS StartDt,
> > > CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 1 - DATEPART(dw, GETDATE()),
> > > 101), 101) AS EndDt
> > >
> > > You could probably use a variation of this for your monthly report. I hope
> > > this helps!
> > >
> > > "Enchantnet" wrote:
> > >
> > > > I have a report that charts our weekly sales and monthly sales. I use the
> > > > following SQL statement to reflect 7 days or 30 days, which works but not
> > > > properly. I'd like for my report to run from Sunday to Sunday for the 7-day
> > > > report, and from the 1st of the month to the end of the month, regardless of
> > > > the number of days in the month. How do I modfify my statement to do this?
> > > > Here's my SQL statement for the 30-day or monthly report:
> > > >
> > > > SELECT receipt_date, SUM(total_amt) AS Total_AMT, receipt_no, stamp, COUNT
> > > > (*) AS TOT
> > > > FROM Sysadm.receipt
> > > > WHERE (receipt_date BETWEEN CONVERT(datetime, CONVERT(varchar, DATEADD(day,
> > > > -30, GETDATE()), 101), 101) AND CONVERT(varchar, DATEADD(day, 0, GETDATE()),
> > > > 101), 101))
> > > > GROUP BY receipt_date, receipt_no, stamp
> > > > ORDER BY receipt_date
> > > > --
> > > > Enchantnet|||Perfect! Thank u, Thank u, Thank u!!!!!
--
Enchantnet
"daw" wrote:
> Sorry, I had that wrong as far as what you need. Try this:
> select ....
> from...
> where date between CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 -
> DATEPART(dw, GETDATE()), 101), 101) AND CONVERT(datetime, CONVERT(nvarchar,
> GETDATE() - 1 - DATEPART(dw, GETDATE()), 101), 101)
> "daw" wrote:
> > I'm not sure what that particular error means, but your statement should look
> > something like:
> >
> > select CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw,
> > GETDATE()), 101), 101) AS StartDt, CONVERT(datetime, CONVERT(nvarchar,
> > GETDATE() - 1 - DATEPART(dw, GETDATE()), 101), 101) AS EndDt
> > from ....
> >
> > "Enchantnet" wrote:
> >
> > > Hey DAW,
> > >
> > > Thanks for the code. However, I'm a newbie at this sort of stuff and I get
> > > the following error when I entered your code: "ADO error: Incorrect syntax
> > > near the keyword 'AS'. Statement(s) could not be prepared. Deferred prepare
> > > could not be completed." Any idea? Do I need to include or define the
> > > StartDt and EndDt elsewhere?
> > > --
> > > Enchantnet
> > >
> > >
> > > "daw" wrote:
> > >
> > > > I used the following statement to run a report for the previous week, from
> > > > Saturday to Friday:
> > > > CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw, GETDATE()),
> > > > 101), 101) AS StartDt,
> > > > CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 1 - DATEPART(dw, GETDATE()),
> > > > 101), 101) AS EndDt
> > > >
> > > > You could probably use a variation of this for your monthly report. I hope
> > > > this helps!
> > > >
> > > > "Enchantnet" wrote:
> > > >
> > > > > I have a report that charts our weekly sales and monthly sales. I use the
> > > > > following SQL statement to reflect 7 days or 30 days, which works but not
> > > > > properly. I'd like for my report to run from Sunday to Sunday for the 7-day
> > > > > report, and from the 1st of the month to the end of the month, regardless of
> > > > > the number of days in the month. How do I modfify my statement to do this?
> > > > > Here's my SQL statement for the 30-day or monthly report:
> > > > >
> > > > > SELECT receipt_date, SUM(total_amt) AS Total_AMT, receipt_no, stamp, COUNT
> > > > > (*) AS TOT
> > > > > FROM Sysadm.receipt
> > > > > WHERE (receipt_date BETWEEN CONVERT(datetime, CONVERT(varchar, DATEADD(day,
> > > > > -30, GETDATE()), 101), 101) AND CONVERT(varchar, DATEADD(day, 0, GETDATE()),
> > > > > 101), 101))
> > > > > GROUP BY receipt_date, receipt_no, stamp
> > > > > ORDER BY receipt_date
> > > > > --
> > > > > Enchantnet

Monthly Reports

Hi,
Actually I am generating report by stored procedure by passing two parameters like START date and END date.
Now my problem is I need to generate report for every month seperately that fall in range of START date and END date.
thanks
IndiraIndira,
Group by Date, and when creating the group, if you use a date, Crystal will, by default, give the grouping options:
the date, in a dropdown
the order to sort by, in a dropdown,
the grouping frequency, in a dropdown (defaults to "for each day.")
change the last one to "for each month."
Crystal will then produce your summaries for each calendar month within the range.
You can then add a page break after this date group, to start anew page with a new month.
That's how you'd produce one report covering many months, aggregated by each month.
YOur question says you want to produce separate monthly reports.
If they are paper copies, you just restart your page numbering for each new group, print the report out, and then distribute the separate months pages as separate reports.
If you want to produce separate files, you will need to outline which method you want to use to distribute the separate reports, because thismight require code via VB or C etc...

Dave

Monthly report query

Someone please shed some light on how to write a select statement that will only pull out a bunch of records belongs only to a certain month. The field(sys_date) that keeps track of each record is a datatime field. Let's say that I need to select all the records starting from 03/01/2007 to 03/31/2007 at the end of March. I can't hardcode the dates because this report is scheduled to run at the end of every month via a DTS job in Sql 2000. Please help out. Thanks.

blumonde

Found the solution. Just in case anyone needs it:

Where (DATEPART(Month, sys_date) =
DATEPART(Month, GETDATE())) And (DATEPART(Year, sys_date) = DATEPART(Year,
GETDATE()))

Hope that helps.

Monthly parameter expressions [Formerly:Queried parameters]

Hello,

I need to be able to set the date parameters of a report dynamically when it is run based on system time. The problem I am having is being able to compare the dates (StartDate & EndDate) against [Service Date 1]. Essentially this report will only pull the current month's data.

The date fields being created with the GETDATE, DATEADD & DATEDIFF functions are working correctly. Do I need to create a separate dataset to be able to run the parameters automatically in the actual report?

Any help would be greatly appreciated!

SELECT TodaysDate =GetDate()-2,dbo.[Billing Detail].[Service Date 1], DATEADD(mm, DATEDIFF(mm, 0, DATEADD(yy, 0, GETDATE())), 0) AS StartDate, DATEADD(dd, - 1, DATEADD(mm, DATEDIFF(mm, -1, GETDATE()), 0)) AS EndDate, dbo.[Billing Detail].Billing, dbo.[Billing Detail].Chart, dbo.[Billing Detail].Item,
dbo.[Billing Detail].[Sub Item], dbo.Patient.[Patient Code], dbo.Patient.[Patient Type], dbo.[Billing Header].Charges, dbo.Practice.Name
FROM dbo.[Billing Detail] INNER JOIN
dbo.Patient ON dbo.[Billing Detail].Chart = dbo.Patient.[Chart Number] INNER JOIN
dbo.[Billing Header] ON dbo.[Billing Detail].Billing = dbo.[Billing Header].Billing CROSS JOIN
dbo.Practice
WHERE (dbo.[Billing Detail].Item = 0) AND (dbo.[Billing Detail].[Sub Item] = 0) AND (dbo.[Billing Detail].[Service Date 1] Between StartDate AND EndDate

Phorest,

You should be able to add the parameters to your query. If you are going against SQL Server, you can replace your parameters with @.StartDate AND @.EndDate. Then in the properies of the dataset, you can assign those parameters to Parameters!StartDate.Value and Parameters!EndDate.Value, respectively.

Jessica

|||

Thanks for your reply!

OK,

I think what I need to do is write the expression as a non-queried default value. However when I paste in what I know works in SQL Management Studio it returns an error "Name 'mm' is not declared"

<@.StartDate> =DATEADD(mm, DATEDIFF(mm, 0, DATEADD(yy, 0, GETDATE())), 0)

<@.EndDate> =DATEADD(dd, - 1, DATEADD(mm, DATEDIFF(mm, -1, GETDATE()), 0))

I tried putting an integer after DATEADD(mm, X , 102 DATEDIFF... but i can't get beyond intellisense. How can I fix my expression to work with Reporting Services?

What I need is to have expressions to choose the first day of the month to the last day of the same month compared to NOW()

|||

Apparently that is the trick to use non-queried default values as an expression, However what I posted yesterday will not work as an expression due to the expressions limitations in SSRS:

<@.StartDate> =DATEADD(mm, DATEDIFF(mm, 0, DATEADD(yy, 0, GETDATE())), 0)

<@.EndDate> =DATEADD(dd, - 1, DATEADD(mm, DATEDIFF(mm, -1, GETDATE()), 0))

Now I am using:

<@.StartDate> =DATEADD("D", -30, NOW())

<@.EndDate> =DATEADD("D", 1, NOW())

After much searching and experimentation I can get this to work well, but it isn't exactly what I want. Does any one have any tips as to being able to write the expression to select the first day of the current month and last day of the month?

It seems to be just beyond my grasp at this time...

Thanks!

|||

Phorest,

I'm afraid I misunderstood what you're trying to do. If you want a query that returns rows where the [Billing Detail].[Service Date 1] is between the start and the end of the current month, you can do that all in SQL.

It would look something similar to:

WHERE dbo.[Billing Detail].[Service Date 1]

BETWEEN dateadd(mm, datediff(mm,0,getdate()), 0)

AND dateadd(ms,-3,dateadd(mm, datediff(m,0,getdate() ) + 1, 0))

Does that work for you?

Jessica

|||

I'll have to try that in the SQL, though I was more after an expression more as a datetime datatype so it picks all the dates in the current month only and the user can then adjust the parameter manually after the initial running of the report if they so choose.

Thanks!

|||

I found what I was looking for here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1581230&SiteID=1

In the Report Parameters properties I set the DataType to DateTime and using the Default Values, Non-Queried radio button set the expressions like the following:

@.StartDate =DateSerial(Year(NOW()), Month(NOW)) +0,1) gives me the first date of the current month.

@.EndDate =DateSerial(Year(NOW()), Month(NOW)) +1,0) gives me the last date of the current month.

All is wellnow!

sql

Monthly date range substitution

I would like to run a report for each month over two years. I am currently
using a date range like this. Then manually substitute the error_time
bounds for each month and rerun the query. How can I script this so I can
programmatically perform the substitution in a loop. Thanx in advance.

select count(*) from application_errors
where error_message like 'Time%'
and error_time >= '1Apr2004' and error_time < '1May2004'Robert (robert.j.sipe@.boeing.com) writes:
> Maybe this is a lot easier to do than I first thought:
> select count(*) from application_errors
> where error_message like 'Time%'
> and error_time >= '1Jan2004' and error_time < '1Jan2005'
> group by month (error_time)
> This saves me a lot of work. Now, if I could figure out how to span years
> and still group by months...

select convert(char(6), error_time, 112), count(*) from application_errors
where error_message like 'Time%'
and error_time >= '1Jan2004'
group by convert(char(6), error_time, 112)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanx Erland, I am not worthy!!

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns975E38A7771EYazorman@.127.0.0.1...
> Robert (robert.j.sipe@.boeing.com) writes:
>> Maybe this is a lot easier to do than I first thought:
>>
>> select count(*) from application_errors
>> where error_message like 'Time%'
>> and error_time >= '1Jan2004' and error_time < '1Jan2005'
>> group by month (error_time)
>>
>> This saves me a lot of work. Now, if I could figure out how to span
>> years
>> and still group by months...
>
> select convert(char(6), error_time, 112), count(*) from
> application_errors
> where error_message like 'Time%'
> and error_time >= '1Jan2004'
> group by convert(char(6), error_time, 112)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Robert wrote:
> Thanx Erland, I am not worthy!!

You can as well use DATEPART to extract year and month from the timestamp
column.

robert

> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns975E38A7771EYazorman@.127.0.0.1...
>> Robert (robert.j.sipe@.boeing.com) writes:
>>> Maybe this is a lot easier to do than I first thought:
>>>
>>> select count(*) from application_errors
>>> where error_message like 'Time%'
>>> and error_time >= '1Jan2004' and error_time < '1Jan2005'
>>> group by month (error_time)
>>>
>>> This saves me a lot of work. Now, if I could figure out how to span
>>> years
>>> and still group by months...
>>
>>
>> select convert(char(6), error_time, 112), count(*) from
>> application_errors
>> where error_message like 'Time%'
>> and error_time >= '1Jan2004'
>> group by convert(char(6), error_time, 112)
>>
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>>
>> Books Online for SQL Server 2005 at
>>
http://www.microsoft.com/technet/pr...oads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Create a report range table:

CREATE TABLE ReportRanges
(range_name CHAR(15)
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date),
..);

INSERT INTO ReportRanges
VALUES ('2005 Jan', '2005-01-01', '2005-01-31 23:59:59.999');

INSERT INTO ReportRanges
VALUES ('2005 Feb', '2005-02-01', '2005-02-28 23:59:59.999');

etc.

INSERT INTO ReportRanges
VALUES ('2005 Total', '2005-01-01', '2005-12-31 23:59:59.999');

Now use it to drive all of your reports, so they will be consistent.

SELECT R.range_name, COUNT(*)
FROM AppErrors AS A, ReportRanges AS R
WHERE A.error_time BETWEEN R.start_date AND R.end_date
GROUP BY R.range_name;

You are making a classic newbie design flaw. You still think of
programming with procedural code and functions, but not with relational
operators.|||> CREATE TABLE ReportRanges
> (range_name CHAR(15)
> start_date DATETIME NOT NULL,
> end_date DATETIME NOT NULL,
> CHECK (start_date < end_date),
> ..);

This non-table is unusable. It has no key and cannot have a key because
range_name is NULLable.

> INSERT INTO ReportRanges
> VALUES ('2005 Jan', '2005-01-01', '2005-01-31 23:59:59.999');

Do you really think it a good idea to use the month name in the data like
this? What about other languages - French, Italian etc...

> SELECT R.range_name, COUNT(*)
> FROM AppErrors AS A, ReportRanges AS R
> WHERE A.error_time BETWEEN R.start_date AND R.end_date
> GROUP BY R.range_name;

You are still using the 89 syntax and should be using the more recent 92
syntax.

SELECT R.range_name, COUNT(*)
FROM AppErrors AS A
CROSS JOIN ReportRanges AS R
WHERE A.error_time BETWEEN R.start_date AND R.end_date
GROUP BY R.range_name;

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138908826.954654.5580@.g47g2000cwa.googlegrou ps.com...
> Create a report range table:
> CREATE TABLE ReportRanges
> (range_name CHAR(15)
> start_date DATETIME NOT NULL,
> end_date DATETIME NOT NULL,
> CHECK (start_date < end_date),
> ..);
> INSERT INTO ReportRanges
> VALUES ('2005 Jan', '2005-01-01', '2005-01-31 23:59:59.999');
> INSERT INTO ReportRanges
> VALUES ('2005 Feb', '2005-02-01', '2005-02-28 23:59:59.999');
> etc.
> INSERT INTO ReportRanges
> VALUES ('2005 Total', '2005-01-01', '2005-12-31 23:59:59.999');
> Now use it to drive all of your reports, so they will be consistent.
> SELECT R.range_name, COUNT(*)
> FROM AppErrors AS A, ReportRanges AS R
> WHERE A.error_time BETWEEN R.start_date AND R.end_date
> GROUP BY R.range_name;
> You are making a classic newbie design flaw. You still think of
> programming with procedural code and functions, but not with relational
> operators.|||On 2 Feb 2006 11:33:47 -0800, --CELKO-- wrote:

>Create a report range table:
>CREATE TABLE ReportRanges
>(range_name CHAR(15)
> start_date DATETIME NOT NULL,
> end_date DATETIME NOT NULL,
> CHECK (start_date < end_date),
> ..);
>INSERT INTO ReportRanges
>VALUES ('2005 Jan', '2005-01-01', '2005-01-31 23:59:59.999');
>INSERT INTO ReportRanges
>VALUES ('2005 Feb', '2005-02-01', '2005-02-28 23:59:59.999');
>etc.
>INSERT INTO ReportRanges
>VALUES ('2005 Total', '2005-01-01', '2005-12-31 23:59:59.999');

Hi Joe,

1. Never omit the column list of an INSERT. THis, like SELECT *, is
extremely bad practice.

2. Please use unambiguous date formats:

* yyyymmdd for date only
* yyyy-mm-ddThh:mm:ss or yyyy-mm-ddThh:mm:ss.ttt for date plus time
(with or without milliseconds).

3. Because SQL Server has datetime precision of 1/300 seecond, the
values for end_date will be rounded UP to 2005-02-01T00:00:00.000,
2005-03-01T00:00:00.000, and 2006-01-01T00:00:00.000. Not the values you
want with the query you propose...

>Now use it to drive all of your reports, so they will be consistent.
>SELECT R.range_name, COUNT(*)
> FROM AppErrors AS A, ReportRanges AS R
>WHERE A.error_time BETWEEN R.start_date AND R.end_date
> GROUP BY R.range_name;

.... however, this query is no good either. Never use BETWEEN for date
comparisons.

You should populate the Reportanges table as follows:

INSERT INTO ReportRanges (range_name, start_date, end_date)
VALUES ('2005 Jan', '20050101', '20050201');
INSERT INTO ReportRanges (range_name, start_date, end_date)
VALUES ('2005 Feb', '20050201', '20050301');
(...)
INSERT INTO ReportRanges (range_name, start_date, end_date)
VALUES ('2005 Total', '20050101', '20060101');

And change the query to

SELECT R.range_name, COUNT(*)
FROM AppErrors AS A
INNER JOIN ReportRanges AS R
ON A.error_time >= R.start_date
AND A.error_time < R.end_date
GROUP BY R.range_name;

(Note the use of greater _OR EQUAL_ for start_date, but lesser (and not
equal) for end_date).
This will always work - both for datetime and smalldatetime, and it will
continue to work if Microsoft ever decides to change the precision of
their datetime datatypes.

--
Hugo Kornelis, SQL Server MVP|||>> Note the use of greater _OR EQUAL_ for start_date, but lesser (and not equal) for end_date). This will always work - both for datetime and smalldatetime, and it will
continue to work if Microsoft ever decides to change the precision of
their datetime datatypes. <<

Good point. I keep forgetting that SQL Server does not follow the
FIPS-127 rules about keeping at least five decimal places of seconds
like other products. Generally goiing to 1/100 of a second has worked
for me in the real world -- CAST ('2006-01-01 23:59:59.99' AS
DATETIME).

If we had the OVERLAPS predicate, we could use that, but I prefer the
BETWEEN with adjusted times in the non-conformng SQLs I use. I can
move the code with a text change.|||On 3 Feb 2006 17:38:40 -0800, --CELKO-- wrote:

>>> Note the use of greater _OR EQUAL_ for start_date, but lesser (and not equal) for end_date). This will always work - both for datetime and smalldatetime, and it will
>continue to work if Microsoft ever decides to change the precision of
>their datetime datatypes. <<
>Good point. I keep forgetting that SQL Server does not follow the
>FIPS-127 rules about keeping at least five decimal places of seconds
>like other products. Generally goiing to 1/100 of a second has worked
>for me in the real world -- CAST ('2006-01-01 23:59:59.99' AS
>DATETIME).

Hi Joe,

This will still bite you if smalldatetime is used. Or if ever an entry
makes it into the datebase with a 23:59:99.993 timestamp.

What is your objection to
SomeDate >= StartOfInterval
AND SomeDate < EndOfInterval
(with EndOfInterval actually being equal to the first fraction of a
second after the end of the interval, or the start of the next interval
if there are consecutive intervals)

AFAICT, this will work on ALL products, regardless of the precision of
the date and time datatypes used in the product. Am I wrong?

--
Hugo Kornelis, SQL Server MVP|||>> his will still bite you if smalldatetime is used. Or if ever an entrymakes it into the datebase with a 23:59:99.993 timestamp. <<

I never use SMALLDATETIME because it is soooo proprietary and does not
match the FIPS-127 requirements.

>> What is your objection to
SomeDate >= StartOfInterval
AND SomeDate < EndOfInterval <<

Mostly style and portable code. The BETWEEN predicate reads so much
better to a human. I would prefer OVERLAPS and some of Rick
Snodgrass's operators if i coudl get them.

>> FAICT, this will work on ALL products, regardless of the precision of the date and time datatypes used in the product. Am I wrong? <<

Yeah, yeah!! But I hate 5to split a single concept (between-ness) into
muliple predicates. I also hate a change of ORs when I can use IN(),
etc.

--|||On 4 Feb 2006 14:22:07 -0800, --CELKO-- wrote:

>>> his will still bite you if smalldatetime is used. Or if ever an entrymakes it into the datebase with a 23:59:99.993 timestamp. <<
>I never use SMALLDATETIME because it is soooo proprietary and does not
>match the FIPS-127 requirements.

Hi Joe,

So instead, you use DATETIME, which also is proprieatary, which also
doesn't match FIPS-127, and which takes twice the space. Good job. For a
table with mostly date columns, your performance will now be about twice
as slow.

>>> What is your objection to
> SomeDate >= StartOfInterval
> AND SomeDate < EndOfInterval <<
>Mostly style and portable code.

Style, like beauty, is in the eye of the beholder. So I won't comment on
that.

But "portable code"? <Cough!> Please tell me: what part of the code
above is not portable, and why?

> The BETWEEN predicate reads so much
>better to a human.

Maybe. But does '2006-02-28T23:59:59.997' also read better to a human
than '2006-03-01'?

SomeDate >= '2006-02-01'
AND SomeDate < '2006-03-01'

or

SomeDate BETWEEN '2006-02-01' AND '2006-02-28T23:59:59.997'

Are you really going to tell me that the latter reads better to a human?

--
Hugo Kornelis, SQL Server MVP

Month and date wrong way around

Hi,

I am querying a report that was written with reporting services, via a webpage in vs2005. However when I input a date field to query from i.e 14/01/2007, this produces an error because the report is seeing this as 01/14/2007, even though in the database the record shows 14/01/2007? Please can someone offer any advice what to check?

thanks,

Harry.

Is that report runs client side?

|||

It seems to happen when report is tested in 'preview mode', and when the report is deployed it happens on the client. An error results because it can't handle the DD/MM the wrong way around?

There must be something I'm missing here...?

Many thanks,

Harry

|||

Hi, Harry:

You should try to format your date before you using them.

You can check out this article about how to format the date in SSRS.

http://msdn2.microsoft.com/en-us/library/ms157328.aspx

I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance

|||

Hi,

I'm a little confused how I can use the conversions,

I'm passing StartDate AND EndDate from 2 txtboxes,, my data is SELECT par1, par2, par2 from tbldatabase1 WHERE par1 >@.StartDate and par1 <@.EndDate.

But the parameters from the date boxes are taken in the wrong wat around. Do you know how I can implement some code to change this?. My main visual studio pages are written in c#?.

Or would you do this in the query itself?

Any help would be greatly appreciated.

Thanks Harry.

|||

HI,camper :

If your referring toReport Manager displaying the DateTime in theparameter input box, then no, you cannotformat this. You can onlyformat the date within thereport itself, or like the example I posted as following, give the user a dropdownparameter list of dates.

Apparently you can set theparameter to a string and then it won't enter the time, though you'll have to convert it into a date before it runs against your dataset. This way though,report users can enter a non-date as aparameter.

For some of myreports I create aparameter dataset based on the table holding the dates.

SELECT DATENAME(day, MyDateField) + ' ' + DATENAME(month, MyDateField) + ' ' + DATENAME(year, MyDateField) AS Label, MyDateField AS Value
FROM MyTable
GROUP BY DATENAME(day, MyDateField) + ' ' + DATENAME(month, MyDateField) + ' ' + DATENAME(year, MyDateField), MyDateField
ORDER BY MyDateField

This creates 2 fields, 'Label' which is what the user selects from and 'Value' which the dataset uses to query on.
Create aparameter (@.MyParameterDate) and reference it against your main dataset e.g.

SELECT * FROM MyTable WHERE MyDateField = @.MyParameterDate

In yourReportparameters set the values to be from a query and select yourparameter dataset. Set the Value and Label datafields to the ones created above and ensure theparameter datatype is DateTime. You can change the aboveformat if you don't want dates displaying as '7 November 2005'

I hope the above information will be helpful. If you have any issues or concerns, please let

me know. It's my pleasure to be of assistance

|||

Hi,

I know it's a few months since the last entry but I was having the same problems and fixed it by setting the Report properties: Language Property to my locale (English (United Kingdom) in this case). You can get at these properties from the report designer (in local mode) and clicking on the grey area outside the design grid. This stopped the report from swapping the months and days around :-)

Good luck.

|||

Change this line in your model's smdl file

<Culture>de-DE</Culture>

In this case German yyyy.mm.dd

|||

I am having the same problem. I saw your solution and tried that. However, it appears that those settings do not get passed to the report server. The report now correctly allows entry of the date in the Visual Studio designer, and when selected shows the date correctly, however, when the report is delpyed, the server, does not change behavior. It still thinks it is using mm/dd/yyy format.

Any ideas how to change the server side format to allow dd/mm/yy parameter format entry?

|||I also had to change regional settings to YYYY/MM/DD format on server. In layout view click properties on righthand side and select report from available fields, Click on language and choose English South Africa for YYYY/MM/DD format.sql

Monitor the daily performance report of SQL 2000.

What are the most necessary item to be checked to monitor
the daily performance report of SQL 2000.
Any list of system Stored procedure? Any list of tables.
If so please specify.
Thanks in advane.
AnandThanks for your reply.
>--Original Message--
>Anand,
>the first (and best) place to start looking is
>http://www.sql-server-performance.com
>--
>I hope this helps
>regards
>Greg O MCSD
>SQL Scribe Documentation Builder
>Document any SQL server database in minutes
>Programmers love it, DBA dream of it
>AGS SQL Scribe download a 30 day trial today
>http://www.ag-software.com/ags_scribe_index.asp
>
>"gurus" <gurusanand@.yahoo.com> wrote in message
>news:065201c35199$1ca11230$a501280a@.phx.gbl...
>> What are the most necessary item to be checked to
monitor
>> the daily performance report of SQL 2000.
>> Any list of system Stored procedure? Any list of tables.
>> If so please specify.
>> Thanks in advane.
>> Anand
>
>.
>

MOM Report Integration with Sharepoint Portal

All,
I work for a partner, and we use MOM 2005 in a "managed services" scenario
to monitor client environments, and are attempting to develop client-facing
sites within Sharepoint portal to display various reports and alert information.
This effort spawned the following question:
What would be the best way to provide a MOM "State View" (green/yellow/red)
within a Sharepoint site. I cannot find the state data within the Onepoint
database (nor any reference in the MOMSDK), nor any existing SPS web part
to fit the bill.
Any pointer to where in the database this data lies or opinions on the best
way to address the challenge would be appreciated.
Thanks,
PetMOM 2005 comes with a range of Reporting Services reports, probably
including the one you want. With Reporting Services SP 2, there comes 2 web
parts for SharePoint, the Report Explorer and the Report Viewer. So what you
can do is to set up Reporting Services, and then install the two RS Web
parts to your SharePOint site, and point the reports you want to see. I
think you can use the Report Viewer to show just one report, so you don't
show everyone all the other MOM reports.
Kaisa M. Lindahl
"Pete Zerger" <pete.zerger@.nospam.gmail.com> wrote in message
news:7e9e9bb33a01d8c792709c6ecd86@.msnews.microsoft.com...
> All,
> I work for a partner, and we use MOM 2005 in a "managed services" scenario
> to monitor client environments, and are attempting to develop
> client-facing sites within Sharepoint portal to display various reports
> and alert information. This effort spawned the following question:
> What would be the best way to provide a MOM "State View"
> (green/yellow/red) within a Sharepoint site. I cannot find the state data
> within the Onepoint database (nor any reference in the MOMSDK), nor any
> existing SPS web part to fit the bill.
> Any pointer to where in the database this data lies or opinions on the
> best way to address the challenge would be appreciated.
>
> Thanks,
> Pete
>

MOLAP or ROLAP

Hi,

I have to implement a report where a text search functionality is to be provided. There will be 3 search options for report users: whole phrase, some of the words or all of the words.

The user will key in the words to be searched and depending on the search option chosen, records must be displayed where two free text columns provide the match.

What would be the performance implications of implementing this in a MOLAP platform? Or is ROLAP more suited for this purpose?

Regards,

Emil

Hi Emil,

It's not obvious from your report description why you're considering an OLAP solution at all - is the data multi-dimensional?

|||

Hi Deepak,

The data is multi-dimensional. The columns where text search is to be performed holds problem and its solution. There are other dimensions on which most of the analysis of the data is done. The fact holds no aggregatable measures except count of records for the various dimensions.

I hope I have been able to give you some clarity.

Thanks and Regards,

Emil