October 28, 2015 at 9:38 am
I need help in creating a second parameter and third parameter drop down with 'Comparison' Operators to it.
Here is what I have created till now.
1) The Data Source is an SQL file.
2) I have selected "Use a dataset embedded in my report" and placed a similar query in here. The query is below -
SELECT c.[columnname1],
[columnname2],
[columnname3],
[columnname4],
[columnname5],
[columnname6],
[columnname7],
columnname8,
ContractCnt,
columnname10,
AssignmentCnt,
columnname12,
columnname13,
stt.Value AS ManagerSalaryReportReviewed,
stte.Value AS EthinicityManagerReviewed,
js.[LastExecution] AS LastValidation
FROM [DISEID].[Master].[CurrentCompany] c LEFT JOIN
(SELECT [columnname1], [columnname5] AS ...., [columnname6] AS ....,
[columnname7] AS ... FROM
(SELECT [columnname1], m.[Id] AS Mid, l.Description AS ValidationMesageType
FROM [DISEID].[dbo].[Message] m INNER JOIN
DISEID.dbo.Lookup l ON l.Id = [MessageTypeId]
WHERE m.[Active] = 1 AND [Acknowledged] IS NULL AND Year =
(SELECT value FROM [DISEID].[dbo].[Setting]
WHERE Type = 'ApplicationSettings' AND NAME = 'CurrentCompanyYear')) p
PIVOT (COUNT(Mid) FOR ValidationMesageType IN ([columnname5], [columnname6],
[columnname7])) AS msgpvt) AS v1 ON v1.columnname1 = c.columnname1 LEFT JOIN
(SELECT [columnname1], count(1) AS columnname8
FROM [DISEID].[Company].[Contract]
WHERE [Year] = (SELECT value FROM [DISEID].[dbo].[Setting]
WHERE Type = 'ApplicationSettings' AND NAME = 'CurrentCompanyYear') - 1
GROUP BY [columnname1]) pcv ON pcv.columnname1 = c.columnname1 LEFT JOIN
(SELECT COUNT(1) AS ContractCnt, [columnname1]
FROM Company.contract c
WHERE [Year] = (SELECT value FROM [DISEID].[dbo].[Setting]
WHERE Type = 'ApplicationSettings' AND NAME = 'CurrentCompanyYear')
GROUP BY [columnname1]) cv ON cv.columnname1 = c.columnname1 LEFT JOIN
(SELECT l.HireCompanyKey AS columnname1, count(1) AS columnname10
FROM [DISEID].[Staff].Assignment l
WHERE [Year] = (SELECT value FROM [DISEID].[dbo].[Setting]
WHERE Type = 'ApplicationSettings' AND NAME = 'CurrentCompanyYear') - 1
GROUP BY HireCompanyKey) pav ON pav.columnname1 = c.columnname1 LEFT JOIN
(SELECT COUNT(1) AS AssignmentCnt, [HireCompanyKey]
FROM Company.Assignment a
WHERE [Year] = (SELECT value FROM [DISEID].[dbo].[Setting]
WHERE Type = 'ApplicationSettings' AND NAME = 'CurrentCompanyYear')
GROUP BY [HireCompanyKey]) ca ON ca.HireCompanyKey = c.columnname1 LEFT JOIN
(SELECT COUNT([Id]) AS SnapCount, MAX(CreatedDate) lastCerDate,
[columnname1]
FROM [HistSnap].[dbo].[Snapshot]
WHERE CompanyYear = (SELECT value FROM [DISEID].[dbo].[Setting]
WHERE Type = 'ApplicationSettings' AND NAME = 'CurrentCompanyYear') AND Type
= 'CertifiedManager'
GROUP BY columnname1) SnapV ON SnapV.columnname1 = c.columnname LEFT JOIN
Setting stt ON stt.columnname1 = c.columnname1 AND stt.type = 'DCert' AND
stt.description = 'ManagerSalRepReview' LEFT JOIN
Setting stte ON stte.columnname1 = c.columnname1 AND stte.type = 'DCert' AND
stte.description = 'ManagerEthReview' LEFT JOIN
[DISEID].[dbo].[JobScheduler] js ON js.[columnname1] = c.columnname1 AND js.
[Name] = 'ManagerValidation'
WHERE CompanyType <> '07'
AND CompanyType <> '05'
AND CompanyType <> '09'
AND companyType <> '61'
AND CompanyType <> '49M'
AND c.columnname1 <> 1448
OR (CompanyType = '05' AND c.columnname1 IN (008224, 008223))
ORDER BY 3
3) I created 'DataSet1' and created a simple table.
4) I created 'DataSet2' to create a parameter with ColumnName3.
5) With 'ColumnName3' I have created a parameter with drop down.
6) @ReportParam is used for ColumnName3. The 'Report Parameter Properties' uses DataSet2, ColumnName3 for Value and Label Fields.
Now I want to create another 2 drop down parameters which should use the greater than logic to retrieve the actual result of the drop down from the source and the result in the table based on the logic applied. ???
Drop down Parameter Example: ContractCnt > 0 and AssignmentCnt > 0 instead of what it is displaying in the attached image.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply