cancel
Showing results for 
Search instead for 
Did you mean: 

No Join in Tables

former_member402770
Participant
0 Kudos

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,

Accepted Solutions (1)

Accepted Solutions (1)

amitrathi239
Active Contributor
0 Kudos

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

former_member402770
Participant
0 Kudos

Hi Amit,

  Thanks Is the above screen fiscal month/year object->advance->List of values and select the newly created LOV's for this to be applied for both Derived table Delhi and Mumbai Fiscal Year/period (Selection Options).


Appreciate your help

Thanks,

Dinya

mhmohammed
Active Contributor
0 Kudos

Yes, it has to be applied in the LOVs for both the objects in Delhi and Mumbai folders.

Thanks,

Mahboob Mohammed

former_member402770
Participant
0 Kudos

Hi,

Amit and Mohammed, i tried the steps, iam ended up in

Invalid Table or view identifier 'DT_Mumbai'

Script for above error is:

WITH

"DT_Delhi" AS NATIVE ( "Test Delhi CONNECTION",

'sELECT KYD.Fiscal Year/Period (Selection Options), YFD.act, KYD.ugt, KYD.no, KYD.desc, KYD.hoj, KYD.Unit, HAH.sTotal, KYD.vTotal AS EAC, KYD.sdfo, tblJobs.sdfe, ent.wefr, ent.sadity, ins.wer FROM (((KYD INNER JOIN HAH ON (KYD.ewf = HAH.ewf) AND (KYD.xc = HAH.xc)) ) INNER JOIN ubf ON KYD.sd = sd.fg INNER JOIN ins on (KYD.Instance = ins.Instance) INNER JOIN ent on (ent.Entity = ins.Entity) WHERE ((HAH.Action='sdf') AND ((KYD.Show)<>0));',

)

SELECT

  Table__13."Fiscal Year/Period (Selection Options)"

FROM

  "DT_Delhi"  Table__13

union

SELECT

  Table__14."Fiscal Year/Period (Selection Options)"

FROM

  "DT_Mumbai" Table__14

Pls. note that this is Multisource Connection one each for Delhi and Mumbai source(MSSQL)..

Thanks,

Dinya

amitrathi239
Active Contributor
0 Kudos

Sql starts from WITH is coming default when you are viewing the object script?

former_member402770
Participant
0 Kudos

Yes Amit i checked it starts WITH

Thanks,

Dinya

amitrathi239
Active Contributor
0 Kudos

create one more derived table on the table 13.

inside sql like select  * from table 13 for table 15.

map the fiscal year object with this nested derived table.if this will work with custom lovs then later can remap the table 13 objects to table 15

former_member402770
Participant
0 Kudos

Ami,

I tried with options of Combined queris union in the tool. i got an dialog query script is valid. let me goahead and posted with the updates. BTW, anyways  to check the LOV's in universe it self for this new LOV.

Thanks,

Dinya.

amitrathi239
Active Contributor
0 Kudos

After assigning the lovs to objects within businss layer you can create queries and drag the object in filter panel.can see the object lovs.query panel is similar to webi query panel

in my first post query icon is in left and side of  parameters icon in first screenshot

former_member402770
Participant
0 Kudos

Hi Amit,

  After assigning LOV's , below is what hapening, the Fiscal Year/Period prompt selection keeps on refreshing without populating LOV's. Below screen is what it behaves on clicking prompt selection screen.

Thanks,

Dinya

amitrathi239
Active Contributor
0 Kudos

might be LOV's list is with high volume of  records.That is the reason it is taking time to display the list.try to search some record and see if that will work. how many records are in the individual objects?

former_member402770
Participant
0 Kudos

Hi Amit,

I have fiscal period as 20 Year LOV's. Also i have one more clarity do we need to place query filter of Fiscal year/Period for both delhi and mumbai  to have as common prompt else one filter sufice as we already did universe level union ?

simply use this object in the report query filter for delhi and mumbai separately to get all the LOV's?

Thanks,

Dinya.

amitrathi239
Active Contributor
0 Kudos

Hi,

Assign the custom LOV's to only mumbai fiscal month/year object and in webi keep the same prompt text for both .Select the "prompt with list of values" option in Prompt option  for Mumbai fiscal month/year prompt.

For other unchecked the "prompt with list of values" option.with this you will get the all values from Mumbai fiscal month/year object.

Amit

former_member402770
Participant
0 Kudos

Amit,

Correct me if iam wrong,.


New Lov created based on query 1 Delhi and click on fiscal month/year object of Delhi 's->advance->List of values and select ed the newly created LOV's for this.

in webi keep the same prompt text for both .Select the "prompt with list of values" option in Prompt option  for Mumbai fiscal month/year prompt.

For other unchecked the "prompt with list of values"option.with this you will get the all values from Mumbaifiscal month/year object.

Since there is an change some additional connection/class coming in as Delhi, Mumbai, Banglr, Chennai. So now to create new LOV based on which class/query object and assign to which objcet of class

iSelect the "prompt with list of values" option in Prompt option  for  which class fiscal month/year prompt.

For other unchecked the "prompt with list of values"option?

Thanks,

Dinya.

amitrathi239
Active Contributor
0 Kudos

Hi,

if you are selecting the custom LOV's for Delhi then follow these.

in webi keep the same prompt text for both .Select the"prompt with list of values" option in Prompt option  for Delhi fiscal month/year prompt. because you have added the custom lov's for Delhi object.

For Mumbai unchecked the "prompt with list of values"option.with this you will get the all values from Delhi fiscal month/year object.


Amit

former_member402770
Participant
0 Kudos

Amit,

thanks for your quick reply, my webi report does recon data between delhi and mumbai, i would be merging prompts and merging dimension(plant, ord no etc.) to show the expected results. Will the above custom Lov will do any restriction based on data if applied at prompt level, If yes, what i should do like this kind of recon report scenarions.

Thanks,

Dinya.

amitrathi239
Active Contributor
0 Kudos

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

Answers (1)

Answers (1)

mhmohammed
Active Contributor
0 Kudos

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