reporting services valid values query based

I have a parameter that is query based. I have created my own UI to list out
the text boxes per parameter required by the report. Now comes the problem of
a query based parameter. How to I get the queary based valid values to
populate my drop down list boxEddie,
If I understand your correctly I may have some ideas that will help you with
query based Parameters, but first please clarify the reason you built a UI
for users to pass parameters into the report?
thx.
"Eddie" wrote:
> I have a parameter that is query based. I have created my own UI to list out
> the text boxes per parameter required by the report. Now comes the problem of
> a query based parameter. How to I get the queary based valid values to
> populate my drop down list box|||I have several clients that use browsers which the report viewer doesn't
behave well.
I did figure it out. The valid values come back as array when you call the
GetReportParameter method and set the "ForRendering" to TRUE.
So all I did was add the values to ListItems and populated a drop down.
Also, a nifty thing was to find all the Date type parameters and lock the
text box and provide a calendar. This allowed me to use our application's
Localization methods to handle all the different Date formats.
"RS_CZAR" wrote:
> Eddie,
> If I understand your correctly I may have some ideas that will help you with
> query based Parameters, but first please clarify the reason you built a UI
> for users to pass parameters into the report?
> thx.
> "Eddie" wrote:
> > I have a parameter that is query based. I have created my own UI to list out
> > the text boxes per parameter required by the report. Now comes the problem of
> > a query based parameter. How to I get the queary based valid values to
> > populate my drop down list box

Reporting Services SP using Dynamic Queries

Hey i am using Stored Procedures in Sql Reporting Services.
This SP has some parameters and based on these parameter's value Final query is being created at run time. But when i use this SP in VS.Net Reporting Services as dataset then it doesnt show its fields.

Here I am writing some part of my SP to show how things are working at my end. @.Country , @.StartDate , @.EndDate are SP parameters and based on these parameter’s value, some part of query is being added to main query. So in this case again I am not able to get fields in report.

IF LTRIM(RTRIM(@.Country)) IS NOT NULL

BEGIN
SET @.CountryQuery = " AND ID3.id_value LIKE '" + LTRIM(RTRIM(@.Country)) +"' "
END

IF LTRIM(RTRIM(@.StartDate)) IS NOT NULL AND LTRIM(RTRIM(@.EndDate)) IS NOT NULL
BEGIN
SET @.DateQuery = " AND ( "+@.FieldName+" >= '" + LTRIM(RTRIM(@.StartDate)) + "' AND "+@.FieldName+" <= '" + LTRIM(RTRIM(@.EndDate)) + "' ) "
END

SET @.Query = " SELECT DISTINCT field1, field2, field3 FROM Table1 T1 INNER JOIN Table2 T2 ON T1.id_fk = T2.id_code " + @.CityQuery + " WHERE tr_status =1" + @.DateQuery

EXEC (@.Query)

Even I have used temp table in SP and stored values in it but could not able to get desired result (get fields in report).

PLease help me
Thanks

Hi,

Try these steps:

1. In your DataSet put the query with fixed values (i.e. without parameters):

SELECT DISTINCT field1, field2, field3 FROM Table1 ...

2. Execute the Query

3. Go to the "DataSets Window" and select "refresh"

4. Now you should replace the query above with the Stored Procedure command

I think this solves your problem.

--

Telmo Moreira

Reporting Services Service Pack 2

Ok,
How do I get what Service Pack if any is installed for Reporting Services?
Version?
SQL query?
I see there is a Service Pack 2 for Reporting Services. Should everything be
fine with my reports after I
install this or is this going to be a mess? Break everything?
thanks
gvThere is no service pack 2 for RS 2005. RS 2000 does have it and is good, I
ran in production with that for quite awhile. For RS 2005 if you install SP1
then you also need to get a hotfix because SP1 broke using All for
multi-select parameters. SP2 is out as a pre-release. I have not installed
it however.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"gv" <viator.gerry@.gmail.com> wrote in message
news:%23OkTFkBBHHA.3368@.TK2MSFTNGP03.phx.gbl...
> Ok,
> How do I get what Service Pack if any is installed for Reporting Services?
> Version?
> SQL query?
> I see there is a Service Pack 2 for Reporting Services. Should everything
> be fine with my reports after I
> install this or is this going to be a mess? Break everything?
> thanks
> gv
>|||We are running SQL 2000 Report Services.
How do I check Version Information? What has been installed
thanks for your help
gv
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:uwG5U1BBHHA.1780@.TK2MSFTNGP03.phx.gbl...
> There is no service pack 2 for RS 2005. RS 2000 does have it and is good,
> I ran in production with that for quite awhile. For RS 2005 if you install
> SP1 then you also need to get a hotfix because SP1 broke using All for
> multi-select parameters. SP2 is out as a pre-release. I have not installed
> it however.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "gv" <viator.gerry@.gmail.com> wrote in message
> news:%23OkTFkBBHHA.3368@.TK2MSFTNGP03.phx.gbl...
>> Ok,
>> How do I get what Service Pack if any is installed for Reporting
>> Services? Version?
>> SQL query?
>> I see there is a Service Pack 2 for Reporting Services. Should everything
>> be fine with my reports after I
>> install this or is this going to be a mess? Break everything?
>> thanks
>> gv
>|||http://servername/Reportserver will show you the Version of the Reporting
service:
original installation 8.00.743.00
SP1 is Version 8.00.878.00
SP2 is Version 8.00.1038.00
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"gv" <viator.gerry@.gmail.com> wrote in message
news:uEryG5BBHHA.3560@.TK2MSFTNGP04.phx.gbl...
> We are running SQL 2000 Report Services.
> How do I check Version Information? What has been installed
> thanks for your help
> gv
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:uwG5U1BBHHA.1780@.TK2MSFTNGP03.phx.gbl...
>> There is no service pack 2 for RS 2005. RS 2000 does have it and is good,
>> I ran in production with that for quite awhile. For RS 2005 if you
>> install SP1 then you also need to get a hotfix because SP1 broke using
>> All for multi-select parameters. SP2 is out as a pre-release. I have not
>> installed it however.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "gv" <viator.gerry@.gmail.com> wrote in message
>> news:%23OkTFkBBHHA.3368@.TK2MSFTNGP03.phx.gbl...
>> Ok,
>> How do I get what Service Pack if any is installed for Reporting
>> Services? Version?
>> SQL query?
>> I see there is a Service Pack 2 for Reporting Services. Should
>> everything be fine with my reports after I
>> install this or is this going to be a mess? Break everything?
>> thanks
>> gv
>>
>|||ok great!
I'm running SP2 - 8.00.1038.00
thanks for your help
gv
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:e%237Y1ICBHHA.1196@.TK2MSFTNGP02.phx.gbl...
> http://servername/Reportserver will show you the Version of the Reporting
> service:
> original installation 8.00.743.00
> SP1 is Version 8.00.878.00
> SP2 is Version 8.00.1038.00
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "gv" <viator.gerry@.gmail.com> wrote in message
> news:uEryG5BBHHA.3560@.TK2MSFTNGP04.phx.gbl...
>> We are running SQL 2000 Report Services.
>> How do I check Version Information? What has been installed
>> thanks for your help
>> gv
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:uwG5U1BBHHA.1780@.TK2MSFTNGP03.phx.gbl...
>> There is no service pack 2 for RS 2005. RS 2000 does have it and is
>> good, I ran in production with that for quite awhile. For RS 2005 if you
>> install SP1 then you also need to get a hotfix because SP1 broke using
>> All for multi-select parameters. SP2 is out as a pre-release. I have not
>> installed it however.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "gv" <viator.gerry@.gmail.com> wrote in message
>> news:%23OkTFkBBHHA.3368@.TK2MSFTNGP03.phx.gbl...
>> Ok,
>> How do I get what Service Pack if any is installed for Reporting
>> Services? Version?
>> SQL query?
>> I see there is a Service Pack 2 for Reporting Services. Should
>> everything be fine with my reports after I
>> install this or is this going to be a mess? Break everything?
>> thanks
>> gv
>>
>>
>|||Another easy way of figuring out if you have SP 2 to RS 2000, is to see if
there's a printer icon on the toolbar of a report that has been rendered. No
printer icon = no SP 2. Printer icon = SP2.
Kaisa M. Lindahl Lervik
"gv" <viator.gerry@.gmail.com> wrote in message
news:eHAz1MCBHHA.996@.TK2MSFTNGP02.phx.gbl...
> ok great!
> I'm running SP2 - 8.00.1038.00
> thanks for your help
> gv
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:e%237Y1ICBHHA.1196@.TK2MSFTNGP02.phx.gbl...
>> http://servername/Reportserver will show you the Version of the Reporting
>> service:
>> original installation 8.00.743.00
>> SP1 is Version 8.00.878.00
>> SP2 is Version 8.00.1038.00
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "gv" <viator.gerry@.gmail.com> wrote in message
>> news:uEryG5BBHHA.3560@.TK2MSFTNGP04.phx.gbl...
>> We are running SQL 2000 Report Services.
>> How do I check Version Information? What has been installed
>> thanks for your help
>> gv
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:uwG5U1BBHHA.1780@.TK2MSFTNGP03.phx.gbl...
>> There is no service pack 2 for RS 2005. RS 2000 does have it and is
>> good, I ran in production with that for quite awhile. For RS 2005 if
>> you install SP1 then you also need to get a hotfix because SP1 broke
>> using All for multi-select parameters. SP2 is out as a pre-release. I
>> have not installed it however.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "gv" <viator.gerry@.gmail.com> wrote in message
>> news:%23OkTFkBBHHA.3368@.TK2MSFTNGP03.phx.gbl...
>> Ok,
>> How do I get what Service Pack if any is installed for Reporting
>> Services? Version?
>> SQL query?
>> I see there is a Service Pack 2 for Reporting Services. Should
>> everything be fine with my reports after I
>> install this or is this going to be a mess? Break everything?
>> thanks
>> gv
>>
>>
>>
>

Reporting Services Query designer toolbar disabled

Hi to all , i'm trying to modify an mdx generated by query designer in "Design" mode.
I would like to switch to query designer in "Query" mode but the query designer toolbar and its button "Change Type" appears to be disabled.
In this project i use a shared data source and the data source type is Analysis Services.
In another project I followed the first basic Tutorial on Reporting Service ("Sales Order" report) so data source it's SQL Server and query designer it is locked to Query Mode and i cannot change it to Design Mode.
Thanks for help.
Alberto De MarcoI'm not sure which buttons you are referring to. Could you attach a picture of what you are seeing?

-Albert

Reporting Services Query designer toolbar disabled

Hi to all , i'm trying to modify an mdx generated by query designer in "Design" mode.
I would like to switch to query designer in "Query" mode but the query designer toolbar and its button "Change Type" appears to be disabled.
In this project i use a shared data source and the data source type is Analysis Services.
In another project I followed the first basic Tutorial on Reporting Service ("Sales Order" report) so data source it's SQL Server and query designer it is locked to Query Mode and i cannot change it to Design Mode.
Thanks for help.
Alberto De MarcoI'm not sure which buttons you are referring to. Could you attach a picture of what you are seeing?

-Albert

Reporting Services Query assistance...

Is it possible to do the following in Reporting Services...
In the query of the report have a column's critera be something along the
lines of this:
= ReportItem!Textbox25
In access it is possible through this syntax..
[Reports]![Report1]![Text5]
ThanksDon't worry I have sorted this out with a sub-report.
"ashvsaod" <ashvsaod@.hotmail.com> wrote in message
news:evkowNSeEHA.1036@.TK2MSFTNGP10.phx.gbl...
> Is it possible to do the following in Reporting Services...
> In the query of the report have a column's critera be something along the
> lines of this:
> = ReportItem!Textbox25
> In access it is possible through this syntax..
> [Reports]![Report1]![Text5]
> Thanks
>sql

Reporting Services Query

Hi
SQL Reporting Services 2005 is the first reporting tool I have ever
used so I am battling a bit to set up some of the reports I'd like to
create. I'd very much appreciate some help.
Question 1
Take the following simple dataset as an example.
VALUE
100
120
150
500
501
502
520
Is it possible in RS to group the values on ranges? For example, to
generate a report that looks like:
RANGE COUNT
0-100 1
100-150 2
150-500 0
500-520 4
Question 2
Dataset:
NAME MONTH COUNT
A Jan 10
B Aug 88
B Nov 22
Can I get RS to generate the 'missing' months for me and put those
Counts to 0? In other words...for 'A' there is only a count for the
month of January. I'd like the report to show columns from 'Jan' to
'Dec' and to put 0's in the months that are not in the dataset for that
row.
Example result :
NAME JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
A 10 0 0 0 0 0 0 0
0 0 0 0
B 0 0 0 0 0 0 0
88 0 0 22 0
I can generate the 'missing' months in my dataset query but its fairly
expensive.
Kinds regards,
Pieter LessingThe short answer is no to both questions.
Question 1. What you need is called discretization (equal ranges in this
case) which Analysis Services supports but not Reporting Services. So, one
solution to this requirement is to build a light-weight cube on top your
data source and use Reporting Services to report from it. Another approach,
of course, is to implement your own discretization at the data source level.
Question 2. You should be able to do this at the data source, e.g. using a
cursor.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
<pieter.lessing@.gmail.com> wrote in message
news:1134121073.144341.281820@.z14g2000cwz.googlegroups.com...
> Hi
> SQL Reporting Services 2005 is the first reporting tool I have ever
> used so I am battling a bit to set up some of the reports I'd like to
> create. I'd very much appreciate some help.
> Question 1
> Take the following simple dataset as an example.
> VALUE
> 100
> 120
> 150
> 500
> 501
> 502
> 520
> Is it possible in RS to group the values on ranges? For example, to
> generate a report that looks like:
> RANGE COUNT
> 0-100 1
> 100-150 2
> 150-500 0
> 500-520 4
> Question 2
> Dataset:
> NAME MONTH COUNT
> A Jan 10
> B Aug 88
> B Nov 22
> Can I get RS to generate the 'missing' months for me and put those
> Counts to 0? In other words...for 'A' there is only a count for the
> month of January. I'd like the report to show columns from 'Jan' to
> 'Dec' and to put 0's in the months that are not in the dataset for that
> row.
> Example result :
> NAME JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
> A 10 0 0 0 0 0 0 0
> 0 0 0 0
> B 0 0 0 0 0 0 0
> 88 0 0 22 0
> I can generate the 'missing' months in my dataset query but its fairly
> expensive.
> Kinds regards,
> Pieter Lessing
>

reporting services parameter problem

I have a report that uses as available values for a parameter a query. My problem is that the query return a list of available months, however, sometimes, i would like the user to choose null when they want to see all months in the report. but the query does not have a month null so null is not allowed.
Is there anything I can do? I cannot add a null record in the table itself.
thank you, dreyYou can either allow null for a parameter or use a UNION to add All Months to the available options. (Adding All Months tends to give a better end-user experience.) Note that the following query (because of the UNION) isn't valid in the graphical query designer. You'll be prompted to switch to the generic query designer. You'll have to modify the query to match your particular DB schema.

SELECT MonthNumber, MonthName
FROM Months
UNION
SELECT -1, 'All Months'

Now you've got your parameter values populated. If you're using the 'All Months' option, you'll want to uncheck "Allow null values".

You should now modify your dataset query to:

SELECT column1, column2, column3
FROM table1
WHERE (MonthNumber = @.MonthNumber OR @.MonthNumber = -1)

If you select a month, you will get the appropriate data back for that month. If you select All Months, then MonthNumber = -1 will always be false (since -1 isn't a valid month number), but the second part will evaluate as true for all months.

Hope that helps.

Reporting Services on AS2005 cube

Hi all,

I'm trying to create a RS report that will use an AS2005 cube as a datasource. I did try to use the query wizard from reporting services to build the mdx statement but I'm having a hard time with it... How could I use the following MDX statement in a reporting services report? Do you have to use the query builder?

Code Snippet

WITH SET [Last 5 Months] AS

{TAIL( NONEMPTY( {[Date].[Calendar].[Month].MEMBERS}, {[Measures].[Internet Sales Amount]} ) ,1).ITEM(0).LAG(1),

TAIL( NONEMPTY( {[Date].[Calendar].[Month].MEMBERS}, {[Measures].[Internet Sales Amount]} ) ,1).ITEM(0).LAG(2),

TAIL( NONEMPTY( {[Date].[Calendar].[Month].MEMBERS}, {[Measures].[Internet Sales Amount]} ) ,1).ITEM(0).LAG(3),

TAIL( NONEMPTY( {[Date].[Calendar].[Month].MEMBERS}, {[Measures].[Internet Sales Amount]} ) ,1).ITEM(0).LAG(4),

TAIL( NONEMPTY( {[Date].[Calendar].[Month].MEMBERS}, {[Measures].[Internet Sales Amount]} ) ,1).ITEM(0).LAG(5)}

MEMBER [Date].[Calendar].[AVG 5 months] AS

AVG([Last 5 Months])

MEMBER [Date].[Calendar].[Last Mth Var.] AS

(TAIL( NONEMPTY( {[Date].[Calendar].[Month].MEMBERS}, {[Measures].[Internet Sales Amount]} ) ,1).ITEM(0).LAG(1) - AVG([Last 5 Months]))

MEMBER [Date].[Calendar].[YTD] AS

AGGREGATE(YTD(TAIL( NONEMPTY( {[Date].[Calendar].[Month].MEMBERS}, {[Measures].[Internet Sales Amount]} ) ,1).ITEM(0).LAG(1)))

SELECT

{[Last 5 Months],[AVG 5 Months],[Last Mth Var.],[YTD]} ON COLUMNS,

NONEMPTY([Product].[Category].Members) ON ROWS

FROM [Adventure Works]

WHERE [Measures].[Internet Sales Amount]

It's probably very simple to acheive but for some reason I can't get this to work properly...

I did a lot of RS against relational DB but reporting out of a cube is new to me.

TIA,

Eric

In the query builder, there's an option at the top for design mode, clicking this will bring you to the mdx.

If you've already been there, sorry for the explanation. Are you receiving any errors?

cheers,

Andrew

|||The SSAS 2005 provider, which the MDX Query Designer uses, requires measures on columns while you request an MDX set. The long story short -- you cannot use this query as it is with the MDX Query Designer. Instead, consider using the Microsoft OLE DB Provider for Analysis Services 9.0. It allows you to send whatever query you want but it doesn't support parameterized queries.|||

Thanks Teo,

I looks like RS is a good reporting tool against Relational DB but it's not so good with complex MDX that you need to parameterized...

|||Please post this to the wish list on connect.microsoft.com.

Reporting Services Has a problem with my Dynamic SQL

I have a stored proc that I can execute from Visual Studio, Query Analyzer,
and from within the "Design the Query" window in the Report Wizard. It
returns data in all cases. However, when I continue on with the wizard and
get to the "Design the Table" page, there are no fields in the "Available
Fields" list box. If I continue on and Finish, there an ugly error message
about "MS Development Environment is unable to load this document.
Deserialization failed: The element 'http://schemas..../reportdefinition:
TableCells' has incomplete content... An error occurred at , (33, 16)."
What is unusual is that it seems to bomb anytime I use dynamic SQL, but if I
use a local variable (casting a date as a string) it will work. If I use the
same local variable, and parse the incoming date parameter to the identical
date string, it will not work.
Here's the code... any help is appreciated.
ALTER PROCEDURE ggpappl.INCIDENT_SUMMARY_RPT
(
@.Shop varchar(15),
@.DateBgn datetime,
@.DateEnd datetime,
@.IncTypes varchar(4096)
)
AS
SET NOCOUNT ON
DECLARE
@.lv_shop varchar(15),
@.lv_types varchar(4096),
@.lv_SQL varchar(8000)
BEGIN
If IsNumeric(@.Shop) = 1
SET @.lv_shop = @.Shop
Else
SET @.lv_shop = '%'
SET @.lv_types = @.IncTypes
SET @.lv_types = Replace(@.lv_types, ', ', ''',''')
SET @.lv_types = ('''' + @.lv_types + '''')
SET @.lv_SQL = 'select ggptrk08.prod_ln_i, ' +
'ggptrk07.incdnt_typ_d, ' +
'ggptrk08.incdnt_strt_t, ' +
'ggptrk08.incdnt_end_t, ' +
'ggptrk08.incdnt_n, ' +
'Round(DateDiff(mi, ggptrk08.incdnt_strt_t, ISNULL(ggptrk08.incdnt_end_t,
getdate())), 2) Duration, ' +
'ggptrk08.mold_n, ' +
'ggptrk08.mount_sect_i, ' +
'ggptrk08.mount_pstn_i, ' +
'ggptrk06.incdnt_rsn_d, ' +
'ggptrk08.incdnt_cmnt_e, ' +
'ggptrk27.incdnt_actn_t, ' +
'ggptrk27.incdnt_solution_c, ' +
'ggptrk27.actn_cmnt, actn_wrkd_x, ' +
'SUBSTRING(ggpemp02_incident.emp_m, 1, 3) emp_incident, ' +
'SUBSTRING(ggpemp02_action.emp_m, 1, 3) emp_action ' +
'FROM ggp.GGPEMP02_EMP GGPEMP02_incident ' +
'RIGHT OUTER JOIN ggp.GGPTRK08_PROD_LN_INCDNT GGPTRK08 ON
GGPEMP02_incident.JDE_ADDR_BOOK_N = GGPTRK08.EMPL_JDE_ADDR_BOOK_N ' +
'LEFT OUTER JOIN ggp.GGPTRK06_INCDNT_RSN GGPTRK06 ON GGPTRK08.INCDNT_RSN_C
= GGPTRK06.INCDNT_RSN_C ' +
'LEFT OUTER JOIN ggp.GGPTRK07_INCDNT_TYP GGPTRK07 ON GGPTRK08.INCDNT_TYP_C
= GGPTRK07.INCDNT_TYP_C ' +
'LEFT OUTER JOIN ggp.GGPTRK27_INCDNT_ACTN GGPTRK27 ' +
'LEFT OUTER JOIN ggp.GGPEMP02_EMP GGPEMP02_action ON
GGPTRK27.EMPL_JDE_ADDR_BOOK_N = GGPEMP02_action.JDE_ADDR_BOOK_N ON ' +
'GGPTRK08.INCDNT_N = GGPTRK27.INCDNT_N ' +
'WHERE ggptrk08.prod_ln_i LIKE ''' + @.Shop + ''' ' +
'AND ggptrk08.incdnt_strt_t >= ''' + Cast(@.DateBgn AS varchar(20)) + '''
' +
'AND ggptrk08.incdnt_strt_t < ''' + Cast(@.DateEnd AS varchar(20)) + '''
' +
'AND ggptrk07.incdnt_typ_c IN(' + @.lv_types + ') ' +
'ORDER BY ' +
'ggptrk08.prod_ln_i, ' +
'ggptrk07.incdnt_typ_d, ' +
'ggptrk08.incdnt_strt_t'
Exec (@.lv_SQL)
ENDIt seems to me that Report Wizard cannot get field list because it is a
dynamic query.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"JKramer" <JKramer@.discussions.microsoft.com> wrote in message
news:E9FF54EA-478F-4CE4-BC17-219BAA942D4D@.microsoft.com...
>I have a stored proc that I can execute from Visual Studio, Query Analyzer,
> and from within the "Design the Query" window in the Report Wizard. It
> returns data in all cases. However, when I continue on with the wizard and
> get to the "Design the Table" page, there are no fields in the "Available
> Fields" list box. If I continue on and Finish, there an ugly error message
> about "MS Development Environment is unable to load this document.
> Deserialization failed: The element 'http://schemas..../reportdefinition:
> TableCells' has incomplete content... An error occurred at , (33, 16)."
> What is unusual is that it seems to bomb anytime I use dynamic SQL, but if
> I
> use a local variable (casting a date as a string) it will work. If I use
> the
> same local variable, and parse the incoming date parameter to the
> identical
> date string, it will not work.
> Here's the code... any help is appreciated.
> ALTER PROCEDURE ggpappl.INCIDENT_SUMMARY_RPT
> (
> @.Shop varchar(15),
> @.DateBgn datetime,
> @.DateEnd datetime,
> @.IncTypes varchar(4096)
> )
> AS
> SET NOCOUNT ON
> DECLARE
> @.lv_shop varchar(15),
> @.lv_types varchar(4096),
> @.lv_SQL varchar(8000)
> BEGIN
> If IsNumeric(@.Shop) = 1
> SET @.lv_shop = @.Shop
> Else
> SET @.lv_shop = '%'
> SET @.lv_types = @.IncTypes
> SET @.lv_types = Replace(@.lv_types, ', ', ''',''')
> SET @.lv_types = ('''' + @.lv_types + '''')
> SET @.lv_SQL = 'select ggptrk08.prod_ln_i, ' +
> 'ggptrk07.incdnt_typ_d, ' +
> 'ggptrk08.incdnt_strt_t, ' +
> 'ggptrk08.incdnt_end_t, ' +
> 'ggptrk08.incdnt_n, ' +
> 'Round(DateDiff(mi, ggptrk08.incdnt_strt_t, ISNULL(ggptrk08.incdnt_end_t,
> getdate())), 2) Duration, ' +
> 'ggptrk08.mold_n, ' +
> 'ggptrk08.mount_sect_i, ' +
> 'ggptrk08.mount_pstn_i, ' +
> 'ggptrk06.incdnt_rsn_d, ' +
> 'ggptrk08.incdnt_cmnt_e, ' +
> 'ggptrk27.incdnt_actn_t, ' +
> 'ggptrk27.incdnt_solution_c, ' +
> 'ggptrk27.actn_cmnt, actn_wrkd_x, ' +
> 'SUBSTRING(ggpemp02_incident.emp_m, 1, 3) emp_incident, ' +
> 'SUBSTRING(ggpemp02_action.emp_m, 1, 3) emp_action ' +
> 'FROM ggp.GGPEMP02_EMP GGPEMP02_incident ' +
> 'RIGHT OUTER JOIN ggp.GGPTRK08_PROD_LN_INCDNT GGPTRK08 ON
> GGPEMP02_incident.JDE_ADDR_BOOK_N = GGPTRK08.EMPL_JDE_ADDR_BOOK_N ' +
> 'LEFT OUTER JOIN ggp.GGPTRK06_INCDNT_RSN GGPTRK06 ON GGPTRK08.INCDNT_RSN_C
> = GGPTRK06.INCDNT_RSN_C ' +
> 'LEFT OUTER JOIN ggp.GGPTRK07_INCDNT_TYP GGPTRK07 ON GGPTRK08.INCDNT_TYP_C
> = GGPTRK07.INCDNT_TYP_C ' +
> 'LEFT OUTER JOIN ggp.GGPTRK27_INCDNT_ACTN GGPTRK27 ' +
> 'LEFT OUTER JOIN ggp.GGPEMP02_EMP GGPEMP02_action ON
> GGPTRK27.EMPL_JDE_ADDR_BOOK_N = GGPEMP02_action.JDE_ADDR_BOOK_N ON ' +
> 'GGPTRK08.INCDNT_N = GGPTRK27.INCDNT_N ' +
> 'WHERE ggptrk08.prod_ln_i LIKE ''' + @.Shop + ''' ' +
> 'AND ggptrk08.incdnt_strt_t >= ''' + Cast(@.DateBgn AS varchar(20)) + '''
> ' +
> 'AND ggptrk08.incdnt_strt_t < ''' + Cast(@.DateEnd AS varchar(20)) + '''
> ' +
> 'AND ggptrk07.incdnt_typ_c IN(' + @.lv_types + ') ' +
> 'ORDER BY ' +
> 'ggptrk08.prod_ln_i, ' +
> 'ggptrk07.incdnt_typ_d, ' +
> 'ggptrk08.incdnt_strt_t'
> Exec (@.lv_SQL)
> END|||Hi JKramer,
yes, when you link a stored procedure to a dataset in report services
it queries the report and builds the list of columns returned from the
query.....this is very, very handy as you can name your fields
consistently and then copy reports and just change the stored procedure
the report uses and it will 'auto-magically' put the data into the
right fields in the datasets linked to tables and charts.....very very
handy...if you use strings like this I gather than you can not do
this...
I don't see why you would want to generate strings like this.....we
tried this and found performance around 50% down on just using straight
sql with variables in it....also with strings you are limited to 8000
characters which you may over-run without knowing it....we have
queries over 8000 characters....
We are yet to find a situation where we cannot make do with using sql
and not strings like this......
Best Regards
Peter
www.peternolan.com|||Peter,
The reason I use dynamic SQL is because one of the where conditions is a
LIKE condition, which I'm taking in as one string parameter. So the input
might be "21,22,23" or it might be "31". It is also dynamic, so I'm never
exactly sure what might be coming in (though I'm sure I won't hit the 8000
character limit). I don't know how to build that using straight SQL. If it
can be done, I agree, that's the way to go.
Can you tell me how I'd accomplish what I'm trying to do with straight SQL?
"Peter Nolan" wrote:
> Hi JKramer,
> yes, when you link a stored procedure to a dataset in report services
> it queries the report and builds the list of columns returned from the
> query.....this is very, very handy as you can name your fields
> consistently and then copy reports and just change the stored procedure
> the report uses and it will 'auto-magically' put the data into the
> right fields in the datasets linked to tables and charts.....very very
> handy...if you use strings like this I gather than you can not do
> this...
> I don't see why you would want to generate strings like this.....we
> tried this and found performance around 50% down on just using straight
> sql with variables in it....also with strings you are limited to 8000
> characters which you may over-run without knowing it....we have
> queries over 8000 characters....
> We are yet to find a situation where we cannot make do with using sql
> and not strings like this......
> Best Regards
> Peter
> www.peternolan.com
>|||In case anyone is interested, I think the problem is actually with the wizard
and not in the handling of the dynamic SQL. After re-reading the error, I got
to thinking the problem was in the *.rdl. So as I attempted to "fix" it (and
I'm not familiar enough with it to do so), I didn't help the issue at all. So
I copied the text of a different *.rdl inot the one I couldn't get to work
and saved it. When I opened it up, it opened just fine (of course the data
source was wrong, since it was copied from another). So I went to the data
tab, pointed it to the correct Stored Proc (still using dynamic SQL) and it
worked fine from there. So the problem was not the data source, but the
wizard.
Hopefully that helps anyone else who runs into this one.
Where does one post a bug for reporting services?
"JKramer" wrote:
> I have a stored proc that I can execute from Visual Studio, Query Analyzer,
> and from within the "Design the Query" window in the Report Wizard. It
> returns data in all cases. However, when I continue on with the wizard and
> get to the "Design the Table" page, there are no fields in the "Available
> Fields" list box. If I continue on and Finish, there an ugly error message
> about "MS Development Environment is unable to load this document.
> Deserialization failed: The element 'http://schemas..../reportdefinition:
> TableCells' has incomplete content... An error occurred at , (33, 16)."
> What is unusual is that it seems to bomb anytime I use dynamic SQL, but if I
> use a local variable (casting a date as a string) it will work. If I use the
> same local variable, and parse the incoming date parameter to the identical
> date string, it will not work.
> Here's the code... any help is appreciated.
> ALTER PROCEDURE ggpappl.INCIDENT_SUMMARY_RPT
> (
> @.Shop varchar(15),
> @.DateBgn datetime,
> @.DateEnd datetime,
> @.IncTypes varchar(4096)
> )
> AS
> SET NOCOUNT ON
> DECLARE
> @.lv_shop varchar(15),
> @.lv_types varchar(4096),
> @.lv_SQL varchar(8000)
> BEGIN
> If IsNumeric(@.Shop) = 1
> SET @.lv_shop = @.Shop
> Else
> SET @.lv_shop = '%'
> SET @.lv_types = @.IncTypes
> SET @.lv_types = Replace(@.lv_types, ', ', ''',''')
> SET @.lv_types = ('''' + @.lv_types + '''')
> SET @.lv_SQL = 'select ggptrk08.prod_ln_i, ' +
> 'ggptrk07.incdnt_typ_d, ' +
> 'ggptrk08.incdnt_strt_t, ' +
> 'ggptrk08.incdnt_end_t, ' +
> 'ggptrk08.incdnt_n, ' +
> 'Round(DateDiff(mi, ggptrk08.incdnt_strt_t, ISNULL(ggptrk08.incdnt_end_t,
> getdate())), 2) Duration, ' +
> 'ggptrk08.mold_n, ' +
> 'ggptrk08.mount_sect_i, ' +
> 'ggptrk08.mount_pstn_i, ' +
> 'ggptrk06.incdnt_rsn_d, ' +
> 'ggptrk08.incdnt_cmnt_e, ' +
> 'ggptrk27.incdnt_actn_t, ' +
> 'ggptrk27.incdnt_solution_c, ' +
> 'ggptrk27.actn_cmnt, actn_wrkd_x, ' +
> 'SUBSTRING(ggpemp02_incident.emp_m, 1, 3) emp_incident, ' +
> 'SUBSTRING(ggpemp02_action.emp_m, 1, 3) emp_action ' +
> 'FROM ggp.GGPEMP02_EMP GGPEMP02_incident ' +
> 'RIGHT OUTER JOIN ggp.GGPTRK08_PROD_LN_INCDNT GGPTRK08 ON
> GGPEMP02_incident.JDE_ADDR_BOOK_N = GGPTRK08.EMPL_JDE_ADDR_BOOK_N ' +
> 'LEFT OUTER JOIN ggp.GGPTRK06_INCDNT_RSN GGPTRK06 ON GGPTRK08.INCDNT_RSN_C
> = GGPTRK06.INCDNT_RSN_C ' +
> 'LEFT OUTER JOIN ggp.GGPTRK07_INCDNT_TYP GGPTRK07 ON GGPTRK08.INCDNT_TYP_C
> = GGPTRK07.INCDNT_TYP_C ' +
> 'LEFT OUTER JOIN ggp.GGPTRK27_INCDNT_ACTN GGPTRK27 ' +
> 'LEFT OUTER JOIN ggp.GGPEMP02_EMP GGPEMP02_action ON
> GGPTRK27.EMPL_JDE_ADDR_BOOK_N = GGPEMP02_action.JDE_ADDR_BOOK_N ON ' +
> 'GGPTRK08.INCDNT_N = GGPTRK27.INCDNT_N ' +
> 'WHERE ggptrk08.prod_ln_i LIKE ''' + @.Shop + ''' ' +
> 'AND ggptrk08.incdnt_strt_t >= ''' + Cast(@.DateBgn AS varchar(20)) + '''
> ' +
> 'AND ggptrk08.incdnt_strt_t < ''' + Cast(@.DateEnd AS varchar(20)) + '''
> ' +
> 'AND ggptrk07.incdnt_typ_c IN(' + @.lv_types + ') ' +
> 'ORDER BY ' +
> 'ggptrk08.prod_ln_i, ' +
> 'ggptrk07.incdnt_typ_d, ' +
> 'ggptrk08.incdnt_strt_t'
> Exec (@.lv_SQL)
> END|||In general with Stored Procedures I very seldom use the Report Wizard. I
create a new report, go to the data tab and set it up, then to layout and
drag and drop the table. Right mouse click on a column to add additional
columns. Or, start off with a query that has the appropriate fields, use the
wizard and then change to a stored procedure.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JKramer" <JKramer@.discussions.microsoft.com> wrote in message
news:75EEA5FA-0373-420B-B90E-0CF660220EF2@.microsoft.com...
> In case anyone is interested, I think the problem is actually with the
> wizard
> and not in the handling of the dynamic SQL. After re-reading the error, I
> got
> to thinking the problem was in the *.rdl. So as I attempted to "fix" it
> (and
> I'm not familiar enough with it to do so), I didn't help the issue at all.
> So
> I copied the text of a different *.rdl inot the one I couldn't get to work
> and saved it. When I opened it up, it opened just fine (of course the data
> source was wrong, since it was copied from another). So I went to the data
> tab, pointed it to the correct Stored Proc (still using dynamic SQL) and
> it
> worked fine from there. So the problem was not the data source, but the
> wizard.
> Hopefully that helps anyone else who runs into this one.
> Where does one post a bug for reporting services?
> "JKramer" wrote:
>> I have a stored proc that I can execute from Visual Studio, Query
>> Analyzer,
>> and from within the "Design the Query" window in the Report Wizard. It
>> returns data in all cases. However, when I continue on with the wizard
>> and
>> get to the "Design the Table" page, there are no fields in the "Available
>> Fields" list box. If I continue on and Finish, there an ugly error
>> message
>> about "MS Development Environment is unable to load this document.
>> Deserialization failed: The element
>> 'http://schemas..../reportdefinition:
>> TableCells' has incomplete content... An error occurred at , (33, 16)."
>> What is unusual is that it seems to bomb anytime I use dynamic SQL, but
>> if I
>> use a local variable (casting a date as a string) it will work. If I use
>> the
>> same local variable, and parse the incoming date parameter to the
>> identical
>> date string, it will not work.
>> Here's the code... any help is appreciated.
>> ALTER PROCEDURE ggpappl.INCIDENT_SUMMARY_RPT
>> (
>> @.Shop varchar(15),
>> @.DateBgn datetime,
>> @.DateEnd datetime,
>> @.IncTypes varchar(4096)
>> )
>> AS
>> SET NOCOUNT ON
>> DECLARE
>> @.lv_shop varchar(15),
>> @.lv_types varchar(4096),
>> @.lv_SQL varchar(8000)
>> BEGIN
>> If IsNumeric(@.Shop) = 1
>> SET @.lv_shop = @.Shop
>> Else
>> SET @.lv_shop = '%'
>> SET @.lv_types = @.IncTypes
>> SET @.lv_types = Replace(@.lv_types, ', ', ''',''')
>> SET @.lv_types = ('''' + @.lv_types + '''')
>> SET @.lv_SQL = 'select ggptrk08.prod_ln_i, ' +
>> 'ggptrk07.incdnt_typ_d, ' +
>> 'ggptrk08.incdnt_strt_t, ' +
>> 'ggptrk08.incdnt_end_t, ' +
>> 'ggptrk08.incdnt_n, ' +
>> 'Round(DateDiff(mi, ggptrk08.incdnt_strt_t, ISNULL(ggptrk08.incdnt_end_t,
>> getdate())), 2) Duration, ' +
>> 'ggptrk08.mold_n, ' +
>> 'ggptrk08.mount_sect_i, ' +
>> 'ggptrk08.mount_pstn_i, ' +
>> 'ggptrk06.incdnt_rsn_d, ' +
>> 'ggptrk08.incdnt_cmnt_e, ' +
>> 'ggptrk27.incdnt_actn_t, ' +
>> 'ggptrk27.incdnt_solution_c, ' +
>> 'ggptrk27.actn_cmnt, actn_wrkd_x, ' +
>> 'SUBSTRING(ggpemp02_incident.emp_m, 1, 3) emp_incident, ' +
>> 'SUBSTRING(ggpemp02_action.emp_m, 1, 3) emp_action ' +
>> 'FROM ggp.GGPEMP02_EMP GGPEMP02_incident ' +
>> 'RIGHT OUTER JOIN ggp.GGPTRK08_PROD_LN_INCDNT GGPTRK08 ON
>> GGPEMP02_incident.JDE_ADDR_BOOK_N = GGPTRK08.EMPL_JDE_ADDR_BOOK_N ' +
>> 'LEFT OUTER JOIN ggp.GGPTRK06_INCDNT_RSN GGPTRK06 ON
>> GGPTRK08.INCDNT_RSN_C
>> = GGPTRK06.INCDNT_RSN_C ' +
>> 'LEFT OUTER JOIN ggp.GGPTRK07_INCDNT_TYP GGPTRK07 ON
>> GGPTRK08.INCDNT_TYP_C
>> = GGPTRK07.INCDNT_TYP_C ' +
>> 'LEFT OUTER JOIN ggp.GGPTRK27_INCDNT_ACTN GGPTRK27 ' +
>> 'LEFT OUTER JOIN ggp.GGPEMP02_EMP GGPEMP02_action ON
>> GGPTRK27.EMPL_JDE_ADDR_BOOK_N = GGPEMP02_action.JDE_ADDR_BOOK_N ON ' +
>> 'GGPTRK08.INCDNT_N = GGPTRK27.INCDNT_N ' +
>> 'WHERE ggptrk08.prod_ln_i LIKE ''' + @.Shop + ''' ' +
>> 'AND ggptrk08.incdnt_strt_t >= ''' + Cast(@.DateBgn AS varchar(20)) + '''
>> ' +
>> 'AND ggptrk08.incdnt_strt_t < ''' + Cast(@.DateEnd AS varchar(20)) + '''
>> ' +
>> 'AND ggptrk07.incdnt_typ_c IN(' + @.lv_types + ') ' +
>> 'ORDER BY ' +
>> 'ggptrk08.prod_ln_i, ' +
>> 'ggptrk07.incdnt_typ_d, ' +
>> 'ggptrk08.incdnt_strt_t'
>> Exec (@.lv_SQL)
>> END|||Hi JKramer,
does it recognise the fieds being returned or do you have to type them
into the dataset? When you add a dataset to a report it automatically
adds the parameters and the fields returned from the SP...this is what
I was saying was useful...I gave up on wizards almost
immediately...they are just WAY too limited....we set up templates and
put the templates into a vs directory and they appear as templates when
we create a new report..although most of the time we just copy reports
anyway...
Peter|||It appears that a lot of folks have raised this issue also, but there was
never a concreate solution from the MFST experts?
Here is an example of a stored procedure that creates a dynamic query. I am
using a simple example to demonstrate the problem that can be recreated
easily, so please do not reply and said "why dont you use a straight select
statement instead?"
Create PROCEDURE dbo.QuarterlyReportingTest
@.CustomerPk int
AS
declare @.SQL varchar(2000)
select @.SQL = 'select * from [DataWarehouse].dbo.CustomerDim where
CustomerPk = ' + convert(varchar(10), @.CustomerPk)
exec (@.SQL)
This query will return the particular rows of information depending on the
report parameter @.CustomerPk
In RS, the dataset was setup as command type of 'Stored Procedure'. The
query string field has 'QuarterlyReportingTest'. When I clicked the '!', it
will prompt me for the CustomerPk. I would enter the CustomerPk, and it will
return the appropriate data.
The problem is, the DataSet fields remain blank. I can click the "Refresh
field" button, but the DataSet fields are still blank. Of course, without
the DataSet field, you cannot go and create the report, and report rendering
would also have error because there are no fields associated with the dataset.
Can anyone help in this so that the Dataset fields are populated / available
for these dynamic queries stored procedures?
Thanks.
LBJOHN99@.yahoo.com|||Hi JKramer,
can you use 'in' and just pass this set of values?
Also, I just got my copy of hitchhikers guide to SQL Server 2000 and
there is a specific example of this on pages 535 and 536. Since I'm a
believer in guys getting paid for their books I won't write the example
here...but they provide an example of picking multiple values from a
parameter in 2000 and then passing it through to a query making it safe
from SQL Injection along the way....the example is selecting a set of
employee ids from a list and printing the employee details...
Not sure if this would be applicable to your specific problem...but if
you know someone near you with a copy of the book you can look and see.
Best Regards
Peter Nolan
www.peternolan.com

Reporting Services Data Source and Windows Integrated Security to

Hi,
I need to query SSAS Cube from Reporting Services with a Data Source which
use Windows Integrated Security, but I can't do this.
I've installed Reporting Services on a Win2k3 Server (machine A).
Reporting Services is configured to run with Network Service Identity.
The security is to be Windows Integrated, and so I've done.
Reporting Services uses a Windows Integrated Data Source to connect to SQL
Server Analys Services.
I've installed SQL Server 2005 on another Win2k3 (machine B).
The account used to run SQL Server 2005 and the Analysys Services is a
Domain Account.
Both servers are in the same Domain.
I use the windows integrated security to access the main page of Reporting
Services.
When I click on my report I receives an error.
The Reporting Services fails passing the correct credential to the Data
Source, which is configured to run as Windows Integrated Security.
With Windows Integrated security I am able to filter data on SSAS Cube, and
this is mandatory.
How I can force Reporting services to pass the Windows Integrated Credential?
Do I have to use Kerberos? If yes, How can I do this?
Thanks,
Pietro.Just go to cube and right click for properties and on the datsource select
the default radio button. It should work.
Amarnath
"Pietro" wrote:
> Hi,
> I need to query SSAS Cube from Reporting Services with a Data Source which
> use Windows Integrated Security, but I can't do this.
> I've installed Reporting Services on a Win2k3 Server (machine A).
> Reporting Services is configured to run with Network Service Identity.
> The security is to be Windows Integrated, and so I've done.
> Reporting Services uses a Windows Integrated Data Source to connect to SQL
> Server Analys Services.
> I've installed SQL Server 2005 on another Win2k3 (machine B).
> The account used to run SQL Server 2005 and the Analysys Services is a
> Domain Account.
> Both servers are in the same Domain.
> I use the windows integrated security to access the main page of Reporting
> Services.
> When I click on my report I receives an error.
> The Reporting Services fails passing the correct credential to the Data
> Source, which is configured to run as Windows Integrated Security.
> With Windows Integrated security I am able to filter data on SSAS Cube, and
> this is mandatory.
> How I can force Reporting services to pass the Windows Integrated Credential?
> Do I have to use Kerberos? If yes, How can I do this?
> Thanks,
> Pietro.
>
>|||Hi, Amarnath
Your suggestion doesn't work.
I've cheched the data source and cube connection on the Cube SSAS. Both are
checked as "default".
When the Reporting Services and SQL Cube are on the same server, then there
are NO errors.
When Reporting Services and the SSAS Cube are into different machines, then
the integrated authentication fails.
Pietro.
"Amarnath" wrote:
> Just go to cube and right click for properties and on the datsource select
> the default radio button. It should work.
> Amarnath
> "Pietro" wrote:
> > Hi,
> > I need to query SSAS Cube from Reporting Services with a Data Source which
> > use Windows Integrated Security, but I can't do this.
> >
> > I've installed Reporting Services on a Win2k3 Server (machine A).
> > Reporting Services is configured to run with Network Service Identity.
> > The security is to be Windows Integrated, and so I've done.
> >
> > Reporting Services uses a Windows Integrated Data Source to connect to SQL
> > Server Analys Services.
> >
> > I've installed SQL Server 2005 on another Win2k3 (machine B).
> > The account used to run SQL Server 2005 and the Analysys Services is a
> > Domain Account.
> >
> > Both servers are in the same Domain.
> >
> > I use the windows integrated security to access the main page of Reporting
> > Services.
> >
> > When I click on my report I receives an error.
> > The Reporting Services fails passing the correct credential to the Data
> > Source, which is configured to run as Windows Integrated Security.
> >
> > With Windows Integrated security I am able to filter data on SSAS Cube, and
> > this is mandatory.
> >
> > How I can force Reporting services to pass the Windows Integrated Credential?
> > Do I have to use Kerberos? If yes, How can I do this?
> >
> > Thanks,
> > Pietro.
> >
> >
> >
> >|||When you select the data source using datasource designer, have you tested
the connection and is it connecting ? try when the server is different and
"test the connection" and see whether it is connecting...
Amarnath
"Pietro" wrote:
> Hi, Amarnath
> Your suggestion doesn't work.
> I've cheched the data source and cube connection on the Cube SSAS. Both are
> checked as "default".
> When the Reporting Services and SQL Cube are on the same server, then there
> are NO errors.
> When Reporting Services and the SSAS Cube are into different machines, then
> the integrated authentication fails.
> Pietro.
>
> "Amarnath" wrote:
> > Just go to cube and right click for properties and on the datsource select
> > the default radio button. It should work.
> >
> > Amarnath
> >
> > "Pietro" wrote:
> >
> > > Hi,
> > > I need to query SSAS Cube from Reporting Services with a Data Source which
> > > use Windows Integrated Security, but I can't do this.
> > >
> > > I've installed Reporting Services on a Win2k3 Server (machine A).
> > > Reporting Services is configured to run with Network Service Identity.
> > > The security is to be Windows Integrated, and so I've done.
> > >
> > > Reporting Services uses a Windows Integrated Data Source to connect to SQL
> > > Server Analys Services.
> > >
> > > I've installed SQL Server 2005 on another Win2k3 (machine B).
> > > The account used to run SQL Server 2005 and the Analysys Services is a
> > > Domain Account.
> > >
> > > Both servers are in the same Domain.
> > >
> > > I use the windows integrated security to access the main page of Reporting
> > > Services.
> > >
> > > When I click on my report I receives an error.
> > > The Reporting Services fails passing the correct credential to the Data
> > > Source, which is configured to run as Windows Integrated Security.
> > >
> > > With Windows Integrated security I am able to filter data on SSAS Cube, and
> > > this is mandatory.
> > >
> > > How I can force Reporting services to pass the Windows Integrated Credential?
> > > Do I have to use Kerberos? If yes, How can I do this?
> > >
> > > Thanks,
> > > Pietro.
> > >
> > >
> > >
> > >