Data region in RS allows only 1 data source - virtual cube solutions?

I have a report requiring two different data sources be compared in one
data region. For example, I'm comparing a measure for a client against
a national average. The client measure comes from one cube and the
national average is stored in a sql database. RS won't allow two data
sources to be used in a data region unless you can use an aggregate
function on the second data source. I attempted to solve this by
creating a cube for the second data source (national averages) and then
a virtual cube to make one data source (i.e. one MDX statement from the
virtual cube per data region) for RS. The orginal MDX used for the
first data source required slicing by dimensions not included in the
national average cube and visa versa. I have been able to get the
correct answer for each cube before combining into the virtual cube,
but I can not get the measures correctly in the virtual if, for each
measure, I need to slice by a dimension that is not shared. I haven't
been able to find the information anywhere (and I've tried internet and
2 books), but because my measures are not correct I assume that a
virtual cube in only useful if the correct results are obtained by
slicing into only the shared dimensions? Are there any other solutions
to the rediculous RS limitation of only being able to provide one data
source to a data region? What we they thinking'oops, should have used spell check before submitting. it is a
ridiculous RS limitation.|||Hi Carrie,
I believe you can refer to more than one datasource from a data region such
as a table. Try putting in two data sources on a report and drag data from
them both into a table (note, you have to set the tables data source to one
data set).
see how that goes
<cwilliams@.choosehmc.com> wrote in message
news:1104767162.584780.225260@.z14g2000cwz.googlegroups.com...
> I have a report requiring two different data sources be compared in one
> data region. For example, I'm comparing a measure for a client against
> a national average. The client measure comes from one cube and the
> national average is stored in a sql database. RS won't allow two data
> sources to be used in a data region unless you can use an aggregate
> function on the second data source. I attempted to solve this by
> creating a cube for the second data source (national averages) and then
> a virtual cube to make one data source (i.e. one MDX statement from the
> virtual cube per data region) for RS. The orginal MDX used for the
> first data source required slicing by dimensions not included in the
> national average cube and visa versa. I have been able to get the
> correct answer for each cube before combining into the virtual cube,
> but I can not get the measures correctly in the virtual if, for each
> measure, I need to slice by a dimension that is not shared. I haven't
> been able to find the information anywhere (and I've tried internet and
> 2 books), but because my measures are not correct I assume that a
> virtual cube in only useful if the correct results are obtained by
> slicing into only the shared dimensions? Are there any other solutions
> to the rediculous RS limitation of only being able to provide one data
> source to a data region? What we they thinking'
>|||The answer is to create a calculated member using the ValidMeasure
function for each measure in the virtual cube. Just in case anyone is
interested...sql

Data Grouping

hi,

I want to group data in matrix column. Lets say i have a field say Weekday which has weekdays from monday to friday. then suppose i have measure "my expenditure".

I will place Weekdays in column field of matrix and "my expenditure" in data field". lets not worry about rows.

Now i want something like this.

Group my expenditure in three categories like

1. My expenditure on monday

2. My expenditure on tuesday

3. My expenditure on days other than monday and tuesday.(means it should show me data for wednesday,thursday,friday and also if no weekday is entered)

what I am doing now is i m writting IIF expression in the column field but with that i get data for monday and tuesday but data for all the other days is not getting clubbed.

how can i do this?

Thanks

rohit

Is my problem not clear?

Please anybody help!!

Thanks & regards

|||

Instead of using a matrix table top create your report, you could use a query to pivot your data.

(SUM(CASE WHEN DAY = 'MONDAY' THEN EXPENDITURE ELSE 0 END)) AS [MON EXP],

(SUM(CASE WHEN DAY = 'TUESDAY' THEN EXPENDITURE ELSE 0 END)) AS [TUES EXP],

(SUM(CASE WHEN (DAY <> 'MONDAY' AND DAY <>'TUESDAY') THEN EXPENDITURE ELSE 0 END)) AS [ROW EXP]

FROM EXPENDITURE_TABLE

Then insert the fields from the datasource into a std table component in a report.