Measures with various data expressions per intersection in a crosstab report

[cognovice]

Hi,
I would like to know if we are able to create a crosstab report as per the attached screenshot.  There are different measures like Order count, Line count, Average orders, Order value etc.. to be displayed in columns as Today, WTD, FTD, MTD, QTD, YTD.

Thank you for your help.

[cognostechie]

I think it can be done. You can put the measures in the rows one by one. I can 4 measures. The Average Orders per Day would be Number of Orders / No. of Days. Those counts can be created and the WTD, YTD etc. are the relative time which you can create either in Framework Manager or Report Studio.

[cognovice]

Hi Cognostechie,
Thank you for your prompt response.  However, how can I insert the first measure in six different columns for me to get six different values of the same measure (Today, WTD, FTD, MTD, QTD, YTD).

[cognostechie]

Hi cognovice -

The base measure should not be used in the query. You can create separate measures from any base measure for only that date range which is needed
for that column.

Ex:  Suppose you have a measure for No. of Orders called 'Order Count'. Now you can create another measure with this condition:


      Case
         When Date Range >= ... and Date Range <= ... then [Order Count]
         Else 0
      End

If the Date range is within the week then this measure will give you the Order Count for WTD. Similarly, you can create measures for Order Count QTD etc.
Just be careful that the count is not always additive so you might have to use a condition like:

Count (distinct   Case When......   )

This is to give you an idea of how you can create different measures for WTD, YTD, WTD etc and then put those measures in the columns.     

[cognostechie]

With a dimensional package like a cube , this can be done with just drag & drop as this would be already built-in when the cube is created.
However, if you are using a Relational package then you might have to use the technique I suggested in multiple queries (one for each measure)
and then union it.

[cognovice]

Hi Cognostechie,
I am getting this below error message.  I am lost a bit.  could you please help where I am making a mistake.

RSV-VAL-0010 Failed to load the report specification.  RSV-RND-0136 The data item reference in the crosstab space is not valid

[sdf]

dimensional you can use TUPLE for each measure,
relational you can use FOR.

[cognovice]

Hi Friends,
I still can't get this report to work.  somehow I am missing a major part of how to work with multiple measures in a crosstab report.  As per my previous attachment and now attached again, can some one post a working sample of a similar report please..

[MFGF]

Are you using a dimensional package or a relational package?

MF.

[cognovice]

Hi MFGF,
Thanks for your response.  I am using relational package.

[MFGF]

Hi MFGF,
Thanks for your response.  I am using relational package.

Oh dear - that makes things very complicated in that case. It would have been much simpler using a dimensional package.

The approach you are going to have to take here is to create separate queries corresponding to 'Today', 'WTD', 'FTD', 'MTD', 'QTD' and 'YTD'. Each will need to have all your desired measures in it plus a calculated item specific to each - the query calculation in the Today query would contain 'Today' as the expression, and the query calculation in the WTD query would contain 'WTD' as the expression etc. You'd then need to add an appropriate detail filter to each query so it retrieves the desired date or date range. Next you'd need to union all the queries (in the query explorer) and have the final result flowing through to the query for your crosstab.

Good luck!

MF.