Skip to Content

No Join in Tables

Hi ,

I have two isolated tabels in my universe design like below as its business layer separately.

At report level, iam trying to have Fiscal Year/period (Selection Options) as common query filter by having same prompt text, where iam trying to search value, at any point of time only delhi conn prompt LOV is apearing. Basically i want LOV's as union of both.

Appreciate your help.

Thanks,

Dinya,

Capture.JPG (28.1 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Oct 19, 2015 at 04:03 PM
    10

    Hi,

    if you will go with default LOV's then in webi you will get the LOV's of the objects where highest options are selected at prompt level.Like  in prompt option "prompt with list of values" selected for  First query.but this is not selected in the second webi query prompt option.in this case you can only see  LOV's from one object only.At one time you can see LOV's from one object only.

    Other option is to  create Custom LOV's at universe level for let say Fiscal Year /Period from delhi class.

    Follow these steps.

    1) click on parameters and list of values and create list of values based on the business layer objects.

    2) drag the object and click on the view script.

    3) add the union and manually the second object sql & validate and save it.

    4)click on fiscal month/year object->advance->List of values and select the newly created LOV's for this.

    After this use this object in the query and you will get all the LOV's.

    Amit

    Add comment
    10|10000 characters needed characters exceeded

    • Hi,

      no impact on the data. assigning custom LOV's are only limited with the display of LOV's in the prompt.with the custom lov's you can see all the  values in the prompt list and the value you select in the prompt based on that you will get the data from two  queries.

      let say if you select prompt value from LOV's 010.2015 then same value  pass in the Delhi and Mumbai queries.if values are not present in the Mumbai query for 010.2015 then you will not get the data.

      Amit

  • Oct 19, 2015 at 03:58 PM

    Hi Din 44,

    Apart from all the technical and functional aspects, if you just want to see all the possible values (from 2 tables) in the LOVs, create a LOV in the Parameters and LOVs section of the universe (in IDT) as say Fiscal Year/Period, in the SQL of that LOV, add a combined Query and update the SQL by clicking on View Script, (this is just an example) and Check the option that says "User custom query script".

    select distinct fiscalyearperiod from derivedtabledelhi

    union

    select distinct fiscalyearperiod from derivedtablemumbai

    And finally, associate that newly created LOV to the Fiscal Year/Period object.


    Thanks,

    Mahboob Mohammed

    Add comment
    10|10000 characters needed characters exceeded