Report Parameters

I am using stored procedures and I would like an input parameter to use a
different stored procedure from the report. Query access seems to be on the
report stored proc. I also don't want to run the report untill the user
selects parameters since it is long.
Thanks,
maaOn Nov 4, 7:30 am, maa <m...@.discussions.microsoft.com> wrote:
> I am using stored procedures and I would like an input parameter to use a
> different stored procedure from the report. Query access seems to be on the
> report stored proc. I also don't want to run the report untill the user
> selects parameters since it is long.
> Thanks,
> maa
In the Data tab of the BIDS environment, select the drop-down list box
to the right of 'Dataset:' and select '<New Dataset...>' change
'Command type:' to StoredProcedure and below 'Query string:' enter in
the new stored procedure that you want to use. Then select the
'Report' drop-down tab and select 'Report Parameters...' >> select the
'Add' button and below 'Available values:' select the dataset you just
created below 'Dataset:' The 'Value field:' will be what is used in
the report or elsewhere in another stored procedure (sometimes an
identity field in a table or primary key of sorts: but not always) and
the 'Label field:' is what is shown to the user to select from. To
delay the report running until after the parameter is selected, below
the 'Default values:' during the parameter creation process select
'Null.' Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Report parameters

I'm using a query to populate a drop down list for a report parameter. E.g. a
number of stores. I'm using this to get sales figures per store.
Now I need a way to show the sales figures for all stores. A user should for
example be able to skip the stores selection list to select them all...
I haven't figured out yet how to skip this, or some other solution to get
every store. When I do not select a store, RS complains there's no store
selected. Even when I select "allow null value" in the parameter options...
Can someone help me?Hi Andreas,
look here in the Newsgroup :
Report Parameters with a choice of "All"
mfg Georg
"Andreas" <Andreas@.discussions.microsoft.com> schrieb im Newsbeitrag
news:BC84B219-9A78-4E5F-8FC1-5CC1A4C175D1@.microsoft.com...
> I'm using a query to populate a drop down list for a report parameter.
E.g. a
> number of stores. I'm using this to get sales figures per store.
> Now I need a way to show the sales figures for all stores. A user should
for
> example be able to skip the stores selection list to select them all...
> I haven't figured out yet how to skip this, or some other solution to get
> every store. When I do not select a store, RS complains there's no store
> selected. Even when I select "allow null value" in the parameter
options...
> Can someone help me?
>|||thx georg
"Georg Schmelzer" wrote:
> Hi Andreas,
> look here in the Newsgroup :
> Report Parameters with a choice of "All"
>
> mfg Georg
>
>
> "Andreas" <Andreas@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:BC84B219-9A78-4E5F-8FC1-5CC1A4C175D1@.microsoft.com...
> > I'm using a query to populate a drop down list for a report parameter.
> E.g. a
> > number of stores. I'm using this to get sales figures per store.
> > Now I need a way to show the sales figures for all stores. A user should
> for
> > example be able to skip the stores selection list to select them all...
> > I haven't figured out yet how to skip this, or some other solution to get
> > every store. When I do not select a store, RS complains there's no store
> > selected. Even when I select "allow null value" in the parameter
> options...
> >
> > Can someone help me?
> >
>
>

Report parameter with datetime

Hi,

Anyone can help me?

I created a store proc with @.startdate and @.enddate

in query analyser - I run it as Exec SP_Admission '2006/01/01','2006/01/25' and it is ok.

Then I created a report in VS2005 and set the parameter as datetime in report parameter.

if I manually force to fill startdate and enddate with ‘yyyy/mm/dd’ format in preview, it works.

However if I choose the date from the given calendar(Date Time Picker), it doesn’t work

it is said that "The value provided the report parameter 'enddate' is not valid for its type.

Of course it not valid because it always comes with ‘dd/mm/yyyy’ format

I didn't setup the date time picker, it will automatically when I choose datetime as datatype.

any idea what should I do?

thanks,

Susan

Hi Susan,

not exactly brilliant, but

Exec SP_Admission convert(datetime,'12/10/2006',103),convert(datetime,'12/11/2006',103)
should work in your case, so put your parameter instead of '12/10/2006'
|||

Thanks...

someone suggested that in my SP -

I declare as varchar then in where statement I convert it as you mention.

however in report para - I can't set it as datetime but set to string.

it works ok but I can't use the date time picker .

well at least works that way...

Thanks so much

Report Parameter not working in subquery?

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

Report Paramert problem

I am trying to put a parameter on my report to select a field from my query but keep getting this error. What am I doing wrong?

[rsInvalidReportParameterDependency] The report parameter ‘Program’ has a DefaultValue or a ValidValue that depends on the report parameter “Program”. Forward dependencies are not valid.

Build complete -- 1 errors, 0 warnings

Hi,

For the report parameter 'Program',On the Data tab Select on the Dataset name through which you are sending this 'Program' parameter to the database and click on this symbol "..." adjacent to the dataSet selection drop down list.Now a dataset window opens,From that window click on the Parameters tab.Parameters:

Name Value

If the parameter Name:@.Program

Value:Parameters!Program.Value

then you get this error.

Hope this helps.

|||

Mr4100

[rsInvalidReportParameterDependency] The report parameter ‘Program’ has a DefaultValue or a ValidValue that depends on the report parameter “Program”. Forward dependencies are not valid.

This problem comes from when you are using a parameter that has reference to another parameter. It appears that "Program" is depended on another parameter in your report. The solution is to move the "Program" parameter in the parameterlist ahead or the other parameter.

For Example: if City is depend on State

@.Date

@.City

@.State this will product the above error

Change to this:

@.Date

@.State

@.City

This will fix the problem

Ham

|||

I can't move this ahead or anything this is the only parameter I have. I am trying to assign a value from my dataset back to the parameter using a prompt. I want the user to be able to select their program which would then compare the parameter value to the dataset value and return the correct data in the report atleast this how I think it should work. I've tried using a filter in the dataset properties but it won't work either.

|||I don't understand Mahima, this is what I have done and the reason I'm getting the error?|||

mr4100.

Parameters are evaluated and executed before your data is return from the dataset, so I would suggest on your parameter "default values" - do a returnset of all avaliable problems. Also, you should be able to pass this parameter value into your stored procedure to filter only for the "programs" selected. Hint, go to data tab, select "...", select the parameter tab and make sure that the parameter your passing to your Stored Procedure to the same name as your report parameter.

Ham

|||Sorry should read "available programs"|||

I took a different route and figured it out.

Thanks.

Report pagination for charts

Hi,

We have developed few reports displaying data using chart layout. In the Data tab, we have specified MDX query that will return top 10 records.

But now, instead of restricting to just top 10 records, we would like to display all records and go in for pagination.

Is there some setting in the chart properties, where in I can display the first n records in first page, and the next n records( if available) in the next page and so on?

Can I specify the value of n somewhere in the propeties?

I read through many posts regarding pagination but those couldn't help me much.

Please help me in solving this problem.

Any help would be appreciated.

Thanks in advance!

No response yet!:(|||

One approach is the following:

Step 1: Add a table to the report
Step 2: Group by a number of rows
Right-click on the table and select Properties. Add a table group (with a group header, but no group footer)
Enter this for the group expression: =Ceiling(RowNumber(Nothing)/10)
This will cause the table to group on every ten rows. So you'll get a separate table group for every ten rows.
Step 3: Add a chart in the table group header
Design your chart.

Note: this approach is similar to the table inline charts approach discussed in the following whitepaper: http://msdn2.microsoft.com/en-us/library/aa964128.aspx

-- Robert

|||

Thanks a lot Robert!

I got the idea from the solution provided by you and did the following:

Step 1: Add a list to the report

Step 2: Added group expression for the list to group the data

Grouping =Ceiling(RowNumber(Nothing)/10)

Set the following properties for list:

KeepTogether: False

PageBreakAtEnd: True

Step 3: Added chart to the list

Set the property, PageBreakAtEnd: True for the chart.

The above steps solved my problem of report pagination for charts.

Thanks once again!

sql

Report pagination for charts

Hi,

We have developed few reports displaying data using chart layout. In the Data tab, we have specified MDX query that will return top 10 records.

But now, instead of restricting to just top 10 records, we would like to display all records and go in for pagination.

Is there some setting in the chart properties, where in I can display the first n records in first page, and the next n records( if available) in the next page and so on?

Can I specify the value of n somewhere in the propeties?

I read through many posts regarding pagination but those couldn't help me much.

Please help me in solving this problem.

Any help would be appreciated.

Thanks in advance!

No response yet!:(|||

One approach is the following:

Step 1: Add a table to the report
Step 2: Group by a number of rows
Right-click on the table and select Properties. Add a table group (with a group header, but no group footer)
Enter this for the group expression: =Ceiling(RowNumber(Nothing)/10)
This will cause the table to group on every ten rows. So you'll get a separate table group for every ten rows.
Step 3: Add a chart in the table group header
Design your chart.

Note: this approach is similar to the table inline charts approach discussed in the following whitepaper: http://msdn2.microsoft.com/en-us/library/aa964128.aspx

-- Robert

|||

Thanks a lot Robert!

I got the idea from the solution provided by you and did the following:

Step 1: Add a list to the report

Step 2: Added group expression for the list to group the data

Grouping =Ceiling(RowNumber(Nothing)/10)

Set the following properties for list:

KeepTogether: False

PageBreakAtEnd: True

Step 3: Added chart to the list

Set the property, PageBreakAtEnd: True for the chart.

The above steps solved my problem of report pagination for charts.

Thanks once again!

Report Model Timeout

When building a report model on a large set of data I constantly get a
timeouts before it complets building. I've changed the query timeout on the
data source to both 600 seconds (the max) and 0 seconds (which should be no
timeout) and I still get timeouts. I installed SP1 for SQL Server 2005
because it talked about something similiar to this issue but it didn't fix
this issue. I still get a timeout and the model is not built. Is there
anything else I can try and is this expected behavior?
Thanks.No one has timeouts when building models against large tables'
"Aaron" wrote:
> When building a report model on a large set of data I constantly get a
> timeouts before it complets building. I've changed the query timeout on the
> data source to both 600 seconds (the max) and 0 seconds (which should be no
> timeout) and I still get timeouts. I installed SP1 for SQL Server 2005
> because it talked about something similiar to this issue but it didn't fix
> this issue. I still get a timeout and the model is not built. Is there
> anything else I can try and is this expected behavior?
> Thanks.|||you need to give it more than 13 minutes, buddy
when you say.. large tables; you're talking about a few, maybe a dozen
tables; with like a million records?
give us some scope; ok?
-Aaron
Aaron wrote:
> No one has timeouts when building models against large tables'
> "Aaron" wrote:
> > When building a report model on a large set of data I constantly get a
> > timeouts before it complets building. I've changed the query timeout on the
> > data source to both 600 seconds (the max) and 0 seconds (which should be no
> > timeout) and I still get timeouts. I installed SP1 for SQL Server 2005
> > because it talked about something similiar to this issue but it didn't fix
> > this issue. I still get a timeout and the model is not built. Is there
> > anything else I can try and is this expected behavior?
> >
> > Thanks.sql

Report item expression can only refer to fields within the current data set scope

I switched my reports datasource and made sure the new query outputs
the same fields. However i now get this error...
Report item expression can only refer to fields within the current
data set scope or, if inside an aggregate, the specified data set
scope
I've checked everywhere to confirm I'm pointing to the right dataset
and I am.On Oct 10, 9:09 pm, jobs <j...@.webdos.com> wrote:
> I switched my reports datasource and made sure the new query outputs
> the same fields. However i now get this error...
> Report item expression can only refer to fields within the current
> data set scope or, if inside an aggregate, the specified data set
> scope
> I've checked everywhere to confirm I'm pointing to the right dataset
> and I am.
You might want to select the Refresh icon in the Data view. Also, if
you are using a parameter in the dataset, you will want to make sure
that it is mapped correctly (via the Data view >> Edit Selected
Dataset [...] >> Parameters tab). Another thing to check is to make
sure that you did not accidentally misspell one of the dataset fields
since the previous dataset. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Report is blank, but QA returns results

I've tested a query in Query Analyzer and it returns the desired results.
However, the same query in a SQL report finishes without errors but the
report is completely blank. Similarly formatted reports work fine. Here's the
query:
SELECT CRMAF_Task.modifiedonutc, CRMAF_Task.modifiedbyname,
CRMAF_Task.owneridname, CRMAF_Task.subject, CRMAF_Task.statecodename,
CRMAF_Task.description, CRMAF_Task.regardingobjectid,
CRMAF_Notes.subject AS NotesSubj, CRMAF_Notes.notetext AS NoteText,
CRMAF_Notes.createdonutc
FROM FilteredTask CRMAF_Task LEFT JOIN
FilteredAnnotation CRMAF_Notes ON
CRMAF_Task.activityid = CRMAF_Notes.objectid
WHERE (CRMAF_Task.subject LIKE '%reb%') AND
(CRMAF_Task.regardingobjectid = 'D5C79CEC-D3AF-DB11-86DF-0003FFDAF900') AND
(CRMAF_Task.statecode = 1)
Anyone have any ideas?
--
Matt Wittemann, CRM MVP
http://icu-mscrm.blogspot.comOn Feb 19, 6:12 pm, MattNC <Mat...@.discussions.microsoft.com> wrote:
> I've tested a query in Query Analyzer and it returns the desired results.
> However, the same query in a SQL report finishes without errors but the
> report is completely blank. Similarly formatted reports work fine. Here's the
> query:
> SELECT CRMAF_Task.modifiedonutc, CRMAF_Task.modifiedbyname,
> CRMAF_Task.owneridname, CRMAF_Task.subject, CRMAF_Task.statecodename,
> CRMAF_Task.description, CRMAF_Task.regardingobjectid,
> CRMAF_Notes.subject AS NotesSubj, CRMAF_Notes.notetext AS NoteText,
> CRMAF_Notes.createdonutc
> FROM FilteredTask CRMAF_Task LEFT JOIN
> FilteredAnnotation CRMAF_Notes ON
> CRMAF_Task.activityid = CRMAF_Notes.objectid
> WHERE (CRMAF_Task.subject LIKE '%reb%') AND
> (CRMAF_Task.regardingobjectid = 'D5C79CEC-D3AF-DB11-86DF-0003FFDAF900') AND
> (CRMAF_Task.statecode = 1)
> Anyone have any ideas?
> --
> Matt Wittemann, CRM MVPhttp://icu-mscrm.blogspot.com
That's a tough one. Have you tried refreshing the dataset and making
sure that you are not filtering out anything (if you're grouping in
the report)?
Enrique Martinez
Sr. SQL Server Developer|||In two things it should be empty.
1. If no error, but empty it means it is not satisfying the where
conditions. So just check the where conditions may be spelling etc...
2. you said the same query works in QA, then just check and re-check where
your datasource is connecting, is it pointing to the same database/table
where your QA also connects ' just check this.. I think that must be wrong...
Amarnath
"MattNC" wrote:
> I've tested a query in Query Analyzer and it returns the desired results.
> However, the same query in a SQL report finishes without errors but the
> report is completely blank. Similarly formatted reports work fine. Here's the
> query:
> SELECT CRMAF_Task.modifiedonutc, CRMAF_Task.modifiedbyname,
> CRMAF_Task.owneridname, CRMAF_Task.subject, CRMAF_Task.statecodename,
> CRMAF_Task.description, CRMAF_Task.regardingobjectid,
> CRMAF_Notes.subject AS NotesSubj, CRMAF_Notes.notetext AS NoteText,
> CRMAF_Notes.createdonutc
> FROM FilteredTask CRMAF_Task LEFT JOIN
> FilteredAnnotation CRMAF_Notes ON
> CRMAF_Task.activityid = CRMAF_Notes.objectid
> WHERE (CRMAF_Task.subject LIKE '%reb%') AND
> (CRMAF_Task.regardingobjectid = 'D5C79CEC-D3AF-DB11-86DF-0003FFDAF900') AND
> (CRMAF_Task.statecode = 1)
> Anyone have any ideas?
> --
> Matt Wittemann, CRM MVP
> http://icu-mscrm.blogspot.com|||Who are you running the query as in QA?
Since you are using filtered views, if you are not running as the same
user running the reports, it may be an issue of security.
On Feb 19, 7:12 pm, MattNC <Mat...@.discussions.microsoft.com> wrote:
> I've tested a query in Query Analyzer and it returns the desired results.
> However, the same query in a SQL report finishes without errors but the
> report is completely blank. Similarly formatted reports work fine. Here's the
> query:
> SELECT CRMAF_Task.modifiedonutc, CRMAF_Task.modifiedbyname,
> CRMAF_Task.owneridname, CRMAF_Task.subject, CRMAF_Task.statecodename,
> CRMAF_Task.description, CRMAF_Task.regardingobjectid,
> CRMAF_Notes.subject AS NotesSubj, CRMAF_Notes.notetext AS NoteText,
> CRMAF_Notes.createdonutc
> FROM FilteredTask CRMAF_Task LEFT JOIN
> FilteredAnnotation CRMAF_Notes ON
> CRMAF_Task.activityid = CRMAF_Notes.objectid
> WHERE (CRMAF_Task.subject LIKE '%reb%') AND
> (CRMAF_Task.regardingobjectid = 'D5C79CEC-D3AF-DB11-86DF-0003FFDAF900') AND
> (CRMAF_Task.statecode = 1)
> Anyone have any ideas?
> --
> Matt Wittemann, CRM MVPhttp://icu-mscrm.blogspot.com

Report Headers showing on blank page

I hope someone will have time to answer a really basic RS question.

I have a simple report, the query will only return a few rows (16 - 20), my header is repeating on a 2nd blank page. I'm sure it's because I grouped on a column from the record set. I can not find where you delete the grouping.

Anyone have any good suggestions on RS books?

Thanks

Hey Marc,

There are a couple of places to check:

When you click on the table, are there rows that have little numbers on them (1,2, etc)? If so, you should be able to right-click on these and select Delete Groups.

Another place could be the Properties of the table (Select the table, right click on the Properties option). Select the Grouping tab. Delete any of the groups in there (usually named something like "details_group")

Good luck!

Jessica