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 Member]

Hi,

Open webi report > Data access > Edit > SQL (on top of query window).

There you have the option of adding custom sql and modify the query.

Else you could create a derived table in the Universe and bring to report.

Arun

[Former Member]

I wasn't asking about modifying the SQL. My question was about how to make the report match the custom SQL when the query returns the same column multiple times. I am unable to drag a column more than once into the report pane

[Former Member]

You should use different columns from the universe for the custom query but you will have to make sure the data type and measure and dimensions match with your custom sql..

Once you have done this you can just use these objects (all different) with a reference to what you have defined in the custom SQL

Also check the screenshot where I am using territory code in custom query even the element in the report is a different dimension

[Former Member]

Uday can you post a screenshot? in the reports pane you should be able to use the same columns as may times as you want. Also which tab are you using, Crosstab?

[Former Member]

What do you mean by "Query returns column multiple times"?

Have you tried the sql in SQL Server and see whats the result? If it doesn't work in SQL, it won't work in BI also.

[Former Member]

My custom query (with genericised view names to protect the innocent) is attached.

As you can see, it involves multiple joins on the same view (e.g., View2 is joined twice, once as relation x and once as relation x2). View2 has ISN as one of its columns.

I need to drag this twice into my report pane - once for the first column of the report (x.ISN) and once for the 12th column (x2.ISN). Webi will not let me drag this column a second time so how do I get around it?

CustomSQL.txt.zip