How to: Add, View, Modify, or Delete a Named Calculation to a Table Using Data Source View Designer (Analysis Services)
Both Microsoft SQL Server Analysis Services and SQL Server Integration Services projects can include data source views, and both support the ability to add named calculations to a table.
To add a named calculation to a table
In Business Intelligence Development Studio, open the project or connect to the database that contains the data source view in which you wish to define a named calculation.
In Solution Explorer, expand the Data Source Views folder, then open the data source view in Data Source View Designer by doing one of the following:
Double-click the data source view.
Right-click the data source view and click Open.
Click the data source view and then on the View menu, click Open.
Open the Create Named Calculation dialog box by doing one of the following:
Right-click the table in which you wish to define the named calculation in either the Tables or the Diagram pane, and then click New Named Calculation.
Click the table in which you wish to define the named calculation in either the Tables or the Diagram pane, and then click New Named Calculation on the Data Source View Designer toolbar.
Click the table in which you wish to define the named calculation in either the Tables or the Diagram pane, and then click New Named Calculation on the Data Source View menu.
Note
To locate a table or view, you can use the Find Table option by either clicking the Data Source View menu or right-clicking in an open area of the Tables or Diagram panes. For more information, see How to: View Objects in a Data Source View Using Data Source View Designer (Analysis Services).
In the Create Named Calculations dialog box, do the following:
In the Column name text box, type the name of the new column.
In the Description text box type a description for the new column.
In the Expression text box, type the expression that yields the content of the new column in the SQL dialect appropriate for the data provider.
Click OK.
The named calculation column appears as the last column in the data source view table. A calculator symbol indicates that the column contains a named calculation. To add additional named calculations, repeat steps 3 through 5.
Save the modified data source view by doing one of the following:
On the File menu, click Save Selected Items or Save All.
On the Data Source View Designer toolbar, click Save Selected Items or Save All.
Close the Data Source View Designer in Business Intelligence Development Studio and then click Save when prompted.
To edit or view a named calculation
In Business Intelligence Development Studio, open the project or connect to the database that contains the data source view in which you wish to edit or view a named calculation.
In Solution Explorer, expand the Data Source Views folder, then open the data source view in Data Source View Designer by doing one of the following:
Double-click the data source view.
Right-click the data source view and click Open.
Click the data source view and then on the View menu, click Open.
Open the table containing the named calculation in either the Diagram pane or the Tables pane.
Open the Edit Named Calculation dialog box by doing one of the following:
Double-click the named calculation that you wish to edit or view.
Right-click the named calculation that you wish to edit or view, and then click Edit Named Calculation.
Click the named calculation that you wish to edit or view, and then click Edit Named Calculation on the Data Source View menu.
Note
To locate a table or view, you can use the Find Table option by either clicking the Data Source View menu or right-clicking in an open area of the Tables or Diagram panes. For more information, see How to: View Objects in a Data Source View Using Data Source View Designer (Analysis Services).
In the Edit Named Calculations dialog box, do the following:
Update the Column name text box as appropriate.
Update the Description text box as appropriate.
Update the Expression text box with the expression that yields the content of the new column in the SQL dialect appropriate for the data provider.
Click OK or Cancel as appropriate.
Save the modified data source view by doing one of the following:
On the File menu, click Save Selected Items or Save All.
On the Data Source View Designer toolbar, click Save Selected Items or Save All.
Close the Data Source View Designer in Business Intelligence Development Studio and then click Save when prompted.
To delete a named calculation
In Business Intelligence Development Studio, open the project or connect to the database that contains the data source view in which you wish to delete a named calculation.
In Solution Explorer, expand the Data Source Views folder, then open the data source view in Data Source View Designer by doing one of the following:
Double-click the data source view.
Right-click the data source view and click Open.
Click the data source view and then on the View menu, click Open.
Open the Delete Objects dialog box by doing one of the following:
Right-click the named calculation that you wish to delete in either the Tables or the Diagram pane, and then click Delete Column.
Click the named calculation that you wish to delete in either the Tables or the Diagram pane, and then click Delete on the Data Source View Designer toolbar.
Note
To locate a table or view, you can use the Find Table option by either clicking the Data Source View menu or right-clicking in an open area of the Tables or Diagram panes. For more information, see How to: View Objects in a Data Source View Using Data Source View Designer (Analysis Services).
In the Delete Objects dialog box, review the objects that will be deleted and the objects that will no longer be valid and then click OK or Cancel as appropriate.
Save the modified data source view by doing one of the following:
On the File menu, click Save Selected Items or Save All.
On the Data Source View Designer toolbar, click Save Selected Items or Save All.
Close the Data Source View Designer in Business Intelligence Development Studio and then click Save when prompted.