Peduliar problem in SSRS

Hi all ,

I am encountering a peculiar problem while creating a linked report in SSRS.

The scenario:

My first reports contains 3 columns . The first 3 columns contain data from vendor table in AdventureWorks DB. They are VendorID , Name, CreditRating. The fourth column contains a link to the second report. It is just a link which jumps to the other report and passes the above three values as parameters to the second report.

The second report contains 4 parameters which includes VendorID , Name, CreditRating and one more Postedby parameter which has to be entered manually when the second report is displayed. The three parameters from first report are passed on and one extra parameter has to entered manually then only the second report displays.

It works fine when I check it on Visual Studio preview mode. I click the link for a particular row in First Report and it asks me to enter the Postedby Parameter. After entering it it displays the 2nd report. But the problem arises when I deploy it to report server. When I click the link on first report it throughs an error like " The Parameter 'PostedBy' is missing a value" whereas it should prompt the user to enter the PostedBy Parameter.

I am stuck in this and not able to understand why this happens in ReportServer and not in Visual Studio Preview mode.

Does anybody has any idea why this is happening ?

Any Suggestion will be appreciated.

Regards...

Girija Shankar Beuria

You must specify the default value for the fourth parameter. Visual Studio is an interactive environment, so you get to enter the value on request by the VS. But in production environment, reports are consumed by other applications and the report will not be able to request for an input from the calling application and this applies to Report Manager also which is an external web application that consumes the reporting services.

Shyam

|||

just go to the code and see if there is any parameter hard coded in xml.

Remove that parameter and save it .

This will probably solve your problem.

Vamsi Krishna Korasiga

Peculiar T-SQL requirement.

Hi all,

I have a scenario in which I need to do an amount balancing ( meaning, OFFSET few records with positive amount whose sum equals the value in the negative amount in the same recordset).

Consider the below example.

Col1 Col2 Amount OFFSet

A 01 100 0
A 01 20 0
A 01 30 0
A 01 25 0
A 01 -145 0
A 01 15 0

Above 6 records are fetched for a particular condition. My requirement now is to update the OFFSET column of the positive valued records whose sum equals the value in the negative amount (145) .

That is, I need the below result.

Col1 Col2 Amount OFFSet

A 01 100 1
A 01 20 1
A 01 30 0
A 01 25 1
A 01 -145 0
A 01 15 0

Only the records whose sum of amount matches the negative amount value should be OFFSet'd no matter in what sequence they are.

I guess I'm confusing a bit, let me know if you need more explanation.

Any help would be appreciated.

Thanks,

DBLearner

What you describe is a HUGE logic problem, not a SQL problem. That is very complicated to accomplish.

What do you do with multiple records match your criteria. For example:

Col1 Col2 Amount OFFSet

A 01 100 0
A 01 20 0
A 01 30 0
A 01 25 0
A 01 -145 0
A 01 15 0
A 01 25 0
A 01 100 0
A 01 25 0
A 01 25 0
A 01 25 0
A 01 15 0
A 01 10 0

What now?

-145 = 100+20+25
-145 = 25+25+25+25+20+25
-145 = 100+20+15+10
etc

Peculiar T-SQL requirement.

Hi all,

I have a scenario in which I need to do an amount balancing ( meaning, OFFSET few records with positive amount whose sum equals the value in the negative amount in the same recordset).

Consider the below example.

Col1 Col2 Amount OFFSet

A 01 100 0
A 01 20 0
A 01 30 0
A 01 25 0
A 01 -145 0
A 01 15 0

Above 6 records are fetched for a particular condition. My requirement now is to update the OFFSET column of the positive valued records whose sum equals the value in the negative amount (145) .

That is, I need the below result.

Col1 Col2 Amount OFFSet

A 01 100 1
A 01 20 1
A 01 30 0
A 01 25 1
A 01 -145 0
A 01 15 0

Only the records whose sum of amount matches the negative amount value should be OFFSet'd no matter in what sequence they are.

I guess I'm confusing a bit, let me know if you need more explanation.

Any help would be appreciated.

Thanks,

DBLearner

What you describe is a HUGE logic problem, not a SQL problem. That is very complicated to accomplish.

What do you do with multiple records match your criteria. For example:

Col1 Col2 Amount OFFSet

A 01 100 0
A 01 20 0
A 01 30 0
A 01 25 0
A 01 -145 0
A 01 15 0
A 01 25 0
A 01 100 0
A 01 25 0
A 01 25 0
A 01 25 0
A 01 15 0
A 01 10 0

What now?

-145 = 100+20+25
-145 = 25+25+25+25+20+25
-145 = 100+20+15+10
etc

sql

Passing SELECTed rows to a calling SP

