Question Re: Repeating columns in report (to support custom SQL) in Technology Q&A

Repeating columns in report (to support custom SQL)

Former Member — Tue, 19 Nov 2013 13:40:12 GMT

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

Re: Repeating columns in report (to support custom SQL)

Former Member — Tue, 19 Nov 2013 14:11:10 GMT

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

Re: Repeating columns in report (to support custom SQL)

Former Member — Tue, 19 Nov 2013 16:52:09 GMT

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

Re: Repeating columns in report (to support custom SQL)

Former Member — Tue, 19 Nov 2013 16:57:28 GMT

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

Re: Repeating columns in report (to support custom SQL)

Former Member — Tue, 19 Nov 2013 16:57:45 GMT

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?

Re: Repeating columns in report (to support custom SQL)

Former Member — Tue, 19 Nov 2013 17:02:47 GMT

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.

Re: Repeating columns in report (to support custom SQL)

former_member190855 — Tue, 19 Nov 2013 17:09:44 GMT

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

Re: Repeating columns in report (to support custom SQL)

Former Member — Tue, 19 Nov 2013 20:40:49 GMT

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?

Re: Repeating columns in report (to support custom SQL)

Former Member — Tue, 19 Nov 2013 20:44:57 GMT

Hi Runali,

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

Thanks

Uday

Re: Repeating columns in report (to support custom SQL)

Former Member — Tue, 19 Nov 2013 21:08:38 GMT

Hello Moderators,

Could you move this thread to the Webi space?

Re: Repeating columns in report (to support custom SQL)

Former Member — Tue, 19 Nov 2013 22:34:11 GMT

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

Re: Repeating columns in report (to support custom SQL)

Former Member — Wed, 20 Nov 2013 00:13:23 GMT

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

Re: Repeating columns in report (to support custom SQL)

former_member190855 — Wed, 20 Nov 2013 00:28:48 GMT

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