Data tab works great but i can't get it work in Preview tab in reporting services

This code works great in data tab but when i go to preview it i get a len function error.
I am passing possible BLANK values in all the strings not NULL. Do you think its an parameter expression issue? If so, how do I write a expression for each parameter that will reflect this logic. NOTE: As you can see states and program are multi param, and I don't know ahead of time what values will be entered. So if some could please help.
SELECT DISTINCT A.RECIPIENT_STATE, B.FISCAL_YEAR, B.FEDERAL_SHARE, B.NON_FEDERAL_SHARE
FROM Tab_Awards AS A JOIN TAB_Amendments AS B ON A.AWARD_NUMBER = B.AWARD_NUMBER JOIN Tab_Program AS C
ON A.FPO_ID = C.FPO_ID
WHERE (LEN(@.keywords)<1 OR (FREETEXT(A.*,@.keywords)))
AND (LEN(@.states)<1 OR A.RECIPIENT_STATE IN (@.states))
AND (LEN(@.program) <1 OR C.PROGRAM IN (@.program))
AND (LEN(@.fromYear)<1 OR B.FISCAL_YEAR >= @.fromYear)
AND (LEN(@.toYear)<1 OR B.FISCAL_YEAR <= @.toYear)
AND (LEN(@.year)<1 OR LEN(@.year)>=1)
AND (LEN(@.organization)<1 OR LEN(@.organization)>=1
From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comWhat exactly is the error ? Why I am asking is that len must be evaluating
some numeric value e.g year parameter must be having numeric and Len will
take string expression only. So Just have a look at all the parameters again
and see their data type.
Amarnath
"Dre" wrote:
> This code works great in data tab but when i go to preview it i get a len function error.
> I am passing possible BLANK values in all the strings not NULL. Do you think its an parameter expression issue? If so, how do I write a expression for each parameter that will reflect this logic. NOTE: As you can see states and program are multi param, and I don't know ahead of time what values will be entered. So if some could please help.
> SELECT DISTINCT A.RECIPIENT_STATE, B.FISCAL_YEAR, B.FEDERAL_SHARE, B.NON_FEDERAL_SHARE
> FROM Tab_Awards AS A JOIN TAB_Amendments AS B ON A.AWARD_NUMBER = B.AWARD_NUMBER JOIN Tab_Program AS C
> ON A.FPO_ID = C.FPO_ID
> WHERE (LEN(@.keywords)<1 OR (FREETEXT(A.*,@.keywords)))
> AND (LEN(@.states)<1 OR A.RECIPIENT_STATE IN (@.states))
> AND (LEN(@.program) <1 OR C.PROGRAM IN (@.program))
> AND (LEN(@.fromYear)<1 OR B.FISCAL_YEAR >= @.fromYear)
> AND (LEN(@.toYear)<1 OR B.FISCAL_YEAR <= @.toYear)
> AND (LEN(@.year)<1 OR LEN(@.year)>=1)
> AND (LEN(@.organization)<1 OR LEN(@.organization)>=1)
> From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
>

Data Sources

Is there anyway to use a Class File (VB.Net or C# class that generates a
DataSet) as the data source for a SQL Server 2005 Reporting Services report?
If there is, where are some examples or how-to's?
The only obvious way I see to generate a Data Source for a SQL report is a
sproc or dynamic query.
Thank You.Alex,
You want to use a Custom Data Processing Extension. You can even tie
the extension to an existing data layer.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_5c2q.asp
Andy Potter

Data Source on another server

I have always used Reporting Services to point to a DB on the same server as
RS.
If I set the datasource to another server I get:
Reporting Services Error
----
a.. An error has occurred during report processing. (rsProcessingAborted)
Get Online Help
a.. Cannot create a connection to data source 'dbAudit'.
(rsErrorOpeningConnection) Get Online Help
a.. Login failed for user 'Bha'. Reason: Not associated with a trusted
SQL Server connection.
----
How do I set this as a trusted connection?My guess is a problem with how you have set up your credentials for the data
source.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John Hann [MVP]" <blah@.blah.blah> wrote in message
news:%23vaUvnYzEHA.924@.TK2MSFTNGP10.phx.gbl...
> I have always used Reporting Services to point to a DB on the same server
as
> RS.
> If I set the datasource to another server I get:
> Reporting Services Error
> ----
--
> a.. An error has occurred during report processing.
(rsProcessingAborted)
> Get Online Help
> a.. Cannot create a connection to data source 'dbAudit'.
> (rsErrorOpeningConnection) Get Online Help
> a.. Login failed for user 'Bha'. Reason: Not associated with a
trusted
> SQL Server connection.
> ----
--
>
> How do I set this as a trusted connection?
>|||Its set as Windows Auth...Any special access I need on the DB at the other
server?
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OziGkwYzEHA.2716@.TK2MSFTNGP14.phx.gbl...
> My guess is a problem with how you have set up your credentials for the
> data
> source.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "John Hann [MVP]" <blah@.blah.blah> wrote in message
> news:%23vaUvnYzEHA.924@.TK2MSFTNGP10.phx.gbl...
>> I have always used Reporting Services to point to a DB on the same server
> as
>> RS.
>> If I set the datasource to another server I get:
>> Reporting Services Error
>> ----
> --
>> a.. An error has occurred during report processing.
> (rsProcessingAborted)
>> Get Online Help
>> a.. Cannot create a connection to data source 'dbAudit'.
>> (rsErrorOpeningConnection) Get Online Help
>> a.. Login failed for user 'Bha'. Reason: Not associated with a
> trusted
>> SQL Server connection.
>> ----
> --
>>
>> How do I set this as a trusted connection?
>>
>|||When you setup a datasource there is a tab that says credentials. You are
telling RS how to access the data for the report. For instance, I go against
Sybase a lot. I set it to a special username and password that is a readonly
username. Although you can tell it to use the username and password of the
person running the report, I recommend against doing that. If you can use a
set username and password then the system will pool connections and it can
reuse a connection rather than create a new one for each user.
Read up on credentials in the help.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John Hann [MVP]" <blah@.blah.blah> wrote in message
news:e49kiAZzEHA.3596@.TK2MSFTNGP12.phx.gbl...
> Its set as Windows Auth...Any special access I need on the DB at the other
> server?
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OziGkwYzEHA.2716@.TK2MSFTNGP14.phx.gbl...
> > My guess is a problem with how you have set up your credentials for the
> > data
> > source.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "John Hann [MVP]" <blah@.blah.blah> wrote in message
> > news:%23vaUvnYzEHA.924@.TK2MSFTNGP10.phx.gbl...
> >> I have always used Reporting Services to point to a DB on the same
server
> > as
> >> RS.
> >>
> >> If I set the datasource to another server I get:
> >>
> >> Reporting Services Error
>> ----
-
> > --
> >>
> >> a.. An error has occurred during report processing.
> > (rsProcessingAborted)
> >> Get Online Help
> >> a.. Cannot create a connection to data source 'dbAudit'.
> >> (rsErrorOpeningConnection) Get Online Help
> >> a.. Login failed for user 'Bha'. Reason: Not associated with a
> > trusted
> >> SQL Server connection.
> >>
>> ----
-
> > --
> >>
> >>
> >> How do I set this as a trusted connection?
> >>
> >>
> >
> >
>|||I see. I just assumed that the Windows Auth would work and use the access I
had provided to the db on that server.
John Hann
MOM MVP
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:Ooe9WRZzEHA.1292@.TK2MSFTNGP10.phx.gbl...
> When you setup a datasource there is a tab that says credentials. You are
> telling RS how to access the data for the report. For instance, I go
> against
> Sybase a lot. I set it to a special username and password that is a
> readonly
> username. Although you can tell it to use the username and password of the
> person running the report, I recommend against doing that. If you can use
> a
> set username and password then the system will pool connections and it can
> reuse a connection rather than create a new one for each user.
> Read up on credentials in the help.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "John Hann [MVP]" <blah@.blah.blah> wrote in message
> news:e49kiAZzEHA.3596@.TK2MSFTNGP12.phx.gbl...
>> Its set as Windows Auth...Any special access I need on the DB at the
>> other
>> server?
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:OziGkwYzEHA.2716@.TK2MSFTNGP14.phx.gbl...
>> > My guess is a problem with how you have set up your credentials for the
>> > data
>> > source.
>> >
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> >
>> > "John Hann [MVP]" <blah@.blah.blah> wrote in message
>> > news:%23vaUvnYzEHA.924@.TK2MSFTNGP10.phx.gbl...
>> >> I have always used Reporting Services to point to a DB on the same
> server
>> > as
>> >> RS.
>> >>
>> >> If I set the datasource to another server I get:
>> >>
>> >> Reporting Services Error
>> ----
> -
>> > --
>> >>
>> >> a.. An error has occurred during report processing.
>> > (rsProcessingAborted)
>> >> Get Online Help
>> >> a.. Cannot create a connection to data source 'dbAudit'.
>> >> (rsErrorOpeningConnection) Get Online Help
>> >> a.. Login failed for user 'Bha'. Reason: Not associated with a
>> > trusted
>> >> SQL Server connection.
>> >>
>> ----
> -
>> > --
>> >>
>> >>
>> >> How do I set this as a trusted connection?
>> >>
>> >>
>> >
>> >
>>
>|||Hmm...Still getting the Not a trusted connection with the local user id to
the SQL server...
"John Hann [MVP]" <blah@.blah.blah> wrote in message
news:uyDXwpZzEHA.1860@.TK2MSFTNGP15.phx.gbl...
>I see. I just assumed that the Windows Auth would work and use the access
>I had provided to the db on that server.
> John Hann
> MOM MVP
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:Ooe9WRZzEHA.1292@.TK2MSFTNGP10.phx.gbl...
>> When you setup a datasource there is a tab that says credentials. You are
>> telling RS how to access the data for the report. For instance, I go
>> against
>> Sybase a lot. I set it to a special username and password that is a
>> readonly
>> username. Although you can tell it to use the username and password of
>> the
>> person running the report, I recommend against doing that. If you can use
>> a
>> set username and password then the system will pool connections and it
>> can
>> reuse a connection rather than create a new one for each user.
>> Read up on credentials in the help.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "John Hann [MVP]" <blah@.blah.blah> wrote in message
>> news:e49kiAZzEHA.3596@.TK2MSFTNGP12.phx.gbl...
>> Its set as Windows Auth...Any special access I need on the DB at the
>> other
>> server?
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:OziGkwYzEHA.2716@.TK2MSFTNGP14.phx.gbl...
>> > My guess is a problem with how you have set up your credentials for
>> > the
>> > data
>> > source.
>> >
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> >
>> > "John Hann [MVP]" <blah@.blah.blah> wrote in message
>> > news:%23vaUvnYzEHA.924@.TK2MSFTNGP10.phx.gbl...
>> >> I have always used Reporting Services to point to a DB on the same
>> server
>> > as
>> >> RS.
>> >>
>> >> If I set the datasource to another server I get:
>> >>
>> >> Reporting Services Error
>> ----
>> -
>> > --
>> >>
>> >> a.. An error has occurred during report processing.
>> > (rsProcessingAborted)
>> >> Get Online Help
>> >> a.. Cannot create a connection to data source 'dbAudit'.
>> >> (rsErrorOpeningConnection) Get Online Help
>> >> a.. Login failed for user 'Bha'. Reason: Not associated with a
>> > trusted
>> >> SQL Server connection.
>> >>
>> ----
>> -
>> > --
>> >>
>> >>
>> >> How do I set this as a trusted connection?
>> >>
>> >>
>> >
>> >
>>
>>
>|||Have you pulled up the datasource in Report Manager, properties and set the
credentials. You should see something like this:
"John Hann [MVP]" <blah@.blah.blah> wrote in message
news:OMRdpwZzEHA.3844@.TK2MSFTNGP12.phx.gbl...
> Hmm...Still getting the Not a trusted connection with the local user id to
> the SQL server...
>
> "John Hann [MVP]" <blah@.blah.blah> wrote in message
> news:uyDXwpZzEHA.1860@.TK2MSFTNGP15.phx.gbl...
> >I see. I just assumed that the Windows Auth would work and use the
access
> >I had provided to the db on that server.
> > John Hann
> > MOM MVP
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > news:Ooe9WRZzEHA.1292@.TK2MSFTNGP10.phx.gbl...
> >> When you setup a datasource there is a tab that says credentials. You
are
> >> telling RS how to access the data for the report. For instance, I go
> >> against
> >> Sybase a lot. I set it to a special username and password that is a
> >> readonly
> >> username. Although you can tell it to use the username and password of
> >> the
> >> person running the report, I recommend against doing that. If you can
use
> >> a
> >> set username and password then the system will pool connections and it
> >> can
> >> reuse a connection rather than create a new one for each user.
> >>
> >> Read up on credentials in the help.
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "John Hann [MVP]" <blah@.blah.blah> wrote in message
> >> news:e49kiAZzEHA.3596@.TK2MSFTNGP12.phx.gbl...
> >> Its set as Windows Auth...Any special access I need on the DB at the
> >> other
> >> server?
> >>
> >>
> >> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> >> news:OziGkwYzEHA.2716@.TK2MSFTNGP14.phx.gbl...
> >> > My guess is a problem with how you have set up your credentials for
> >> > the
> >> > data
> >> > source.
> >> >
> >> > --
> >> > Bruce Loehle-Conger
> >> > MVP SQL Server Reporting Services
> >> >
> >> > "John Hann [MVP]" <blah@.blah.blah> wrote in message
> >> > news:%23vaUvnYzEHA.924@.TK2MSFTNGP10.phx.gbl...
> >> >> I have always used Reporting Services to point to a DB on the same
> >> server
> >> > as
> >> >> RS.
> >> >>
> >> >> If I set the datasource to another server I get:
> >> >>
> >> >> Reporting Services Error
> >>
>> ----
--
> >> -
> >> > --
> >> >>
> >> >> a.. An error has occurred during report processing.
> >> > (rsProcessingAborted)
> >> >> Get Online Help
> >> >> a.. Cannot create a connection to data source 'dbAudit'.
> >> >> (rsErrorOpeningConnection) Get Online Help
> >> >> a.. Login failed for user 'Bha'. Reason: Not associated with
a
> >> > trusted
> >> >> SQL Server connection.
> >> >>
> >>
>> ----
--
> >> -
> >> > --
> >> >>
> >> >>
> >> >> How do I set this as a trusted connection?
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >>
> >>
> >
> >
>|||Have you go into the datasource via Report Manager, properties? You should
see something like this for credentials:
Connect Using:
The credentials supplied by the user running the report
Display the following text to prompt user for a login name and
password:
Use as Windows credentials when connecting to the data
source
Credentials stored securely in the report server
To make sure everything is working just pick the first one. Then if you can
get to the database using query analyzer then report server can to. As I
mentioned before, I prefer specifying a single readonly user for the
credentials.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John Hann [MVP]" <blah@.blah.blah> wrote in message
news:OMRdpwZzEHA.3844@.TK2MSFTNGP12.phx.gbl...
> Hmm...Still getting the Not a trusted connection with the local user id to
> the SQL server...
>
> "John Hann [MVP]" <blah@.blah.blah> wrote in message
> news:uyDXwpZzEHA.1860@.TK2MSFTNGP15.phx.gbl...
> >I see. I just assumed that the Windows Auth would work and use the
access
> >I had provided to the db on that server.
> > John Hann
> > MOM MVP
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > news:Ooe9WRZzEHA.1292@.TK2MSFTNGP10.phx.gbl...
> >> When you setup a datasource there is a tab that says credentials. You
are
> >> telling RS how to access the data for the report. For instance, I go
> >> against
> >> Sybase a lot. I set it to a special username and password that is a
> >> readonly
> >> username. Although you can tell it to use the username and password of
> >> the
> >> person running the report, I recommend against doing that. If you can
use
> >> a
> >> set username and password then the system will pool connections and it
> >> can
> >> reuse a connection rather than create a new one for each user.
> >>
> >> Read up on credentials in the help.
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "John Hann [MVP]" <blah@.blah.blah> wrote in message
> >> news:e49kiAZzEHA.3596@.TK2MSFTNGP12.phx.gbl...
> >> Its set as Windows Auth...Any special access I need on the DB at the
> >> other
> >> server?
> >>
> >>
> >> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> >> news:OziGkwYzEHA.2716@.TK2MSFTNGP14.phx.gbl...
> >> > My guess is a problem with how you have set up your credentials for
> >> > the
> >> > data
> >> > source.
> >> >
> >> > --
> >> > Bruce Loehle-Conger
> >> > MVP SQL Server Reporting Services
> >> >
> >> > "John Hann [MVP]" <blah@.blah.blah> wrote in message
> >> > news:%23vaUvnYzEHA.924@.TK2MSFTNGP10.phx.gbl...
> >> >> I have always used Reporting Services to point to a DB on the same
> >> server
> >> > as
> >> >> RS.
> >> >>
> >> >> If I set the datasource to another server I get:
> >> >>
> >> >> Reporting Services Error
> >>
>> ----
--
> >> -
> >> > --
> >> >>
> >> >> a.. An error has occurred during report processing.
> >> > (rsProcessingAborted)
> >> >> Get Online Help
> >> >> a.. Cannot create a connection to data source 'dbAudit'.
> >> >> (rsErrorOpeningConnection) Get Online Help
> >> >> a.. Login failed for user 'Bha'. Reason: Not associated with
a
> >> > trusted
> >> >> SQL Server connection.
> >> >>
> >>
>> ----
--
> >> -
> >> > --
> >> >>
> >> >>
> >> >> How do I set this as a trusted connection?
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >>
> >>
> >
> >
>
begin 666 blank.gif
M1TE&.#EA`0`!`/<``````( ```" `(" ````@.( `@. " @.,# P,#<P-30R$ @.
M`& @.`( @.`* @.`, @.`. @.``! `"! `$! `&! `(! `*! `,! `.! ``!@.`"!@.
M`$!@.`&!@.`(!@.`*!@.`,!@.`.!@.``" `"" `$" `&" `(" `*" `," `." ``"@.
M`""@.`$"@.`&"@.`("@.`*"@.`,"@.`."@.``# `"# `$# `&# `(# `*# `,# `.#
M``#@.`"#@.`$#@.`&#@.`(#@.`*#@.`,#@.`.#@.````0" `0$ `0& `0( `0* `0, `
M0. `0 `@.0" @.0$ @.0& @.0( @.0* @.0, @.0. @.0 ! 0"! 0$! 0&! 0(! 0*!
M0,! 0.! 0 !@.0"!@.0$!@.0&!@.0(!@.0*!@.0,!@.0.!@.0 " 0"" 0$" 0&" 0("
M0*" 0," 0." 0 "@.0""@.0$"@.0&"@.0("@.0*"@.0,"@.0."@.0 # 0"# 0$# 0&#
M0(# 0*# 0,# 0.# 0 #@.0"#@.0$#@.0&#@.0(#@.0*#@.0,#@.0.#@.0 ``@." `@.$ `
M@.& `@.( `@.* `@., `@.. `@. `@.@." @.@.$ @.@.& @.@.( @.@.* @.@., @.@.. @.@. ! @."!
M@.$! @.&! @.(! @.*! @.,! @..! @. !@.@."!@.@.$!@.@.&!@.@.(!@.@.*!@.@.,!@.@..!@.@. "
M@."" @.$" @.&" @.(" @.*" @.," @.." @. "@.@.""@.@.$"@.@.&"@.@.("@.@.*"@.@.,"@.@.."@.
M@. # @."# @.$# @.&# @.(# @.*# @.,# @..# @. #@.@."#@.@.$#@.@.&#@.@.(#@.@.*#@.@.,#@.
M@..#@.@. ``P" `P$ `P& `P( `P* `P, `P. `P `@.P" @.P$ @.P& @.P( @.P* @.
MP, @.P. @.P ! P"! P$! P&! P(! P*! P,! P.! P !@.P"!@.P$!@.P&!@.P(!@.
MP*!@.P,!@.P.!@.P " P"" P$" P&" P(" P*" P," P." P "@.P""@.P$"@.P&"@.
MP("@.P*"@.P,"@.P."@.P # P"# P$# P&# P(# P*# P/_[\#INI8" @./\```#_
J`/__````__\`_P#______R'Y! $``/T`+ `````!``$```@.$`/L%! `[
`
end|||I did...May call this in to PSS...Don't want to waste your time
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:ec25o4ZzEHA.3820@.TK2MSFTNGP11.phx.gbl...
> Have you pulled up the datasource in Report Manager, properties and set
> the
> credentials. You should see something like this:
> "John Hann [MVP]" <blah@.blah.blah> wrote in message
> news:OMRdpwZzEHA.3844@.TK2MSFTNGP12.phx.gbl...
>> Hmm...Still getting the Not a trusted connection with the local user id
>> to
>> the SQL server...
>>
>> "John Hann [MVP]" <blah@.blah.blah> wrote in message
>> news:uyDXwpZzEHA.1860@.TK2MSFTNGP15.phx.gbl...
>> >I see. I just assumed that the Windows Auth would work and use the
> access
>> >I had provided to the db on that server.
>> > John Hann
>> > MOM MVP
>> >
>> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> > news:Ooe9WRZzEHA.1292@.TK2MSFTNGP10.phx.gbl...
>> >> When you setup a datasource there is a tab that says credentials. You
> are
>> >> telling RS how to access the data for the report. For instance, I go
>> >> against
>> >> Sybase a lot. I set it to a special username and password that is a
>> >> readonly
>> >> username. Although you can tell it to use the username and password of
>> >> the
>> >> person running the report, I recommend against doing that. If you can
> use
>> >> a
>> >> set username and password then the system will pool connections and it
>> >> can
>> >> reuse a connection rather than create a new one for each user.
>> >>
>> >> Read up on credentials in the help.
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "John Hann [MVP]" <blah@.blah.blah> wrote in message
>> >> news:e49kiAZzEHA.3596@.TK2MSFTNGP12.phx.gbl...
>> >> Its set as Windows Auth...Any special access I need on the DB at the
>> >> other
>> >> server?
>> >>
>> >>
>> >> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> >> news:OziGkwYzEHA.2716@.TK2MSFTNGP14.phx.gbl...
>> >> > My guess is a problem with how you have set up your credentials for
>> >> > the
>> >> > data
>> >> > source.
>> >> >
>> >> > --
>> >> > Bruce Loehle-Conger
>> >> > MVP SQL Server Reporting Services
>> >> >
>> >> > "John Hann [MVP]" <blah@.blah.blah> wrote in message
>> >> > news:%23vaUvnYzEHA.924@.TK2MSFTNGP10.phx.gbl...
>> >> >> I have always used Reporting Services to point to a DB on the same
>> >> server
>> >> > as
>> >> >> RS.
>> >> >>
>> >> >> If I set the datasource to another server I get:
>> >> >>
>> >> >> Reporting Services Error
>> >>
>> ----
> --
>> >> -
>> >> > --
>> >> >>
>> >> >> a.. An error has occurred during report processing.
>> >> > (rsProcessingAborted)
>> >> >> Get Online Help
>> >> >> a.. Cannot create a connection to data source 'dbAudit'.
>> >> >> (rsErrorOpeningConnection) Get Online Help
>> >> >> a.. Login failed for user 'Bha'. Reason: Not associated with
> a
>> >> > trusted
>> >> >> SQL Server connection.
>> >> >>
>> >>
>> ----
> --
>> >> -
>> >> > --
>> >> >>
>> >> >>
>> >> >> How do I set this as a trusted connection?
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>|||Man...That worked, but not the supplied credentials in the datasource
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OJ%23FJ6ZzEHA.1404@.TK2MSFTNGP11.phx.gbl...
> Have you go into the datasource via Report Manager, properties? You should
> see something like this for credentials:
> Connect Using:
> The credentials supplied by the user running the report
> Display the following text to prompt user for a login name and
> password:
> Use as Windows credentials when connecting to the data
> source
> Credentials stored securely in the report server
>
> To make sure everything is working just pick the first one. Then if you
> can
> get to the database using query analyzer then report server can to. As I
> mentioned before, I prefer specifying a single readonly user for the
> credentials.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "John Hann [MVP]" <blah@.blah.blah> wrote in message
> news:OMRdpwZzEHA.3844@.TK2MSFTNGP12.phx.gbl...
>> Hmm...Still getting the Not a trusted connection with the local user id
>> to
>> the SQL server...
>>
>> "John Hann [MVP]" <blah@.blah.blah> wrote in message
>> news:uyDXwpZzEHA.1860@.TK2MSFTNGP15.phx.gbl...
>> >I see. I just assumed that the Windows Auth would work and use the
> access
>> >I had provided to the db on that server.
>> > John Hann
>> > MOM MVP
>> >
>> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> > news:Ooe9WRZzEHA.1292@.TK2MSFTNGP10.phx.gbl...
>> >> When you setup a datasource there is a tab that says credentials. You
> are
>> >> telling RS how to access the data for the report. For instance, I go
>> >> against
>> >> Sybase a lot. I set it to a special username and password that is a
>> >> readonly
>> >> username. Although you can tell it to use the username and password of
>> >> the
>> >> person running the report, I recommend against doing that. If you can
> use
>> >> a
>> >> set username and password then the system will pool connections and it
>> >> can
>> >> reuse a connection rather than create a new one for each user.
>> >>
>> >> Read up on credentials in the help.
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "John Hann [MVP]" <blah@.blah.blah> wrote in message
>> >> news:e49kiAZzEHA.3596@.TK2MSFTNGP12.phx.gbl...
>> >> Its set as Windows Auth...Any special access I need on the DB at the
>> >> other
>> >> server?
>> >>
>> >>
>> >> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> >> news:OziGkwYzEHA.2716@.TK2MSFTNGP14.phx.gbl...
>> >> > My guess is a problem with how you have set up your credentials for
>> >> > the
>> >> > data
>> >> > source.
>> >> >
>> >> > --
>> >> > Bruce Loehle-Conger
>> >> > MVP SQL Server Reporting Services
>> >> >
>> >> > "John Hann [MVP]" <blah@.blah.blah> wrote in message
>> >> > news:%23vaUvnYzEHA.924@.TK2MSFTNGP10.phx.gbl...
>> >> >> I have always used Reporting Services to point to a DB on the same
>> >> server
>> >> > as
>> >> >> RS.
>> >> >>
>> >> >> If I set the datasource to another server I get:
>> >> >>
>> >> >> Reporting Services Error
>> >>
>> ----
> --
>> >> -
>> >> > --
>> >> >>
>> >> >> a.. An error has occurred during report processing.
>> >> > (rsProcessingAborted)
>> >> >> Get Online Help
>> >> >> a.. Cannot create a connection to data source 'dbAudit'.
>> >> >> (rsErrorOpeningConnection) Get Online Help
>> >> >> a.. Login failed for user 'Bha'. Reason: Not associated with
> a
>> >> > trusted
>> >> >> SQL Server connection.
>> >> >>
>> >>
>> ----
> --
>> >> -
>> >> > --
>> >> >>
>> >> >>
>> >> >> How do I set this as a trusted connection?
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>
>|||You might want to check out delegation, it lets you propagate a windows
authentication token from one machine to another. I haven't tried it yet but
i suspect you need to enable delegation for the report server to send the
windows authentication to the data source server. This assumes you are using
windows authentication with an active directory domain.
Please post back here if this is of some help, i intend to try it here some
day when we have more time.
"John Hann [MVP]" wrote:
> I have always used Reporting Services to point to a DB on the same server as
> RS.
> If I set the datasource to another server I get:
> Reporting Services Error
> ----
> a.. An error has occurred during report processing. (rsProcessingAborted)
> Get Online Help
> a.. Cannot create a connection to data source 'dbAudit'.
> (rsErrorOpeningConnection) Get Online Help
> a.. Login failed for user 'Bha'. Reason: Not associated with a trusted
> SQL Server connection.
> ----
>
> How do I set this as a trusted connection?
>
>

Data source name not found and no default driver specified

On the database server, when I try to connect my SQL Server registration, I
get the error on the subject line. WHen I check my SQL Server Services using
SQL Server Services Manager, I do not see my server name in the drop down box
- I have to type it in. SQL Server Agent and server services are started. My
jobs failed because of the error. I have never had to set up a DSN on the
server to get a connection to the database. PLease help to resolve
Hmmm...Is this a clean installation, an upgrade, etc...
Might be related (and I emphasize might) to MDAC. Check out:
http://support.microsoft.com/?kbid=301202
and
http://www.microsoft.com/downloads/d...displaylang=en
HTH
Jerry
"spoons" <spoons@.discussions.microsoft.com> wrote in message
news:CD4E905C-F6B5-4B1F-A1F8-8D205580DB35@.microsoft.com...
> On the database server, when I try to connect my SQL Server registration,
> I
> get the error on the subject line. WHen I check my SQL Server Services
> using
> SQL Server Services Manager, I do not see my server name in the drop down
> box
> - I have to type it in. SQL Server Agent and server services are started.
> My
> jobs failed because of the error. I have never had to set up a DSN on the
> server to get a connection to the database. PLease help to resolve
>

Data source name not found and no default driver specified

On the database server, when I try to connect my SQL Server registration, I
get the error on the subject line. WHen I check my SQL Server Services using
SQL Server Services Manager, I do not see my server name in the drop down box
- I have to type it in. SQL Server Agent and server services are started. My
jobs failed because of the error. I have never had to set up a DSN on the
server to get a connection to the database. PLease help to resolveHmmm...Is this a clean installation, an upgrade, etc...
Might be related (and I emphasize might) to MDAC. Check out:
http://support.microsoft.com/?kbid=301202
and
http://www.microsoft.com/downloads/details.aspx?FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c&displaylang=en
HTH
Jerry
"spoons" <spoons@.discussions.microsoft.com> wrote in message
news:CD4E905C-F6B5-4B1F-A1F8-8D205580DB35@.microsoft.com...
> On the database server, when I try to connect my SQL Server registration,
> I
> get the error on the subject line. WHen I check my SQL Server Services
> using
> SQL Server Services Manager, I do not see my server name in the drop down
> box
> - I have to type it in. SQL Server Agent and server services are started.
> My
> jobs failed because of the error. I have never had to set up a DSN on the
> server to get a connection to the database. PLease help to resolve
>sql

Data source name not found and no default driver specified

On the database server, when I try to connect my SQL Server registration, I
get the error on the subject line. WHen I check my SQL Server Services using
SQL Server Services Manager, I do not see my server name in the drop down bo
x
- I have to type it in. SQL Server Agent and server services are started. My
jobs failed because of the error. I have never had to set up a DSN on the
server to get a connection to the database. PLease help to resolveHmmm...Is this a clean installation, an upgrade, etc...
Might be related (and I emphasize might) to MDAC. Check out:
http://support.microsoft.com/?kbid=301202
and
http://www.microsoft.com/downloads/...&displaylang=en
HTH
Jerry
"spoons" <spoons@.discussions.microsoft.com> wrote in message
news:CD4E905C-F6B5-4B1F-A1F8-8D205580DB35@.microsoft.com...
> On the database server, when I try to connect my SQL Server registration,
> I
> get the error on the subject line. WHen I check my SQL Server Services
> using
> SQL Server Services Manager, I do not see my server name in the drop down
> box
> - I have to type it in. SQL Server Agent and server services are started.
> My
> jobs failed because of the error. I have never had to set up a DSN on the
> server to get a connection to the database. PLease help to resolve
>

Data Source issues through migration

We are using SQL Server 2000 Reporting Services. Our data resides in
Teradata. We have a Development/Acceptance Teradata server (TDDEV) and a
Production Teradata server (TDPROD). Our databases are named slightly
differently on each. For example, a database name EDM_D in development is
named EDM_A for acceptance and EDM_P in Production. What is the best way to
manage the need to change the schema name through migration? We want to
avoid opening every dataset, searching and replacing the schema name. I have
found that I can use a report parameter, but that stilll requires opening
multiple reports. Is there a way to create a global parameter for the
project? I am at my wit's end!Here's how I handle this.
The reports are created using a shared data source, let's say EDM in your
example.
Then in the target folder in each environment, I create a data source named
EDM, and enter an appropriate Connection string that connects to the
appropriate database.
"LoveBoxers" wrote:
> We are using SQL Server 2000 Reporting Services. Our data resides in
> Teradata. We have a Development/Acceptance Teradata server (TDDEV) and a
> Production Teradata server (TDPROD). Our databases are named slightly
> differently on each. For example, a database name EDM_D in development is
> named EDM_A for acceptance and EDM_P in Production. What is the best way to
> manage the need to change the schema name through migration? We want to
> avoid opening every dataset, searching and replacing the schema name. I have
> found that I can use a report parameter, but that stilll requires opening
> multiple reports. Is there a way to create a global parameter for the
> project? I am at my wit's end!

Data Source For Report Model Project

When I tried to generate ad-hoc reports with Microsoft reporting services
2005 (april CTP), it generally ask for the tables and views. Is there a way
that I can just put in a stored procedure and use the results as a data
feeder for the report model? Thanks.I don't think this is supported out of the box
You can however create a named query as part of the data source view
"Geek" wrote:
> When I tried to generate ad-hoc reports with Microsoft reporting services
> 2005 (april CTP), it generally ask for the tables and views. Is there a way
> that I can just put in a stored procedure and use the results as a data
> feeder for the report model? Thanks.sql

Data Source file on Reporting Services server question

In the Reporting Services Manager, when the user selects the folder where the
reports reside there is a file that contains the connection string
information along with a user name and password (Data Source).
Is there a way to keep the file from being selected by the user ?
Thanks.There is a setting in the properties of the data source to "Hide in List
View". Unfortunately the users can just click on the "Show Details"
option and see the datasource. Depending on the permissions you gave
them they will only be able to view the information.
I sent an question out some time ago seeing if there was away to remove
the "Show Details" for some users but no one replied.
Mike wrote:
> In the Reporting Services Manager, when the user selects the folder where the
> reports reside there is a file that contains the connection string
> information along with a user name and password (Data Source).
> Is there a way to keep the file from being selected by the user ?
>
> Thanks.|||You can use a datasource in another location. It's a faff because you
have to change it manually for each deployed report affected. Also
everytime you deploy the report it will create the datasources in the
deployment folder if it can, so you have to remove it each time.
Having said that, this would essentially cure your problem.
Chris
Bon733 wrote:
> There is a setting in the properties of the data source to "Hide in
> List View". Unfortunately the users can just click on the "Show
> Details" option and see the datasource. Depending on the permissions
> you gave them they will only be able to view the information.
> I sent an question out some time ago seeing if there was away to
> remove the "Show Details" for some users but no one replied.
> Mike wrote:
> > In the Reporting Services Manager, when the user selects the folder
> > where the reports reside there is a file that contains the
> > connection string information along with a user name and password
> > (Data Source).
> >
> > Is there a way to keep the file from being selected by the user ?
> >
> >
> > Thanks.

Data source fax copy

Can integration services be used to import data from a fax copy?A fax is just an image, so no.

data source error Login Failed

I am using Analysis Services and SQL 2000. On my Analysis Services machine I
define my data source to point to a remote SQL 2000 server using windows
authentication with my domain admin account. I can create the cube and
everything looks great but when I process the cube I get login errors. data
source provider error Login failed for user '\\domain\computer$' 42000.
Surely, this is an easy fix.
Thanks,
Kevin E.
Processing is actually done by the MSSQLServerOLAPService service
(msmdsrv.exe).
It is not based on your interactive credentials (like browsing, etc.)
You need to modify the service to use your domain account.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"KevinE" <eckart_612@.hotmail.com> wrote in message
news:i8Sdnbpqcc1h0hbfRVn-sA@.centurytel.net...
>I am using Analysis Services and SQL 2000. On my Analysis Services machine
>I define my data source to point to a remote SQL 2000 server using windows
>authentication with my domain admin account. I can create the cube and
>everything looks great but when I process the cube I get login errors.
>data source provider error Login failed for user '\\domain\computer$'
>42000. Surely, this is an easy fix.
> Thanks,
> Kevin E.
>
|||I'm having the same problem now... how do you modify this
MSSQLServerOLAPService service? thanks.
"Dave Wickert [MSFT]" wrote:

> Processing is actually done by the MSSQLServerOLAPService service
> (msmdsrv.exe).
> It is not based on your interactive credentials (like browsing, etc.)
> You need to modify the service to use your domain account.
> --
> Dave Wickert [MSFT]
> dwickert@.online.microsoft.com
> Program Manager
> BI SystemsTeam
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "KevinE" <eckart_612@.hotmail.com> wrote in message
> news:i8Sdnbpqcc1h0hbfRVn-sA@.centurytel.net...
>
>
|||Go into your services on the computer that is accessing the data source and
go to the properties of MSSQLServerOLAPService. In the login tab, change the
user from local system account to a domain level account.
KevinE.
"G. Kumar" <GKumar@.discussions.microsoft.com> wrote in message
news:607F313E-A43E-494C-B8F7-214C8320C013@.microsoft.com...[vbcol=seagreen]
> I'm having the same problem now... how do you modify this
> MSSQLServerOLAPService service? thanks.
> "Dave Wickert [MSFT]" wrote:

data source error Login Failed

I am using Analysis Services and SQL 2000. On my Analysis Services machine I
define my data source to point to a remote SQL 2000 server using windows
authentication with my domain admin account. I can create the cube and
everything looks great but when I process the cube I get login errors. data
source provider error Login failed for user '\\domain\computer$' 42000.
Surely, this is an easy fix.
Thanks,
Kevin E.Processing is actually done by the MSSQLServerOLAPService service
(msmdsrv.exe).
It is not based on your interactive credentials (like browsing, etc.)
You need to modify the service to use your domain account.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"KevinE" <eckart_612@.hotmail.com> wrote in message
news:i8Sdnbpqcc1h0hbfRVn-sA@.centurytel.net...
>I am using Analysis Services and SQL 2000. On my Analysis Services machine
>I define my data source to point to a remote SQL 2000 server using windows
>authentication with my domain admin account. I can create the cube and
>everything looks great but when I process the cube I get login errors.
>data source provider error Login failed for user '\\domain\computer$'
>42000. Surely, this is an easy fix.
> Thanks,
> Kevin E.
>|||I'm having the same problem now... how do you modify this
MSSQLServerOLAPService service? thanks.
"Dave Wickert [MSFT]" wrote:

> Processing is actually done by the MSSQLServerOLAPService service
> (msmdsrv.exe).
> It is not based on your interactive credentials (like browsing, etc.)
> You need to modify the service to use your domain account.
> --
> Dave Wickert [MSFT]
> dwickert@.online.microsoft.com
> Program Manager
> BI SystemsTeam
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> "KevinE" <eckart_612@.hotmail.com> wrote in message
> news:i8Sdnbpqcc1h0hbfRVn-sA@.centurytel.net...
>
>|||Go into your services on the computer that is accessing the data source and
go to the properties of MSSQLServerOLAPService. In the login tab, change the
user from local system account to a domain level account.
KevinE.
"G. Kumar" <GKumar@.discussions.microsoft.com> wrote in message
news:607F313E-A43E-494C-B8F7-214C8320C013@.microsoft.com...[vbcol=seagreen]
> I'm having the same problem now... how do you modify this
> MSSQLServerOLAPService service? thanks.
> "Dave Wickert [MSFT]" wrote:
>

Data Processing Extension and query designer

hi, I have successfully implemented my custom data processing extension for
Reporting Services (SQL Server 2005) and now I want to explore how to
implement my own query designer as well.
I did get a sample of how to do that, but the sample is targeted for
Reporting Services 2000. I could somehow modify it to suit my needs, but I
have a simple question. The sample I got shows how to execute the query to
get the IDataReader object and fill up the DataGrid. Now I want to try the
new DataGridView control instead, but is there a better way than the
following.
The major reason why I ask is that I find the data shown in the grid is one
row at a time (i.e. very SLOW).
IDataReader reader = null;
try
{
reader = command.ExecuteReader(CommandBehavior.SingleResult);
System.Data.DataTable dataTable = new System.Data.DataTable();
for (int i = 0; i < reader.FieldCount; i++)
{
string fieldName = reader.GetName(i);
dataTable.Columns.Add(fieldName);
}
while (reader.Read())
{
System.Data.DataRow dataRow = dataTable.NewRow();
for (int i = 0; i < reader.FieldCount; i++)
{
dataRow[i] = reader.GetValue(i);
}
dataTable.Rows.Add(dataRow);
}
dataGridView.DataSource = dataTable;
}
finally
{
if (reader != null)
{
reader.Dispose();
}
}
Any help is appreciated!!Hi,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood your main concern is the performance
for executing the query to
get the IDataReader object and fill up the DataGrid is very slow. If I have
misunderstood your concern, please feel free to point it out.
I have reviewed the your codes and it is OK. Maybe you should consider use
a stored procedure with SELECT * statement to get the result from resultset
at once instead of getting the data row one by one.
Sincerely yours,
Michael Cheng
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.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for your prompt reply. However, I don't think getting results back
from my data extension is slow ... when I switched back to the generic query
designer in Report Designer, the time taken to fill up the data grid is
instantaneous! But as soon as I use my custom query designer, I could see
how each data row is filled, one at a time (i.e. very slow).
Do you have any other suggestions?
Thank you for your help
Jenny
"Michael Cheng [MSFT]" wrote:
> Hi,
> Welcome to use MSDN Managed Newsgroup!
> From your descriptions, I understood your main concern is the performance
> for executing the query to
> get the IDataReader object and fill up the DataGrid is very slow. If I have
> misunderstood your concern, please feel free to point it out.
> I have reviewed the your codes and it is OK. Maybe you should consider use
> a stored procedure with SELECT * statement to get the result from resultset
> at once instead of getting the data row one by one.
>
> Sincerely yours,
> Michael Cheng
> 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.
> =====================================================> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
> Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
> If you are outside the United States, please visit our International
> Support page: http://support.microsoft.com/common/international.aspx
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Jenny,
Is it possible for you to generate a small sample of your custom Query
Designer for us to reproduce it on my side?
You may attach the zipped file here or send it to me directly. I understand
the information may be sensitive to you, my direct email address is
v-mingqc@.microsoft.com, you may send the file to me directly and I will
keep secure.
If you have any questions or concerns, don't hesitate to let me know. We
are always here to be of assistance!
Sincerely yours,
Michael Cheng
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.|||Thank you for your offer. I have sent you a sample already.
Just to make sure we are on the same page, I'm using VS .NET 2005 and SQL
Server 2005.
Thank you very much
Jenny
"Michael Cheng [MSFT]" wrote:
> Hi Jenny,
> Is it possible for you to generate a small sample of your custom Query
> Designer for us to reproduce it on my side?
> You may attach the zipped file here or send it to me directly. I understand
> the information may be sensitive to you, my direct email address is
> v-mingqc@.microsoft.com, you may send the file to me directly and I will
> keep secure.
> If you have any questions or concerns, don't hesitate to let me know. We
> are always here to be of assistance!
>
> Sincerely yours,
> Michael Cheng
> 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.
>|||Hi Jenny,
Thanks for your email.
I am looking into this issue with the help of .Net Expert. I will keep you
updated as soon as I find anything valueable.
Thank you for your patience and cooperation.
Sincerely yours,
Michael Cheng
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.|||Hi Jenny
I have received your sample project, and build it without any error.
However, I am not sure how to use it to reproduce out your issues. It seems
that the project run type is "Class Library", Can you show me how to use
this class library? I see the entry point should be in CsvDesigner class,
however, it seems that you missed the code to bootstrap this class.
Sincerely yours,
Michael Cheng
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.|||Don't worry, Michael. I have figured it out myself.
The trick is to use the splitter control and make sure the datagridview
control dock property is set to 'fill' underneath the splitter.
Now the datagridview control is filled up instantly.
Cheers
Jenny
"Michael Cheng [MSFT]" wrote:
> Hi Jenny
> I have received your sample project, and build it without any error.
> However, I am not sure how to use it to reproduce out your issues. It seems
> that the project run type is "Class Library", Can you show me how to use
> this class library? I see the entry point should be in CsvDesigner class,
> however, it seems that you missed the code to bootstrap this class.
>
> Sincerely yours,
> Michael Cheng
> 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.
>|||Hi Jenny,
Thanks so much for the update and it's great to hear you have resolved it
:) I this information is very helpful for those who encounter the same
problem.
Sincerely yours,
Michael Cheng
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.

Data Processing Extension - parameters not created

Hi,
I'm writing a Data Processing Extension (DPE) for Reporting Services 2000,
using the FsiExtension sample as a basis. My problem is that the
CreateParameter method of the command class doesn't get called, so when I am
to execute my datareader, the parameters aren't there. I have added the
parameters manually to the dataset in the report, but they don't get carried
over to the DPE. I've debugged the code, and the CreateParameter never gets
called. The strange thing is that I had it working earlier, because I then
could access the parameter collection in the debugger, but now suddenly it
fails.
public IDataParameter CreateParameter()
{
return (IDataParameter)(new dbpDataParameter());
}
Any tips?
Brgds
JonasI found the problem, my class only implemented IDbComamand. When I added
IDbCommandAnalysis and
a GetParameters method it worked like it should.
/Jonas
"Jonas" <Jonas@.nospam.pl> wrote in message
news:OyFcwrwiGHA.4512@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I'm writing a Data Processing Extension (DPE) for Reporting Services 2000,
> using the FsiExtension sample as a basis. My problem is that the
> CreateParameter method of the command class doesn't get called, so when I
> am to execute my datareader, the parameters aren't there. I have added the
> parameters manually to the dataset in the report, but they don't get
> carried over to the DPE. I've debugged the code, and the CreateParameter
> never gets called. The strange thing is that I had it working earlier,
> because I then could access the parameter collection in the debugger, but
> now suddenly it fails.
> public IDataParameter CreateParameter()
> {
> return (IDataParameter)(new dbpDataParameter());
> }
>
> Any tips?
> Brgds
> Jonas
>

Data Parameter Question

Hi All:
Reporting Services newbie here. I have a query that has a fieldcalled Date. I have created two parameters in my report namedStart Date and End Date.
I need to know the proper syntax to build an expression that willreturn a recordset that contains all records with dates between mystart and end dates.
How do I accomplish this? And also, does anyone know of aresource anywhere that lays out examples of the syntax for variousexpressions used in Reporting Services. The help documentationisn't very clear.
Thanks
Brennan

What you need involve Time Interval and it is not implemented in SQL Server now but I found a UDF(User Defined Function) that can do it. Use it and you will get what you need, I would also look at the stored procs in the Time Tracker starter kit it will have time interval cals. Try the link below for the code. Hope this helps.

http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-38-udf_DT_AddTime.htm

|||you can either call a stored proc that takes the startdate and enddate as parameters and gives you the result set or you can get the entire result set from the table and use filters to filter out the data between the dates. I'd recommend using a stored proc.|||

I am not sure if this is what you need but like most suggest you should use a stored proceedure but the syntax for the query would look like:

SELECT column_name FROM table_nameWHERE column_nameBETWEEN @.start_dateAND @.end_date

You can just try this directly in the report designer before making a stored procedure for it.

Note: @.start_date and @.end_date will be the value the user put in the parameter box.

|||Hi,
I have a similar question as you do, in fact I'm tryning to filter my report results between two dates, and I'm trying to do this directly in the Report designer using two report parameters, for the begin and for the end date. My problem is that the data type available for date parameters is "Date Time", and I want my parameter formated as a Date ( I don′t want to do it on the query because the parameter mus be shown as a date in the report) .Is there a way of doing it? Or I must set the parameters as strings, and cast them in the query? If so, how can I send an error message if the dates are not well fomated?
Thanks in advance for any help!
|||I didnt understand your question. Can you rephrase ?|||

Yes, what I wrote before was a little bit confuse,

In fact I have 2 different questions:

The first one is, can I have a report parameter configured as a Date instead of Date Time? (the data types available for the report parameters don't include the type Date).
The second question is, how can I send a pop-up with an error message in my report ? For example, if the parameter value inserted by the user is not valid for my report.

Thanks,
Cat.

|||

Hello:
Yes, just CAST the datetime value provided by the parameter within RS within SQL to the date - but thre is more to it than that!
The date passed as a parameter could be in any number of many formats - YYYY.DD/DD or YYYY-MM-DD, etc.
The dates based upon the culture or the dates stored within the data base!
This "date" thing drives me nuts at times in that I have to support Thai (which their year is 543 years ahead of the western calendar) but this is what I do and this may or may not be of help.
In my SQL data base I have dates that are in YYYY-MM-DD Hour:Minute:Sec, YYYYMMDD, MM/DD/YY, MM/DD/YYYY, DD/MM/YYYY, DD/MM/YY, etc. I did not create this mess but inherited it from programmers who developed the system in Thailand and Korea which I used the data within this database for another application -- I get really frustrated at times.
So, I define all date parameters in RS as strings. When an RS report is launched and the date parameter is displayed I provide a drop down list of available parameters which are:
NONE, Today, Yesterday, Beginning of Week, BI -Weekly, Beginning of Month, Monthly, Bi-Monthly, Quarterly, Yearly not in this particular order but nevertheless.
If they select NONE - I provide and additional two(2) parameters which allow them to enter the date in YYYYMMDD format.
I have also created a table in SQL called a Calendar Time Dimension (which is mandatory for Analysis Services).
The Calendar Time Dimension has a day for each day of the year and I have populated for 1995 through 2015.
The Calendar Time Dimension file has for each date within the year(s) that I populate the following:
Time Key which has a value for (eg. todays date 20050904) in the following formats:
YYYYMMDD, YYMMDD, MMDDYY, MMDDYYYY, DDMMYY, DDMMYYYY, MM/DD/YYYY, DD/MM/YYYY, including the Thai date which ifor today i s 20050904 + 543 (years) = 25480904, etc. I have all of the weirdo formats defined.
Also in the Calendar Time Dimension is the Calendar Week, Calendar Quarter, Fiscal Quarter, Month of Year, Week within Month, etc.
So when a user enters their own date via the date parameter (and elects not to select the any other parameter such as Today) within RS (a call to a stored procedure) I validate against the Calendar Time Dimension and if the date is invalid I provide back an error message via RS that indicates the date is invalid.
So my point (even though LOOONG WINDED) is that you should consider to manipulate the dates within a stored procedure based upon anything entered by the user within the date parameter within RS and validate against a similar Calendar Time Dimension table within the data base. So when a date is valid you use a JOIN to the Calendar Time Dimension to filter out the records you want within SQL. Another added benefit is that this really improves performance in that you are not filtering or comparing dates within each record within the DB.
If the user selects TODAY, Yesterday, Weekly etc. I have UDF's that generate the date ranges I use against the Calendar Time Dimension to process only the records I need against the table I go after in the Stored Procedure for the respective RS program.
If you want I can give you a stored procedure to create the Calendar TIme Dimension and then you can go from there.
Best Regards,

|||Thanks a lot for your post!
Cat.

Data mining

Maybe this question has been asked before but I still haven't found the answer.

Is it possible to create a Report with Reporting Services which would be similar to the Cube browser in Analysis Services?

With dynamic data mining directly in the report. I don't want a static report based on a cube. Otherwise it won't be useful to have a cube in my situation.

Thanks

Moving to Reporting Services forum

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Take a look at Report Builder in SQL Server 2005 Reporting Services. This isn't quite the same a a cube browser, but I think you'll find it does address many of the issues you'd be looking at.

Using Report Builder with a Report Model built on top of an Analysis Services cube is pretty easy.

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

-Lukasz

Data Integration Services

I am doing a data mining project using the census bureau data set. The data set is a pdf, so I saved it as a text file, I imported as a flat source file and am trying to create my data source? But am having trouble with this? Please help?

Can you post details of the errors you're seeing? This might be a question for the Integration Services forum.|||i fixed the error. but thanks. Perhaps, you could help me with Analysis services, Data source views. Under the Data source views tab select tables and views, if I only have one file of data with columns and rows, is it possible to data mine this one file. or should i be incorporating more files?|||

There are no specific restrictions on the number of tables you need to use to perform data mining. You can certainly mine data in a single file/table if that contains the attributes you're interesting in mining.

For example, you could have a single customer table with demographic information like age, income and so forth and you could apply several of the available algorithms to it to derive interesting patterns - clustering to group together similar customers, decision trees to classify them based on how they behave with respect to a predictable attribute or naive-bayes to explore dependencies and differences between attributes for customers.

|||

How would you implement the MS Time series algorithm to predict population patterns? or would you suggest I use MS decision trees?

Thanks

|||Create a table with date and columns for each series you want to analyze

Data incorrectly repeating itself

I have run into a strange issue that I believe is a SQL Reporting Services issue.

I have a report laid out in landscape setting that has 4 columns of text. Two of the columns are sub-reports (due to the complexity and size, we did not flatten out the data in the stored procedure) and two of the columns are regular fields.

The 2 columns of regular fields are smaller, and normally only grow to about 1/2 the height of he page. One of the two sub-reports contains large amounts of text, and at time grows larger than the height of the page.

When the sub-report grows larger than the current page, it correctly starts up on the next page. But the 2 fields of data from the main dataset (not the sub-report columns) repeat themselves on the next page as well.

What is even more strange is the 2 fields of data from the main dataset only repeat data to grow vertically as far as the sub-report needs to grow. So if there is more data in either of these 2 fields than is needed for the sub-report to grow on the 2nd page, it will cut off the data in both of these fields.

I have tried placing the information in a group header. Turned the "Repeat on new page" both True and False, Took away the table header and footer, forced a page break after each group, tried using the "Hide Duplicates" property on the field within the details section, and nothing has seemed to fix the issue.

If anyone has run into this and found a work around, let me know.

Thank you,

T.J.

Just bumping this thread on a Monday morning after posting late on a Friday afternoon.

I am wondering if anyone has run into this issue, and if so if there have been any resolutions or work arounds.

Thank you,

T.J.

Data in Table not showing

I am just starting with Report Services, so this may be an easy or dumb
problem but here goes:
I am working in Visual Studio with a simple report based on a sproc with
several parameters that returns a result set. In the report I have set up a
table and the dataset for it, as well as the parameters. Everything works
great until I test the report, the report renders but the table shows as
having no data. I can go to the data tab and run the sproc and enter the
same parameters and I get data back.
I have checked all the places I could think of that might hide the data, but
nothing.
I'm hoping someone else has been through this or has some ideas on where I
could look.
TIA,
NancyHave you added a filter somewhere? Edit the dataset on the Data tab, click
the Filters tab and see. Also go to Properties of your table and check the
Filters tab. If your sproc returns data, the data should appear in the
report. I have a sproc in one of my reports and ti works just fine.
Charles Kangai, MCT, MCDBA
"Nancy Lytle" wrote:
> I am just starting with Report Services, so this may be an easy or dumb
> problem but here goes:
> I am working in Visual Studio with a simple report based on a sproc with
> several parameters that returns a result set. In the report I have set up a
> table and the dataset for it, as well as the parameters. Everything works
> great until I test the report, the report renders but the table shows as
> having no data. I can go to the data tab and run the sproc and enter the
> same parameters and I get data back.
> I have checked all the places I could think of that might hide the data, but
> nothing.
> I'm hoping someone else has been through this or has some ideas on where I
> could look.
> TIA,
> Nancy
>
>|||Thanks, I went ahead and I checked for filters and I can find none. I know
it must be something simple I am missing.
Any other ideas? Anyone?
TIA,
Nancy
"Charles Kangai" <CharlesKangai@.discussions.microsoft.com> wrote in message
news:53541C34-C4A7-4769-AB92-43C023605AFD@.microsoft.com...
> Have you added a filter somewhere? Edit the dataset on the Data tab, click
> the Filters tab and see. Also go to Properties of your table and check the
> Filters tab. If your sproc returns data, the data should appear in the
> report. I have a sproc in one of my reports and ti works just fine.
> Charles Kangai, MCT, MCDBA
> "Nancy Lytle" wrote:
> > I am just starting with Report Services, so this may be an easy or dumb
> > problem but here goes:
> >
> > I am working in Visual Studio with a simple report based on a sproc with
> > several parameters that returns a result set. In the report I have set
up a
> > table and the dataset for it, as well as the parameters. Everything
works
> > great until I test the report, the report renders but the table shows as
> > having no data. I can go to the data tab and run the sproc and enter
the
> > same parameters and I get data back.
> > I have checked all the places I could think of that might hide the data,
but
> > nothing.
> >
> > I'm hoping someone else has been through this or has some ideas on where
I
> > could look.
> > TIA,
> > Nancy
> >
> >
> >

data horizontally

Hi
I would like to show the data in the report horizontally instead of
vertically. I am using reporting services 2000 and sql server 2000.
Thanks
passxPradeepRed...@.gmail.com wrote:
> Hi
> I would like to show the data in the report horizontally instead of
> vertically(which is the normal format). I am using reporting services 2000 and sql server 2000. Any immediate solutions will reallybe appreciated.
>
> Thanks
> passx|||PradeepRed...@.gmail.com wrote:
> Hi
> I would like to show the data in the report horizontally instead of
> vertically(which is the normal format). I am using reporting services 2000 and sql server 2000. Any immediate solutions will reallybe appreciated.
>
> Thanks
> passx|||PradeepRed...@.gmail.com wrote:
> Hi
> I would like to show the data in the report horizontally instead of
> vertically(which is the normal format). I am using reporting services 2000 and sql server 2000. Any immediate solutions will reallybe appreciated.
>
> Thanks
> passx|||PradeepReddy1@.gmail.com wrote:
> Hi
> I would like to show the data in the report horizontally instead of
> vertically. I am using reporting services 2000 and sql server 2000.
>
> Thanks
> passx
I think you can solve your problem when you can use the matrix and
place the row you want to place in a Column group
Regards
RajDeep.A|||No Raj
My requirement is something different. For example :
If we retrieve some values from a table in sql. Now these values are
represented as follows in the reporting serivices.
1st row 1st column of sql data in 1st row 1st column of report.
2nd row 1st column of sql data in 1st row 2nd column of report
3rd row 1st column of sql data in 1st row 3rd column of report.
|||ly for 1st row 2nd column of sql data in 2nd row 1st column in
report.
Hope this makes my requirement much more clear.
I think this can be done in sql but have no idea how to do that.
Any help will be appreciated.
RajDeep wrote:
> PradeepReddy1@.gmail.com wrote:
> > Hi
> >
> > I would like to show the data in the report horizontally instead of
> > vertically. I am using reporting services 2000 and sql server 2000.
> >
> >
> > Thanks
> > passx
> I think you can solve your problem when you can use the matrix and
> place the row you want to place in a Column group
> Regards
> RajDeep.A