Grand Total of an iif expression

I have a report grouped on the user. The report calculates various
utilization totals based on the username. Some users have a Utilization = 100% for only 12 or 17 hours and other have a Utilization = 100% for 40
hours. At the group level, I can calculate the utilization with the
following formula...
=iif ( Fields!zUserID.Value ="USER1 ",
(Sum( Fields!zBillingWorkQty.Value , "GroupUserName") + Sum(
Fields!zQtyBilled.Value , "GroupUserName")) / 12 ,
iif ( Fields!zUserID.Value ="USER2 ", (Sum(
Fields!zBillingWorkQty.Value , "GroupUserName") + Sum(
Fields!zQtyBilled.Value , "GroupUserName")) / 17 , (Sum(
Fields!zBillingWorkQty.Value , "GroupUserName") + Sum(
Fields!zQtyBilled.Value , "GroupUserName")) / 40 ))
However, my issue is how do I get a grand total of this utilization on the
report footer. I cannot sum () this formula in the report footer. I receive
an error becuase of the "GroupUserName" parameter.
Is there a way to use the sum () function in the Report Footer and just
reference the name of the textbox in the group footer and it's corresponding
value that has already been calculated for each group?I probably do not understand your question very well, but you might try to
simply do the sum without regard to the groupings ( simply take out the
groups) and do the sum for the entire data set...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Jack Bender" wrote:
> I have a report grouped on the user. The report calculates various
> utilization totals based on the username. Some users have a Utilization => 100% for only 12 or 17 hours and other have a Utilization = 100% for 40
> hours. At the group level, I can calculate the utilization with the
> following formula...
> =iif ( Fields!zUserID.Value ="USER1 ",
> (Sum( Fields!zBillingWorkQty.Value , "GroupUserName") + Sum(
> Fields!zQtyBilled.Value , "GroupUserName")) / 12 ,
> iif ( Fields!zUserID.Value ="USER2 ", (Sum(
> Fields!zBillingWorkQty.Value , "GroupUserName") + Sum(
> Fields!zQtyBilled.Value , "GroupUserName")) / 17 , (Sum(
> Fields!zBillingWorkQty.Value , "GroupUserName") + Sum(
> Fields!zQtyBilled.Value , "GroupUserName")) / 40 ))
> However, my issue is how do I get a grand total of this utilization on the
> report footer. I cannot sum () this formula in the report footer. I receive
> an error becuase of the "GroupUserName" parameter.
> Is there a way to use the sum () function in the Report Footer and just
> reference the name of the textbox in the group footer and it's corresponding
> value that has already been calculated for each group?
>|||The issue here is that I need a conditional total and percentage calculation
based on the users that billed time in the report. If User 1 and/or User 2
are in the report, then they have different criterias for utilization
calcualtons than everyone else. All other users are based on 40 hours a
week. I determine this already at the group footer, so it would be much
easier just to grand total the cell value of the group footer then to
re-engineer the formula for the report footer.
"Wayne Snyder" wrote:
> I probably do not understand your question very well, but you might try to
> simply do the sum without regard to the groupings ( simply take out the
> groups) and do the sum for the entire data set...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Jack Bender" wrote:
> > I have a report grouped on the user. The report calculates various
> > utilization totals based on the username. Some users have a Utilization => > 100% for only 12 or 17 hours and other have a Utilization = 100% for 40
> > hours. At the group level, I can calculate the utilization with the
> > following formula...
> >
> > =iif ( Fields!zUserID.Value ="USER1 ",
> > (Sum( Fields!zBillingWorkQty.Value , "GroupUserName") + Sum(
> > Fields!zQtyBilled.Value , "GroupUserName")) / 12 ,
> >
> > iif ( Fields!zUserID.Value ="USER2 ", (Sum(
> > Fields!zBillingWorkQty.Value , "GroupUserName") + Sum(
> > Fields!zQtyBilled.Value , "GroupUserName")) / 17 , (Sum(
> > Fields!zBillingWorkQty.Value , "GroupUserName") + Sum(
> > Fields!zQtyBilled.Value , "GroupUserName")) / 40 ))
> >
> > However, my issue is how do I get a grand total of this utilization on the
> > report footer. I cannot sum () this formula in the report footer. I receive
> > an error becuase of the "GroupUserName" parameter.
> >
> > Is there a way to use the sum () function in the Report Footer and just
> > reference the name of the textbox in the group footer and it's corresponding
> > value that has already been calculated for each group?
> >
> >

Grabbing CURRENT Parameter Data

So it looks like when you add an expression which grabs a parameter value
(Parameters!SRNumber.Value), it grabs the data from when the "View Report"
button was originally pressed. My goal is to allow someone to punch in new
data into a parameter and without having to re-run the report, be able to use
that for an action. Any ideas? Example of what I am attempting to do below.
= "mailto:" + Fields!SOURCE_EMAIL.Value + "?subject=SR #"
+Parameters!SRNumber.Value + " has been assigned to you"...
(SRNumber is a blank parameter which isn't actually used in any of my
queries, but rather just there for this purpose)On Dec 12, 5:25 pm, Peetaah <Peet...@.discussions.microsoft.com> wrote:
> So it looks like when you add an expression which grabs a parameter value
> (Parameters!SRNumber.Value), it grabs the data from when the "View Report"
> button was originally pressed. My goal is to allow someone to punch in new
> data into a parameter and without having to re-run the report, be able to use
> that for an action. Any ideas? Example of what I am attempting to do below.
> = "mailto:" + Fields!SOURCE_EMAIL.Value + "?subject=SR #"
> +Parameters!SRNumber.Value + " has been assigned to you"...
> (SRNumber is a blank parameter which isn't actually used in any of my
> queries, but rather just there for this purpose)
This is a long shot, but you could try using javascript. Normally, the
last value selected before the View Report button is selected is shown
in an expression based on a parameter. Sorry that I could not be of
further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks, that is what I was thinking too-- just hoping there was a secret call
I could make to get the current value :)
"EMartinez" wrote:
> On Dec 12, 5:25 pm, Peetaah <Peet...@.discussions.microsoft.com> wrote:
> > So it looks like when you add an expression which grabs a parameter value
> > (Parameters!SRNumber.Value), it grabs the data from when the "View Report"
> > button was originally pressed. My goal is to allow someone to punch in new
> > data into a parameter and without having to re-run the report, be able to use
> > that for an action. Any ideas? Example of what I am attempting to do below.
> >
> > = "mailto:" + Fields!SOURCE_EMAIL.Value + "?subject=SR #"
> > +Parameters!SRNumber.Value + " has been assigned to you"...
> > (SRNumber is a blank parameter which isn't actually used in any of my
> > queries, but rather just there for this purpose)
>
> This is a long shot, but you could try using javascript. Normally, the
> last value selected before the View Report button is selected is shown
> in an expression based on a parameter. Sorry that I could not be of
> further assistance.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Dec 12, 10:21 pm, Peetaah <Peet...@.discussions.microsoft.com>
wrote:
> Thanks, that is what I was thinking too-- just hoping there was a secret call
> I could make to get the current value :)
> "EMartinez" wrote:
> > On Dec 12, 5:25 pm, Peetaah <Peet...@.discussions.microsoft.com> wrote:
> > > So it looks like when you add an expression which grabs a parameter value
> > > (Parameters!SRNumber.Value), it grabs the data from when the "View Report"
> > > button was originally pressed. My goal is to allow someone to punch in new
> > > data into a parameter and without having to re-run the report, be able to use
> > > that for an action. Any ideas? Example of what I am attempting to do below.
> > > = "mailto:" + Fields!SOURCE_EMAIL.Value + "?subject=SR #"
> > > +Parameters!SRNumber.Value + " has been assigned to you"...
> > > (SRNumber is a blank parameter which isn't actually used in any of my
> > > queries, but rather just there for this purpose)
> > This is a long shot, but you could try using javascript. Normally, the
> > last value selected before the View Report button is selected is shown
> > in an expression based on a parameter. Sorry that I could not be of
> > further assistance.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Sorry that I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Global expression apply to all fields in report

Hi All,

I got a situation that need to write a expression for doing if the value is negtive then display () around this value, and this expression should apply to 30 fields in my report. so i just wonder is that any way that i can create this expression as global variable , then i can use this expression in each field, instead of i write IIF function in every field expression area.

Any helps are appreciated.

Cheers

Nick

Moved to SSRS forum....(from SSIS)|||

Hi Nick,

You can declare a public variable in the Custom code section of the SSRS reports. The syntax for this variable declaration should be VB. Once declared, it can be used in any part of the report by referring to the field as Code."Variable Name". eg. Code.IntCounter.

Regards,

Pradeep

|||

Yeah, i did this way, and it works.. Thanks mate

But i have a little question, it always hightlight the global variable name and indicate it is not declared object in expression area. even through it still works.

Cheers

Nick

Global expression apply to all fields in report

Hi All,

I got a situation that need to write a expression for doing if the value is negtive then display () around this value, and this expression should apply to 30 fields in my report. so i just wonder is that any way that i can create this expression as global variable , then i can use this expression in each field, instead of i write IIF function in every field expression area.

Any helps are appreciated.

Cheers

Nick

Moved to SSRS forum....(from SSIS)|||

Hi Nick,

You can declare a public variable in the Custom code section of the SSRS reports. The syntax for this variable declaration should be VB. Once declared, it can be used in any part of the report by referring to the field as Code."Variable Name". eg. Code.IntCounter.

Regards,

Pradeep

|||

Yeah, i did this way, and it works.. Thanks mate

But i have a little question, it always hightlight the global variable name and indicate it is not declared object in expression area. even through it still works.

Cheers

Nick