on 10-15-2015 4:11 PM
Hi,
1) I have been provided with an SQL Query like
Select
From
Where
2) I also could able to see the above tables present in the SQL Query with an universe connection(MSSQL) based schema user.
Now, i am struck with what universe design i can approach for eg: derived table with provided sql query or to design with table and joins of the SQL Query or to write an custom sql in the report level. Pls. provide some steps for the right approach.
Thanks,
Dinya,
If the tables are already in your universe and so are the objects, then you can just build a Webi query with them. A derived table is not a best practice solution.
If the tables are not there:
Insert the tables into your schema, join them on the keys in your join condition.
Create the objects that you need to use in your query.
The key to your date calculations is how your fiscal period is stored. What data type is it on SQL Server? If it is datetime then you can simply use the datepart function to create further objects within the same class as your fiscal period object.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Can we use the below sql in derived tables. Pls put your thoughts on top of this, willit cause any performance in webi, If yes any chance of alteration in the query
sELECT KYD.month, 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));
Any thoughts on above for fine-tuning
Thanks,
Dinya.
Hi,
If you have already the SQL then easy step is to create derived table.create objects and use in the webi.
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,
Thanks, i have created derived table with the already available sql below:
Select FiscalMonth, projtype
From , FiscalPeriod INNER JOIN Proj ON (FiscalPeriod .FiscalMonth = Proj .projtype)
Where (FiscalPeriod .FiscalMonth = '11/1/2012' AND projtype<>0);
Below is the output of the above query:
I am trying to get separate out the Month and Year and form as Prompt Filter in universe to display 011.2012.
One more questions can i use the inner join on from clause and where condition in an derived table because i would like to use the say for eg: FiscalMonth and projtype!=0 as webi prompts.
Appreciate your help may be with some screenshots
Thanks,
Dinya.
Hi,
I am not good in the writing SQL..If you can write SQL and spilt the records then you can use in the derived table.Yes you can defined the prompts in the derived table.
If not then might be use different tables and joins.in your earlier thread i have explained with steps to combined the month and year.
Amit
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.