Avatar of rcowen00
rcowen00Flag for United States of America

asked on 

VS 2005 Dataset question

User generated image
What populates the drop down in the Value field?  I have a parameter that is showing up but isn't reflected in the stored procedure (@Status).  Thank you.
--ReportIndividualsbyState 49, 0,0,0,'0','0'
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[ReportIndividualsbyState]( @CompanyProfileKey int, @LicenseStatusKey int = NULL,@StateKey int = NULL,@BranchProfileKey int = NULL,@TeamID varchar(50)='0' , @GroupID varchar(50)='0', @IndStatusKey int = 2 )
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	-- active individuals
	IF @IndStatusKey = 2
		BEGIN
	SELECT        c.CompanyName, (CASE WHEN @BranchProfileKey IS NULL THEN 'All Branches' ELSE b.BranchName END) AS BranchName, dbo.FormatFullName(lo.FirstName, 
	                         lo.LastName, '') AS FullName, lo.NMLSID, b.BranchNumber, s.StateAbbr, lio.IndividualLicenseName AS LicenseName, lo.GroupID AS Groups, lo.TeamID AS Team, 
	                         (CASE WHEN lol.LicenseStatusKey <> 1 THEN 'N/A' ELSE lol.License_ID END) AS License_ID, 
	                         (CASE WHEN lol.LicenseStatusKey <> 1 THEN 'N/A' ELSE CONVERT(varchar(25), lol.IssueDate, 101) END) AS IssueDate, 
	                         (CASE WHEN lol.LicenseStatusKey <> 1 THEN 'N/A' ELSE CONVERT(varchar(25), dbo.GetLicenseRenewalDate(lol.LicenseKey, 0), 101) END) AS ExpirationDate, 
	                         dbo.GetLicenseStatusText(lol.LicenseKey) AS LicenseStatus, lo.LastName, lol.LastRenewalDate
	FROM            LoanOfficerLicense AS lol INNER JOIN
	                         LoanOfficerProfile AS lo ON lo.LoanOfficerProfileKey = lol.LoanOfficerProfileKey INNER JOIN
	                         LicenseInformationLoanOfficer AS lio ON lio.LicenseInformationLoanOfficerKey = lol.LicenseInformationLoanOfficerKey INNER JOIN
	                         LicenseInformation AS li ON li.LicenseInformationKey = lio.LicenseInformationKey INNER JOIN
	                         State AS s ON s.StateKey = li.StateKey INNER JOIN
	                         BranchProfile AS b ON b.BranchProfileKey = lo.BranchProfileKey INNER JOIN
	                         CompanyProfile AS c ON c.CompanyProfileKey = lo.CompanyProfileKey LEFT OUTER JOIN
	                         Address AS a1 ON a1.AddressKey = lo.ResAddressKey
	WHERE        (li.StateKey = ISNULL(NULLIF (@StateKey, 0), li.StateKey)) AND (lo.CompanyProfileKey = @CompanyProfileKey) AND 
	                         (lo.BranchProfileKey = ISNULL(NULLIF (@BranchProfileKey, 0), lo.BranchProfileKey)) AND (lol.LicenseStatusKey = ISNULL(NULLIF (@LicenseStatusKey, 0), 
	                         lol.LicenseStatusKey)) AND (lo.TeamID = ISNULL(NULLIF (@TeamID, '0'), lo.TeamID)) AND (lo.GroupID = ISNULL(NULLIF (@GroupID, '0'), lo.GroupID)) AND 
	                         (b.DeletedFlag = 0) AND (lo.DeletedFlag = 0) AND (lol.DeletedFlag = 0) AND (lo.InactiveDate IS NULL)
	ORDER BY lo.LastName
END
ELSE
				-- inactive records.
				IF @IndStatusKey = 3
				BEGIN
				SELECT        c.CompanyName, (CASE WHEN @BranchProfileKey IS NULL THEN 'All Branches' ELSE b.BranchName END) AS BranchName, dbo.FormatFullName(lo.FirstName, 
				                         lo.LastName, '') AS FullName, lo.NMLSID, b.BranchNumber, s.StateAbbr, lio.IndividualLicenseName AS LicenseName, lo.GroupID AS Groups, lo.TeamID AS Team, 
				                         (CASE WHEN lol.LicenseStatusKey <> 1 THEN 'N/A' ELSE lol.License_ID END) AS License_ID, 
				                         (CASE WHEN lol.LicenseStatusKey <> 1 THEN 'N/A' ELSE CONVERT(varchar(25), lol.IssueDate, 101) END) AS IssueDate, 
				                         (CASE WHEN lol.LicenseStatusKey <> 1 THEN 'N/A' ELSE CONVERT(varchar(25), dbo.GetLicenseRenewalDate(lol.LicenseKey, 0), 101) END) AS ExpirationDate, 
				                         dbo.GetLicenseStatusText(lol.LicenseKey) AS LicenseStatus, lo.LastName, lol.LastRenewalDate, lo.InactiveDate
				FROM            LoanOfficerLicense AS lol INNER JOIN
				                         LoanOfficerProfile AS lo ON lo.LoanOfficerProfileKey = lol.LoanOfficerProfileKey INNER JOIN
				                         LicenseInformationLoanOfficer AS lio ON lio.LicenseInformationLoanOfficerKey = lol.LicenseInformationLoanOfficerKey INNER JOIN
				                         LicenseInformation AS li ON li.LicenseInformationKey = lio.LicenseInformationKey INNER JOIN
				                         State AS s ON s.StateKey = li.StateKey INNER JOIN
				                         BranchProfile AS b ON b.BranchProfileKey = lo.BranchProfileKey INNER JOIN
				                         CompanyProfile AS c ON c.CompanyProfileKey = lo.CompanyProfileKey LEFT OUTER JOIN
				                         Address AS a1 ON a1.AddressKey = lo.ResAddressKey
				WHERE        (li.StateKey = ISNULL(NULLIF (@StateKey, 0), li.StateKey)) AND (lo.CompanyProfileKey = @CompanyProfileKey) AND 
				                         (lo.BranchProfileKey = ISNULL(NULLIF (@BranchProfileKey, 0), lo.BranchProfileKey)) AND (lol.LicenseStatusKey = ISNULL(NULLIF (@LicenseStatusKey, 0), 
				                         lol.LicenseStatusKey)) AND (lo.TeamID = ISNULL(NULLIF (@TeamID, '0'), lo.TeamID)) AND (lo.GroupID = ISNULL(NULLIF (@GroupID, '0'), lo.GroupID)) AND 
				                         (b.DeletedFlag = 0) AND (lo.DeletedFlag = 0) AND (lol.DeletedFlag = 0) AND (lo.InactiveDate IS NOT NULL)
				ORDER BY lo.LastName
				END
			ELSE
			-- all records.
				IF @IndStatusKey = 1
				BEGIN
