How do I expand a funtion's maximum character length? eg CUBESET

C

Connie-UTHSCSA

A function's maximum character length is limited to 255. However, our cube
has multiple date layers [year, quarter & month]. We are in the 11th month
of the fiscal year. Therefore, I am max'ing out the character length
maximum. Any ideas of how I can get around this? Here's what I need to
retrieve.

=CUBESET("RMDSCHED","{[Fiscal_Period].[All Fiscal Period].[2009].[Quarter
1],[Fiscal_Period].[All Fiscal Period].[2009].[Quarter
2],[Fiscal_Period].[All Fiscal Period].[2009].[Quarter
3],[Fiscal_Period].[All Fiscal Period].[2009].[Quarter 4].[June].[All Fiscal
Period].[2009].[Quarter 4].[July]}","[Multiple Items]")
 
J

jamescox

When your formula is copied and pasted into a cell, the error messag
says:

"Text values in formulas are limited to 255 characters. To create tex
values longer than 255 characters in a formula, use the CONCATENAT
function or the concatenation operator (&)."

Hopefully, you looked those two topics (CONCATENATE and th
concatenation operator) up in the help, but had problems figuring ou
how to use them.

In an arbitrary area of your worksheet (I used B26:B30) enter th
following text in the cells

{[Fiscal_Period].[All Fiscal Period].[2009].[Quarter 1],
[Fiscal_Period].[All Fiscal Period].[2009].[Quarter 2],
[Fiscal_Period].[All Fiscal Period].[2009].[Quarter 3],
[Fiscal_Period].[All Fiscal Period].[2009].[Quarter 4].[June],
[Fiscal_Period].[All Fiscal Period].[2009].[Quarter 4].[July]}

Then, enter in some cell (B35 for me)

= B26 & B27 &B28 & B29 & B30

Your cubeset formula (wherever you had it) now becomes:

=CUBESET("RMDSCHED",B35,"[Multiple Items]")

That should work, though of course for me it throws a #NAME erro
because I don't have RMDSCHED or any of these fields/parameter
defined.

Hope this helps.... :Bg
 
C

Connie-UTHSCSA

Thanks! This worked. We also read that a text only cell has a 37,000+
character limit. Therefore, we could type the text in cell A1

{[Fiscal_Period].[All Fiscal Period].[2009].[Quarter 1],[Fiscal_Period].[All
Fiscal Period].[2009].[Quarter 2],[Fiscal_Period].[All Fiscal
Period].[2009].[Quarter 3],[Fiscal_Period].[All Fiscal
Period].[2009].[Quarter 4].[June],[Fiscal_Period].[All Fiscal
Period].[2009].[Quarter 4].[July]}

and the function would be written thus

=CUBESET("RMDSCHED",A1,"[Multiple Items]")

Your explanation spurred my workmates to think outside the box. Thanks!
Connie
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top