Repeating columns in report (to support custom SQL)

[#1]

I'm not sure if this has been addressed in this forum but I wasn't able to find any references hence this question.

We have a requirement to support custom SQL (i.e., not generated by webi) for some of our reports. The same column needs to appear more than once in the report and but I am unable to drag it from my set of universe objects more than once into the report pane. I've tried using a blank cell and trying to bind that to the query but haven't had any success.

What is the best way to create such reports in web intelligence?

Uday

[former_member190855]

Hi Uday

For future reference, try to post any WebI questions in the Web Intelligence forum.

However, if I understand your requirement correctly, I think you should drag the universe object only once in QueryPanel and create a variable inside the report > equate it to the object you want to repeat > in the report table add universe object and the variable.

Let us know if that works and if you have any further questions.

Thanks

Runali

[Former Member]

Hi Runali,

Your suggestion sounds promising - will try it and let you know how it goes.

Thanks

Uday

[Former Member]

Runali,

Tried your suggestion and I was able to create two new variables ISN2 and CSP2. When I try validating my sql I still get an error "the sql query has 13 instead of 11 columns". The report structure in the "edit report" tab has 13 columns but the Result Objects pane in "Edit Query" tab only has the 11 from my universe.

To get around this I dragged two more columns into the Result Objects pane that have the same data type. The sql validated fine after I did this but my results show the same value for column 12 (ISN2) as for column 1 (ISN) instead of the expected value. Guess that is because my variable is defined as

=[ISN] in the formula section.

Guess I'm missing something in what you said. The webi product guide says nothing about any of this! Help!

Thanks

Uday

p.s. apologies for continuing this in the non-webi forum. .

[Former Member]

You will need to remove two columns from your custom SQL for this error... May be the old two columns are still there..

[former_member190855]

Hi Uday

What I am suggesting is say you have requirement of showing columns C1, C2, C3,....C11 and C12=C1

then in your Query dont add any dummy extra objects. Add only objects C1 through C11 and you dont need to use custom SQL unless you need it for any other purposes.

After running the query, add all the 11 objects to a table. Create a variable with formula =C1

Insert a column to the right of the table and type in =variable in the formula box.

Hope this makes it clear.

Runali