on 09-23-2013 10:04 AM
Hello. Hopefully that I posted at the correct place.
I have 2 questions regarding to IDT. I'm using BI platform 4.0 SP4 (with FP3).
eg. SELECT a.*, b.*
FROM b
LEFT JOIN a ON a.bla = b.bla AND a.blaxx = b.blaxx and a.id = 'C001' (prompt)
Are they possible?
Thanks.
Regards,
Amano
Hi Amano,
This you can achieve using a Derive table in universe.
You can write your custom built query in it and also use prompts in joins.
Thanks
Gaurav
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Gaurav,
Thanks, but is that possible by having dynamic prompts (which is not pre-defined) in crosstab/joins?
So far what I've been doing is creating views and creating dimensions by using derived tables. So I don't know how can I create a crosstab/join with dynamic prompts inside.
(SQL copied from postgresql)
SELECT *
FROM crosstab(
'select rowid, attribute, value
from ct
where attribute = ''att2'' or attribute = ''att3''
order by 1,2')
AS ct(row_name text, category_1 text, category_2 text, category_3 text);
I'm not sure my explanation is clear enough or not...
Let's said, my derived table as above, but I want att2 and att3 as my dynamic prompts. How could I achieve that in IDT? Could you show the way?
Thanks.
Regards,
Amano
Hi Amano,
You can place the prompt syntax the same way you apply it in Where clause.
If my understanding is correct att1 and att2 are the values and not columns right!!....If yes than you can right the query as below:
SELECT *
FROM crosstab(
'select rowid, attribute, value
from ct
where attribute = @Prompt('Displayed text ','A',,,) or attribute = @Prompt('Displayed text ','A',,,)
order by 1,2')
AS ct(row_name text, category_1 text, category_2 text, category_3 text);
Thanks
Gaurav
Adding to that...if the att1 and att2 are column names and not row values...we can do this:
1. Create derive table using union and place the column names in rows.
For eg.
Table D1
Attribute
att1
att2
att3
Now use this as a subquery in your cross tab derive table with @prompt in the subquery.
I have not tried it, but logically seems it will work.
Thanks
Gaurav
Hi Gaurav,
I understand that now. This may sound out of topic, but by doing so, I won't be able to get the prompt in dashboard. To stick to my post, I've opened another post here.
I've just tried crosstab in MS SQL SERVER 2008 R2. The SQL passed validation in IDT, but when I want to preview the result, it contains error. Please refer to my attachment. Does it mean that crosstab is not supported?
Thank you.
Regards,
amano
Hi Gaurav,
Here it is.
SELECT lecName, [F] AS Female, [M] AS Male
FROM
(SELECT l.lecName, s.gender, s.studName
FROM "tblLecturer" l LEFT JOIN "tblStudent" s ON l.lecID = s.lecID) p
PIVOT
(COUNT(studName)
FOR gender IN ([F], [M])
) AS PVT
Sorry, it was my mistake. The derived table name I named it as "pivot", so IDT returns me the error.
Thanks a lot.
Regards,
amano
1. Do you mean like the PIVOT functionality in SQL Server? If so, use a view or if you have to, a derived table, so long as your DBMS supports it.
2. Yes - just modify the join and try it!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
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.