locked
Filtering DVWP by Sum of Calculated Column RRS feed

  • Question

  • I have a DVWP for a MOSS 2007 list.  The DVWP is grouped by UserID, and has two calculated columns (AmtTot & MinTot). Each of these calculated columns sums two list columns (AmtTot=Amt1+Amt2, and MinTot=Min1+Min2).

     

    The two calculated columns AmtTot and MinTot are both totaled in a footer row.  What I now need to do is filter the list by the value of the footer row totals for these colunns, for each ID.  The filter criteria should be "where (AmtTot>=6000 OR MinTot>=150) AND Date>(Today-180 days)".

     

    I've tried to figure out how to filter using CAML and XSLT filter with no success...

     

    Help with a solution would be greatly appreciated!!

    Monday, November 21, 2011 5:07 PM

Answers

  • Hi ThomTX,

    I’m not clear about how you get the value for the footer row, I have a test in my local server, you can check it to get more information:

    1.       Add a row on the Data View Web Part,  for the cell to display AmtTot and MinTot columns totals, set a default value for it first, then right click the cell, choose edit Formula, type the formula like: sum(/dsQueryResponse/Rows/Row/@AmtTot)

    2.       In the Common Data View Tasks, choose Filter, add XSLT filtering, if I filter the AmtTot>=average of AmtTot, type the format like:
    [
    (sum(/dsQueryResponse/Rows/Row/@AmtTot)div count(/dsQueryResponse/Rows/Row/@AmtTot)<=@AmtTot)]

    if I filter the date field, please try to format the date field first, and try to filter, type the formula like:
    [(number(ddwrt:FormatDateTime(ddwrt:FormatDate(string(@date),1033,5),1033,'yyyyMMdd')>(number(ddwrt:FormatDateTime(ddwrt:FormatDate(string(ddwrt:TodayIso()),1033,5),1033,'yyyyMMdd')-180))]

    You may have a try to check whether it works.

    Thanks,
    Qiao

    Wednesday, November 23, 2011 11:04 AM
    Moderator