Logo 
Search:

Sharepoint Forum

Ask Question   UnAnswered
Home » Forum » Sharepoint       RSS Feeds

Convert excel formula to xsl for use in data form web part

  Asked By: Imran    Date: Mar 12    Category: Sharepoint    Views: 3622

I hate having to ask and prefer to figure things out on my own, but many times I
get stumped. This is one of those times.

I have looked online and have not found any sites that are very much help in
figurung this out. (I really need to learn more about xsl) basically I want to
set an xsl:variable to calculate how many different items show up in a column in
a list.

For instance, a column may contain the values, A, A, C, A, B, C, A, B, C. Even
thought there are nine entries, there are only 3 values; A, B and C. I need a
formula that would return the result of there being 3 values. I figured out how
to do this in excel, but converting the formula to xsl has managed to elude me.
The Excel formula is as follows; =SUM(IF(FREQUENCY(A1:A100,A1:A100)>0,1)) (This
is obviously only for cells in rows 1 through 100 in column A. I need it to
count the entire column, which will likely change over time as more items are
added to the list.) I'm really lost on this now though, and any help would be
greatly appreciated. I hope I explained this well enough.

We have a help desk website set up, and I wanted to use this variable to show an
average of cases closed per day. In order to do that, I need a variable as
described above, so that I can incorporate that into another formula, which I
already know how I'm going to get. Hopefully that helps to explain it a little
better.

Share: 

 

10 Answers Found

 
Answer #1    Answered By: Jaya Deoghar     Answered On: Mar 12

I don't have an exact answer, BUT, I've seen some XSLT recipes somewhere and
thought one of them was "counting distinct items." Try the W3..org site. Or
google "XSLT recipes".

 
Answer #2    Answered By: Candi Branch     Answered On: Mar 12

XSLT 2.0 supports the "distinct-values" operator, but SharePoint, or more
specifically SPD, does not. I have been searching and so far the only answer I
have seen is "counting is the same as sorting is the same as grouping." No, it
really isn't. (None of the sample solutions are really helpful, and most don't
work at all.) I would have thought that counting values  but excluding repeat
occurrences of the value would be failrly easy. Sadly I am mistaken. If anyone
has any ideas on how to simply count how many entries  are in a column, excluding
duplicates, I would love to know.

 
Answer #3    Answered By: Jan Chen     Answered On: Mar 12

No easy way at all, but still doable.

Look for Munchian group algorithm, best described by Jeni Tennison
www.jenitennison.com/xslt/grouping/index.html

In a nutshell:
You want to group and count an attribute @attribute which is part  of an ROW
element.

Step 1: Set up a key: <xsl:key name="Rows-by-attr" match="Row"
use="@attribute"/>

Step 2: Create a template with mode attr-group.

<xsl:template match="Row" mode="attr-group">
<xsl:value-of select="@attribute" />
(<xsl:value-of select="count(key(' Rows-by-attr, @attribute))" />)
</xsl:template>

Step 3: Wherever you are interested in the result  apply the template from step
2:

