none
Totals by Coloumn RRS feed

  • Question

  • Hopefully some help can prevail.

     

    I am trying to get data in a table to sum based on a value in the header row.

     

    For example, I have a table grouped by an entity (e.g. Admin then Warehousing) and would like to head up a coloumn based on different staff costs (e.g. ST01 then ST02) and have a value for each staff cost. I have tried using a formula along the lines of =iif(left(field!staff_code.value,4) = "ST01",sum(field!actual.value),0)

     

    This returns the sum for the entity, but returns a total for all items in the databsase, not only for ST01. I assume I have to add something to the formula, the sum part to get this to work but cannot work out any syntax.

     

    The report works like a matrix report but matrix reports have some restrictions that I need to overcome.

     

    Any help will be appreciated.

     

    Batty

     

     

    Friday, October 26, 2007 1:21 PM

Answers

  • >>I don't really know what you mean by cast, that formula does not seem to be available in VS.

     

    Steve, "cast" is a concept, not a function.  In VB (within a report) you use a variety of functions to cast data to different data types.  For example, CDBL(). You have not answered my (implied) question:

     

    >>Sorry, I had no idea what the data types of your columns are. 

     

    I still don't know what the data types of your columns (such as fields!actual) are.  I do realize that the values look like integers in your report output, but that does not mean that the data types of the columns are integers underneath. I was suspecting that the values are doubles.  Sometimes in this situation it is enough to supply the literal value of 0 as a decimal value instead of an integer:

     

    Code Block

    =sum(iif(left(field!staff_code.value,4) = "ST01",
          field!actual.value,0.00))

     

     

     

    , and sometimes you need to do a cast.  I was suggesting you try 0.00.

     

    >>Just using the iif without the Sum only returns the first row, which is correct for ST01 but then calcs 0 for ST02.

     

    This is an accident of your current data set.  In reality it would often be incorrect for every classification.  You do want to use the sum function here and you should be able to do so.

     

    >>as I see it, it has issues with Sum on the iif because of string value in Short Code.

     

    I'm sorry, this is just not the cause of your issue.  You are drawing an incorrect inference from the error message you have received.  I have just done the following test, which should be sufficient to convince you that I am using a string condition inside the SUM():

     

    Code Block


    =sum(iif(Fields!QReportLabel.Value.ToString().ToLower().Contains("project"),
             Fields!ID.Value,0))

     

     

    ... the reason this works and yours does not is that my ID field is an integer type.

     

    >L<

     

     

     

     

    Tuesday, October 30, 2007 12:04 AM

All replies

  • have you tried moving the SUM() outside, like this:

     

    Code Block


    =sum(iif(left(field!staff_code.value,4) = "ST01",
          field!actual.value,0))

     

     

     

    ... I am not testing this right now but at first glance this seems like it would work better. 

     

    >L<

     

    Saturday, October 27, 2007 6:39 AM
  • Thanks L but not quite right

     

    The formula returns an error, "the expression uses an aggregate function on data of varying types"

     

    I tried leaving out the sum and it actually returned the value for ST01 that I was looking for, however when I put another coloumn in and changed the ST01 to ST04, it returns 0 and should return values. Even if I copy the table and replace the expression with ST04 and only one coloumn, it still does not return a value for ST04.

     

    Maybe I am missing something set up wise, I am actually lost on this one.

     

    Steve

    Monday, October 29, 2007 11:54 AM
  • Further to my last item,

     

    If I display the detail row, there are values for ST04, these show on the 4th row of the detail. As I get results only for the first line (by group), the non-sum formula only works on the first line. It looks like I need some way around the sum problem.

    Monday, October 29, 2007 12:21 PM
  •  

    Sorry, I had no idea what the data types of your columns are.  Is it a double or something?  Try 0.00 instead of 0, and sometimes you have to do a cast to get it to work.  If it doesn't I promise I will try to test this with a simple set of data <s>.

     

    >L<

    Monday, October 29, 2007 3:56 PM
  • Hi L

     

    I don't really know what you mean by cast, that formula does not seem to be available in VS. Here is a bit more info for you.

    My dataset result shows (in summary)

    Legal Entity (CENO) Short Code (ST01) Value 500

    Legal Entity (CENO) Short Code (ST02) Value 600 

     

    Legal Entity (CINO) Short Code (ST01) Value 200

    Legal Entity (CINO) Short Code (ST02) Value 400

     

    Where the Legal Entity, Short Code and Value are Coloumns and the rest are the values returned.

     

    When i look at the detail in my table, it reads like this.

     

    CENO           ST01          500

    CENO           ST02          600

    CINO            ST01          200

    CINO            ST02          400

     

    I have grouped by legal entity and would like the coloumns to show the total by group, the table should look something like this.

     

    Legal Entity    |    ST01    |  ST02

    --------------------------------------------------

    CENO            |    500      |  600

    CINO              |   200       |  400

     

     

    I hope I have explained it properly for you, as I see it, it has issues with Sum on the iif because of string value in Short Code. Just using the iif without the Sum only returns the first row, which is correct for ST01 but then calcs 0 for ST02.

     

    Thanks for the help.

     

    Steve

    Monday, October 29, 2007 6:36 PM
  • >>I don't really know what you mean by cast, that formula does not seem to be available in VS.

     

    Steve, "cast" is a concept, not a function.  In VB (within a report) you use a variety of functions to cast data to different data types.  For example, CDBL(). You have not answered my (implied) question:

     

    >>Sorry, I had no idea what the data types of your columns are. 

     

    I still don't know what the data types of your columns (such as fields!actual) are.  I do realize that the values look like integers in your report output, but that does not mean that the data types of the columns are integers underneath. I was suspecting that the values are doubles.  Sometimes in this situation it is enough to supply the literal value of 0 as a decimal value instead of an integer:

     

    Code Block

    =sum(iif(left(field!staff_code.value,4) = "ST01",
          field!actual.value,0.00))

     

     

     

    , and sometimes you need to do a cast.  I was suggesting you try 0.00.

     

    >>Just using the iif without the Sum only returns the first row, which is correct for ST01 but then calcs 0 for ST02.

     

    This is an accident of your current data set.  In reality it would often be incorrect for every classification.  You do want to use the sum function here and you should be able to do so.

     

    >>as I see it, it has issues with Sum on the iif because of string value in Short Code.

     

    I'm sorry, this is just not the cause of your issue.  You are drawing an incorrect inference from the error message you have received.  I have just done the following test, which should be sufficient to convince you that I am using a string condition inside the SUM():

     

    Code Block


    =sum(iif(Fields!QReportLabel.Value.ToString().ToLower().Contains("project"),
             Fields!ID.Value,0))

     

     

    ... the reason this works and yours does not is that my ID field is an integer type.

     

    >L<

     

     

     

     

    Tuesday, October 30, 2007 12:04 AM
  • L

     

    Thanks for the help again!

     

    I eventually got it to work, but used some seat of my pants sql stuff that you probably know a better way round but here goes.

     

    I changed the data using the following.

     

    cast (actual_quarter_4 as integer) as actual_quarter_4

     

    This returns the value as an integer i guess, (thanks to you I understand the cast better).

     

    I then change my expression in the table to read

     

    =sum(iif(trim(Fields!short_code.value) = "ST04", Fields!actual_quarter_4.Value,0))

     

    This then returns a group total for ST04 for my selected legal entity.

     

    So job done really, thanks for the great help and for the SQL lesson.

     

    Steve

     

     

    Tuesday, October 30, 2007 9:53 AM
  • hi Steve,

     

    Glad you have it working at the SQL level. There is nothing "better" about doing the conversion of the data type at the report level versus the SQL level -- in fact if you are not using the source procedure, view, or whatever to do anything but this report then I would actually prefer to do it in the SQL. 

     

    But sometimes people don't have access to the SQL level when they ask questions here -- or doing it in the SQL might de-stabilize some other usage of the same SQL code <s>.

     

    If you ever do have to do it at the report level, remember that you can do this with functions like CDBL() around the Fields value, or sometimes (again) by using 0.00 instead of 0 as the literal in your expression...

     

    Have fun,

     

    >L<

    Tuesday, October 30, 2007 1:16 PM