Solved

XSL Query Sharepoint

Posted on 2009-07-07
58
853 Views
Last Modified: 2013-11-18
I am trying to modify a query in my sharepoint Designer, and I cannot figure out the porper syntax:
this is the original syntax:
<xsl:value-of select="format-number(sum(/dsQueryResponse/NewDataSet/Row/@XTNDPRCE), '$#,##0.00;-$#,##0.00')" />

The query is basically a sum of the field "XTNDPRCE". The field contains numbers of my invoices or return Items, unfotunately my negative amount still shows positive by design so I need to put a "-" sign in front of each return amount. In order to figure out which item is negative I need to do a calculation that implies another field: SOPTYPE.
This formula will change the sign accordingly. ((-(@SOPTYPE*2-7)*(@XTNDPRCE))

This what I tried to do:

<xsl:value-of select="format-number(sum(/dsQueryResponse/NewDataSet/Row/ ((-(@SOPTYPE*2-7)*(@XTNDPRCE))), '$#,##0.00;-$#,##0.00')" /> but that doesn't work

How can I include the formula in my sum syntax ?
Thanks in Advanced. let me know if you want me to describe better.
David
0
Comment
Question by:taverny
  • 32
  • 26
58 Comments
 
LVL 11

Expert Comment

by:andrei_teodorescu
ID: 24797025
try this:
<xsl:value-of select="format-number(-(sum(/dsQueryResponse/NewDataSet/Row/@XTNDPRCE)*2-7)*sum(/dsQueryResponse/NewDataSet/Row/@XTNDPRCE), '$#,##0.00;-$#,##0.00')" />
0
 

Author Comment

by:taverny
ID: 24797110
Andrei thanks for your response,
Itried it and I am getting a huge number

I don't know if it helps but here is the number for SOPTYPE : it can only be 3 or 4
so If it's a 3 it's a positive number if it's a 4 it's a negative number:
if my item is 100:
-(3*2-7)*(100) =    $100.00
-(4*2-7)*(100) =   -$100.00


0
 
LVL 11

Expert Comment

by:andrei_teodorescu
ID: 24797174
:)

sorry, but you haven't been carefully :)
I put @XTNDPRCE twice

<xsl:value-of select="format-number(-(sum(/dsQueryResponse/NewDataSet/Row/@SOPTYPE)*2-7)*sum(/dsQueryResponse/NewDataSet/Row/@XTNDPRCE), '$#,##0.00;-$#,##0.00')" />
0
 
LVL 11

Expert Comment

by:andrei_teodorescu
ID: 24797194
another way is to put the following block before your (the first one)
     <xsl:if test="/dsQueryResponse/NewDataSet/Row/@SOPTYPE=4">
      <span>-</span>
      </xsl:if>

not sure if you need to put dsQueryResponse/NewDataSet/Row/@SOPTYPE in a variable before making the comparison

0
 

Author Comment

by:taverny
ID: 24797197
I know I saw it after I post my message. It still doesn't work. I think the first sum should not be there. I am trying some stuff right now.
0
 

Author Comment

by:taverny
ID: 24797240
If I put a filter before , then I will get all my positive invoices total , but I actually need to subrstract my negative number as well in my sum. I think you are in the right track with the first syntax you sent me.
0
 

Author Comment

by:taverny
ID: 24797479
I took your syntax and modify it a little but it doens't work at all now , do you see anything wrong in it?


<xsl:value-of select="format-number(sum((-((/dsQueryResponse/NewDataSet/Row/@SOPTYPE)*2-7)*(/dsQueryResponse/NewDataSet/Row/@XTNDPRCE)), '$#,##0.00;-$#,##0.00')" />


This is the error I am getting:


Custom QuerySharePoint Designer cannot render the XSLT in this Data View. Try to undo your changes or re-insert the Data View.Failed setting processor stylesheet : 0x80004005 : Invalid number of arguments. format-number(-->sum((-((/dsQueryResponse/NewDataSet/Row/@SOPTYPE)*2-7)*(/dsQueryResponse/NewDataSet/Row/@XTNDPRCE)), '$#,##0.00;-$#,##0.00')<--
0
 
LVL 11

Expert Comment

by:andrei_teodorescu
ID: 24797504
you can not apply sum to all that
0
 

Author Comment

by:taverny
ID: 24797542
ok, well it's driving me crazy. basically the first argument where the SOPTYPE is only gonna be 1 or -1.
DO you have any suggestion?
0
 
LVL 11

Expert Comment

by:andrei_teodorescu
ID: 24797605
xsl:if block for showing the - sign?
0
 

Author Comment

by:taverny
ID: 24797621
I am sorry I am not really sure what you asking me,
0
 

Author Comment

by:taverny
ID: 24797696
oh I see what you mean sorry; you asking me to try with the IF code posted before.
Where should I put it? If I put it before the original , would it put a "-" in front of the sum?
0
 
LVL 11

Expert Comment

by:andrei_teodorescu
ID: 24797718
<xsl:variable name="soptype" select="/dsQueryResponse/NewDataSet/Row/@SOPTYPE" />
     <xsl:if test="$soptype=4">
      <span>-</span>
      </xsl:if>
<xsl:value-of select="format-number(sum(/dsQueryResponse/NewDataSet/Row/@XTNDPRCE), '$#,##0.00;-$#,##0.00')" />
0
 
LVL 11

Expert Comment

by:andrei_teodorescu
ID: 24797726
you may want to try <xsl:if test="$soptype='4'"> - not sure what data type you have declared
0
 

Author Comment

by:taverny
ID: 24797825
I tried the code with the if statement , it gives me a total with a - in front , so the code works but it doesn't give me the correct result.
let me show the table with the data in it and the result that I have, I am sure you understand it very good but may be with the data you will see right away where is my mistake:

SOPTYPE       XTNDPRCE
3                      90.00
3                     100.00
3                       10.00
4                         5.00
4                         2.00

The sum of that should be 193.00 but instead with the code posted before with the if statement  I get -193.00
0
 

Author Comment

by:taverny
ID: 24797852
I am sorry , I mistype  I am getting as a result (-207.00) not -193.00 for the if statement
0
 
LVL 11

Expert Comment

by:andrei_teodorescu
ID: 24797894
post the entire code of the page; since you're saying that you have a row with the total, this means that you got somewhere in your code a line with

<xsl:value-of select="format-number(/dsQueryResponse/NewDataSet/Row/@XTNDPRCE, '$#,##0.00;-$#,##0.00')" />
and a line with
<xsl:value-of select="format-number(sum(/dsQueryResponse/NewDataSet/Row/@XTNDPRCE), '$#,##0.00;-$#,##0.00')" />

or maybe I'm wrong...
anyhow, the xsl:if block will not help you for the total row
0
 
LVL 11

Expert Comment

by:andrei_teodorescu
ID: 24797912
"I am sorry , I mistype  I am getting as a result (-207.00) not -193.00 for the if statement"

in this case you have a design flow... the data must be negative before you select it, so you can aggregate it into a sum function
0
 
LVL 11

Expert Comment

by:andrei_teodorescu
ID: 24797951
and you got the - for the last row (the total row), because the last value of /dsQueryResponse/NewDataSet/Row/@XTNDPRCE is 4 according to the above data
0
 

Author Comment

by:taverny
ID: 24797971
I know that's my problem the table where I pull the data from is set this way. that why I have to do something with the SOPTYPE. I cannot change my table , it's coming from my ERP DATABASE (DYNAMICS)
0
 
LVL 11

Expert Comment

by:andrei_teodorescu
ID: 24797986
Can't you create a view? it would be much easier to solve the problem at data tier.
0
 

Author Comment

by:taverny
ID: 24798092
you mean create a view in SQL ? I never done that before. but if it's easier I don't mind
0
 
LVL 11

Expert Comment

by:andrei_teodorescu
ID: 24798123
make the proper select statement and just append CREATE VIEW AS before it
0
 
LVL 11

Expert Comment

by:andrei_teodorescu
ID: 24798137
then run the query to create the view (you can't runt it twice, because it will create the view and on second run it will throw an error - to alter the view, right click on the view and click edit)
0
 

Author Comment

by:taverny
ID: 24798399
ok , I tried to create the view , I guess it's not gonna work since I am joinging table that some of the column has the same column name:
CREATE VIEW DAVID AS
select * from PSH..SOP10200 T1 with (nolock)  INNER JOIN PSH..GL00105 T2 with (nolock)  on  T2.[ACTINDX] = T1.[SLSINDX]  INNER JOIN PSH..SOP10100 T3 with (nolock)  on  T3.[SOPNUMBE] = T1.[SOPNUMBE] WHERE T3.[VOIDSTTS] = '0'
0
 

Author Comment

by:taverny
ID: 24798464
I attched a snapshot of my sharepoint designer with some data on it , also I was able to change the data in the view but of course it doens't affect the sum.

Capture.JPG
0
 
LVL 11

Expert Comment

by:andrei_teodorescu
ID: 24798488
why it should not work? :)

you can make you select this way:
select T1.NAME as T1_NAME, T2.NAME as T2_NAME ...
0
 

Author Comment

by:taverny
ID: 24798507
it gives me an error:
Msg 4506, Level 16, State 1, Procedure DAVID, Line 2
Column names in each view or function must be unique. Column name 'DEX_ROW_ID' in view or function 'DAVID' is specified more than once.
0
 
LVL 11

Expert Comment

by:andrei_teodorescu
ID: 24798532
but now you have to drop the idea of making use of <xsl:value-of select="format-number(sum(/dsQueryResponse/NewDataSet/Row/ ((-(@SOPTYPE*2-7)*(@XTNDPRCE))), '$#,##0.00;-$#,##0.00')" />

we need to adjust this view:
select * from PSH..SOP10200 T1 with (nolock)  INNER JOIN PSH..GL00105 T2 with (nolock)  on  T2.[ACTINDX] = T1.[SLSINDX]  INNER JOIN PSH..SOP10100 T3 with (nolock)  on  T3.[SOPNUMBE] = T1.[SOPNUMBE] WHERE T3.[VOIDSTTS] = '0'

in which tabels are SOPTYPE and XTNDPRCE?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 11

Expert Comment

by:andrei_teodorescu
ID: 24798544
"it gives me an error:
Msg 4506, Level 16, State 1, Procedure DAVID, Line 2
Column names in each view or function must be unique. Column name 'DEX_ROW_ID' in view or function 'DAVID' is specified more than once."

because you have to enumerate all th e column tables instead *
and make use of select T1.NAME as T1_NAME, T2.NAME as T2_NAME ...
0
 

Author Comment

by:taverny
ID: 24798619
ok,
SOPTYPE AND XTNDPRICE are only on SOP10200, my SOP10200 has my invoices from all the departments, I joined the other tables to pull only invoices per account # ,
But anyway the 2 fields are in the SOP10200
0
 
LVL 11

Accepted Solution

by:
andrei_teodorescu earned 500 total points
ID: 24798698
ALTER VIEW DAVID AS
select T1.SOPTYPE, T1.SOPNUMBE, T1.ITEMNMBR, (-1 * (T1.SOPTYPE*2-7) * T1.XTNDPRCE) as XTNDPRCE from PSH..SOP10200 T1 with (nolock)  INNER JOIN PSH..GL00105 T2 with (nolock)  on  T2.[ACTINDX] = T1.[SLSINDX]  INNER JOIN PSH..SOP10100 T3 with (nolock)  on  T3.[SOPNUMBE] = T1.[SOPNUMBE] WHERE T3.[VOIDSTTS] = '0'
0
 
LVL 11

Expert Comment

by:andrei_teodorescu
ID: 24798736
include other columns if you want to the view
don't forget to remove those nasty calculations from xsl

man, I'm going to get a sleep, here is 00:30 (AM), I had enough for the last day :)
0
 

Author Comment

by:taverny
ID: 24798740
ok , I did it . and it created a view.
0
 

Author Comment

by:taverny
ID: 24798845
I appreciate so much your help , I will try to see what I can do , otherwise we can continue tomorrow, Thank you so much for your help.
0
 

Author Comment

by:taverny
ID: 24805506
Hi Andrei,
I created the view, the column that has the XTNDPRCE still shows positive numbers , do I have to change something to the view?
0
 
LVL 11

Expert Comment

by:andrei_teodorescu
ID: 24806658
pls, post the query output here
0
 

Author Comment

by:taverny
ID: 24806725
I am sorry , it does work. I can see the negative number in the table .
0
 
LVL 11

Expert Comment

by:andrei_teodorescu
ID: 24806728
so, far so good; how about the sum?
0
 

Author Comment

by:taverny
ID: 24806740
I am gonna try to do it now
0
 

Author Comment

by:taverny
ID: 24806846
IT DOES WORK!!!!!!
0
 

Author Comment

by:taverny
ID: 24806859
now a question about the view , if my table changes does the view changes right away ?
0
 
LVL 11

Expert Comment

by:andrei_teodorescu
ID: 24807025
if the structure of the table changes, for example if you rename the columns, you'll have to rename columns in your view as well
or if you want to select data from a different table you should adapt your view to meet these changes as well
0
 

Author Comment

by:taverny
ID: 24807490
ok, but the data will be pass right away. let's say I delete or update my original table, the view will reflect the changes right away?
0
 
LVL 11

Expert Comment

by:andrei_teodorescu
ID: 24807506
yes
0
 

Author Comment

by:taverny
ID: 24807594
ok thank you.
I think you are pretty good with Sharepoint and Sharepoint Designer. I am currently setup our intranet with sharepoint for the first time and I am learning as I customize it, and I have a bunch of question specially regarding data connection. are you very familiar with Sharepoint?
0
 
LVL 11

Expert Comment

by:andrei_teodorescu
ID: 24807636
since 2005 :)
0
 

