Solved

SSRS 2008 dynamic Parameters

Posted on 2013-01-24
4
472 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

729 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