parameter is missing a value.

Hi Friends,

I have created a child report with hidden parameter. When I call this report from the Master report using a link, I am getting error 'the xxxx parameter is missing a value'.

From, master report, if I click a value, it should take that value to child report where it filters for that value and should display the child report.

How to overcome this?

Thanks & Regards,

Naveen J V

Have you set any default value for the parameter in your subreport?

|||

Hi,

No default value has been set.

Regards,

Naveen J V

|||Ok, then try giving a default value for your hidden parameter. Give the same expression that you have used in the value field for the parameter. This might work. Not sure.|||

Hi,

I already tried this option, but I'm still getting the same error.

Thanks & Regards,

Naveen

|||

All the parameters created in sub-report should get some value, so if you have more than one parameter in the sub-report and from the master report you are passing value of only one parameter then obviously the value of other parameters will be missing. I suggest you to set the default value for the parameters in the sub report, so that the report get displayed even if you are passing wrong values from the master report.

By the way what is the expression that you are passing? And how many parameters you have?

|||

Hi Rohit,

My sub report has only one parameter. In my master report, If I click a particular column, it should take me to the sub report, where I should be able to see the filtered report based on the passed value.

It is working for one of my report which uses a different cube.

Thanks & Regards,

Naveen

|||

Can you give the expression that you are passing?

|||

Rohit,

While creating a report, in Query builder I selected a parameter (which referes to a member of a dimension). Same parameter I'm using in my report. I am not using any expressions.

Thanks & Regards,

Naveen

|||

what is the expression you are writting in the navigation properties for the parameter of your sub report?

|||

Hi,

Jump to Report ->Sub_rpt (sub report name)

Parameter name -> DLVLTM

Parameter value -> =Fields!TM.Uniquename

Regards,

Naveen J V

|||

Actually everything seems to be fine. It may be a data issue though.

anyways again give it a try:

Go to Sub report and in the report parameter window try by making available values non-queried.

|||

Hi Rohit,

If I dont hide the parameter, the report works fine. If I hide, only then I get this error. In the present context, it should be hidden. Based on the selection made from the master report, it should disply the child report.

I dont understand wht could be the problem?

Regards,

Naveen

|||

Hi Rohit,

I'm getting the same error. Please refer to my rprevious mail.

Regards,

Naveen J V

parameter is missing a value.

Hi Friends,

I have created a child report with hidden parameter. When I call this report from the Master report using a link, I am getting error 'the xxxx parameter is missing a value'.

From, master report, if I click a value, it should take that value to child report where it filters for that value and should display the child report.

How to overcome this?

Thanks & Regards,

Naveen J V

Have you set any default value for the parameter in your subreport?

|||

Hi,

No default value has been set.

Regards,

Naveen J V

|||Ok, then try giving a default value for your hidden parameter. Give the same expression that you have used in the value field for the parameter. This might work. Not sure.|||

Hi,

I already tried this option, but I'm still getting the same error.

Thanks & Regards,

Naveen

|||

All the parameters created in sub-report should get some value, so if you have more than one parameter in the sub-report and from the master report you are passing value of only one parameter then obviously the value of other parameters will be missing. I suggest you to set the default value for the parameters in the sub report, so that the report get displayed even if you are passing wrong values from the master report.

By the way what is the expression that you are passing? And how many parameters you have?

|||

Hi Rohit,

My sub report has only one parameter. In my master report, If I click a particular column, it should take me to the sub report, where I should be able to see the filtered report based on the passed value.

It is working for one of my report which uses a different cube.

Thanks & Regards,

Naveen

|||

Can you give the expression that you are passing?

|||

Rohit,

While creating a report, in Query builder I selected a parameter (which referes to a member of a dimension). Same parameter I'm using in my report. I am not using any expressions.

Thanks & Regards,

Naveen

|||

what is the expression you are writting in the navigation properties for the parameter of your sub report?

|||

Hi,

Jump to Report ->Sub_rpt (sub report name)

Parameter name -> DLVLTM

Parameter value -> =Fields!TM.Uniquename

Regards,

Naveen J V

|||

Actually everything seems to be fine. It may be a data issue though.

anyways again give it a try:

Go to Sub report and in the report parameter window try by making available values non-queried.

|||

Hi Rohit,

If I dont hide the parameter, the report works fine. If I hide, only then I get this error. In the present context, it should be hidden. Based on the selection made from the master report, it should disply the child report.

I dont understand wht could be the problem?

Regards,

Naveen

|||

Hi Rohit,

I'm getting the same error. Please refer to my rprevious mail.

Regards,

Naveen J V

parameter is missing a value.

Hi Friends,

I have created a child report with hidden parameter. When I call this report from the Master report using a link, I am getting error 'the xxxx parameter is missing a value'.

From, master report, if I click a value, it should take that value to child report where it filters for that value and should display the child report.

How to overcome this?

Thanks & Regards,

Naveen J V

Have you set any default value for the parameter in your subreport?

|||

Hi,

No default value has been set.

Regards,

Naveen J V

|||Ok, then try giving a default value for your hidden parameter. Give the same expression that you have used in the value field for the parameter. This might work. Not sure.|||

Hi,

I already tried this option, but I'm still getting the same error.

Thanks & Regards,

Naveen

|||

All the parameters created in sub-report should get some value, so if you have more than one parameter in the sub-report and from the master report you are passing value of only one parameter then obviously the value of other parameters will be missing. I suggest you to set the default value for the parameters in the sub report, so that the report get displayed even if you are passing wrong values from the master report.

By the way what is the expression that you are passing? And how many parameters you have?

|||

Hi Rohit,

My sub report has only one parameter. In my master report, If I click a particular column, it should take me to the sub report, where I should be able to see the filtered report based on the passed value.

It is working for one of my report which uses a different cube.

Thanks & Regards,

Naveen

|||

Can you give the expression that you are passing?

|||

Rohit,

While creating a report, in Query builder I selected a parameter (which referes to a member of a dimension). Same parameter I'm using in my report. I am not using any expressions.

Thanks & Regards,

Naveen

|||

what is the expression you are writting in the navigation properties for the parameter of your sub report?

|||

Hi,

Jump to Report ->Sub_rpt (sub report name)

Parameter name -> DLVLTM

Parameter value -> =Fields!TM.Uniquename

Regards,

Naveen J V

|||

Actually everything seems to be fine. It may be a data issue though.

anyways again give it a try:

Go to Sub report and in the report parameter window try by making available values non-queried.

|||

Hi Rohit,

If I dont hide the parameter, the report works fine. If I hide, only then I get this error. In the present context, it should be hidden. Based on the selection made from the master report, it should disply the child report.

I dont understand wht could be the problem?

Regards,

Naveen

|||

Hi Rohit,

I'm getting the same error. Please refer to my rprevious mail.

Regards,

Naveen J V

Paper about SQL Server 2005

Hey,

did anyone know a good paper or a good ms link about SQL Server 2005 -
because I have problems to install the Beta Version..

thanks very muchHi

SQL 2005 Beta newsgroups:

http://communities.microsoft.com/ne...ver2005.express

Regards
----------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@.epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Philipp" <Philipp_Gufler@.yahoo.de> wrote in message
news:42597997$1@.e-post.inode.at...
> Hey,
> did anyone know a good paper or a good ms link about SQL Server 2005 -
> because I have problems to install the Beta Version..
> thanks very much

Paging records on SQL server using derived tables

I am using derived tables to Page data on the SQL Server side.
I used this link as my mentor for doing paging on the SQL
Serverhttp://msdn2.microsoft.com/en-us/library/ms979197.aspx
I wanted to use USER PAGING, thus I used the following code:

CREATE PROCEDURE UserPaging
(
@.currentPage int = 1, @.pageSize int =1000
)
AS
DECLARE @.Out int, @.rowsToRetrieve int, @.SQLSTRING nvarchar(1000)

SET @.rowsToRetrieve = (@.pageSize * @.currentPage)

SET NOCOUNT ON
SET @.SQLSTRING = N'select
CustomerID,CompanyName,ContactName,ContactTitle from
( SELECT TOP '+ CAST(@.pageSize as varchar(10)) +
'CustomerId,CompanyName,ContactName,ContactTitle from
( SELECT TOP ' + CAST(@.rowsToRetrieve as varchar(10)) +
'CustomerID,CompanyName,ContactName,ContactTitle FROM
( SELECT TOP ' + CAST(@.rowsToRetrieve as varchar(10)) +
'CustomerID,CompanyName,ContactName,ContactTitle FROM Customers as T1
ORDER BY contactname) AS T2 ORDER BY contactname DESC ) AS T3)
As T4 ORDER BY contactname ASC'

