DASHBOARD COMPONENTS

Dashboards are composed of many elements, as follows:

  • DB Connections and Datasets

DB Connections and their requisite Dataset(s) supply Report Visuals with their data; a single Dataset can provide the data for multiple Report Visuals

  • Report Visuals

A report type, e.g. bar, pie, line, table for displaying data

  • Dashboard Groups (main folders)

Unique “Buckets” for grouping App Dashboards & Report Visuals by subject matter

  • App Dashboards (subfolders)

The layout choices for how Report Visuals appear within each view and the “Buckets” for Report Visuals within each Dashboard Group

  • Filters

Filter the data displayed in Report Visuals, e.g. numeric range, multi-select listbox, calendar dates; specific Filter choices are populated by writing basic queries

DB CONNECTION & DATASETS

For your data to appear in a report you must set up a DB Connection pointing to your dataset(s).
Once a database connection is created in RockDaisy it is automatically available throughout the application and usable by multiple reports and datasets.

From the setup portal choose Add [DATASETS] -> [DB Connection] and enter the connection information.

Supported data source types include:

  • MS SQL SERVER
  • MYSQL
  • ORACLE
  • POSTGRES
  • AMAZON REDSHIFT
  • GOOGLE BIG QUERY
  • EXCEL / CSV (via RockDaisy import wizard connecting to MS Excel data).

REPORT VISUALS

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 tells 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 Range Bar 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

By choosing the correct Filter Variable(s) (Parameter) and filling in the Design Preview Value, you’ll activate the Design/Preview tab to display a preview of your chart.

Design Options

Change Bar Color

RockDaisy will automatically pick 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

To make edits directly from a Report Visual within an App Dashboard, right-click 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 of below image, right side). 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 0.

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)

Sample Map dataset/query. In your query you may want to dynamically return specific colors based on a condition.
This example is not doing that and simply hardcoding it.

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 to display correctly 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

Multi Axis/Series Chart

Multi Axes/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 Axes/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 additonal information about the specific day clicked.

Date Interactions as as 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 additonal 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 show more details about the event.

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 drill down. (see Report Drilldown section 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.***

DATASOURCES

Supported Datasources

RockDaisy Connects to the Most Popular Databases

  • MS SQL server
  • MySQL
  • Oracle
  • Amazon Redshift
  • Postgres
  • Google Big Query
  • Excel (use Rockdaisy import wizard to connect to MS Excel data)

Add graphic form home page?

DASHBOARDS

Dashboards are containers for the Report Visuals created above. There are many different styles of Dashboard containers. In order to view any Report Visuals (bar, line, pie, table, etc.), you must add them to an App Dashboard.

App Dashboards

Each App Dashboard option dictates the visual layout of Report Visuals. In every view, all App Dashboards display either a single (e.g. Table) or multiple Report Visuals (e.g. Table and Bar Chart).

The most frequently used App Dashboards are as follows:

APP DASHBOARD

Format

Example

Standard Layout

Multiple Report Visuals

Displays a Table and Pie Chart

Single Report

Single Report Visual

One full screen data Table

ADD an App Dashboard

Choose +ADD then [APP DASHBOARD] -> Select the desired layout option

After choosing an App Dashboard, the below five tabs will appear:

(The first two tabs, [REPORT VISUALS] and [FILTERS], are relevant in other steps, as referenced above in Report Visuals and below in Filters.)

Assigning an App Dashboard to a Dashboard Group
Select the third tab – DASHBOARD GROUPS.  By default, as explained below, there will be a Dashboard Group already listed called ‘ALL DASHBOARDS’.  Each App Dashboard by default is added to the [ALL DASHBOARDS] group.

From the left-side menu choose [EDIT] -> [APP DASHBOARDS] -> Choose APP DASHBOARD -> [DASHBOARD GROUPS] tab -> click the ‘ADD’ button and select from the list. You must already have created Dashboard Groups to see them in list (see below on adding Dashboard Groups)

Dashboard Groups

Dashboard Groups gather related App Dashboards into one group, functioning as main folders.

Adding a Dashboard Group
Open the Setup Portal (found in the top right corner cubes dropdown button)

On the left side select [+ADD] then GROUP OPTION -> [DASHBOARD GROUP]

Name – Displayed internally

Title – Displayed to the end user

Image – The icon appearing in the Dashboard homepage

Click ‘SAVE’ in the top left corner when done.

Any one Dashboard Group can house multiple App Dashboards which contain various Report Visuals (as explained below), for example:

Dashboard Group

App Dashboard

Report Visual(s)

Product Sales

Single Report

Top 10 Product Sales (Table)

Product Sales

Standard Layout

Bottom 10 Product Sales (Table, Bar, Pie Chart)

One Dashboard Group can have multiple App Dashboards. App Dashboards contain one more report visuals.

Slideshow Mode

RockDaisy Slideshow is a collection of Dashboards arranged in sequence. Unlike a PowerPoint presentation, RockDaisy Slideshow is fully interactive and ideal for presenting to a larger group.  This means a presenter can interact with the reports by clicking, drilling down, and filtering.

Any user can create a slideshow by clicking the [Slideshow] button and then choosing from existing reports. These reports will always display the most recently updated information, as they connect back to the database on refresh.

Slideshows can be clicked thru manually or configured to auto-play.

To create a Slideshow:

  1. Navigate to any Dashboard and click the Slideshow icon on top right side of screen
  2. Add any number of reports to your Slideshow

REPORT DRILLDOWN

Chart Drilldown

Most charts in RockDaisy feature drilldown functionality, enabling the user to interactively see more detailed data. For example, users can click on a specific bar in a bar chart or a slice in a pie chart.

** Only a single parameter can be passed from a parent report to a child drilldown report. Use the SQL fnsplit() function to parse your single parameter into multiple parameters. In example below, DrillDownParameter (SQL column) is passed to the child report. The child report SQL stored procedure splits the month number and customerid which are separated by this pipe symbol |

Sample Dataset (drilldown parameter is monthnumber and customerid separated by pipe symbol)

OrderMonth

TotalItemsOrdered

OrderMonthNumber

DrillDownParameter

Jan

17

1

1|ALFKI

Mar

18

3

3|ALFKI

Apr

60

4

4|ALFKI

Parent Report

Parent Report

Within the [Interactions] section of the [Design Panel]: (see above)

  • Choose an existing report visual that you created.
  • Choose a column from the parent data set that will be used to pass to the child drilldown report

In this example when a bar is selected, the @DrillDownParameter will be passed to an existing report visual called: Table Drill Down

Table Drilldown

Data is displayable in many formats, both as a high-level summary and/or with its accompanying details. Table Report Drilldown allows users to view details from any existing Report Visual. The details are displayed within a pop-up window or an entirely new dashboard.

There are three options for displaying the data within this Table drilldown functionality:

  • Pop-up window
  • New dashboard in same window (refresh page)
  • New dashboard in new window

Three components are necessary to create this Table drilldown experience:

  • Main report
  • URL report
  • Drilldown report

 

Report Type

DESCRIPTION

EXAMPLE

Main Report

The user’s existing Report Visual

Table, Pie Chart, Bar Chart

URL Link Report

Links the Main Report and Drilldown Report

When clicking on a row in the Main report the user will be shown a related report (the Drilldown Report) within an existing App Dashboard with the according details of data.

The below screenshot shows the passing of the @Productname parameter in order to enable the Main Report to retrieve data associated with the Drilldown Report’s identical parameter (in this example to retrieve details of the Product).

Sample URL Report Syntax:

SELECT ‘https://customer.rockdaisy.com/view/4/@ProductName=’ + cast(@ProductName as varchar(max)) + ‘//FullScreenMode=True’ as Url

Drilldown Report

Resulting Drilldown report

Dashboard

The [URL Link Report] visual is a redirect report used to connect the Main Report (parent) and its associated Drilldown Report (child). The [URL Link Report] takes a single URL parameter (type URL) and redirects the user to a preexisting saved view (i.e. an App Dashboard containing Report Visual(s)) in RockDaisy

To get the URL of an existing saved view. Navigate directly to that saved view and copy its URL, as shown here:

Here is an example of a URL parameter used in a [URL Link Report] (see screenshot below):

SELECT 'https://customer.rockdaisy.com/view/4/@ProductName=' + cast(@ProductName as varchar(max)) + '//FullScreenMode=True' as Url

In this example, when the [URL Link Report] is executed it will redirect to an existing report – https://customer.rockdaisy.com/view/4/ and pass the @ProductName parameter.

The @ProductName parameter is passed to this [URL Link Report] from an existing page Main Report (parent) where the user performed a drilldown (see the next section, Table Report Drilldown Types).

Table Report Row Drilldown

Row Drilldown

Click on a row in a [Table Report] to drilldown to details

Existing [Main Report] (parent), in this case a Table report, with a Row Drilldown Interaction defined pointing to the desired -> [URL Link Report] (passing the @ProductName Filter Variable as chosen within the Interactions screenshot on left side)

[URL Link Report] (screenshot on right side) redirects to ->

SELECT 'https://customer.rockdaisy.com/view/4/@ProductName=' + cast(@ProductName as varchar(max)) + '//FullScreenMode=True' as Url   (see Embed Dashboards Section for additional URL switches)

Note: https://customer.rockdaisy.com/view/4 is an existing saved view (as described above)

Note: Parameters are assigned via Filters. For example, the below [Table Report] has @ProductName defined, therefore it can be passed upon clicking it for the row selected.

Table Report Cell Drilldown

Expand the [INTERACTIONS] component in the design panel for an existing Table report

Select Cell Drilldown from the [Interaction Type] dropdown listChoose the report you want to drilldown to [Drilldown to Report Visual]

Choose the [Filter Variable] that you will pass to the child report. This assumes the child report/dashboard is using this Filter Variable to retrieve information. A CustomerID or ProductID is a common variable to pass between them.

FILTERS

Filters function by ‘slicing down’ the data returned to a given report view. Any combination of filters is usable within your dataset query.  In general, filter options are generated via the queries you write.

To add a Filter , Choose ADD -> Filter -> [Choose the desired filter type]

Filter Variables

Before adding Filters, you may want to create your dataset query/stored procedure containing the necessary parameter(s).  This ensures that the [Filter Variable] dropdown field populates.

LIST Filter sample screenshot:

[Name] – The filter’s name (what the end user sees)

[Filter Variable] – The @parameter as defined in the dataset query/stored procedure.  When the user selects one or more items in a filter, those items are passed to the Filter Variable as a string (separated by commas in a multi-select filter).
For example: If the filter contains a list of Positions, and the user selects positions: ‘Executive’ and ‘Manager’, then the @Position Filter Variable (as in above screenshot) will be passed the string value of [Executive, Manager]

[Dataset] – Choose the requisite dataset query/stored procedure which contains the list of items that should appear in the filter list.
For example: If you want to filter by Customers, the dataset query/stored procedure should return a unique list of Customers the end user can choose from. The user selection(s) will be mapped to the variable listed in the [Filter Variable] field

** Note – not all filters require a dataset query/stored procedure to populate. For example, Calendar and Numeric Range filters

After creating your filter and filter variables you can use the filter variable to filter report visuals

Example: Bar Chart using @Position variable

You are responsible to parse the filter variable in your report query/stored procedure.

For Example:

create procedure sp_GetSalary
@Position varchar(max)

AS

Select Name, Salary, Position
FROM HR
where Position in (select item from dbo.fnsplit(@Position,','))

** The sql dbo.fnsplit is very useful for parsing delimited strings

Adding a List Filter to filter data on user dashboards

Open ‘Setup Portal’, choose [+ADD -> Filter] then select the List Filter. After adding the List filter dataset don’t forget the last step in the [Assign to Dashboards] tab

** Be aware regarding List Filter – if you want the selection to be a single-select instead of the default multi-select, go to Edit-> App Dashboards – Choose your App Dashboard -> Filters tab – > Delete filter from re-add (unselect MultiSelect)

Filter Control Types

MOST COMMON FILTER TYPES

FORMAT

EXAMPLE

LIST

Comma delimited list

Juice, Milk, Fruit

Date Range

StartDate – EndDate

1/1/2018 – 1/1/2019

Numeric Range

MinNumber – MaxNumber

1 – 10

LIST Filter
Passes a comma delimited string to your stored procedure/query.

MSFT SQL example: if a parameter named @Group is passed to your query/stored procedure (from a List Filter), you can use the fnSplit function to parse the string.

select Sum(Quantity) FROM Products
WHERE ProductName IN (select item from dbo.fnsplit(@Group,','))

How To Parse Filter Strings in your Query:

The fnSplit() function is included as part of the RockDaisy install. For more information on fnSplit() click here.

Date Range Filter (Calendar)
Passes two dates as a string to your stored procedure/query. For example: 10/01/2019 – 10/31/2019

MS SQL example: if a parameter named @DateRange is passed to your query/stored procedure, you can use the fnSplit() function to parse the string.

SELECT @startDate = min(cast(item as date)), 
@endDate = max(cast(item as date)) 
FROM dbo.fnSplit(@DateRange,' - ')   

Numeric Range Filter
Passes two numbers as a string to your stored procedure/query. For example:  1 – 10

MS SQL example: if a parameter named @NumericRange is passed to your query/stored procedure, you can use the fnSplit() function to parse the string

SELECT @MinNumber = min(cast(item as int)), 
@MaxNumber = max(cast(item as int)) 
FROM dbo.fnSplit(@NumericRange,' - ')   

Filter Groups

Filter Groups are used to group your user’s filters by category
Creating a new Filter Group:
Choose [GROUP OPTION] -> FILTER GROUP

Filtering Data by User/Group

This feature provides a way of limiting data access to certain user(s)/group(s).
For example, this is useful if you have confidential data points within a report which should only display to certain employees, like the CEO, CFO and/or IT administrator.

RockDaisy assigns an internal unique identifier to each user. This identifier is found in the [Custom Field] in the [Edit User] screen.  When executing any SQL query/stored procedure from within RockDaisy you have access to this variable and within your query you can perform the necessary logic to filter the data based on the UserID. (For example – ‘do not return salary information if the user variable passed is not in the HR group’)

You can find this variable in the Edit Users screen.

You can use this variable as a parameter to your query

Off-Screen Filters

By default, every filter you create is an off-screen filterOff-Screen Filters and are visible to users within a dashboard’s[REFINE] button

On-Screen Filters

On-Screen Filters are the same as Off-Screen filters except that On-Screen Filters are visible in the main user dashboard area. Off-Screen filters are only visible within the left side [REFINE] button

     Off-Screen Filter                                            On-Screen Filter

 

Supported On-Screen Filter controls

  • Table (single select)
  • Calendar

To create an On-Screen Filter, choose [Add New Report Visual] -> Select desired filter type

On-Screen Table Filter Example

Choose Table Report -> Choose your dataset

Set the Interaction Type in the [Design Panel] and assign the Filter Variable

REPORT PARAMETERS

Overview

Parameters inside a dataset query/stored procedure are frequently used to filter report data before the data is returned to the report.  They also can link related reports, control the report appearance, and/or narrow the scope of a report for specific users.    

Creating Report Parameters:
A query parameter is populated in two ways:
1. Static (hard-coded)
2. Assigned via Filter (Map [Filter Variable] to Report Parameter)

  1. Static:
    If it’s a “Static” Parameter, check the empty Static checkbox and enter a Design Preview Value

Static Parameters:
These are parameters which your query requires to be “hardcoded”. For example, a static parameter could be used with one report which uses one query, but the query will return different result sets based on the static parameter passed.
Sample query using static parameter:
IF @MyStaticParameter = 1 BEGIN select * from table where country = ‘United States’ END
IF @MyStaticParameter = 2 BEGIN select * from table where country <> ‘United States’ END

  1. Assigned via Filter:
    A report designed with report parameters must assign each Dataset Parameter to its according [Filter Variable]. By default, the names of these two items will match, as shown below.

By choosing the [Filter Variable] from the dropdown list you will pass the Dataset Parameter to the query. Next enter a default value in [Design Preview Value] box and click the [Design Preview] button to preview the report.

For more information, see Drilldown Reports.  

Please note – Dataset Parameters are passed to queries/stored procedures or sub-reports and dashboards which also use those same parameters as the parent report.

EMAIL & SMS TEXT SUBSCRIPTIONS

Overview

RockDaisy provides schedule and alert-based subscriptions to help you control processing and distribution of reports. The two types of subscriptions slightly differ in functionality and management:

  • Schedule-based: Specify a type of recurrence: daily, weekly, or monthly. Within each type set the intervals and range for how often an event occurs.
  • Alert-based: Occurs when a specified event happens. For example: Total sales increased more than 10% from last month’s total sales.

End users can subscribe to any report/dashboard by simply clicking the [SEND] button on the left toolbar

Setting Up Subscriptions

For an administrator to create subscriptions, go to the Setup Portal and click [Subscriptions Admin]

Also assign reports for delivery to groups of users or select individuals.

Before setting subscriptions, you need to enter your SMTP Settings

Choose Configure -> Application Settings – > SMTP Settings

Enter your SMTP information

Alert-Based Subscriptions

Occurs when a specified event happens. For example: Total sales have increased more than 10% from last month total sales. To setup an alert-based subscription:

  1. First create a sql stored procedure which returns a value of 1 (true) based on the specific scenario
  2. Schedule this stored procedure (shown above) to execute one or more times within a specified time period
  3. If stored procedure returns true, the report/dashboard/message will be sent to subscribers

ROLES & PERMISSIONS

Setup Roles & Permissions

RockDaisy supports two security models:

  • Windows Active Directory (single sign-on)
  • Application level security

Every user in RockDaisy is mapped to a unique UserID and must be assigned one of the built-in roles, as follows:

ROLE

ACCESS

Super Admin

Full Access (including dev toolkit)

Standard User

View, Interact, Export, Share and Save dashboards

Limited Viewer

View, Interact, Export dashboards

Choose +ADD -> USER

Fill in the Properties in the New User template

After adding a User go to EDIT -> USER

Assign user to existing groups and App Dashboard

Security and Protection

Data Security

RockDaisy does not store any data. It is strictly a data presentation platform which pulls data in real-time from the client secure database server and presents it on the screen. As a result, all the data security is controlled at the database server level.

Dynamic Row Level Security

RockDaisy Row-Level Security enables you to use user/group membership to control access to rows in a database table. Row-Level Security (RLS) simplifies the design and coding of security in your application. This is accomplished by RockDaisy passing the unique RockDaisy UserID to your SQL query/stored procedure. The SQL query/stored procedure can then filter the data based on the person executing the report/dashboard.

The most common use for this is creating a single dashboard but showing differing sets of data based on who is viewing the report. See Filtering Data by User/Group to filter data by individual users or groups

Site Level Security

Secure the entire portal with a username and password. We also support Windows AD single sign-on

EMBED DASHBOARDS

Overview

RockDaisy is a fully customizable responsive web application which enables seamless embedding of RockDaisy into an existing app and complete branding of the UI (see how to brand RockDaisy)

Because every report in RockDaisy is accessible via URL, all that’s required is to navigate your existing app to prebuilt RockDaisy reports/widgets via RockDaisy’s report URL.

With RockDaisy Embedded—for application developers—you can embed visual analytics in your products, so your users and customers can gain valuable insights, and you can get to market fast.

To embed RockDaisy in your application or portal, all you need to do is generate a URL from your application in a specific format (see below) and call the RockDaisy application. This enables your app/portal to securely access RockDaisy dashboards, reports, and visualizations.

Create rich, interactive reports with visual analytics for embedding with RockDaisy report designer portal.

It is possible to modify the overall appearance of the application with additional parameters passed to the Saved Dashboard URL.

Parameter

Description

fullScreen

hides left navigation menu if anything passed, otherwise empty

drilldown

hides top navigation bar menu if anything passed, otherwise empty

hideHeaders

hides all the report headers on the dashboard

allowExporting

parameter that hide / shows export dashboard feature

hideHeaderLogo

hides the client logo in the top left corner

hideMobileMenu

makes mobile view to be kinda full-screen

hideReportsHeaders

Example of usage:

SELECT ‘https://sample.rockdaisy.com/view/2///fullScreen’ as Url – Full-Screen mode

SELECT ‘https://sample.rockdaisy.com/view/2////drilldown’ as Url – Drilldown mode

SELECT ‘https://sample.rockdaisy.com/view/2/////hideHeaders’ as Url – Hidden Header

SELECT https:// sample.rockdaisy.com/view/847/////1 -- completely hides top bar

Passing parameters from your application to RockDaisy

select 'https://yoursite.rockdaisy.com/view/510/@RDPlayerId=' + cast(@RDPlayerId as varchar(max)) + '//FullScreenMode=True' as Url

Pass 2 parameters

select 'https:// yoursite.rockdaisy.com/view/510/@RDPlayerId=' + cast(@RDPlayerId as varchar(max)) + '&@Param2= '  + cast(@Param2 as varchar(max)) + '//FullScreenMode=True' as Url

URL does not support spaces in variable values, e.g. @RDPlayerId=’Tom Brady’ will not work. browser will restrict & character in this case.

Url with date range and another parameter (URLEncode is used to handle spaces)

SELECT  'https:// yoursite.rockdaisy.com/view/510/@SSPlayerId='+ cast(@SSPlayerId as varchar(max)) + '&@DateRange=' + REPLACE(dbo.URLEncode(CONVERT(varchar, (CONVERT(datetimeoffset, dateadd(day,-30,cast(@StartDate as datetimeoffset)), 127)), 127) + ' - ' + CONVERT(varchar, (CONVERT(datetimeoffset, cast(@EndDate as datetimeoffset),
127)), 127)), '%', '__') + '//true'  as SavedDashboardUrl

Kiosk Mode

RockDaisy interactive kiosk mode provide audiences with engaging digital content and information through a user-friendly interface. This type of self-service solution has many uses and benefits, and can be used for a variety of purposes including retail sales, consumer/employee engagement, information sharing and more. RockDaisy kiosks can also function as digital signage when not in use.  Kiosk and signage content is completely customizable and can be easily updated using the RockDaisy report designer toolkit.

Implementing a RockDaisy Kiosk is nothing more than taking advantage of the built-in embedding features (hiding toolbars, menu-bars etc.) and leveraging RockDaisy’s interactive On-Screen Filters and Slideshow mode

DATA IMPORT/COLLECTION

Build & Customize Forms

Design professional looking forms with RockDaisy Form Builder. Customize with advanced styling options to match your branding. All data captured through Rockdaisy forms is stored in your database, making it easily accessible for export or to create reports within the RockDaisy Setup Portal (Report Designer)

To create a new form go to ADD -> Custom Form

Two ways to create the table in the database to store the data entered in form.

  1. Connect to your database a manually create the table then point form to using the RockDaisy UI (recommended)
  2. Create a table automatically using the format as an existing Excel file (by checking NEW checkbox)

 

To Create Form:

  1. Enter Form Name
  2. Choose DB connection string to where the entered data should be stored
  3. Choose NEXT

Additional options include:

After Sync Exec Stored Procedure: This means after data is entered you want to execute a stored procedure. Potentially for data integrity checks, update other tables based on data entered, send an alter email etc.

By default, all form fields are editable textboxes (like excel). If you wanted to make a field a dropdown, Choose the edit icon next to the field you want to modify. Change the Editor Type in the Design Panel.

Excel Upload

RockDaisy supports uploading Excel/CSV data. To upload your excel data follow these steps:

Create a table in your database that will store the information. The Table must have a primary key and one column for each column in the Excel file, plus three additional system columns:

[LastUpdateUser] (string)

[LastModifedDate] (date)

[FileName]    (string)

The above columns will be automatically populated.

Go to +ADD -> CUSTOM FORM -> EXCEL FORM

Fill out the Information

 

Tip – Create a table in your database to store the data temporarily. After the data is loaded use the [After Data Sync Stored Procedure] option execute a SQL statement immediately after the data is loaded. In this procedure “clean”, error check etc. the data loaded and then potentially move to a final table. Then truncate the temporary load table so it’s empty for the next run.