May 13, 2008 at 7:40 pm
I have been tasked by our VP to create reports for the Server Information DB that I created... I am learning basic SQL as I go along, and now I have to have reports generated and published in 3 days...
So, I'm looking for a way to have users interact with the Report.
For instance..
I want to have a report that shows Server Name, Application, and Environment. I want the user to get a drop down box where they can choose the Environment (PROD, DEV, TEST, etc..) and the report only shows server in that environment. I have looked at the Report Paramaters and I have zero luck with that thing.:hehe:
Can someone send me in the right direction???
Heather aka sql-retard :D
May 13, 2008 at 9:15 pm
SQL-Retard (5/13/2008)
I have been tasked by our VP to create reports for the Server Information DB that I created... I am learning basic SQL as I go along, and now I have to have reports generated and published in 3 days...So, I'm looking for a way to have users interact with the Report.
For instance..
I want to have a report that shows Server Name, Application, and Environment. I want the user to get a drop down box where they can choose the Environment (PROD, DEV, TEST, etc..) and the report only shows server in that environment. I have looked at the Report Paramaters and I have zero luck with that thing.:hehe:
Can someone send me in the right direction???
Do you actually have the table structure to support this kind of query (meaning - can you query by TEST/DEV/PROD and get the results you want?) If so - then yes - report parameters (which you will have to incorporate into the query behind the report) is the way to go. Otherwise - you need to work on how you'd do this OUTSIDE of SSRS first.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 13, 2008 at 9:36 pm
Thankfully, I am sql literate enough that I created a good Database structure and I know enough to at least select * from Server_List where env = 'dev'
😀
So with that being said, how do i use these report parameters to have a drop down offer dev as an option and then when I click Dev it actually filters those results to only servers in the dev environment?
I messed with the parameters and got it to say things like Prod, Dev, etc..but it didn't filter the results based on my choice...which is why I am here begging for help 😉
Heather aka sql-retard :D
May 13, 2008 at 10:01 pm
1. get the report to something SQL static.
2. once you're through the initial wizard, click on report, parameters, add a new parameter, and populate the drop down area (label and value columns) with what you want. make the type of the parameter consistent with the data you need to filter.
3. Once the parameter is set up, assuming you named it parm1.
4. switch back to the data tab. Modify the query to include your parameter. Yours would look something like:
select * from Server_List where env =@parm1
5. Run the report.....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 13, 2008 at 10:15 pm
Well we're definately getting somewhere... When I run the statement on the Data tab it does only pull servers with Dev, since that's what i selected. Select server_name, application, env from ibss_srv where env=@param
But when i run the report, here's what I get
The report parameter ‘param1’ has a DefaultValue or a ValidValue that depends on the report parameter “param1”. Forward dependencies are not valid.
Heather aka sql-retard :D
May 13, 2008 at 10:32 pm
Ok. I've made a few changes AGAIN, and now the drop down is showing the Environments and I'm not getting any errors on the Report Preview, but I'm also not seeing any data except the column headers that I designed... see attached.
Heather aka sql-retard :D
May 13, 2008 at 10:58 pm
SQL-challenged (5/13/2008)
Ok. I've made a few changes AGAIN, and now the drop down is showing the Environments and I'm not getting any errors on the Report Preview, but I'm also not seeing any data except the column headers that I designed... see attached.
What you have for the values is not correct. You have 'param1 = DEV' etc... What is happening is that value is being passed to your query and ends up as:
SELECT * FROM table WHERE env = 'param1 = DEV'
Since you don't have an env called 'param1 = DEV' you are not getting any results. Just put the actual value in for each value and you should see results (as long as you have an env that matches). With the parameter as DEV - the query would end up being:
SELECT * FROM table WHERE env = 'DEV'
HTH,
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 14, 2008 at 7:25 am
yup - just like Jeff mentioned. Just make the values 'Dev','Prod', and 'test' (skipping the quotes when you type it in.)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 14, 2008 at 9:05 am
Well...BOTH OF YOU TOTALLY ROCK! IT IS WORKING AND I'M SO EXCITED! THANKS FOR YOUR HELP, IT IS MUCH APPRECIATED!:smooooth:
Heather aka sql-retard :D
May 15, 2008 at 11:41 am
OK, so now that I have the report parameters working where you can select one paramenter from the drop down, how do I get this to work with Multiple parameters in the Drop Down? I selected "Multiple" in the paramenter sheet, but when I run the report I get an error that says
Query Execution Failed for Data set 'ibss_srv'
Incorrect syntax near ','
I also tried to set the parameter to have an option for 'ALL' where all options in the Drop down would be selected. The ALL value is blank, and when i run the report no data shows. Any ideas?
Heather aka sql-retard :D
May 15, 2008 at 1:51 pm
1. mark the parameter as multi-valued, and DON't include an "all" in your drop down list. it will do that for you.
2. multi-valued parameters come through as comma-separated lists so your criteria should look like
WHERE
myColumnName in (@parmName)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply