on 10-19-2015 4:33 PM
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,
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
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
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.
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
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.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
83 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.