Solved

SSRS 2008 dynamic Parameters

Posted on 2013-01-24
4
461 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
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.

730 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