Forms auth. Redirect failed

Hello,
(finally) I got forms authentication to work. I have one remaining problem.
If I use a URL to call a report, the logon dialog is displayed. That's ok.
If I logon (which works prefect) I receive the following error message from
the redirect call:
The return URL specified for request redirection is invalid.
RS (SQL 2005) is installed on a Windows 2003 Server SP2.
This is the URL in the address line of the browser, if the logon dialog
appears:
http://dev01/ReportServer/logon.aspx?ReturnUrl=%2fReportServer%3f%2fPages%2fReportViewer.aspx%3f%252fSupport%252fTicketList%26rs%3aCommand%3dRender&%2fPages%2fReportViewer.aspx%3f%2fSupport%2fTicketList&rs%3aCommand=Render
SSL is not activated jet, becouse I'm not productive right now.
The problem is in the development envoirment and appears also on the
web-server. On both machines forms authentication works.
I can't direct the users to the ReportServer page to select reports by them
selfe. They need to call Reports using URL Access.
Any help would be appreciated.
I don't know what's wrong with URL, how to config RS to change the URL, or
how to write a different redirect command in the security sample.
Detailed (exact) explanation would be great.I solved this problem.
The couse was the last part of the URL "&rs%3aCommand=Render".
After removing this part the redirect worked.
"Ralf Stofer" wrote:
> Hello,
> (finally) I got forms authentication to work. I have one remaining problem.
> If I use a URL to call a report, the logon dialog is displayed. That's ok.
> If I logon (which works prefect) I receive the following error message from
> the redirect call:
> The return URL specified for request redirection is invalid.
> RS (SQL 2005) is installed on a Windows 2003 Server SP2.
> This is the URL in the address line of the browser, if the logon dialog
> appears:
> http://dev01/ReportServer/logon.aspx?ReturnUrl=%2fReportServer%3f%2fPages%2fReportViewer.aspx%3f%252fSupport%252fTicketList%26rs%3aCommand%3dRender&%2fPages%2fReportViewer.aspx%3f%2fSupport%2fTicketList&rs%3aCommand=Render
> SSL is not activated jet, becouse I'm not productive right now.
> The problem is in the development envoirment and appears also on the
> web-server. On both machines forms authentication works.
> I can't direct the users to the ReportServer page to select reports by them
> selfe. They need to call Reports using URL Access.
> Any help would be appreciated.
> I don't know what's wrong with URL, how to config RS to change the URL, or
> how to write a different redirect command in the security sample.
> Detailed (exact) explanation would be great.
>

Formatting text in crystal reports

hi,
is it possible to format the size, font, color of the text displayed in the report?just right click on that text and click format text :sql

Formatting a date displayed in a crystal report

Hello

I am using VS 2005 CR 10.

I am using typed datasets to populate a report.

From my database I have 1 field called incidentDate, this is a smallTimeDate datatype.

My reports always displayes the correct date, but is followed by the time which is always 12:00. For example 18/12/2006 12:00 AM.

I don't want to display this time. I have tried setting the DateWindowsDefaultType in the report properties for that field. But this did not work.

Does anyone have any other ideas,

Thanks very much,

SteveMake sure the type of the field in the report is Date.then whenu put it in the report right click choose formatObject u will find a Date Tab click and choose the format u want...

Formating

I have a field which returns a decimal (3.0000) i only want the whole
value(3) not the parts (.0000) to be displayed in the report.
when i right click on the textbox and select propeties then under the
'format' section i click on 'number' and select '1234' hit 'ok' and preview
the report it still displays 3.000.
how can i display just the 3(whole value as opposed to the part)?
Tia,
VinnySet the format to "F0"
"Vinny Vinn" wrote:
> I have a field which returns a decimal (3.0000) i only want the whole
> value(3) not the parts (.0000) to be displayed in the report.
> when i right click on the textbox and select propeties then under the
> 'format' section i click on 'number' and select '1234' hit 'ok' and preview
> the report it still displays 3.000.
> how can i display just the 3(whole value as opposed to the part)?
> Tia,
> Vinny|||Thank You,it worked!!
"David Siebert" wrote:
> Set the format to "F0"
> "Vinny Vinn" wrote:
> > I have a field which returns a decimal (3.0000) i only want the whole
> > value(3) not the parts (.0000) to be displayed in the report.
> > when i right click on the textbox and select propeties then under the
> > 'format' section i click on 'number' and select '1234' hit 'ok' and preview
> > the report it still displays 3.000.
> > how can i display just the 3(whole value as opposed to the part)?
> >
> > Tia,
> > Vinny

format phone numbers

how can i format a phone number to be displayed as follows in the report
(678) 444-4444
Thanks!"rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
news:E8FFA4A2-0CEF-4AF5-9CCB-ED08E8DCBE51@.microsoft.com...
> how can i format a phone number to be displayed as follows in the report
> (678) 444-4444
> Thanks!
Assuming all records are complete with 10 digits.
Try this:
=Format(cdec(PhoneNumber.Value),"(###) ###-####")|||= Format(Convert.ToDouble("90155585850000"), "(###) ###-#### 'Ext.' ####")
or use a regular expression, sth like
=System.Text.RegularExpressions.Regex.Replace(Fields!phone.Value,
"(\d{3})(\d{3})(\d{4})", "($1) $2-$3")
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
news:E8FFA4A2-0CEF-4AF5-9CCB-ED08E8DCBE51@.microsoft.com...
> how can i format a phone number to be displayed as follows in the report
> (678) 444-4444
> Thanks!|||okay great. I used the first example. Thanks!
Now i have discovered that there are some rows without phone numbers.
How do I do an IIF statement, that will return the formatted phone number
and not available when there is no phone number?
"Teo Lachev [MVP]" wrote:
> = Format(Convert.ToDouble("90155585850000"), "(###) ###-#### 'Ext.' ####")
> or use a regular expression, sth like
> =System.Text.RegularExpressions.Regex.Replace(Fields!phone.Value,
> "(\d{3})(\d{3})(\d{4})", "($1) $2-$3")
> --
> HTH,
> ---
> Teo Lachev, MVP, MCSD, MCT
> "Microsoft Reporting Services in Action"
> "Applied Microsoft Analysis Services 2005"
> Home page and blog: http://www.prologika.com/
> ---
> "rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
> news:E8FFA4A2-0CEF-4AF5-9CCB-ED08E8DCBE51@.microsoft.com...
> > how can i format a phone number to be displayed as follows in the report
> > (678) 444-4444
> >
> > Thanks!
>
>|||=Iif (Fields!phone.Value Is Nothing, Nothing,
Format(Convert.ToDouble(Fields!phone.Value), "(###) ###-#### 'Ext.' ####"))
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
news:03C42C02-DBFD-44AA-A297-FEA74C6ACB29@.microsoft.com...
> okay great. I used the first example. Thanks!
> Now i have discovered that there are some rows without phone numbers.
> How do I do an IIF statement, that will return the formatted phone number
> and not available when there is no phone number?
> "Teo Lachev [MVP]" wrote:
>> = Format(Convert.ToDouble("90155585850000"), "(###) ###-#### 'Ext.'
>> ####")
>> or use a regular expression, sth like
>> =System.Text.RegularExpressions.Regex.Replace(Fields!phone.Value,
>> "(\d{3})(\d{3})(\d{4})", "($1) $2-$3")
>> --
>> HTH,
>> ---
>> Teo Lachev, MVP, MCSD, MCT
>> "Microsoft Reporting Services in Action"
>> "Applied Microsoft Analysis Services 2005"
>> Home page and blog: http://www.prologika.com/
>> ---
>> "rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
>> news:E8FFA4A2-0CEF-4AF5-9CCB-ED08E8DCBE51@.microsoft.com...
>> > how can i format a phone number to be displayed as follows in the
>> > report
>> > (678) 444-4444
>> >
>> > Thanks!
>>|||THANKS!!!!!!!!!
"rybrown1818" wrote:
> how can i format a phone number to be displayed as follows in the report
> (678) 444-4444
> Thanks!|||OK -- this works to a point. I have real string data in my field that I want
to display if the field !isnumeric. So, here is the code I am using:
=iif(isnumeric(Fields!Q4Amt.Value),
Format(convert.ToDouble(Fields!Q4Amt.Value),
"$###,###,##0.00;($###,###,##0.00);$0.00"), "N/A")
Every time the N/A is supposed to be displayed, I get an error message.
Thanks for any and all help
"Teo Lachev [MVP]" wrote:
> =Iif (Fields!phone.Value Is Nothing, Nothing,
> Format(Convert.ToDouble(Fields!phone.Value), "(###) ###-#### 'Ext.' ####"))
> --
> HTH,
> ---
> Teo Lachev, MVP, MCSD, MCT
> "Microsoft Reporting Services in Action"
> "Applied Microsoft Analysis Services 2005"
> Home page and blog: http://www.prologika.com/
> ---
> "rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
> news:03C42C02-DBFD-44AA-A297-FEA74C6ACB29@.microsoft.com...
> > okay great. I used the first example. Thanks!
> > Now i have discovered that there are some rows without phone numbers.
> > How do I do an IIF statement, that will return the formatted phone number
> > and not available when there is no phone number?
> >
> > "Teo Lachev [MVP]" wrote:
> >
> >> = Format(Convert.ToDouble("90155585850000"), "(###) ###-#### 'Ext.'
> >> ####")
> >>
> >> or use a regular expression, sth like
> >>
> >> =System.Text.RegularExpressions.Regex.Replace(Fields!phone.Value,
> >> "(\d{3})(\d{3})(\d{4})", "($1) $2-$3")
> >>
> >> --
> >> HTH,
> >> ---
> >> Teo Lachev, MVP, MCSD, MCT
> >> "Microsoft Reporting Services in Action"
> >> "Applied Microsoft Analysis Services 2005"
> >> Home page and blog: http://www.prologika.com/
> >> ---
> >> "rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
> >> news:E8FFA4A2-0CEF-4AF5-9CCB-ED08E8DCBE51@.microsoft.com...
> >> > how can i format a phone number to be displayed as follows in the
> >> > report
> >> > (678) 444-4444
> >> >
> >> > Thanks!
> >>
> >>
> >>
>
>|||You need to change the format of the textbox to be an expression - if the
value is NOT numeric, then the format is BLANK. You cannot dispaly regular
text for objects that are, in this case, formatted for numeric display.
Hope this helps.
=-Chris
"msflinx" <msflinx@.discussions.microsoft.com> wrote in message
news:6A8AED73-5347-4A1B-9FEF-A4BAD968FCD6@.microsoft.com...
> OK -- this works to a point. I have real string data in my field that I
> want
> to display if the field !isnumeric. So, here is the code I am using:
> =iif(isnumeric(Fields!Q4Amt.Value),
> Format(convert.ToDouble(Fields!Q4Amt.Value),
> "$###,###,##0.00;($###,###,##0.00);$0.00"), "N/A")
> Every time the N/A is supposed to be displayed, I get an error message.
> Thanks for any and all help
> "Teo Lachev [MVP]" wrote:
>> =Iif (Fields!phone.Value Is Nothing, Nothing,
>> Format(Convert.ToDouble(Fields!phone.Value), "(###) ###-#### 'Ext.'
>> ####"))
>> --
>> HTH,
>> ---
>> Teo Lachev, MVP, MCSD, MCT
>> "Microsoft Reporting Services in Action"
>> "Applied Microsoft Analysis Services 2005"
>> Home page and blog: http://www.prologika.com/
>> ---
>> "rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
>> news:03C42C02-DBFD-44AA-A297-FEA74C6ACB29@.microsoft.com...
>> > okay great. I used the first example. Thanks!
>> > Now i have discovered that there are some rows without phone numbers.
>> > How do I do an IIF statement, that will return the formatted phone
>> > number
>> > and not available when there is no phone number?
>> >
>> > "Teo Lachev [MVP]" wrote:
>> >
>> >> = Format(Convert.ToDouble("90155585850000"), "(###) ###-#### 'Ext.'
>> >> ####")
>> >>
>> >> or use a regular expression, sth like
>> >>
>> >> =System.Text.RegularExpressions.Regex.Replace(Fields!phone.Value,
>> >> "(\d{3})(\d{3})(\d{4})", "($1) $2-$3")
>> >>
>> >> --
>> >> HTH,
>> >> ---
>> >> Teo Lachev, MVP, MCSD, MCT
>> >> "Microsoft Reporting Services in Action"
>> >> "Applied Microsoft Analysis Services 2005"
>> >> Home page and blog: http://www.prologika.com/
>> >> ---
>> >> "rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
>> >> news:E8FFA4A2-0CEF-4AF5-9CCB-ED08E8DCBE51@.microsoft.com...
>> >> > how can i format a phone number to be displayed as follows in the
>> >> > report
>> >> > (678) 444-4444
>> >> >
>> >> > Thanks!
>> >>
>> >>
>> >>
>>|||I'm not sure what I want right now. I want to be able to display the "N/A"
and $123,456.78 in the same textbox. So my options are:
1) If you cannot display a string and a number in the same textbox, can you
superimpose one textbox on top of another, with a switch as to the visible
textbox, based on the expression IsHidden =iif(isnumeric(Fields!Q4Amt.Values),True, False
2) Write a parsing function in the Report Parameters Code window and return
ONLY string values
3) Punt...
Any other ideas would be appreciated.
"Chris Conner" wrote:
> You need to change the format of the textbox to be an expression - if the
> value is NOT numeric, then the format is BLANK. You cannot dispaly regular
> text for objects that are, in this case, formatted for numeric display.
> Hope this helps.
> =-Chris
>
> "msflinx" <msflinx@.discussions.microsoft.com> wrote in message
> news:6A8AED73-5347-4A1B-9FEF-A4BAD968FCD6@.microsoft.com...
> > OK -- this works to a point. I have real string data in my field that I
> > want
> > to display if the field !isnumeric. So, here is the code I am using:
> >
> > =iif(isnumeric(Fields!Q4Amt.Value),
> > Format(convert.ToDouble(Fields!Q4Amt.Value),
> > "$###,###,##0.00;($###,###,##0.00);$0.00"), "N/A")
> >
> > Every time the N/A is supposed to be displayed, I get an error message.
> >
> > Thanks for any and all help
> >
> > "Teo Lachev [MVP]" wrote:
> >
> >> =Iif (Fields!phone.Value Is Nothing, Nothing,
> >> Format(Convert.ToDouble(Fields!phone.Value), "(###) ###-#### 'Ext.'
> >> ####"))
> >>
> >> --
> >> HTH,
> >> ---
> >> Teo Lachev, MVP, MCSD, MCT
> >> "Microsoft Reporting Services in Action"
> >> "Applied Microsoft Analysis Services 2005"
> >> Home page and blog: http://www.prologika.com/
> >> ---
> >> "rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
> >> news:03C42C02-DBFD-44AA-A297-FEA74C6ACB29@.microsoft.com...
> >> > okay great. I used the first example. Thanks!
> >> > Now i have discovered that there are some rows without phone numbers.
> >> > How do I do an IIF statement, that will return the formatted phone
> >> > number
> >> > and not available when there is no phone number?
> >> >
> >> > "Teo Lachev [MVP]" wrote:
> >> >
> >> >> = Format(Convert.ToDouble("90155585850000"), "(###) ###-#### 'Ext.'
> >> >> ####")
> >> >>
> >> >> or use a regular expression, sth like
> >> >>
> >> >> =System.Text.RegularExpressions.Regex.Replace(Fields!phone.Value,
> >> >> "(\d{3})(\d{3})(\d{4})", "($1) $2-$3")
> >> >>
> >> >> --
> >> >> HTH,
> >> >> ---
> >> >> Teo Lachev, MVP, MCSD, MCT
> >> >> "Microsoft Reporting Services in Action"
> >> >> "Applied Microsoft Analysis Services 2005"
> >> >> Home page and blog: http://www.prologika.com/
> >> >> ---
> >> >> "rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
> >> >> news:E8FFA4A2-0CEF-4AF5-9CCB-ED08E8DCBE51@.microsoft.com...
> >> >> > how can i format a phone number to be displayed as follows in the
> >> >> > report
> >> >> > (678) 444-4444
> >> >> >
> >> >> > Thanks!
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Thanks Chris. I posted this twice, as this thread did not move up the forum,
when I replied to it. I didn't think anyone was watching it.
The answer I received was option #2
This is the code I place in the Report Property's Code Tab:
Public Function NumericStringToCurrencyString(ByVal strPhrase as string) as
string
if isnumeric(strPhrase) then
NumericStringToCurrencyString =format(cdec(strPhrase),"$###,###,###.##;($###,###,###.##);$0.00")
else
NumericStringToCurrencyString = strPhrase
end if
end function
Then in the textbox Expression field, I entered:
=Code.NumericStringToCurrencyString( Fields!Q4Amt.Value)
This gave me the desired results.
Thank you for your assistance
Thank you ever so much.
"msflinx" wrote:
> I'm not sure what I want right now. I want to be able to display the "N/A"
> and $123,456.78 in the same textbox. So my options are:
> 1) If you cannot display a string and a number in the same textbox, can you
> superimpose one textbox on top of another, with a switch as to the visible
> textbox, based on the expression IsHidden => iif(isnumeric(Fields!Q4Amt.Values),True, False
> 2) Write a parsing function in the Report Parameters Code window and return
> ONLY string values
> 3) Punt...
> Any other ideas would be appreciated.
> "Chris Conner" wrote:
> > You need to change the format of the textbox to be an expression - if the
> > value is NOT numeric, then the format is BLANK. You cannot dispaly regular
> > text for objects that are, in this case, formatted for numeric display.
> >
> > Hope this helps.
> >
> > =-Chris
> >
> >
> >
> > "msflinx" <msflinx@.discussions.microsoft.com> wrote in message
> > news:6A8AED73-5347-4A1B-9FEF-A4BAD968FCD6@.microsoft.com...
> > > OK -- this works to a point. I have real string data in my field that I
> > > want
> > > to display if the field !isnumeric. So, here is the code I am using:
> > >
> > > =iif(isnumeric(Fields!Q4Amt.Value),
> > > Format(convert.ToDouble(Fields!Q4Amt.Value),
> > > "$###,###,##0.00;($###,###,##0.00);$0.00"), "N/A")
> > >
> > > Every time the N/A is supposed to be displayed, I get an error message.
> > >
> > > Thanks for any and all help
> > >
> > > "Teo Lachev [MVP]" wrote:
> > >
> > >> =Iif (Fields!phone.Value Is Nothing, Nothing,
> > >> Format(Convert.ToDouble(Fields!phone.Value), "(###) ###-#### 'Ext.'
> > >> ####"))
> > >>
> > >> --
> > >> HTH,
> > >> ---
> > >> Teo Lachev, MVP, MCSD, MCT
> > >> "Microsoft Reporting Services in Action"
> > >> "Applied Microsoft Analysis Services 2005"
> > >> Home page and blog: http://www.prologika.com/
> > >> ---
> > >> "rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
> > >> news:03C42C02-DBFD-44AA-A297-FEA74C6ACB29@.microsoft.com...
> > >> > okay great. I used the first example. Thanks!
> > >> > Now i have discovered that there are some rows without phone numbers.
> > >> > How do I do an IIF statement, that will return the formatted phone
> > >> > number
> > >> > and not available when there is no phone number?
> > >> >
> > >> > "Teo Lachev [MVP]" wrote:
> > >> >
> > >> >> = Format(Convert.ToDouble("90155585850000"), "(###) ###-#### 'Ext.'
> > >> >> ####")
> > >> >>
> > >> >> or use a regular expression, sth like
> > >> >>
> > >> >> =System.Text.RegularExpressions.Regex.Replace(Fields!phone.Value,
> > >> >> "(\d{3})(\d{3})(\d{4})", "($1) $2-$3")
> > >> >>
> > >> >> --
> > >> >> HTH,
> > >> >> ---
> > >> >> Teo Lachev, MVP, MCSD, MCT
> > >> >> "Microsoft Reporting Services in Action"
> > >> >> "Applied Microsoft Analysis Services 2005"
> > >> >> Home page and blog: http://www.prologika.com/
> > >> >> ---
> > >> >> "rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
> > >> >> news:E8FFA4A2-0CEF-4AF5-9CCB-ED08E8DCBE51@.microsoft.com...
> > >> >> > how can i format a phone number to be displayed as follows in the
> > >> >> > report
> > >> >> > (678) 444-4444
> > >> >> >
> > >> >> > Thanks!
> > >> >>
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >

Format of DateTime types in Select vs Open Table

From within the SQL Server Management Studio, if I select a table and displa
y
it (right click Open Table), columns of type DateTime are displayed with the
mm/dd/yyyy hh:mm:ss format. However, if I SELECT * FROM the same table from
a
query in the Query Analyzer (or New Query in 2005), the same column is
displayed with a yyyy-mm-dd hh:mm:ss format.
Why is there a difference in display format between opening the table in the
Management Studio and SELECT'ing in a query?
Short of using a CONVERT in the SELECT, is there a way of controlling the
display format of DateTime types in both scenarios?
Michael
--
Michael Hocksteinmichael (howlinghound@.nospam.nospam) writes:
> From within the SQL Server Management Studio, if I select a table and
> display it (right click Open Table), columns of type DateTime are
> displayed with the mm/dd/yyyy hh:mm:ss format. However, if I SELECT *
> FROM the same table from a query in the Query Analyzer (or New Query in
> 2005), the same column is displayed with a yyyy-mm-dd hh:mm:ss format.
> Why is there a difference in display format between opening the table in
> the Management Studio and SELECT'ing in a query?
Open Table respects the regional settings, while by default Query Analyzer
does not. You can change this for QA by going into Tools->Options->
Connections and check "Use regional settings...". There does not seem to be
any similar option for Management Studio. but query results always
apparently uses ISO format. (I can't really tell from here, because my
regional settings are the ISO format.)
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|||Thank you!
Michael Hockstein
"Erland Sommarskog" wrote:

> michael (howlinghound@.nospam.nospam) writes:
> Open Table respects the regional settings, while by default Query Analyzer
> does not. You can change this for QA by going into Tools->Options->
> Connections and check "Use regional settings...". There does not seem to b
e
> any similar option for Management Studio. but query results always
> apparently uses ISO format. (I can't really tell from here, because my
> regional settings are the ISO format.)
>
> --
> 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
>

Format for Parameters

Simple question - I have a date as a parameter. When I enter 12/1/2004 for
example and run the report, after the data is displayed, the parameter
displays "12/1/2004 12:00:00AM". Is there any way to pass the typical date
format like "MM/dd/yyyy" to the parameter so that it formats the date only?
thanks
billHi Bill,
Based on my knowledge, we are unable to format the
parameters. But I would like to perform further research
to confirm if this is possible. I will update you once I
have more information.
BTW: If you display the date in a text box in the Report
body, you can set the format via the text box Properties
dialog box.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>Reply-To: <sutphinwb@.nospam.nospam>
>From: <sutphinwb@.nospam.nospam>
>Subject: Format for Parameters
>Date: Wed, 2 Feb 2005 00:37:39 -0500
>Lines: 10
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>Message-ID: <es9bQlOCFHA.1424@.TK2MSFTNGP09.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: ip24-250-32-203.ri.ri.cox.net
24.250.32.203
>Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.p
hx.gbl!TK2MSFTNGP09.phx.gbl
>Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.reportingsvcs:41614
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>Simple question - I have a date as a parameter. When I
enter 12/1/2004 for
>example and run the report, after the data is
displayed, the parameter
>displays "12/1/2004 12:00:00AM". Is there any way to
pass the typical date
>format like "MM/dd/yyyy" to the parameter so that it
formats the date only?
>thanks
>bill
>
>|||William is correct to the best of my knowledge as well... If you need pretty
formatting either use a string, check that it is a valid date, and format it
as you wish... You could also use an HTML page as a front end to the report,
formating date strings etc using html techniques, then passing the params to
the URL... ( I prefer the simpler string method if possible.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<sutphinwb@.nospam.nospam> wrote in message
news:es9bQlOCFHA.1424@.TK2MSFTNGP09.phx.gbl...
> Simple question - I have a date as a parameter. When I enter 12/1/2004
for
> example and run the report, after the data is displayed, the parameter
> displays "12/1/2004 12:00:00AM". Is there any way to pass the typical
date
> format like "MM/dd/yyyy" to the parameter so that it formats the date
only?
> thanks
> bill
>|||You can use a stored procedure to retrieve the data from the database. And
then you can format the parameter inside the stored procedure.
"Wayne Snyder" wrote:
> William is correct to the best of my knowledge as well... If you need pretty
> formatting either use a string, check that it is a valid date, and format it
> as you wish... You could also use an HTML page as a front end to the report,
> formating date strings etc using html techniques, then passing the params to
> the URL... ( I prefer the simpler string method if possible.)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> <sutphinwb@.nospam.nospam> wrote in message
> news:es9bQlOCFHA.1424@.TK2MSFTNGP09.phx.gbl...
> > Simple question - I have a date as a parameter. When I enter 12/1/2004
> for
> > example and run the report, after the data is displayed, the parameter
> > displays "12/1/2004 12:00:00AM". Is there any way to pass the typical
> date
> > format like "MM/dd/yyyy" to the parameter so that it formats the date
> only?
> >
> > thanks
> >
> > bill
> >
> >
>
>

Format DateTime attribute in AS 2005

Hi,

I have an attribute a Time dimension which is of the datetime datatype. In the browser and in other tools, the displayed members are formatted like this:

dd-mm-yyyy hh:mm:ss

I want to change this format. How is this done? The format property of the attribute in the dimension editor seems to have no effect... I tried "mm/dd/yyyy" and without quotes.

Thnx... Jeroen.

Hi Jeroen,

If you customize the date format to what you want in the regional settings in the control panel, and refresh the browser, you will get the format you wanted. I've tried this out and it works.

Yan

|||

Hello Yan,

Thnx for your reply. I will try that. However, we want to implement a solution that's independent of the local user settings. For instance, we don't want the time displayed in this case. I figured this would be peanuts with AS 2005. Ofcourse, we can always do it in an extra column in the table or in the data source view.

Regards,

Jeroen

|||these are the value to use instead of "dd/mm/yyyy"

Constant

Description

DateFormat.GeneralDate

Display a date and/or time. Display a date part as a short date. If there is a time part, display it as a long time. If present, both parts display.

DateFormat.LongDate

Display a date using the long date format specified in your computer's regional settings.

DateFormat.ShortDate

Display a date using the short date format specified in your computer's regional settings.

DateFormat.LongTime

Display a time using the time format specified in your computer's regional settings.

DateFormat.ShortTime

Display a time using the 24-hour format (hh:mm).

|||

I am trying to solve the same problem.

So where do you need to set this property? I tried setting it in the NameColumn.Formatting property and in the Value.Nameformating property, but to no avail!

|||

Please could you telll me where to set this constant value?

I'm have tried in the format property but it does not work.

|||

Hello. I would recommend you to add a named calculation to your time dimension and use the TSQL-function CONVERT to transform your date to an appropriate format.

CONVERT(Char(10), GETDATE(), 112) will change a dateformat to '2007-05-25' (ISO-style)

You will find more information about this function in Books OnLine.

Try

select Getdate(), Convert(Char(10),GetDate(), 112)

in a query in management studio on the database engine.

HTH

Thomas Ivarsson

|||

Thank you. If you say that is the only solution, i will do so. But it will be very hard for me to add a named calculation to all date field in my data source view. I have about 50 date field and i was just wondering why it is not possible to set date format in the "format proterty field". The solution Mentionned by Yan is very interring for me, but it does not work. In fact, how to use VB code in AS projet?

|||

I have used the following code in the Calculations tab of Cube Designer in BIDS to acheive the formating for calculated measure. You can try doing the same for dimensions - I guess it would work. Pl change the format string to appropriate one.

FORMAT_STRING([Measures].[MyDimName]) = "#,#;(#,#)";

Format DateTime attribute in AS 2005

Hi,

I have an attribute a Time dimension which is of the datetime datatype. In the browser and in other tools, the displayed members are formatted like this:

dd-mm-yyyy hh:mm:ss

I want to change this format. How is this done? The format property of the attribute in the dimension editor seems to have no effect... I tried "mm/dd/yyyy" and without quotes.

Thnx... Jeroen.

Hi Jeroen,

If you customize the date format to what you want in the regional settings in the control panel, and refresh the browser, you will get the format you wanted. I've tried this out and it works.

Yan

|||

Hello Yan,

Thnx for your reply. I will try that. However, we want to implement a solution that's independent of the local user settings. For instance, we don't want the time displayed in this case. I figured this would be peanuts with AS 2005. Ofcourse, we can always do it in an extra column in the table or in the data source view.

Regards,

Jeroen

|||these are the value to use instead of "dd/mm/yyyy"

Constant

Description

DateFormat.GeneralDate

Display a date and/or time. Display a date part as a short date. If there is a time part, display it as a long time. If present, both parts display.

DateFormat.LongDate

Display a date using the long date format specified in your computer's regional settings.

DateFormat.ShortDate

Display a date using the short date format specified in your computer's regional settings.

DateFormat.LongTime

Display a time using the time format specified in your computer's regional settings.

DateFormat.ShortTime

Display a time using the 24-hour format (hh:mm).

|||

I am trying to solve the same problem.

So where do you need to set this property? I tried setting it in the NameColumn.Formatting property and in the Value.Nameformating property, but to no avail!

|||

Please could you telll me where to set this constant value?

I'm have tried in the format property but it does not work.

|||

Hello. I would recommend you to add a named calculation to your time dimension and use the TSQL-function CONVERT to transform your date to an appropriate format.

CONVERT(Char(10), GETDATE(), 112) will change a dateformat to '2007-05-25' (ISO-style)

You will find more information about this function in Books OnLine.

Try

select Getdate(), Convert(Char(10),GetDate(), 112)

in a query in management studio on the database engine.

HTH

Thomas Ivarsson

|||

Thank you. If you say that is the only solution, i will do so. But it will be very hard for me to add a named calculation to all date field in my data source view. I have about 50 date field and i was just wondering why it is not possible to set date format in the "format proterty field". The solution Mentionned by Yan is very interring for me, but it does not work. In fact, how to use VB code in AS projet?

|||

I have used the following code in the Calculations tab of Cube Designer in BIDS to acheive the formating for calculated measure. You can try doing the same for dimensions - I guess it would work. Pl change the format string to appropriate one.

FORMAT_STRING([Measures].[MyDimName]) = "#,#;(#,#)";

|||

Hi Thomas,

And thanks for this helpful answer : Date labels are well formatted with this conversion.

However, an OLAP client such as Excel 2007 will not recognize the attribute datatype and thus will not display the "date filters" for that dimension.

Moreover, an error occurs when setting the DataType of the NameColumn to something else than WChar (Date, for instance...)

SSAS allows to use a ValueColumn for the attribute, which I set to the CONVERTed date, in both Date and WChar formats, but the client still does not recognize it as a date field...

Any idea ?

Thank you

Greg

|||

Hi all,

Updating the thread with great news :

To have the Date attribute of a Time hierarchy recognized as a Date field in Excel pivot tables and display the Date Filtern, you have to set the ValueColumn of the key attribute (not the Date attribute) to the Date attribute column. Typically, the key attribute is an int32, this is why you don't think about making it point to the date column.

HTH

Greg

|||I've been trying to get this to function right for months to no avail. I've tried setting the key's value column to the date attribute column, It didn't work (excel still thinks my dates are text). My key was already a date type, perhaps that's the issue?|||

Hello Greg. You can set the name property to the named calculation that I have suggested but keep the key column to the date time data type. This is how I hve done it in SSAS2005.

HTH

Thomas Ivarsson

|||The problem I've had with doing that is that Excel 2007 still thinks that the value is text, it doesn't realize it's a date value...hence all the date filters won't work (and graphing by date is not done correctly).|||

Hello Jamie! I have tried this on the time dimension in the Adventure Works cube projects and it works correctly. I have used a proper dateTime column as the key and the output of the Convert function as the name column.

Do not forget to set the order by property to key column.

Regards

Thomas Ivarsson

Format DateTime attribute in AS 2005

Hi,

I have an attribute a Time dimension which is of the datetime datatype. In the browser and in other tools, the displayed members are formatted like this:

dd-mm-yyyy hh:mm:ss

I want to change this format. How is this done? The format property of the attribute in the dimension editor seems to have no effect... I tried "mm/dd/yyyy" and without quotes.

Thnx... Jeroen.

Hi Jeroen,

If you customize the date format to what you want in the regional settings in the control panel, and refresh the browser, you will get the format you wanted. I've tried this out and it works.

Yan

|||

Hello Yan,

Thnx for your reply. I will try that. However, we want to implement a solution that's independent of the local user settings. For instance, we don't want the time displayed in this case. I figured this would be peanuts with AS 2005. Ofcourse, we can always do it in an extra column in the table or in the data source view.

Regards,

Jeroen

|||these are the value to use instead of "dd/mm/yyyy"

Constant

Description

DateFormat.GeneralDate

Display a date and/or time. Display a date part as a short date. If there is a time part, display it as a long time. If present, both parts display.

DateFormat.LongDate

Display a date using the long date format specified in your computer's regional settings.

DateFormat.ShortDate

Display a date using the short date format specified in your computer's regional settings.

DateFormat.LongTime

Display a time using the time format specified in your computer's regional settings.

DateFormat.ShortTime

Display a time using the 24-hour format (hh:mm).

|||

I am trying to solve the same problem.

So where do you need to set this property? I tried setting it in the NameColumn.Formatting property and in the Value.Nameformating property, but to no avail!

|||

Please could you telll me where to set this constant value?

I'm have tried in the format property but it does not work.

|||

Hello. I would recommend you to add a named calculation to your time dimension and use the TSQL-function CONVERT to transform your date to an appropriate format.

CONVERT(Char(10), GETDATE(), 112) will change a dateformat to '2007-05-25' (ISO-style)

You will find more information about this function in Books OnLine.

Try

select Getdate(), Convert(Char(10),GetDate(), 112)

in a query in management studio on the database engine.

HTH

Thomas Ivarsson

|||

Thank you. If you say that is the only solution, i will do so. But it will be very hard for me to add a named calculation to all date field in my data source view. I have about 50 date field and i was just wondering why it is not possible to set date format in the "format proterty field". The solution Mentionned by Yan is very interring for me, but it does not work. In fact, how to use VB code in AS projet?

|||

I have used the following code in the Calculations tab of Cube Designer in BIDS to acheive the formating for calculated measure. You can try doing the same for dimensions - I guess it would work. Pl change the format string to appropriate one.

FORMAT_STRING([Measures].[MyDimName]) = "#,#;(#,#)";

Format DateTime attribute in AS 2005

Hi,

I have an attribute a Time dimension which is of the datetime datatype. In the browser and in other tools, the displayed members are formatted like this:

dd-mm-yyyy hh:mm:ss

I want to change this format. How is this done? The format property of the attribute in the dimension editor seems to have no effect... I tried "mm/dd/yyyy" and without quotes.

Thnx... Jeroen.

Hi Jeroen,

If you customize the date format to what you want in the regional settings in the control panel, and refresh the browser, you will get the format you wanted. I've tried this out and it works.

Yan

|||

Hello Yan,

Thnx for your reply. I will try that. However, we want to implement a solution that's independent of the local user settings. For instance, we don't want the time displayed in this case. I figured this would be peanuts with AS 2005. Ofcourse, we can always do it in an extra column in the table or in the data source view.

Regards,

Jeroen

|||these are the value to use instead of "dd/mm/yyyy"

Constant

Description

DateFormat.GeneralDate

Display a date and/or time. Display a date part as a short date. If there is a time part, display it as a long time. If present, both parts display.

DateFormat.LongDate

Display a date using the long date format specified in your computer's regional settings.

DateFormat.ShortDate

Display a date using the short date format specified in your computer's regional settings.

DateFormat.LongTime

Display a time using the time format specified in your computer's regional settings.

DateFormat.ShortTime

Display a time using the 24-hour format (hh:mm).

|||

I am trying to solve the same problem.

So where do you need to set this property? I tried setting it in the NameColumn.Formatting property and in the Value.Nameformating property, but to no avail!

|||

Please could you telll me where to set this constant value?

I'm have tried in the format property but it does not work.

|||

Hello. I would recommend you to add a named calculation to your time dimension and use the TSQL-function CONVERT to transform your date to an appropriate format.

CONVERT(Char(10), GETDATE(), 112) will change a dateformat to '2007-05-25' (ISO-style)

You will find more information about this function in Books OnLine.

Try

select Getdate(), Convert(Char(10),GetDate(), 112)

in a query in management studio on the database engine.

HTH

Thomas Ivarsson

|||

Thank you. If you say that is the only solution, i will do so. But it will be very hard for me to add a named calculation to all date field in my data source view. I have about 50 date field and i was just wondering why it is not possible to set date format in the "format proterty field". The solution Mentionned by Yan is very interring for me, but it does not work. In fact, how to use VB code in AS projet?

|||

I have used the following code in the Calculations tab of Cube Designer in BIDS to acheive the formating for calculated measure. You can try doing the same for dimensions - I guess it would work. Pl change the format string to appropriate one.

FORMAT_STRING([Measures].[MyDimName]) = "#,#;(#,#)";