BarChart Setup and Usage

This topic covers the fundamental aspects of creating a write-back capable report using the BarChart visual.

Configure BarChart for Write-back Reports

This section describes the basic configuration options for the BarChart visual.

Validate the Write-back Service

After installation, it is essential to verify that the write-back service is functioning correctly.

Navigate to your deployed URL, which follows this format: http(s)://yourserverName/PPWebService/PPWebService.svc and ensure that the following page displays.

Set up the Basics

1. Launch Power BI Desktop and connect to your data source.
2. Import the visuals (VPService and BarChart) into your report.
3. Configure the VPService visual so that it knows where to find the write-back service and which connection to use during the process. See BarChart Prerequisites for more information.
4. Drop the first column into the BarChart visual to view the configuration options in the Format section of the Visualizations panel.
5. To set up a working report element for the write-back functionality, configure the WebService, Connection, and Type properties. See Configure the Connectivity with the Write-back Service for more information.



6. The correct usage of the BarChart includes:
   • Adding measures to the Measure Data section. A measure is a quantitative or numeric field.
   • Adding dimensions to the Category Data and Series Data sections. Dimensions are categorical or text data.
   • Adding multiple measures or adding one measure with one series dimension.

Publish and Test the Report

Follow these steps after you finish creating your report:

1. Publish your report to either your on-premises Power BI Report server or to PowerBI.com. The write-back functionality works reliably only when your report is published. You cannot fully test it within Power BI Desktop during the authoring phase.
2. Modify a cell value and click the Save Changes button to test the write-back functionality. Your report is now updated.

Configure the Write-back Service Connection

Configure the following options to successfully connect to the write-back service:

• WebService - Specify the write-back service URL. This URL should point to the hosting machine or app service where the write-back service is installed. The typical URL format is: http(s)://WEBSERVICE_COMPUTER_NAME/PPWebservice/PPWebservice.svc. Ensure that there is no extra slash at the end of the URL. If the web service URL is missing or incorrect, an error message will appear during the first write-back operation.





• Type - Select the connection type for the write-back service:
  • SQL - Set the connection string directly in the Connection field. If the default SQLConnection is set in the web.config of the write-back service, leave the connection blank.
  • SQL Datasource - Set the connection name defined in the PPWebService web.config file. For SQL connections to work without an SSAS semantic model, ensure that the relationships defined in your Power BI report exist in your database with foreign key constraints, and only simple measures are used.
  • SSAS - Set the connection string directly in the Connection field. If the default SSASConnection is set in the web.config of the write-back service, leave the connection blank.
  • SSAS Datasource - Set the connection name defined in the web.config of the webservice.
• Connection - Specify the name of the SQL or SSAS connection defined in the web.config file of the webservice, or set the connection string directly, or leave it blank based on the selected connection type. The maximum length of this property is 250 characters. If the connection is missing, an error message will appear during the first write-back operation.

General

Version

It provides the version number of the current visual.

Data Entry (Settings)

Configure the following options to define the behavior of the BarChart visual:

• Windows Authentication
  • If you are using Power BI Report Server in an on-premises environment and have Windows Authentication enabled in IIS for the write-back service, turn on this setting. This allows Windows credentials to be passed back to the underlying data source.
  • If your reports are published to the PowerBI.com service in the cloud and you are using Azure AD, turn off this setting. This ensures that AD credentials are used when accessing the data source.
  • When using a gateway, turning on this setting will have the following effects - Instead of setting PowerBI.com credentials in the write-back service request, the visual posts the Windows login context (e.g., domain\user instead of username@domain.com). If you use USERNAME() in computed or default value columns and SQL context variables, this value will be set. It also enables impersonation and is necessary if Windows Authentication is set in IIS for the write-back service.
• DirectQuery - This is a legacy option. The type of the SSAS model (if present) is determined automatically and will be removed in future releases.
• Customer - If you have already specified the customer name along with the License Server license key in the web.config file (which is typically done during setup), leave this field blank. However, if you are sharing one webservice among multiple license keys, specify the customer key you want to use.
• Domain - Set it to one of the following options. If it is configured in the write-back service web.config file during setup, leave this field blank.

  • Set the fixed internal domain name (e.g., DOMAIN).

  • Auto - Use the domain part (part after @) of the powerbi.com user.

  • Auto-short - Use the short domain part (part after @ and before .) of the powerbi.com user.

  • Azure: Use the full powerbi.com username (e.g., user@domain.com) for Azure Active Directory authentication.

  • Leave empty for no domain (e.g., for a SQL authentication user).

  • SQL=… and SSAS=… - Set domain options separately for SQL and SSAS.

  • Set up custom user mapping of powerbi.com users to SQL and SSAS users in the UserMapping table in the SQL database. This table should contain User, SQLUser, and SSASUser varchar columns. See Custom User Mapping When Using PowerBI.com Service for more information.

• Initial Refresh - This setting affects how data is retrieved when the visual is first rendered on the report page.

  • Turned off - The matrix will use the PowerBI cache (if available) to retrieve data.

  • Turned on - The matrix will reach out to the SSAS model to fetch the most up-to-date data. PowerBI caches cell data for faster report rendering. It is useful when you have multiple report pages and navigate between them. By default, the BarChart has this setting enabled, ensuring that it always connects to the underlying SSAS model for data retrieval and does not use the cache. This is important for concurrent workloads; if you have made changes and someone else has too, navigating away and back ensures you see the most recent committed changes.

Commenting

Turning ON the commenting property allows the users to add comments to each data point.

Double clicking on a point brings up a small window where you can edit the value and the corresponding comment. To save the data and comment, press the [Save changes] button.

Hover on a data point the comment will be shown in the tooltip.

To refresh the comments on the visual press the [Reload Data] button on the top ribbon of the visual.

The commenting feature of Barchart can work together with a DEMx visual. Comments written on the Barchart will be visible on the same dimension total members on DEMx and vice versa. (Saved in the same comments table, with the same JSON structure)

Color Settings

Chart style is set to Line Chart:

Every measure on the chart will be appearing under the [Color settings] and you can change their line colors with the regarding Color picker. If you have only one measure it will change only the color of the line but if there are multiple ones it effects the data points as well. When you have only one measure on the chart you can change the color of the data points by the values of Category Data (X axis).

Chart style is set to Bar Chart:

You can set the color of each bar by the values of Category Data or by measures if you have multiple ones. When you have only one measure and changing its color, it will affect only the color of the legend.

Chart Options

Dragging Precision

Only available when the Percent View property is turned OFF. With this property, you can change the accuracy of moving a data point. Recommended to set it manually based on the scale of your Y axis.

Pay attention on the value that you give as a Dragging Precision, because moving the data points, the value of the measure can be the multiple of the Dragging Precision only. It is not a step size.

E.g.: you have a data point where the value is 1.75 and this property is set to 3. When you try to change the value 1.75, it can jump only to 0, 3, 6, 9... etc. You will not get values like 4.75, 7.45 etc. (if you want to get these values you should apply a smaller Dragging Precision)

Percent Dragging Precision

Only available when the Percent View property is turned ON. With this property, you can change the accuracy of moving a data point. The value of this field is in percentage, e.g.: 10 equals to 10%, 0.1 equals to 0.1%.

In this case it is also not a step size, works like the normal Dragging Precision.

Chart Style

You can switch the style of the chart between Line chart and Bar chart views.

Line chart:

You can drag the data points and change their values by moving them.

Bar chart:

You can drag the small black rectangle at the end of each bar to change the values.

Legend Position

You can change the position of the displayed measures’ legends.

Top

Bottom

None

Percent View

It is turned OFF (by default):

The chart is in the normal view and all data points show the exact value of the current context. By dragging a data point you can set a new value based on the setup Dragging precision.

Turned ON:

The data points will show the percentage ratio of the total value. Modifying one data point will affect the other ones as well by keeping the total value of the measure (e.g.: you have two data points, and their distribution is 50-50%. When you change one of them to 40% the other one will be changed to 60% automatically.).

When the BarChart is in Percent view, the data points can be locked. Locked points will not be affected by changing other points on the chart. To lock a data point you can use CTRL + Left click combination on it or open the context menu and check the Lock box. (Locked points’ color become darker.)

Lock function works only if Percent View is enabled.

Transparent Background

Turning it ON makes the chart area transparent so, its color will match with the background color of the visual.

OFF

ON

Line Styles

Only available when the Chart style is set to Line Chart. You can change the line style of any measure or series element by selecting one from the dropdown menu.

Importance of SmartFilter Helper Visual

It is a general functionality in PowerBI that when you are using slicers, page-report or visual level filters the visual itself is not aware that it is receiving a filtered dataset. For the write-back to work properly if you are using any of these filtering capable elements, you need place SmartFilter helper visuals in your report, so that the BarChart will be aware of these, and the write-back engine will take these into consideration when it composes the SQL statements. Without it, slicer selection will be left out of the tuple that is being sent back to the service, it will be missing from the SQL statement, hence the saved result will be wrong. It is important that you use the visual that is shipped with the setup kit, as you can find a visual with the same name in the Microsoft Visual Store but that does not have this functionality.

This visual is invisible to the end users as it has no background no borders, and the values are not shown.

General rules:

• If both the field that is used in a slicer and a field used in your BarChart category or series data collection are originated from the same dimension (table), you do not need to use SmartFilter – as the field in the category or series data is deterministic.
• If the field of the slicer can be found in BarChart you do not need to use SmartFilter.
• If you use a field that is not used in your BarChart and originated from a different dimension, put a SmartFilter in your report with the same field. Example: you have a slicer on Customer Group Name, but the customer table is not used at all in the BarChart, add a SmartFilter helper visual to your report and place the same field in it – in this case the Customer Group Name
• If you have more slicers which fields are originated from the same table, put a SmartFilter in your report and use the lowest granularity field in it. Example: you have slicers on Size and Brand which are both columns of the Product table, add a SmartFilter helper to your report and put the ProductId in it.

Performance Optimization

In case of large fact tables reprocessing in-memory SSAS models can take some time. The write-back service can determine which portion of your data should be processed. For that you can implement special partitioning. Please refer to the following article in our Knowledge Base for examples, walk throughs and tips:

• https://support.poweronbi.com/portal/kb/articles/performance-tips-for-direct-query-mode
• https://support.poweronbi.com/portal/kb/articles/performance-optimization-tips
• https://support.poweronbi.com/portal/kb/articles/implement-ssas-partitioning-for-improved-write-back-performance
• https://support.poweronbi.com/portal/kb/articles/dedicated-table-for-write-back-using-ssas-partitioning

Models with Calculated Tables and Columns

As described in the Limitations chapter, you cannot write-back on calculated tables and columns. You can have these types of objects inside your SSAS model, but by default the write-back service always do a model metadata check before saving changes, and if it finds such structures will prohibit the process. If you are certain that you are not trying to write-back on measures that are using calculated columns, and you are not using calculated tables in your chart (neither on category nor series), also these objects are not used in any kind of filters (slicers, visual level filters, etc.), you can turn off this strict checking by using Advanced Configurations.

Advanced Configuration

You can control the behavior of the write-back service for each SSAS model. For this you need to create a dedicated SQL table in a strict format. It is important to know, that if you have multiple data sources in your SSAS model, you need to tell the write-back engine that in which data source should it look for this setting table. The way to do it is to add the write-back – case sensitive string in the name of your data source where you will be creating this helper table. So, for instance if you have three data sources defined  (ds_A, ds_B and ds_C) for three different SQL databases (Database_1, Database_2, Database_3) and you want to create this settings table in Database_2 – which is used in ds_B, then rename it to be ds_B_WriteBack for instance.

The structure of the table is:

CREATE TABLE [dbo].[AdvancedSettings]

(

    [Setting] [varchar](255) NOT NULL,

    [Value] [sql_variant] NULL,

CONSTRAINT [pk_AdvancedSettings] PRIMARY KEY CLUSTERED( [Setting] ASC )

)

You cannot rename the table or use other schema then dbo at the moment. Be aware, that the [Value] column is an sql_variant datatype, so different settings will have different types (like bit, varchar, numeric, etc.) It means that when you try to copy this table by the Generate Script tasks in SQL Management Studio it will not recognize the correct data types and might result in a non-compatible setting. If you need to move/copy this table – or the entire database – always re-create this helper table with the appropriate data types by using the correct insert statements.

Below you find the most important settings, for the complete list please refer to this article:

https://support.poweronbi.com/portal/kb/articles/advanced-configuration

Important settings:

1. Turn strict metadata OFF checking in case of you have calculated tables, relationships by default the service will prohibit the operation. If you turn this OFF, the operation will not be blocked. Be careful that you will not use unsupported objects during the write-back (for example you do not use calculated column as a row / column member in your matrix or as slicers, etc.)

2. Enable tracing: if you enable this a Trace.Txt file be created in the folder at the location of the webservice which will log all operations during write-back.

3. Save write-back history: if you enable this, a dbo.WriteBackHistory table will be created in your database (in case of multiple data sources, it will use the connection with the WriteBack tag) that will contain the following information for each cell modification:
   1. ChangeDate – time of change
   2. ChangedBy – username who committed the change
   3. Tuple – the cell’s tuple or coordinate
   4. OldValue – original value of the cell
   5. NewValue the updated value of the cell

For point 1:

INSERT [dbo].[AdvancedSettings] ([Setting], [Value]) VALUES (N'SkipInvalidMeasures', CAST(1 as bit)) --Don't throw error on non-parsable DAX expressions

INSERT [dbo].[AdvancedSettings] ([Setting], [Value]) VALUES (N'SkipCalculatedRelationships', CAST(1 as bit)) --Don't throw error if calculated column used in relation

For point 2:

INSERT [dbo].[AdvancedSettings] ([Setting], [Value]) VALUES (N'EnableTracing', CAST(1 as bit)) -- creates Trace.txt inside the web service's folder and log all operations

For point 3:

INSERT [dbo].[AdvancedSettings] ([Setting], [Value]) VALUES (N'SaveWritebackHistory', CAST(1 as bit)) -- creates dbo.WritebackHistory table and logs cell changes