coosing more than 1 value in QUERY PARAMETERS Dialogue Box

You know how there is a Query Parameter Dialogue Box in the Data Tab in Reporting services. In other words, if you have a query with a parameter and want to run your query, this dialogue box appears and wants you to enter the value for the parameter. How can I choose more than 1 parameter in Query parameter dialogue box. I mean, I have a SalesPerson parameter in my query, and whenever I enter John as the value for SalesPerson Parameter I am OK. Whenever I enter Bob as the value I am OK too. How can I see the results for both John and Bob?

John, Bob seems not to work

IN (John, Bob) Seems not to work either.

What is the correct syntax.

Pleaseee.(I'm going crazy)

Thank you

This problem would better be solved by viewing these scenarios in the preview tab.

Cookie problem in reporting services

Hi, I'm using URL access to view my reports from within my website. My report
is being viewed from within an IFrame on the site. IE 6 with default security
blocks cookies coming from the IFrame. Apparently - setting
"UseSessionCookies" to false in the site settings is supposed to cause RS to
manage session via the querystring and instead. I have done this and RS is
still sending cookies? (I am running SP1)
Is this a bug is RS or am i missing something?Hi Jarrod,
From your descriptions, I understood cookies are continuing sending even
you have set UseSessionCookies to false. Have I understood you? Correct me
if I was wrong.
Based on my scope, UseSessionCookies is a system property and you could set
that to false by using the SOAP API SetSystemProperties method.
I am not sure why it is continuing sending cookies as you said. I have
surveied known issues internally and do not find anything related with
UseSessionCookies so that is it possible for you to generate a samll sampel
for me to reproduce it?
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Jarrod,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Converting Word Document into RDL Format

Hi,
how can I convert a word document into the RDL Format for Reporting Services.
Is there a tool or an workaround ?
Thanks
StefanOn Jul 19, 11:46 pm, Stefan <Ste...@.discussions.microsoft.com> wrote:
> Hi,
> how can I convert a word document into the RDL Format for Reporting Services.
> Is there a tool or an workaround ?
> Thanks
> Stefan
As far as I know, there is not really anything available. You would
need to investigate into using a custom .NET application to read in
the word document (most likely using System.IO and Streamreader) and
then output the file in the RDL format (using Streamwriter, XMLDoc,
etc). Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Is the issue that you want a word document that comes up similar to click on
a report. I do this now. In the designer just do a right mouse click on
reports, add and existing item, then select your word document. Deploy like
a normal report. The only caveat is when you redeploy first go into report
manager and delete it and then deploy. I have found a redeploy does not
stick.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stefan" <Stefan@.discussions.microsoft.com> wrote in message
news:E80FD6D3-A511-4C37-B018-5C872946555D@.microsoft.com...
> Hi,
> how can I convert a word document into the RDL Format for Reporting
> Services.
> Is there a tool or an workaround ?
> Thanks
> Stefan

Converting US Date To UK Date in Reporting Services

I am using thise function in the Code Properties of my Report to convert US Date formats to UK Date format :

Public Function ConvertUSDateToUKDate(ByVal strUSDate As Object) As String
Dim strDay As String = strUSDate.Substring(3, 2)
Dim strMonth As String = strUSDate.Substring(0, 2)
Dim strYear As String = strUSDate.Substring(6, 4)
Dim strUKDate As String = strDay + "/" + strMonth + "/" + strYear + strUSDate.Substring(10, 12)
Return strUKDate
End Function

I keep getting an error with it, I'm not sure whether it is a VB.NET code error, or a Reporting Services error. Can anybody help?

Cheers,

Mike

Sorted this out :

Public Function ConvertUSDateToUKDate(ByVal strUSDate As String) As String
Dim strUKDate As String = ""
If Not (strUSDate = "") Then
Dim arrRateArray As String()
Dim arrSeparator As Char() = {"/"C}
arrRateArray = strUSDate.Split(arrSeparator)
Dim strMonth As String = arrRateArray(0)
Dim strDay As String = arrRateArray(1)
Dim strYear As String = arrRateArray(2).Substring(0, 4)
Dim strTime As String = arrRateArray(2).Substring(5, arrRateArray(2).Length - 5)
strUKDate = strDay + "/" + strMonth + "/" + strYear + " " + strTime
Else
strUKDate = ""
End If

converting RS2000 reports to 2005

I am trying to convert reports from reporting services 2000 to 2005. I did not open the solution that contained the reports because I wanted to start fresh with a new structure. I added the projects and brought the existing reports in. My question is, I am findingt the only way to convert the reports is to open them in design view and specify that I want to convert them at the prompt. After a little digging, it appeared the only different was the report declaration at the top:

2005:
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefini..."xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">

2003:
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini..."xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">

Since it is about ten times faster to open the report in xml (code) view, I thought maybe I could just do a search and replace on the two lines above, or at least copy over them, and then save all. I have over 100 reports and it would be pretty time consuming to do a conversion on each. Anyway, my question is is this the only thing I need to do to convert a report or will I screw up my reports doing it this way. I did one and it seemed to work.

Any advice is appreciated.

The RS 2005 Report Server can run RS 2000 RDLs. You can just publish them to the Report Server - they will be auto-upgraded during publishing. Note: when you retrieve it from the report server, you’ll get the original RS 2000 RDL you published to the server.

From that point of view it is not mandatory to upgrade old RDLs to the new schema definition.

BTW: there are a few more changes in the new schema than just the namespace change.

-- Robert

Converting reports from 2000 to 2005

I am trying to convert reports from reporting services 2000 to 2005. I
did not open the solution that contained the reports because I wanted
to start fresh with a new structure. I added the projects and brought
the existing reports in. My question is, I am findingt the only way to
convert the reports is to open them in design view and specify that I
want to convert them at the prompt. After a little digging, it
appeared the only different was the report declaration at the top:
2005:
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
2003:
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
Since it is about ten times faster to open the report in xml (code)
view, I thought maybe I could just do a search and replace on the two
lines above, or at least copy over them, and then save all. I have
over 100 reports and it would be pretty time consuming to do a
conversion on each. Anyway, my question is is this the only thing I
need to do to convert a report or will I screw up my reports doing it
this way. I did one and it seemed to work.
Any advice is appreciated.I don't know. I think you are playing with fire. What if one of your reports
has something different that requires more. I would think you could do a 100
reports in about an hour. A boring hour but it is the safest way.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stephen" <switter@.enpathmed.com> wrote in message
news:1142439465.876284.160770@.p10g2000cwp.googlegroups.com...
>I am trying to convert reports from reporting services 2000 to 2005. I
> did not open the solution that contained the reports because I wanted
> to start fresh with a new structure. I added the projects and brought
> the existing reports in. My question is, I am findingt the only way to
> convert the reports is to open them in design view and specify that I
> want to convert them at the prompt. After a little digging, it
> appeared the only different was the report declaration at the top:
> 2005:
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> 2003:
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> Since it is about ten times faster to open the report in xml (code)
> view, I thought maybe I could just do a search and replace on the two
> lines above, or at least copy over them, and then save all. I have
> over 100 reports and it would be pretty time consuming to do a
> conversion on each. Anyway, my question is is this the only thing I
> need to do to convert a report or will I screw up my reports doing it
> this way. I did one and it seemed to work.
> Any advice is appreciated.
>

Converting report input dates to UTC

I have created a report in reporting services and it takes a number of
DateTime values as input parameters. These values will be passed on to
queries in my database; the problem is that my database stores DateTime
values as UTC.
Is there a way I can convert them to UTC before the values are sent to the
query?Hello,
You could try this:
DATEADD(Hour, DATEDIFF(Hour, GETUTCDATE(), GETDATE()), @.LocalDate)
http://geekswithblogs.net/ewright/archive/2004/09/14/11180.aspx
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
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.
>Thread-Topic: Converting report input dates to UTC
>thread-index: AcYtdvuTa+Zu+2dZQC6M3qdl1xmC3Q==>X-WBNR-Posting-Host: 195.139.24.170
>From: "=?Utf-8?B?Q2hyaXN0b3BoZXIgS2ltYmVsbA==?="
<c_kimbell@.newsgroup.nospam>
>Subject: Converting report input dates to UTC
>Date: Thu, 9 Feb 2006 04:47:27 -0800
>Lines: 7
>Message-ID: <920BBD92-A5C6-4A43-B965-8D1AE0151687@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:68388
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>I have created a report in reporting services and it takes a number of
>DateTime values as input parameters. These values will be passed on to
>queries in my database; the problem is that my database stores DateTime
>values as UTC.
>Is there a way I can convert them to UTC before the values are sent to the
>query?
>

Converting oracle calculation to sql server 2005 calculation

Hi I am having to convert some oracle reports to Reporting Services. Where I am having difficulty is with the

calculations.

Oracle

TO_DATE(TO_CHAR(Visit Date+Visit Time/24/60/60,'DD-Mon-YYYY HH24:MISS'),'DD-Mon-YYYY HH24:MISS')

this is a sfar as I have got with the sql version

SQLSERVER2005

= DateAdd("s",Fields!VISIT_DATE.Value,Fields!VISIT_TIME.Value\24\60\60 )

visit_date is date datatype visit_time is number datatype. have removed : from MI(here)SS as was showing as smiley.

using:

VS 2005 BI Tools

SQLServer 2005

You need SQL Server convert code 13 or 113, try the link below for all the conversion codes. Hope this helps.

http://sqljunkies.com/HowTo/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

|||

Thanks for the reply, I tried....

= Convert(varchar(10),DateAdd("s",Fields!VISIT_DATE.Value,Fields!VISIT_TIME.Value\24\60\60,103)

'Convert' is underlined red tool tip says 'unrecognised identifier'

|||

The link I gave you is for you to do the caculations in the database not Reporting services table control Expression box.

|||Thanks, much easier doing calculated fields in db.

Converting numeric data type to text data type

Hi,

I would like to convert a dollar amount ($1,500) to represent Fifteen
hundred dollars and 00/100 cents only for SQL reporting purposes. Is
this possible and can I incorporate the statement into an existing
left outer join query.

Thanks in advance,

Gavin[posted and mailed, please reply in news]

Gavin (gsegal@.mps.com) writes:
> I would like to convert a dollar amount ($1,500) to represent Fifteen
> hundred dollars and 00/100 cents only for SQL reporting purposes. Is
> this possible and can I incorporate the statement into an existing
> left outer join query.

If it is for reporting issues, it may be better to do this on client
level, but you could use a table to hold the various strings. I would
suggest that it will be simpler to implement, if you permit 1500 to be
rendered as "One thousand five hundred".

The table would look like this:

CREATE TABLE numberstrs (nr tinyint NOT NULL
CONSTRAINT ck_nr CHECK (nr BETWEEN 0 AND 99),
str varchar(23) NOT NULL,
CONSTRAINT pk_nr PRIMARY KEY (nr))
go
INSERT numberstrs (nr, str) VALUES (0, '')
INSERT numberstrs (nr, str) VALUES (1, 'one')
...
INSERT numberstrs (nr, str) VALUES (99, 'ninety-nine')

You would then use it as

SELECT CASE WHEN mil.str IS NOT NULL
THEN mil.str + ' millions and '
ELSE ''
END +
CASE WHEN hth.str IS NOT NULL
THEN mil.str + ' hundred '
ELSE ''
END +
CASE WHEN th.str IS NOT NULL
THEN th.str + ' thousand and '
ELSE ''
END +
CASE WHEN hun.str IS NOT NULL
THEN hun.str + ' hundred '
ELSE ''
END +
one.str + ' dollars ' +
ltrim(str((a.amt * 100) % 100)) + '/100 cents'
FROM tbl a
JOIN numberstrs one ON a.amt % 100 = nr
LEFT JOIN numberstrs hun ON (convert(int, a.amt) / 100) % 10 = nr
LEFT JOIN numberstrs th ON (convert(int, a.amt) / 1000) % 100 = nr
LEFT JOIN numberstrs hth ON (convert(int, a.amt) / 100000) % 10 = nr
LEFT JOIN numberstrs mil ON (convert(int, a.amt) / 1000000) % 100 = nr

This is something I made up, and I have not tested it. I don't think
the result will be that excellent for all numbers. For instance 101
would not come out pretty if you want it as "One hundred and one".
For even values like "One million" you need to add some logic.

Notice that there is a upper limit of 100 millions as I have written the
query. You would also have to arrange for the first letter in the
resulting string to be uppercase.

You mention how would incorporate into an existing query, but since I
don't see that query and don't where you want the value, I'm not taking
a stab at that.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp