Avatar of TBSupport
TBSupport
 asked on

SSRS: Boolean Parameter

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
SSRSMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Vikas Garg

8/22/2022 - Mon
Gottler

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

ASKER
A SQL query.
SOLUTION
Gottler

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
TBSupport

ASKER
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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Gottler

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

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 Patel

Go to Tablix and select ROW visibility.

Write expression.

=IIF(ReportFiled.FiledName.Value > 1 , FALSE, TRUE)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Vikas Garg

Go to Tablix and select ROW visibility.

Write expression.

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