Hi!
I'm a new T-SQL developer and just hit a roadblock.
I have a scenario that goes like this: I have 2 stored procedures,
spInner and spOuter. spInner has a SELECT statement which would
normally be used by a class using MS Enterprise Library and that output
goes into a DataSet. However, I need to get the output of the SELECT
statement to go into spOuter and that's what I can't seem to figure
out.
I know I may be asked to use functions that return tables in replies to
this post, but I can't do that as some parts of my application have an
EXEC(string) for dynamic SQL, instead of spInner.
Any help appreciated.
Cheers,
N.I.T.I.N.For the results of spInner to be available to spOuter, there are a few
options. I have used temporary tables or persisted the data into a real
table to avoid recompiles and also have a record of the data. If you use the
latter option, you can use a guid column and each row with a guid obtained
from spOuter. This way you can easily pick up the rows you need.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||NiTiN (emailme.nitin@.gmail.com) writes:
> I'm a new T-SQL developer and just hit a roadblock.
> I have a scenario that goes like this: I have 2 stored procedures,
> spInner and spOuter. spInner has a SELECT statement which would
> normally be used by a class using MS Enterprise Library and that output
> goes into a DataSet. However, I need to get the output of the SELECT
> statement to go into spOuter and that's what I can't seem to figure
> out.
> I know I may be asked to use functions that return tables in replies to
> this post, but I can't do that as some parts of my application have an
> EXEC(string) for dynamic SQL, instead of spInner.
I have an article on my web site that discusses possible options. See
http://www.sommarskog.se/share_data.html.
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

Passing SELECTed rows to a calling SP

Hi!
I'm a new T-SQL developer and just hit a roadblock.
I have a scenario that goes like this: I have 2 stored procedures,
spInner and spOuter. spInner has a SELECT statement which would
normally be used by a class using MS Enterprise Library and that output
goes into a DataSet. However, I need to get the output of the SELECT
statement to go into spOuter and that's what I can't seem to figure
out.
I know I may be asked to use functions that return tables in replies to
this post, but I can't do that as some parts of my application have an
EXEC(string) for dynamic SQL, instead of spInner.
Any help appreciated.
Cheers,
N.I.T.I.N.For the results of spInner to be available to spOuter, there are a few
options. I have used temporary tables or persisted the data into a real
table to avoid recompiles and also have a record of the data. If you use the
latter option, you can use a guid column and each row with a guid obtained
from spOuter. This way you can easily pick up the rows you need.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||NiTiN (emailme.nitin@.gmail.com) writes:
> I'm a new T-SQL developer and just hit a roadblock.
> I have a scenario that goes like this: I have 2 stored procedures,
> spInner and spOuter. spInner has a SELECT statement which would
> normally be used by a class using MS Enterprise Library and that output
> goes into a DataSet. However, I need to get the output of the SELECT
> statement to go into spOuter and that's what I can't seem to figure
> out.
> I know I may be asked to use functions that return tables in replies to
> this post, but I can't do that as some parts of my application have an
> EXEC(string) for dynamic SQL, instead of spInner.
I have an article on my web site that discusses possible options. See
http://www.sommarskog.se/share_data.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Passing SELECTed rows to a calling SP

Hi!

I'm a new T-SQL developer and just hit a roadblock.

I have a scenario that goes like this: I have 2 stored procedures,
spInner and spOuter. spInner has a SELECT statement which would
normally be used by a class using MS Enterprise Library and that output
goes into a DataSet. However, I need to get the output of the SELECT
statement to go into spOuter and that's what I can't seem to figure
out.

I know I may be asked to use functions that return tables in replies to
this post, but I can't do that as some parts of my application have an
EXEC(string) for dynamic SQL, instead of spInner.

Any help appreciated.

Cheers,
N.I.T.I.N.For the results of spInner to be available to spOuter, there are a few
options. I have used temporary tables or persisted the data into a real
table to avoid recompiles and also have a record of the data. If you use the
latter option, you can use a guid column and each row with a guid obtained
from spOuter. This way you can easily pick up the rows you need.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||NiTiN (emailme.nitin@.gmail.com) writes:

Quote:

Originally Posted by

I'm a new T-SQL developer and just hit a roadblock.
>
I have a scenario that goes like this: I have 2 stored procedures,
spInner and spOuter. spInner has a SELECT statement which would
normally be used by a class using MS Enterprise Library and that output
goes into a DataSet. However, I need to get the output of the SELECT
statement to go into spOuter and that's what I can't seem to figure
out.
>
I know I may be asked to use functions that return tables in replies to
this post, but I can't do that as some parts of my application have an
EXEC(string) for dynamic SQL, instead of spInner.

I have an article on my web site that discusses possible options. See
http://www.sommarskog.se/share_data.html.

--
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