Report parameter read only

Got this error message while trying to load a sub-report from an existing
report and passing a parameter into the sub-report. I created the report
parameter just like it did for the source report. Please help.That means you forgot to provide a Prompt for the parameter in the
subreport.
Omitting the Prompt indicates to the server that the user should not be
prompted for a value (hence: read-only).
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"John Cosmas" <vabo214@.msn.com> wrote in message
news:OKnfI3WZEHA.3228@.TK2MSFTNGP12.phx.gbl...
> Got this error message while trying to load a sub-report from an existing
> report and passing a parameter into the sub-report. I created the report
> parameter just like it did for the source report. Please help.
>

report parameter order

hi,
after changing the order of my report parameters (in this example test)
in the menue via the up and down arrow I get the following error
message:
an error occured during local processing. an error occured during
report processing. the value expression for the query parameter 'date'
contains an error: the expression referenced a non-existing parameter
in the report parameters collection.
anybody knows how to do this?
thanksIt sounds like you have either:
A) deleted a parameter which was there before, or more likely
B) changed the sequence required for cascading parameters, i.e. parameters
where one value depends on another one previously entered. It is probably
something to do with one of your report parameters' 'available values' or
'default values' definitions.
HTH,
Magendo_man
"florian" wrote:
> hi,
> after changing the order of my report parameters (in this example test)
> in the menue via the up and down arrow I get the following error
> message:
> an error occured during local processing. an error occured during
> report processing. the value expression for the query parameter 'date'
> contains an error: the expression referenced a non-existing parameter
> in the report parameters collection.
> anybody knows how to do this?
> thanks
>|||You're right I got this error msg. with cascading parameters. The
hierachie goes like this: A-->B-->C Originally I only included A and C
in my parameters but B was missing. So I added B but I didn't like it
to occure as the last report parameter so I wanted to change the
position and then I got the error msg. I get the same error when I
modifie the rdl file and swith the Report Parameters.
Any ideas instead of writing the whole report once again?
magendo_man schrieb:
> It sounds like you have either:
> A) deleted a parameter which was there before, or more likely
> B) changed the sequence required for cascading parameters, i.e. parameters
> where one value depends on another one previously entered. It is probably
> something to do with one of your report parameters' 'available values' or
> 'default values' definitions.
> HTH,
> Magendo_man
> "florian" wrote:
> > hi,
> >
> > after changing the order of my report parameters (in this example test)
> > in the menue via the up and down arrow I get the following error
> > message:
> >
> > an error occured during local processing. an error occured during
> > report processing. the value expression for the query parameter 'date'
> > contains an error: the expression referenced a non-existing parameter
> > in the report parameters collection.
> >
> > anybody knows how to do this?
> >
> > thanks
> >
> >|||Just add parameter B back in to Report Parameters in 2nd position.
"florian" wrote:
> You're right I got this error msg. with cascading parameters. The
> hierachie goes like this: A-->B-->C Originally I only included A and C
> in my parameters but B was missing. So I added B but I didn't like it
> to occure as the last report parameter so I wanted to change the
> position and then I got the error msg. I get the same error when I
> modifie the rdl file and swith the Report Parameters.
> Any ideas instead of writing the whole report once again?
>
> magendo_man schrieb:
> > It sounds like you have either:
> > A) deleted a parameter which was there before, or more likely
> > B) changed the sequence required for cascading parameters, i.e. parameters
> > where one value depends on another one previously entered. It is probably
> > something to do with one of your report parameters' 'available values' or
> > 'default values' definitions.
> >
> > HTH,
> > Magendo_man
> >
> > "florian" wrote:
> >
> > > hi,
> > >
> > > after changing the order of my report parameters (in this example test)
> > > in the menue via the up and down arrow I get the following error
> > > message:
> > >
> > > an error occured during local processing. an error occured during
> > > report processing. the value expression for the query parameter 'date'
> > > contains an error: the expression referenced a non-existing parameter
> > > in the report parameters collection.
> > >
> > > anybody knows how to do this?
> > >
> > > thanks
> > >
> > >
>|||do mean at the data view or where?
magendo_man schrieb:
> Just add parameter B back in to Report Parameters in 2nd position.
> "florian" wrote:
> > You're right I got this error msg. with cascading parameters. The
> > hierachie goes like this: A-->B-->C Originally I only included A and C
> > in my parameters but B was missing. So I added B but I didn't like it
> > to occure as the last report parameter so I wanted to change the
> > position and then I got the error msg. I get the same error when I
> > modifie the rdl file and swith the Report Parameters.
> >
> > Any ideas instead of writing the whole report once again?
> >
> >
> > magendo_man schrieb:
> >
> > > It sounds like you have either:
> > > A) deleted a parameter which was there before, or more likely
> > > B) changed the sequence required for cascading parameters, i.e. parameters
> > > where one value depends on another one previously entered. It is probably
> > > something to do with one of your report parameters' 'available values' or
> > > 'default values' definitions.
> > >
> > > HTH,
> > > Magendo_man
> > >
> > > "florian" wrote:
> > >
> > > > hi,
> > > >
> > > > after changing the order of my report parameters (in this example test)
> > > > in the menue via the up and down arrow I get the following error
> > > > message:
> > > >
> > > > an error occured during local processing. an error occured during
> > > > report processing. the value expression for the query parameter 'date'
> > > > contains an error: the expression referenced a non-existing parameter
> > > > in the report parameters collection.
> > > >
> > > > anybody knows how to do this?
> > > >
> > > > thanks
> > > >
> > > >
> >
> >|||In Layout view select from the menu Report> Report Parameters..
You should then be able to add back in your deleted parameter and move it in
to the correct position.
~ Magendo_man
"florian" wrote:
> do mean at the data view or where?
>
> magendo_man schrieb:
> > Just add parameter B back in to Report Parameters in 2nd position.
> >
> > "florian" wrote:
> >
> > > You're right I got this error msg. with cascading parameters. The
> > > hierachie goes like this: A-->B-->C Originally I only included A and C
> > > in my parameters but B was missing. So I added B but I didn't like it
> > > to occure as the last report parameter so I wanted to change the
> > > position and then I got the error msg. I get the same error when I
> > > modifie the rdl file and swith the Report Parameters.
> > >
> > > Any ideas instead of writing the whole report once again?
> > >
> > >
> > > magendo_man schrieb:
> > >
> > > > It sounds like you have either:
> > > > A) deleted a parameter which was there before, or more likely
> > > > B) changed the sequence required for cascading parameters, i.e. parameters
> > > > where one value depends on another one previously entered. It is probably
> > > > something to do with one of your report parameters' 'available values' or
> > > > 'default values' definitions.
> > > >
> > > > HTH,
> > > > Magendo_man
> > > >
> > > > "florian" wrote:
> > > >
> > > > > hi,
> > > > >
> > > > > after changing the order of my report parameters (in this example test)
> > > > > in the menue via the up and down arrow I get the following error
> > > > > message:
> > > > >
> > > > > an error occured during local processing. an error occured during
> > > > > report processing. the value expression for the query parameter 'date'
> > > > > contains an error: the expression referenced a non-existing parameter
> > > > > in the report parameters collection.
> > > > >
> > > > > anybody knows how to do this?
> > > > >
> > > > > thanks
> > > > >
> > > > >
> > >
> > >
>sql

