July 27, 2010 at 7:44 am
Hi, I'm new to Reporting Services and looking for some advice/tips on how to do some troubleshooting. I have some reports that I imported from an Access database. When they converted from Access to Reporting Services, it brought over input boxes and converted them into parameters. The input boxes were "Start Date" and "End Date". (These reports run for specific time frames.) Question....do I have to edit the parameters or filters tab of the Dataset properties panel to "attach" the parameters to the dataset? When I run the report(s) they always return the entire data set no matter what dates I put in the parameter boxes. The report isn't filtering based on the values that I enter in the parameter boxes. I'm using Visual Studio 2008 to do my development and the data source is currently looking at the Access database though this will change over to SQL Server in the future. This is my first project in Reporting Services so if I'm not providing enough/correct information, please let me know. Any replies are greatly appreciated!
July 27, 2010 at 8:18 am
Look at your data set, parameters. They should have a parameter value that looks to the report parameter value
July 28, 2010 at 7:42 am
Can you clarify what you mean please? I'm sure this is a very elementary concept but I don't know what you're asking. I understand what you mean when you say "Look at your data set, parameters". But what do you mean when you say "they should have a parameter value that looks to the report parameter value"?
FYI: I'm trying to do a prior month date range for my report. The code for my "beginning date" parameter is:
=formatdatetime(dateserial(year(now), month(now)-1, 1))
The code for my "ending date" parameter is:
=formatdatetime(dateserial(year(now), month(now), 0))
When I do a Preview of the report the above parameter values that are returned are
6/1/2010 and 6/30/2010, and this is what I want.
However, the data that is returned in the report is every record in the table, and the data in the table goes all the way back to 2006.
thanks!
July 28, 2010 at 7:57 am
Ducati: You can use the parameters in the queries themselves. The query should already be a SELECT statement of some kind. You'd want to add something like this:
WHERE [DateField] BETWEEN @beginning_date AND @ending_date
The name you would use is set in the Report > Report Parameters dialog. Under properties, it says name. Add a @ before it to reference it as a variable in a query.
July 28, 2010 at 8:08 am
I used your code and it works! However....when I go into Query Designer and add in my parameter names in the WHERE clause, it doesn't like the ampersand, (@) sign and gives the Unable to parse query text box. When I use the parameter name without the ampersand it does work. Think I'm doing something wrong or is this expected??
Also, here's another question. I have another report that has 2 datasets, (queries). One of the datasets provides all the base data. (I have 2 parameters here called "Start Date" and "End Date" that filter on this base data) The other dataset has only one field in it, "Department" and a 3rd parameter called "Department". The 2 Date parameters work but the Department one doesn't. What do I need to do to get this Department parameter to work when there's multiple datasets? Do the datasets need to be connected together somehow like in the Where clause or??? I want to be able to filter on the Department in the report.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply