error w/ custom report. any help?

[L8rdays]

Hi all,

My programming skills are over 10 years old, and need some help. We have a
consultant creating a couple of small custom reports for us. We are getting
an error when we run one of the reports. He says he doesn't get the error in
his environment. So here is the error we get...

Must declare the scalar variable "@ProjectStatus".

When I did a find through his code I can only find two instances of this
variable. Here is the code.. any thoughts?

WHERE (MSP_EpmTask_UserView.TaskOutlineLevel = 0) AND
(MSP_EpmTask_UserView.TaskOutlineNumber = 0) AND
(MSP_EpmProject_UserView.[Project Status] =
@ProjectStatus) AND (MSP_EpmTask_UserView_1.[TaskIsMilestone ] = 1) OR
(MSP_EpmTask_UserView.TaskOutlineLevel = 0) AND
(MSP_EpmTask_UserView.TaskOutlineNumber = 0) AND
(MSP_EpmTask_UserView_1.[TaskIsMilestone ] = 1) AND
(@ProjectStatus = 'All')

Thanks!

 

[Stephen Sanderlin]

Is this an SRS report or just a SQL script?

 

[L8rdays]

SRS

Is this an SRS report or just a SQL script?

Hi all,

My programming skills are over 10 years old, and need some help. We have a
consultant creating a couple of small custom reports for us. We are getting
an error when we run one of the reports. He says he doesn't get the error in
his environment. So here is the error we get...

Must declare the scalar variable "@ProjectStatus".

When I did a find through his code I can only find two instances of this
variable. Here is the code.. any thoughts?

WHERE (MSP_EpmTask_UserView.TaskOutlineLevel = 0) AND
(MSP_EpmTask_UserView.TaskOutlineNumber = 0) AND
(MSP_EpmProject_UserView.[Project Status] =
@ProjectStatus) AND (MSP_EpmTask_UserView_1.[TaskIsMilestone ] = 1) OR
(MSP_EpmTask_UserView.TaskOutlineLevel = 0) AND
(MSP_EpmTask_UserView.TaskOutlineNumber = 0) AND
(MSP_EpmTask_UserView_1.[TaskIsMilestone ] = 1) AND
(@ProjectStatus = 'All')

Thanks!

 

[Stephen Sanderlin]

Are you sure the parameter @ProjectStatus is defined in the dataset that
uses it, in the report itself, and that the two are correctly linked?

--
Stephen Sanderlin
Principal Consultant
MSProjectExperts

For Project Server Consulting: http://www.msprojectexperts.com
For Project Server Training: http://www.projectservertraining.com

Read my blog at: http://www.projectserverhelp.com
Join the community at: http://forums.epmfaq.com

SRS

:

Is this an SRS report or just a SQL script?

Hi all,

My programming skills are over 10 years old, and need some help. We have a
consultant creating a couple of small custom reports for us. We are getting
an error when we run one of the reports. He says he doesn't get the error in
his environment. So here is the error we get...

Must declare the scalar variable "@ProjectStatus".

When I did a find through his code I can only find two instances of this
variable. Here is the code.. any thoughts?

WHERE (MSP_EpmTask_UserView.TaskOutlineLevel = 0) AND
(MSP_EpmTask_UserView.TaskOutlineNumber = 0) AND
(MSP_EpmProject_UserView.[Project Status] =
@ProjectStatus) AND (MSP_EpmTask_UserView_1.[TaskIsMilestone ] = 1) OR
(MSP_EpmTask_UserView.TaskOutlineLevel = 0) AND
(MSP_EpmTask_UserView.TaskOutlineNumber = 0) AND
(MSP_EpmTask_UserView_1.[TaskIsMilestone ] = 1) AND
(@ProjectStatus = 'All')

Thanks!

 

[L8rdays]

Well you see that is where I was hoping to get your help.

This is a drop down field the person running the report can input. Which I
do when I try to run the report. There are only 4 choices... ALL, Active,
Closed, On Hold. Doesn't matter which option I choose when I run it, I get
the same error.

Must delcare the scalar variable "@ProjectStatus"

So I opened up the code and did a find for every instance of
"@ProjectStatus". The results of my fine are copied in my original post.
And further down where he is selected all instances of @ProjectStatus = All.
Again I don't see it anywhere else and I can't see why I would be getting
this error. Since he doesn't seem to know, I turned to you guys because
everyone on this board is always so helpful.