Report Parameter not working in subquery?

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

Report Parameter error

Hi ,

This is vishal vidhale. when i pass parameter in reporting services that time it gives me an error "Foward dependancies are not allowed".

How can i overcome this error. Any one who know or having knowledge of SQL reporting services can help me..

Thanks......................

Hi Vishal. May be your parameter(child) is depending on another parameter(Parent) in the Report and that parameter(parent) is declared later this parameter(child).

so, if this is the situation, in the parameters form(Report->Parameters) move your paramter(child) to below the parent parameter and check it out.

Thanks,

Srinivasa Reddy.

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 Model Generating Out of Memory Error

I am creating a report model of a large database with a few hundred tables, the idea being that the users can report on any part of the database and create perspectives for the areas they want users to have access to (In the past the users have moaned about report models in a CA product called Eureka as the models are too small, perspectives seemed a way of allowing one large model but limiting each perspective to a set of users?). Unfortunately presumably due to the size of the model visual studio is giving me an out of memory error when adding new tables. I have a two dual core xeons with 4Gig of ram and I'm running XP Pro 64bit with the developer edition of 2005 with service pack 1 and VS2005 is using around 1/2 a Gb. Is this a known issue and will there be a work around?

Seems to occur when removing anything from the report model. Can reproduce by deleting calculated fields and selecting save.|||This sounds like a bug. Can you describe a list of steps that would repro the issue?|||Its fairly simple I only have to delete a few fields or tables from my report model and it triggers an out of memory error. I can forward the report model if thats helpful? I would like to regenerate the fields from the report model by using the autogenerate/get rid of some of the autogenerated calculated fields bu can't as I get this error each time.|||

yes, the model and the list of steps would help almineev tata microsoft todtod moc

|||

Alexandre Mineev MSFT wrote:

yes, the model and the list of steps would help almineev tata microsoft todtod moc

? noticed the error location button so heres some more info, pls contact direct when you need the model and files, you should have my email. Cool signature!

===================================

Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft Visual Studio)

Program Location:

at System.String.GetStringForStringBuilder(String value, Int32 startIndex, Int32 length, Int32 capacity)
at System.Text.StringBuilder.GetNewString(String currentString, Int32 requiredLength)
at System.Text.StringBuilder.Append(Char value)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BufferTextWriter.Write(Char ch)
at System.IO.TextWriter.Write(Char[] buffer, Int32 index, Int32 count)
at System.Xml.XmlEncodedRawTextWriter.FlushBuffer()
at System.Xml.XmlEncodedRawTextWriter.RawText(Char* pSrcBegin, Char* pSrcEnd)
at System.Xml.XmlEncodedRawTextWriter.RawText(String s)
at System.Xml.XmlEncodedRawTextWriterIndent.WriteIndent()
at System.Xml.XmlEncodedRawTextWriterIndent.WriteStartElement(String prefix, String localName, String ns)
at System.Xml.XmlWellFormedWriter.WriteStartElement(String prefix, String localName, String ns)
at System.Xml.XmlWriter.WriteStartElement(String localName, String ns)
at Microsoft.ReportingServices.Modeling.AttributeRefNode.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.Expression.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.Expression.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.FunctionNode.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.Expression.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelAttribute.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.ModelAttribute.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.ModelEntity.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.ModelEntityFolder.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.SemanticModel.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.SemanticModel.WriteTo(XmlWriter xw, ModelingSerializationOptions options)
at Microsoft.ReportingServices.ModelDesigner.Serialization.ModelDesignerSerializer.SerializeComponent(IDesignerSerializationManager manager, IComponent component, Object serializationStream)
at Microsoft.DataWarehouse.Serialization.DesignerComponentSerializer.Serialize(IDesignerSerializationManager manager, Object value)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.DataWarehouseDesignerLoader.Serialize()
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BaseDesignerLoader.Flush(Boolean forceful)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BaseDesignerLoader.Flush()
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.DataWarehouseContainerManager.OnBeforeSave(UInt32 docCookie)

|||Hi Phil, I did not get an email from you|||Where do I send the email?|||almineev tata microsoft todtod moc

Report Model Generating Out of Memory Error

I am creating a report model of a large database with a few hundred tables, the idea being that the users can report on any part of the database and create perspectives for the areas they want users to have access to (In the past the users have moaned about report models in a CA product called Eureka as the models are too small, perspectives seemed a way of allowing one large model but limiting each perspective to a set of users?). Unfortunately presumably due to the size of the model visual studio is giving me an out of memory error when adding new tables. I have a two dual core xeons with 4Gig of ram and I'm running XP Pro 64bit with the developer edition of 2005 with service pack 1 and VS2005 is using around 1/2 a Gb. Is this a known issue and will there be a work around?

Seems to occur when removing anything from the report model. Can reproduce by deleting calculated fields and selecting save.|||This sounds like a bug. Can you describe a list of steps that would repro the issue?|||Its fairly simple I only have to delete a few fields or tables from my report model and it triggers an out of memory error. I can forward the report model if thats helpful? I would like to regenerate the fields from the report model by using the autogenerate/get rid of some of the autogenerated calculated fields bu can't as I get this error each time.|||

yes, the model and the list of steps would help almineev tata microsoft todtod moc

|||

Alexandre Mineev MSFT wrote:

yes, the model and the list of steps would help almineev tata microsoft todtod moc

? noticed the error location button so heres some more info, pls contact direct when you need the model and files, you should have my email. Cool signature!

===================================

Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft Visual Studio)

Program Location:

at System.String.GetStringForStringBuilder(String value, Int32 startIndex, Int32 length, Int32 capacity)
at System.Text.StringBuilder.GetNewString(String currentString, Int32 requiredLength)
at System.Text.StringBuilder.Append(Char value)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BufferTextWriter.Write(Char ch)
at System.IO.TextWriter.Write(Char[] buffer, Int32 index, Int32 count)
at System.Xml.XmlEncodedRawTextWriter.FlushBuffer()
at System.Xml.XmlEncodedRawTextWriter.RawText(Char* pSrcBegin, Char* pSrcEnd)
at System.Xml.XmlEncodedRawTextWriter.RawText(String s)
at System.Xml.XmlEncodedRawTextWriterIndent.WriteIndent()
at System.Xml.XmlEncodedRawTextWriterIndent.WriteStartElement(String prefix, String localName, String ns)
at System.Xml.XmlWellFormedWriter.WriteStartElement(String prefix, String localName, String ns)
at System.Xml.XmlWriter.WriteStartElement(String localName, String ns)
at Microsoft.ReportingServices.Modeling.AttributeRefNode.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.Expression.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.Expression.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.FunctionNode.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.Expression.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelAttribute.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.ModelAttribute.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.ModelEntity.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.ModelEntityFolder.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.SemanticModel.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.SemanticModel.WriteTo(XmlWriter xw, ModelingSerializationOptions options)
at Microsoft.ReportingServices.ModelDesigner.Serialization.ModelDesignerSerializer.SerializeComponent(IDesignerSerializationManager manager, IComponent component, Object serializationStream)
at Microsoft.DataWarehouse.Serialization.DesignerComponentSerializer.Serialize(IDesignerSerializationManager manager, Object value)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.DataWarehouseDesignerLoader.Serialize()
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BaseDesignerLoader.Flush(Boolean forceful)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BaseDesignerLoader.Flush()
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.DataWarehouseContainerManager.OnBeforeSave(UInt32 docCookie)

|||Hi Phil, I did not get an email from you|||Where do I send the email?|||almineev tata microsoft todtod moc

Report Model Generating Out of Memory Error

I am creating a report model of a large database with a few hundred tables, the idea being that the users can report on any part of the database and create perspectives for the areas they want users to have access to (In the past the users have moaned about report models in a CA product called Eureka as the models are too small, perspectives seemed a way of allowing one large model but limiting each perspective to a set of users?). Unfortunately presumably due to the size of the model visual studio is giving me an out of memory error when adding new tables. I have a two dual core xeons with 4Gig of ram and I'm running XP Pro 64bit with the developer edition of 2005 with service pack 1 and VS2005 is using around 1/2 a Gb. Is this a known issue and will there be a work around?

Seems to occur when removing anything from the report model. Can reproduce by deleting calculated fields and selecting save.|||This sounds like a bug. Can you describe a list of steps that would repro the issue?|||Its fairly simple I only have to delete a few fields or tables from my report model and it triggers an out of memory error. I can forward the report model if thats helpful? I would like to regenerate the fields from the report model by using the autogenerate/get rid of some of the autogenerated calculated fields bu can't as I get this error each time.|||

yes, the model and the list of steps would help almineev tata microsoft todtod moc

|||

Alexandre Mineev MSFT wrote:

yes, the model and the list of steps would help almineev tata microsoft todtod moc

