| Author |
Topic |
|
werseyjersey
Starting Member
47 Posts |
 Posted - 2008-09-06 : 09:51:24
|
| Hello! I have the following problem and I am VERY VERY new to SQL Reporting Services (I know the basics): 1.) I have a long complicated Stored Procedure that pulls for various tables the information I want to display on my report. Works GREAT. 2.) I want to add three parameters to the report that the user can select from a listing of the data within those fields: a.) Company - the company I want the report (ftereporttypedesc) b.) Payroll Start Date - the ending date of the 1st payroll (same field as c) - PayrollEndDate (PayrollMaster.PayrollEnd) c.) Payroll End Date - the ending date of the 2nd payrol (same field as b) - PayrollEndDate3.) I have one parameter in the original stored procedure (PED which is a text box we type in) I don't want them to free form type I want them to select from the box in #2 above.4.) The three parameters are coming from tables that are NOT included in the first (#1) stored procedure -- the only common factor is the PayrollMasterID:SELECT PayrollMaster.PayrollMasterID, lu_ftereporttype.ftereporttypeid, lu_ftereporttype.ftereporttypedesc, lu_payrolltype.CCC, PayrollMaster.PayrollStart, PayrollMaster.PayrollEnd, PayrollMaster.CCC AS CompanyNameFROM lu_ftereporttype INNER JOIN lu_payrolltype ON lu_ftereporttype.ftereporttypeid = lu_payrolltype.ftereporttypeid INNER JOIN PayrollMaster ON lu_payrolltype.CCC = PayrollMaster.CCCwhere PayrollMaster.PayrollEnd >= @PEDStDt and PayrollMaster.PayrollEnd <= @PEDEndDt and ftereporttypeid = @ReportTypeI am unsure the steps I need to take to make sure the link is made between this query and the original stored procedure in SQL Reporting Services. I have tried adding the parameter and selecting the fields but I seem to have missed something. Can some one give me a step by step on how to do this as I am new to this and am the type of person that needs to understand how to do this going forward to understand the concept. I have a couple books but neither takes me through each step necessary to make this work with my original SP. If I need to turn the above query into a stored procedure then let me know that also and it wouldn't be a problem. It is further my understanding that you can only pass ONE parameter per dataset, is that correct? Thanks.werseyjersey |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-07 : 02:40:31
|
| 1.Go to datatab & create a new dataset (add new dataset from dropdown)2. Give dataset a suitable name,select the datasource(create one if havent created one already. Select commandtype value as text. and give your query in text area below. click apply ok. 3. run the query (! icon on top). it will prompt you for values of involved parameters (@PEDStDt,@PEDEndDt ,..). give suitable values and click ok. you can see datatab grid populated with data for passed params. click refresh on top. 4. click view ->datasets from top menu. now you can see dataset on left window. on expanding you'll see fields in your dataset (as provided by query.5. click on reports->report parameters and you'll see list being prepopulated by parameters used in query. you can change their datatype and name if you want (by default it assumes name of param used in query and also value from comparing field).6. Go to preview tab and you will see input boxes for parameters.You can make it dropdown if you want by creating a new dataset for populating values and using dataset as source for parameter by using option from query in 5th point above. |
 |
|
|
werseyjersey
Starting Member
47 Posts |
Posted - 2008-09-07 : 22:25:08
|
| I'll give it a whirl. I still don't understand how it knows what the link is between the original stored procedure and the dataset but maybe when I do the above it will click.Additionally, is it true that only ONE parameter per dataset can be passed into the report, is that correct?werseyjersey |
|
|
|
werseyjersey
Starting Member
47 Posts |
Posted - 2008-09-08 : 08:54:01
|
| I did what you said step by step. I am now getting this error for each parameter:[rsInvalidReportParameterDependency] The report parameter ‘PEDEndDt’ has a DefaultValue or a ValidValue that depends on the report parameter “PEDEndDt”. Forward dependencies are not valid.What did I do wrong?werseyjersey |
|
|
|
werseyjersey
Starting Member
47 Posts |
Posted - 2008-09-10 : 09:21:46
|
| Thought I'd update I got this to work with the steps above plus a couple other items that were left off. I'll update with more notes when I have time.werseyjersey |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-13 : 14:09:33
|
quote:
Originally posted by werseyjersey
Thought I'd update I got this to work with the steps above plus a couple other items that were left off. I'll update with more notes when I have time.werseyjersey
No problem. Let us know if you face any more issues. |
|
|
|
werseyjersey
Starting Member
47 Posts |
Posted - 2008-10-27 : 15:18:17
|
| As promised here the steps to set up a parameter:How to set up a Parameter for SQL Reporting Services:1. Determine the main dataset(s) needed to produce the report. This should have ALL fields as well as the parameters the report requires to be generated. Example: FTEReportSP 2.) Create the individual parameter datasets in a stored procedure. a. @PEDStDt and @PEDEndDt b. @FTEReportType3.) Modify the Main dataset by adding the following to the top section of the Stored Procedure:4.) ALTER the stored procedure as necessary for the paraments previously described. Finally, add the following in the group by area of the statement: pm.PayrollEnd >= @PEDStDt and pm.PayrollEnd <= @PEDEndDt and rt.ftereporttypeid = @FTEReportTypeid --and occ.GLSegment = '620'5.) Add a new Stored Procedure for each dataset required to complete the report. 6.) Execute and refresh the list of Stored Procedures added.7.) Click on the … next to the dataset on the Data Tab.8.) Add the following dataset information: a. Query Tab: i. Name: dsPEDStDt ii. Data Source: Data Source 1 (same source as main query) iii. Command Type: StoredProcedure iv. Click OK and select the stored procedure from the drop down list and it will populate the Query String: below b. Fields Tab: i. Fields name: as shown below c. This will be the field that will be passed to the report and associated with the main report. Parameters Tab: i. Name @FTEReportTypeID ii. Value: Click on the applicable field 9.) Add the following dataset information: d. Query Tab: i. Name: dsFTEReportType ii. Data Source: Data Source 1 (same source as main query) iii. Command Type: StoredProcedure iv. Click OK and select the stored procedure from the drop down list and it will populate the Query String: below e. Fields Tab: i. Fields name: as shown below10.) Add the following dataset information: f. Query Tab: i. Name: dsPEDEndDt ii. Data Source: Data Source 1 (same source as main query) iii. Command Type: StoredProcedure iv. Click OK and select the stored procedure from the drop down list and it will populate the Query String: below g. Fields Tab: i. Fields name: as shown below h. This will be the field that will be passed to the report and associated with the main report. This also sets the report priority order. Parameters Tab: i. Name @FTEReportTypeID ii. Value: Click on the applicable field iii. Name @PEDStDt iv. Value: Click on the applicable field11.) Click on Report: i. Click on Report Parameters: j. Notice that the previously set up Parameters will appear in the dialog box once it pops up. k. Set the following Parameter(s) settings as indicated: i. Name: FTEReportTypeID ii. Data Type: String iii. Prompt: FTEReportTypeID iv. Dataset: dsFTEReport TypeID v. Value Field: ftereporttypeid vi. Label Field: ftereporttypedescl1A. Set the following Parameter(s) settings as indicated: i. Name: PEDStDt ii. Data Type: DateTime iii. Prompt: Start Date iv. Dataset: dsPEDStDt v. Value Field: PayrollEnd vi. Label Field: PayrollEnd m. Set the following Parameter(s) settings as indicated: i. Name: PEDEndDT ii. Data Type: DateTime iii. Prompt: End Date iv. Dataset: dsPEDEndDt v. Value Field: PayrollEnd vi. Label Field: PayrollEnd n. Create the report, if it hasn’t already been created.12.) Click on Preview: a. Click FTEReportTypeID and select the type required. b. Click on Start Date and select the Date to start from the drop down list. c. Click on End Date and select the Date to end on from the drop down list.13.) Click on “View Report” and notice that the data will be pulled to the report!I hope this helps someone else that is new to SQL or maybe someone who doesn't use it alot! Happy reporting!!!! |
|
|
|
andros30
Yak Posting Veteran
80 Posts |
Posted - 2008-10-27 : 17:21:25
|
| I'm also getting started with RS and working with parameters. I'll have to digest your list and see if I can get anything useful out of it... Here is my situation:I have a stored procedure which has two pass-in parameters defaulted to null. Here is what it looks like:ALTER PROCEDURE [dbo].[usp_NeverBanded]@StartDate datetime = null,@EndDate datetime = null...if @startdate is null beginset @startdate = dateadd(mm, -1, dbo.udf_FirstDayofMonth(getdate()))endif @enddate is null beginset @enddate = dbo.udf_FirstDayofMonth(getdate())endIn my layout, I added two parameters which allow nulls. I also added a text box with the following expression:="Date Range: " & Format(Parameters!StartDate.Value,"d") & " to " & Format(Parameters!EndDate.Value,"d")When I view the report, my text box doesn't return the dates because they were defaulted to Nulls. I do get results back though. When I uncheck the Null and select the dates, then the text box shows the date range properly.So my question is how can I format my expression so that if the date fields are null then it will get the 1st day of last month to the 1st day of the current month. |
|
|
|
|
Step by Step to add Parameters to a report