Drill into SQL Report UF from SQL Report UF

Answered

Rob

• April 18, 2016 03:05

Is it possible to Drill into a Universal Function SQL Report from another  SQL Report?

We have a rolling 12 month Sales History with Months listed in columns. I would like to "Drill Into"each month to check on another SQL report i have written. It all works fine with hard coded data, but i want it to be dynamic.

For Example, I click on the Golden Arrow listed in "Jan"

This SQL Query is run.

select T0.ItemCode, T3.CardCode, T3.CardName, Sum(T0.Quantity) as 'Qty'--, T3.DocNum
from INV1 T0
inner join OITM T1 on T0.itemcode = T1.itemcode
inner join OCRD T2 on T1.cardcode = T2.cardcode
inner join OINV T3 on T0.docentry = T3.docentry
WHERE
t3.canceled = 'n'
AND t0.itemcode = $[$BOY_1.ItemCode.0]
AND CONVERT(varchar,datepart(MONTH,t0.shipdate)) = 1
GROUP BY
T0.ItemCode, T3.CardCode, T3.CardName

 

But i get this error message.

I took the $[$BOY_1.ItemCode.0] from this information

Is there another trick to this? Or is it not possible?

Thanks!

Rob

Vote

0

• Official comment

  

  Naama Aharoni

  • April 19, 2016 10:04

  Hi Rob,

  I have tried to exact query in a similar scenario to yours but I do not get the error. If you can copy your sql configuration and format wizard to the post, I will be able to look further.

  Thanks, Naama
• 

  Rob

  • April 19, 2016 12:41

   

  Hi Naama,

  This is the main Sales History config (i assume its what you're after?)

  

  The format wizard config is here

  

  And the Universal Function for the Monthly History

   

  
  
  
  
• 

  Yaremi Miranda Lopez

  • April 21, 2016 15:24

  Hello Rob,

  You need to define an specific row when you click in the Golden Arrow. Please try this 2 scenarios:

  - Change your Dynamic Syntax to something like this:
  $[$BOY_1.ItemCode.0.SELECTED]
  Before you click in the golden Arrow select the line and the report will popup.

  -Change the syntax to $[$BOY_1.ItemCode.0.FOCUSED] and try to open the report.

   
• 

  Rob

  • April 22, 2016 00:39

  Hi Yaremi,

  Still the same issue. I thought the "0" was the row number?

  I have tried both scenarios listed above but still get the same error.

   
• 

  Rob

  • May 02, 2016 09:23

  Can anyone assist with the answer to this?
• 

  Yaremi Miranda Lopez

  • May 02, 2016 20:54

  Hello Rob,

  The 0 corresponds to the type in this case alphanumeric. If the problem persist, I suggest you contact your local partner for more assistance.
• 

  Rob

  • May 03, 2016 00:05

  I have contacted my partner who suggested i post on here as they were unsure of the error.

  Is there anything else i can try? I have been playing around with this for a few weeks, everyone i ask said what i have "should work", but doesn't.

  Is there a Boyum IT helpdesk i can contact?
• 

  Rob

  • May 06, 2016 01:14

  Turned out to be a very simple issue. Boyum IT got back to me (thank you for the help!)

  If anyone is curious, it was to do with the case of the text. It should have all been lower case, so "itemcode" not "ItemCode"

Please sign in to leave a comment.