@Dpvalue in custom sql

building reports webintelligence
1

Hi All,

I have a report that has two queries. Both queries has Incident ID’s. I dont want to display any Incident Id that is present in first query. So I used “Result from another query” and assigned Incident ID of first query. If I wont use custom sql query then the query runs without any issues but as soon as I change the sql to custom then it gives an error

A filter contains a wrong value. You cannot run this query. (Error: WIS 00007)

Can anyone please help on this issue.

Thanks

nivas258 (BOB member since 2008-04-14)

2

I suspect that this is because the @Dpvalue syntax is not actual SQL. Maybe you should look at a subquery to do this instead, or get the necessary universe changes made so you can just use this in the standard fashion.

Mak 1 :uk: (BOB member since 2005-01-06)

3

Hello Mark can u tell me how and what parameters can tweaked in universe.

Koppakapradeep (BOB member since 2014-03-23)

4

I wasn’t referring to any parameters, I was suggesting creating the necessary objects in the universe so you can avoid creating custom SQL.

Mak 1 :uk: (BOB member since 2005-01-06)

5

Hello,

Same problem for me

I pick up the “@dpvalue” line (for me @dpvalue(‘A’, DP1.DO64)) from the original generated SQL then paste it to my handmade SQL to make sure y have the correct synthax and reference

Same result in Webi Rich Client : A filter contains a wrong value. You cannot run this query. (Error: WIS 00007)

It seems like it doesn’t “translate” the @dpvalue when it check if SQL have a correct synthax …

Did someone know how to verify that my @dpvalue params (‘A’, DP1.DO64) point correctly to the right object of my over data source ?

Thanks

------------- EDIT -------------

Seems like I find a solution to that …

  1. In your main data source : define your subquery filter
  2. Let the main data source in mode “generated SQL”
  3. run the “sub data source” (query that gets the values used in the main data source @dpvalue)
  4. apply modifications and close data source editor
  5. refresh all data sources
  6. Edit the main data source and copy the “@dpvalues” refs
  7. paste your handmade SQL with the “@dpvalues” correct refs.
  8. apply modifications and close data source editor

After doing that the “WIS 00007” doesn’t occur anymore
Seems like the datasource generator needs saved informations before we can use @dpvalues in handmade SQL

Hope this could helps (in spite of my awful english)

khrel44 :fr: (BOB member since 2010-03-25)

6

I’m not a fan of custom SQL, but well done for figuring this out 8) .

Mak 1 :uk: (BOB member since 2005-01-06)