0

SQL-formula on our Payroll-db VALUE error

Jet Reports Historic Posts

July 03, 2015 08:10

None

Follow

Hi all,

I am a bit lost and I really hope someone can help me out. I created a SQL-query which runs fine in the SQL Server Management Studio.
SELECT
sum(csa.dlt_fakt)
FROM
cmb cmb_k_srt_contr,csa,con,wnr,wg1
WHERE
(csa.bed_id=wnr.bed_id and csa.wns_id=wnr.wns_id)
AND (csa.bed_id=con.bed_id and csa.wns_id=con.wns_id and csa.dvb_id=con.dvb_id and csa.con_id=con.con_id)
AND (cmb_k_srt_contr.cmbnaam='k_srt_contr')
AND (csa.srt_arb_contr=cmb_k_srt_contr.cmbkode)
AND (wg1.bed_id=wnr.bed_id)
AND ((con.dat_uit is null OR con.dat_uit>='2015-07-01' AND con.dat_ind<='2015-07-01')
AND (csa.dat_ing=(SELECT max(csa_MaxRegel.dat_ing) FROM CSA csa_MaxRegel
WHERE csa_MaxRegel.BED_ID=csa.BED_ID
AND csa_MaxRegel.WNS_ID=csa.WNS_ID
AND csa_MaxRegel.DVB_ID=csa.DVB_ID
AND csa_MaxRegel.CON_ID=csa.CON_ID
AND (csa_MaxRegel.dat_ing<='2015-07-01')
))
AND wg1.bed_id IN (200)
)

I copied this query to a Jetreports formula and replaced the date and company-values with cell-references. This is my formula:
=NL(;"SQL=SELECT sum(csa.dlt_fakt) AS HeadCount FROM cmb cmb_k_srt_contr,csa,con,wnr,wg1"&"
WHERE (csa.bed_id=wnr.bed_id and csa.wns_id=wnr.wns_id)"&"
AND (csa.bed_id=con.bed_id and csa.wns_id=con.wns_id and csa.dvb_id=con.dvb_id and csa.con_id=con.con_id)"&"
AND (cmb_k_srt_contr.cmbnaam='k_srt_contr')"&"
AND (csa.srt_arb_contr=cmb_k_srt_contr.cmbkode)"&"
AND (wg1.bed_id=wnr.bed_id)"&"
AND ((con.dat_uit is null OR con.dat_uit>='"&$E$10&"' AND con.dat_ind<='"&$E$10&"')"&"
AND (csa.dat_ing=(SELECT max(csa_MaxRegel.dat_ing) FROM CSA csa_MaxRegel"&"
WHERE csa_MaxRegel.BED_ID=csa.BED_ID"&"
AND csa_MaxRegel.WNS_ID=csa.WNS_ID"&"
AND csa_MaxRegel.DVB_ID=csa.DVB_ID"&"
AND csa_MaxRegel.CON_ID=csa.CON_ID"&"
AND (csa_MaxRegel.dat_ing<='"&$E$10&"')"&"
))"&"
AND wg1.bed_id IN ("&$E$7&")"&")";"HeadCount";"DataSource=";$E$6)

The "&" at the end of the lines is because of the 255 character limitation in Exel formulas.

Does anyone know what I am doing wrong here?
Unfortunately this gives me a #VALUE error. I can't seem to figure this one out myself…. :-(.


Thanks in advance for your help!!!

Best regards,
Gerard

• Facebook
• Twitter
• LinkedIn

4 comments

• 
  Jet Reports Historic Posts July 15, 2015 08:36 Official comment

  Hi rmw,
  
  Sorry it took a while to reply.
  
  I decided to solve this in a different way. I created a vba script in Excel to retrieve the data I need from the SQL-Server.
  The same SQL-statement does work this way, so that's fine.
  
  Thanks for taking the time to answer my question!!!
  
  Best regards,
  Gerard

• 0
  
  Jet Reports Historic Posts July 03, 2015 11:18

  AND ((con.dat_uit is null OR con.dat_uit>='"&$E$10&"' AND con.dat_ind<='"&$E$10&"')"&"

  
  You better use the %Filter1% placeholder to get data from the sheet into the formula.
  
  NL(;"SELECT * FROM table WHERE %Filter1% AND %Filter2%";"1s=TextField";"FilterValue";"2d=DateField";"<="&$E$10)Because SQL needs certain datatypes in certain formatting.
  Doing it like this, Jet will handle the formatting for you.
  
  Keep in mind that this only works with the Universal Connector!
  See here.
  
  
  HTH
  
  rmw
• 0
  
  Jet Reports Historic Posts July 03, 2015 15:00

  Hi rmw,
  
  Thanks for your answer. I will rewrite my formulas to match your explanation.
  
  However, even if I paste the working SQL-query in the NL formula I will get the same error.
  I also created several reports using the syntax in my post and they all work just fine. So I think the value-error must be caused by something else.
  
  Could the following syntax be the problem?:
  FROM
  cmb cmb_k_srt_contr,csa,con,wnr,wg1
  WHERE
  
  
  Regards,
  Gerard

• 0
  
  Jet Reports Historic Posts July 08, 2015 07:21

  Could the following syntax be the problem?:
  FROM
  cmb cmb_k_srt_contr,csa,con,wnr,wg1
  WHERE

  
  Normally databases that you pull data from are comma separated.
  Is that space used for renaming the database for further use?
  I would suggest you use the AS clause to do that
  FROM cmb AS cmb_k_srt_contr,csa,con,wnr,wg1Does that help?
  
  rmw