Solved

SSRS 2008 dynamic Parameters

Posted on 2013-01-24
4
444 Views
Last Modified: 2013-01-30
I am creating a report with several parameters for users to insert data into a table.  
Staff_Name that is filled with a SP.
Client_Name that is filled based on the Staff_Name choice from a SP.
The user will pick the option of 'New Service' or a service already inserted prieviously by the user.
Textboxs and date fields that the user will enter data to be inserted into the Table.
What I would like the report to do is when the user selects 'New Service' the textboxs and data fields would be blank.  If the user selects a service they already create to update fields (inserting a new row into the table and recorddeleting the old row.)

I have created the SP for the textboxs and date fields:
IF @ClientLabId <> 'New%' BEGIN
SELECT 
 cc.Date_Service_Referral, cc.Ordering_Provider, cc.Provider_Specialty, cc.Provider_Contact, cc.Desc_of_Order,
 cc.First_Scheduled_Date_Service, cc.Attendance_Confirmed, cc.Date_Record_New_Service_Receieved,
 cc.Date_Record_Ordering_Provider_Confirmed, cc.Date_Record_PCP_Confirmed	
FROM CCMT_Service_Referral_Tracking cc
WHERE cc.Client_Service_Id = @ClientLabId 
AND cc.RecordDeleted = 'N'
END
IF @ClientLabId = 'New%' BEGIN
SELECT 
 NULL AS Date_Service_Referral, NULL AS Ordering_Provider, NULL AS Provider_Specialty, NULL AS Provider_Contact, 
 NULL AS Desc_of_Order, NULL AS First_Scheduled_Date_Service, NULL AS Attendance_Confirmed, NULL AS Date_Record_New_Service_Receieved,
 NULL AS Date_Record_Ordering_Provider_Confirmed, NULL AS Date_Record_PCP_Confirmed	
END

Open in new window

Setting the output of the query to the default values of the SSRS seems to lock the parameters and using it as the available values creates a Dropdown the will not allow the user to enter data.  Is there a way to code this so it will populate the parameters or leave them null depending on the user third choice?
0
Comment
Question by:skaighin
  • 2
  • 2
4 Comments
 
LVL 6

Expert Comment

by:liija
Comment Utility
Not 100% sure how that should work.
But two tips; third parameter is not able to affect on the two first parameter values. This you should have noticed already.
SSRS doesn't like NULL values. How about replacing the current NULL's with '' in the data source queries?

Could you provide with a screenshot on the report parameters, that might make more clear what you are after?
0
 

Author Comment

by:skaighin
Comment Utility
Here are screenshots of the running report and parameters.  I am trying to get it so that the textboxes and date fields are filled depending on what the user picks in the Client Service id.
ReportDisplay.jpg
ReportDisplay2.jpg
0
 
LVL 6

Accepted Solution

by:
liija earned 500 total points
Comment Utility
Hmm, I had never used SSRS that way. Had to run a few tests.

In fact you can't utilize parameters' default values like you'd like.
Default values are filled in once, when you open the report. You cannot affect on the default values again.

Utilizing parameter dependency won't work either. Then you'd have to use 'Get values from query' in your parameter. With that - you are not able to fill in free text as the value as you would like.

Right now my best advice is to build two separate reports. Other to use the existing values and other with a possibility to fill in new values to the database.
0
 

Author Closing Comment

by:skaighin
Comment Utility
Thanks for the feedback.  I was afraid that was what I was going to have to do with this report.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

763 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

7 Experts available now in Live!

Get 1:1 Help Now