<xsl:apply-templates mode="attr-group" select="Row[generate-id(.) =
generate-id(key(Rows-by-attr ', @attribute)[1])]"> <xsl:sort
select="@attribute"/>
</xsl:apply-templates>

Step 4: Find out what did go wrong when you implemented 1-3, which is the
hardest part at all.

 
Answer #4    Answered By: Britni Shepard     Answered On: Mar 12

One of my xslt texts has a simpler technique. They have the example
of a set of city elements with a country attribute. To count the
number of distinct countries, they build a node-set of the cities
whose country does not match any preceding country, and count the
number of elements of that:

<xsl:value-of select="count(//city[not
(@country=preceding::city/@country)]"/>

Performance might be an issue with a large data  set, but should be
OK for most SharePoint list  situations.

 
Answer #5    Answered By: Girja Garg     Answered On: Mar 12

I tried the solution that you recommended, and the result  that I get is not
correct. I must have screwed something up somewhere in the code. All of which
is pasted here for reference. Once I can get this value, I can plug it into a
variable to use, but getting the initial value is proving the most difficult.

<WebPartPages:DataFormWebPart runat="server" __markuptype="vsattributemarkup"
ViewFlag="9" ViewContentTypeId="0x" DetailLink="/HelpDesk/Lists/Service
Requests/My Active Service Requests.aspx" Width="" Height="" FrameState="Normal"
IsIncluded="true" IsVisible="true" Dir="Default" AllowRemove="true"
AllowZoneChange="true" AllowMinimize="true" Description="Use the Service
Requests list  to track detailed information and resolutions for service
requests." MissingAssembly="Cannot import this Web Part." PartImageSmall=""
PartImageLarge="/_layouts/images/ittask.gif" PartOrder="2" DisplayName=""
ListName="{091F2CA2-D414-4DE2-B9B2-EB48448566B3}" Title="Service Requests (1)"
FrameType="Default" NoDefaultStyle="TRUE"
__WebPartId="{724A9A85-A56C-41BA-A4E8-A498124C0570}"
id="g_724a9a85_a56c_41ba_a4e8_a498124c0570" pagesize="100">
<DataSources>
<SharePoint:SPDataSource runat="server" IncludeHidden="true"
SelectCommand="<View><Query><Where> <Eq> \
<FieldRef Name="Status"/> <Value
Type="Text">Closed</Value> </Eq> </Wher\
e> <OrderBy><FieldRef Name="DueDate"
Ascending="TRUE"/></OrderBy><GroupBy
Collapse="TRUE"> <FieldRef
Name="DueDate"/> </GroupBy> </Query></Vie\
w>" id="datasource1" DataSourceMode="List"
UseInternalName="true"><InsertParameters><asp:Parameter
DefaultValue="{091F2CA2-D414-4DE2-B9B2-EB48448566B3}"
Name="ListID"></asp:Parameter>
</InsertParameters><UpdateParameters><asp:Parameter
DefaultValue="{091F2CA2-D414-4DE2-B9B2-EB48448566B3}"
Name="ListID"></asp:Parameter>
</UpdateParameters><DeleteParameters><asp:Parameter
DefaultValue="{091F2CA2-D414-4DE2-B9B2-EB48448566B3}"
Name="ListID"></asp:Parameter>
</DeleteParameters><SelectParameters><asp:Parameter Name="ListID"
DefaultValue="{091F2CA2-D414-4DE2-B9B2-EB48448566B3}"/><asp:QueryStringParameter
Name="RootFolder" querystringfield="RootFolder" type="String"/><asp:Parameter
Name="StartRowIndex" DefaultValue="0"/><asp:Parameter Name="nextpagedata"
DefaultValue="0"/><asp:Parameter Name="MaximumRows"
DefaultValue="100"/></SelectParameters>
</SharePoint:SPDataSource>
</DataSources>
<ParameterBindings>
<ParameterBinding Name="ListName" Location="None" DefaultValue="Service
Requests"/>
<ParameterBinding Name="Userid"
Location="Postback;Connection;CAMLVariable"/>
<ParameterBinding Name="UserID" Location="CAMLVariable"
DefaultValue="CurrentUserName"/>
<ParameterBinding Name="Today" Location="CAMLVariable"
DefaultValue="CurrentDate"/>
</ParameterBindings>
<XSL>
<xsl:stylesheet version="1.0" exclude-result-prefixes="rs z o s ddwrt dt msxsl"
xmlns:msxsl="urn:schemas-microsoft-com:xslt"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:SharePoint="Microsoft.SharePoint.WebControls"
xmlns:__designer="schemas.microsoft.com/.../designer" target="_blank" rel="nofollow">schemas.microsoft.com/.../designer"
xmlns:asp="http://schemas.microsoft.com/ASPNET/20" target="_blank" rel="nofollow">http://schemas.microsoft.com/ASPNET/20"
xmlns:ddwrt="schemas.microsoft.com/WebParts/v2/DataView/runtime"
xmlns:o="urn:schemas-microsoft-com:office"
xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema"
xmlns:ddwrt2="urn:frontpage:internal"><xsl:output method="html" indent="no"
/><xsl:decimal-format NaN="" />
<xsl:param name="FilterLink" select="ddwrt:FilterLink()" />
<xsl:param name="dvt_apos">'</xsl:param>
<xsl:param name="filterParam" ddwrt:NoCAMLVariable="1"></xsl:param>
<xsl:param name="Userid"></xsl:param>
<xsl:variable name="dvt_1_automode">0</xsl:variable>
<xsl:template match="/" xmlns:SharePoint="Microsoft.SharePoint.WebControls"
xmlns:__designer="schemas.microsoft.com/.../designer" target="_blank" rel="nofollow">schemas.microsoft.com/.../designer"
xmlns:asp="http://schemas.microsoft.com/ASPNET/20" target="_blank" rel="nofollow">http://schemas.microsoft.com/ASPNET/20">
<xsl:call-template name="dvt_1" />
</xsl:template>
<xsl:key name="Rows-by-Date" match="Row" use="@DueDate" />
<xsl:template name="dvt_1" match="Row" mode="attr-group">
<xsl:value-of select="@DueDate" />
<xsl:value-of select="count(key('Rows-by-Date', @DueDate))" />
<xsl:apply-templates mode="attr-group"
select="Row[generate-id(.)=generate-id(key('Rows-by-Date', @DueDate)[1])]">
<xsl:sort select="@DueDate"/>
</xsl:apply-templates>
</xsl:template>

</xsl:stylesheet>
</XSL>

<datafields>@Title,Service
Request;@Details,Details;@Customer,Customer;@Keywords,Keywords;@Priority,Priorit\
y;@Status,Status;@AssignedTo,Assigned To;@Comments,Comments;@DueDate,Resolution
Date;@Resolution_x0020_Time,Resolution Time;@StartDate,Start
Date;@WorkflowLink,Link;@OffsiteParticipant,External
Participant;@OffsiteParticipantReason,External Participant
Reason;@WorkflowOutcome,Outcome;@ContentType,Content
Type;@Resolution_x0020_Type,Resolution Type;@Resolution_x0020_Details,Resolution
Details;@HelpDesk,Help Desk Notification;@Reminder,Reminders;@Workflow,Close
Request;@ID,ID;@Modified,Modified;@Created,Created;@Author,Created
By;@Editor,Modified
By;@_UIVersionString,Version;@Attachments,Attachments;@File_x0020_Type,File
Type;@FileLeafRef,Name (for use in forms);@FileDirRef,Path;@FSObjType,Item
Type;@_HasCopyDestinations,Has Copy Destinations;@_CopySource,Copy
Source;@ContentTypeId,Content Type ID;@_ModerationStatus,Approval
Status;@_UIVersion,UI
Version;@Created_x0020_Date,Created;@FileRef,URL Path;
</datafields>
</WebPartPages:DataFormWebPart>

 
Answer #6    Answered By: Jarod Mathews     Answered On: Mar 12

Yeap, there are some issues with your XSLT.

But before we go any further here's a rule of thumb. Whenever you need help  with
XSLT, please provide a) an example of the source XML, b) the XSLT (done below)
and c) the desired output.

To accomplish a)
Save the following in a text file called XMLResults.xslt and link that to your
web part  using <XslLink>PathToXMLresults.xslt</XslLink>
This element should be used either before or after the <Xsl> element. You can
use the web  part properties miscellaneous section as well to configure this.

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes" />

<xsl:template match="/">
<xmp>
<xsl:copy-of select="*" />
</xmp>
</xsl:template>
</xsl:stylesheet>

When you open the page with the browser you should see something similar to the
following:

<dsQueryResponse>
<Rows>
<Row Title="hello world" Priority="(2) Normal" Status="Not Started" ...
more attributes />
</Rows>
</dsQueryResponse>

Note: One Row element is enough and please cut out attributes that are not
required for the example.

 
Answer #7    Answered By: Amanda Lewis     Answered On: Mar 12

I'm not sure what the source xsl  would be since it is pulling from a SharePoint
list. As far as the result. It's probably overly simple; basically what I am
trying to do is to show  an average of requests closed per day. I can get a
total number of requests closed fairly easily and set that as a variable. For
each request in the list, there is a field that is automatically populated with
the date that the request was closed. If I were to group everything by date
closed, I just need to count how many groups there are. The only output I need
from this is a number; how many dates are in the list. (I will add that as a
variable to use in the formula  to calculate  the average.) Once I can get that
number, I'm good. Hope that helps describe my issue in more detail.

 
Answer #8    Answered By: Cassie Snyder     Answered On: Mar 12

I tried implementing your formula, but again, I am missing something, as this
gives me a total count instead of a count of distinct values. (I think part  of
the issue is that I am not trying to count a subset of values, but a count of
unique values  for the entire list.

my formula  is as follows;

<xsl:value-of
select="count(//dsQueryResponse/Rows/Row/@DueDate[not(dsQueryResponse/Rows/Row/@\
DueDate=preceding::dsQueryResponse/Rows/Row/@DueDate)])"/>

 
Answer #9    Answered By: Parijat Pathak     Answered On: Mar 12

I tested on a list  with a choice field "colour" and the following
formula counted the number of unique colours selected:

<xsl:value-of select="count(//Row[not
(@Colour=preceding::Row/@Colour)])"/>

In your case, I suggest you try

<xsl:value-of select="count(//Row[not
(@DueDate=preceding::Row/@DueDate)])"/>


 
Answer #10    Answered By: Ella Sargent     Answered On: Mar 12

Another issue you might have: I don't know what data  is in "DueDate"
but if it has a time component you will need to ensure all the
records have the same time or the comparison won't work.

 
Didn't find what you were looking for? Find more on Convert excel formula to xsl for use in data form web part Or get search suggestion and latest updates.




Tagged: