Uniquely name your column aliases

Mike — Mon, 24 Nov 2008 13:33:37 GMT

Adding a dataset to your report model may use a SQL query from an external database. When selecting the ellipis (...) on the Query parameter a list of stored procedures is displayed from the database pointed to by the configured datasource property. If this query does not contain unique names amongst all columns then an error "Property value is not valid" with the detailed message "An item with the same key has already been added". I think this is due to the Dataset object cannot be created using identically named columns (DatasetFieldDefinition).

1. With datasource configured, create your dataset.

2. Select stored procedure as the Query for the Dataset

3. Error message

4. SQL query causing problems

ALTER PROCEDURE [dbo].[mikeGetData]
    -- Add the parameters for the stored procedure here
    @WhichCategory int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT cat.[Name] as Category, subcat.[Name] as Category

        FROM [Production].[ProductCategory] cat inner join [Production].[ProductSubcategory] subcat
            on cat.[ProductCategoryID] = subcat.[ProductCategoryID]

        WHERE cat.[ProductCategoryID] = @WhichCategory

END

I cannot remember everything - Dynamics Ax Reporting

Uniquely name your column aliases