November 2, 2015 at 9:03 am
I have a problem in getting the Table result. This is what I tried -
I created two Parameters with allow multiple values with Y and N in it. I created "Available Values" and added Y = 1 and N = 0. My main query reads as follows:
Only the CASE statement and WHERE in the end of the query were the only changes from the actual query.
SELECT * FROM
(SELECT c.[columnname1],
ContractCnt,
CASE
WHEN ContractCnt > 0 THEN 1
ELSE 0
END AS ContractCountIndicator**,
AssignmentCnt,
CASE
WHEN AssignmentCnt > 0 THEN 1
ELSE 0
END AS AssignmentCountIndicator**,
FROM ....
------------------- AS ORG_Manager_Q
WHERE (ORG_Manager_Q.CompanyShortName IN (@ReportParam)) AND
((ORG_Manager_Q.ContractCountIndicator IN (@ContractsCountParam))AND
(ORG_Manager_Q.AssignmentCountIndicator IN (@AssignmentsCountParam)))
1) ContractsCountParam = Y, AssignmentsCountParam = N, Result = Table is displaying the result. Actual Result should be -
Table should not display the result since source file has Assignments Count data and should display if the filter selection is Y in AssignmentsCountParam.
2) ContractsCountParam = N, AssignmentsCountParam = Y, Result = Table is displaying the result. Actual Result should be - Table should not display the result since source file has Contracts Count data and should display if the filter selection is Y in ContractsCountParam.
Is there any mistake in my query??
November 2, 2015 at 9:33 am
Looking at your WHERE clause:
WHERE (ORG_Manager_Q.CompanyShortName IN (@ReportParam)) AND
((ORG_Manager_Q.ContractCountIndicator IN (@ContractsCountParam))AND
(ORG_Manager_Q.AssignmentCountIndicator IN (@AssignmentsCountParam)))
This will select rows where CompanyShortName, ContractCountIndicator and AssignmentCountIndicator equal to @ReportParam, @ContractsCountParam and @AssignmentsCountParam respectively.
Is that what you want?
Gerald Britton, Pluralsight courses
November 2, 2015 at 3:49 pm
Yes, it should select rows from CompanyShortName, ContractCountIndicator which has Y and N multi selections and AssignmentCountIndicator which has Y and N multi selections.
I have attached a screenshot of what it looks like in front end and also the sample data source.
Example:
1) Lets say, I select 'DDD' and FFF from 1st filter "Company Name".
- Row 5 should display in the table only when my selection is Y in ContractCnt and N in AssignmentCnt and Row 7 should not display in the table and vice-versa.
2) If BBB and FFF are selected.
- Row 3 should display in the table only when my selection is Y in ContractCnt and Y in AssignmentCnt and Row 7 should not display in the table.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply