Report Parameter not working in subquery?

Hi All,
There are two parameters in our DataSet based on the query below.
@.Year causes "syntax or access violation" error, because it's in subquery.
@.Parameter2 works (in the main query)
----
SELECT *,
( CASE WHEN Av_MTD >= ISNULL(Av_Comm, 0)
THEN 'Green'
WHEN Av_MTD >= (Av_Comm) * .9 THEN 'Yellow'
WHEN Av_MTD < ISNULL(Av_Comm,0)
THEN 'Red' ELSE 'Black'
END) AS Avail_color_MTD,
( CASE WHEN Re_MTD <= ISNULL(Re_Comm, 0)
THEN 'Green' WHEN Re_MTD <= (Re_Comm) * 1.1
THEN 'Yellow' WHEN Re_MTD > ISNULL(Re_Comm, 0)
THEN 'Red' ELSE 'Black' END
) AS Resp_color_MTD
FROM (
SELECT
Sla_Name_By_Loc,
MAX(Sla_Definition) AS Sla_Definition,
MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%' THEN
Sla_Commitment_By_Loc END) AS Av_Comm,
MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%' THEN
Sla_Commitment_By_Loc END) AS Re_Comm,
MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%' THEN
Sla_Result_by_loc END) AS Av_MTD,
MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%' THEN
Sla_Result_by_loc END) AS Re_MTD
FROM
Sla_Results_Trans_BY_LOC
WHERE (Sla_Category_id_By_Loc = 2)
AND (Sla_Year_By_Loc = @.Year) -- Syntax Error Or Access Violation
AND (Sla_Month_by_loc = 9)
AND (Sla_Pete_Clients_Code_by_loc = 'NJ01')
GROUP BY
Sla_Name_By_Loc) Y
where Y.Re_Comm = @.Parameter2 -- works!
ORDER BY Sla_Name_By_Loc
---
Please help!
TaoMake sure that you have two report parameters. In layout, menu
reports->report parameter. Next go to your dataset, ... , parameters tab and
make sure that each of your query parameters are mapped to a report
parameter. Being a subquery or not really doesn't matter.
Bruce L-C
"Tao Zuo" <Tao Zuo@.discussions.microsoft.com> wrote in message
news:69F8B719-B789-4DFC-8D6E-20ECD6430B7E@.microsoft.com...
> Hi All,
> There are two parameters in our DataSet based on the query below.
> @.Year causes "syntax or access violation" error, because it's in subquery.
> @.Parameter2 works (in the main query)
> ----
> SELECT *,
> ( CASE WHEN Av_MTD >= ISNULL(Av_Comm, 0)
> THEN 'Green'
> WHEN Av_MTD >= (Av_Comm) * .9 THEN 'Yellow'
> WHEN Av_MTD < ISNULL(Av_Comm,0)
> THEN 'Red' ELSE 'Black'
> END) AS Avail_color_MTD,
> ( CASE WHEN Re_MTD <= ISNULL(Re_Comm, 0)
> THEN 'Green' WHEN Re_MTD <= (Re_Comm) * 1.1
> THEN 'Yellow' WHEN Re_MTD > ISNULL(Re_Comm, 0)
> THEN 'Red' ELSE 'Black' END
> ) AS Resp_color_MTD
> FROM (
> SELECT
> Sla_Name_By_Loc,
> MAX(Sla_Definition) AS Sla_Definition,
> MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%' THEN
> Sla_Commitment_By_Loc END) AS Av_Comm,
> MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%' THEN
> Sla_Commitment_By_Loc END) AS Re_Comm,
> MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%' THEN
> Sla_Result_by_loc END) AS Av_MTD,
> MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%' THEN
> Sla_Result_by_loc END) AS Re_MTD
> FROM
> Sla_Results_Trans_BY_LOC
> WHERE (Sla_Category_id_By_Loc = 2)
> AND (Sla_Year_By_Loc = @.Year) -- Syntax Error Or Access Violation
> AND (Sla_Month_by_loc = 9)
> AND (Sla_Pete_Clients_Code_by_loc = 'NJ01')
> GROUP BY
> Sla_Name_By_Loc) Y
> where Y.Re_Comm = @.Parameter2 -- works!
> ORDER BY Sla_Name_By_Loc
> ---
> Please help!
> Tao|||Such a dataset would fail:
(pubs database)
SELECT au_lname
FROM authors
WHERE (au_id IN
(SELECT au_id
FROM titleauthor
WHERE title_id = @.title))
Error message: "Parameter Information cannot be derived from SQL statements
with sub-select queries. Set parameter information before preparing command."
But the parametere exists under Layout/Reports/Report Parameters and the
parameter mapping is there in the Dataset/Edit/Parameters tab:
@.title =Parameters!title.Value
Can some one try this out to see if you can succeed?
Thanks,
Tao
"Bruce Loehle-Conger" wrote:
> Make sure that you have two report parameters. In layout, menu
> reports->report parameter. Next go to your dataset, ... , parameters tab and
> make sure that each of your query parameters are mapped to a report
> parameter. Being a subquery or not really doesn't matter.
> Bruce L-C
> "Tao Zuo" <Tao Zuo@.discussions.microsoft.com> wrote in message
> news:69F8B719-B789-4DFC-8D6E-20ECD6430B7E@.microsoft.com...
> > Hi All,
> >
> > There are two parameters in our DataSet based on the query below.
> >
> > @.Year causes "syntax or access violation" error, because it's in subquery.
> > @.Parameter2 works (in the main query)
> > ----
> > SELECT *,
> > ( CASE WHEN Av_MTD >= ISNULL(Av_Comm, 0)
> > THEN 'Green'
> > WHEN Av_MTD >= (Av_Comm) * .9 THEN 'Yellow'
> > WHEN Av_MTD < ISNULL(Av_Comm,0)
> > THEN 'Red' ELSE 'Black'
> > END) AS Avail_color_MTD,
> > ( CASE WHEN Re_MTD <= ISNULL(Re_Comm, 0)
> > THEN 'Green' WHEN Re_MTD <= (Re_Comm) * 1.1
> > THEN 'Yellow' WHEN Re_MTD > ISNULL(Re_Comm, 0)
> > THEN 'Red' ELSE 'Black' END
> > ) AS Resp_color_MTD
> > FROM (
> > SELECT
> > Sla_Name_By_Loc,
> > MAX(Sla_Definition) AS Sla_Definition,
> > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%' THEN
> > Sla_Commitment_By_Loc END) AS Av_Comm,
> > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%' THEN
> > Sla_Commitment_By_Loc END) AS Re_Comm,
> > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%' THEN
> > Sla_Result_by_loc END) AS Av_MTD,
> > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%' THEN
> > Sla_Result_by_loc END) AS Re_MTD
> > FROM
> > Sla_Results_Trans_BY_LOC
> > WHERE (Sla_Category_id_By_Loc = 2)
> > AND (Sla_Year_By_Loc = @.Year) -- Syntax Error Or Access Violation
> > AND (Sla_Month_by_loc = 9)
> > AND (Sla_Pete_Clients_Code_by_loc = 'NJ01')
> > GROUP BY
> > Sla_Name_By_Loc) Y
> > where Y.Re_Comm = @.Parameter2 -- works!
> > ORDER BY Sla_Name_By_Loc
> > ---
> >
> > Please help!
> >
> > Tao
>
>|||Got the solution for you. If you use the generic query instead (click on the
button next to the !) then it will work. I think you have come across a
limitation of the graphical designer to deal with subqueries and parameters.
Works just the way you want from the query designer.
Bruce L-C [MVP Reporting Services]
"Tao Zuo" <TaoZuo@.discussions.microsoft.com> wrote in message
news:0E099BE5-CD05-4B94-BD2C-EB9F0DB5D908@.microsoft.com...
> Such a dataset would fail:
> (pubs database)
> SELECT au_lname
> FROM authors
> WHERE (au_id IN
> (SELECT au_id
> FROM titleauthor
> WHERE title_id = @.title))
> Error message: "Parameter Information cannot be derived from SQL
statements
> with sub-select queries. Set parameter information before preparing
command."
> But the parametere exists under Layout/Reports/Report Parameters and the
> parameter mapping is there in the Dataset/Edit/Parameters tab:
> @.title =Parameters!title.Value
> Can some one try this out to see if you can succeed?
> Thanks,
> Tao
> "Bruce Loehle-Conger" wrote:
> > Make sure that you have two report parameters. In layout, menu
> > reports->report parameter. Next go to your dataset, ... , parameters tab
and
> > make sure that each of your query parameters are mapped to a report
> > parameter. Being a subquery or not really doesn't matter.
> >
> > Bruce L-C
> >
> > "Tao Zuo" <Tao Zuo@.discussions.microsoft.com> wrote in message
> > news:69F8B719-B789-4DFC-8D6E-20ECD6430B7E@.microsoft.com...
> > > Hi All,
> > >
> > > There are two parameters in our DataSet based on the query below.
> > >
> > > @.Year causes "syntax or access violation" error, because it's in
subquery.
> > > @.Parameter2 works (in the main query)
> > > ----
> > > SELECT *,
> > > ( CASE WHEN Av_MTD >= ISNULL(Av_Comm, 0)
> > > THEN 'Green'
> > > WHEN Av_MTD >= (Av_Comm) * .9 THEN 'Yellow'
> > > WHEN Av_MTD < ISNULL(Av_Comm,0)
> > > THEN 'Red' ELSE 'Black'
> > > END) AS Avail_color_MTD,
> > > ( CASE WHEN Re_MTD <= ISNULL(Re_Comm, 0)
> > > THEN 'Green' WHEN Re_MTD <= (Re_Comm) * 1.1
> > > THEN 'Yellow' WHEN Re_MTD > ISNULL(Re_Comm, 0)
> > > THEN 'Red' ELSE 'Black' END
> > > ) AS Resp_color_MTD
> > > FROM (
> > > SELECT
> > > Sla_Name_By_Loc,
> > > MAX(Sla_Definition) AS Sla_Definition,
> > > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%'
THEN
> > > Sla_Commitment_By_Loc END) AS Av_Comm,
> > > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%'
THEN
> > > Sla_Commitment_By_Loc END) AS Re_Comm,
> > > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%'
THEN
> > > Sla_Result_by_loc END) AS Av_MTD,
> > > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%'
THEN
> > > Sla_Result_by_loc END) AS Re_MTD
> > > FROM
> > > Sla_Results_Trans_BY_LOC
> > > WHERE (Sla_Category_id_By_Loc = 2)
> > > AND (Sla_Year_By_Loc = @.Year) -- Syntax Error Or Access Violation
> > > AND (Sla_Month_by_loc = 9)
> > > AND (Sla_Pete_Clients_Code_by_loc = 'NJ01')
> > > GROUP BY
> > > Sla_Name_By_Loc) Y
> > > where Y.Re_Comm = @.Parameter2 -- works!
> > > ORDER BY Sla_Name_By_Loc
> > > ---
> > >
> > > Please help!
> > >
> > > Tao
> >
> >
> >|||I am having the same problem.
I have selected the Generic Query pane and it let me then enter a parameter
value.
It ran but showed no data. The field names were shown but no data was shown.
Any help would be appreciated. Thank you.
"Bruce Loehle-Conger [MVP]" wrote:
> Got the solution for you. If you use the generic query instead (click on the
> button next to the !) then it will work. I think you have come across a
> limitation of the graphical designer to deal with subqueries and parameters.
> Works just the way you want from the query designer.
> Bruce L-C [MVP Reporting Services]
> "Tao Zuo" <TaoZuo@.discussions.microsoft.com> wrote in message
> news:0E099BE5-CD05-4B94-BD2C-EB9F0DB5D908@.microsoft.com...
> > Such a dataset would fail:
> >
> > (pubs database)
> > SELECT au_lname
> > FROM authors
> > WHERE (au_id IN
> > (SELECT au_id
> > FROM titleauthor
> > WHERE title_id = @.title))
> >
> > Error message: "Parameter Information cannot be derived from SQL
> statements
> > with sub-select queries. Set parameter information before preparing
> command."
> >
> > But the parametere exists under Layout/Reports/Report Parameters and the
> > parameter mapping is there in the Dataset/Edit/Parameters tab:
> > @.title =Parameters!title.Value
> >
> > Can some one try this out to see if you can succeed?
> >
> > Thanks,
> >
> > Tao
> >
> > "Bruce Loehle-Conger" wrote:
> >
> > > Make sure that you have two report parameters. In layout, menu
> > > reports->report parameter. Next go to your dataset, ... , parameters tab
> and
> > > make sure that each of your query parameters are mapped to a report
> > > parameter. Being a subquery or not really doesn't matter.
> > >
> > > Bruce L-C
> > >
> > > "Tao Zuo" <Tao Zuo@.discussions.microsoft.com> wrote in message
> > > news:69F8B719-B789-4DFC-8D6E-20ECD6430B7E@.microsoft.com...
> > > > Hi All,
> > > >
> > > > There are two parameters in our DataSet based on the query below.
> > > >
> > > > @.Year causes "syntax or access violation" error, because it's in
> subquery.
> > > > @.Parameter2 works (in the main query)
> > > > ----
> > > > SELECT *,
> > > > ( CASE WHEN Av_MTD >= ISNULL(Av_Comm, 0)
> > > > THEN 'Green'
> > > > WHEN Av_MTD >= (Av_Comm) * .9 THEN 'Yellow'
> > > > WHEN Av_MTD < ISNULL(Av_Comm,0)
> > > > THEN 'Red' ELSE 'Black'
> > > > END) AS Avail_color_MTD,
> > > > ( CASE WHEN Re_MTD <= ISNULL(Re_Comm, 0)
> > > > THEN 'Green' WHEN Re_MTD <= (Re_Comm) * 1.1
> > > > THEN 'Yellow' WHEN Re_MTD > ISNULL(Re_Comm, 0)
> > > > THEN 'Red' ELSE 'Black' END
> > > > ) AS Resp_color_MTD
> > > > FROM (
> > > > SELECT
> > > > Sla_Name_By_Loc,
> > > > MAX(Sla_Definition) AS Sla_Definition,
> > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%'
> THEN
> > > > Sla_Commitment_By_Loc END) AS Av_Comm,
> > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%'
> THEN
> > > > Sla_Commitment_By_Loc END) AS Re_Comm,
> > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%'
> THEN
> > > > Sla_Result_by_loc END) AS Av_MTD,
> > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%'
> THEN
> > > > Sla_Result_by_loc END) AS Re_MTD
> > > > FROM
> > > > Sla_Results_Trans_BY_LOC
> > > > WHERE (Sla_Category_id_By_Loc = 2)
> > > > AND (Sla_Year_By_Loc = @.Year) -- Syntax Error Or Access Violation
> > > > AND (Sla_Month_by_loc = 9)
> > > > AND (Sla_Pete_Clients_Code_by_loc = 'NJ01')
> > > > GROUP BY
> > > > Sla_Name_By_Loc) Y
> > > > where Y.Re_Comm = @.Parameter2 -- works!
> > > > ORDER BY Sla_Name_By_Loc
> > > > ---
> > > >
> > > > Please help!
> > > >
> > > > Tao
> > >
> > >
> > >
>
>|||Not the same problem. He was getting an error when trying to execute it. If
you are getting the field list back then that means that it is executing
correctly. Most likely something is not being specified correctly for your
parameter when you are prompted. What database are you going against (SQL
Server 2000?), what is the datatype of the field you are querying against?
How are you putting in the value when prompted for the parameter when you
execute it from the generic query pane.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"wilerwin" <wilerwin@.discussions.microsoft.com> wrote in message
news:514A5CDE-79F4-47F0-AB3B-EDAB30A19895@.microsoft.com...
> I am having the same problem.
> I have selected the Generic Query pane and it let me then enter a
parameter
> value.
> It ran but showed no data. The field names were shown but no data was
shown.
> Any help would be appreciated. Thank you.
> "Bruce Loehle-Conger [MVP]" wrote:
> > Got the solution for you. If you use the generic query instead (click on
the
> > button next to the !) then it will work. I think you have come across a
> > limitation of the graphical designer to deal with subqueries and
parameters.
> > Works just the way you want from the query designer.
> >
> > Bruce L-C [MVP Reporting Services]
> >
> > "Tao Zuo" <TaoZuo@.discussions.microsoft.com> wrote in message
> > news:0E099BE5-CD05-4B94-BD2C-EB9F0DB5D908@.microsoft.com...
> > > Such a dataset would fail:
> > >
> > > (pubs database)
> > > SELECT au_lname
> > > FROM authors
> > > WHERE (au_id IN
> > > (SELECT au_id
> > > FROM titleauthor
> > > WHERE title_id = @.title))
> > >
> > > Error message: "Parameter Information cannot be derived from SQL
> > statements
> > > with sub-select queries. Set parameter information before preparing
> > command."
> > >
> > > But the parametere exists under Layout/Reports/Report Parameters and
the
> > > parameter mapping is there in the Dataset/Edit/Parameters tab:
> > > @.title =Parameters!title.Value
> > >
> > > Can some one try this out to see if you can succeed?
> > >
> > > Thanks,
> > >
> > > Tao
> > >
> > > "Bruce Loehle-Conger" wrote:
> > >
> > > > Make sure that you have two report parameters. In layout, menu
> > > > reports->report parameter. Next go to your dataset, ... , parameters
tab
> > and
> > > > make sure that each of your query parameters are mapped to a report
> > > > parameter. Being a subquery or not really doesn't matter.
> > > >
> > > > Bruce L-C
> > > >
> > > > "Tao Zuo" <Tao Zuo@.discussions.microsoft.com> wrote in message
> > > > news:69F8B719-B789-4DFC-8D6E-20ECD6430B7E@.microsoft.com...
> > > > > Hi All,
> > > > >
> > > > > There are two parameters in our DataSet based on the query below.
> > > > >
> > > > > @.Year causes "syntax or access violation" error, because it's in
> > subquery.
> > > > > @.Parameter2 works (in the main query)
> > > > > ----
> > > > > SELECT *,
> > > > > ( CASE WHEN Av_MTD >= ISNULL(Av_Comm, 0)
> > > > > THEN 'Green'
> > > > > WHEN Av_MTD >= (Av_Comm) * .9 THEN 'Yellow'
> > > > > WHEN Av_MTD < ISNULL(Av_Comm,0)
> > > > > THEN 'Red' ELSE 'Black'
> > > > > END) AS Avail_color_MTD,
> > > > > ( CASE WHEN Re_MTD <= ISNULL(Re_Comm, 0)
> > > > > THEN 'Green' WHEN Re_MTD <= (Re_Comm) * 1.1
> > > > > THEN 'Yellow' WHEN Re_MTD > ISNULL(Re_Comm, 0)
> > > > > THEN 'Red' ELSE 'Black' END
> > > > > ) AS Resp_color_MTD
> > > > > FROM (
> > > > > SELECT
> > > > > Sla_Name_By_Loc,
> > > > > MAX(Sla_Definition) AS Sla_Definition,
> > > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE
'Avail%'
> > THEN
> > > > > Sla_Commitment_By_Loc END) AS Av_Comm,
> > > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE
'Respo%'
> > THEN
> > > > > Sla_Commitment_By_Loc END) AS Re_Comm,
> > > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE
'Avail%'
> > THEN
> > > > > Sla_Result_by_loc END) AS Av_MTD,
> > > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE
'Respo%'
> > THEN
> > > > > Sla_Result_by_loc END) AS Re_MTD
> > > > > FROM
> > > > > Sla_Results_Trans_BY_LOC
> > > > > WHERE (Sla_Category_id_By_Loc = 2)
> > > > > AND (Sla_Year_By_Loc = @.Year) -- Syntax Error Or Access Violation
> > > > > AND (Sla_Month_by_loc = 9)
> > > > > AND (Sla_Pete_Clients_Code_by_loc = 'NJ01')
> > > > > GROUP BY
> > > > > Sla_Name_By_Loc) Y
> > > > > where Y.Re_Comm = @.Parameter2 -- works!
> > > > > ORDER BY Sla_Name_By_Loc
> > > > > ---
> > > > >
> > > > > Please help!
> > > > >
> > > > > Tao
> > > >
> > > >
> > > >
> >
> >
> >|||Thank you very much for responding.
Problem fixed. When I clicked back from the Generic SQL Query and added more
fields, my query formatting changed somehow. Now it works great.
Thanks alot.
"Bruce L-C [MVP]" wrote:
> Not the same problem. He was getting an error when trying to execute it. If
> you are getting the field list back then that means that it is executing
> correctly. Most likely something is not being specified correctly for your
> parameter when you are prompted. What database are you going against (SQL
> Server 2000?), what is the datatype of the field you are querying against?
> How are you putting in the value when prompted for the parameter when you
> execute it from the generic query pane.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "wilerwin" <wilerwin@.discussions.microsoft.com> wrote in message
> news:514A5CDE-79F4-47F0-AB3B-EDAB30A19895@.microsoft.com...
> > I am having the same problem.
> > I have selected the Generic Query pane and it let me then enter a
> parameter
> > value.
> > It ran but showed no data. The field names were shown but no data was
> shown.
> > Any help would be appreciated. Thank you.
> >
> > "Bruce Loehle-Conger [MVP]" wrote:
> >
> > > Got the solution for you. If you use the generic query instead (click on
> the
> > > button next to the !) then it will work. I think you have come across a
> > > limitation of the graphical designer to deal with subqueries and
> parameters.
> > > Works just the way you want from the query designer.
> > >
> > > Bruce L-C [MVP Reporting Services]
> > >
> > > "Tao Zuo" <TaoZuo@.discussions.microsoft.com> wrote in message
> > > news:0E099BE5-CD05-4B94-BD2C-EB9F0DB5D908@.microsoft.com...
> > > > Such a dataset would fail:
> > > >
> > > > (pubs database)
> > > > SELECT au_lname
> > > > FROM authors
> > > > WHERE (au_id IN
> > > > (SELECT au_id
> > > > FROM titleauthor
> > > > WHERE title_id = @.title))
> > > >
> > > > Error message: "Parameter Information cannot be derived from SQL
> > > statements
> > > > with sub-select queries. Set parameter information before preparing
> > > command."
> > > >
> > > > But the parametere exists under Layout/Reports/Report Parameters and
> the
> > > > parameter mapping is there in the Dataset/Edit/Parameters tab:
> > > > @.title =Parameters!title.Value
> > > >
> > > > Can some one try this out to see if you can succeed?
> > > >
> > > > Thanks,
> > > >
> > > > Tao
> > > >
> > > > "Bruce Loehle-Conger" wrote:
> > > >
> > > > > Make sure that you have two report parameters. In layout, menu
> > > > > reports->report parameter. Next go to your dataset, ... , parameters
> tab
> > > and
> > > > > make sure that each of your query parameters are mapped to a report
> > > > > parameter. Being a subquery or not really doesn't matter.
> > > > >
> > > > > Bruce L-C
> > > > >
> > > > > "Tao Zuo" <Tao Zuo@.discussions.microsoft.com> wrote in message
> > > > > news:69F8B719-B789-4DFC-8D6E-20ECD6430B7E@.microsoft.com...
> > > > > > Hi All,
> > > > > >
> > > > > > There are two parameters in our DataSet based on the query below.
> > > > > >
> > > > > > @.Year causes "syntax or access violation" error, because it's in
> > > subquery.
> > > > > > @.Parameter2 works (in the main query)
> > > > > > ----
> > > > > > SELECT *,
> > > > > > ( CASE WHEN Av_MTD >= ISNULL(Av_Comm, 0)
> > > > > > THEN 'Green'
> > > > > > WHEN Av_MTD >= (Av_Comm) * .9 THEN 'Yellow'
> > > > > > WHEN Av_MTD < ISNULL(Av_Comm,0)
> > > > > > THEN 'Red' ELSE 'Black'
> > > > > > END) AS Avail_color_MTD,
> > > > > > ( CASE WHEN Re_MTD <= ISNULL(Re_Comm, 0)
> > > > > > THEN 'Green' WHEN Re_MTD <= (Re_Comm) * 1.1
> > > > > > THEN 'Yellow' WHEN Re_MTD > ISNULL(Re_Comm, 0)
> > > > > > THEN 'Red' ELSE 'Black' END
> > > > > > ) AS Resp_color_MTD
> > > > > > FROM (
> > > > > > SELECT
> > > > > > Sla_Name_By_Loc,
> > > > > > MAX(Sla_Definition) AS Sla_Definition,
> > > > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE
> 'Avail%'
> > > THEN
> > > > > > Sla_Commitment_By_Loc END) AS Av_Comm,
> > > > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE
> 'Respo%'
> > > THEN
> > > > > > Sla_Commitment_By_Loc END) AS Re_Comm,
> > > > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE
> 'Avail%'
> > > THEN
> > > > > > Sla_Result_by_loc END) AS Av_MTD,
> > > > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE
> 'Respo%'
> > > THEN
> > > > > > Sla_Result_by_loc END) AS Re_MTD
> > > > > > FROM
> > > > > > Sla_Results_Trans_BY_LOC
> > > > > > WHERE (Sla_Category_id_By_Loc = 2)
> > > > > > AND (Sla_Year_By_Loc = @.Year) -- Syntax Error Or Access Violation
> > > > > > AND (Sla_Month_by_loc = 9)
> > > > > > AND (Sla_Pete_Clients_Code_by_loc = 'NJ01')
> > > > > > GROUP BY
> > > > > > Sla_Name_By_Loc) Y
> > > > > > where Y.Re_Comm = @.Parameter2 -- works!
> > > > > > ORDER BY Sla_Name_By_Loc
> > > > > > ---
> > > > > >
> > > > > > Please help!
> > > > > >
> > > > > > Tao
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>

Report Parameter As CheckBox

Hello Sir,
I m working on Microsoft SQL reporting service tool 2005.
I want to know how can display a checkbox on report parameter form
which is automatilcally build by reporting service tool.
Means i want parameter field as checkbox.
I have a query :
select name from employeesTbl
where Sex = @.ParameterValue
User select designation from multiple CheckBox.
and i think remeber i want to know also that user select only one
checkbox at a time.
There may be number of checkboxes for this.
e.g : Male,Female,OtherOn Jun 21, 2:34 am, Dheeraj <dheeraj.dhiman.daffo...@.gmail.com> wrote:
> Hello Sir,
> I m working on Microsoft SQL reporting service tool 2005.
> I want to know how can display a checkbox on report parameter form
> which is automatilcally build by reporting service tool.
> Means i want parameter field as checkbox.
> I have a query :
> select name from employeesTbl
> where Sex = @.ParameterValue
> User select designation from multiple CheckBox.
> and i think remeber i want to know also that user select only one
> checkbox at a time.
> There may be number of checkboxes for this.
> e.g : Male,Female,Other
If I'm understanding you correctly, you will only be able to use a
checkbox as part of a multi-select parameter. Is there a particular
reason why you can't use the standard drop-down list for the parameter
selections?
Enrique Martinez
Sr. Software Consultant

