Paginated Report Tutorial: Create an Archimedean Spiral with Scatter Line Charts

Today we will walk through how to create a scatter line chart in a Paginated Report using Power BI Report Builder. As the name would suggest, a scatter line chart is a scatter plot that includes a line connecting the data points. This type of chart is great option when you have data that has both X-Y coordinates and a relationship between points. Thanks again to Jason Thomas who first demonstrated how to build this type of chart in SSRS.

In this tutorial, we will create an Archimedean spiral in Power BI Report Builder as an example of a scatter line chart. An Archimedean spiral is characterized by the path traced out by a point moving away from a fixed location at a constant speed along a line that rotates with constant angular velocity. In polar coordinates, an Archimedean spiral can be defined by: r = a + bθ. If this sounds like a foreign language to you, not to worry. An understanding of the math is not necessary to follow along. However, for those interested in the math of the Archimedean spiral, this Wikipedia article provides more detail and derives the polar formula from the Cartesian form.

To get started, all you will need is Power BI Report Builder installed on your machine. If you don’t already have this installed, you can find the free download for Power BI Report Builder here.

Create a Dataset

1. In an Excel workbook, create a table for the inputs of the Archimedean spiral equation: r = a + bθ. Enter arbitrary values for a and b, pick a start and end point for θ (I chose 0 and 5π), and enter a value for the number of data points n (I chose 50).

   

2. Create a column for θ (theta) and use the formula shown in the below screen capture to derive its value for each data point. Drag this formula down so that you have n rows.

   

3. Create a category column and enter values from 1 to n. This will provide the order of each X-Y pair. I called this column Pos.
4. Create columns for the X and Y Cartesian coordinates. Use the following formulas to derive the Cartesian coordinates from the polar form:
   x = r * cos(θ) = (a + b(θ)) * cos(θ)
   y = r * sin(θ) = (a + b(θ)) * sin(θ)

   

   

5. Make sure you drag all for formulas down so you are left with n data points. Copy the data in the Pos, X, and Y columns.

Build the Visual

1. Open Power BI Report Builder and right click on Datasets in the Report Data pane to add a dataset.

   

2. Within Dataset Properties select New to add a data source. Within Data Source Properties select Enter Data form the dropdown and click OK.
3. Select Query Designer and paste in the three columns you copied from Excel. Make sure your column names (Pos, x, and y) are in the header row, not the first row of data.

   

4. Drag and drop a chart onto the report body, selecting a Smooth Line Chart with Markers.

   

5. From your dataset, drag y into Values and Pos into Category Groups on your chart.

   

6. Next, click on the arrow next to [Count(y)] select Expression. Change the expression to:

   =Fields!y.Value

7. Click on y in Values and select Series Properties. Add [x] as the Category field. This will locate the y values relative to their corresponding x values.

   

8. Right click Pos within Category Groups and select Category Group Properties. Within Sorting, delete the entry for [Pos]. This must be done since Pos contains numerical values. Click OK.

   

9. Adjust formatting of your chart as desired. I have changed the horizontal axis type to scalar, adjusted the range of the horizonal axis, and customized the colour of the data and background.

   

10. Click Run to view your scatter line chart of an Archimedean spiral.

    

Great job! For reference, you can download my Excel data here and my completed Paginated Report here.