?
Solved

SSRS 2008 dynamic Parameters

Posted on 2013-01-24
4
Medium Priority
?
511 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
ID: 38815936
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
ID: 38816145
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 2000 total points
ID: 38817608
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
ID: 38835638
Thanks for the feedback.  I was afraid that was what I was going to have to do with this report.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

864 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