June 9, 2014 at 1:55 pm
Is it possible to avoid duplicate values? I build a small report and I also added a parameter but when I hit preview and select the assignee, the reports gives me duplicate values to select from. There are only 12 assignee but it is showing me a long list.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
June 9, 2014 at 2:09 pm
How do you get the parameter list? Is it a query where you could you simply add a DISTINCT?
June 9, 2014 at 2:12 pm
The query for the drop-down parameter is probably returning a name for each row in the view or table. How about something like this: Select Distinct [Name] From [YourTable].
June 9, 2014 at 2:14 pm
Luis Cazares (6/9/2014)
How do you get the parameter list? Is it a query where you could you simply add a DISTINCT?
This is what I did. (see attachment)
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
June 9, 2014 at 2:20 pm
New Born DBA (6/9/2014)
Luis Cazares (6/9/2014)
How do you get the parameter list? Is it a query where you could you simply add a DISTINCT?This is what I did. (see attachment)
So your parameter list comes from a query.
What's the query?
June 9, 2014 at 2:24 pm
Luis Cazares (6/9/2014)
How do you get the parameter list? Is it a query where you could you simply add a DISTINCT?
When I run a query without DISTINCT, it works fine but I get an error when I add DISTINCT
Get an ERROR:
SELECT DISTINCT Assignee,
Assigned_Group,
Incident_Number,
First_Name,
Last_Name,
Resolution,
Submit_Date,
Status,
Description,
Detailed_Decription,
SRID,
Submitter
FROM HPD_Help_Desk
Order by Submit_Date desc
WORKS FINE
SELECT Assignee,
Assigned_Group,
Incident_Number,
First_Name,
Last_Name,
Resolution,
Submit_Date,
Status,
Description,
Detailed_Decription,
SRID,
Submitter
FROM HPD_Help_Desk
Order by Submit_Date desc
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
June 9, 2014 at 3:04 pm
Why do you have so many columns?
It's normal that you get an error if you're using distinct and ordering by a column that's not returned in the select.
You should use a different query, something like this:
SELECT DISTINCT Assignee,
First_Name + ' ' + Last_Name AS Name
FROM HPD_Help_Desk
ORDER BY Name
I'm assuming that Assignee is the identifier and name is the name of the assignee. I might be wrong.
June 10, 2014 at 7:06 am
Luis Cazares (6/9/2014)
Why do you have so many columns?It's normal that you get an error if you're using distinct and ordering by a column that's not returned in the select.
You should use a different query, something like this:
SELECT DISTINCT Assignee,
First_Name + ' ' + Last_Name AS Name
FROM HPD_Help_Desk
ORDER BY Name
I'm assuming that Assignee is the identifier and name is the name of the assignee. I might be wrong.
Thanks for your help but I am not getting anywhere. I tried running your query but ended up getting an error.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
June 10, 2014 at 7:40 am
I am the noob here but when I create a Parameter for a query I find the table that just has say the assignees or users or location in it. I may be wrong, but are you using the same query for your results as your Parameter? Do you have a table in the db that just lists the assignees maybe a lookup table that has their name and maybe a Assignee ID? This would be the best way to do a query for a Parameter.
I do a ton of these parameters I could also walk you through my method of being able to do a multiple select list and passing that through to your Main queries WHERE clause using a split function.
***SQL born on date Spring 2013:-)
June 10, 2014 at 7:49 am
thomashohner (6/10/2014)
I am the noob here but when I create a Parameter for a query I find the table that just has say the assignees or users or location in it. I may be wrong, but are you using the same query for your results as your Parameter? Do you have a table in the db that just lists the assignees maybe a lookup table that has their name and maybe a Assignee ID? This would be the best way to do a query for a Parameter.I do a ton of these parameters I could also walk you through my method of being able to do a multiple select list and passing that through to your Main queries WHERE clause using a split function.
I just started working on SSRS about a month ago and this is the 2nd report I created (first 1 just simple report with no added parameter). I have no prior development experience. I do appreciate the fact that you are willing to help me.
To answer your question, yes I am using the same query as my parameter. I also don't have access to the DB yet. I am using the shared dataset which was provided to me.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
June 10, 2014 at 7:58 am
Dang that sux, this would be much easier and cleaner with access to the db. What value is Assignee is it the full name? Is that what you wish to filter on?
If so I'll make a slight adjustment to Luis's
SELECT Distinct Assignee AS AssigneeValue,
Assignee AS AssigneeLabel
FROM HPD_Help_Desk
ORDER BY AssigneeValue
IF you get the cannot connect error again you can put USE "whatever your db name is" before the select and see if that works
***SQL born on date Spring 2013:-)
June 10, 2014 at 8:26 am
New Born DBA (6/10/2014)
I also don't have access to the DB yet. I am using the shared dataset which was provided to me.
That can be a problem and you might not be able to change anything until you have access to the DB.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply