Hello, I have an SSRS 2005 report with a chart (stacked area chart). I have some optional input parameters which are series in the chart. When I select just one series, the chart shows. But, when I select more than one series, the chart does not show. Please help. Value in the chart is a parameter, which is Metric. Category group is a parameter, which is a Date Series group are parameters, Field1 and Field2, here is the expression I have for it... iif(Fields!Field1.Value = "'Null'","",Fields!Field1.Value) & " " & iif(Fields!Field2.Value = "'Null'","",Fields!Field2.Value) When I enter a value for Field1, the chart shows. But, when I enter a value for Field1 and Field2, the chart does not show. Why? Thank you in advance.

Hi mexl, I’m glad to help you on this issue. However, I am confused about the description. When you say parameter, do mean the data field in the dataset? Generally speaking, data field and parameter are different concept in Reporting Services. We can specify data field to the chart, and we can add parameter to the report to allow users filter report data or control the report appearance. And when you enter a value for Field1, do you use a report parameter or change the data in database? If possible, please post the dataset structure with some sample data, then I will try to assist you to achieve the needs. Thanks, Tony ChainTony Chain [MSFT] MSDN Community Support | Feedback to us Get or Request Code Sample from Microsoft Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Hey Tony, Thanks for your response. Field1 and Field2 are parameters to the report---a user can select the values to control report data and report appearance. but, I would like to use these fields as data fields in my dataset to specify data fields in my chart. These fields are input parameters to a stored procedure. The stored procedure has a dynamic query in it. A user can choose the value of 'Field1' and 'Field2' through a drop down list. These fields are "group by" fields. I am trying to show these fields on a chart. When I choose a value for Field1 only, my chart display ok., but when I choose something for both field1 and field2, the chart does not display. The data output displays ok, but not the chart. I hope this has cleared up my problem. What do you think? Thanks.

Hi mexl, Sorry for delay. Based on your description, I noticed that you are using dynamic query based on 2 multi-value parameters. Generally, stored procedures don't support multi-valued parameters in the way SSRS uses them. So, the values must be passed as a delimited string (for example, we can use the expression like =Join(Parameters!p1.Value,”,”) to concatenate the selected values) and then parsed in the procedure. However, I am still confused the dataset structure and how do you specify data fields to the chart. Could please post an example to show what you have achieved, and also post the current result and the expected result. Thanks, Tony ChainTony Chain [MSFT] MSDN Community Support | Feedback to us Get or Request Code Sample from Microsoft Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Hey Tony, The parameters are NOT multi-valued. User can only choose 1 value for each parameter. My chart is chart1. Chart properties: Under Data Under Series Groups is chart1_SeriesGroup1 The Group on expression is: =iif(Fields!Field1.Value = "'Null'","",Fields!Field1.Value) & " " & iif(Fields!Field2.Value = "'Null'","",Fields!Field2.Value) Field1 and Field2 are single valued parameters. A user can choose a value for each of these parameters. These values would be used in the GROUP BY clause in my query in my stored procedure. The default value for each of these is 'Null'. This is so if they do not choose a value, the query would still run. Here is an example of the query in the stored proc. SELECT [Round Trip Time], [Field1], [Field2] FROM [TableName] GROUP BY [Field1], [Field2] So, the user can choose values for Field1 and Field2 or leave them at the default as 'Null'. so, chart displays OK for the following. SELECT [Round Trip Time], [Destination Node], ['Null'] FROM [TableName] GROUP BY [Destination Node], ['Null'] But the CHART DOES NOT SHOW for the following SELECT [Round Trip Time], [Destination Node], [Interface Name] FROM [TableName] GROUP BY [Destination Node], [Interface Name] However, the data displays ok for this, but the chart does not display. I created the chart as a subreport. When I run it with a value for Field2, it says No Data returned. IS there something wrong with my group on expression in my chart properties? or the query? Thanks.

Hey Tony, The parameters are NOT multi-valued. User can only choose 1 value for each parameter. My chart is chart1. Chart properties: Under Data Under Series Groups is chart1_SeriesGroup1 The Group on expression is: =iif(Fields!Field1.Value = "'Null'","",Fields!Field1.Value) & " " & iif(Fields!Field2.Value = "'Null'","",Fields!Field2.Value) Field1 and Field2 are single valued parameters. A user can choose a value for each of these parameters. These values would be used in the GROUP BY clause in my query in my stored procedure. The default value for each of these is 'Null'. This is so if they do not choose a value, the query would still run. Here is an example of the query in the stored proc. SELECT SUM([Round Trip Time]), [Field1], [Field2] FROM [TableName] GROUP BY [Field1], [Field2] So, the user can choose values for Field1 and Field2 or leave them at the default as 'Null'. so, chart displays OK for the following. SELECT SUM([Round Trip Time]), [Destination Node], ['Null'] FROM [TableName] GROUP BY [Destination Node], ['Null'] But the CHART DOES NOT SHOW for the following SELECT SUM([Round Trip Time]), [Destination Node], [Interface Name] FROM [TableName] GROUP BY [Destination Node], [Interface Name] However, the data displays ok for this, but the chart does not display. I created the chart as a subreport. When I run it with a value for Field2, it says No Data returned. IS there something wrong with my group on expression in my chart properties? or the query? Thanks.

Hi, Thank you for providing the example. From the example, you might specify the data field SUM([Round Trip Time]) to the chart data fields area, and specify the series group by the expression =iif(Fields!Field1.Value ="'Null'","",Fields!Field1.Value) & " " & iif(Fields!Field2.Value = "'Null'","",Fields!Field2.Value) In this way, we need to ensure the data fields Field1 and Field2 are always included in the dataset even though the query is dynamically. For example, if only select the value DestinationNode in the parameter Field1, the query should be SELECT SUM([Round Trip Time]) AS TRIPTIME, [Destination Node] AS Field1, NULL AS Field2 FROM [TableName] GROUP BY [Destination Node] If select the value DestinationNode in the parameter Field1 and select the value InterfaceName in the parameter Field2, the query should be SELECT SUM([Round Trip Time]) AS TRIPTIME, [Destination Node],[Interface Name] AS Field2 FROM [TableName] GROUP BY [Destination Node], [Interface Name] After that, the series group can be changed dynamically be parameters Thanks, Tony ChainTony Chain [MSFT] MSDN Community Support | Feedback to us Get or Request Code Sample from Microsoft Please remember to mark the replies as answers if they help and unmark them if they provide no help.