Wednesday, March 21, 2012
ReportSelectionFormula To bring Null Values
Can anyone help me how to View the CR with ReportSelectionFormula with Particular field having Null Values
I have written like this
rpt1.RecordSelectionFormula = "{Dpled.Match} IS NULL"
or
rpt1.RecordSelectionFormula = "{Dpled.Match}='' "
but it gives blank reportUse isnull() function
rpt1.RecordSelectionFormula = isnull({Dpled.Match})
it will report only those records which has match field as null|||I tried out this
rpt1.RecordSelectionFormula = " IsNull({Dpled.Match})"
Still did not work out to bring Report with Null Values|||try this
rpt1.RecordSelectionFormula = " if IsNull({Dpled.Match}) then true"
Tuesday, March 20, 2012
Reports run automatically w/ Allow Null parameters?
both should not be null at the same time... it is either one or the other...
if both are null then I return an error from the stored procedure.
So now I am making the report to use this stored procedure.
When I click the "Allow null value" checkbox for both report parameters, the
report run automatically without waiting for the user to click the "View
Report" button! This creates a situation where if the user navigates to the
report using the Report Manager web application, then it will always first
give an error and then the user can enter a value and re-execute the report.
Is there some way to ALLOW null but not ASSUME null? I would like the user
to see the textbox for typing the parameter values and also see the unchecked
boxes for nulls.
PS: Both report parameters currently have the "None" as their default value.I have the exact same problem. Anyone find a solution for this?
"Al" wrote:
> I wrote a stored procedure with two paramters, both of which are nullable but
> both should not be null at the same time... it is either one or the other...
> if both are null then I return an error from the stored procedure.
> So now I am making the report to use this stored procedure.
> When I click the "Allow null value" checkbox for both report parameters, the
> report run automatically without waiting for the user to click the "View
> Report" button! This creates a situation where if the user navigates to the
> report using the Report Manager web application, then it will always first
> give an error and then the user can enter a value and re-execute the report.
> Is there some way to ALLOW null but not ASSUME null? I would like the user
> to see the textbox for typing the parameter values and also see the unchecked
> boxes for nulls.
> PS: Both report parameters currently have the "None" as their default value.
>
Monday, February 20, 2012
Reporting Services: Allow Blank Value for a multi-valued Parameter
I have a multi-value parameter "Keyword" that also allows blank value. When I try to view my report with a default null Keyword value, it doesn't display any. Please help.
Thanks!
Gerri
I assume you want to return everything if the parameter is null, right? does you query support null parameters? For instance, if my query says:
select * from table where field IN(@.parameter)
not sure of the technical way to explain this, but a null parameter would return no data because null is not a value in "field". In fact, I doubt that a null parameter would even result in a dataset where the value of "field" is null (never thought about it). I would guess, (disclaimer - I could be wrong here), that a null parameter is an object rather than a value, therefore it would not be the same as "is null" in sql server. I would guess again that it is more like IsNothing in RS, and therefore the database has no idea what to do with it. Maybe someone could verify that.
Anyway, you could try something like this:
select * from table where (IN(@.parameter) or (@.parameter is null))
that way, if your parameter is null, the where clause basically only TESTS if the parameter is null (which it is) and does nothing else.
|||Thank you.
I would also like to ask. I have 7 parameters for my report. 3 of which are coming from UI. and 4 are visible in report viewer. When I set a value from the parameter in RS and click 'View Report', the value is set to null, thus, disregarding the values of these 4 and considering only those 3 coming from UI. Please help.
Thanks!
Gerri