Dynamic Columns in BI Services?

[david_lai]

Hi,

I was wondering if anyone has figured out a work around to have a dynamic set of columns working for BI Services

For example I have a cross tab where the columns change depending on what region was selected

Region 1 selected

Office 1 | Office 2

a

Region 2 selected

Office 1 | Office 2 | Office 3

a

Unfortunately in Xcelsius if I try to bind 3 columns, the first selection values will come back all messed up.

I was thinking if there were some way to put buffer columns in the back somehow so that we always retreive a consistent amount of columns?

If anyone can shed some light on this it would be greatly appreciated.

Thanks

David

[Former Member · Accepted solution]

I've written a short post on how to do this. (I see you've read it already David.)

The BI Service contains values for the number of columns and rows it contains. Using Excel formulas you can re-create a varying column Webi block in the spreadsheet.

http://blog.davidg.com.au/2012/06/bi-services-with-crosstabs-variable.html

[Former Member]

in xcelsius ->go to data-> connection---->there you could extent the the no of columns manually

[david_lai]

Umm Manual does not equal dynamic..

[Former Member]

ya....but you mark manually 500 columns so that it does not going to exit........

-

---

******************----

---

As you said no of rows returned may be dynamic in that case 100 % we have to map manually....up to now every one is doing that only........ I dont hope so its not possible what you are expecting

[david_lai]

#1 the question says "Dynamic Columns" not "Dynamic Rows"

Columns goes like this | | | |

Rows goes like this

-

-

-

-

#2 I mentioned on the example if you set a fixed amount of columns on Xcelsius let's say 3 columns. But only 2 columns is returned. Then the output of the set containing 2 columns will be messed up.

Anyhow thanks for trying