SELECT        c.CompanyName, (CASE WHEN @BranchProfileKey IS NULL THEN 'All Branches' ELSE b.BranchName END) AS BranchName, dbo.FormatFullName(lo.FirstName, 
	                         lo.LastName, '') AS FullName, lo.NMLSID, b.BranchNumber, s.StateAbbr, lio.IndividualLicenseName AS LicenseName, lo.GroupID AS Groups, lo.TeamID AS Team, 
	                         (CASE WHEN lol.LicenseStatusKey <> 1 THEN 'N/A' ELSE lol.License_ID END) AS License_ID, 
	                         (CASE WHEN lol.LicenseStatusKey <> 1 THEN 'N/A' ELSE CONVERT(varchar(25), lol.IssueDate, 101) END) AS IssueDate, 
	                         (CASE WHEN lol.LicenseStatusKey <> 1 THEN 'N/A' ELSE CONVERT(varchar(25), dbo.GetLicenseRenewalDate(lol.LicenseKey, 0), 101) END) AS ExpirationDate, 
	                         dbo.GetLicenseStatusText(lol.LicenseKey) AS LicenseStatus, lo.LastName,lol.LastRenewalDate
	FROM            LoanOfficerLicense AS lol INNER JOIN
	                         LoanOfficerProfile AS lo ON lo.LoanOfficerProfileKey = lol.LoanOfficerProfileKey INNER JOIN
	                         LicenseInformationLoanOfficer AS lio ON lio.LicenseInformationLoanOfficerKey = lol.LicenseInformationLoanOfficerKey INNER JOIN
	                         LicenseInformation AS li ON li.LicenseInformationKey = lio.LicenseInformationKey INNER JOIN
	                         State AS s ON s.StateKey = li.StateKey INNER JOIN
	                         BranchProfile AS b ON b.BranchProfileKey = lo.BranchProfileKey INNER JOIN
	                         CompanyProfile AS c ON c.CompanyProfileKey = lo.CompanyProfileKey LEFT OUTER JOIN
	                         Address AS a1 ON a1.AddressKey = lo.ResAddressKey
	WHERE        (li.StateKey = ISNULL(NULLIF (@StateKey, 0), li.StateKey)) AND (lo.CompanyProfileKey = @CompanyProfileKey) AND 
	                         (lo.BranchProfileKey = ISNULL(NULLIF (@BranchProfileKey, 0), lo.BranchProfileKey)) AND (lol.LicenseStatusKey = ISNULL(NULLIF (@LicenseStatusKey, 0), 
	                         lol.LicenseStatusKey)) AND (lo.TeamID = ISNULL(NULLIF (@TeamID, '0'), lo.TeamID)) AND (lo.GroupID = ISNULL(NULLIF (@GroupID, '0'), lo.GroupID)) AND 
	                         (b.DeletedFlag = 0) AND (lo.DeletedFlag = 0) AND (lol.DeletedFlag = 0) 
	ORDER BY lo.LastName
	

END
END

Open in new window

ASP.NETVisual Basic.NETSSRS

Avatar of undefined
Last Comment
Nico Bontenbal
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Do you mean indStatusKey?
Avatar of rcowen00
rcowen00
Flag of United States of America image

ASKER

No, I mean @Status.  When I go to the Preview tab the parameter Status is there but not included in the dataset Parameters.  It is reflecting in the drop down under the Parameter section of the dataset but I don't know where it is coming from

User generated image
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Are you sure you are not using any other stored procedures or queries in this report?
Avatar of rcowen00
rcowen00
Flag of United States of America image

ASKER

Under the Data tab the only stored procedure listed is the one I included.  
Looks like you may have two of the same parameters just different labels.
Avatar of rcowen00
rcowen00
Flag of United States of America image

ASKER

planocz, can you tell me where to find or look for the "extra" one.
ASKER CERTIFIED SOLUTION
Avatar of Howard Cantrell
Howard Cantrell
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of rcowen00
rcowen00
Flag of United States of America image

ASKER

That's the thing it's not there, see the screen shot from the original question.  It is not listed as a parameter.  
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASP.NET
ASP.NET

The successor to Active Server Pages, ASP.NET websites utilize the .NET framework to produce dynamic, data and content-driven web applications and services. ASP.NET code can be written using any .NET supported language. As of 2009, ASP.NET can also apply the Model-View-Controller (MVC) pattern to web applications

128K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo