SSRS 2008 dynamic Parameters

Question

View of final report . We are currently using MS SQL server 2008 R2 along with SSRS 2008. 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 to be populated based on the 3rd choice:

IF @ClientServiceId <> '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 = @ClientServiceId 
AND cc.RecordDeleted = 'N'
END
IF @ClientServiceId = '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

Setting the values of the user defined parameters to the default values of the SSRS seems to lock the parameters even after the third parameter changes 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?

How many peraments you have. Are you try to update and enter data in to table from report perameter? — Hiten004
your other question stackoverflow.com/questions/14490200/… and this question are similar right? — Hiten004
Yeah I am trying to tackle it different ways but I think that I am going to have to make two different report to do what I want. I am a .net programmer and we just started using SSRS. In .net you can populate a field(parameter) on the fly but in SSRS it seems that is not the case. — Michael Rowley
Are you all set?. take look at this linlk have Alternative SSRS Front-ends stackoverflow.com/questions/5272753/alternative-ssks-front-ends — Hiten004

djangojazz djangojazz · Accepted Answer · 2013-01-25T22:45:17

I would do this(shortening your example a little bit):

Set up a first variable with choices 'New' or something else. I chose text and then for 'available values' I chose 'specify values' and put in 'New' and 'Not New' with the label and the value being the same. I set my name of the variable as 'Parm'.
Set up a dataset with the code like what you stated above but ensure that the data condition is the name of the variable set above. Call this dataset 'Choices'
```
If @Parm <> 'New'
Select 'I am not new'
else 
select 'I am new'
```
Now set up a second parameter. Choose 'Available Values' and specify 'Get values from a query'. Choose your dataset 'Choices' created above and select the given 'ID' value for both.

You now have a DEPENDENT variable with selections determined by your first choice's logic. You can change the dataset as needed.

SSRS 2008 dynamic Parameters

1 Answers