My dataset returns data from a range of effective dates for a single
month. In the database, these dates are unfortunately not of type
datetime, but are decimal and are in the YYYYMMDD format.
I'm having trouble getting the report to execute because at the query
level, the datatype has to be decimal. I need to parameterize these
dates, but there is no decimal datatype for parameters.
I have a dataset that generates the necessary dates based on the type
of report the user wants (60/90/120 day) (if they want a 60-day report,
the effective date = 2 months from the first day of the current month,
etc).
SELECT DISTINCT
CONVERT(datetime,LEFT(CAST(effdte AS varchar), 4) + '/' +
SUBSTRING(CAST(effdte AS varchar), 5, 2) + '/' + '01') +
@.period_additive AS effdte_low,
CONVERT(datetime,LEFT(CAST(effdte AS varchar), 4) + '/' +
SUBSTRING(CAST(effdte AS varchar), 5, 2) + '/' + '31') +
@.period_additive AS effdte_high
FROM dbo.t_policy
WHERE YEAR(GETDATE()) - 1 = LEFT(CAST(effdte AS varchar), 4) AND
MONTH(GETDATE()) = SUBSTRING(CAST(effdte AS varchar), 5, 2) AND
LEFT(policy, 3) LIKE @.dept_cd AND LEFT(policy, 3) <> 'LPA'
What would the appropriate code be to do this within the Report
Parameters dialog instead? It seems like I'd have a better chance of
success if it were done that way.
Thanks!
MikeNo matter what, I get this error at runtime (Preview mode):
--
Processing Errors
--
An error has occurred during report processing.
Cannot read the next data row for the data set ds_main.
Arithmetic overflow error converting expression to data type datetime.
--
OK
--|||I think I fixed it...
Even though the database type is decimal, when searching by that range,
I have to put single quotes around it. Therefore, I converted the date
type back to a varchar and changed the parameter data types to string.
Now the report runs.
But there should be a more straightforward way to do this within the
Report Parameters dialog box, shouldn't there?sql
Showing posts with label dataset. Show all posts
Showing posts with label dataset. Show all posts
Friday, March 30, 2012
Report parameters
Hi everybody.
I have one dataset with parameter 'UserID' and second Dataset which fill
UserID parameter. When i run my report i can choose value from second
Dataset, but i wont that in my Parameter will be present value as 'All' which
mean all records from query. It is possible? if yes how? any sample?
P.S.
I know how add this value but i don't know how to write my query.
Thank you.Hello Dmitri,
Here is how to write the query to have 'All' included in your list of
users:
SELECT UserNo, UserName FROM Users
UNION
SELECT -1, 'All'
Take care,
Michelle|||Hi,
SELECT col1,col2,...
FROM table1
where col1 = CASE WHEN @.Param = 'All' THEN col1
ELSE @.Param
END
Eric|||oops, I forgot the second half - to check for the 'All' in your second
query:
SELECT something1, something2
FROM someTable
WHERE (User = @.User OR @.User = 'All')
or
SELECT something1, something2
FROM someTable
WHERE (UserNo = @.UserNo OR @.UserNo = -1)
Michelle|||Hi,
select col1,col2,col3
from table1
where col1 = case when @.param = 'all' then col1
else @.param
end
hth,
Eric|||Thank you all.
"Aiwa" wrote:
> Hi,
> select col1,col2,col3
> from table1
> where col1 = case when @.param = 'all' then col1
> else @.param
> end
> hth,
> Eric
>
I have one dataset with parameter 'UserID' and second Dataset which fill
UserID parameter. When i run my report i can choose value from second
Dataset, but i wont that in my Parameter will be present value as 'All' which
mean all records from query. It is possible? if yes how? any sample?
P.S.
I know how add this value but i don't know how to write my query.
Thank you.Hello Dmitri,
Here is how to write the query to have 'All' included in your list of
users:
SELECT UserNo, UserName FROM Users
UNION
SELECT -1, 'All'
Take care,
Michelle|||Hi,
SELECT col1,col2,...
FROM table1
where col1 = CASE WHEN @.Param = 'All' THEN col1
ELSE @.Param
END
Eric|||oops, I forgot the second half - to check for the 'All' in your second
query:
SELECT something1, something2
FROM someTable
WHERE (User = @.User OR @.User = 'All')
or
SELECT something1, something2
FROM someTable
WHERE (UserNo = @.UserNo OR @.UserNo = -1)
Michelle|||Hi,
select col1,col2,col3
from table1
where col1 = case when @.param = 'all' then col1
else @.param
end
hth,
Eric|||Thank you all.
"Aiwa" wrote:
> Hi,
> select col1,col2,col3
> from table1
> where col1 = case when @.param = 'all' then col1
> else @.param
> end
> hth,
> Eric
>
Report Parameters
If I specifiy a parameter in my report dataset like @.status to filter the
report based on a status selected by the user - I can't figure out how to
give them the "ALL" option - so that the report ignores the status and
displays all the records.
--
ArkayTry this
SELECT NULL AS StatusValue, '<All>' AS Status
UNION
SELECT StatusValue, Status
FROM Status_mstr
"Arkay" <Arkay@.discussions.microsoft.com> wrote in message
news:7E0C2DD8-A1E9-407C-BDDD-AD0B110C5775@.microsoft.com...
> If I specifiy a parameter in my report dataset like @.status to filter the
> report based on a status selected by the user - I can't figure out how to
> give them the "ALL" option - so that the report ignores the status and
> displays all the records.
>
> --
> Arkay
report based on a status selected by the user - I can't figure out how to
give them the "ALL" option - so that the report ignores the status and
displays all the records.
--
ArkayTry this
SELECT NULL AS StatusValue, '<All>' AS Status
UNION
SELECT StatusValue, Status
FROM Status_mstr
"Arkay" <Arkay@.discussions.microsoft.com> wrote in message
news:7E0C2DD8-A1E9-407C-BDDD-AD0B110C5775@.microsoft.com...
> If I specifiy a parameter in my report dataset like @.status to filter the
> report based on a status selected by the user - I can't figure out how to
> give them the "ALL" option - so that the report ignores the status and
> displays all the records.
>
> --
> Arkay
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
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
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
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
Wednesday, March 28, 2012
Report parameter dropdown
Hello,
I need to create a MyPar parameter dropdown list, that is why I created a new dataset that gets data through a stored procedure. I also add @.MyPar into the query string of MyMainDataSet . I defined this parameter in Parameter screen for both available and default values.
When I run the report, I get the following error.
Query execution failed for data set MyMainDataSet.
Must declare the variable @.MyPar
What is problem?
Hello:
If I understand correctly:
The first stored procedure that displays the parameters for MyPar is a straight SQL Select Statement - So this SQL Select just gets the data for the available parameters to display in the dropdown list.
The next step the user selects one value from all of the available parameters in the dropdown list - so once this parameter is selected you need to pass this selected parameter to your next SQL Select statement so you only get the data based on the parameter the user selected!
Include the following in the Stored Procedure (if your are using one for the selection of data based on the parameter the user selected)
CREATE PROCEDURE "Whatever the Procedure name is"
@.MyPar nvarchar(10) or whatever length the parameter is
-- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
set nocount on
Hope this helps!
Best Regards - Joe
Report Parameter Dataset Running Twice?
Hi,
I have a DDL Report Parameter tied to a Dataset.
When I go to view the Report in RS the Dataset is run to populate the
Parameter, but it also seems to run the Dataset when I hit the View Report
button.
I don't have the Dataset tied to anything except the Report Parameter.
Is there a way to stop the 2nd run?
thank you
tOn May 22, 11:59 am, DigHazuse <DigHaz...@.discussions.microsoft.com>
wrote:
> Hi,
> I have a DDL Report Parameter tied to a Dataset.
> When I go to view the Report in RS the Dataset is run to populate the
> Parameter, but it also seems to run the Dataset when I hit the View Report
> button.
> I don't have the Dataset tied to anything except the Report Parameter.
> Is there a way to stop the 2nd run?
> thank you
> t
It should only be running to populate the report parameter. It sounds
like you possibly have the autorefresh set for the report (go to the:
Report drop-down tab -> Report Properties... -> General tab ->
autorefresh and see if it is selected). Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thank you for responding Enrique ... unfortunately that box is not checked.
Any other ideas?
thank you
t
"EMartinez" wrote:
> On May 22, 11:59 am, DigHazuse <DigHaz...@.discussions.microsoft.com>
> wrote:
> > Hi,
> > I have a DDL Report Parameter tied to a Dataset.
> >
> > When I go to view the Report in RS the Dataset is run to populate the
> > Parameter, but it also seems to run the Dataset when I hit the View Report
> > button.
> >
> > I don't have the Dataset tied to anything except the Report Parameter.
> >
> > Is there a way to stop the 2nd run?
> >
> > thank you
> > t
>
> It should only be running to populate the report parameter. It sounds
> like you possibly have the autorefresh set for the report (go to the:
> Report drop-down tab -> Report Properties... -> General tab ->
> autorefresh and see if it is selected). Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>sql
I have a DDL Report Parameter tied to a Dataset.
When I go to view the Report in RS the Dataset is run to populate the
Parameter, but it also seems to run the Dataset when I hit the View Report
button.
I don't have the Dataset tied to anything except the Report Parameter.
Is there a way to stop the 2nd run?
thank you
tOn May 22, 11:59 am, DigHazuse <DigHaz...@.discussions.microsoft.com>
wrote:
> Hi,
> I have a DDL Report Parameter tied to a Dataset.
> When I go to view the Report in RS the Dataset is run to populate the
> Parameter, but it also seems to run the Dataset when I hit the View Report
> button.
> I don't have the Dataset tied to anything except the Report Parameter.
> Is there a way to stop the 2nd run?
> thank you
> t
It should only be running to populate the report parameter. It sounds
like you possibly have the autorefresh set for the report (go to the:
Report drop-down tab -> Report Properties... -> General tab ->
autorefresh and see if it is selected). Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thank you for responding Enrique ... unfortunately that box is not checked.
Any other ideas?
thank you
t
"EMartinez" wrote:
> On May 22, 11:59 am, DigHazuse <DigHaz...@.discussions.microsoft.com>
> wrote:
> > Hi,
> > I have a DDL Report Parameter tied to a Dataset.
> >
> > When I go to view the Report in RS the Dataset is run to populate the
> > Parameter, but it also seems to run the Dataset when I hit the View Report
> > button.
> >
> > I don't have the Dataset tied to anything except the Report Parameter.
> >
> > Is there a way to stop the 2nd run?
> >
> > thank you
> > t
>
> It should only be running to populate the report parameter. It sounds
> like you possibly have the autorefresh set for the report (go to the:
> Report drop-down tab -> Report Properties... -> General tab ->
> autorefresh and see if it is selected). Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>sql
Report parameter as a where clause
Hello
Is it possible to construct a dataset where the parameter of the report is the where clause?
I have tried setting the dataset of the report to be a variable to execute, but any time I introduce the parameter into the dataset, the report will not run.
Hi,
you want to use parameter in the report use @.parameter_name.
|||Here's what I want to do...SELECT name, phone FROM contacts WHERE @.parameter_name
This does not work.
I have also tried, but it does not work:
DECLARE @.s AS nvarchar(1000)
SET @.s = N'SELECT name, phone FROM contacts WHERE ' + @.parameter_name
EXEC sp_executesql @.s
|||
You need to set your procedure up properly:
@.Parameter_Name varchar(1000) = null
,
AS
SELECT NAME, PHONE
FROM CONTACTS
WHERE NAME = @.Parameter_Name
|||Thanks for your reply, but I need you to further clarify.From your example, it is expecting to define the value for 'NAME'. What I want to do is to have the *entire* where clause as a parameter. e.g. @.whereclause = WHERE name = 'bob' and phone = '2222'
|||*bump* can any of the experts look into this?
|||
Hi ajhuddy!
Jim is correct in that you need to base your parameter values against a fieldname. In your example this should work:
SELECT NAME, PHONE
FROM CONTACTS
WHERE NAME IN(@.Parameter_Name) AND PHONE IN(@.Parameter_Phone)
This query will pull in all selected parameter values into your report.|||Thanks for the reply Chuck,So is there any humanly possible way to make the parameter the entire where clause? I don't want to the SQL statement of the report to be hard-wired for all the possible fields. E.g. @.whereclause = "WHERE name = bob or phone = 2222"
This is an important requirement for me, as I need the user to be able to create advanced 'where' criteria before launching the report.
|||
Hi,
If I understand you correctly this is an example on how to use dynamic SQL:
Code Snippet
DECLARE @.select NVARCHAR(1000)
DECLARE @.from NVARCHAR(1000)
DECLARE @.where NVARCHAR(1000)
DECLARE @.QueryString NVARCHAR(3003)
SET @.select = 'SELECT <Columns> '
SET @.from = 'FROM <Table> '
SET @.where = 'WHERE <WherePart>'
SET @.QueryString = @.select + @.from + @.where
EXEC sp_executesql @.QueryString
If you work with char datatypes in your where clause,you will have to double the quotes
Code Snippet
SET @.where = 'WHERE name = ''YourValue'''
|||Hi,
This is an important requirement for me, as I need the user to be able to create advanced 'where' criteria before launching the report.
Did you have a look at report builder?
HTH,
|||Aiwa,Thanks very much for the reply. This is exactly what I want to do. In your example, are you inferring that @.where is the parameter for the report? If so, this is what I have been trying to do, but I get errors for each field on the report. E.g. :
[rsFieldReference] The Value expression for the textbox ‘b_name’ refers to the field ‘b_name’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
|||
Aiwa wrote:
Hi,
If I understand you correctly this is an example on how to use dynamic SQL:
Code Snippet
DECLARE @.select NVARCHAR(1000)
DECLARE @.from NVARCHAR(1000)
DECLARE @.where NVARCHAR(1000)
DECLARE @.QueryString NVARCHAR(3003)
SET @.select = 'SELECT <Columns> '
SET @.from = 'FROM <Table> '
SET @.where = 'WHERE <WherePart>'
SET @.QueryString = @.select + @.from + @.where
EXEC sp_executesql @.QueryString
If you work with char datatypes in your where clause,you will have to double the quotes
Code Snippet
SET @.where = 'WHERE name = ''YourValue'''
Hi,
This is an important requirement for me, as I need the user to be able to create advanced 'where' criteria before launching the report.
Did you have a look at report builder?
HTH,
Aiwa,
Yes, I did look at the report builder, and it is great - but it can only create simple reports. Ideally, the 'filter' option that exists in the report builder would exist in the standard reports.
|||Please see this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2041825&SiteID=1&mode=1sql
Report parameter as a where clause
Hello
Is it possible to construct a dataset where the parameter of the report is the where clause?
I have tried setting the dataset of the report to be a variable to execute, but any time I introduce the parameter into the dataset, the report will not run.
Hi,
you want to use parameter in the report use @.parameter_name.
|||Here's what I want to do...SELECT name, phone FROM contacts WHERE @.parameter_name
This does not work.
I have also tried, but it does not work:
DECLARE @.s AS nvarchar(1000)
SET @.s = N'SELECT name, phone FROM contacts WHERE ' + @.parameter_name
EXEC sp_executesql @.s
|||
You need to set your procedure up properly:
@.Parameter_Name varchar(1000) = null
,
AS
SELECT NAME, PHONE
FROM CONTACTS
WHERE NAME = @.Parameter_Name
|||Thanks for your reply, but I need you to further clarify.From your example, it is expecting to define the value for 'NAME'. What I want to do is to have the *entire* where clause as a parameter. e.g. @.whereclause = WHERE name = 'bob' and phone = '2222'
|||*bump* can any of the experts look into this?
|||
Hi ajhuddy!
Jim is correct in that you need to base your parameter values against a fieldname. In your example this should work:
SELECT NAME, PHONE
FROM CONTACTS
WHERE NAME IN(@.Parameter_Name) AND PHONE IN(@.Parameter_Phone)
This query will pull in all selected parameter values into your report.|||Thanks for the reply Chuck,So is there any humanly possible way to make the parameter the entire where clause? I don't want to the SQL statement of the report to be hard-wired for all the possible fields. E.g. @.whereclause = "WHERE name = bob or phone = 2222"
This is an important requirement for me, as I need the user to be able to create advanced 'where' criteria before launching the report.
|||
Hi,
If I understand you correctly this is an example on how to use dynamic SQL:
Code Snippet
DECLARE @.select NVARCHAR(1000)
DECLARE @.from NVARCHAR(1000)
DECLARE @.where NVARCHAR(1000)
DECLARE @.QueryString NVARCHAR(3003)
SET @.select = 'SELECT <Columns> '
SET @.from = 'FROM <Table> '
SET @.where = 'WHERE <WherePart>'
SET @.QueryString = @.select + @.from + @.where
EXEC sp_executesql @.QueryString
If you work with char datatypes in your where clause,you will have to double the quotes
Code Snippet
SET @.where = 'WHERE name = ''YourValue'''
|||Hi,
This is an important requirement for me, as I need the user to be able to create advanced 'where' criteria before launching the report.
Did you have a look at report builder?
HTH,
|||Aiwa,Thanks very much for the reply. This is exactly what I want to do. In your example, are you inferring that @.where is the parameter for the report? If so, this is what I have been trying to do, but I get errors for each field on the report. E.g. :
[rsFieldReference] The Value expression for the textbox ‘b_name’ refers to the field ‘b_name’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
|||
Hi,
If I understand you correctly this is an example on how to use dynamic SQL:
Code Snippet
DECLARE @.select NVARCHAR(1000)
DECLARE @.from NVARCHAR(1000)
DECLARE @.where NVARCHAR(1000)
DECLARE @.QueryString NVARCHAR(3003)
SET @.select = 'SELECT <Columns> '
SET @.from = 'FROM <Table> '
SET @.where = 'WHERE <WherePart>'
SET @.QueryString = @.select + @.from + @.where
EXEC sp_executesql @.QueryString
If you work with char datatypes in your where clause,you will have to double the quotes
Code Snippet
SET @.where = 'WHERE name = ''YourValue'''
Hi,
This is an important requirement for me, as I need the user to be able to create advanced 'where' criteria before launching the report.
Did you have a look at report builder?
HTH,
Aiwa,
Yes, I did look at the report builder, and it is great - but it can only create simple reports. Ideally, the 'filter' option that exists in the report builder would exist in the standard reports.
|||Please see this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2041825&SiteID=1&mode=1
Monday, March 26, 2012
Report only showing a single record
I have a DataSet which is defined as "SELECT * FROM Table". There are MANY
records in Table. However, the report only shows a single record/page. I
placed the fields on the report in the "Body". What am I missing here?Nevermind... Got it. It's the List item.
"Dan" wrote:
> I have a DataSet which is defined as "SELECT * FROM Table". There are MANY
> records in Table. However, the report only shows a single record/page. I
> placed the fields on the report in the "Body". What am I missing here?|||When you place the fields on the blank layout surface you are not using any
of the controls that know about multiple rows. You need to place either a
table control or a list control and then drag and drop onto them.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:7F61EA6A-DB25-43FC-9117-84A2E7E037AA@.microsoft.com...
>I have a DataSet which is defined as "SELECT * FROM Table". There are MANY
> records in Table. However, the report only shows a single record/page. I
> placed the fields on the report in the "Body". What am I missing here?sql
records in Table. However, the report only shows a single record/page. I
placed the fields on the report in the "Body". What am I missing here?Nevermind... Got it. It's the List item.
"Dan" wrote:
> I have a DataSet which is defined as "SELECT * FROM Table". There are MANY
> records in Table. However, the report only shows a single record/page. I
> placed the fields on the report in the "Body". What am I missing here?|||When you place the fields on the blank layout surface you are not using any
of the controls that know about multiple rows. You need to place either a
table control or a list control and then drag and drop onto them.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:7F61EA6A-DB25-43FC-9117-84A2E7E037AA@.microsoft.com...
>I have a DataSet which is defined as "SELECT * FROM Table". There are MANY
> records in Table. However, the report only shows a single record/page. I
> placed the fields on the report in the "Body". What am I missing here?sql
Report not returning all the records
Hello EveryBody,
I am designing report using report designer...I have defined two dataset..
First Dataset is just returning all the Category(i.e select CategoryName
from Category)
and in the second dataset i am passing that CategoryName to get all the
products belongs to that category...
So basically CagegoryName is my dropdown list to select category... Now
whiile is designing the report in the report body i have drag and drop all
the fields of second dataset...with their lebels.. but when i preview the
report i am just able to see first record even though each category has
atleast 10 products...
my sample textbox value is as =First(Fields!ProductName.Value, "Product_Set")
Eventhough i removed First and tried still it is returning one record...
Pls help
thxWhat you want in this case is to have one report with another report
embedded as a subreport. You will want to read up on subreports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:4BE1702E-B131-4B03-8CF7-6508727B26AE@.microsoft.com...
> Hello EveryBody,
> I am designing report using report designer...I have defined two dataset..
> First Dataset is just returning all the Category(i.e select CategoryName
> from Category)
> and in the second dataset i am passing that CategoryName to get all the
> products belongs to that category...
> So basically CagegoryName is my dropdown list to select category... Now
> whiile is designing the report in the report body i have drag and drop all
> the fields of second dataset...with their lebels.. but when i preview the
> report i am just able to see first record even though each category has
> atleast 10 products...
> my sample textbox value is as =First(Fields!ProductName.Value,
> "Product_Set")
> Eventhough i removed First and tried still it is returning one record...
> Pls help
> thx|||It is not a subreport.. It is just selecting a CategoryName from the dropdown
list on the top bar and passing that value to second dataset..
"Bruce L-C [MVP]" wrote:
> What you want in this case is to have one report with another report
> embedded as a subreport. You will want to read up on subreports.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:4BE1702E-B131-4B03-8CF7-6508727B26AE@.microsoft.com...
> > Hello EveryBody,
> > I am designing report using report designer...I have defined two dataset..
> > First Dataset is just returning all the Category(i.e select CategoryName
> > from Category)
> > and in the second dataset i am passing that CategoryName to get all the
> > products belongs to that category...
> >
> > So basically CagegoryName is my dropdown list to select category... Now
> > whiile is designing the report in the report body i have drag and drop all
> > the fields of second dataset...with their lebels.. but when i preview the
> > report i am just able to see first record even though each category has
> > atleast 10 products...
> >
> > my sample textbox value is as =First(Fields!ProductName.Value,
> > "Product_Set")
> >
> > Eventhough i removed First and tried still it is returning one record...
> >
> > Pls help
> >
> > thx
>
>|||It sounds to me like one dataset is just for the dropdown for the parameter
to list. Then that gets passed as a parameter to the second dataset that you
want to display. My guess is that you are dropping textboxes on the report
design surface. What you want to do is drop a table (it will default to 3
fields). Then drag and drop fields onto it. Add additional columns by doing
a right mouse click on the table.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:1588A17A-D5BE-4325-A5B3-F979E03E0642@.microsoft.com...
> It is not a subreport.. It is just selecting a CategoryName from the
> dropdown
> list on the top bar and passing that value to second dataset..
> "Bruce L-C [MVP]" wrote:
>> What you want in this case is to have one report with another report
>> embedded as a subreport. You will want to read up on subreports.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "mvp" <mvp@.discussions.microsoft.com> wrote in message
>> news:4BE1702E-B131-4B03-8CF7-6508727B26AE@.microsoft.com...
>> > Hello EveryBody,
>> > I am designing report using report designer...I have defined two
>> > dataset..
>> > First Dataset is just returning all the Category(i.e select
>> > CategoryName
>> > from Category)
>> > and in the second dataset i am passing that CategoryName to get all the
>> > products belongs to that category...
>> >
>> > So basically CagegoryName is my dropdown list to select category... Now
>> > whiile is designing the report in the report body i have drag and drop
>> > all
>> > the fields of second dataset...with their lebels.. but when i preview
>> > the
>> > report i am just able to see first record even though each category has
>> > atleast 10 products...
>> >
>> > my sample textbox value is as =First(Fields!ProductName.Value,
>> > "Product_Set")
>> >
>> > Eventhough i removed First and tried still it is returning one
>> > record...
>> >
>> > Pls help
>> >
>> > thx
>>|||Yes that was the problem.. It is working now... Thanks
"Bruce L-C [MVP]" wrote:
> It sounds to me like one dataset is just for the dropdown for the parameter
> to list. Then that gets passed as a parameter to the second dataset that you
> want to display. My guess is that you are dropping textboxes on the report
> design surface. What you want to do is drop a table (it will default to 3
> fields). Then drag and drop fields onto it. Add additional columns by doing
> a right mouse click on the table.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:1588A17A-D5BE-4325-A5B3-F979E03E0642@.microsoft.com...
> > It is not a subreport.. It is just selecting a CategoryName from the
> > dropdown
> > list on the top bar and passing that value to second dataset..
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> What you want in this case is to have one report with another report
> >> embedded as a subreport. You will want to read up on subreports.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> >> news:4BE1702E-B131-4B03-8CF7-6508727B26AE@.microsoft.com...
> >> > Hello EveryBody,
> >> > I am designing report using report designer...I have defined two
> >> > dataset..
> >> > First Dataset is just returning all the Category(i.e select
> >> > CategoryName
> >> > from Category)
> >> > and in the second dataset i am passing that CategoryName to get all the
> >> > products belongs to that category...
> >> >
> >> > So basically CagegoryName is my dropdown list to select category... Now
> >> > whiile is designing the report in the report body i have drag and drop
> >> > all
> >> > the fields of second dataset...with their lebels.. but when i preview
> >> > the
> >> > report i am just able to see first record even though each category has
> >> > atleast 10 products...
> >> >
> >> > my sample textbox value is as =First(Fields!ProductName.Value,
> >> > "Product_Set")
> >> >
> >> > Eventhough i removed First and tried still it is returning one
> >> > record...
> >> >
> >> > Pls help
> >> >
> >> > thx
> >>
> >>
> >>
>
>sql
I am designing report using report designer...I have defined two dataset..
First Dataset is just returning all the Category(i.e select CategoryName
from Category)
and in the second dataset i am passing that CategoryName to get all the
products belongs to that category...
So basically CagegoryName is my dropdown list to select category... Now
whiile is designing the report in the report body i have drag and drop all
the fields of second dataset...with their lebels.. but when i preview the
report i am just able to see first record even though each category has
atleast 10 products...
my sample textbox value is as =First(Fields!ProductName.Value, "Product_Set")
Eventhough i removed First and tried still it is returning one record...
Pls help
thxWhat you want in this case is to have one report with another report
embedded as a subreport. You will want to read up on subreports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:4BE1702E-B131-4B03-8CF7-6508727B26AE@.microsoft.com...
> Hello EveryBody,
> I am designing report using report designer...I have defined two dataset..
> First Dataset is just returning all the Category(i.e select CategoryName
> from Category)
> and in the second dataset i am passing that CategoryName to get all the
> products belongs to that category...
> So basically CagegoryName is my dropdown list to select category... Now
> whiile is designing the report in the report body i have drag and drop all
> the fields of second dataset...with their lebels.. but when i preview the
> report i am just able to see first record even though each category has
> atleast 10 products...
> my sample textbox value is as =First(Fields!ProductName.Value,
> "Product_Set")
> Eventhough i removed First and tried still it is returning one record...
> Pls help
> thx|||It is not a subreport.. It is just selecting a CategoryName from the dropdown
list on the top bar and passing that value to second dataset..
"Bruce L-C [MVP]" wrote:
> What you want in this case is to have one report with another report
> embedded as a subreport. You will want to read up on subreports.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:4BE1702E-B131-4B03-8CF7-6508727B26AE@.microsoft.com...
> > Hello EveryBody,
> > I am designing report using report designer...I have defined two dataset..
> > First Dataset is just returning all the Category(i.e select CategoryName
> > from Category)
> > and in the second dataset i am passing that CategoryName to get all the
> > products belongs to that category...
> >
> > So basically CagegoryName is my dropdown list to select category... Now
> > whiile is designing the report in the report body i have drag and drop all
> > the fields of second dataset...with their lebels.. but when i preview the
> > report i am just able to see first record even though each category has
> > atleast 10 products...
> >
> > my sample textbox value is as =First(Fields!ProductName.Value,
> > "Product_Set")
> >
> > Eventhough i removed First and tried still it is returning one record...
> >
> > Pls help
> >
> > thx
>
>|||It sounds to me like one dataset is just for the dropdown for the parameter
to list. Then that gets passed as a parameter to the second dataset that you
want to display. My guess is that you are dropping textboxes on the report
design surface. What you want to do is drop a table (it will default to 3
fields). Then drag and drop fields onto it. Add additional columns by doing
a right mouse click on the table.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:1588A17A-D5BE-4325-A5B3-F979E03E0642@.microsoft.com...
> It is not a subreport.. It is just selecting a CategoryName from the
> dropdown
> list on the top bar and passing that value to second dataset..
> "Bruce L-C [MVP]" wrote:
>> What you want in this case is to have one report with another report
>> embedded as a subreport. You will want to read up on subreports.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "mvp" <mvp@.discussions.microsoft.com> wrote in message
>> news:4BE1702E-B131-4B03-8CF7-6508727B26AE@.microsoft.com...
>> > Hello EveryBody,
>> > I am designing report using report designer...I have defined two
>> > dataset..
>> > First Dataset is just returning all the Category(i.e select
>> > CategoryName
>> > from Category)
>> > and in the second dataset i am passing that CategoryName to get all the
>> > products belongs to that category...
>> >
>> > So basically CagegoryName is my dropdown list to select category... Now
>> > whiile is designing the report in the report body i have drag and drop
>> > all
>> > the fields of second dataset...with their lebels.. but when i preview
>> > the
>> > report i am just able to see first record even though each category has
>> > atleast 10 products...
>> >
>> > my sample textbox value is as =First(Fields!ProductName.Value,
>> > "Product_Set")
>> >
>> > Eventhough i removed First and tried still it is returning one
>> > record...
>> >
>> > Pls help
>> >
>> > thx
>>|||Yes that was the problem.. It is working now... Thanks
"Bruce L-C [MVP]" wrote:
> It sounds to me like one dataset is just for the dropdown for the parameter
> to list. Then that gets passed as a parameter to the second dataset that you
> want to display. My guess is that you are dropping textboxes on the report
> design surface. What you want to do is drop a table (it will default to 3
> fields). Then drag and drop fields onto it. Add additional columns by doing
> a right mouse click on the table.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:1588A17A-D5BE-4325-A5B3-F979E03E0642@.microsoft.com...
> > It is not a subreport.. It is just selecting a CategoryName from the
> > dropdown
> > list on the top bar and passing that value to second dataset..
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> What you want in this case is to have one report with another report
> >> embedded as a subreport. You will want to read up on subreports.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> >> news:4BE1702E-B131-4B03-8CF7-6508727B26AE@.microsoft.com...
> >> > Hello EveryBody,
> >> > I am designing report using report designer...I have defined two
> >> > dataset..
> >> > First Dataset is just returning all the Category(i.e select
> >> > CategoryName
> >> > from Category)
> >> > and in the second dataset i am passing that CategoryName to get all the
> >> > products belongs to that category...
> >> >
> >> > So basically CagegoryName is my dropdown list to select category... Now
> >> > whiile is designing the report in the report body i have drag and drop
> >> > all
> >> > the fields of second dataset...with their lebels.. but when i preview
> >> > the
> >> > report i am just able to see first record even though each category has
> >> > atleast 10 products...
> >> >
> >> > my sample textbox value is as =First(Fields!ProductName.Value,
> >> > "Product_Set")
> >> >
> >> > Eventhough i removed First and tried still it is returning one
> >> > record...
> >> >
> >> > Pls help
> >> >
> >> > thx
> >>
> >>
> >>
>
>sql
Report not reflecting Dataset
My SSRS 2005 Dataset refreshes fine but my report, even though I refresh isn't really picking it up 100%...it's missing an account but I know that account shows in my dataset in VS. I only am using one filter and that is fine...has no affect.
Is there a specific way to refresh my report after my dataset besides the refresh button for the report itself?
If you run the report in report designer, it will cache the data to a local file on disk (e.g. report1.rdl.data). The .rdl.data file should be refreshed if you click the green refresh icon in preview. If this doesn't work, you can always close report designer/VS, delete the .rdl.data file from disk and it will recreate that file the next you run the report by reexecuting queries.
-- Robert
|||thanks a bunch.|||Thanks for the info on the .rdl.data, but I don't have such a file.
Two (out of three) datasets (ds) on my report are not refreshing, and if you try to run them, they err. The third dataset works fine. And, another report with 3 other datasets, works fine.
I have verified all the way back to the stored procs (SP) for the name, and a syntax check. And, in query analyzer they run.
When you look at a good dataset window, you have the SP dropdown on the upper right, which if the dataset result is displayed, the drop down is not available.
Then there's a light yellow sql section that just says that the ds 'uses' the SP name.
On one of the non working ds's, the sql section is white, has my SP name in it as tho it was sql code, and the drop down says it's an SP and shows the correct name.
I also tried a brand new project with just one of the offending ds's, and it didn't refresh or run there either.
Halp.
sqlWednesday, March 7, 2012
Report item not linked to a dataset
I have a need to add a text box to a report. The text box will have an
expression that I need to have that captues the value of a field from a
second data set associated with the report. I add a text box but in the edit
expresssion process I can't access any fields (from any data set) and i get
the message Report item not linked to a dataset. I'm not trying to add a new
table or anything like that. I just want to be able to grab that field value
and disply.
--
Thanks, JimOn Sep 21, 11:38 am, Jim B <J...@.lightning.com> wrote:
> I have a need to add a text box to a report. The text box will have an
> expression that I need to have that captues the value of a field from a
> second data set associated with the report. I add a text box but in the edit
> expresssion process I can't access any fields (from any data set) and i get
> the message Report item not linked to a dataset. I'm not trying to add a new
> table or anything like that. I just want to be able to grab that field value
> and disply.
> --
> Thanks, Jim
If I'm understanding you correctly, you should be able to reference a
dataset via an aggregate expression. Something like this should work.
=Max(Fields!SomeFieldName.Value, "SomeDatasetName")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||That's exactly what I needed to know. Thanks for your response to my question.
--
Thanks, Jim
"EMartinez" wrote:
> On Sep 21, 11:38 am, Jim B <J...@.lightning.com> wrote:
> > I have a need to add a text box to a report. The text box will have an
> > expression that I need to have that captues the value of a field from a
> > second data set associated with the report. I add a text box but in the edit
> > expresssion process I can't access any fields (from any data set) and i get
> > the message Report item not linked to a dataset. I'm not trying to add a new
> > table or anything like that. I just want to be able to grab that field value
> > and disply.
> > --
> > Thanks, Jim
>
> If I'm understanding you correctly, you should be able to reference a
> dataset via an aggregate expression. Something like this should work.
> =Max(Fields!SomeFieldName.Value, "SomeDatasetName")
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Sep 23, 9:20 am, Jim B <J...@.lightning.com> wrote:
> That's exactly what I needed to know. Thanks for your response to my question.
> --
> Thanks, Jim
> "EMartinez" wrote:
> > On Sep 21, 11:38 am, Jim B <J...@.lightning.com> wrote:
> > > I have a need to add a text box to a report. The text box will have an
> > > expression that I need to have that captues the value of a field from a
> > > second data set associated with the report. I add a text box but in the edit
> > > expresssion process I can't access any fields (from any data set) and i get
> > > the message Report item not linked to a dataset. I'm not trying to add a new
> > > table or anything like that. I just want to be able to grab that field value
> > > and disply.
> > > --
> > > Thanks, Jim
> > If I'm understanding you correctly, you should be able to reference a
> > dataset via an aggregate expression. Something like this should work.
> > =Max(Fields!SomeFieldName.Value, "SomeDatasetName")
> > Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Glad I could help.
Regards,
Enrique Martinez
Sr. Software Consultant
expression that I need to have that captues the value of a field from a
second data set associated with the report. I add a text box but in the edit
expresssion process I can't access any fields (from any data set) and i get
the message Report item not linked to a dataset. I'm not trying to add a new
table or anything like that. I just want to be able to grab that field value
and disply.
--
Thanks, JimOn Sep 21, 11:38 am, Jim B <J...@.lightning.com> wrote:
> I have a need to add a text box to a report. The text box will have an
> expression that I need to have that captues the value of a field from a
> second data set associated with the report. I add a text box but in the edit
> expresssion process I can't access any fields (from any data set) and i get
> the message Report item not linked to a dataset. I'm not trying to add a new
> table or anything like that. I just want to be able to grab that field value
> and disply.
> --
> Thanks, Jim
If I'm understanding you correctly, you should be able to reference a
dataset via an aggregate expression. Something like this should work.
=Max(Fields!SomeFieldName.Value, "SomeDatasetName")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||That's exactly what I needed to know. Thanks for your response to my question.
--
Thanks, Jim
"EMartinez" wrote:
> On Sep 21, 11:38 am, Jim B <J...@.lightning.com> wrote:
> > I have a need to add a text box to a report. The text box will have an
> > expression that I need to have that captues the value of a field from a
> > second data set associated with the report. I add a text box but in the edit
> > expresssion process I can't access any fields (from any data set) and i get
> > the message Report item not linked to a dataset. I'm not trying to add a new
> > table or anything like that. I just want to be able to grab that field value
> > and disply.
> > --
> > Thanks, Jim
>
> If I'm understanding you correctly, you should be able to reference a
> dataset via an aggregate expression. Something like this should work.
> =Max(Fields!SomeFieldName.Value, "SomeDatasetName")
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Sep 23, 9:20 am, Jim B <J...@.lightning.com> wrote:
> That's exactly what I needed to know. Thanks for your response to my question.
> --
> Thanks, Jim
> "EMartinez" wrote:
> > On Sep 21, 11:38 am, Jim B <J...@.lightning.com> wrote:
> > > I have a need to add a text box to a report. The text box will have an
> > > expression that I need to have that captues the value of a field from a
> > > second data set associated with the report. I add a text box but in the edit
> > > expresssion process I can't access any fields (from any data set) and i get
> > > the message Report item not linked to a dataset. I'm not trying to add a new
> > > table or anything like that. I just want to be able to grab that field value
> > > and disply.
> > > --
> > > Thanks, Jim
> > If I'm understanding you correctly, you should be able to reference a
> > dataset via an aggregate expression. Something like this should work.
> > =Max(Fields!SomeFieldName.Value, "SomeDatasetName")
> > Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Glad I could help.
Regards,
Enrique Martinez
Sr. Software Consultant
report item not linked to a dataset
Hi,
I'm using SQLServer 2000 and Visual Studio 2003.
Today all the sudden in Visual Studio the field list pane is empty for all
reports I have created. When I try to ad a field using Expressions, I receive
the message "report item not linked to a dataset". As a source for the
dataset I use a storede procedure.
The reports can still be used. I just cannot properly edit them anymore.
When l look at the xml code, the fields are in the report.
--
JanHi Jan
Have you tried right-clicking on the table in the report, going to properties,
then linking it to a dataset manually?
I've never had reports suddenly lose their datasets like that, but there are
often times when I add a table to a report and it isn't automatically linked
to a dataset.
Jan Hussaarts wrote:
>Hi,
>I'm using SQLServer 2000 and Visual Studio 2003.
>Today all the sudden in Visual Studio the field list pane is empty for all
>reports I have created. When I try to ad a field using Expressions, I receive
>the message "report item not linked to a dataset". As a source for the
>dataset I use a storede procedure.
>The reports can still be used. I just cannot properly edit them anymore.
>When l look at the xml code, the fields are in the report.|||The point is that normally there is a Fields pane on the left of the screen.
Add a table and you can drop a Field from the Fields pane on a cell in the
table. The field pane remains empty after manually linking the table to the
dataset.|||Have you checked your dataset to make sure it can still access the data?
If there is no dataset, or if you don't have access to the data for whatever
reason (maybe your user account doesn't have authority to view the data) then
the fields pane will remain empty, since RS can't actually see the fields
returned by the dataset query.
Jan Hussaarts wrote:
>The point is that normally there is a Fields pane on the left of the screen.
>Add a table and you can drop a Field from the Fields pane on a cell in the
>table. The field pane remains empty after manually linking the table to the
>dataset.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200509/1|||When I look at de rdl version of the report, under < Datasets> the dataset
and all the fields that are output of the stored procedure are there. So,
access rights are ok. It is just the Fields pane that remains empty.
I'm thinking of uninstalling and installing RS. I think that might solve the
problem. It has taken a lot of my time up till now.|||unable to add a field in report viewer which is not selected in datase
From http://www.developmentnow.com/g/115_2005_9_0_0_598578/report-item-not-linked-to-a-dataset.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com|||unable to add a field in report viewer which is not selected in datase
From http://www.developmentnow.com/g/115_2005_9_0_0_598578/report-item-not-linked-to-a-dataset.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com|||In the future post your problem. Don't expect us to follow a link. I almost
just ignored this.
Go into the dataset that is no longer showing the field list and click on
the refresh fields button (it is to the right of the ...). See if that
brings back the field list.
This sometimes occurs with stored procedures. If it does not bring back the
list then it is possible that somebody has modified that stored procedure in
such a way that it causes a problem.
First see if this fixes your problem.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sunil tripathi" <sunil137_bpl@.yahoo.com> wrote in message
news:cbbcde3f-6750-4e12-bc8c-9a0d62474c18@.developmentnow.com...
> unable to add a field in report viewer which is not selected in dataset
> From
> http://www.developmentnow.com/g/115_2005_9_0_0_598578/report-item-not-linked-to-a-dataset.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
I'm using SQLServer 2000 and Visual Studio 2003.
Today all the sudden in Visual Studio the field list pane is empty for all
reports I have created. When I try to ad a field using Expressions, I receive
the message "report item not linked to a dataset". As a source for the
dataset I use a storede procedure.
The reports can still be used. I just cannot properly edit them anymore.
When l look at the xml code, the fields are in the report.
--
JanHi Jan
Have you tried right-clicking on the table in the report, going to properties,
then linking it to a dataset manually?
I've never had reports suddenly lose their datasets like that, but there are
often times when I add a table to a report and it isn't automatically linked
to a dataset.
Jan Hussaarts wrote:
>Hi,
>I'm using SQLServer 2000 and Visual Studio 2003.
>Today all the sudden in Visual Studio the field list pane is empty for all
>reports I have created. When I try to ad a field using Expressions, I receive
>the message "report item not linked to a dataset". As a source for the
>dataset I use a storede procedure.
>The reports can still be used. I just cannot properly edit them anymore.
>When l look at the xml code, the fields are in the report.|||The point is that normally there is a Fields pane on the left of the screen.
Add a table and you can drop a Field from the Fields pane on a cell in the
table. The field pane remains empty after manually linking the table to the
dataset.|||Have you checked your dataset to make sure it can still access the data?
If there is no dataset, or if you don't have access to the data for whatever
reason (maybe your user account doesn't have authority to view the data) then
the fields pane will remain empty, since RS can't actually see the fields
returned by the dataset query.
Jan Hussaarts wrote:
>The point is that normally there is a Fields pane on the left of the screen.
>Add a table and you can drop a Field from the Fields pane on a cell in the
>table. The field pane remains empty after manually linking the table to the
>dataset.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200509/1|||When I look at de rdl version of the report, under < Datasets> the dataset
and all the fields that are output of the stored procedure are there. So,
access rights are ok. It is just the Fields pane that remains empty.
I'm thinking of uninstalling and installing RS. I think that might solve the
problem. It has taken a lot of my time up till now.|||unable to add a field in report viewer which is not selected in datase
From http://www.developmentnow.com/g/115_2005_9_0_0_598578/report-item-not-linked-to-a-dataset.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com|||unable to add a field in report viewer which is not selected in datase
From http://www.developmentnow.com/g/115_2005_9_0_0_598578/report-item-not-linked-to-a-dataset.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com|||In the future post your problem. Don't expect us to follow a link. I almost
just ignored this.
Go into the dataset that is no longer showing the field list and click on
the refresh fields button (it is to the right of the ...). See if that
brings back the field list.
This sometimes occurs with stored procedures. If it does not bring back the
list then it is possible that somebody has modified that stored procedure in
such a way that it causes a problem.
First see if this fixes your problem.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sunil tripathi" <sunil137_bpl@.yahoo.com> wrote in message
news:cbbcde3f-6750-4e12-bc8c-9a0d62474c18@.developmentnow.com...
> unable to add a field in report viewer which is not selected in dataset
> From
> http://www.developmentnow.com/g/115_2005_9_0_0_598578/report-item-not-linked-to-a-dataset.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
Saturday, February 25, 2012
report in SQL 2005 problem
Hi,
I am using the Business Intelligence Development studio to create a
simple report in SQL 2005 server. I have created a dataset and tried to
connect to a database and it failed to connect. An error is given out and
said that it cannot connect to the database because the server does not
allow remote connection and error 40. Can anyone tell me how to enable
"allow remote connection" in a database or how to solve this problem?
Thanks.
Use the "SQL Server Surface Area Configuration" tool.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"00KobeBrian" <a@.b.com> wrote in message news:%23shnTtR6GHA.4348@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I am using the Business Intelligence Development studio to create a
> simple report in SQL 2005 server. I have created a dataset and tried to
> connect to a database and it failed to connect. An error is given out and
> said that it cannot connect to the database because the server does not
> allow remote connection and error 40. Can anyone tell me how to enable
> "allow remote connection" in a database or how to solve this problem?
> Thanks.
>
|||Hi,
I tried to select "Local and remote connection" - "TCP/IP" and
"TCP/IP and named pipe" option and run again. But still got the same error.
Can anyone please help? Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ue7WsZV6GHA.3452@.TK2MSFTNGP05.phx.gbl...[vbcol=seagreen]
> Use the "SQL Server Surface Area Configuration" tool.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "00KobeBrian" <a@.b.com> wrote in message
> news:%23shnTtR6GHA.4348@.TK2MSFTNGP03.phx.gbl...
|||Sorry Folks. I got it. I just didn't point to the correct server. Cheers
"00KobeBrian" <a@.b.com> wrote in message
news:uhhxA8z6GHA.4644@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I tried to select "Local and remote connection" - "TCP/IP" and
> "TCP/IP and named pipe" option and run again. But still got the same
> error. Can anyone please help? Thanks.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:ue7WsZV6GHA.3452@.TK2MSFTNGP05.phx.gbl...
>
I am using the Business Intelligence Development studio to create a
simple report in SQL 2005 server. I have created a dataset and tried to
connect to a database and it failed to connect. An error is given out and
said that it cannot connect to the database because the server does not
allow remote connection and error 40. Can anyone tell me how to enable
"allow remote connection" in a database or how to solve this problem?
Thanks.
Use the "SQL Server Surface Area Configuration" tool.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"00KobeBrian" <a@.b.com> wrote in message news:%23shnTtR6GHA.4348@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I am using the Business Intelligence Development studio to create a
> simple report in SQL 2005 server. I have created a dataset and tried to
> connect to a database and it failed to connect. An error is given out and
> said that it cannot connect to the database because the server does not
> allow remote connection and error 40. Can anyone tell me how to enable
> "allow remote connection" in a database or how to solve this problem?
> Thanks.
>
|||Hi,
I tried to select "Local and remote connection" - "TCP/IP" and
"TCP/IP and named pipe" option and run again. But still got the same error.
Can anyone please help? Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ue7WsZV6GHA.3452@.TK2MSFTNGP05.phx.gbl...[vbcol=seagreen]
> Use the "SQL Server Surface Area Configuration" tool.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "00KobeBrian" <a@.b.com> wrote in message
> news:%23shnTtR6GHA.4348@.TK2MSFTNGP03.phx.gbl...
|||Sorry Folks. I got it. I just didn't point to the correct server. Cheers
"00KobeBrian" <a@.b.com> wrote in message
news:uhhxA8z6GHA.4644@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I tried to select "Local and remote connection" - "TCP/IP" and
> "TCP/IP and named pipe" option and run again. But still got the same
> error. Can anyone please help? Thanks.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:ue7WsZV6GHA.3452@.TK2MSFTNGP05.phx.gbl...
>
report in SQL 2005 problem
Hi,
I am using the Business Intelligence Development studio to create a
simple report in SQL 2005 server. I have created a dataset and tried to
connect to a database and it failed to connect. An error is given out and
said that it cannot connect to the database because the server does not
allow remote connection and error 40. Can anyone tell me how to enable
"allow remote connection" in a database or how to solve this problem?
Thanks.Use the "SQL Server Surface Area Configuration" tool.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"00KobeBrian" <a@.b.com> wrote in message news:%23shnTtR6GHA.4348@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I am using the Business Intelligence Development studio to create a
> simple report in SQL 2005 server. I have created a dataset and tried to
> connect to a database and it failed to connect. An error is given out and
> said that it cannot connect to the database because the server does not
> allow remote connection and error 40. Can anyone tell me how to enable
> "allow remote connection" in a database or how to solve this problem?
> Thanks.
>|||Hi,
I tried to select "Local and remote connection" - "TCP/IP" and
"TCP/IP and named pipe" option and run again. But still got the same error.
Can anyone please help? Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ue7WsZV6GHA.3452@.TK2MSFTNGP05.phx.gbl...
> Use the "SQL Server Surface Area Configuration" tool.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "00KobeBrian" <a@.b.com> wrote in message
> news:%23shnTtR6GHA.4348@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I am using the Business Intelligence Development studio to create
>> a simple report in SQL 2005 server. I have created a dataset and tried to
>> connect to a database and it failed to connect. An error is given out and
>> said that it cannot connect to the database because the server does not
>> allow remote connection and error 40. Can anyone tell me how to enable
>> "allow remote connection" in a database or how to solve this problem?
>> Thanks.|||Sorry Folks. I got it. I just didn't point to the correct server. Cheers
"00KobeBrian" <a@.b.com> wrote in message
news:uhhxA8z6GHA.4644@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I tried to select "Local and remote connection" - "TCP/IP" and
> "TCP/IP and named pipe" option and run again. But still got the same
> error. Can anyone please help? Thanks.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:ue7WsZV6GHA.3452@.TK2MSFTNGP05.phx.gbl...
>> Use the "SQL Server Surface Area Configuration" tool.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "00KobeBrian" <a@.b.com> wrote in message
>> news:%23shnTtR6GHA.4348@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I am using the Business Intelligence Development studio to
>> create a simple report in SQL 2005 server. I have created a dataset and
>> tried to connect to a database and it failed to connect. An error is
>> given out and said that it cannot connect to the database because the
>> server does not allow remote connection and error 40. Can anyone tell me
>> how to enable "allow remote connection" in a database or how to solve
>> this problem? Thanks.
>
I am using the Business Intelligence Development studio to create a
simple report in SQL 2005 server. I have created a dataset and tried to
connect to a database and it failed to connect. An error is given out and
said that it cannot connect to the database because the server does not
allow remote connection and error 40. Can anyone tell me how to enable
"allow remote connection" in a database or how to solve this problem?
Thanks.Use the "SQL Server Surface Area Configuration" tool.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"00KobeBrian" <a@.b.com> wrote in message news:%23shnTtR6GHA.4348@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I am using the Business Intelligence Development studio to create a
> simple report in SQL 2005 server. I have created a dataset and tried to
> connect to a database and it failed to connect. An error is given out and
> said that it cannot connect to the database because the server does not
> allow remote connection and error 40. Can anyone tell me how to enable
> "allow remote connection" in a database or how to solve this problem?
> Thanks.
>|||Hi,
I tried to select "Local and remote connection" - "TCP/IP" and
"TCP/IP and named pipe" option and run again. But still got the same error.
Can anyone please help? Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ue7WsZV6GHA.3452@.TK2MSFTNGP05.phx.gbl...
> Use the "SQL Server Surface Area Configuration" tool.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "00KobeBrian" <a@.b.com> wrote in message
> news:%23shnTtR6GHA.4348@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I am using the Business Intelligence Development studio to create
>> a simple report in SQL 2005 server. I have created a dataset and tried to
>> connect to a database and it failed to connect. An error is given out and
>> said that it cannot connect to the database because the server does not
>> allow remote connection and error 40. Can anyone tell me how to enable
>> "allow remote connection" in a database or how to solve this problem?
>> Thanks.|||Sorry Folks. I got it. I just didn't point to the correct server. Cheers
"00KobeBrian" <a@.b.com> wrote in message
news:uhhxA8z6GHA.4644@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I tried to select "Local and remote connection" - "TCP/IP" and
> "TCP/IP and named pipe" option and run again. But still got the same
> error. Can anyone please help? Thanks.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:ue7WsZV6GHA.3452@.TK2MSFTNGP05.phx.gbl...
>> Use the "SQL Server Surface Area Configuration" tool.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "00KobeBrian" <a@.b.com> wrote in message
>> news:%23shnTtR6GHA.4348@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I am using the Business Intelligence Development studio to
>> create a simple report in SQL 2005 server. I have created a dataset and
>> tried to connect to a database and it failed to connect. An error is
>> given out and said that it cannot connect to the database because the
>> server does not allow remote connection and error 40. Can anyone tell me
>> how to enable "allow remote connection" in a database or how to solve
>> this problem? Thanks.
>
report in SQL 2005 problem
Hi,
I am using the Business Intelligence Development studio to create a
simple report in SQL 2005 server. I have created a dataset and tried to
connect to a database and it failed to connect. An error is given out and
said that it cannot connect to the database because the server does not
allow remote connection and error 40. Can anyone tell me how to enable
"allow remote connection" in a database or how to solve this problem?
Thanks.Use the "SQL Server Surface Area Configuration" tool.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"00KobeBrian" <a@.b.com> wrote in message news:%23shnTtR6GHA.4348@.TK2MSFTNGP03.phx.gbl...[vbc
ol=seagreen]
> Hi,
> I am using the Business Intelligence Development studio to create
a
> simple report in SQL 2005 server. I have created a dataset and tried to
> connect to a database and it failed to connect. An error is given out and
> said that it cannot connect to the database because the server does not
> allow remote connection and error 40. Can anyone tell me how to enable
> "allow remote connection" in a database or how to solve this problem?
> Thanks.
>[/vbcol]|||Hi,
I tried to select "Local and remote connection" - "TCP/IP" and
"TCP/IP and named pipe" option and run again. But still got the same error.
Can anyone please help? Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ue7WsZV6GHA.3452@.TK2MSFTNGP05.phx.gbl...[vbcol=seagreen]
> Use the "SQL Server Surface Area Configuration" tool.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "00KobeBrian" <a@.b.com> wrote in message
> news:%23shnTtR6GHA.4348@.TK2MSFTNGP03.phx.gbl...|||Sorry Folks. I got it. I just didn't point to the correct server. Cheers
"00KobeBrian" <a@.b.com> wrote in message
news:uhhxA8z6GHA.4644@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I tried to select "Local and remote connection" - "TCP/IP" and
> "TCP/IP and named pipe" option and run again. But still got the same
> error. Can anyone please help? Thanks.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:ue7WsZV6GHA.3452@.TK2MSFTNGP05.phx.gbl...
>
I am using the Business Intelligence Development studio to create a
simple report in SQL 2005 server. I have created a dataset and tried to
connect to a database and it failed to connect. An error is given out and
said that it cannot connect to the database because the server does not
allow remote connection and error 40. Can anyone tell me how to enable
"allow remote connection" in a database or how to solve this problem?
Thanks.Use the "SQL Server Surface Area Configuration" tool.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"00KobeBrian" <a@.b.com> wrote in message news:%23shnTtR6GHA.4348@.TK2MSFTNGP03.phx.gbl...[vbc
ol=seagreen]
> Hi,
> I am using the Business Intelligence Development studio to create
a
> simple report in SQL 2005 server. I have created a dataset and tried to
> connect to a database and it failed to connect. An error is given out and
> said that it cannot connect to the database because the server does not
> allow remote connection and error 40. Can anyone tell me how to enable
> "allow remote connection" in a database or how to solve this problem?
> Thanks.
>[/vbcol]|||Hi,
I tried to select "Local and remote connection" - "TCP/IP" and
"TCP/IP and named pipe" option and run again. But still got the same error.
Can anyone please help? Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ue7WsZV6GHA.3452@.TK2MSFTNGP05.phx.gbl...[vbcol=seagreen]
> Use the "SQL Server Surface Area Configuration" tool.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "00KobeBrian" <a@.b.com> wrote in message
> news:%23shnTtR6GHA.4348@.TK2MSFTNGP03.phx.gbl...|||Sorry Folks. I got it. I just didn't point to the correct server. Cheers
"00KobeBrian" <a@.b.com> wrote in message
news:uhhxA8z6GHA.4644@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I tried to select "Local and remote connection" - "TCP/IP" and
> "TCP/IP and named pipe" option and run again. But still got the same
> error. Can anyone please help? Thanks.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:ue7WsZV6GHA.3452@.TK2MSFTNGP05.phx.gbl...
>
Subscribe to:
Posts (Atom)