Using report parameters to return all data or just selection

Question

Using report parameters to return all data or just selection

frbec

SSC Veteran

Points: 299
More actions
March 7, 2008 at 3:22 am

#184857

I have a report using serveral parameters which work OK as long as values are entered. But what I want is to be able to return data by selecting from the parameters or ignoring paraemter and returning everything.
I have added a new parameter for project no. as I want the user to be able to enter a project no and return lines just for that project no. or to ignore parameter altogether and bring back everything. I would like this to work for dates as well. What happens is that no data is returned unless values are entered into parameters.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

maxeverest@googlemail.com Old Hand Points: 332 More actions · Answer 1

I have been working with this problem today. i wanted to let the user specify a particular record or return all of them. I was able to make this work by:

(MAIN_ID = @test-2 or isnull(@TEST,0) = 0)

This means that if a value is entered in the parameter @test-2 only records matching it will be returned. If it is left blank then the second part of the statement works and all results are returned.

I'm sure there are better or simpler ways of making this work but it should meet your requirements

thanks

frbec SSC Veteran Points: 299 More actions · Answer 2

What I want is for the user running the report to be able to ignore the parameters they don't want (without being prompted to input one) or to enter a project no without entering any other parameter values. The above reply works but still prompts me to enter the other values. I am using the multi-value option and therefore can't have null values. Code from main query below: each parameter has its own dataset which is just a SELECT DISTINCT

SELECT * FROM CRMData

WHERE (OITDate BETWEEN @StartDate AND @EndDate)

And (BidStatusName IN (@Status) OR 'ALL' IN (@Status) OR (@Status is null) )

And (Department IN (@Department) )

And (Salesman IN (@Salesman) )

And (SolutionCode1 IN (@Solcode) )

And (ForecastName IN (@Forecast) OR (@Forecast is null) )

And (ProjectNo = @Projectno or isnull (@Projectno,0) = 0)

ORDER BY OITDate

frbec SSC Veteran Points: 299 More actions · Answer 3

.... also if I don't enter any values into the data parameters (which can be null) no data is returned when what I actually want is all dates

jharrison-622657 SSC Veteran Points: 240 More actions · Answer 4

so in your where clause....try this

where (@FromDate is null or @FromDate <= datefield1)

and (@ToDate is null or @ToDate >= datefield1)

Louise-604184 SSC-Addicted Points: 489 More actions · Answer 5

I already have something very similar. I probably didn't make myself clear enough but what happens it that as soon as the preview button is pressed the report runs immediately for all possible data before I have a chance to press the view report button (although I can pause processing by pressing the red stop rendering button). Some parameters default to null where they are not multi value buttons but the rest are set to ALL). Minor thing I know, but someone will complain.