Crosstab column aligning issues - Dimensional Model

[geethuworks12]

Hi,

I got a weird problem. Please help to resolve.

Im creating a crosstab report based on a Cube.

Column 1 : set([CubeName].[DimName].[Hierarchy] -> ?p_Region?)
Column 2 is nested on different measures based on static choices selected from Prompt :
Column 2 Nested 1 : case when ('S1' in (#promptmany('Measure','varchar')#)) then([Revenue]) else('NA') end
Column 2 Nested 2 : case when ('S2' in (#promptmany('Measure','varchar')#)) then([Sales]) else('NA') end
Row : set([CubeName].[DimName].[Hierarchy] -> ?p_Date?)

Depending on the selection from prompt either Revenue or Sales need to be displayed in Report under each Region.
So i did a BOX TYPE none for the Boolean Variable on the Nested Column and their Member fact cells.

Now the problem is when i execute the report for more than one region the output is correct but the alignment is wrong.

Please refer the attached screenshot. Please provide some idea.

[TheFrenchGuy]

Hello,

If I've understood, a prompt allow user to select a measure (choice between S1 Revenue or S2 Sales).
An other way to deal with that is to create a variable, 1 crosstab/measure and render crosstab depending on your variable

[geethuworks12]

Hi Frenchguy,

Instead of multiple measures in the Nested Column if i have only one for example Revenue alone it is working properly and alignment is also perfect.

If it is a render variable then i need to make 5 variables as i need to give static choices for selecting among five measures and 5 pages of report to render it.

This nested column alignment is the issue. When i select Sales in the prompt instead of Revenue then the alignment is moving one column forward and collapsing. Trying to find a way to fix this.

Thank you

Thank u

[TheFrenchGuy]

The layout issues aren't the easier to solve ... and more difficult about box type issues with nested columns !

Can you provide the table structure of your report ?
A solution could be to duplicate column1 to have 1 data item above each nested column.
Or you can modify you case when and concatenate it whith your other value

Code Select Expand

case
when ('S1' in (#promptmany('Measure','varchar')#))
then([Revenue])
when ('S2' in (#promptmany('Measure','varchar')#))
then([Sales])
else('NA') end

Btw I don't understand why 5 pages, and not 5 crosstabs ...

[geethuworks12]

Hi Frenchguy,

I have removed the Box Type = None and tried to adjust the properties like Padding, Size and Overflow, Font Size, Border, Background and Foreground Color. I was able to suceed with the alignment but there was a very small white behind each column.

But your idea of putting all the Static Choice selection in one Data Item worked perfectly to my problem. Until i saw you code i got your solution as rendering a page. But i implemented your solution on a Style Variable and it is working perfectly.

Thank you so much.  Thanks a lot for taking time to understand my problem in your shoes and reply to it.

[TheFrenchGuy]

No problem =)

[geethuworks12]

sorry sorry...no variable at all now in report that case when statement deals all :-)