One chart with two datasets

Question

One chart with two datasets

Helical Joan2

Old Hand

Points: 368
More actions
May 10, 2012 at 7:40 am

#256287

Hi,
I have a report with two datasets. Each dataset used the same cube, but different dimension.
Dataset 1:
Month
Advance_count, with dimension CreationDate
SELECT NON EMPTY { [Measures].[Advance_Count] } ON COLUMNS, NON EMPTY { ([Creation Date].[Month].[Month].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@CreationDateMonth, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [CRM Advances].[Company Name].&[IFX Co] } ) ON COLUMNS FROM [CubeAdvance])) WHERE ( [CRM Advances].[Company Name].&[IFX Co] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
Dataset 2:
Month
Advance_State
Advance_count, with dimension UpdatedDate
SELECT NON EMPTY { [Measures].[Advance Count] } ON COLUMNS, NON EMPTY { ([Update Date].[Month].[Month].ALLMEMBERS * [CRM Advances].[Advance State].[Advance State].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [CRM Advances].[Advance State].&[Rechazada], [CRM Advances].[Advance State].&[Completada] } ) ON COLUMNS FROM ( SELECT ( STRTOSET(@UpdateDateMonth, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [CRM Advances].[Company Name].&[IFX Co] } ) ON COLUMNS FROM [CubeAdvance]))) WHERE ( [CRM Advances].[Company Name].&[IFX Co] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
How can i draw in a chart this information?
I have two charts, but I must do it in only one chart.
Thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

im_lanie SSC Veteran Points: 223 More actions · Answer 1

I'm pretty sure you can't use two datasets in a Chart. I assume your report has two data regions on it and that's why you are able to have the two datasets represented. Try to combine your MDX query into one. If I'm wrong, maybe someone else out there can correct me.

Mark Fitzgerald-331224 SSCrazy Eights Points: 8489 More actions · Answer 2

Helical Joan2 (5/10/2012)
Hi,
I have a report with two datasets. Each dataset used the same cube, but different dimension.
Dataset 1:
Month
Advance_count, with dimension CreationDate
SELECT NON EMPTY { [Measures].[Advance_Count] } ON COLUMNS, NON EMPTY { ([Creation Date].[Month].[Month].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@CreationDateMonth, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [CRM Advances].[Company Name].&[IFX Co] } ) ON COLUMNS FROM [CubeAdvance])) WHERE ( [CRM Advances].[Company Name].&[IFX Co] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
Dataset 2:
Month
Advance_State
Advance_count, with dimension UpdatedDate
SELECT NON EMPTY { [Measures].[Advance Count] } ON COLUMNS, NON EMPTY { ([Update Date].[Month].[Month].ALLMEMBERS * [CRM Advances].[Advance State].[Advance State].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [CRM Advances].[Advance State].&[Rechazada], [CRM Advances].[Advance State].&[Completada] } ) ON COLUMNS FROM ( SELECT ( STRTOSET(@UpdateDateMonth, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [CRM Advances].[Company Name].&[IFX Co] } ) ON COLUMNS FROM [CubeAdvance]))) WHERE ( [CRM Advances].[Company Name].&[IFX Co] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
How can i draw in a chart this information?
I have two charts, but I must do it in only one chart.
Thanks.

If you are using SSRS 2008 R2 then you can make use of the LOOKUP function. This must be done from within Dataset2 into Dataset1. This is due to having multiple month (per Advance States) in Dataset2, but only a single value for each month in Dataset1.

Create a chart using dataset1. Then add the dataset1 AdvanceCount again to the chart and change the expression to :

Example:-

=LOOKUP(Fields!FieldNameToUse.Value, Fields!ToLookValueUpIn.Value, Fields!ToReturn.Value, "DatasetToLookupInto")

Using your data (guessing):-

=LOOKUP(Fields!Month.Value, Fields!Month.Value, Fields!Advance_Count.Value, "Dataset1")

This should lookup the Advance_Count field from the "Dataset1" dataset. You will need to change the names if they do not match your names.

Fitz