report pack for iis - report generation taking long?

I've got this installed and the sample database was working fine. I then
imported about a weeks worth from two iis servers (grew the DB to about 5
GB) and now pulling up any of the default 12 reports takes at least 30
minutes to an hour or longer. Is this normal? The SQL server is a dual
xeon, 2 GB of RAM, 3 disk (15k rpm) raid 5 with SQL 2000 SP3 and Reporting
Services SP2.Try to do a select of the same data and see how long it takes...RS will be
slower than that of course - but will not be faster than your select when it
queries the data..
Ensure you have properly indexed tables.
You may also wish to run the query on a schedule and deliver it from cache -
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Joe" <joe@.do.not.email.me> wrote in message
news:%23%234FpU4qFHA.248@.TK2MSFTNGP14.phx.gbl...
> I've got this installed and the sample database was working fine. I then
> imported about a weeks worth from two iis servers (grew the DB to about 5
> GB) and now pulling up any of the default 12 reports takes at least 30
> minutes to an hour or longer. Is this normal? The SQL server is a dual
> xeon, 2 GB of RAM, 3 disk (15k rpm) raid 5 with SQL 2000 SP3 and Reporting
> Services SP2.
>

report not running

hi all

i am working on SQL Reporting 2005.

after clicking on report name in report manager or report server the report is not generated .

