INTRODUCTION TO REPORT VISUALS

Overview

Report Visuals are individual report types (e.g. bar, pie, line charts, table) which display your data in an easily consumable and interactive fashion. An App Dashboard can display one or more Report Visuals simultaneously.  

Each Report Visual has unique attributes and expected parameters. Experiment with them to see how they work.

To add one, on the left side of the screen go to +ADD -> REPORT VISUAL -> choose desired type

Tables

Use a Table to display detailed or grouped data in a paginated report. You can hide/show, reorder columns, add aggregations, and/or format rows and columns to highlight important data points.  There is also an option to hide ‘detail data’ and enable the user to interactively reveal said data via drilldown toggles.  

Adding a Table to display detailed data
Open ‘Setup Portal’, choose [+ADD -> Report Visual] then select the Table graphic (first option on top left)

[PROPERTIES] tab:

Name: Enter Name of Report (as displayed to users)

Dataset: Choose your existing Dataset from the dropdown list or [Add New Query] / [Add Stored Procedure], …then click NEXT

[PARAMETERS] tab:
If the query has parameters, see Report Parameters

[DESIGN/PREVIEW] tab:
Select: +COLUMNS FROM DATASET
Once columns are added, you can also reorder columns by dragging column header names

Tables – Adding Total Rows

Adding ‘Total Rows’ in Tables

In the Design tab on the right side go to the section called ‘Footer Items’ -> select ‘NEW’

All four fields are required and you can add one or more of these Total Rows in the same Table:
Choose [Type], enter desired column [Name], [Label Display] – this is the column under which the aggregate value will appear**, and [Columns]

**The word ‘Total’ or ‘Average’ cannot appear underneath its own column

Adding Custom Aggregations to a Table
Custom aggregations are used for calculated metrics. For example, Column1 ÷ Column2
The Custom Aggregation Columns sub-panel is found in the “Footer Items” section upon creating a new item.  Select a Column for the custom aggregation logic and enter a formula in the following format using available aggregations: FieldName.aggregation

Adding New Columns to an Existing Table
Within the [DESIGN/PREVIEW] tab, Click [+MORE COLUMNS] (if all columns are already selected the ‘+MORE COLUMNS’ will not appear)

Use the second to last tab [ASSIGN TO DASHBOARDS] to assign each Report Visual to an existing App Dashboard(s). If you do not have an App Dashboard you must create one. (see App Dashboards in the Dashboards section)

Table Styles & Conditional Formatting

RockDaisy Tables are fully enabled for conditional formatting and changing column headers and data styles.

Conditional Formatting

There are two methodologies for conditional formatting:

  • Pre-defined options, e.g. color scales, data bars.
  • Query Logic

Open the Setup Portal and click [Edit Report] -> choose the column you want to conditionally format
Note: the column type must be defined as ‘Number’

In the Conditional Formatting dropdown choose one of the pre-defined options.

Query Logic:

Or, at the bottom of that list choose [Custom] and select the column in your query to return the [Code Template] (the info icon will display valid codes)

Here is a sample SQL query which populates the [Code Template] field.  Add such a command within your query/standard procedure.

Select col1, col2,… case when sum(TotalSales) > 10000 Then 'greenbg' else 'redbg' END AS [Sales Format]
FROM Products

Modifying Column Styles

Bolding column headers in a Table:
Click on desired Column header -> Column Header Style -> Modify the “Custom CSS Style” field in the “Design” panel (right side). font-weight: bold;

Changing background colors of the column headers:
Click on desired Column header -> Modify the “Custom CSS Style” field in the “Design” panel.
background-color: rgb(255,0,0);

Modify Table Data Style

Bolding cells in a Table:
Return HTML tags from your query

select    C.Firstname, '<b>' + P.ProductName + '</b>' as ProductName …….

Set the [Custom Display] field to “HTML” and [HTML Template Field] to the desired column.
This instructs RockDaisy to render that column using the HTML tags (as opposed to text)

Report Styles

Adding borders to columns:

Use CSS to make columns visually unique. You’ll find these options within the ‘Column Header Style’ section, there you can add custom CSS inside the “Custom CSS Style” field for both group and column headers.

For example, create distinguishing columns by using differing border lines and/or alternating colors

Style example for column group header and column header styles:

background-color: rgba(255,255,255,0.1);

Extending the border to the data cells:

border-left: 1px solid grey;

Dynamic Control Headers

Dynamic headers allow you to programmatically change the control header using SQL. By default, each control header will match the name entered in the [NAME] text box.

The Dynamic Report Visual Title field can take a column from your query. This will override the [NAME] field mentioned above. For example, your query may have a column called EventName. Based on the query results you may want to have the event name displayed as the control header (as opposed to a static header).

Bar Chart

A Bar Chart displays any given ‘Series’ as a set of horizontal bars (similar to a Column Chart which displays Series as a set of vertical bars or a Bullet Chart which displays horizontal bars with varying beginning and end points).

To add, Choose Add -> Report Visual -> Bar Chart -> select Dataset -> then select from the dropdowns in Chart Settings

Parameters

Upon choosing the correct Filter Variable(s) (see Report Variables) and choosing a Design Preview Value you’ll activate the Design/Preview tab to display a preview of your chart.

Design Options

Change Bar Color

RockDaisy automatically picks colors for you. Alternatively, you can apply logic from your query/stored procedure to change the color of your bars. In this example, bar color is based on the Month Number. In the [Series Color] dropdown choose the column for your query that’s returning the color (in this example it’s called BarColor)

SELECT
  CASE
        when Month(OrderDate) between 1 AND 4  then '#ffffff' 
        when Month(OrderDate) between 5 AND 8  then 'yellow' 
        when Month(OrderDate) between 9 AND 12 then 'red' 
  END 'BarColor'
FROM Orders

Within the Design/Preview tab you can further configure the chart inside the ‘Settings’ and ‘Design’ panels

Within the Design panel, the Value Axis & Group Axis determine the appearance of the x and y axes

Make edits directly from a Report Visual within an App Dashboard by right-clicking on the chart itself; three editing options will appear:
1. Edit Report Visual – opens the Design/Preview tab (access to Settings and Design panel)
2. Copy Report Visual – creates a copy of the report to ‘Save As’ with different specifications
3. Manage Tabs – opens to the [+REPORT VISUALS TO GROUP] tab

Bullet Chart

The Bullet Chart  is a variation of the Bar Chart. Generally, it is used to display progress toward a certain goal.

 CompanyName

TotalSalesCurrentYear

TotalSalesPrevYear

 Save-a-lot Markets

42,806

62,659

 Ernst Handel

42,599

56,303

 QUICK-Stop

40,527

65,762

Change Quota line color in the Quote Line Options section:

**Don’t forget to assign each new report to its requisite Dashboard(s)**

Stacked Column Chart

Stacked Column Chart charts are useful in showing total(s) and their proportions.

To add, Choose ADD -> Report Visual -> Stacked Column Chart -> select your Dataset (in this example the SQL stored procedure dataset is called dbo.cat_Stackedbar)

Review sample dataset result set

Dataset requirements:

  • You need to create a placeholder for each record in your Series and Category (possibly with a cross join?)

Notice each CustomerID has a record in each month, even if they have zero orders.

CustomerID

MonthNumber

MonthName

NumberOfOrders

MetricReplacement

CategoryColorInStack

ALFKI

1

Jan

6

6

red

ANATR

1

Jan

0

 

yellow

ANTON

1

Jan

7

7

green

AROUT

1

Jan

0

 

orange

BERGS

1

Jan

36

36

blue

This chart leverages the Metric Replacement Display property (bottom right of below image). Note, the above result returns blank ‘’ if there were zero sales. RockDaisy will always override the default labels with Metric Replacement Display. In this case, the chart shows blank ‘’ instead of zeros.

As depicted below, in the month of Jan, despite both ANATR (yellow) and AROUT (orange) having values (albeit zero) the Metric Replacement Display function converts them into blanks and therefore correctly leaves them out of the chart.

Adjust label position using the Metric properties

MAP Chart

The Map Chart plots points and/or shades regions based on geographical data returned from a dataset. Change the [Layer] dropdown to view different map types (e.g. plot data based on zip codes, city)

Here is a sample Map dataset/query. In your query you may want to dynamically return specific colors based on a condition.  This example does not dynamically return colors rather only hard-codes them.

SELECT 500 as Metric, 90005 as ZipCode, 'NV' as StateCode, 'rgba(0,128,0,.7)' as Color
UNION ALL
SELECT 750 as Metric, 90001 as ZipCode, 'NM' as StateCode, 'rgba(255,165,0,.7)' as Color
UNION ALL
SELECT 1000 as Metric, 90209 as ZipCode, 'CA' as StateCode, 'rgba(128,0,128,.7)' as Color

Modify the Zoom options in the Design Panel to default the map display settings in your reports.

Matrix Report

Use a Matrix to display grouped data and summary information. Matrices provide functionality similar to crosstabs and pivot tables. Use a Matrix report when your column headers are “dynamic”, meaning they will be calculated at run time. The report data and data regions are combined at run time, a matrix grows horizontally and vertically on the page. Values in matrix cells display aggregate values scoped to the intersection of the row and column groups to which the cell belongs. You can format the rows and columns to highlight the data you want to emphasize.

To add, Choose ADD -> Report Visual -> Matrix (last icon on the third row) -> select Dataset -> then choose from dropdowns in Chart Settings

Sample Dataset

CustomerID

OrderYear

TotalOrders

ANATR

1996

1

ANTON

1997

1

AROUT

1997

2

You can change the column header of your primary (first) column in the [Design Panel] on the right side

Please Note: Data Writeback settings slightly differ for the Matrix report in that the Dynamic Column Type editor settings are defined in the Writeback tab itself.

Multi-Axis/Series Chart

Multi-Axis/Series charts allow you to plot data for multiple metrics. For example, you can plot the total order for each customer as compared to the average number of orders for all customers

To add, Choose ADD -> Report Visual -> Multi-Axis/Series Chart (sixth icon in the first row) -> select Dataset -> then choose from dropdowns in Chart Settings

Sample Dataset

CustomerID

TotalOrders

AVGOrdersForAllCustomers

ALFKI

6

9

ANATR

4

9

ANTON

7

9

AROUT

13

9

BERGS

18

9

Use the [NEW] button in the [Design Panel] to create your Series

HTML Output Control

HTML control will render any html passed to it as html.

To add, Choose ADD -> Report Visual -> HTML Output -> select your Dataset

In this example a query is used to generate dynamic HTML output

Declare @MaxFreight float

Select @MaxFreight = max(Freight) from Orders
Select 'ShippingCost' = '<p style="font-size: 20px; color: purple; padding-left: 20px;">Shipping Cost Summary:</p><p style="font-size: 20px; color: blue; padding-left: 20px;"><b>'+UPPER(CAST(CustomerID as varchar)) + '</b> has the highest shipping cost at <b>' +UPPER(CAST(max(Freight) as varchar)) + '</b></p>'
From Orders
where Freight = @MaxFreight
group by CustomerID

SQL Result dataset:

<p style="font-size: 20px; color: purple; padding-left: 20px;">Shipping Cost Summary:</p><p style="font-size: 20px; color: blue; padding-left: 20px;"><b>QUICK</b> has the highest shipping cost at <b>1007.64</b></p>

Calendar Control

Calendar Control is used to display one or more events on specific days.

To add, Choose ADD -> Report Visual -> Calendar -> select your Dataset

Sample Dataset

EventName

DefaultSelectedDate

EventDate

CondCode

Color

GameKey

@ NE 1PM

2019-08-26

9/22/2019

greenfont

white

1

BUF 1PM

2019-08-26

9/8/2019

whitefont

green

2

CLE 8PM

2019-08-26

9/16/2019

whitefont

green

3


Date Interactions

Used to display additional information about the specific day clicked.

Date Interactions as an On-Screen Filter.  On-Screen Filter details can be found in the [FILTERS] section of the document. In summary on-screen filters allow the user to interact with a control. This filter control will filter datasets in the other controls on screen. For example click on calendar day and have events display for that specific day in a table control on the dashboard.

Date Item Interactions

Used to display additional information about the specific event clicked.

In sample resultset (above) GameKey is returned to the calendar.

A Date Item Interaction is defined. In this example GameKey is passed to an existing table report called

[Calendar Drilldown] to display additional event details.

Tile Control

Tile control is used to display a single metric

To add, Choose ADD -> Report Visual -> Tile -> select your Dataset

Sample Dataset Query

Select
1000 AS 'TotalSales',
case when 1000 > 0 Then 'whitefont' else 'bluefont' END 'TileFontColor',
case when 1000 > 0 Then 'green' else 'red' END 'TileBgColor',
'Total Sales' AS TileLabel

Dynamically set all the properties from your query (see below) or use the Design Panel to select colors, fonts, etc.

KPI Card Control

KPI Card Control evaluates the current value and status of a metric against a defined target. A KPI visual requires a base measure which evaluates to a value, a target measure or value, and a threshold or goal.

To add, Choose ADD -> Report Visual -> KPI Card-> select your Dataset

There are a number of Properties you can dynamically set.

Sample Resultset

TEAMLOGO

INTGAMENUM

GROSS_REV

PLANS_REV

CALLED_ATT

OuterBackgroundColor

InnerBackgroundColor

url to logo

40

yellowcircle

yellowcircle

yellowcircle

rgb(191,143,0)

black

Note: You can change outer card color by passing color back from the query. You can also have tooltips returns from the query.

Use the Design Panel to add labels. Change the icon / color displayed in the card by passing back the code template code from your SQL query / procedure

Use the Interactions section in Design Panel to add a drilldown. (see Report Interactions for details)

Report Tabs

Include multiple reports within a single report view by utilizing the toggle feature of Tab.

Go to any existing report in the Setup Portal. Choose [REPORT VISUALS GROUP] tab. Enter the width of the tab. Click the [ADD] button to add report

***If your report has Filters and you are ready to view your reports in action, you must first add Filters, jump here to see more on Filters.***