? noticed the error location button so heres some more info, pls contact direct when you need the model and files, you should have my email. Cool signature!

===================================

Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft Visual Studio)

Program Location:

at System.String.GetStringForStringBuilder(String value, Int32 startIndex, Int32 length, Int32 capacity)
at System.Text.StringBuilder.GetNewString(String currentString, Int32 requiredLength)
at System.Text.StringBuilder.Append(Char value)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BufferTextWriter.Write(Char ch)
at System.IO.TextWriter.Write(Char[] buffer, Int32 index, Int32 count)
at System.Xml.XmlEncodedRawTextWriter.FlushBuffer()
at System.Xml.XmlEncodedRawTextWriter.RawText(Char* pSrcBegin, Char* pSrcEnd)
at System.Xml.XmlEncodedRawTextWriter.RawText(String s)
at System.Xml.XmlEncodedRawTextWriterIndent.WriteIndent()
at System.Xml.XmlEncodedRawTextWriterIndent.WriteStartElement(String prefix, String localName, String ns)
at System.Xml.XmlWellFormedWriter.WriteStartElement(String prefix, String localName, String ns)
at System.Xml.XmlWriter.WriteStartElement(String localName, String ns)
at Microsoft.ReportingServices.Modeling.AttributeRefNode.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.Expression.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.Expression.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.FunctionNode.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.Expression.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelAttribute.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.ModelAttribute.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.ModelEntity.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.ModelEntityFolder.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.SemanticModel.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.SemanticModel.WriteTo(XmlWriter xw, ModelingSerializationOptions options)
at Microsoft.ReportingServices.ModelDesigner.Serialization.ModelDesignerSerializer.SerializeComponent(IDesignerSerializationManager manager, IComponent component, Object serializationStream)
at Microsoft.DataWarehouse.Serialization.DesignerComponentSerializer.Serialize(IDesignerSerializationManager manager, Object value)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.DataWarehouseDesignerLoader.Serialize()
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BaseDesignerLoader.Flush(Boolean forceful)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BaseDesignerLoader.Flush()
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.DataWarehouseContainerManager.OnBeforeSave(UInt32 docCookie)

|||Hi Phil, I did not get an email from you|||Where do I send the email?|||almineev tata microsoft todtod moc

Report Model Expression

I'm new to Report Model and trying to create a new expression field with IF condition on related Role's attribute when i do this it gives error "The arguments to the following function are not valid: = (Equal to)" but if put direct Entity's column it works fine.. heres the expression

IF(Activity Type List Value Display Name = "Sick Day", 1, 0)

"Activity Type" is the Role(1--*) in Employee Table, and i'm adding this expression in the Employee Table

are there any sample models that i can download with some complex filters/expressions etc?

regards

faraz

any idea?|||It seems microsoft people are not interested in Report Model :)|||

Sorry for the delay in responding. Based on the cardinality of the role (1-*), it sounds like you are trying to compare a *set* of activity type display names to a single value. The equals operator cannot do this. Instead, wrap the field reference in the COUNT aggregate function, then double-click to expand the field reference and add a filter to count only the activity types where the display name is "Sick Day". You can then use your IF function to return a result based on the number of activity types that meet this condition.

Hope this helps!

sql

Report Model Expression

I'm new to Report Model and trying to create a new expression field with IF condition on related Role's attribute when i do this it gives error "The arguments to the following function are not valid: = (Equal to)" but if put direct Entity's column it works fine.. heres the expression

IF(Activity Type List Value Display Name = "Sick Day", 1, 0)

"Activity Type" is the Role(1--*) in Employee Table, and i'm adding this expression in the Employee Table

are there any sample models that i can download with some complex filters/expressions etc?

regards

faraz

any idea?|||It seems microsoft people are not interested in Report Model :)|||

Sorry for the delay in responding. Based on the cardinality of the role (1-*), it sounds like you are trying to compare a *set* of activity type display names to a single value. The equals operator cannot do this. Instead, wrap the field reference in the COUNT aggregate function, then double-click to expand the field reference and add a filter to count only the activity types where the display name is "Sick Day". You can then use your IF function to return a result based on the number of activity types that meet this condition.

Hope this helps!

Report Model Error in WSS 3.0

The model specified must be a generated model. (rsModelNotGenerated)

This error comes up after clicking on the new report model. The report model and datasource deployed to the sharepoint server with no issues.

Hi,

Did you ever find a solution to the error:
"The model specified must be a generated model (rsModelNotGenerated)."

I'm getting it after I have deployed a Report Model to Microsoft Office Sharepoint Server 2007, and when trying to create a Report Builder model in Sharepoint.

I appreciate any help you can provide.

Thanks,
Fady
|||

Hi,

Did you manage to resolve your problem, if yes could you please share your experiences.

Thanks

Mon

Report Model Error in WSS 3.0

The model specified must be a generated model. (rsModelNotGenerated)

This error comes up after clicking on the new report model. The report model and datasource deployed to the sharepoint server with no issues.

Hi,

Did you ever find a solution to the error:
"The model specified must be a generated model (rsModelNotGenerated)."

I'm getting it after I have deployed a Report Model to Microsoft Office Sharepoint Server 2007, and when trying to create a Report Builder model in Sharepoint.

I appreciate any help you can provide.

Thanks,
Fady
|||

Hi,

Did you manage to resolve your problem, if yes could you please share your experiences.

Thanks

Mon

Report Model Error in WSS 3.0

The model specified must be a generated model. (rsModelNotGenerated)

This error comes up after clicking on the new report model. The report model and datasource deployed to the sharepoint server with no issues.

Hi,

Did you ever find a solution to the error:
"The model specified must be a generated model (rsModelNotGenerated)."

I'm getting it after I have deployed a Report Model to Microsoft Office Sharepoint Server 2007, and when trying to create a Report Builder model in Sharepoint.

I appreciate any help you can provide.

Thanks,
Fady|||

Hi,

Did you manage to resolve your problem, if yes could you please share your experiences.

Thanks

Mon

Report Model Deployment Error

Hi All,
I created a report model and when I tried to deploy I got the following:

The model ID of the submitted model must match that of the current model

I have searched all over for the cause and solution but, have had no luck.

Can someone please help me?

Thanks,

Roy
The SemanticModel ID of the submitted model must match the semantic model id of the existing model, otherwise you'll get this error.

The solution is to delete model from server (using Report Manager or Management Studio)before deploying.|||Yes, I had the same problem. Surely a deploy would be able to deal with this automatically?|||Deploy tries not to override existing model with completely different model.

Report Model Deployment Error

Hi All,
I created a report model and when I tried to deploy I got the following:

The model ID of the submitted model must match that of the current model

I have searched all over for the cause and solution but, have had no luck.

Can someone please help me?

Thanks,

Roy
The SemanticModel ID of the submitted model must match the semantic model id of the existing model, otherwise you'll get this error.

The solution is to delete model from server (using Report Manager or Management Studio)before deploying.|||Yes, I had the same problem. Surely a deploy would be able to deal with this automatically?|||Deploy tries not to override existing model with completely different model.sql

Report Model Deployment : The model ID of the submitted model must match that of the

Running 2005 Beta 3 Refresh. When I first deploy, it works fine. Subsequent deployments yield the following error:

Error 1 The model ID of the submitted model must match that of the
current model. C:\Source\********.smdl 0 0

When I change the name of the smdl, it works fine. (Properties of the .smdl file)

Any ideas?

For existing models Deploy actually means Update

However if Model ID is different, this cannot be considered as Update - the model may be completely different. In this case user must remove model from server before re-deploying it.

|||

Yes, thanks.

The scenario arises when you try to deploy a model in a NEW report project which has the same name as a model designed in a previous report project.

Jean-Pierre

|||

Lev Semenets - MSFT wrote:

For existing models Deploy actually means Update

However if Model ID is different, this cannot be considered as Update - the model may be completely different. In this case user must remove model from server before re-deploying it.

How can i remove a model from the server.?

Thanks !!|||

Programmatically - by calling SOAP API method DeleteItem()

|||

Hi,

no need to do SOAP.

Just go to the reporting web site, go to the models folder and delete the report.

Re-deploy and it's updated version is back in the folder.

Report Model Deployment : The model ID of the submitted model must match that of the

Running 2005 Beta 3 Refresh. When I first deploy, it works fine. Subsequent deployments yield the following error:

Error 1 The model ID of the submitted model must match that of the
current model. C:\Source\********.smdl 0 0

When I change the name of the smdl, it works fine. (Properties of the .smdl file)

Any ideas?

For existing models Deploy actually means Update

However if Model ID is different, this cannot be considered as Update - the model may be completely different. In this case user must remove model from server before re-deploying it.

|||

Yes, thanks.

The scenario arises when you try to deploy a model in a NEW report project which has the same name as a model designed in a previous report project.

Jean-Pierre

|||

Lev Semenets - MSFT wrote:

For existing models Deploy actually means Update

However if Model ID is different, this cannot be considered as Update - the model may be completely different. In this case user must remove model from server before re-deploying it.

How can i remove a model from the server.?

Thanks !!
|||

Programmatically - by calling SOAP API method DeleteItem()

|||

Hi,

no need to do SOAP.

Just go to the reporting web site, go to the models folder and delete the report.

Re-deploy and it's updated version is back in the folder.

Report Model Deployment : The model ID of the submitted model must match that of the

Running 2005 Beta 3 Refresh. When I first deploy, it works fine. Subsequent deployments yield the following error:

Error 1 The model ID of the submitted model must match that of the
current model. C:\Source\********.smdl 0 0

When I change the name of the smdl, it works fine. (Properties of the .smdl file)

Any ideas?

For existing models Deploy actually means Update

However if Model ID is different, this cannot be considered as Update - the model may be completely different. In this case user must remove model from server before re-deploying it.

|||

Yes, thanks.

The scenario arises when you try to deploy a model in a NEW report project which has the same name as a model designed in a previous report project.

Jean-Pierre

|||

Lev Semenets - MSFT wrote:

For existing models Deploy actually means Update

However if Model ID is different, this cannot be considered as Update - the model may be completely different. In this case user must remove model from server before re-deploying it.

How can i remove a model from the server.?

Thanks !!|||

Programmatically - by calling SOAP API method DeleteItem()

|||

Hi,

no need to do SOAP.

Just go to the reporting web site, go to the models folder and delete the report.

Re-deploy and it's updated version is back in the folder.

Report Model Deployment : The model ID of the submitted model must match that of the

Running 2005 Beta 3 Refresh. When I first deploy, it works fine. Subsequent deployments yield the following error:

Error 1 The model ID of the submitted model must match that of the
current model. C:\Source\********.smdl 0 0

When I change the name of the smdl, it works fine. (Properties of the .smdl file)

Any ideas?

For existing models Deploy actually means Update

However if Model ID is different, this cannot be considered as Update - the model may be completely different. In this case user must remove model from server before re-deploying it.

|||

Yes, thanks.

The scenario arises when you try to deploy a model in a NEW report project which has the same name as a model designed in a previous report project.

Jean-Pierre

|||

Lev Semenets - MSFT wrote:

For existing models Deploy actually means Update

However if Model ID is different, this cannot be considered as Update - the model may be completely different. In this case user must remove model from server before re-deploying it.

How can i remove a model from the server.?

Thanks !!|||

Programmatically - by calling SOAP API method DeleteItem()

|||

Hi,

no need to do SOAP.

Just go to the reporting web site, go to the models folder and delete the report.

Re-deploy and it's updated version is back in the folder.

report model and it's datasource

hi everyone,

I created a report model and deployed it. When I open the rpt builder on web I get the following error.

'The selected data source does not have and content available'

Seemed like it would be a easy issue, but I cannot retrieve any data in the rpt model. The datasource I'm using does work for rpts not associated with the rpt model. Any suggestions? It seems like I've missed a step. Thanks, Lisa

If there is no primary key defined, you can build the model, but no contents. Do you have primary key defined?sql