The internet explorer shows that "opening page ......." in task bar but actually report is not generated .

However same report runs in preview of SQL server BI development studio.

also the same report runs when deployed in some other machine.

plz help me.

Do you get an error of some sort in IE? Check the log file and event log. Anything identifies the error? Start with your report server.|||

i don't get any kind of error message.

In the task bar IE shows message "opening page..." and also shows the progress bar for loading page but i can only see the page without anythinh on it. Also the progress bar doesn't disappear. i think its sort of infinite loop where IE is trying several times to load that page.

plz help me.

Report Manager: Unable to communicate with report server.

Hello,
I'm using forms authentication with Reporting Services for integration
within an application. The integration is working -- I'm able to
access reports server through URL access from the app, and any
attempts to access reports server before authenticating redirect the
user to the login page.
My problem is that Report Manager is not working. Any attempt to
access it yields the "Unable to communicate with report server. Please
verify that the report server is operational".
I've seen lots of references in posts to the error that I'm receiving
but none that have solved the problem for me. Most threads indicate
that it is a configuration file error.
I have gone over the MSDN article
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ufairs.asp)
tens of times to make sure I performed all the steps and to ensure
that all my configuration settings are correct.
All of my configuration settings for the reports server URL matches up
(and call the machine name not 'localhost').
Reports server seems to be working correctly. I can access it
directly from the URL. I'm also confident that my authentication
extension is working. When attempting to login to Report Manager, I
have verified that the 'LogonUser' web service call is being made and
returns true. The authentication ticket is being forwarded back to
the client browser.
Something must be configured incorrectly in Report Manager but there
are not that many settings and I've checked them all dozens of times!
;-)
One difference between my implementation and the sample is that I'm
using SSL to access the reports server everywhere. I'm assuming that
this would not cause problems for Report Manager. Does it?
I'd appreciate any help. Anyone have any ideas? Attached is the
trace of the error message from Report Manager's log.
Thanks,
Stefan
--
w3wp!ui!5a4!11/12/2004-18:09:20:: e ERROR: Unable to communicate with
report server. Please verify that the report server is operational.
w3wp!ui!5a4!11/12/2004-18:09:21:: e ERROR: HTTP status code --> 500
--Details--
CantCommunicateWithReportServerException: Unable to communicate with
report server. Please verify that the report server is operational.
at Microsoft.ReportingServices.UI.RSWebServiceWrapper.GetSecureMethods()
at Microsoft.SqlServer.ReportingServices.RSConnection.IsSecureMethod(String
methodname)
at Microsoft.ReportingServices.UI.Global.SecureAllAPI()
at Microsoft.ReportingServices.UI.ReportingPage.EnsureHttpsLevel(HttpsLevel
level)
at Microsoft.ReportingServices.UI.ReportingPage.ReportingPage_Init(Object
sender, EventArgs args)
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Web.UI.Control.OnInit(EventArgs e)
at System.Web.UI.Control.InitRecursive(Control namingContainer)
at System.Web.UI.Page.ProcessRequestMain()
w3wp!ui!5a4!11/12/2004-18:09:23:: e ERROR: Exception in ShowErrorPage:
System.Threading.ThreadAbortException: Thread was being aborted.
at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at System.Web.HttpServerUtility.Transfer(String path, Boolean
preserveForm)
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String
errMsg) at at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at System.Web.HttpServerUtility.Transfer(String path, Boolean
preserveForm)
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String
errMsg)I had the same problem but when I commented the <deny users="?"> line in the
web.config under the ReportServer folder, I was able to logon onto the
Report Manager using the same Forms auth. But now, the reports will not
work. If I undo the change, we are back to square one. Let me know if you
find a solution to this one.
DC
"Stefan Leyhane" <sleyhane@.gmail.com> wrote in message
news:b3f9a06e.0411121551.2a1e3381@.posting.google.com...
> Hello,
> I'm using forms authentication with Reporting Services for integration
> within an application. The integration is working -- I'm able to
> access reports server through URL access from the app, and any
> attempts to access reports server before authenticating redirect the
> user to the login page.
> My problem is that Report Manager is not working. Any attempt to
> access it yields the "Unable to communicate with report server. Please
> verify that the report server is operational".
> I've seen lots of references in posts to the error that I'm receiving
> but none that have solved the problem for me. Most threads indicate
> that it is a configuration file error.
> I have gone over the MSDN article
>
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/ht
ml/ufairs.asp)
> tens of times to make sure I performed all the steps and to ensure
> that all my configuration settings are correct.
> All of my configuration settings for the reports server URL matches up
> (and call the machine name not 'localhost').
> Reports server seems to be working correctly. I can access it
> directly from the URL. I'm also confident that my authentication
> extension is working. When attempting to login to Report Manager, I
> have verified that the 'LogonUser' web service call is being made and
> returns true. The authentication ticket is being forwarded back to
> the client browser.
> Something must be configured incorrectly in Report Manager but there
> are not that many settings and I've checked them all dozens of times!
> ;-)
> One difference between my implementation and the sample is that I'm
> using SSL to access the reports server everywhere. I'm assuming that
> this would not cause problems for Report Manager. Does it?
> I'd appreciate any help. Anyone have any ideas? Attached is the
> trace of the error message from Report Manager's log.
> Thanks,
> Stefan
> --
> w3wp!ui!5a4!11/12/2004-18:09:20:: e ERROR: Unable to communicate with
> report server. Please verify that the report server is operational.
> w3wp!ui!5a4!11/12/2004-18:09:21:: e ERROR: HTTP status code --> 500
> --Details--
> CantCommunicateWithReportServerException: Unable to communicate with
> report server. Please verify that the report server is operational.
> at
Microsoft.ReportingServices.UI.RSWebServiceWrapper.GetSecureMethods()
> at
Microsoft.SqlServer.ReportingServices.RSConnection.IsSecureMethod(String
> methodname)
> at Microsoft.ReportingServices.UI.Global.SecureAllAPI()
> at
Microsoft.ReportingServices.UI.ReportingPage.EnsureHttpsLevel(HttpsLevel
> level)
> at
Microsoft.ReportingServices.UI.ReportingPage.ReportingPage_Init(Object
> sender, EventArgs args)
> at System.EventHandler.Invoke(Object sender, EventArgs e)
> at System.Web.UI.Control.OnInit(EventArgs e)
> at System.Web.UI.Control.InitRecursive(Control namingContainer)
> at System.Web.UI.Page.ProcessRequestMain()
> w3wp!ui!5a4!11/12/2004-18:09:23:: e ERROR: Exception in ShowErrorPage:
> System.Threading.ThreadAbortException: Thread was being aborted.
> at System.Threading.Thread.AbortInternal()
> at System.Threading.Thread.Abort(Object stateInfo)
> at System.Web.HttpResponse.End()
> at System.Web.HttpServerUtility.Transfer(String path, Boolean
> preserveForm)
> at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String
> errMsg) at at System.Threading.Thread.AbortInternal()
> at System.Threading.Thread.Abort(Object stateInfo)
> at System.Web.HttpResponse.End()
> at System.Web.HttpServerUtility.Transfer(String path, Boolean
> preserveForm)
> at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String
> errMsg)|||DC,
I saw your posting from November. Did you ever find a solution for
this? I have the same issue and it's driving me nuts. Every time I
want to use ReportManager I have to comment out the
deny user=? line in the web config. I assume this is due to my using
forms authentication and messing up a config file somehow...
Can any Forma auth users shed some light on this one?
thanks,
Vince
Thomson Delmar Learning
DC wrote:
> I had the same problem but when I commented the <deny users="?"> line
in the
> web.config under the ReportServer folder, I was able to logon onto
the
> Report Manager using the same Forms auth. But now, the reports will
not
> work. If I undo the change, we are back to square one. Let me know if
you
> find a solution to this one.
> DC
>
> "Stefan Leyhane" <sleyhane@.gmail.com> wrote in message
> news:b3f9a06e.0411121551.2a1e3381@.posting.google.com...
> > Hello,
> >
> > I'm using forms authentication with Reporting Services for
integration
> > within an application. The integration is working -- I'm able to
> > access reports server through URL access from the app, and any
> > attempts to access reports server before authenticating redirect
the
> > user to the login page.
> >
> > My problem is that Report Manager is not working. Any attempt to
> > access it yields the "Unable to communicate with report server.
Please
> > verify that the report server is operational".
> >
> > I've seen lots of references in posts to the error that I'm
receiving
> > but none that have solved the problem for me. Most threads
indicate
> > that it is a configuration file error.
> >
> > I have gone over the MSDN article
> >
>
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/ht
> ml/ufairs.asp)
> > tens of times to make sure I performed all the steps and to ensure
> > that all my configuration settings are correct.
> >
> > All of my configuration settings for the reports server URL matches
up
> > (and call the machine name not 'localhost').
> >
> > Reports server seems to be working correctly. I can access it
> > directly from the URL. I'm also confident that my authentication
> > extension is working. When attempting to login to Report Manager,
I
> > have verified that the 'LogonUser' web service call is being made
and
> > returns true. The authentication ticket is being forwarded back to
> > the client browser.
> >
> > Something must be configured incorrectly in Report Manager but
there
> > are not that many settings and I've checked them all dozens of
times!
> > ;-)
> >
> > One difference between my implementation and the sample is that I'm
> > using SSL to access the reports server everywhere. I'm assuming
that
> > this would not cause problems for Report Manager. Does it?
> >
> > I'd appreciate any help. Anyone have any ideas? Attached is the
> > trace of the error message from Report Manager's log.
> >
> > Thanks,
> >
> > Stefan
> >
> > --
> > w3wp!ui!5a4!11/12/2004-18:09:20:: e ERROR: Unable to communicate
with
> > report server. Please verify that the report server is
operational.
> > w3wp!ui!5a4!11/12/2004-18:09:21:: e ERROR: HTTP status code --> 500
> > --Details--
> > CantCommunicateWithReportServerException: Unable to communicate
with
> > report server. Please verify that the report server is
operational.
> >
> > at
> Microsoft.ReportingServices.UI.RSWebServiceWrapper.GetSecureMethods()
> > at
>
Microsoft.SqlServer.ReportingServices.RSConnection.IsSecureMethod(String
> > methodname)
> > at Microsoft.ReportingServices.UI.Global.SecureAllAPI()
> > at
>
Microsoft.ReportingServices.UI.ReportingPage.EnsureHttpsLevel(HttpsLevel
> > level)
> > at
>
Microsoft.ReportingServices.UI.ReportingPage.ReportingPage_Init(Object
> > sender, EventArgs args)
> > at System.EventHandler.Invoke(Object sender, EventArgs e)
> > at System.Web.UI.Control.OnInit(EventArgs e)
> > at System.Web.UI.Control.InitRecursive(Control namingContainer)
> > at System.Web.UI.Page.ProcessRequestMain()
> > w3wp!ui!5a4!11/12/2004-18:09:23:: e ERROR: Exception in
ShowErrorPage:
> > System.Threading.ThreadAbortException: Thread was being aborted.
> > at System.Threading.Thread.AbortInternal()
> > at System.Threading.Thread.Abort(Object stateInfo)
> > at System.Web.HttpResponse.End()
> > at System.Web.HttpServerUtility.Transfer(String path, Boolean
> > preserveForm)
> > at
Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String
> > errMsg) at at System.Threading.Thread.AbortInternal()
> > at System.Threading.Thread.Abort(Object stateInfo)
> > at System.Web.HttpResponse.End()
> > at System.Web.HttpServerUtility.Transfer(String path, Boolean
> > preserveForm)
> > at
Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String
> > errMsg)|||I was able to find a workaround to the problem.
To get over the error that I had reported to you, I had to add the
following element to the ReportServer web.config file:
<!-- Allow anonymous access to the web service. Report Manager seems
to require this. -->
<location path="ReportService.asmx">
<system.web>
<authorization>
<allow users="?" />
</authorization>
</system.web>
</location>
It seems that Report Manager makes a call to the Report Server web
service on initialization. With only the "<deny users="?" />" rule
that was specified in the MSDN article
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ufairs.asp),
Report Manager was not authorized to contact the web service.
I worked with Microsoft partner services but I wasn't able to get
confirmation that my assumption was correct. My support contact was
not able to duplicate the problem using the sample forms authentication
app.
Good luck,
Stefan Leyhane
Firmwater Inc.|||Thanks! I really appreciate your tip- this problem was really
bothering me. I thought I must have messed up a config file- I guess I
put to much trust in that Microsoft Whitepaper
having complete directions. If you're listening, MS, please add this
to the Whitepaper. I was on the verge of giving up on Report
Services...
sleyhane@.gmail.com wrote:
> I was able to find a workaround to the problem.
> To get over the error that I had reported to you, I had to add the
> following element to the ReportServer web.config file:
> <!-- Allow anonymous access to the web service. Report Manager seems
> to require this. -->
> <location path="ReportService.asmx">
> <system.web>
> <authorization>
> <allow users="?" />
> </authorization>
> </system.web>
> </location>
> It seems that Report Manager makes a call to the Report Server web
> service on initialization. With only the "<deny users="?" />" rule
> that was specified in the MSDN article
>
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ufairs.asp),
> Report Manager was not authorized to contact the web service.
> I worked with Microsoft partner services but I wasn't able to get
> confirmation that my assumption was correct. My support contact was
> not able to duplicate the problem using the sample forms
authentication
> app.
> Good luck,
> Stefan Leyhane
> Firmwater Inc.|||Where in the nesting of the web.config did you place this additional
configuration information?
Also, are you using IP address or machine name when specifying the
ReportServerUrl in rsWebApp.config for ReportManager?
Thanks,
Jessica
"sleyhane@.gmail.com" wrote:
> I was able to find a workaround to the problem.
> To get over the error that I had reported to you, I had to add the
> following element to the ReportServer web.config file:
> <!-- Allow anonymous access to the web service. Report Manager seems
> to require this. -->
> <location path="ReportService.asmx">
> <system.web>
> <authorization>
> <allow users="?" />
> </authorization>
> </system.web>
> </location>
> It seems that Report Manager makes a call to the Report Server web
> service on initialization. With only the "<deny users="?" />" rule
> that was specified in the MSDN article
> (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ufairs.asp),
> Report Manager was not authorized to contact the web service.
> I worked with Microsoft partner services but I wasn't able to get
> confirmation that my assumption was correct. My support contact was
> not able to duplicate the problem using the sample forms authentication
> app.
> Good luck,
> Stefan Leyhane
> Firmwater Inc.
>|||Hello Guys AoA!
The only thing i had to do is as follows:
1) Open configuration file as C:\Program Files\Microsoft SQL
Server\MSSQL\Reporting Services\ReportManager\RSWebApplication.config in
NotePad.Exe
2) ReportingServices by default install as :
<UI>
<ReportServerUrl>
http://localhost/ReportServer
</ReportServerUrl>
</UI>
3) Check about your directory structure , replace it if it is different from
yours.
4) Save and browse for report manager it should work now.
5) Best of luck
Thanks, Regards and AH
--
Allah Pakistan Ki Hifazat Farmay
"Jessica Landisman" wrote:
> Where in the nesting of the web.config did you place this additional
> configuration information?
> Also, are you using IP address or machine name when specifying the
> ReportServerUrl in rsWebApp.config for ReportManager?
> Thanks,
> Jessica
> "sleyhane@.gmail.com" wrote:
> > I was able to find a workaround to the problem.
> >
> > To get over the error that I had reported to you, I had to add the
> > following element to the ReportServer web.config file:
> >
> > <!-- Allow anonymous access to the web service. Report Manager seems
> > to require this. -->
> > <location path="ReportService.asmx">
> > <system.web>
> > <authorization>
> > <allow users="?" />
> > </authorization>
> > </system.web>
> > </location>
> >
> > It seems that Report Manager makes a call to the Report Server web
> > service on initialization. With only the "<deny users="?" />" rule
> > that was specified in the MSDN article
> > (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ufairs.asp),
> > Report Manager was not authorized to contact the web service.
> >
> > I worked with Microsoft partner services but I wasn't able to get
> > confirmation that my assumption was correct. My support contact was
> > not able to duplicate the problem using the sample forms authentication
> > app.
> >
> > Good luck,
> >
> > Stefan Leyhane
> > Firmwater Inc.
> >
> >

Report Manager stops with HTTP 401 error (unauthorized)

Hello!
During my holiday the report manager stopped working and it is not possible
to access the reports using http://reportserver/reports. All automatic
distribution of reports have also stopped. As always, nobody has done
anything on the web server except adding a new website.
All reports are available from http://reportserver/reportserver and working
as they should.
Has anyone experienced the same and have any leads on how to fix this? I
have tried different security settings on the virtual directories, but
everything seems like it was from installation date. I am reluctant to
change security settings using rsconfig, etc. because all reports are
working.
Many thanks in advance.
Best regards,
Vemund HagaYou might take a look at a couple of things. I am not sure what the cause
is, but it sounds like a user account issue with the reports directory.
The account which runs the Reports web service might have changed or have a
bad password. You can find this in IIS for the virtual directory.
The unattended execution account may have changed or have a bad password.
The SQL Agent may have stopped. Subscriptions would no longer be delivered.
As you know, there could be various reasons why something like this
happens. Just wanted to throw you a few bones to get you going.
Accessing the Reports directory, or subscriptions failing to fire should
write errors to the log file. If you send me the log files, I will take a
look and see if I find something.
(bradsy@.microsoft.com)
--
| Reply-To: "Vemund Haga" <vemund.haga@.nospam.nospam>
| From: "Vemund Haga" <vemund.haga@.nospam.nospam>
| Subject: Report Manager stops with HTTP 401 error (unauthorized)
| Date: Tue, 16 Aug 2005 16:30:06 +0200
| Lines: 21
| Organization: DM Huset AS
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
| X-RFC2646: Format=Flowed; Original
| Message-ID: <O#PxA8moFHA.2580@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: mail.dmhuset.no 195.0.194.2
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:50459
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hello!
| During my holiday the report manager stopped working and it is not
possible
| to access the reports using http://reportserver/reports. All automatic
| distribution of reports have also stopped. As always, nobody has done
| anything on the web server except adding a new website.
|
| All reports are available from http://reportserver/reportserver and
working
| as they should.
|
| Has anyone experienced the same and have any leads on how to fix this? I
| have tried different security settings on the virtual directories, but
| everything seems like it was from installation date. I am reluctant to
| change security settings using rsconfig, etc. because all reports are
| working.
|
| Many thanks in advance.
|
| Best regards,
| Vemund Haga
|
|
|

Report Manager Not Working

I am suddenly unable to access report manager :
http://localhost/ReportManager/Pages/Folder.aspx
I am getting the error :
Could not find file "C:\WINDOWS\TEMP\_cm2qpki.dll".
The name of the DLL is different each time I try, but the end result
is the same. I can't find much on this. What I did find indicates a
lack of permission. I have tried accessing it with a couple of
different accounts (both were administrators on the server) and
security methods (anonymous access w/ an admin account, integrated
windows , digest, and basic authentication). I still get the same
error. This is Windows 2003 server, and SQL 2000. Any ideas? Thanks in
advance.
EricDOH!
I must have searched the web for an hour and a half for this. As soon
as I posted this I found the solution right here on Google groups:
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/9f7ef9267b35eb5c/300a9da8cb33b9f7?lnk=gst&q=Report+manager+%22could+not+find+file%22+temp&rnum=1#300a9da8cb33b9f7

report manager is not working--urgent

hi,
I was working on reporting services and when i opening the report
manager in the browser, it is giving me error
the error is
the request failed with HTTP status 404: Not Found
and the reportserver it working fine.
I have seen the rswebapplication.config and the 2 virtual directories
also but i could trace out the problem. So please any one could help me
in finding the solution.
Thanks and Regards
JaganTry this : http://<servername>/Reports/Pages/Folder.aspx
And also : http://<servername>
On the other hand, when you do this : http://<servername>/reports/Pages,
error 404, it's normal.
However that may be, the server can't access the folder or the page...
Hope this can help|||I am also having same problem where in suddenly I have started getting:
The request failed with HTTP status 404: Not Found.
I have tried qualifying the server name and checked rswebapplication.config
file too but cant fix the problem.
Any other pointers will be highly appreciated!!
--
Thanks
"Doume" wrote:
> Try this : http://<servername>/Reports/Pages/Folder.aspx
> And also : http://<servername>
> On the other hand, when you do this : http://<servername>/reports/Pages,
> error 404, it's normal.
> However that may be, the server can't access the folder or the page...
> Hope this can help
>|||Hi What do we need to check in the rswebapplication.config file?
I have the same issue
"Doume" wrote:
> Try this : http://<servername>/Reports/Pages/Folder.aspx
> And also : http://<servername>
> On the other hand, when you do this : http://<servername>/reports/Pages,
> error 404, it's normal.
> However that may be, the server can't access the folder or the page...
> Hope this can help
>|||Check to see if you have removed the Virtual Directory 'Reportserver' in IIS
if so add back - it should point to "C:\Program Files\Microsoft SQL
Server\MSSQL\Reporting Services\ReportServer" or there's abouts.
"tiwanap" wrote:
> I am also having same problem where in suddenly I have started getting:
> The request failed with HTTP status 404: Not Found.
> I have tried qualifying the server name and checked rswebapplication.config
> file too but cant fix the problem.
> Any other pointers will be highly appreciated!!
> --
> Thanks
>
> "Doume" wrote:
> > Try this : http://<servername>/Reports/Pages/Folder.aspx
> >
> > And also : http://<servername>
> > On the other hand, when you do this : http://<servername>/reports/Pages,
> > error 404, it's normal.
> >
> > However that may be, the server can't access the folder or the page...
> >
> > Hope this can help
> >
> >

Report Manager error

Hi,
I don't know way but after working fine for 4 months when I start Report
Manager I receive a strange error: Error ?
What can I do?
ThanksWhat's the error?
"Dana" <Dana@.discussions.microsoft.com> wrote in message
news:76A236DA-D641-493F-B815-F227CDAECD2A@.microsoft.com...
> Hi,
> I don't know way but after working fine for 4 months when I start Report
> Manager I receive a strange error: Error ?
> What can I do?
> Thanks|||This is the error: "Error ?"
"FNDS" wrote:
> What's the error?
> "Dana" <Dana@.discussions.microsoft.com> wrote in message
> news:76A236DA-D641-493F-B815-F227CDAECD2A@.microsoft.com...
> > Hi,
> >
> > I don't know way but after working fine for 4 months when I start Report
> > Manager I receive a strange error: Error ?
> >
> > What can I do?
> >
> > Thanks
>
>|||Look through the log files and the Windows Event Viewer and see what you can
find.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Dana" <Dana@.discussions.microsoft.com> wrote in message
news:62D6D002-B0D8-42E3-B1F7-960C45CC070D@.microsoft.com...
> This is the error: "Error ?"
> "FNDS" wrote:
>> What's the error?
>> "Dana" <Dana@.discussions.microsoft.com> wrote in message
>> news:76A236DA-D641-493F-B815-F227CDAECD2A@.microsoft.com...
>> > Hi,
>> >
>> > I don't know way but after working fine for 4 months when I start
>> > Report
>> > Manager I receive a strange error: Error ?
>> >
>> > What can I do?
>> >
>> > Thanks
>>

Report Manager buttons not working

