SSRS:  Boolean Parameter

TBSupport
TBSupport used Ask the Experts™
on
Hello:

I have a simple SQL Reporting Services (SSRS) report with three columns.  The final column is a column of numbers.

I created a True/False "Boolean" report parameter.  I want to "tell" the report to display rows where numbers are greater than or equal to 1 if "True" is chosen.  If "False" is chosen, I want the report to display rows where numbers are less than 1.

I'm drawing a blank on how to configure this logic.  Can someone please point me in the right direction?

This "logic" is similar to Crystal Reports' "Select Expert" where you configure the report to display certain data based on certain criteria.

Thanks!

TBSupport
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
How are you fetching the data from database? Using a Stored Procedure?

Author

Commented:
A SQL query.
Commented:
Pass the Boolean parameter as String to the Query. Say @param_bool (True/False)

SELECT
   ROWS
FROM
   TABLE
WHERE
  (1 = CASE
          WHEN @param_bool = 'True'
             THEN 1
        ELSE 2
       END
   AND Numbers >= 1)
   OR
  (1 = CASE
          WHEN @param_bool = 'False'
           THEN 1
        ELSE 2
       END
   AND Numbers < 1)
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Author

Commented:
That looks like it would work.  But, I'm having a hard time nailing down the syntax.

Below is my query, and it shows where I have taken your code and have tried embedding it.

SSRS gives me an error saying that an aggregate cannot be in a WHERE clause unless it is in a subquery containing a HAVING clause or a select list.

Any thoughts?

TBSupport


SELECT     IV00102.ITEMNMBR, IV00102.BINNMBR, IV00102.LOCNCODE, COUNT(*) AS TimesPicked
FROM         (SELECT     SOPTYPE, SOPNUMBE, LNITMSEQ, ITEMNMBR, ITEMDESC, LOCNCODE, QUANTITY, QTYPRINV, ReqShipDate, FUFILDAT, CONTSERIALNBR,
                                              ISLINEINTRA, DEX_ROW_TS, DEX_ROW_ID
                       FROM          SOP30300
                       WHERE      (ITEMNMBR IN (@Item, ',')) AND (FUFILDAT BETWEEN @BEGDATE AND @ENDDATE) AND (SOPTYPE = '2') AND (LOCNCODE IN (@Location, ',')))
                      AS Picking INNER JOIN
                      IV00102 ON Picking.ITEMNMBR = IV00102.ITEMNMBR AND Picking.LOCNCODE = IV00102.LOCNCODE
WHERE     ((IV00102.BINNMBR <> '') AND
  (1 = CASE
          WHEN @Movement = 'True'
             THEN 1
        ELSE 2
       END
   AND Count(*) >= 1))
   OR ((IV00102.BINNMBR <> '') AND
  (1 = CASE
          WHEN @Movement = 'False'
           THEN 1
        ELSE 2
       END
   AND Count(*) < 1))
GROUP BY IV00102.ITEMNMBR, IV00102.BINNMBR, IV00102.LOCNCODE

Commented:
You cannot include Count() in the case. Include that in HAVING clause after the Group by clause

Commented:
And... if the Count(*) is less than 1 which means the query will return 0 rows. Can you put some light on that? What for you included Count(*) ?
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)

Commented:
Go to Tablix and select ROW visibility.

Write expression.

=IIF(ReportFiled.FiledName.Value > 1 , FALSE, TRUE)
BI Consultant
Most Valuable Expert 2011
Commented:
To get around the issue of the COUNT() in the WHERE clause you could use a CTE (common table expression), like this:

with TheData as (
	SELECT     IV00102.ITEMNMBR, IV00102.BINNMBR, IV00102.LOCNCODE, COUNT(*) AS TimesPicked
	FROM         (SELECT     SOPTYPE, SOPNUMBE, LNITMSEQ, ITEMNMBR, ITEMDESC, LOCNCODE, QUANTITY, QTYPRINV, ReqShipDate, FUFILDAT, CONTSERIALNBR, 
												  ISLINEINTRA, DEX_ROW_TS, DEX_ROW_ID
						   FROM          SOP30300
						   WHERE      (ITEMNMBR IN (@Item, ',')) AND (FUFILDAT BETWEEN @BEGDATE AND @ENDDATE) AND (SOPTYPE = '2') AND (LOCNCODE IN (@Location, ','))) 
						  AS Picking INNER JOIN
						  IV00102 ON Picking.ITEMNMBR = IV00102.ITEMNMBR AND Picking.LOCNCODE = IV00102.LOCNCODE
	WHERE     (IV00102.BINNMBR <> '')
	GROUP BY IV00102.ITEMNMBR, IV00102.BINNMBR, IV00102.LOCNCODE
)
select * from TheData
where
  (1 = CASE
          WHEN @Movement = 1 --'True'
             THEN 1
        ELSE 2
       END
	AND TimesPicked >= 1)
   OR (1 = CASE
          WHEN @Movement = 0 --'False'
           THEN 1
        ELSE 2
       END
   AND TimesPicked < 1)

Open in new window

I've also replaced the true/false strings with a proper boolean in T-SQL.  If you define your parameter as bit then 1 is true and 0 is false.
Vikas GargAssociate Principal Engineer
Top Expert 2014

Commented:
Go to Tablix and select ROW visibility.

Write expression.

=IIF(ReportFiled.FiledName.Value >= 1 AND Parameters.Parametername.value = "TRUE", FALSE, TRUE)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial