problem in SSRS

Question

problem in SSRS

arup chakraborty

Hall of Fame

Points: 3640
More actions
June 16, 2009 at 11:38 pm

#384784

I have 4 parameters in my report. None of them mandatory. But at least one should be supplied. How ll make the check that at least one parameter has been supplied?
Also, all the parameters can contain multiple values. As well as, they can tale NULL values. How to implement this one?
Any help is cordially accepted as I am very new in ssrs.
Thanks in advance.

Viewing 12 posts - 1 through 11 (of 11 total)

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

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 1

Sorry, but I have forgotten to mention, i also dont want to show that "select All" option in the parameter list. Is it possible? If possible, then how?

WilliamBendall SSCrazy Points: 2591 More actions · Answer 2

Hi arup_kc, is your report running from a cube?

----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 3

Hi

the report is not running from a cube.

Anyway, I have got the result 4 "select all" and NUll parameter for multi value.

Only thing i need to know is checking the at least one parameters mandatory.

plz help.

WilliamBendall SSCrazy Points: 2591 More actions · Answer 4

You could possibly do one of the following:

1. set one of the parameters to have a default value(thereby eliminating the fact that a user might forget to select one)

2. execute a stored procedure that accepts the parameters and if all parameters are NULL then return a default data set(pretty much the same as point 1)

----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 5

Hi William,

Thanks 4 ur reply.

But I cant set any of the parameters as default value bcause none of the field is not mandatory.

And SP also cant return a default data set bkz that may seems erronous.

Cant I check it after clicking the view report button through some code?

WilliamBendall SSCrazy Points: 2591 More actions · Answer 6

I spoke to one of my colleagues about the problem here and he pretty much has the same idea.

Execute a stored procedure that checks if a parameter was selected. If no parameter selected then return a TOP 0 on the dataset and populate a text box saying "Please select a parameter".

I'm not a web developer, but I don't think that you'll have access to the properties of the "View Report" button. Perhaps someone with web development experience can assist you.

Sorry I can't be of more assistance.

Regards,

William

----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 7

Hi WolliamB,

Great help.

"Execute a stored procedure that checks if a parameter was selected. If no parameter selected then return a TOP 0 on the dataset and populate a text box saying "Please select a parameter"."

I need to go through this idea.

Only one thing to know, how to send and populate the text box saying "please select a parameter"?

WilliamBendall SSCrazy Points: 2591 More actions · Answer 8

For the textbox you could use an expression similar to this for the value property of the textbox: iif(IsNothing(first(fieldname1)) = 1, "Please select a parameter!", "")

I'm not 100% sure if this will work but play around with the idea to see what helps you best in your scenario.

----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 9

arup chakraborty

Hall of Fame

Points: 3640

June 17, 2009 at 3:27 am

#1010716

Thanks william.

I wil try the idea.

thanks again.

thetodaisies Ten Centuries Points: 1387 More actions · Answer 10

Hi

if you are using reportviewer control in ASP.net you should be able to capture the parameters selected in the databinding event of the reportviewer control.

Microsoft.Reporting.WebForms.ReportParameterInfoCollection col = ReportViewer1.ServerReport.GetParameters();

I have never tried this. but you may give it a try

thanx

Siva Gurusamy SSCrazy Points: 2711 More actions · Answer 11

If no params are entered then just quit the SP without returning any reords and in the NoRowsMessage/No Rows property of Table/Tablix enter the text, "Please select atleast one parameter".

This text will appear on the report when no records are returned.

The above holds good if you are sure the report will always retrun records with atleast one valid param value.

Remember the property is "No Rows"

Again, the text will appear even when valid params are entered and 0 records are returned.

so, decide if you want to go through this approach.

"Software changes. This is a rather obvious statement, but it is a fact that must be ever present in the minds of developers and architects. Although we tend to think of software development as chiefly an engineering exercise, the analogy breaks down very quickly. When was the last time someone asked the designers of the Empire State building to add ten new floors at the bottom, put a pool on the top, and have all of this done before Monday morning? " : Doug Purdy, Microsoft Corporation