I think I see in this code where it looks like he is setting the variable
@ProjectStatus to the = the Project Status selected.

Are you sure the parameter @ProjectStatus is defined in the dataset that
uses it, in the report itself, and that the two are correctly linked?

--
Stephen Sanderlin
Principal Consultant
MSProjectExperts

For Project Server Consulting: http://www.msprojectexperts.com
For Project Server Training: http://www.projectservertraining.com

Read my blog at: http://www.projectserverhelp.com
Join the community at: http://forums.epmfaq.com

SRS

:

Is this an SRS report or just a SQL script?

Hi all,

My programming skills are over 10 years old, and need some help. We have a
consultant creating a couple of small custom reports for us. We are getting
an error when we run one of the reports. He says he doesn't get the error in
his environment. So here is the error we get...

Must declare the scalar variable "@ProjectStatus".

When I did a find through his code I can only find two instances of this
variable. Here is the code.. any thoughts?

WHERE (MSP_EpmTask_UserView.TaskOutlineLevel = 0) AND
(MSP_EpmTask_UserView.TaskOutlineNumber = 0) AND
(MSP_EpmProject_UserView.[Project Status] =
@ProjectStatus) AND (MSP_EpmTask_UserView_1.[TaskIsMilestone ] = 1) OR
(MSP_EpmTask_UserView.TaskOutlineLevel = 0) AND
(MSP_EpmTask_UserView.TaskOutlineNumber = 0) AND
(MSP_EpmTask_UserView_1.[TaskIsMilestone ] = 1) AND
(@ProjectStatus = 'All')

Thanks!

 

[Stephen Sanderlin]

OK, no problem. You'll need the appropriate version of the Business
Intelligence Development Studio installed -- you can find it on the SQL
CD/DVD.

Take a look at this article on Adding/Editing/Deleting report
parameters:
http://msdn.microsoft.com/en-us/library/ms159266(SQL.90).aspx
And this article on linking query parameters with report parameters:
http://msdn.microsoft.com/en-us/library/ms157187(SQL.90).aspx

For example: If you discover that there's a parameter called
ProjectStatus in the Report Parameter dialog, you'll want to go to the
Data tab of the report and edit the dataset where you found the code you
posted earlier. Click on the Parameters tab, and find @ProjectStatus. If
it doesn't already exist (it should), enter @ProjectStatus in the Name
column. Then, enter =Parameters!ProjectStatus.Value in the same row in
the Value column.

Hope that helps,
Steve

--
Stephen Sanderlin
Principal Consultant
MSProjectExperts

For Project Server Consulting: http://www.msprojectexperts.com
For Project Server Training: http://www.projectservertraining.com

Read my blog at: http://www.projectserverhelp.com
Join the community at: http://forums.epmfaq.com

Well you see that is where I was hoping to get your help.

This is a drop down field the person running the report can input. Which I
do when I try to run the report. There are only 4 choices... ALL, Active,
Closed, On Hold. Doesn't matter which option I choose when I run it, I get
the same error.

Must delcare the scalar variable "@ProjectStatus"

So I opened up the code and did a find for every instance of
"@ProjectStatus". The results of my fine are copied in my original post.
And further down where he is selected all instances of @ProjectStatus = All.
Again I don't see it anywhere else and I can't see why I would be getting
this error. Since he doesn't seem to know, I turned to you guys because
everyone on this board is always so helpful.



I think I see in this code where it looks like he is setting the variable
@ProjectStatus to the = the Project Status selected.



:

Are you sure the parameter @ProjectStatus is defined in the dataset that
uses it, in the report itself, and that the two are correctly linked?

--
Stephen Sanderlin
Principal Consultant
MSProjectExperts

For Project Server Consulting: http://www.msprojectexperts.com
For Project Server Training: http://www.projectservertraining.com

Read my blog at: http://www.projectserverhelp.com
Join the community at: http://forums.epmfaq.com

SRS

:


Is this an SRS report or just a SQL script?



Hi all,

My programming skills are over 10 years old, and need some help. We have a
consultant creating a couple of small custom reports for us. We are getting
an error when we run one of the reports. He says he doesn't get the error in
his environment. So here is the error we get...

Must declare the scalar variable "@ProjectStatus".

When I did a find through his code I can only find two instances of this
variable. Here is the code.. any thoughts?

WHERE (MSP_EpmTask_UserView.TaskOutlineLevel = 0) AND
(MSP_EpmTask_UserView.TaskOutlineNumber = 0) AND
(MSP_EpmProject_UserView.[Project Status] =
@ProjectStatus) AND (MSP_EpmTask_UserView_1.[TaskIsMilestone ] = 1) OR
(MSP_EpmTask_UserView.TaskOutlineLevel = 0) AND
(MSP_EpmTask_UserView.TaskOutlineNumber = 0) AND
(MSP_EpmTask_UserView_1.[TaskIsMilestone ] = 1) AND
(@ProjectStatus = 'All')

Thanks!

 

[L8rdays]

Stephen,

Thank you so much for your help! This was exactly what I needed. I did
get the vendor to help me resolve it, just after reading your information and
we used Visual Source instead of Business Intelligence.

I've learned alot with the links you shared and having to be the driver
while we research the error. (Brought back a few memories.)

I just wish that the error shown was more intuitive. The real problem was a
line of code had the table name wrong and then also the field wrong, but
nothing to do with the @ProjectStatus.

THANKS AGAIN! for taking the time to help.

OK, no problem. You'll need the appropriate version of the Business
Intelligence Development Studio installed -- you can find it on the SQL
CD/DVD.

Take a look at this article on Adding/Editing/Deleting report
parameters:
http://msdn.microsoft.com/en-us/library/ms159266(SQL.90).aspx
And this article on linking query parameters with report parameters:
http://msdn.microsoft.com/en-us/library/ms157187(SQL.90).aspx

For example: If you discover that there's a parameter called
ProjectStatus in the Report Parameter dialog, you'll want to go to the
Data tab of the report and edit the dataset where you found the code you
posted earlier. Click on the Parameters tab, and find @ProjectStatus. If
it doesn't already exist (it should), enter @ProjectStatus in the Name
column. Then, enter =Parameters!ProjectStatus.Value in the same row in
the Value column.

Hope that helps,
Steve

--
Stephen Sanderlin
Principal Consultant
MSProjectExperts

For Project Server Consulting: http://www.msprojectexperts.com
For Project Server Training: http://www.projectservertraining.com

Read my blog at: http://www.projectserverhelp.com
Join the community at: http://forums.epmfaq.com

Well you see that is where I was hoping to get your help.

This is a drop down field the person running the report can input. Which I
do when I try to run the report. There are only 4 choices... ALL, Active,
Closed, On Hold. Doesn't matter which option I choose when I run it, I get
the same error.

Must delcare the scalar variable "@ProjectStatus"

So I opened up the code and did a find for every instance of
"@ProjectStatus". The results of my fine are copied in my original post.
And further down where he is selected all instances of @ProjectStatus = All.
Again I don't see it anywhere else and I can't see why I would be getting
this error. Since he doesn't seem to know, I turned to you guys because
everyone on this board is always so helpful.



I think I see in this code where it looks like he is setting the variable
@ProjectStatus to the = the Project Status selected.



:

Are you sure the parameter @ProjectStatus is defined in the dataset that
uses it, in the report itself, and that the two are correctly linked?

--
Stephen Sanderlin
Principal Consultant
MSProjectExperts

For Project Server Consulting: http://www.msprojectexperts.com
For Project Server Training: http://www.projectservertraining.com

Read my blog at: http://www.projectserverhelp.com
Join the community at: http://forums.epmfaq.com

SRS

:

Is this an SRS report or just a SQL script?

Hi all,

My programming skills are over 10 years old, and need some help. We have a
consultant creating a couple of small custom reports for us. We are getting
an error when we run one of the reports. He says he doesn't get the error in
his environment. So here is the error we get...

Must declare the scalar variable "@ProjectStatus".

When I did a find through his code I can only find two instances of this
variable. Here is the code.. any thoughts?

WHERE (MSP_EpmTask_UserView.TaskOutlineLevel = 0) AND
(MSP_EpmTask_UserView.TaskOutlineNumber = 0) AND
(MSP_EpmProject_UserView.[Project Status] =
@ProjectStatus) AND (MSP_EpmTask_UserView_1.[TaskIsMilestone ] = 1) OR
(MSP_EpmTask_UserView.TaskOutlineLevel = 0) AND
(MSP_EpmTask_UserView.TaskOutlineNumber = 0) AND
(MSP_EpmTask_UserView_1.[TaskIsMilestone ] = 1) AND
(@ProjectStatus = 'All')

Thanks!

 

[Stephen Sanderlin]

Great! Glad to hear you got it fixed.

--
Stephen Sanderlin
Principal Consultant
MSProjectExperts

For Project Server Consulting: http://www.msprojectexperts.com
For Project Server Training: http://www.projectservertraining.com

Read my blog at: http://www.projectserverhelp.com
Join the community at: http://forums.epmfaq.com

Stephen,

Thank you so much for your help! This was exactly what I needed. I did
get the vendor to help me resolve it, just after reading your information and
we used Visual Source instead of Business Intelligence.

I've learned alot with the links you shared and having to be the driver
while we research the error. (Brought back a few memories.)

I just wish that the error shown was more intuitive. The real problem was a
line of code had the table name wrong and then also the field wrong, but
nothing to do with the @ProjectStatus.

THANKS AGAIN! for taking the time to help.

:

OK, no problem. You'll need the appropriate version of the Business
Intelligence Development Studio installed -- you can find it on the SQL
CD/DVD.

Take a look at this article on Adding/Editing/Deleting report
parameters:
http://msdn.microsoft.com/en-us/library/ms159266(SQL.90).aspx
And this article on linking query parameters with report parameters:
http://msdn.microsoft.com/en-us/library/ms157187(SQL.90).aspx

For example: If you discover that there's a parameter called
ProjectStatus in the Report Parameter dialog, you'll want to go to the
Data tab of the report and edit the dataset where you found the code you
posted earlier. Click on the Parameters tab, and find @ProjectStatus. If
it doesn't already exist (it should), enter @ProjectStatus in the Name
column. Then, enter =Parameters!ProjectStatus.Value in the same row in
the Value column.

Hope that helps,
Steve

--
Stephen Sanderlin
Principal Consultant
MSProjectExperts

For Project Server Consulting: http://www.msprojectexperts.com
For Project Server Training: http://www.projectservertraining.com

Read my blog at: http://www.projectserverhelp.com
Join the community at: http://forums.epmfaq.com

Well you see that is where I was hoping to get your help.

This is a drop down field the person running the report can input. Which I
do when I try to run the report. There are only 4 choices... ALL, Active,
Closed, On Hold. Doesn't matter which option I choose when I run it, I get
the same error.

Must delcare the scalar variable "@ProjectStatus"

So I opened up the code and did a find for every instance of
"@ProjectStatus". The results of my fine are copied in my original post.
And further down where he is selected all instances of @ProjectStatus = All.
Again I don't see it anywhere else and I can't see why I would be getting
this error. Since he doesn't seem to know, I turned to you guys because
everyone on this board is always so helpful.



I think I see in this code where it looks like he is setting the variable
@ProjectStatus to the = the Project Status selected.



:


Are you sure the parameter @ProjectStatus is defined in the dataset that
uses it, in the report itself, and that the two are correctly linked?

--
Stephen Sanderlin
Principal Consultant
MSProjectExperts

For Project Server Consulting: http://www.msprojectexperts.com
For Project Server Training: http://www.projectservertraining.com

Read my blog at: http://www.projectserverhelp.com
Join the community at: http://forums.epmfaq.com





SRS

:



Is this an SRS report or just a SQL script?




Hi all,

My programming skills are over 10 years old, and need some help. We have a
consultant creating a couple of small custom reports for us. We are getting
an error when we run one of the reports. He says he doesn't get the error in
his environment. So here is the error we get...

Must declare the scalar variable "@ProjectStatus".

When I did a find through his code I can only find two instances of this
variable. Here is the code.. any thoughts?

WHERE (MSP_EpmTask_UserView.TaskOutlineLevel = 0) AND
(MSP_EpmTask_UserView.TaskOutlineNumber = 0) AND
(MSP_EpmProject_UserView.[Project Status] =
@ProjectStatus) AND (MSP_EpmTask_UserView_1.[TaskIsMilestone ] = 1) OR
(MSP_EpmTask_UserView.TaskOutlineLevel = 0) AND
(MSP_EpmTask_UserView.TaskOutlineNumber = 0) AND
(MSP_EpmTask_UserView_1.[TaskIsMilestone ] = 1) AND
(@ProjectStatus = 'All')

Thanks!