Link to home
Start Free TrialLog in
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
Avatar of Gottler
Gottler

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

ASKER

A SQL query.
SOLUTION
Avatar of Gottler
Gottler

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
You cannot include Count() in the case. Include that in HAVING clause after the Group by clause
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(*) ?
Avatar of Alpesh Patel
Go to Tablix and select ROW visibility.

Write expression.

=IIF(ReportFiled.FiledName.Value > 1 , FALSE, TRUE)
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Go to Tablix and select ROW visibility.

Write expression.

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