Author Comment

by:taverny
ID: 24807662
wow, nice!! I really think we can do a lot with sharepoint and solve a lot of issues that we are having regarding collaboration. well I let you know when I have my next question posted. where are you located?
0
 
LVL 11

Expert Comment

by:andrei_teodorescu
ID: 24807688
Bucharest, Romania
0
 

Author Comment

by:taverny
ID: 24807704
ok , well thank you again. I am gonna close this question.
0
 

Author Closing Comment

by:taverny
ID: 31600725
Thank you very much for your help and for an accurate solution.
0
 

Author Comment

by:taverny
ID: 24817211
Hi Andrei,
I am sorry to bother you again. but if I want to pull a field from another table into the same view how to I do that?
I would like to pull the ACCOUNT_1 from the GL00105.
0
 
LVL 11

Expert Comment

by:andrei_teodorescu
ID: 24817330
hi there,

ALTER VIEW DAVID AS
select T2.ACCOUNT_1, T1.SOPTYPE, T1.SOPNUMBE, T1.ITEMNMBR, (-1 * (T1.SOPTYPE*2-7) * T1.XTNDPRCE) as XTNDPRCE from PSH..SOP10200 T1 with (nolock)  INNER JOIN PSH..GL00105 T2 with (nolock)  on  T2.[ACTINDX] = T1.[SLSINDX]  INNER JOIN PSH..SOP10100 T3 with (nolock)  on  T3.[SOPNUMBE] = T1.[SOPNUMBE] WHERE T3.[VOIDSTTS] = '0'
0
 

Author Comment

by:taverny
ID: 24817369
Thank you so much
0
 

Author Comment

by:taverny
ID: 24824959
Hi Andrei , I just posted a new question regarding a connection string. I don't know if you are interrested in answering it.
here is the question title: "I can't connect to my SQL DATABASE from Sharepoint Designer"
Thanks
0
 

Author Comment

by:taverny
ID: 26412724
Hi Andrei,
I know it's an old post. I am creating a new view and I was wondering what is the (NoLock) do and if I should use it?
Thanks
David
0
 
LVL 11

Expert Comment

by:andrei_teodorescu
ID: 26412944
Hi,

(nolock) mean reading "dirty" data - uncommited data; you can read an answer here:
http://stackoverflow.com/questions/210171/effect-of-nolock-hint-in-select-statements

0
 

Author Comment

by:taverny
ID: 26413018
Got it , thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I will show you how to create a ASP.NET Captcha control without using any HTTP HANDELRS or what so ever. you can easily plug it into your web pages. For Example a = 2 + 3 (where 2 and 3 are 2 random numbers) Session("Answer") = 5 then we…
Browsers only know CSS so your awesome SASS code needs to be translated into normal CSS. Here I'll try to explain what you should aim for in order to take full advantage of SASS.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now