Customize Prompt Message for Stored Procudure

Hello all,
I have a stored procedure that prompts the user for beginning date and
ending date to run a monthly report. The prompt says
Enter_Beginning_Date and Enter_Ending_Date. I want the prompt to say
Enter Beginning Date (Example:1-1-2003) or something like that. Is
there a way to do this?

CREATE PROCEDURE dbo.MonthlyReport(@.Enter_Beginning_Date datetime,
@.Enter_Ending_Date datetime)
AS SELECT incident, @.Enter_Beginning_Date AS BeginningDate,
@.Enter_Ending_Date AS EndingDate, COUNT(*) AS Occurances
FROM dbo.Incident
WHERE (DateOccured BETWEEN @.Enter_Beginning_Date AND
@.Enter_Ending_Date)
GROUP BY incident
GO"ndn_24_7" <ndn_24_7@.yahoo.com> wrote in message
news:1105983132.532065.27990@.c13g2000cwb.googlegro ups.com...
> Hello all,
> I have a stored procedure that prompts the user for beginning date and
> ending date to run a monthly report. The prompt says
> Enter_Beginning_Date and Enter_Ending_Date. I want the prompt to say
> Enter Beginning Date (Example:1-1-2003) or something like that. Is
> there a way to do this?
> CREATE PROCEDURE dbo.MonthlyReport(@.Enter_Beginning_Date datetime,
> @.Enter_Ending_Date datetime)
> AS SELECT incident, @.Enter_Beginning_Date AS BeginningDate,
> @.Enter_Ending_Date AS EndingDate, COUNT(*) AS Occurances
> FROM dbo.Incident
> WHERE (DateOccured BETWEEN @.Enter_Beginning_Date AND
> @.Enter_Ending_Date)
> GROUP BY incident
> GO

MSSQL is purely a server, so it doesn't have any idea about GUIs or
prompts - if you want to present a more user-friendly description of the two
parameters, then you would have to do that in the front-end application
where the users select the dates.

One possible approach would be to add an extended property to the two
parameters which has the description in it, then retrieve that from the
front end when you display the input screen. See "Using Extended Properties
on Database Objects" in Books Online for more details. But I don't know if
that would be a suitable solution for your toolset and design.

Simon|||I'm sorry
I should have been more discriptive. My program has a Access 2000 front
end and a SQL 2000 server backend. I have a button that the user clicks
that brings up the prompt window for the stored proc. So would I do
this on the Access front end, Where would Icustomize this message?|||Not a clue how you are prompting the user thru Stored Procedure. May be
you missed out some valuable info on the post........!|||ndn_24_7 (ndn_24_7@.yahoo.com) writes:
> I should have been more discriptive. My program has a Access 2000 front
> end and a SQL 2000 server backend. I have a button that the user clicks
> that brings up the prompt window for the stored proc. So would I do
> this on the Access front end, Where would Icustomize this message?

Sounds like you should try an Access newsgroup. It is possible that
you can use extended properties for this, but I have no knowledge
what Access makes use of. So try comp.databases.ms-access where the
expertise for this question might hang out.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||As far I understood,

You have a stored procedure that accepts parameters and you have an
Access front end that accepts input parameters thru prompts and pass
them to the backend stored procedure... Am I correct?

If so, what you are doing is correct so far. You need to find out how
to call a stored procedure thru access and once you know, all you need
to do is, thru access, get those inputs (you may display anyway you
need - that is irrelevent to the backend procedure...) and pass them as
parameters to the backend stored proc.. May be an access guru can show
some light on how to accomplish this..!|||"ndn_24_7" <ndn_24_7@.yahoo.com> wrote in message
news:1105990021.951317.205140@.c13g2000cwb.googlegr oups.com...
> I'm sorry
> I should have been more discriptive. My program has a Access 2000 front
> end and a SQL 2000 server backend. I have a button that the user clicks
> that brings up the prompt window for the stored proc. So would I do
> this on the Access front end, Where would Icustomize this message?

I suggest you want a form that the user enters the dates in and then clicks
the button to run the stored proc.
I'll assume you know a little about VB code.
Otherwise, you got a steep learning curve ahead mate.

You probably want to validate the fields using isdate()

Access now uses ADO, so the code involves using an ado connection and
command.
I found pretty much the below code by using google to search the access
newsgroup.
Not tested it and I had to add the execute line, so this is just to get you
started.
BTW You'll want to get used to doing such searches if you are new to this
lark.
I suggest also take a look at each of the bits in turn and read up using
msdn so you get a better understanding what you're up to.
I have deliberately not changed the parameter type to date and input because
you'd learn stuff all if I just gave you the code.

There's a couple gotchas with datetime. Inside access some bits want this
delimited by # but not with ado. Remember also the time bit of a date is
likely there. Not such a problem with > or < but throw that to the back of
your mind for later.

Anyhow.
You run the thing by using the execute method of the command.
There's a parameter collection associated with a command you add the values
to:

'start untested snippet
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "stored procedurename"
cmd.CommandType = adCmdStoredProc
Set prm = cmd.CreateParameter("@.CompanyID", adInteger, adParamOutput _
, , forms!yourformname!text1.Value)
cmd.Parameters.Append prm

cmd.execute

set prm = Nothing
Set cmd = Nothing

' end snippet

HTH

--
Regards,
Andy O'Neill

Customising Date Picker

O Great Beings of MSDN,

After much fruitless search I am requesting help on whether there's any way to disable some of the dates in SSRS date picker control.

I have a date parameter in which I want users only be able to select the Fridays. I could show a drop down list of Fridays but that doesn't look too flash, especially when the list has about 2K Fridays.

Any help would be appreciated.

The parameter controls on the standard toolbar cannot be customized. If this is a must have requirement, you need to implement a custom application front end and use the ASP.NET Report Viewer (which is what the Report Manager uses).|||

Thanks Teo,

I think I will have to give up on this one as we already have a custom report protal on which all the RS reports and other reports are displayed. Having another application fire up to collect parameters is not an option for me so that's a bit of bugger.

Hope microsoft can utilise the dataset section in order to provide this in the next service pack. Ie, it would be so much easier if we could have the option of having a list of dates defined in a data set and hence when connected to the datetime parameter, it could use this list to limit the date picker dates that could be chosen by user (having dates that were not in the dataset disabled).

Fortunately the data for this particular report for my company is valid since June 2006 so have implemented the datetime as a drop down list for now (only showing fridays dates after 2006) without the datepicker so the list of Fridays isn't too big just yet.

Totally unsatisfactory from my point of view but that's the best that I could come up with.

customers in 2003, not 2004 (was "Help with (probably) simple query")

I have 2 tables showing customers from 2003 and 2004.
They show customer number, customer name, and date attended.
I need to show what customers came in 2003, but not in 2004 by name. Problem with the customer number is that some times the customers lose their cards and get new numbers.

Any help is appreciated.You will still have difference if also the name changed!
But you could try:
SELECT NAME FROM TAB2003
MINUS
SELECT NAME FROM TAB2004;
;)

custom view according to date

I want to add custom view so that it show the records that their date
is less than a specific field like "2007/12/25". Dates are saved in DB like "2006/08/29 12:00:00 A.M" nad
smalldatetime format in MSSQL.
what should I do?

The easiest way to achieve that using SQL statement.

Did you try it?

|||

I did it but It has no results.could you pu the sql query code here

thanks,

|||

Whatever you are binding the field to, probably has a DataFormatString property, and a HtmlEncode property. Set the DataFormatString property to {0:d} and set the HtmlEncode property to false and see if that is what you are looking for. If that isn't it (Which uses whatever format you have set in your control panel's short date format), you can force it to a specific format by setting the DataFormatString to {0:yyyy/MM/dd}

PS. Please read up on data types. The database can not both store dates like "2006/01/01 12:00:00 PM" and also store them in a smalldatetime. It's one or the other. If it's storing them into a smalldatetime, then it's stored as a smalldatetime which is not a string format. If you are storing them as a varchar/char/nchar in YYYY/MM/DD HH:MM:SS format, then they aren't stored as a smalldatetime.

|||

If you write VB.NET you have two DateDiff functions to use. Try the links below for details. Hope this helps.

http://blogs.msdn.com/vbfaq/

http://www.stanford.edu/~bsuter/sql-datecomputations.html

custom flat file for log transactions

Hi,
I am new at reporting services and need help creating a flat file that
will contain transaction logs like userid, amount, date and a comment
line.
I do not want the full report history since that would take a lot of
space on the server.
Is there such a way to create a flat file with plain text
characters(maybe csv or just spaces...) with only the information I
want to provide and to the folder/file I specify?
Thanks.
BTW, this is also my first posting ever.If the data is already in a database table, you should be able to create a
dataset against it, pull the data (i.e., only the columns and rows you want)
into a report table, publish the report, and export it to CSV. Check BOL for
samples and walkthroughs:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSAMPLES/htm/rss_overview_v1_631v.asp?frame=true.
There's also a How To section in BOL:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_4bhu.asp?frame=true.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"A Gutie" <fiututor@.yahoo.com> wrote in message
news:eca873f7.0408030527.27d8e376@.posting.google.com...
> Hi,
> I am new at reporting services and need help creating a flat file that
> will contain transaction logs like userid, amount, date and a comment
> line.
> I do not want the full report history since that would take a lot of
> space on the server.
> Is there such a way to create a flat file with plain text
> characters(maybe csv or just spaces...) with only the information I
> want to provide and to the folder/file I specify?
> Thanks.
> BTW, this is also my first posting ever.|||Hi Ravi,
This report does not use any tables but only textboxes that are
populated from different datasets. All is working fine.
EXCEPT: I need to create a text file(could be csv or just plain text
separated by blanks...) that will get appended information to it. It
will contain a few fields from the report. This process MUST be made
automatically without having the user click on the 'export' link. In
other words, like behind the scenes event when clicking on the 'view
report' button.
What I have done is to create a dataset that has an insert but this is
adding the fields to a table I created for testing. If I could just
make the 'insert' send the output to a text file instead of a table
will do all I need.
if you know how, please let me know. I have not had any luck sending
the output to a file from an insert - have not found any documentation
on it but just DTS code that will not suffice.
I apologize for not giving specific details.
Thanks again.
"Ravi Mumulla \(Microsoft\)" <ravimu@.online.microsoft.com> wrote in message news:<OqoCEoXeEHA.1644@.tk2msftngp13.phx.gbl>...
> If the data is already in a database table, you should be able to create a
> dataset against it, pull the data (i.e., only the columns and rows you want)
> into a report table, publish the report, and export it to CSV. Check BOL for
> samples and walkthroughs:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSAMPLES/htm/rss_overview_v1_631v.asp?frame=true.
> There's also a How To section in BOL:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_4bhu.asp?frame=true.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "A Gutie" <fiututor@.yahoo.com> wrote in message
> news:eca873f7.0408030527.27d8e376@.posting.google.com...
> > Hi,
> > I am new at reporting services and need help creating a flat file that
> > will contain transaction logs like userid, amount, date and a comment
> > line.
> > I do not want the full report history since that would take a lot of
> > space on the server.
> > Is there such a way to create a flat file with plain text
> > characters(maybe csv or just spaces...) with only the information I
> > want to provide and to the folder/file I specify?
> > Thanks.
> > BTW, this is also my first posting ever.|||Sounds like execution log will give you what you're looking for. Take a look
at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm/arp_rslogfiles_v1_88gy.asp?frame=true.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"A Gutie" <fiututor@.yahoo.com> wrote in message
news:eca873f7.0408040544.744a044@.posting.google.com...
> Hi Ravi,
> This report does not use any tables but only textboxes that are
> populated from different datasets. All is working fine.
> EXCEPT: I need to create a text file(could be csv or just plain text
> separated by blanks...) that will get appended information to it. It
> will contain a few fields from the report. This process MUST be made
> automatically without having the user click on the 'export' link. In
> other words, like behind the scenes event when clicking on the 'view
> report' button.
> What I have done is to create a dataset that has an insert but this is
> adding the fields to a table I created for testing. If I could just
> make the 'insert' send the output to a text file instead of a table
> will do all I need.
> if you know how, please let me know. I have not had any luck sending
> the output to a file from an insert - have not found any documentation
> on it but just DTS code that will not suffice.
> I apologize for not giving specific details.
> Thanks again.
> "Ravi Mumulla \(Microsoft\)" <ravimu@.online.microsoft.com> wrote in
message news:<OqoCEoXeEHA.1644@.tk2msftngp13.phx.gbl>...
> > If the data is already in a database table, you should be able to create
a
> > dataset against it, pull the data (i.e., only the columns and rows you
want)
> > into a report table, publish the report, and export it to CSV. Check BOL
for
> > samples and walkthroughs:
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSAMPLES/htm/rss_overview_v1_631v.asp?frame=true.
> > There's also a How To section in BOL:
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_4bhu.asp?frame=true.
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > "A Gutie" <fiututor@.yahoo.com> wrote in message
> > news:eca873f7.0408030527.27d8e376@.posting.google.com...
> > > Hi,
> > > I am new at reporting services and need help creating a flat file that
> > > will contain transaction logs like userid, amount, date and a comment
> > > line.
> > > I do not want the full report history since that would take a lot of
> > > space on the server.
> > > Is there such a way to create a flat file with plain text
> > > characters(maybe csv or just spaces...) with only the information I
> > > want to provide and to the folder/file I specify?
> > > Thanks.
> > > BTW, this is also my first posting ever.|||Report Server Execution Log Data would had been great but it does not
contain information from a dataset but only parameter
information(according to the specs).
This link is very good but it does not include values from dataset
if I could just include field values from dataset then it'd do.
any other hints or maybe the execution log may be modified to include
values from datasets?
Thanks again
"Ravi Mumulla \(Microsoft\)" <ravimu@.online.microsoft.com> wrote in message news:<uO7gHJoeEHA.2396@.TK2MSFTNGP11.phx.gbl>...
> Sounds like execution log will give you what you're looking for. Take a look
> at
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm/arp_rslogfiles_v1_88gy.asp?frame=true.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "A Gutie" <fiututor@.yahoo.com> wrote in message
> news:eca873f7.0408040544.744a044@.posting.google.com...
> > Hi Ravi,
> >
> > This report does not use any tables but only textboxes that are
> > populated from different datasets. All is working fine.
> >
> > EXCEPT: I need to create a text file(could be csv or just plain text
> > separated by blanks...) that will get appended information to it. It
> > will contain a few fields from the report. This process MUST be made
> > automatically without having the user click on the 'export' link. In
> > other words, like behind the scenes event when clicking on the 'view
> > report' button.
> >
> > What I have done is to create a dataset that has an insert but this is
> > adding the fields to a table I created for testing. If I could just
> > make the 'insert' send the output to a text file instead of a table
> > will do all I need.
> > if you know how, please let me know. I have not had any luck sending
> > the output to a file from an insert - have not found any documentation
> > on it but just DTS code that will not suffice.
> > I apologize for not giving specific details.
> > Thanks again.
> >
> > "Ravi Mumulla \(Microsoft\)" <ravimu@.online.microsoft.com> wrote in
> message news:<OqoCEoXeEHA.1644@.tk2msftngp13.phx.gbl>...
> > > If the data is already in a database table, you should be able to create
> a
> > > dataset against it, pull the data (i.e., only the columns and rows you
> want)
> > > into a report table, publish the report, and export it to CSV. Check BOL
> for
> > > samples and walkthroughs:
> > >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSAMPLES/htm/rss_overview_v1_631v.asp?frame=true.
> > > There's also a How To section in BOL:
> > >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_4bhu.asp?frame=true.
> > >
> > > --
> > > Ravi Mumulla (Microsoft)
> > > SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > > "A Gutie" <fiututor@.yahoo.com> wrote in message
> > > news:eca873f7.0408030527.27d8e376@.posting.google.com...
> > > > Hi,
> > > > I am new at reporting services and need help creating a flat file that
> > > > will contain transaction logs like userid, amount, date and a comment
> > > > line.
> > > > I do not want the full report history since that would take a lot of
> > > > space on the server.
> > > > Is there such a way to create a flat file with plain text
> > > > characters(maybe csv or just spaces...) with only the information I
> > > > want to provide and to the folder/file I specify?
> > > > Thanks.
> > > > BTW, this is also my first posting ever.

Custom Errors

Hi,

I am showing my report in the web application using Report Viewer control.In the report i have start date and End date parameters.While running the report if i give startdate as '45/66/20007' it is displaying a message which is not user friendly.Is there is any way we can handle this type of errors and display our own custom error messages.

Thanks in advance

Hello Mahima,

I was facing the same problem but I solved it through strored procedures. I passed my custom message in the RaiseError exception after the validation.

Example : This is not a solution for your problem, but I think you change the validation accordingly

IF (DATEDIFF(MM,@.StartReceivedDate,@.EndReceivedDate) > 12)

BEGIN

SET @.DateErrMsg = 'The Date Range should fall with in an year, Please correct the range.'

RAISERROR (@.DateErrMsg,18,1)

END

Hope this helps.

|||

Hi,

I try to implement that when the condition occured, it is displaying like in this way:

Query execution failed for DataSet1.

An error occured during local report processing

under this our raise error message is displaying.

Is there any way only to display our message which in raise error other than first 2 lines.

Thanks in advance

|||

I am using the (ReportErrorEventArgs e) from Microsoft.Reporting.WinForms namespace.

The way I have displayed our Raise Error exception is through the Message box.

Example:

MessageBox.Show(e.Exception.GetBaseException().Message, "Some message",MessageBoxButtons.OK, MessageBoxIcon.Error);

Then

I am hiding the " Query execution failed for DataSet1.

An error occured during local report processing

our raise error message is displaying."

with this expression : ReportErrorEventArgs.Handled = True.

In our requirement we need a messagebox to display ,but I am sure we can find a way to hide the first two lines and dispay only Raise Error execption message.

Hope this helps.....

|||

Hi,

Can you please tell me in which event you are handling this.

Can you please give me the detailed example or any other link which speciefied this event handling.

Thanks in advance

|||

It is the ReportError event for the ReportViewer.

private Microsoft.Reporting.WinForms.ReportViewer rptvSelReport;

Example:

private void rptvSelReport_ReportError(object sender, ReportErrorEventArgs e)

{

MessageBox.Show(e.Exception.GetBaseException().Message, DIALOG_FORM_CAPTION,MessageBoxButtons.OK, MessageBoxIcon.Error);

e.Handled = true;

Cursor.Current = Cursors.Arrow;

}

We have used this in our dev. environment and its working fine.

Let me know if you need any help....

|||

Hi,

Thanks for the information.I will try this and i will ask you when i have problem with this,

Thanks

|||

Custom Errors

Hi,

I am showing my report in the web application using Report Viewer control.In the report i have start date and End date parameters.While running the report if i give startdate as '45/66/20007' it is displaying a message which is not user friendly.Is there is any way we can handle this type of errors and display our own custom error messages.

Thanks in advance

Hello Mahima,

I was facing the same problem but I solved it through strored procedures. I passed my custom message in the RaiseError exception after the validation.

Example : This is not a solution for your problem, but I think you change the validation accordingly

IF(DATEDIFF(MM,@.StartReceivedDate,@.EndReceivedDate)> 12)

BEGIN

SET @.DateErrMsg ='The Date Range should fall with in an year, Please correct the range.'

RAISERROR(@.DateErrMsg,18,1)

END

Hope this helps.

|||

Hi,

I try to implement that when the condition occured, it is displaying like in this way:

Query execution failed for DataSet1.

An error occured during local report processing

under this our raise error message is displaying.

Is there any way only to display our message which in raise error other than first 2 lines.

Thanks in advance

|||

I am using the (ReportErrorEventArgs e) from Microsoft.Reporting.WinForms namespace.

The way I have displayed our Raise Error exception is through the Message box.

Example:

MessageBox.Show(e.Exception.GetBaseException().Message, "Some message",MessageBoxButtons.OK, MessageBoxIcon.Error);

Then

I am hiding the " Query execution failed for DataSet1.

An error occured during local report processing

our raise error message is displaying."

with this expression : ReportErrorEventArgs.Handled = True.

In our requirement we need a messagebox to display ,but I am sure we can find a way to hide the first two lines and dispay only Raise Error execption message.

Hope this helps.....

|||

Hi,

Can you please tell me in which event you are handling this.

Can you please give me the detailed example or any other link which speciefied this event handling.

Thanks in advance

|||

It is the ReportError event for the ReportViewer.

private Microsoft.Reporting.WinForms.ReportViewer rptvSelReport;

Example:

private void rptvSelReport_ReportError(object sender, ReportErrorEventArgs e)

{

MessageBox.Show(e.Exception.GetBaseException().Message, DIALOG_FORM_CAPTION,MessageBoxButtons.OK, MessageBoxIcon.Error);

e.Handled = true;

Cursor.Current = Cursors.Arrow;

}

We have used this in our dev. environment and its working fine.

Let me know if you need any help....

|||

Hi,

Thanks for the information.I will try this and i will ask you when i have problem with this,

Thanks

|||

Custom Date Ranges

I'm currently using Reporting Services for SQL Server 2005. I have been able to setup, and configure the Report Manager interface, as well as generate reports via the Report Builder. What I have been unable to do is allow the user to dynamically set the date range that my SQL query will use. Can someone suggest / is it even possible wihtout using a custom web interface?

If you are using BI studio to build the report, you need to create two parameters, say StartDate and EndDate. Set the data type for both parameters to DateTime. Then in the data set query, in the where clause, you'll need to do something like DateTimeColumn BETWEEN @.StartDate AND DATEADD(d, 1, @.EndDate). HTH.

Custom date format...

Hi,
I'm just trying to get custom date formatting working. I have a datetime
variable plotted on an x axis of a scatter plot. The axis is marked as
continuous ('numeric of timescale values').
I'd like the time to appear as eg 'Jan 04' and thought this would be
possible based on the posting by Robert Bruckner in response to 'Custom Date
Format' (09/Apr) in which he said that one could use these functions
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomdatetimeformatstrings.asp
However I cannot get any of these codes to work. I have tried putting the
codes in or by putting the format code as this:
=Format(Fields!calDay, "MMM")
Woudl be very grateful if someone could tell me whether this is possible and
if so how I should go about it.
Thanks,
Phil
PS I don't want to convert to a string as I have lots of values and quite
like keeping it as a continuous axis.Pull up chart properties dialog, go to X-axis tab, check "Numeric and
Timescale Values" checkbox, check the "Show Labels" checkbox, and enter "MMM
yy" in the Format code textbox (without the double-quotes.)
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Phil Aldis" <PhilAldis@.discussions.microsoft.com> wrote in message
news:5D4AF02E-618F-4FAD-9E52-473A5EAFBB70@.microsoft.com...
> Hi,
> I'm just trying to get custom date formatting working. I have a datetime
> variable plotted on an x axis of a scatter plot. The axis is marked as
> continuous ('numeric of timescale values').
> I'd like the time to appear as eg 'Jan 04' and thought this would be
> possible based on the posting by Robert Bruckner in response to 'Custom
Date
> Format' (09/Apr) in which he said that one could use these functions:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomdatetimeformatstrings.asp
> However I cannot get any of these codes to work. I have tried putting the
> codes in or by putting the format code as this:
> =Format(Fields!calDay, "MMM")
> Woudl be very grateful if someone could tell me whether this is possible
and
> if so how I should go about it.
> Thanks,
> Phil
> PS I don't want to convert to a string as I have lots of values and quite
> like keeping it as a continuous axis.
>
>
>|||Thanks - works perfectly.
Phil
"Ravi Mumulla (Microsoft)" wrote:
> Pull up chart properties dialog, go to X-axis tab, check "Numeric and
> Timescale Values" checkbox, check the "Show Labels" checkbox, and enter "MMM
> yy" in the Format code textbox (without the double-quotes.)
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Phil Aldis" <PhilAldis@.discussions.microsoft.com> wrote in message
> news:5D4AF02E-618F-4FAD-9E52-473A5EAFBB70@.microsoft.com...
> > Hi,
> >
> > I'm just trying to get custom date formatting working. I have a datetime
> > variable plotted on an x axis of a scatter plot. The axis is marked as
> > continuous ('numeric of timescale values').
> >
> > I'd like the time to appear as eg 'Jan 04' and thought this would be
> > possible based on the posting by Robert Bruckner in response to 'Custom
> Date
> > Format' (09/Apr) in which he said that one could use these functions:
> >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomdatetimeformatstrings.asp
> >
> > However I cannot get any of these codes to work. I have tried putting the
> > codes in or by putting the format code as this:
> >
> > =Format(Fields!calDay, "MMM")
> >
> > Woudl be very grateful if someone could tell me whether this is possible
> and
> > if so how I should go about it.
> >
> > Thanks,
> >
> > Phil
> >
> > PS I don't want to convert to a string as I have lots of values and quite
> > like keeping it as a continuous axis.
> >
> >
> >
> >
> >
>
>

Custom Date Format

I need to change the date format from mmddyyyy to yyyymmdd and I'm not
quite sure how to accomplish it. Obviously there are default date
views, but yyyymmdd is not one of them. I'm guessing I need to create
an expression. Can someone help?
Thanks,
tpTry putting this into a textbox.
= Format(<Database Field>, "yyyyMMdd")
This should help with all formatting inquries:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomdatetimeformatstrings.asp
Regards,
Dan|||Thanks Dan. I figured it was an easy one...|||I went though the MSDN docs, and put together a small document which include
all of the formatting special characters which can be used...It might be
worth downloading and printing out if you do a lot of reporting services..
It is at www.msbicentral.com. It is in the articles section, titled
'Formatting Expressions in Reporting Services"
Hope this helps.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"newsgroups@.bendcycling.org" wrote:
> Thanks Dan. I figured it was an easy one...
>

Custom Code to Setup Parameter

I have a function that calcualte last_sunday date and return a datetime.

I have two Parameter in my report

start_date

end_date

and I put th following code in the report Parameer Properties

for Start date parameter

code.Last_Sunday

For End Date Parameter

code.Last_Sunday.adddays(6)

However when I ry to Preview I am getting this error

[rsReportParameterPropertyTypeMismatch] The property ‘DefaultValue’ of report parameter ‘Start_Date’ doesn't have the expected type.

[rsReportParameterPropertyTypeMismatch] The property ‘DefaultValue’ of report parameter ‘End_Date’ doesn't have the expected type.

Build complete -- 2 errors, 0 warnings

What I am doign Wrong both parameter are defined as datetime.

Thanks

Tanweer

Public Function Last_Sunday() As datetime

Dim Processed_date As DateTime
Dim start_date As DateTime
' Dim End_date As DateTime
Processed_date = DateTime.Today

'start_date = DateAdd(Processed_date - Weekday(Processed_date) + 1)
Select Case Processed_date.DayOfWeek.ToString
Case "Monday"
start_date = Processed_date.AddDays(-8)
Case "Tuesday"
start_date = Processed_date.AddDays(-9)
Case "Wednesday"
start_date = Processed_date.AddDays(-10)
Case "Thursday"
start_date = Processed_date.AddDays(-11)
Case "Friday"
start_date = Processed_date.AddDays(-12)
Case "Saturday"
start_date = Processed_date.AddDays(-13)
Case "Sunday"
start_date = Processed_date.AddDays(-14)
End Select
' End_date = format(start_date.AddDays(6),"mm/dd/yyyy")
console.writeline (start_date)
Return format(start_date,"MM/dd/yyyy")
End Function

You may want to try changing this line:

Return format(start_date,"MM/dd/yyyy")

to

CDate(Return format(start_date,"MM/dd/yyyy"))

The format function returns a string - not a DateTime...

Why are you formatting it here?

You mention a report...would it not be better to just pass the unformatted datetime to your report and handle the formatting in the report?

If you want create a re-usable function like this, it is better to return the raw data and handle formatting etc from where it is called. It is more generic and re-useable that way.

Steve