cancel
Showing results for 
Search instead for 
Did you mean: 

Crosstab in IDT

Former Member
0 Kudos

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).

  1. Is that possible to create a crosstab query in IDT?
  2. Is that possible to have a parameter/prompt in JOIN statement? The parameter/prompt has to be put on there instead of WHERE statement due to some reasons.

          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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

Hi Amano,

Can you please post the query you used here.

Thanks

Gaurav

Former Member
0 Kudos

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

Answers (1)

Answers (1)

Former Member
0 Kudos

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!

Former Member
0 Kudos

Hi Mark,

Thanks.

  1. Yes, you are right but I would prefer to have a dynamic prompt. Is that possible to achieve?
  2. Yes, but I want it to be dynamic, because normally we do the parameter/prompt in WHERE statement. I wonder how can we do dynamic prompts in JOIN?

Thanks.

Regards,

Amano

Former Member
0 Kudos

1/ You can add a prompt to a derived table via a self-restricting join.

2/ By dynamic do you mean optional? The only way to implement an optional prompt over a join is in the query panel in Webi - only OLAP-based universes support optional prompts.

Former Member
0 Kudos

Hi Mark,

May I know what is self-restricting join? How the code looks like?

No, it's not optional prompt. What I mean here is, it is not a filter, it is not a pre-defined prompt... It should allow me to change the value in dashboard in the end, which this is my goal.

Thank you.

Regards,

amano