Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 873
  • Last Modified:

XSL Query Sharepoint

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
taverny
Asked:
taverny
  • 32
  • 26
1 Solution
 
Andrei TeodorescuBusiness OwnerCommented:
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
 
tavernyAuthor 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


0
 
Andrei TeodorescuBusiness OwnerCommented:
:)

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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
Andrei TeodorescuBusiness OwnerCommented:
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
 
tavernyAuthor 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.
0
 
tavernyAuthor 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.
0
 
tavernyAuthor 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')<--
0
 
Andrei TeodorescuBusiness OwnerCommented:
you can not apply sum to all that
0
 
tavernyAuthor 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?
0
 
Andrei TeodorescuBusiness OwnerCommented:
xsl:if block for showing the - sign?
0
 
tavernyAuthor Commented:
I am sorry I am not really sure what you asking me,
0
 
tavernyAuthor 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?
0
 
Andrei TeodorescuBusiness OwnerCommented:
<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
 
Andrei TeodorescuBusiness OwnerCommented:
you may want to try <xsl:if test="$soptype='4'"> - not sure what data type you have declared
0
 
tavernyAuthor 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
0
 
tavernyAuthor Commented:
I am sorry , I mistype  I am getting as a result (-207.00) not -193.00 for the if statement
0
 
Andrei TeodorescuBusiness OwnerCommented:
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
 
Andrei TeodorescuBusiness OwnerCommented:
"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
 
Andrei TeodorescuBusiness OwnerCommented:
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
 
tavernyAuthor 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)
0
 
Andrei TeodorescuBusiness OwnerCommented:
Can't you create a view? it would be much easier to solve the problem at data tier.
0
 
tavernyAuthor Commented:
you mean create a view in SQL ? I never done that before. but if it's easier I don't mind
0
 
Andrei TeodorescuBusiness OwnerCommented:
make the proper select statement and just append CREATE VIEW AS before it
0
 
Andrei TeodorescuBusiness OwnerCommented:
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
 
tavernyAuthor 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'
0
 
tavernyAuthor 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
0
 
Andrei TeodorescuBusiness OwnerCommented:
why it should not work? :)

you can make you select this way:
select T1.NAME as T1_NAME, T2.NAME as T2_NAME ...
0
 
tavernyAuthor 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.
0
 
Andrei TeodorescuBusiness OwnerCommented:
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
 
Andrei TeodorescuBusiness OwnerCommented:
"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
 
tavernyAuthor 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
0
 
Andrei TeodorescuBusiness OwnerCommented:
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
 
Andrei TeodorescuBusiness OwnerCommented:
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
 
tavernyAuthor Commented:
ok , I did it . and it created a view.
0
 
tavernyAuthor 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.
0
 
tavernyAuthor 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?
0
 
Andrei TeodorescuBusiness OwnerCommented:
pls, post the query output here
0
 
tavernyAuthor Commented:
I am sorry , it does work. I can see the negative number in the table .
0
 
Andrei TeodorescuBusiness OwnerCommented:
so, far so good; how about the sum?
0
 
tavernyAuthor Commented:
I am gonna try to do it now
0
 
tavernyAuthor Commented:
IT DOES WORK!!!!!!
0
 
tavernyAuthor Commented:
now a question about the view , if my table changes does the view changes right away ?
0
 
Andrei TeodorescuBusiness OwnerCommented:
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
 
tavernyAuthor 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?
0
 
Andrei TeodorescuBusiness OwnerCommented:
yes
0
 
tavernyAuthor 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?
0
 
Andrei TeodorescuBusiness OwnerCommented:
since 2005 :)
0
 
tavernyAuthor 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?
0
 
Andrei TeodorescuBusiness OwnerCommented:
Bucharest, Romania
0
 
tavernyAuthor Commented:
ok , well thank you again. I am gonna close this question.
0
 
tavernyAuthor Commented:
Thank you very much for your help and for an accurate solution.
0
 
tavernyAuthor 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.
0
 
Andrei TeodorescuBusiness OwnerCommented:
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
 
tavernyAuthor Commented:
Thank you so much
0
 
tavernyAuthor 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
0
 
tavernyAuthor 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
0
 
Andrei TeodorescuBusiness OwnerCommented:
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
 
tavernyAuthor Commented:
Got it , thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 32
  • 26
Tackle projects and never again get stuck behind a technical roadblock.
Join Now