We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

XSL Query Sharepoint

Medium Priority
925 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
Comment
Watch Question

Andrei TeodorescuBusiness Owner
CERTIFIED EXPERT
Top Expert 2006

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

Author

Commented:
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


Andrei TeodorescuBusiness Owner
CERTIFIED EXPERT
Top Expert 2006

Commented:
:)

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')" />
Andrei TeodorescuBusiness Owner
CERTIFIED EXPERT
Top Expert 2006

Commented:
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

Author

Commented:
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.

Author

Commented:
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.

Author

Commented:
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')<--
Andrei TeodorescuBusiness Owner
CERTIFIED EXPERT
Top Expert 2006

Commented:
you can not apply sum to all that

Author

Commented:
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?
Andrei TeodorescuBusiness Owner
CERTIFIED EXPERT
Top Expert 2006

Commented:
xsl:if block for showing the - sign?

Author

Commented:
I am sorry I am not really sure what you asking me,

Author

Commented:
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?
Andrei TeodorescuBusiness Owner
CERTIFIED EXPERT
Top Expert 2006

Commented:
<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')" />
Andrei TeodorescuBusiness Owner
CERTIFIED EXPERT
Top Expert 2006

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

Author

Commented:
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

Author

Commented:
I am sorry , I mistype  I am getting as a result (-207.00) not -193.00 for the if statement
Andrei TeodorescuBusiness Owner
CERTIFIED EXPERT
Top Expert 2006

Commented:
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
Andrei TeodorescuBusiness Owner
CERTIFIED EXPERT
Top Expert 2006

Commented:
"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
Andrei TeodorescuBusiness Owner
CERTIFIED EXPERT
Top Expert 2006

Commented:
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

Author

Commented:
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)
Andrei TeodorescuBusiness Owner
CERTIFIED EXPERT
Top Expert 2006

Commented:
Can't you create a view? it would be much easier to solve the problem at data tier.

Author

Commented:
you mean create a view in SQL ? I never done that before. but if it's easier I don't mind
Andrei TeodorescuBusiness Owner
CERTIFIED EXPERT
Top Expert 2006

Commented:
make the proper select statement and just append CREATE VIEW AS before it
Andrei TeodorescuBusiness Owner
CERTIFIED EXPERT
Top Expert 2006

Commented:
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)

Author

Commented:
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'

Author

Commented:
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
Andrei TeodorescuBusiness Owner
CERTIFIED EXPERT
Top Expert 2006

Commented:
why it should not work? :)

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

Author

Commented:
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.
Andrei TeodorescuBusiness Owner
CERTIFIED EXPERT
Top Expert 2006

Commented:
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?
Andrei TeodorescuBusiness Owner
CERTIFIED EXPERT
Top Expert 2006

Commented:
"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 ...

Author

Commented:
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
Business Owner
CERTIFIED EXPERT
Top Expert 2006
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Andrei TeodorescuBusiness Owner
CERTIFIED EXPERT
Top Expert 2006

Commented:
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 :)

Author

Commented:
ok , I did it . and it created a view.

Author

Commented:
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.

Author

Commented:
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?
Andrei TeodorescuBusiness Owner
CERTIFIED EXPERT
Top Expert 2006

Commented:
pls, post the query output here

Author

Commented:
I am sorry , it does work. I can see the negative number in the table .
Andrei TeodorescuBusiness Owner
CERTIFIED EXPERT
Top Expert 2006

Commented:
so, far so good; how about the sum?

Author

Commented:
I am gonna try to do it now

Author

Commented:
IT DOES WORK!!!!!!

Author

Commented:
now a question about the view , if my table changes does the view changes right away ?
Andrei TeodorescuBusiness Owner
CERTIFIED EXPERT
Top Expert 2006

Commented:
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

Author

Commented:
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?
Andrei TeodorescuBusiness Owner
CERTIFIED EXPERT
Top Expert 2006

Commented:
yes

Author

Commented:
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?
Andrei TeodorescuBusiness Owner
CERTIFIED EXPERT
Top Expert 2006

Commented:
since 2005 :)

Author

Commented:
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?
Andrei TeodorescuBusiness Owner
CERTIFIED EXPERT
Top Expert 2006

Commented:
Bucharest, Romania

Author

Commented:
ok , well thank you again. I am gonna close this question.

Author

Commented:
Thank you very much for your help and for an accurate solution.

Author

Commented:
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.
Andrei TeodorescuBusiness Owner
CERTIFIED EXPERT
Top Expert 2006

Commented:
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'

Author

Commented:
Thank you so much

Author

Commented:
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

Author

Commented:
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
Andrei TeodorescuBusiness Owner
CERTIFIED EXPERT
Top Expert 2006

Commented:
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

Author

Commented:
Got it , thanks
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.