HI there,
we just installed reporting services 2000 on our web server, and some
of the buttons like New Folder, etc do not work. When you press on
them nothing happens.
Does anyone know what would cause this?
Regards,
TomMy guess is you have enable anonymous for the website (check in IIS). If so,
then because the user is anonymous you don't have any rights to do anything,
even if you are an admin because it does not know who your are, you are
anonymous.
Hope that makes sense.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<OTSolutions@.shaw.ca> wrote in message
news:1164995767.663955.160290@.l12g2000cwl.googlegroups.com...
> HI there,
> we just installed reporting services 2000 on our web server, and some
> of the buttons like New Folder, etc do not work. When you press on
> them nothing happens.
> Does anyone know what would cause this?
> Regards,
> Tom
>|||Hi Bruce, we did not have enable anonymous for the website checked.
I also have re-registered asp .net just to be safe, any other
suggestions?
PS Thanks for the quick response.
Regards,
Tom
Bruce L-C [MVP] wrote:
> My guess is you have enable anonymous for the website (check in IIS). If so,
> then because the user is anonymous you don't have any rights to do anything,
> even if you are an admin because it does not know who your are, you are
> anonymous.
> Hope that makes sense.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <OTSolutions@.shaw.ca> wrote in message
> news:1164995767.663955.160290@.l12g2000cwl.googlegroups.com...
> > HI there,
> >
> > we just installed reporting services 2000 on our web server, and some
> > of the buttons like New Folder, etc do not work. When you press on
> > them nothing happens.
> >
> > Does anyone know what would cause this?
> >
> > Regards,
> > Tom
> >|||My next guess is that no-one has setup roles in RS. Are you an administrator
on the server that is running RS. If not, then you need to get someone who
is or get your account added to the local administrator account on the
server. By default, local administrator group accounts have admin rights on
RS.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<OTSolutions@.shaw.ca> wrote in message
news:1165074992.191408.136430@.f1g2000cwa.googlegroups.com...
> Hi Bruce, we did not have enable anonymous for the website checked.
> I also have re-registered asp .net just to be safe, any other
> suggestions?
> PS Thanks for the quick response.
> Regards,
> Tom
> Bruce L-C [MVP] wrote:
>> My guess is you have enable anonymous for the website (check in IIS). If
>> so,
>> then because the user is anonymous you don't have any rights to do
>> anything,
>> even if you are an admin because it does not know who your are, you are
>> anonymous.
>> Hope that makes sense.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> <OTSolutions@.shaw.ca> wrote in message
>> news:1164995767.663955.160290@.l12g2000cwl.googlegroups.com...
>> > HI there,
>> >
>> > we just installed reporting services 2000 on our web server, and some
>> > of the buttons like New Folder, etc do not work. When you press on
>> > them nothing happens.
>> >
>> > Does anyone know what would cause this?
>> >
>> > Regards,
>> > Tom
>> >
>

Report Manager and The request failed with HTTP status 400

I have installed Reporting services on a win2k3 on a completely different
irtual Directory than Default Directory .My Report Server is working fine
but I can't get my reporting manager working it give me this error:
The request failed with HTTP status 400:bad request,
Is there somebody who can help me.
ThnaksI think I got the answert ,,
I'm using a DNS for the new virtual directory that I have installed report
server and report manager on it and I forogt to update <ReportServerUrl> in
the RSWebApplication.config file of "Report Manager"
in the following address:
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportManager
It was pointing to the wrong address http://localhost/reportsver where as it
should be http://MyDNS/reportsver.
HTH,
ALI-R
"ALI-R" <newbie@.microsoft.com> wrote in message
news:%23s41$7MBFHA.3528@.tk2msftngp13.phx.gbl...
> I have installed Reporting services on a win2k3 on a completely different
> irtual Directory than Default Directory .My Report Server is working fine
> but I can't get my reporting manager working it give me this error:
> The request failed with HTTP status 400:bad request,
> Is there somebody who can help me.
> Thnaks
>|||Hi all,
we're still in trouble as our ReportManager webapp still won't work.
I verified the RSWebApplication.config files of both Report Manager and
Report Server to contain
<ReportServerUrl>http://localhost/ReportServer</ReportServerUrl>
and
<UrlRoot>http://localhost/ReportServer</UrlRoot>
respectively.
I already verified all permissions and even changed both above URLs to read
http://<servername> instead of localhost. This does not make any noticable
difference.
This is real trouble, MS, I'd hate to have to resort to reïnstalling
everything from scratch, as there's no telling how if, or how long, this will
work...
Please help? :o(
Ramses.
Still, the ReportServer works just fine on both
http://localhost/ReportServer and http://severname/ReportServer, wheras the
ReportManager simply refuses to work. It keeps yielding the dreaded
The request failed with HTTP status 400: Bad Request.
"ALI-R" wrote:
> I think I got the answert ,,
> I'm using a DNS for the new virtual directory that I have installed report
> server and report manager on it and I forogt to update <ReportServerUrl> in
> the RSWebApplication.config file of "Report Manager"
> in the following address:
> C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportManager
> It was pointing to the wrong address http://localhost/reportsver where as it
> should be http://MyDNS/reportsver.
> HTH,
> ALI-R
> "ALI-R" <newbie@.microsoft.com> wrote in message
> news:%23s41$7MBFHA.3528@.tk2msftngp13.phx.gbl...
> > I have installed Reporting services on a win2k3 on a completely different
> > irtual Directory than Default Directory .My Report Server is working fine
> > but I can't get my reporting manager working it give me this error:
> >
> > The request failed with HTTP status 400:bad request,
> >
> > Is there somebody who can help me.
> >
> > Thnaks
> >
> >
>
>|||Hi all,
we're still in trouble as our ReportManager webapp still won't work.
I verified the RSWebApplication.config files of both Report Manager and
Report Server to contain
<ReportServerUrl>http://localhost/ReportServer</ReportServerUrl>
and
<UrlRoot>http://localhost/ReportServer</UrlRoot>
respectively.
I already verified all permissions and even changed both above URLs to read
http://<servername> instead of localhost. This does not make any noticable
difference.
This is real trouble, MS, I'd hate to have to resort to reïnstalling
everything from scratch, as there's no telling how if, or how long, this will
work...
Please help? :o(
Ramses.
"ALI-R" wrote:
> I think I got the answert ,,
> I'm using a DNS for the new virtual directory that I have installed report
> server and report manager on it and I forogt to update <ReportServerUrl> in
> the RSWebApplication.config file of "Report Manager"
> in the following address:
> C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportManager
> It was pointing to the wrong address http://localhost/reportsver where as it
> should be http://MyDNS/reportsver.
> HTH,
> ALI-R
> "ALI-R" <newbie@.microsoft.com> wrote in message
> news:%23s41$7MBFHA.3528@.tk2msftngp13.phx.gbl...
> > I have installed Reporting services on a win2k3 on a completely different
> > irtual Directory than Default Directory .My Report Server is working fine
> > but I can't get my reporting manager working it give me this error:
> >
> > The request failed with HTTP status 400:bad request,
> >
> > Is there somebody who can help me.
> >
> > Thnaks
> >
> >
>
>|||I would check the host header in IIS. I have been having a problem
where I can't view the report properties, export a report, or schedule
a report (viewing is fine, though).
I was messing with the host headers in IIS and got that message if I
had things configured incorrectly. For working locally, you should
probably not have anything in the host header value for the site.|||I have no Host-headers set for the default website - checked this numerous
times. The default website is bound to both 127.0.0.1 and the LAN IP address
of the server. Note that my http://<servername>/Reports tests wouldn't have
worked otherwise.
Still at a loss. :o(
"cybermud" wrote:
> I would check the host header in IIS. I have been having a problem
> where I can't view the report properties, export a report, or schedule
> a report (viewing is fine, though).
> I was messing with the host headers in IIS and got that message if I
> had things configured incorrectly. For working locally, you should
> probably not have anything in the host header value for the site.
>

Report Manager

I think I have Reporting Services installed correctly and it is working except for the Report Manager.
When I browse to http://localhost/reportserver I dont have a user interface. All I see is big black letters saying localhost/reportserver -/ and underneath is a list of the reports I have deployed.
How do I get the user interface to show?
Your help is appreciated.
Roy

OK,
The answer is so simple I can't beleive I overlooked it.
The path to Report Manager is http://<servername>/reports
When I browsed there everything was as expected.|||The Report Manager web application resides here:
http://locahost/reports.

You are accessing the web service URL.

Todd

Report limited to Top 8 but not in the SQL satement?

I got a report that seems to be limiting its displayed data to the first 8 records but not in the SQL statement itself. Although I have been working with SQL for many years I'm very new to Reporting Services so it may be something very simple, like perhaps a property that can be changed on the report for me to increase the amount of records the report is pulling from the database?

I don't seem to find any property set to 8 though.

Also the report is not pulling the records in the order the query pulls them, but in an apparent random order, I don't need to change this but it may help describe the report a bit better.

In Visual Studio, are all the records returned when you execute your SQL statement while you're in the Data tab?

Check to see if you have any filters on your table. Right click on your table and go to Properties, then check the Filters tab. Filters limit your recordset after it is returned to the report, so your query would return it, but it wouldn't show on your report.

This may sound silly, but are you sure the rest of the records are not on another page?

Jarret

|||Yes, the records are all returned without limits in the Data tab, and no there are no extra pages. I was told by the report user that it is intentionally set to, according to him, "8 random records", but for the records appear to be the same 8 every time, just not sorted in any logical order.

I checked the filters in the dataset as that didn't occur to me at first but there were no filters for this dataset.
|||

Do you have any grouping in your table? If so, are you hiding the detail records and only showing the group header/footer?

Can you try running Profiler when the report runs to make sure the query is being run correctly?

Jarret

|||There is no grouping, The query is a very simple select, although it calls a Stored Procedure it is working perfectly. I tested it inside the Visual Studio Business Intelligence environment and inside SQL Management Studio, plus ran the simple SQL that is inside the Stored Procedure on it's own, all with the same results (all records being returned.)

I was told this report was intentionally designed to show 8 records, I simply was requested to increase it to 10.
|||

Well, I think I will have to defer to someone else to help you. I've never seen RS 'eat' records before without having a filter of some sort. Like a filter on the table, a 'top' in the query, or a 'set rowcount X'.

Can you get in contact with the report creator and see how they got it to only show 8?

You could open the RDL file in notepad and see if you can see an '8' anywhere, and what setting it is on.

Jarret

|||Thank you, i forgot that rpl files where xml. I looked it up and found a filter being applied to a "Table1", oddly it never shows in the interface.

Code Snippet

<Table Name="table1">
<Filters>
<Filter>
<Operator>TopN</Operator>
<FilterValues>
<FilterValue>=8</FilterValue>
</FilterValues>
<FilterExpression>=Fields!Account_Number.Value</FilterExpression>
</Filter>
</Filters>

I changed the 8 to 10 but I'm still lost on why it does not show up in the dataset filters... although also there is no dataset called "table1".
|||

I understand now. Here, do this:

In your properties window (F4), use the drop-down to select 'table1'. This is the name of the table object in your report. Then, in the properties window, find and click on 'Filters'. It should say (Collection) next to it, click on the '...' button in the filters property. This will open a dialog box with an expression, operator, and value. In your case, you will see the following settings:

Expression: =Fields!Account_Number.Value

Operator: =

Value: =8

Here is where you can change the number of records you want returned.

Jarret

|||Thank you, this information is very useful!

Report Layout

I'm working on a report that could potentially require 13 or so datasets in
order to get the specific fields that I need for each section of the report.
The SQL table is structured as follows:
select
year,
branch,
measure,
jan,
feb,
mar
from tblBPM_BP
I was trying to bring all of the measures in on one dataset, but I need to
be able to further filter the data for each section of the report (i.e. Sales
& headcount). Is there a better technique than using a dataset for each
section of the report?What about using filters.
"DJONES" wrote:
> I'm working on a report that could potentially require 13 or so datasets in
> order to get the specific fields that I need for each section of the report.
> The SQL table is structured as follows:
> select
> year,
> branch,
> measure,
> jan,
> feb,
> mar
> from tblBPM_BP
> I was trying to bring all of the measures in on one dataset, but I need to
> be able to further filter the data for each section of the report (i.e. Sales
> & headcount). Is there a better technique than using a dataset for each
> section of the report?
>|||Can I filter out data in a table w/i the report designer?
"Victor" wrote:
> What about using filters.
>
> "DJONES" wrote:
> > I'm working on a report that could potentially require 13 or so datasets in
> > order to get the specific fields that I need for each section of the report.
> > The SQL table is structured as follows:
> >
> > select
> > year,
> > branch,
> > measure,
> > jan,
> > feb,
> > mar
> > from tblBPM_BP
> >
> > I was trying to bring all of the measures in on one dataset, but I need to
> > be able to further filter the data for each section of the report (i.e. Sales
> > & headcount). Is there a better technique than using a dataset for each
> > section of the report?
> >|||Table properties -> Filters
"DJONES" wrote:
> Can I filter out data in a table w/i the report designer?
> "Victor" wrote:
> > What about using filters.
> >
> >
> > "DJONES" wrote:
> >
> > > I'm working on a report that could potentially require 13 or so datasets in
> > > order to get the specific fields that I need for each section of the report.
> > > The SQL table is structured as follows:
> > >
> > > select
> > > year,
> > > branch,
> > > measure,
> > > jan,
> > > feb,
> > > mar
> > > from tblBPM_BP
> > >
> > > I was trying to bring all of the measures in on one dataset, but I need to
> > > be able to further filter the data for each section of the report (i.e. Sales
> > > & headcount). Is there a better technique than using a dataset for each
> > > section of the report?
> > >|||That will do the trick. Thanks!
"Victor" wrote:
> Table properties -> Filters
> "DJONES" wrote:
> > Can I filter out data in a table w/i the report designer?
> >
> > "Victor" wrote:
> >
> > > What about using filters.
> > >
> > >
> > > "DJONES" wrote:
> > >
> > > > I'm working on a report that could potentially require 13 or so datasets in
> > > > order to get the specific fields that I need for each section of the report.
> > > > The SQL table is structured as follows:
> > > >
> > > > select
> > > > year,
> > > > branch,
> > > > measure,
> > > > jan,
> > > > feb,
> > > > mar
> > > > from tblBPM_BP
> > > >
> > > > I was trying to bring all of the measures in on one dataset, but I need to
> > > > be able to further filter the data for each section of the report (i.e. Sales
> > > > & headcount). Is there a better technique than using a dataset for each
> > > > section of the report?
> > > >

Report History in SQL Reporting Service

Hi,

I am using SQL Server 2005 Reporting service. I am working on a report. I have made several subscriptions for it. Report runs according to subscription. SQL Server Reporting service also offers functionality to save report histories. Now how can I configure it so that whenever report runs according to its subscription, a history should be automatically saved?

If in case it is not possible by some automatic process then is there any way to set multiple schedules for history of the single report like multiple subscriptions can be set for single report.

Waiting for your response. Thanks in advance.

Regards,
Sulman.

Under the History Properties of the report, you need to select the option to:
Store all report execution snapshots in history.

There are also other options to indicate how many snapshots you want to keep, as well as creating snapshots on a regular schedule.|||Thanx, J Quick

Yes there is an option in Hisotry properties "Store all report execution snapshots in history". But let me clear how it works.

On the Execution property page there are two option.
1. "Render this report with the most recent data"
This option means that whenever the report will be executed, it will get fresh data from database by making query on it.
2. "Render this report from an execution snapshot"
This option means that an execution snapshot of report will be saved either once when the Apply button is pressed or it will keep on updating depending upon schedule. But at one time there will be only one execution snapsho. And whenever the report will be executed, it will be rendered from that execution snapshot and not from the fresh data of database.

Now i come to History property mentioned above to "Store all report execution snapshots in history". It will work only when the execution property of report is set to "Render this report from an execution snapshot". And if execution snapshot is scheduled to be updated then each time the execution snapshot will update it will also be saved in history as history snapshot. So for this history property to work, we have to sacrifice the updated and fresh data. But in my case, i have to get fresh data every time and also i want to save the history of that executed report. Try to think on this line.

Anyhow i appreciate your effort. Thanx again.

Sulman.
|||

>> "It will work only when the execution property of report is set to "Render this report from an execution snapshot".

I don't think the statement above is correct, but I could be wrong since it has been some time since I tried it.

As I recall, the options to "Render this report with the most recent data" and "Store all report execution snapshots in history" are on different property pages and aren't mutually exclusive.

Report Header

i have small doubt. i am using sql2005 reports.

My reports every thing is working properly

what my question is?

My report is like this example:

col1 col2 col3 col4

1 2 2 2

2 3 4 5

ok some time in reports no data so when that time its showing only Header only right?

But i dont want to disply my reports header if there is no data in my report just if no data dont disply my report header how? i am new reporing services

if there is no data in fields dont disply my header : col1 col2 col3 col4

it should be false how?

thanx

The best method to do this is to hide the table completely when there is no data aviable to show. You can do that by stiing the visibility property to an expression which counts the number of rows. If = 0 then the visibility is false else true.

An other option is to fill the NoRow property of the table. This property is only visible in the little properties window (press F4, or View -> Properties window)

|||

thanx Jan Pieter Posthum

its working.

report having larger width not printing

Hi,

I have one report with the page size of 17in, 8.5in. Exporting then printing PDF is working fine. But when I am printing directly from report, It is not printing. It is not saying any error. The popup box is coming and showing the message like printing. But it is not printing. I can't change the page size lesser than 17in.

Can you please give some idea for this issue?

Thanks,

Muniappan Kandasamy

You can keep the report body height as anything, it can be as long as you want but keep the interactive height of the report as 11'

A standard portrait style print is of size 8.5" x 11" keep this as the interactive size of your report.

..............................................................................................

Remember to mark as "Answer" on the post that helped you.

http://technoblab.blogspot.com