CODEWITHSUNDEEP
sqlsql-server-2008reporting-servicesssrs-2008
Michael Rowley
Michael Rowley

Reputation: 667

ssrs 2008 cascading parameters

I am currently using SQL 2008 R2 with SQL server Report Service 2008. I am creating a report with the following parameters - Staff_name, Client_name, Lab, lab_date, etc. When the user selects the Staff name the Client they service will populate a drop-down for them to choose and this works fine. When the user selects a client then the clients labs will populate the Lab drop-down. I have a SP that returns the values of the lab dataset of a chosen lab or empty values if 'New Lab' is picked. The problem is that if I default the parameters below Lab, ex. Lab_date, with the appropriate variable from the sp then choose a Lab and then choose 'New Lab' the Lab_date does not empty.

IF @ClientLabId <> 'New Lab' BEGIN
SELECT 
cc.Date_Lab_Test_Ordered, cc.Ordering_Provider, cc.Provider_Specialty, 
cc.Provider_Contact, cc.Desc_of_Order, cc.Date_Order_Filled, 
cc.Date_Receipt_Verified
FROM CCMT_Lab_Test_Orders_Tracking cc
WHERE cc.Client_Lab_Id = @ClientLabId 
AND cc.RecordDeleted = 'N'
END
IF @ClientLabId = 'New Lab' BEGIN
SELECT ' ',' ',' ',' ',' ',' ',' '
END

I have tried NULL, '', ' ' and the report displays these values as long as you do not pick an actual lab. I know that the first part of the SP works as it sends the data when the SP is set to default. It is the 'New Lab' part that I can not figure out as to what I need to send to SSRS to get the values when 'New Lab' is selected after erased. We want employees to not have to reenter data twice and to eliminate as many errors in data as possible.

As an after thought - Is there a way to force the report to completely refresh after execution. Instead of going out of the report and back the report would just refresh to new every time the user clicked view report?

Upvotes: 1

Views: 922

Answers (2)

Michael Rowley
Michael Rowley

Reputation: 667

After looking at different forums and talking with several IT professionals, I have desided to create two seperate forms. One for the insert and one for the update as SSRS does not seem to have the functionality to do a single form.

Upvotes: 0

Eduardo Molteni
Eduardo Molteni

Reputation: 39413

You are missing the column names. Try with:

SELECT 
' ' as Date_Lab_Test_Ordered, ' ' as Ordering_Provider, ' ' as Provider_Specialty, 
' ' as Provider_Contact, ' ' as Desc_of_Order, ' ' as Date_Order_Filled, 
' ' as Date_Receipt_Verified

Upvotes: 1

Related Questions