Rebuilding Reports — SQL First, Then the Semantic Layer

There are two ways to build reports in DashboardFox, and during a migration you'll use both for different purposes. Direct SQL is the fastest path to a working report when you have existing queries and SQL-capable analysts. The App Builder (DashboardFox's semantic layer) is what you build once the priority reports are running — it's what enables your broader team to build and modify reports without SQL expertise. This chapter covers both, in the order that makes migration go smoothly.

Start With What You'll Cancel Your Contract Over

Before building anything, go back to the usage audit from Chapter 3 and identify the reports your team would notice immediately if they stopped working. These are your migration anchors — the handful of reports that run every Monday, feed the weekly ops meeting, or go out to clients on a schedule. Get those running in DashboardFox first, and run them alongside the old tool until you're confident in the results. Everything else is secondary.

A practical target: rebuild the top 20% of reports by usage frequency before worrying about the long tail. In most organizations, that 20% accounts for 80% or more of actual report consumption. The rest can follow after go-live, or not at all if the usage data shows they're rarely opened.

The Direct SQL Path — Fastest Route to Working Reports

If you have existing SQL queries — from Power BI's query editor, from a query tool your analysts use, or from Tableau's custom SQL — Direct SQL is your fastest path to working reports in DashboardFox. The process is straightforward: select the Direct SQL App when creating a report, choose your data source, and the report tree gives you two fields to work with: Filter and Raw SQL. Paste your SQL into the Raw SQL field using the formula action, save, and the query runs immediately with results in the preview panel.

A few things that make Direct SQL more powerful than a basic query runner. In your WHERE clauses, you can use a syntax like @Filtername to define dynamic parameters — the same query can then be used for multiple purposes just by changing the filter value, which is useful for reports that need to show different time ranges, regions, or categories depending on who's running them. For row-level security, you can embed a data tag placeholder like /#TagName#/ directly in the SQL — when a user runs the report, DashboardFox substitutes their assigned data tag value, automatically filtering results to their scope. This is the secure method when end users don't have the ability to edit the underlying query.

Direct SQL results can also be extended. If you want to build visualizations on top of a SQL query result, use Advanced Reports → Virtual App, which treats a saved report as a virtual data source. The columns from your query become fields you can work with in DashboardFox's visualization builder — adding charts, applying additional filters, or building on top of the result set without rewriting the underlying query. Stored procedures work similarly: Advanced Reports → Stored Procedures gives you a list of available stored procedures on enabled data sources, lets you set their parameters, and saves the result for use in Virtual App the same way.

Building the App — The Investment That Pays for Itself

Direct SQL gets priority reports running fast. The App Builder is what makes those reports accessible to your broader team and enables ongoing self-service reporting without depending on a single analyst. If you're migrating from a tool where only a handful of people could build reports, the App Builder is the mechanism for changing that.

The build process starts with defining your categories (report types). These are the logical groupings for your reporting — Sales Performance, Inventory, Customer Activity, whatever the meaningful units of analysis are in your business. Within each category, you configure the database tables that support it: you bring in the relevant tables in the database view and define the joins between them. If you have existing SQL queries, they're a useful guide — the tables in your FROM and JOIN clauses, and the relationships between them, tell you exactly how to structure the database view for each category.

Once the table relationships are set, you configure the report tree: the fields you want to expose to report builders, organized into folders. You pick from all the columns across your joined tables, set display names that make sense to business users rather than reflecting raw database column names, and apply any formula fields needed. Formula fields use pure SQL expressions — date formatting, concatenations, calculations between fields, period-over-period comparisons. Anything you'd express in Tableau's calculated field syntax or as a DAX measure that maps cleanly to a SQL expression can be defined here as a reusable field.

Once an App is built, any user with the appropriate Builder role can create reports by selecting the App, choosing a category, clicking fields from the organized report tree to add them to the report, and seeing a live 50-row preview as they build. They can add sorting, filters, aggregations, and visualizations without touching SQL. For teams that previously needed an analyst to build every report, this is a meaningful change in how data gets used — managers can answer their own questions, operations staff can build the specific views they need, and the analyst's time goes to more complex work instead of routine report requests.

Visualizations and Dashboards

Whether you're working from a Direct SQL result via Virtual App or from an App Builder dataset, the report builder works the same way. You build your dataset first — the right fields, the right filters, the right aggregations. For visualizations, the dataset needs to be structured to support the chart type: a bar chart needs a character field as the argument and at least one numeric value, a calendar needs a date field and a value, a map needs a state abbreviation and a value. The visualization is built on top of a correctly shaped dataset, not instead of it.

Dashboards in DashboardFox are assembled from saved library reports. You add reports as widgets, size and arrange them, then add dashboard-level filters that apply across multiple widgets. Filters work by matching a shared field across widgets — a date filter applied to a date field that exists in all your widgets simultaneously filters the whole dashboard. Dashboard filters are set as views, and the dashboard owner can set a default view that determines what loads for all users by default.

Migration as a Cleanup Opportunity

The rebuilding process is worth doing thoughtfully rather than mechanically replicating what existed before. Most BI environments accumulate reports that grew organically — multiple reports covering similar data, slightly different versions of the same analysis, reports built for a specific purpose that outlived their usefulness. The migration is a natural point to rationalize that, build a cleaner structure in the App Builder, and deliver an organized library rather than an accumulation.

The next chapter covers the last piece before go-live: setting up users, groups, permissions, and a phased rollout that doesn't land on everyone at once.