Adhoc Report

[tmumaw]

Good morning everyone,

I have created an Adhoc report that has about 35 dimensions and 9 measures.  I have added another dimension and now when I select the new dimension I get the one above it too without selecting it.  Any ideas?

Thanks

[sunny_talwar]

Would it be possible to look at a sample?

[#3]

check whether the dimension is Locked or not.

provide with an example. so that we can look into it.

[tmumaw]

It would be tough.

[sunny_talwar]

What is the show/hide expressions you are using? Is it using SubStringCount()? Is it possible that the new dimension uses part of the other dimension name?

[tmumaw]

For everyone of my dimensions I enable conditional with this =SubStringCount(Concat(_dimension, '|'), 'PC Desc')

                                                                                                                          =SubStringCount(Concat(_dimension, '|'), 'Arborist')

                                                                                                                          =SubStringCount(Concat(_dimension, '|'), 'Client Zip')

                                                                                                                          =SubStringCount(Concat(_dimension, '|'), 'Client Zip9')

When I select just Client Zip9 I also get Client Zip.

[tmumaw]

Yes it does use part of it's name.....bingo....I thing that's the problem

[santiago_respane]

Hi,

Maybe both dimensions have similar names, please can you share your expression?

One expression i used for this:

SubStringCount('|' & Concat(distinct [MyField], '|') & '|', '|MyFieldValue|')

If i dont use the '|' around the value it can happen what you mention. If i have two two fields 'Company' and 'Company Name' when i select one the other will be included also, that's why the '|'.

How to use it, let say my dimensions field is called %UI.Dimensions.Name and i want to conditionally show the Company dimension, the expression should be:

SubStringCount('|' & Concat(distinct [%UI.Dimensions.Name], '|') & '|', '|Company|')

Please let me know if this helps.

Kind regards,

[sunny_talwar · Accepted solution]

Try this may be:

SubStringCount(Concat(DISTINCT '|' & _dimension & '|', ','), '|PC Desc|')

UPDATE: And don't forget to add DISTINCT for a slightly better performance

[tmumaw]

Thanks Sunny and Santiago.....