EXEC(@.SQLSTRING)
RETURN
GO

When I use this. Assume that the Total records returned by the SQL
query is 1198.Thus when I am on Page1 the above Stored Proc (SP) will
return the first 1000 records.
This works absolutely fine.
Now I am on Page2, now I need to retrieve only the remaining 198
records.But if I use the above SP, it will return the last 1000
records.So to tweak this I used the following logic to set the
@.pagesize variable:
Declare @.PageCount int
select @.PageCount = @.TotalRows/@.PageSize
if @.currentPage @.PageCount SET @.PageSize = @.TotalRows%@.PageSize

Since I am on Page2 the above logic will set the PageSize to 198 and
not 1000.But when I use this logic, it takes forever for the SP to
return the 198 records in a resultset.
However if the TotalRows were = 1800, and thus the PageSize=800 or
greater, this SP returns the resultset quickly enough.

Thus to get over this problem I had to use the other logic i.e. using
Application Paging (i.e. first storing the entire result set into a
Temp table, then retrieving only the required records for the PAGE)

Can anyone suggest what is wrong with my user paging logic???
TIA...rbg (rbg.net@.gmail.com) writes:

Quote:

Originally Posted by

Now I am on Page2, now I need to retrieve only the remaining 198
records.But if I use the above SP, it will return the last 1000
records.So to tweak this I used the following logic to set the
@.pagesize variable:
Declare @.PageCount int
select @.PageCount = @.TotalRows/@.PageSize
if @.currentPage @.PageCount SET @.PageSize = @.TotalRows%@.PageSize
>
Since I am on Page2 the above logic will set the PageSize to 198 and
not 1000.But when I use this logic, it takes forever for the SP to
return the 198 records in a resultset.
However if the TotalRows were = 1800, and thus the PageSize=800 or
greater, this SP returns the resultset quickly enough.
>
Thus to get over this problem I had to use the other logic i.e. using
Application Paging (i.e. first storing the entire result set into a
Temp table, then retrieving only the required records for the PAGE)
>
Can anyone suggest what is wrong with my user paging logic???

I can't really say what happens, but if you study the query plans you may
do some discoveries.

But it goes to show that for paging there is not any single solution.
Aaron Bertrand has a fairly good overview on this site:
http://www.aspfaq.com/show.asp?id=2120.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Paging records on SQL server using derived tables

I am using derived tables to Page data on the SQL Server side.
I used this link as my mentor for doing paging on the SQL
Serverhttp://msdn2.microsoft.com/en-us/library/ms979197.aspx
I wanted to use USER PAGING, thus I used the following code:
CREATE PROCEDURE UserPaging
(
@.currentPage int = 1, @.pageSize int =10
)
AS
DECLARE @.Out int, @.rowsToRetrieve int, @.SQLSTRING nvarchar(1000)
SET @.rowsToRetrieve = (@.pageSize * @.currentPage)
SET NOCOUNT ON
SET @.SQLSTRING = N'select
CustomerID,CompanyName,ContactName,Conta
ctTitle from
( SELECT TOP '+ CAST(@.pageSize as varchar(10)) +
'CustomerId,CompanyName,ContactName,Cont
actTitle from
( SELECT TOP ' + CAST(@.rowsToRetrieve as varchar(10)) +
'CustomerID,CompanyName,ContactName,Cont
actTitle FROM
( SELECT TOP ' + CAST(@.rowsToRetrieve as varchar(10)) +
'CustomerID,CompanyName,ContactName,Cont
actTitle FROM Customers as T1
ORDER BY contactname) AS T2 ORDER BY contactname DESC ) AS T3)
As T4 ORDER BY contactname ASC'
EXEC(@.SQLSTRING)
RETURN
GO
When I use this. Assume that the Total records returned by the SQL
query is 1198.Thus when I am on Page1 the above Stored Proc (SP) will
return the first 1000 records.
This works absolutely fine.
Now I am on Page2, now I need to retrieve only the remaining 198
records.But if I use the above SP, it will return the last 1000
records.So to tweak this I used the following logic to set the
@.pagesize variable:
Declare @.PageCount int
select @.PageCount = @.TotalRows/@.PageSize
if @.currentPage > @.PageCount SET @.PageSize = @.TotalRows%@.PageSize
Since I am on Page2 the above logic will set the PageSize to 198 and
not 1000.But when I use this logic, it takes forever for the SP to
return the 198 records in a resultset.
However if the TotalRows were = 1800, and thus the PageSize=800 or
greater, this SP returns the resultset quickly enough.
Thus to get over this problem I had to use the other logic i.e. using
Application Paging (i.e. first storing the entire result set into a
Temp table, then retrieving only the required records for the PAGE)
Can anyone suggest what is wrong with my user paging logic'?
TIA...Hi
"rbg" wrote:

> I am using derived tables to Page data on the SQL Server side.
> I used this link as my mentor for doing paging on the SQL
> Serverhttp://msdn2.microsoft.com/en-us/library/ms979197.aspx
> I wanted to use USER PAGING, thus I used the following code:
> CREATE PROCEDURE UserPaging
> (
> @.currentPage int = 1, @.pageSize int =10
> )
> AS
> DECLARE @.Out int, @.rowsToRetrieve int, @.SQLSTRING nvarchar(1000)
> SET @.rowsToRetrieve = (@.pageSize * @.currentPage)
> SET NOCOUNT ON
> SET @.SQLSTRING = N'select
> CustomerID,CompanyName,ContactName,Conta
ctTitle from
> ( SELECT TOP '+ CAST(@.pageSize as varchar(10)) +
> 'CustomerId,CompanyName,ContactName,Cont
actTitle from
> ( SELECT TOP ' + CAST(@.rowsToRetrieve as varchar(10)) +
> 'CustomerID,CompanyName,ContactName,Cont
actTitle FROM
> ( SELECT TOP ' + CAST(@.rowsToRetrieve as varchar(10)) +
> 'CustomerID,CompanyName,ContactName,Cont
actTitle FROM Customers as T1
> ORDER BY contactname) AS T2 ORDER BY contactname DESC ) AS T3)
> As T4 ORDER BY contactname ASC'
> EXEC(@.SQLSTRING)
> RETURN
> GO
> When I use this. Assume that the Total records returned by the SQL
> query is 1198.Thus when I am on Page1 the above Stored Proc (SP) will
> return the first 1000 records.
> This works absolutely fine.
> Now I am on Page2, now I need to retrieve only the remaining 198
> records.But if I use the above SP, it will return the last 1000
> records.So to tweak this I used the following logic to set the
> @.pagesize variable:
> Declare @.PageCount int
> select @.PageCount = @.TotalRows/@.PageSize
> if @.currentPage > @.PageCount SET @.PageSize = @.TotalRows%@.PageSize
> Since I am on Page2 the above logic will set the PageSize to 198 and
> not 1000.But when I use this logic, it takes forever for the SP to
> return the 198 records in a resultset.
> However if the TotalRows were = 1800, and thus the PageSize=800 or
> greater, this SP returns the resultset quickly enough.
> Thus to get over this problem I had to use the other logic i.e. using
> Application Paging (i.e. first storing the entire result set into a
> Temp table, then retrieving only the required records for the PAGE)
> Can anyone suggest what is wrong with my user paging logic'?
> TIA...
>
Have you seen
http://databases.aspfaq.com/databas...-recordset.html
and
http://databases.aspfaq.com/databas...tic-paging.html
?
With SQL 2005 there are new functions that could help.
John

Paging records on SQL server using derived tables

I am using derived tables to Page data on the SQL Server side.
I used this link as my mentor for doing paging on the SQL
Serverhttp://msdn2.microsoft.com/en-us/library/ms979197.aspx
I wanted to use USER PAGING, thus I used the following code:
CREATE PROCEDURE UserPaging
(
@.currentPage int = 1, @.pageSize int =10
)
AS
DECLARE @.Out int, @.rowsToRetrieve int, @.SQLSTRING nvarchar(1000)
SET @.rowsToRetrieve = (@.pageSize * @.currentPage)
SET NOCOUNT ON
SET @.SQLSTRING = N'select
CustomerID,CompanyName,ContactName,ContactTitle from
( SELECT TOP '+ CAST(@.pageSize as varchar(10)) +
'CustomerId,CompanyName,ContactName,ContactTitle from
( SELECT TOP ' + CAST(@.rowsToRetrieve as varchar(10)) +
'CustomerID,CompanyName,ContactName,ContactTitle FROM
( SELECT TOP ' + CAST(@.rowsToRetrieve as varchar(10)) +
'CustomerID,CompanyName,ContactName,ContactTitle FROM Customers as T1
ORDER BY contactname) AS T2 ORDER BY contactname DESC ) AS T3)
As T4 ORDER BY contactname ASC'
EXEC(@.SQLSTRING)
RETURN
GO
When I use this. Assume that the Total records returned by the SQL
query is 1198.Thus when I am on Page1 the above Stored Proc (SP) will
return the first 1000 records.
This works absolutely fine.
Now I am on Page2, now I need to retrieve only the remaining 198
records.But if I use the above SP, it will return the last 1000
records.So to tweak this I used the following logic to set the
@.pagesize variable:
Declare @.PageCount int
select @.PageCount = @.TotalRows/@.PageSize
if @.currentPage > @.PageCount SET @.PageSize = @.TotalRows%@.PageSize
Since I am on Page2 the above logic will set the PageSize to 198 and
not 1000.But when I use this logic, it takes forever for the SP to
return the 198 records in a resultset.
However if the TotalRows were = 1800, and thus the PageSize=800 or
greater, this SP returns the resultset quickly enough.
Thus to get over this problem I had to use the other logic i.e. using
Application Paging (i.e. first storing the entire result set into a
Temp table, then retrieving only the required records for the PAGE)
Can anyone suggest what is wrong with my user paging logic'?
TIA...Hi
"rbg" wrote:
> I am using derived tables to Page data on the SQL Server side.
> I used this link as my mentor for doing paging on the SQL
> Serverhttp://msdn2.microsoft.com/en-us/library/ms979197.aspx
> I wanted to use USER PAGING, thus I used the following code:
> CREATE PROCEDURE UserPaging
> (
> @.currentPage int = 1, @.pageSize int =10
> )
> AS
> DECLARE @.Out int, @.rowsToRetrieve int, @.SQLSTRING nvarchar(1000)
> SET @.rowsToRetrieve = (@.pageSize * @.currentPage)
> SET NOCOUNT ON
> SET @.SQLSTRING = N'select
> CustomerID,CompanyName,ContactName,ContactTitle from
> ( SELECT TOP '+ CAST(@.pageSize as varchar(10)) +
> 'CustomerId,CompanyName,ContactName,ContactTitle from
> ( SELECT TOP ' + CAST(@.rowsToRetrieve as varchar(10)) +
> 'CustomerID,CompanyName,ContactName,ContactTitle FROM
> ( SELECT TOP ' + CAST(@.rowsToRetrieve as varchar(10)) +
> 'CustomerID,CompanyName,ContactName,ContactTitle FROM Customers as T1
> ORDER BY contactname) AS T2 ORDER BY contactname DESC ) AS T3)
> As T4 ORDER BY contactname ASC'
> EXEC(@.SQLSTRING)
> RETURN
> GO
> When I use this. Assume that the Total records returned by the SQL
> query is 1198.Thus when I am on Page1 the above Stored Proc (SP) will
> return the first 1000 records.
> This works absolutely fine.
> Now I am on Page2, now I need to retrieve only the remaining 198
> records.But if I use the above SP, it will return the last 1000
> records.So to tweak this I used the following logic to set the
> @.pagesize variable:
> Declare @.PageCount int
> select @.PageCount = @.TotalRows/@.PageSize
> if @.currentPage > @.PageCount SET @.PageSize = @.TotalRows%@.PageSize
> Since I am on Page2 the above logic will set the PageSize to 198 and
> not 1000.But when I use this logic, it takes forever for the SP to
> return the 198 records in a resultset.
> However if the TotalRows were = 1800, and thus the PageSize=800 or
> greater, this SP returns the resultset quickly enough.
> Thus to get over this problem I had to use the other logic i.e. using
> Application Paging (i.e. first storing the entire result set into a
> Temp table, then retrieving only the required records for the PAGE)
> Can anyone suggest what is wrong with my user paging logic'?
> TIA...
>
Have you seen
http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html
and
http://databases.aspfaq.com/database/how-do-i-handle-alphabetic-paging.html ?
With SQL 2005 there are new functions that could help.
John