Sorry for the noobish questions, but I just loaded Reporting Services and am trying to learn about it.
1. Is there a way to manipulate the tool bar that appears with the report? The one that has the pages, percent view, find/next, format/export and refresh. I can't seem to find any properties to set for it. For example, when I created reports with the evaluation copy, a print button would be on the toolbar, but with the full version, the reports I create don't have the print button.
2. For report parameters, is there a way to have a text box that users can input into rather than using dropdowns?
3. Does using the refresh button on the report toolbar refresh the dataset used for the report? Or does the user have to exit the report and have it recreated?
4. How do I use a parameter in a report title? For example, if I wanted to have a report where the user selected a month for a report and the parameter name was MONTH, I'd want the title of the report to show up as MONTH Report.
Thanks for any help.
Addendum: Also, I've read through some of the threads in here and read references to a client report builder. Is there a separate client report builder or is it just the add-in to Visual Studio?
Hello jwilliams:I've worked with this RS thing for 4 months. I don't have much experience but I'll try to answer:
1.- No, as far as I know
2.- Yes, when you define the parameter in the Report>Report Parameters... menu you get a dialog window. There, for each parameter, you can specify if you want to get the values from a Query or as an user input. Just select the "Non-Queried" option in the "Available values" section. Be careful: the user might enter an incorrect value without notice it (and soy you may recieve complains like "hey, this report doesn't work")
3.- Yes, clicking the refresh button is enough (as far as I know)
4.- I'll assume that you want to show the parameter value in a textbox. So, you want to concatenate an arbitray text and the parameter value. You can do this by typing something like this in the textbox:
="The value selected is: " & Parameters!ParameterName.Value
or, in the case you refer:
=Parameters!MONTH.Value & " Report"
I hope this helps.
Regards|||Thanks so much for your reply. Any idea on the client report builder? Is there one, or is there only the add-in for Visual Studio? Also, is there a way to add a column to your report once it's made? I used the wizard to generate my reports and on one, I wanted to add a column, but couldn't find where to do it.
Once again, thanks for your help.|||Well, I've just worked with the report designer, not with the report builder. See, Microsoft has just released some courses for free, and I guess you can find some helpful information there (in the bottom of the page there's a linkt to a free online RS course)
https://www.microsoftelearning.com/sqlserver2005/
Regards|||Thanks. As far as your answer to my 2nd question:
2.- Yes, when you define the parameter in the Report>Report Parameters... menu you get a dialog window. There, for each parameter, you can specify if you want to get the values from a Query or as an user input. Just select the "Non-Queried" option in the "Available values" section. Be careful: the user might enter an incorrect value without notice it (and soy you may recieve complains like "hey, this report doesn't work")
When I open the Report>Report Parameters and select the Non-Queried option, it brings up a Label and Value table to allow you to set up a drop down box, but I don't see anywhere that allows you just to specify a text box with a prompt for the user to input into. Am I missing something?
Also, I noticed that in another thread where someone is trying to pass a wildcard in a parameter--I'm trying to do that also, but can't figure out how. (I want the user to select a month or all to run the report on). You provided this if statement:
IF @.param = -5
BEGIN
SELECT *
FROM tbl_employee
END
ELSE
BEGIN
SELECT *
FROM tbl_employee
WHERE id_boss = @.param
END
Where does this statement go? In the table data filters property? That's where I put all my parameters for the report.|||OK, about the 2nd answer: just leave the table empty. This forces the user to write a parameter value in a textbox (if you fill the table then a dropdown list will appear in the report)
About the other one: my scenario is the following.
I had a parameterized report. There's only one parameter (called @.id_acm) and the values are taken from a query (in the report>report parameters... menu I choose "From Query" instead of "non-queried" and so a drop down list is shown to the user) The source query for the parameter values looked like this:
SELECT id_acm AS param_value, nombre AS param_label
FROM tbl_acm
And the query used to get the information for the report (the one I wrote in the DataSet) looked like this:
SELECT *
FROM tbl_sales S
INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acm
WHERE acm.id_acm = @.id_acm
I also did NOT specified any default value for the parameter.
When my boss executed the report he noticed that it showed anything until he selected a value from the drop down list. My boss told me that he wanted to see all the information by default, and if he needed information about a specific person he would select the person from the drop down list. Here comes the tricky part.
I noticed that all the current (and future) values for the id_acm field in the tbl_acm table were greater than 0.
So I modified the source query for the parameter like this:
SELECT -5 AS param_value, 'Everyone' AS param_label
UNION
SELECT id_acm AS param_value, nombre AS param_label
FROM tbl_acm
And I specified a default value of -5 for the @.id_acm parameter.
Also, I modified the principal query like this:
IF @.id_acm = -5
BEGIN
SELECT *
FROM tbl_sales S
INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acb
END ELSE
BEGIN
SELECT *
FROM tbl_sales S
INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acb
WHERE acm.id_acm = @.id_acm
END
Let me explain this: when the user selects "Everyone" the principal query returns the information of all the persons (because there's no WHERE clause)
If the user selects a specific name, then the principal query returns only that person's information (because there's a WHERE clause filtering the data)
By default the paramter value is -5 and so the report shows everyone's information.
Of course, if you have 2 parameters you'll have to nest 2 IF statemens. In fact, if you have n parameters you'll have to nest n IF statemens and you'll get 2^n posibilities for the WHERE clauses (that's the big pain)
I don't know if this helps in your scenario, but I hope it does.
