Parameters limiting my results

SSAS and SSRS
1

I have a SSRS report I created that is using about 6 different parameters to help the user filter their results. I have the parameters set to allow blanks as well in case they want to run it wide-open.

My problem is when the report is ran wide open, I'm not getting all the results I should be.

If I delete the parameters entirely from the report, I get the expected number of returns.

I don't understand why just having the parameters in the report, even though they're blank, is affecting the output.

Hope that makes sense.

2

I am not familiar with SSRS but I am curious about this.

When you do that do you have to code the WHERE clause accordingly in SSRS, or does it "take care of it for you behind the scenes"?

If you have to code it then I reckon folk here will need to see the code to find what is causing it to return the wrong results.

3

I agree, we need to see the code. What you are trying to do is very common but how it is implemented in SSRS is dependent on how you have your SQL written to filter rows.

4

attached is the query, thanks all

**SELECT **
**invend.fac AS [Location], **
**invend.fpartno AS [PDI Part], **
**inmast.fdescript AS [PDI Desc], **
**invend.fpartrev AS [PDI Rev], **
inmast.fgroup AS [GC],
**inmast.fprodcl AS [PC], **
apvend.fcompany AS [Vendor],
**--invend.fvendno AS [Vend #], **
**invend.fvpartno AS [Vend Part], **
**invend.fvptdes AS [Vend Part Desc], **
mfr.fcmfrname as [MFG],
mfrpart.fcmfrpart as [MFG Part],
mfrpart.fmcomments as [MFG Comments],
inmast.fstdmemo as [Item Master Memo] ,
**inmast.fcomment as [Item Master Comment] **

from invend

LEFT JOIN
inmast ON (invend.fpartno + invend.fpartrev + invend.fac) = (inmast.fpartno + inmast.frev + inmast.fac)

LEFT JOIN
mfrpart ON inmast.fpartno = mfrpart.fcpartno

LEFT JOIN
mfr ON mfrpart.fcmfrno = mfr.fcmfrno

LEFT JOIN
apvend ON invend.fvendno = apvend.fvendno

where
(inmast.fdescript LIKE '%' + @pdiDesc + '%')
and
(mfrpart.fcmfrpart LIKE '%' + @mfgPart + '%')
and
(apvend.fcompany LIKE '%' + @vendName + '%')
and
(mfr.fcmfrname LIKE '%' + @mfgName + '%')
and
(inmast.fstdmemo LIKE '%' + @imMemo + '%')
and
(inmast.fcomment LIKE '%' + @imComment + '%')

5

That, and the others like it, will exclude all rows where inmast.fdescrip IS NULL

(@pdiDesc IS NULL OR inmast.fdescript LIKE '%' + @pdiDesc + '%')
AND (...

will take care of that. (If your blank parameter is "blank" rather than NULL you would need

(@pdiDesc = '' OR ...)
AND (...
6

Here is an alternative you would want to consider that includes what Kristen posted:

All Parameter Reports

Granted, if you need the wildcard matching, it complicates things. If you can get away from that and load values for parameters with specific values, it makes it easier and you get away from having to use LIKE.

7

You may have a reason for concatenating the columns together for comparison, but if not

LEFT JOIN inmast
     ON invend.fpartno = inmast.fpartno
    AND invend.fpartrev = inmast.frev
    AND invend.fac = inmast.fac

will be MUCH more efficient if there is a suitable index available

8

this worked, thanks so much!!