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?
> >
> >

got some great replication links?

can anybody give me some nice links with a detailed explaination of the various replication errors and solution

thanks in advance

Jacx

replicationanswers.com

ull find everything here

Google Like Full Text Search

Hello,

I have a Full-Text Catalog that is populated by various columns in a few different tables. I have been able to create a stored procedure that will search across all of the different full-text columns and return me the results.

My problem is, if the someone searches for
hello world
then to my understanding I want to use FREETEXTTABLE to return my results (I actually get 0 results if I use CONTAINSTABLE)

If someone searches for
"hello world"
then I want to use CONTAINSTABLE because FREETEXTTABLE returns too many results.

And now the biggest problem would be, if someone searches for
"hello world" program
I would somehow need to use CONTAINSTABLE for the phrase and FREETEXTTABLE for 'program'. The SQL to accomplish something like this would probably be very ugly (if possible at all)

Can anyone give me any suggestions on this matter? I'm trying to create a google like search on a database which contains text and files (as BLOBs).

Thanks in advanceI should clarify a bit more.

If someone searches for
"hello world"
then I want to use CONTAINSTABLE because FREETEXTTABLE returns too many results.

The problem isn't that FREETEXTTABLE returns too many results but rather the rankings that I get back aren't helpful. Say I have 2 completely different tables and I want to search across both of them. Table1 has rows that contain the phrase "hello world" while Table2 doesn't contain that specific phrase, but does contain the separate words 'hello' and 'world'. I run FREETEXTTABLE on both Table1 and Table2, Union the results, and then return the table to my web app (where other formatting occurs before the results are displayed to the user.) Unfortunately because of how the Ranks are calculated, the entries in Table2 have a higher Rank than the entries in Table1 (even though the exact phrase occurs in Table1)

I want the rows from Table1 to appear before Table2 in this case.

One solution that I have been playing with is to always use CONTAINSTABLE, use regular expressions to insert 'AND' where applicaple, and use the 'FORMSOF(INFLECTIONAL, @.searchStr)' option. Unfortunately this seems to break when someone searches for
hello world
because I would insert an 'AND' between the words and then try to find the inflectional forms of "hello AND world" which of course breaks.

Once again, and ideas/suggestions would be greatly appreciated|||I think I've found the solution

If someone wants to search for...
"hello world" program

Then the sql would look like this...
SELECT *
FROM CONTAINSTABLE(T_Table, *, 'FORMSOF(INFLECTIONAL, "hello world") AND FORMSOF(INFLECTIONAL, "program")')|||Thanks EvilMonkey - very helpful. =)|||

cool but can u give some details.