adding Parameters to ssrs 2008

Hi  I need to add a Parameter to  a report  (Name_of_Employer) there are many instances where the name of the employer are repeated which is right.

When a user goes into this report i want to be able to  only have once instance of the (name_of_employer)  and want to be able to count how many of the instances the name appears.

Is this possible

see bellow data

application_id      application_date      applicant_id      Title      first_names      surname      primary_applicant      Home_Tel_number      Work_Tel_number      Cellphone_number      employment_status      name_of_employer
10155      2012/03/15      17930      Mr      Ferdie      Bester      1                        EMPLOYED      CLICKMAVEN
10155      2012/03/15      17934      Mr      Ferdie      Bester      1                        EMPLOYED      CLICKMAVEN
10163      2012/03/15      17947      Mr      Ferdie      Bester      1                        EMPLOYED      CLICKMAVEN
10215      2012/03/16      18041      Mr      Ferdie      Bester      1                        EMPLOYED      CLICKMAVEN
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Create a view that goes something like this

SELECT TOP 100 PERCENT a.Title + ' ' + a.first_names + ' ' + a.surname, a.the_count
   SELECT DISTINCT Title, first_names, surname, COUNT(surname) as the_count
   FROM YourTable
   GROUP BY Title, first_names, surname) a
ORDER BY a.surname, a.first_names

DO NOT include application_id, application_date, or applicant_id, as these are unique values within a single application.

Then in the .rdl create the parameter if it's not there already, double-click to open Properties, click Available Values, click the 'Get values from a query' radio button, and enter the view name.
DavesmAuthor Commented:
Hi Thanks this worked but i need to change this a bit

I now need  to us a parameter to do the following for example i need the user to be able to  select from the parameters if the same telephone number appears lets say 4 or more  times in the data it will return all the data related to  thoses telephone numbers

Hope this makes sence
DavesmAuthor Commented:
hi Jim

I am getting this error in visual studio 2008 when i try preview the repot
-----All the data
select *
from mis.dbo.mis_Apps_With_SameData
where application_Date between @Startdate and @EndDate
and name_of_employer in (@name_of_employer)
and home_tel_number in (@home_tel_number)
and work_tel_number in (@work_tel_number)
and Residential_Address in (@Residential_Address)

----Query for (name_of_Employer) Parameter
select name_of_employer
from mis.dbo.mis_Apps_With_SameData
where name_of_employer  is not null
Group by name_of_employer
order by  name_of_employer

----Query for (Home_Tel_number) Parameter
SELECT Home_Tel_number
FROM     mis_Apps_With_SameData
WHERE  (Home_Tel_number IS NOT NULL)
GROUP BY Home_Tel_number
ORDER BY Home_Tel_number

----Query for (Work_Tel_number) Parameter
SELECT Work_Tel_number
FROM     mis_Apps_With_SameData
WHERE  (Work_Tel_number IS NOT NULL)
GROUP BY Work_Tel_number
ORDER BY Work_Tel_number

----Query for (Residential_Address) Parameter
SELECT Residential_Address
FROM     mis_Apps_With_SameData
WHERE  (Residential_Address IS NOT NULL)
GROUP BY Residential_Address
ORDER BY Residential_Address

Open in new window

this is the error i get is
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

If i remove anyone of those parameters the report runs fine
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

The 5 pieces of code above are the pieces of code that return lists of values for the "Available Values" settings of the various parameters for the reports?

I've never been bitten by this error message but ... Try making 5 stored procedures and changing your way of getting data for the parameters from providing the SQL directly (the "Text" radio button in the dataset designer) to just the name of the sproc (and choose the "stored procedure" radio button). You may have hit some limitation in amount of code!


Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
DcpKing is correct in that SSIS - SSRS likes to form a 'contract' with a source of data in order to process, and a SP with multiple return sets does not make that happen for all subsequent sets.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Let me know if there was something unique to my comment that solved your problem, otherwise since it clarifies DcpKing's comment he should get some points out of the deal.
He never said he was using an SP for this - simply that this was to code, so I assumed that he was showing us the various pieces of code that he has in the various datasets, kept as textual queries.

My personal preference is to push all of these out into stored procedures, each of which has just one form of data return (through a select statement) so that, as you say, SSRS can be sure statically of what format of data it will receive at run time.

Doing this means that I can test the SPs independently of the report, change them and tweak them and debug them as and how I wish, and the SSRS presentation sees only the same form of data.

hth, Davesm

DavesmAuthor Commented:
Hi Guys

Thanks for your assistance but i have changed the script and have managed to get it working as i needed it to

Thanks for the
Davesm: So could you tell us how SSRS is getting the code (i.e. is the code you provided actually all in one piece, and, if so, where,  or is it in several places and you just showed it all in one piece?) , and what did you do to fix the problem?

That will stay here and, when others have the same problem, they'll search and find your answer. That's the larger value of EE - the accumulation of knowledge over time.

As I said earlier, I'd never seen that particular error message, so I, for one, am curious about what you did to fix it, because it's likely that eventually I'll see it too!

Thanks in advance

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.