how to show same records in all crosstabs

I have 6 crosstabs in the report. I need to show same records for all the measures in same order. I’m getting correct ordering of records only for one measure(total sales) on which i’m sorting the query. But i need to show the same sequence of records for other 5 measures. The only thing i can do is apply ranking on one of the dimesions based on total sales, but then i dont get correct ranking and sorting. My 5 other measures are %values and sorting on those values doesnt make any sense. Any help would be appreciated.


BOisBest :india: (BOB member since 2004-04-05)

It sounds like you’re trying to apply a dynamic custom sort. That might be possible with VBA, but not with standard functionality.


MichaelWelter :vatican_city: (BOB member since 2002-08-08)

Michael,
is there any way this can be done? How do we do it usually i.e. showing same sequence of dimension records for different measures in crosstab report?


BOisBest :india: (BOB member since 2004-04-05)

You can apply a custom sort to any dimension column. However, that sort is not dynamic. Perhaps, if you understand the sorting requirements, you can create a variable that will provide a sortable value for you. However, I’m having a hard tome understanding what you want. :oops:


MichaelWelter :vatican_city: (BOB member since 2002-08-08)

Michael,
i’ll explain my requirement again. I have 6 crosstabs. In each crosstab, i have 3 dimensions and 1 measure. total, i have 3 dimensions and 6 measures. I can sort my reoprt only on Dimension1 based only on Measure1. Based on this sorting, i want to show same sequence of rows in other crosstabs for other measures( i cannot sort using these measures).
I dont know whether i explained properly or not.


BOisBest :india: (BOB member since 2004-04-05)

I see what you’re getting at now. I haven’t tried this but here’s an idea: Try adding Measure 1 to all 6 crosstabs, and sorting on it. Then hide it in the 5 crosstabs where you don’t want it.


MichaelWelter :vatican_city: (BOB member since 2002-08-08)

Thanks Michael, i’ll try that option. But one more question, how do we hide measure in crosstab ? Simply by removing it from crosstab?


BOisBest :india: (BOB member since 2004-04-05)

Don’t remove it, that would defeat the purpose. Format, Table, and on the Pivot tab select the measure and click hide.


Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

Thanks Dwayne. It worked . Thanks Michael.


BOisBest :india: (BOB member since 2004-04-05)