Solved

SSRS 2008 dynamic Parameters

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Error in query 3 52
Help with SQL - TOP 10 by date and by group 13 33
Cant preview report change unless report is save first 5 29
SQL profiler equivalent in MS-Access 3 42
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

911 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

20 Experts available now in Live!

Get 1:1 Help Now