COnditional visibility of a field in SSRS 2005

Hi,

I have a report in which there is a field called "Returned Qty" and there is a parameter called Show Qty now I want this field returned Qty to only appear if this show Qty parameter is set to yes. Can Anyone suggest me how to do this?

I guess It could be done by using the properties option and writing some expression. I appreciate the response,

Thanks,

Rashi

Hello Rashi,

Try putting this in the Hidden property of your textbox:

=IIf(Parameters!ShowQty.Value = "Yes", "False", "True")

Or, you can just replace the value directly in the textbox, using this expression:

=IIf(Parameters!ShowQty.Value = "Yes", Fields!ReturnedQty.Value, "")

Hope this helps.

Jarret

|||

Hello Jarret,

Thanks for the solution, I am sure this should work but somehow it gives me error both ways. I wrote the first expression under the text properties of the box by checking the expression option but it dint work. I wrote the same expression under the hidden properties too but it too dint work. The second expression display field but the filed contains "Error".

I am sure the expresison is correct but I am not using it under correct text box may be.

My aim is not to display the field at all if showQty is set to No.

Pls let me know if I am commiting any mistake while doing this

Thanks again for your help,

Rashi

|||

Hi Rashi,

Try false and true without the quotes ""

=IIf(Parameters!ShowQty.Value = "Yes", False, True)

Thanks,

Panna

|||

Yep, I tried this and it gives me an error " input string was not in correct format". No matter whatever expression do I write I am getting the same error message.

Any idea what it is?

|||

Sorry, yes, you will need to remove the " " from around the false and true. Right click and select Properties on your textbox that will hold the returned quantity. Select the Visibility tab, then select 'Expression:' from the 'Initial visibility:' section. Enter this as the expression here:

=IIf(Parameters!ShowQty.Value = "Yes", False, True)

The other way to do this is to replace the value with nothing in the textbox where it actually shows the value. Right click on the textbox that will hold the returned quantity and select 'Expression:'. Enter this and hit ok.

=IIf(Parameters!ShowQty.Value = "Yes", Fields!ReturnedQty.Value, "")

But, you said this shows Error when you did it, so can you try this instead?

=IIf(Parameters!ShowQty.Value = "Yes", Fields!ReturnedQty.Value, nothing)

Jarret

|||

When referencing parameters you can either look at the "Value" or the "Label".

=IIf(Parameters!ShowQty.Label = "Yes", False, True)

=IIf(Parameters!ShowQty.Value = False, False, True)

Either way should work!

GiveMeABreak. . .

|||I have been doing both ways n the error message is still the same no matter what I try it keeps giving: " input string not in correct format". If I remove all the expression it does build and shows me the returned qty firld but not with the expression values|||

Can you post the expression exactly as you have it in your textbox and in your hidden property?

Also, how do you have your parameter setup?

Jarret

|||

Thank you all, it has been fixed now.

I used the one below

IIf(Parameters!ShowQty.Value = False, False, True)

The mistake was in refrencing the parameters, otherwise its perfect!

Thanks again. :)

Rgds,

Rashi

sqlsql

Conditional visibility in a table group.

I have a report that presents the detail lines in two groupings. So I
have the detail lines as a subset of group1 which is a subset of group2.
Nothing clever there.
I want now to add a line to the report that is only visible if a
parameter "DisplaySignOff" (which will just show a box which users will
sign) has value = "Y".
If I add a detail group below the existing detail line and enter an
expression to make its visibility conditional on the parameter, the
original detail line becomes invisible too. I tried to change the name
of the added group, but it insists on having the same group name- I can
change it (type some other name), but the change is ignored.
If a add a third group that is a subset of group2 (and just select a
footer) and enter an expression to make its visibility conditional on
the parameter, the detail lines disappear again.
Any pointers gratefully received...Do you want to add the line for each record when DisplaySignOff = "Y"? If so,
right-click on your existing Detail row and choose "Insert Row Below". Now
select this row using its grey selector handle (this selects the entire row).
Now set the Hidden property under Visibility to an expression like:
=iif(ReportItems!DisplaySignOff = "Y", False, True)
Charles Kangai, MCDBA, MCT
"Nick Addison" wrote:
> I have a report that presents the detail lines in two groupings. So I
> have the detail lines as a subset of group1 which is a subset of group2.
> Nothing clever there.
> I want now to add a line to the report that is only visible if a
> parameter "DisplaySignOff" (which will just show a box which users will
> sign) has value = "Y".
> If I add a detail group below the existing detail line and enter an
> expression to make its visibility conditional on the parameter, the
> original detail line becomes invisible too. I tried to change the name
> of the added group, but it insists on having the same group name- I can
> change it (type some other name), but the change is ignored.
> If a add a third group that is a subset of group2 (and just select a
> footer) and enter an expression to make its visibility conditional on
> the parameter, the detail lines disappear again.
> Any pointers gratefully received...
>

Conditional visibility

I have a table with US dollar amounts. The user wants to see these
amounts by default, but would like the ability to view their original
currency amounts.
If the original currency amount = the US dollar amount, I don't want
the user to be able to make that row viewable. How can I prevent them
from even seeing the plus sign that would indicate additional data?
Thanks,
MikeWhile , as you know, you can make a row or column conditionally visible with
an expression on the visibility property. But the Toggle column can not be
turned off or on, just shown or hiddent...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bassist695" <Michael.EJ.Reynolds@.gmail.com> wrote in message
news:1116612976.587559.300370@.f14g2000cwb.googlegroups.com...
>I have a table with US dollar amounts. The user wants to see these
> amounts by default, but would like the ability to view their original
> currency amounts.
> If the original currency amount = the US dollar amount, I don't want
> the user to be able to make that row viewable. How can I prevent them
> from even seeing the plus sign that would indicate additional data?
> Thanks,
> Mike
>

Conditional visibility

I need to pass a parameter to determine the visibility of a remark. If the
parameter defines it to be visible, I also need to toggle the visibility of
the remark when the user expands or collapses the containing group. I do
not want the visibility turned on when the parameter wants it always
invisible.
If the Visibility.ToggleItem was based upon an expression, I believe I could
easily do this. Does anyone know how or when this could be done?Have you try the Iif function ?
= Iif(MyParameter.Value = "ViewNotes", True, False)
So set this formula as the visibility formula.
"Walter Lundgren" <lundgren_w@.crane.navy.mil> a écrit dans le message de
news:ekQ2AxhXEHA.1684@.tk2msftngp13.phx.gbl...
> I need to pass a parameter to determine the visibility of a remark. If
the
> parameter defines it to be visible, I also need to toggle the visibility
of
> the remark when the user expands or collapses the containing group. I do
> not want the visibility turned on when the parameter wants it always
> invisible.
> If the Visibility.ToggleItem was based upon an expression, I believe I
could
> easily do this. Does anyone know how or when this could be done?
>|||Thanks, but I also need to consider the need for a drilldown hide/unhide
feature. The ToggleItem, initial visibility, and InitialToggleState are
properties that support drilldown.
The ToggleItem property is hardwired to consider only the collapsed/expanded
state of the controls on the Dropdown list. If I could use an expression to
set ToggleItem, the reported "remarks" would not need to be tied to the
state of the collapsed/expanded control.
"Jéjé" <willgart@._A_hAotmail_A_.com> wrote in message
news:%23QDQI7hXEHA.2940@.TK2MSFTNGP09.phx.gbl...
> Have you try the Iif function ?
> = Iif(MyParameter.Value = "ViewNotes", True, False)
> So set this formula as the visibility formula.
>
> "Walter Lundgren" <lundgren_w@.crane.navy.mil> a écrit dans le message de
> news:ekQ2AxhXEHA.1684@.tk2msftngp13.phx.gbl...
> > I need to pass a parameter to determine the visibility of a remark. If
> the
> > parameter defines it to be visible, I also need to toggle the visibility
> of
> > the remark when the user expands or collapses the containing group. I
do
> > not want the visibility turned on when the parameter wants it always
> > invisible.
> >
> > If the Visibility.ToggleItem was based upon an expression, I believe I
> could
> > easily do this. Does anyone know how or when this could be done?
> >
> >
>

Conditional sum based on visibility

Hi,

I have a report that is conditionally showing a textbox based on the previous entry that is working correctly.

My issue is that the non visible entries are still being added to my Sum statement at the end of the report.

I need a way to exclude an entry based on its visibility.

Any help would be greatly appreciated.

Are you using the Previous aggregate to get the previous entry? If you are not, then you can add a conditional, using the same expression for determining the visibility, to the SUM aggregate. For example, =SUM(IIF(HiddenExpression, 0, Fields!FieldName.Value))|||

Thanks for the response.

I am using the Previous function to control the visibility. The report needs to show the first entry for each particular company code, but not the duplicate entries.

For example: =Previous(Fields!Company_code.Value) = Fields!Company_code.Value

I tried adding a conditional with the same expression to determine if it should be included in the Sum, but that does not work.

Any other suggestions?

Thanks!

|||

Try handling it in the code (Report -> Report Properties -> Code)

Declare a public shared variable (integer/float) in the code and write a public function to sum up the values based on current company code and previous company code. Your code will look something like this in VB.Net:

Public Shared SumTotal as Integer

SumTotal = 0

Public Function CalculateSum(isCompanyCodeSame as Boolean, FieldValue as Integer) As String

If isCompanyCodeSame = False Then

SumTotal = SumTotal + FieldValue

End If

CalculateSum = ""

End Function

and

append this expression to any of the textboxes in your detail row:

Fields!FieldName.Value & Code.CalculateSum(Fields!Company_code.Value=Previous(Fields!Company_code.Value), Fields!FieldToBeSummed.Value)

And use Code.SumTotal to get the sum.

Shyam

|||

Thanks for the code!

It is working correctly now.

|||

I used the same method to stop displaying rows in a table after the 10th row. It works fine in VS 2005 but it acts wierd when I publish it to the production server. I count up the rows that are visible...

Public Shared VisibleRowTotal as Integer=0

Public Function CountVisibleRow(isVisible as Boolean) As String
If isVisible = False Then
VisibleRowTotal = VisibleRowTotal + 1
End If
CountVisibleRow = ""
End Function

Then I added a column in my report table to call the code...

=Code.CountVisibleRow(ReportItems!textbox54.Value) & " " & Code.VisibleRowTotal

Then I based my row visibility on the code value.

=IIF(Code.VisibleRowTotal>=10, True,False)

To get it to work the first time I had to rename the original report on the production server and then upload the new report. Once several users start hitting the report then no rows are visible or it's intermittent.

I'll admit I've have never used custom code in a report before. Is there something different I need to do when uploading an rdl with custom code? Am I handling the custom code properly?

|||
You should change the VisibleRowTotal variable to not be Shared. Having it be shared or static will cause each instance of the report to share the same total value. So, removing the modifier will allow each report instance to execute independently from one another.

Ian|||

Thanks Ian.

I had the issue where my totals were correct for the first time the report loaded, but the totals just kept incrementing when I used different filters on the report.

Taking Shared off of the variable resolved the issue.

Conditional Image Visibility with Embedded code

I am trying to create a embedded function that will accept a dataset field
value and based on the value, make an image visibility hidden or visible in
the body of the report. I receive an error that the mathimage is not
declared. How do I reference an image in the body of the report. The field is
within a list and based on the subject an image is displayed in the bodiy of
the report.
Below is a sample of the embedded code.
Function VisibleYN(subject as string,passedyn as boolean) as double
if subject = "Math" and passedyn = true then
mathimage.visibility = visible
end if
if subject = "Science" and passedyn = true then
scienceimage.visibility = visible
end if
THe following expression is in the visibility property tab of the textbox
that holds the subject.
code.visibleyn(fields!subject.value as string, passedyn as boolean)
Any help would be appreciated!
--
MarieThe actual property is under the visibility and is named "IsHidden", so your
function should return a boolean rather than try and set a property.
Change
mathimage.visibility = visible
to
Return False
and likewise for your science one to get it to work.
"Marie" wrote:
> I am trying to create a embedded function that will accept a dataset field
> value and based on the value, make an image visibility hidden or visible in
> the body of the report. I receive an error that the mathimage is not
> declared. How do I reference an image in the body of the report. The field is
> within a list and based on the subject an image is displayed in the bodiy of
> the report.
> Below is a sample of the embedded code.
> Function VisibleYN(subject as string,passedyn as boolean) as double
> if subject = "Math" and passedyn = true then
> mathimage.visibility = visible
> end if
> if subject = "Science" and passedyn = true then
> scienceimage.visibility = visible
> end if
> THe following expression is in the visibility property tab of the textbox
> that holds the subject.
> code.visibleyn(fields!subject.value as string, passedyn as boolean)
> Any help would be appreciated!
> --
> Marie|||The reason I am trying to set the property is because the function is being
called from a textbox bound to a field. Based on the value in the field I am
setting the property of a checkmark image. If I return the boolean value how
do I use this value to set the property of another report item? I was hoping
I could do something like Reportitem.image1.ishidden = false.
Any suggestions?
Thanks for your help.
--
Marie
"David Swanson" wrote:
> The actual property is under the visibility and is named "IsHidden", so your
> function should return a boolean rather than try and set a property.
> Change
> mathimage.visibility = visible
> to
> Return False
> and likewise for your science one to get it to work.
> "Marie" wrote:
> > I am trying to create a embedded function that will accept a dataset field
> > value and based on the value, make an image visibility hidden or visible in
> > the body of the report. I receive an error that the mathimage is not
> > declared. How do I reference an image in the body of the report. The field is
> > within a list and based on the subject an image is displayed in the bodiy of
> > the report.
> >
> > Below is a sample of the embedded code.
> >
> > Function VisibleYN(subject as string,passedyn as boolean) as double
> > if subject = "Math" and passedyn = true then
> > mathimage.visibility = visible
> > end if
> > if subject = "Science" and passedyn = true then
> > scienceimage.visibility = visible
> >
> > end if
> >
> > THe following expression is in the visibility property tab of the textbox
> > that holds the subject.
> >
> > code.visibleyn(fields!subject.value as string, passedyn as boolean)
> >
> > Any help would be appreciated!
> >
> > --
> > Marie|||You can use an expression for the hidden property of the image in the report.
Right click on the image, expand Visibility, then use this in the Hidden
property
=TextBox1.Value="Math"
or
=iif(textBox1.Value="Math",true,false)
I was looking for something similar the other day, and I found the custom
code is a little quirky because I could not find a way to access control
members via code.
When I passed Me as an object, I got some interesting results. For example,
try
this:
function Test(me as object) as boolean
msgbox("String=" & Me.ToString)
return false
end sub
TextBox9.Value=Code.Test(me)
Check out the title of the msgbox itself! It looks like there is funcky
name mangling going on in the host, which prevents us from using objects
directly in custom code.
msgbox("Name=" & Me.Name) indicates a public member not available, so it
appears that unless there is a reflection trick, I have yet to find a way to
access member variables of a report control in custom code.
I'll keep an eye on this thread, and I hope this is of some help to you..
Dwayne

Conditional Display of report Header/footer data

Is visibility of the header/footer regions able to be turned on off
based on some expression?Yes. Select the header or footer whichever you'd like, and find the
Visiblity/Hidden section, choose "expression" from the drop down, or simply
type your expression in the space provided. This will be evaluated at
runtime and set the visibility to its proper state.
Michael
"Weston Weems" wrote:
> Is visibility of the header/footer regions able to be turned on off
> based on some expression?
>

Conditional Column Formatting

Hi Everyone,
I am trying to hide a column in a matrix table. I have no
trouble using conditional formatting to control the visibility of the
column, but when I hide the column the main row in the matrix does not
shrink. Therefore, I have a big gap... My thought was to use conditional
formatting to control the column width of the main matrix row. Listed
below is the expression that I used. Can anyone help me with the error
message or recommend a better solution?
=IIF(Fields!FiscalYear.Value = IIF(Month(TODAY()) = 10-12,Year(TODAY()) ,Year(Today())-1), 0.625in, 0.25in) cannot be
parsed as a unit because it does not contain numeric values. Examples
of valid unit strings are "1pt" and ".5in".
Regards,
A.AkinPut double quotes around the "0.625in" and "0.25in"
"awakin" wrote:
> Hi Everyone,
>
> I am trying to hide a column in a matrix table. I have no
> trouble using conditional formatting to control the visibility of the
> column, but when I hide the column the main row in the matrix does not
> shrink. Therefore, I have a big gap... My thought was to use conditional
> formatting to control the column width of the main matrix row. Listed
> below is the expression that I used. Can anyone help me with the error
> message or recommend a better solution?
>
>
>
> =IIF(Fields!FiscalYear.Value = IIF(Month(TODAY()) => 10-12,Year(TODAY()) ,Year(Today())-1), 0.625in, 0.25in) cannot be
> parsed as a unit because it does not contain numeric values. Examples
> of valid unit strings are "1pt" and ".5in".
>
> Regards,
>
> A.Akin
>|||That doesn't help. It still gives the same error message.
Is there another way of doing this?
"Michael C" wrote:
> Put double quotes around the "0.625in" and "0.25in"
>
> "awakin" wrote:
> > Hi Everyone,
> >
> >
> >
> > I am trying to hide a column in a matrix table. I have no
> > trouble using conditional formatting to control the visibility of the
> > column, but when I hide the column the main row in the matrix does not
> > shrink. Therefore, I have a big gap... My thought was to use conditional
> > formatting to control the column width of the main matrix row. Listed
> > below is the expression that I used. Can anyone help me with the error
> > message or recommend a better solution?
> >
> >
> >
> >
> >
> >
> >
> > =IIF(Fields!FiscalYear.Value = IIF(Month(TODAY()) => > 10-12,Year(TODAY()) ,Year(Today())-1), 0.625in, 0.25in) cannot be
> > parsed as a unit because it does not contain numeric values. Examples
> > of valid unit strings are "1pt" and ".5in".
> >
> >
> >
> > Regards,
> >
> >
> >
> > A.Akin
> >
> >|||Anthoney,
Did you ever figure this out? I need to control the width of a column but
it appears column width isn't formattable via an expression.
thanks,
Justin
"AnthonyG" wrote:
> That doesn't help. It still gives the same error message.
> Is there another way of doing this?
> "Michael C" wrote:
> >
> > Put double quotes around the "0.625in" and "0.25in"
> >
> >
> > "awakin" wrote:
> >
> > > Hi Everyone,
> > >
> > >
> > >
> > > I am trying to hide a column in a matrix table. I have no
> > > trouble using conditional formatting to control the visibility of the
> > > column, but when I hide the column the main row in the matrix does not
> > > shrink. Therefore, I have a big gap... My thought was to use conditional
> > > formatting to control the column width of the main matrix row. Listed
> > > below is the expression that I used. Can anyone help me with the error
> > > message or recommend a better solution?
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > =IIF(Fields!FiscalYear.Value = IIF(Month(TODAY()) => > > 10-12,Year(TODAY()) ,Year(Today())-1), 0.625in, 0.25in) cannot be
> > > parsed as a unit because it does not contain numeric values. Examples
> > > of valid unit strings are "1pt" and ".5in".
> > >
> > >
> > >
> > > Regards,
> > >
> > >
> > >
> > > A.Akin
> > >
> > >