Solved

Stored Procedure to pass parameter to another SSRS report

Posted on 2010-08-24
2
845 Views
Last Modified: 2012-06-27
Hi Experts,

I have been unemployed since May and I will land this job if I can prove that I can pass parameters from one SSRS report to another with a Stored Procedure.

There are 2 reports, Sales Person Performance and Sales Person Detail.
In the performance table ContactID column I have a navigation property that jumps to the detail report. I pass both of the date parameters there.

I need to figure out how to make an SP that will grab the value of the ContactID that was clicked on and make it available for use as a parameter in the detail report.

Can anyone show me how to create such a procedure and use the value as a parameter in my detail report?

I am pasting the queries for the data sets for both reports below.

Performace Report:
SELECT     TOP (10) SUM(Sales.SalesOrderHeader.TotalDue) AS TotalDue, Person.Contact.FirstName, Person.Contact.LastName, Person.Contact.ContactID
FROM         Sales.SalesOrderHeader INNER JOIN
                      Person.Contact ON Sales.SalesOrderHeader.ContactID = Person.Contact.ContactID INNER JOIN
                      Sales.SalesPerson ON Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.SalesPersonID
WHERE     (Sales.SalesOrderHeader.OrderDate BETWEEN @StartDate AND @EndDate)
GROUP BY Person.Contact.LastName, Person.Contact.FirstName, Person.Contact.ContactID
ORDER BY TotalDue DESC

Detail Report:
SELECT     Sales.SalesOrderHeader.TotalDue, Person.Contact.FirstName, Person.Contact.LastName, Person.Contact.ContactID
FROM         Sales.SalesOrderHeader INNER JOIN
                      Person.Contact ON Sales.SalesOrderHeader.ContactID = Person.Contact.ContactID INNER JOIN
                      Sales.SalesPerson ON Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.SalesPersonID
WHERE     (Sales.SalesOrderHeader.OrderDate BETWEEN @DetailStartDate AND @DetailEndDate)
ORDER BY Person.Contact.ContactID

My 3rd interview with this company is this Thursday 8/26/2010
Any help would be so greatly appreciated!

Thanks

Dan
 
0
Comment
Question by:DanPerlman
2 Comments
 
LVL 7

Accepted Solution

by:
rashmi_vaghela earned 500 total points
Comment Utility
0
 

Author Comment

by:DanPerlman
Comment Utility
rashmi,

I have looked at the links provided. I don;t see what I'm looking for.
I need a stored procedure to get the value of the cell in the table that the user clicked on. When the jump to report fires, I need to use that specific value as a parameter in the detail report. I only want to display the contact id that was selected from the performance report in the detail report.
Does that make sense?

Thanks

Dan
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now