Dream Report: Acquiring Data using SQL Statements

Summary

This tutorial showcases how to integrate Dream Report with SQL Server to visualize data. It demonstrates two methods for pulling data: one using a permanent SQL statement within Dream Report and another by typing SQL statements directly. The video guides viewers through creating a DSN for database connection, setting up SQL queries, and generating reports. It also highlights the importance of sorting within SQL statements and the impact on licensing when using the SQL option in Dream Report.

Takeaways

• 😀 Dream Report is a tool for visualizing data from multiple sources.
• 🔧 Two methods for getting data into Dream Report are discussed: typing SQL statements directly and creating a permanent SQL statement.
• 💾 The script demonstrates setting up a DSN (Data Source Name) within Dream Report, avoiding the need to configure it in Windows.
• 🗂️ The process of creating a DSN involves specifying a DSN definition name, selecting a system data source, and setting up server and authentication details.
• 🔗 The importance of testing the DSN connection within Dream Report to ensure successful setup is highlighted.
• 📊 SQL Manager in Dream Report is used to define and store SQL queries for reuse in reports.
• 📝 The script shows how to create and use predefined SQL queries within Dream Report for sorting data in ascending or descending order.
• 🖥️ The video explains how to use a visual query builder in Dream Report for creating SQL statements without prior SQL knowledge.
• 📈 Dream Report's capability to connect to relational databases and pull data based on complex queries is demonstrated.
• 💼 The licensing model for Dream Report is mentioned, with a note on the 'mees' option for unlimited SQL queries.

Q & A

What is Dream Report and what does it do?

Dream Report is a reporting tool that allows users to visualize data from multiple sources. It is designed to help users create and manage reports efficiently.

How many ways are discussed in the script to get data into Dream Report?

Two ways are discussed in the script to get data into Dream Report: typing SQL statements for each instance and creating a permanent SQL statement that can be reused.

What is a DSN and why is it used in Dream Report?

A DSN (Data Source Name) is used in Dream Report to create a configuration that allows the software to connect to a database without having to configure it within Windows. It simplifies the connection process.

Why is the timestamp important in the SQL Server example provided in the script?

The timestamp is important because it is used to determine the data retrieval based on the first record within the table, using the day timestamp to make that determination.

What is the purpose of DSN Manager in Dream Report?

DSN Manager in Dream Report is used to create the DSN configuration from within the software, which simplifies the process of setting up a connection to a database.

How does one create a DSN in Dream Report as per the script?

To create a DSN in Dream Report, one would go into DSN Manager, give a DSN definition name, add a DSN by selecting a system data source, provide a name, choose the server, select SQL Server authentication, specify the database, and then test the connection.

What is the difference between defining a SQL statement for each instance versus creating a permanent SQL statement in Dream Report?

Defining a SQL statement for each instance is more manual and requires retyping the statement each time, while creating a permanent SQL statement allows for reusability and easier management of the SQL queries within Dream Report.

How does one define a permanent SQL statement in Dream Report?

To define a permanent SQL statement in Dream Report, one would use the SQL Manager, give the statement a name, and type the entire SQL statement as required for the database query.

What is the advantage of using a predefined SQL query in Dream Report?

Using a predefined SQL query in Dream Report allows for the reuse of the same query without having to redefine it each time, which saves time and reduces the potential for errors.

How does the script suggest handling complex queries in Dream Report?

The script suggests using the visual query builder in Dream Report for complex queries, which allows users to visually select the table and fields they want to include in their query.

What licensing consideration is mentioned in the script for using SQL statements in Dream Report?

The script mentions that using SQL statements in Dream Report may affect the licensing model, particularly the tag count. For unlimited SQL queries, the 'mees' option should be included in the license.

Outlines

00:00

📊 Introduction to Dream Report and SQL Integration

The video script introduces Dream Report, a reporting tool for visualizing data from multiple sources. It outlines two methods for integrating SQL statements within Dream Report. The first method involves manually typing SQL statements each time data is pulled, while the second method creates a permanent SQL statement that can be reused. The video emphasizes not defining an ODBC driver but instead creating a DSN (Data Source Name) for data source configuration within Dream Report. The presenter demonstrates setting up a DSN using DSN Manager, selecting a system data source, specifying server details, and using SQL Server authentication to connect to a database containing a table with product and quantity information. The timestamp is crucial for determining the data retrieval based on the first record in the table.

05:02

🔍 Creating Reusable SQL Queries in Dream Report

The script details the process of creating a permanent SQL statement within Dream Report for reuse. This is done through SQL Manager, where the presenter defines a query to sort data by quantity in ascending order. Another query is created to sort the same data in descending order. The advantage of this method is that it allows for more powerful and flexible data retrieval, though it requires more knowledge of SQL. The presenter then shows how to use these predefined queries in a report by selecting the DSN and choosing the appropriate query. The sorting within Dream Report is disabled to let the SQL statement handle the sorting. The video concludes with a demonstration of running the report and viewing the results in ascending and descending order.

10:02

🛠 Advanced SQL Query Building in Dream Report

The final paragraph discusses an advanced method of building SQL queries directly within Dream Report using a visual query builder. This approach allows users to select tables and fields, define sorting orders, and even add filters without manually typing SQL statements. The presenter demonstrates creating a query to retrieve data in descending order using the visual builder. The script also touches on the potential complexity of queries and the flexibility of Dream Report in handling them. It mentions the ability to filter data based on specific conditions and the impact of SQL query generation on the licensing model of Dream Report. The video ends with a call to action for viewers to provide feedback and contact local representatives for more information on Dream Report.

Keywords

💡Dream Report

Dream Report is a reporting tool mentioned in the script, which allows users to visualize data from multiple sources. It is the central focus of the video, as the script describes how to use this tool to create reports. The video aims to help viewers get started with Dream Report quickly, showcasing its capabilities and features.

💡SQL Statements

SQL (Structured Query Language) statements are used in the video to extract data from a database. The script explains two methods of using SQL with Dream Report: typing SQL statements for each instance or creating a permanent SQL statement that can be reused. This is crucial for data retrieval and manipulation within the reporting tool.

💡DSN (Data Source Name)

A DSN is a configuration that defines the connection parameters to a database. In the context of the video, the script describes how to create a DSN using Dream Report's DSN manager, which simplifies the process of connecting to a SQL Server without having to configure it within Windows. The DSN is named 'SQL 2019' in the example, and it's used to establish a connection for data retrieval.

💡ODBC Driver

An ODBC (Open Database Connectivity) driver is a software component that allows applications to connect to and interact with databases. The script notes that for the method being demonstrated, the creation of an ODBC driver from within Dream Report is not required, which streamlines the process of setting up data connections.

💡SQL Server

SQL Server is a relational database management system developed by Microsoft. The video script includes an example where the presenter shows a SQL Server with a table containing product information and timestamps. This server is used to demonstrate how to pull data into Dream Report for reporting purposes.

💡Timestamp

A timestamp in the context of the video refers to a data element that records the date and time when a record was created or modified. The script mentions using the timestamp to determine the data to be retrieved from the SQL Server, specifically to select the data based on the first record within the table.

💡SQL Manager

SQL Manager within Dream Report is a feature that allows users to define and store SQL statements for reuse. The script demonstrates how to create and save SQL queries within Dream Report, which can then be selected and used in reports. This feature enhances efficiency by avoiding the need to repeatedly write the same SQL queries.

💡Report

A report in the video refers to the output generated by Dream Report, which compiles and presents data in a structured format. The script guides viewers through the process of creating a new report, selecting data sources, and applying SQL queries to display the data in either ascending or descending order based on specified criteria.

💡Visual Query Builder

The Visual Query Builder is a tool within Dream Report that allows users to graphically construct SQL queries without manually writing the SQL code. The script describes using this builder to select tables, fields, and sort orders, making it easier for users to create complex queries through a more intuitive interface.

💡Tag Count

Tag count in the context of Dream Report refers to the number of elements or components used in a report, which can affect licensing costs. The script mentions that using SQL statements, as shown in the video, requires the 'Mees' option in the licensing model to allow for unlimited SQL queries, whereas other methods might consume tag count and thus be more costly.

Highlights

Introduction to Dream Report as a data visualization tool.

Explanation of two methods for getting data into Dream Report using SQL statements.

Demonstration of creating a permanent SQL statement within Dream Report for data retrieval.

Clarification on not defining an ODBC driver and instead creating a DSN for data source configuration.

Step-by-step guide on creating a DSN using DSN Manager in Dream Report.

Selection of a system data source and specifying server details for the DSN.

Authentication method choice between SQL Server and Windows authentication for the DSN.

Testing the DSN connection to ensure successful setup.

Creating a new report in Dream Report and setting up the initial configuration.

Using SQL Manager to define and reuse SQL statements for reports.

Creating two SQL statements for sorting data in ascending and descending order.

Adding a SQL query table to the report and selecting a predefined query.

Disabling automatic sorting in Dream Report to let the SQL statement handle it.

Running the report to view data sorted by the predefined SQL queries.

Introduction to the visual query builder for creating SQL statements within Dream Report.

Building a SQL statement using the visual query builder for data sorting.

Discussing the power and flexibility of creating SQL statements directly in Dream Report.

Mention of the licensing model and its impact on using SQL statements in Dream Report.

Conclusion on the capabilities of Dream Report in communicating with databases.

Transcripts

[Music]

dream report is a reporting tool that

allows you to visualize data from

multiple sources these videos are

designed to help you get up and running

with dream report in no time so let's

Jump Right In what I'm want to show is a

way of getting data into dream report

but it will actually require you to

create SQL statements now there's two

ways of doing this one is where we

actually type the SQL statement in for

each instance that we want to pull data

in and another way is we used or we will

create a SQL statement that is a

permanent SQL statement almost like an

object that you can then refer to uh to

pull the data out and this will be a

little bit clearer once you see me go

through it with this way we're going to

be doing it today you will notice one

thing different is we will not Define an

obbc driver

we will create a DSN uh data source we

will go through that process but we will

actually not create an NBC driver out of

dream report to be able to do this so

with that being said let's jump right

into it first off uh here's my SQL

Server I want to show you this I've got

a table I got two records in that table

like a production table of how of a

product we're going to make and the

quantity we're going to make and I've

got a time stamp around that so I'm

going show how all that comes in to

triam report uh the time stamp is going

to be important because when we do this

query we getting this data from SQL

Server we're going to have to uh or the

way I'm going to set it up is to specify

that I want the data based off the first

record within the table and it's going

to be using the day time stamp to make

that determination over on dream report

what we're going to start off by doing

is creating going into DSN manager and

what DSN manager does it's an easy way

for us to create the DSN configuration

from within dream report and not have to

go into windows and create it by the way

it does use the 32bit DSN this is just

makes it a little bit easier to find all

this and get it set up so first thing

we're going to do is we're going to give

us a DSN definition name so for my DSN

definition name I'm it's going to use

SQL

2019 uncore

Dore

name now I do not have a DSN created yet

so I'm going to hit this plus to add a

DSN this stops us from having to

configure this within windows so I'm

going to come down here I'm going tell I

want to use a system data

source I'm going to select the one I

want to use which is SQL in our

case click finish

and now I've got to give it a name and

in my case I'm just going to name it SQL

2019

DSN the server in my case is SQL 2019

that is the name of my SQL Server that

I'm going to be connecting to click

next I'm going to use the SQL Server

authentication you may choose to set

Yours up

differently and use a Windows

authentication but I'm going to use SQL

Server authentication I want to change

it to the default database that I know

I'm trying to work against which is this

ESC test it is the one that contains

that table that I showed you a few

minutes ago click next and I'm going to

click finish and then I'm going to test

this and I see I got a test connect

completed successfully so that's

important that we run that test that

tells us this connection was able or

dream report

DSN has been created

correctly okay now now at this point I

actually got to go and select that after

I created so that plus icon or the plus

button all that did was launch the DSN

Creator to create the DSN now I'm

telling it my DSN definition name is

going to use the one I just created I'm

going to give it a log in again you may

choose to do yours differently or yours

may be different I'm just going to test

this again so I'm seeing I got a

completed test success Y and I'm just

going to add it so now what I

have is the SQL

2019 DSN definition name it's an odbc

and it uses the DSN SLE

2019 DSN and using a sa login so at this

point I just click okay and that part of

it has been set up now that we have the

DSN setup completed we're ready to jump

right in to to creating the report

because we don't need to configure a

driver for this so I'm just going to

right click tell it new report and I'm

again I'm just going to take the default

settings here we will have other videos

that go through all how to configure all

this report setup stuff and different

ways you can do that but we're just

going to do an apply and okay so what we

need to do to begin with for the first

way of doing this is we're going to set

up a SQL statement that is permanently

defined within dream report that we can

reuse over and over again and the way we

do that is with SQL manager up here at

the top so if I click on that I've

already got one defined and I'm just

going to show you how that set up and

then I'll set up another one but I've

got it named SQL Mees query and I

actually typed this SQL statement to run

against that database now if you

remember the database is a little EMS

table that we defined and it's basically

got our order number air product air

quantity and the day time stamp now if

you notice the default order the way

this was entered into the database was

soaps and then the candles was put in

and that's matches up with the date time

stamp on this particular one what I'm

asking for the one I defined at this

point in time was to query that data but

do an sort on that data by the quantity

in ascending order now the one I'm going

to create is I'm going to create another

SQL statement with in dream report and

we're just going to call it SQL mes

query 2 but I'm going to change this

query statement to where it's going to

be in a descending order based off of

the quantity so I'm going to create one

and that's basically all there is to it

you give it a name and then you have to

type the entire SQL statement whatever

you want it to be because you can create

and Define and build queries against

databases the way you need to to make it

for your application so it's probably I

would say more power powerful than the

other way we were doing it but it also

requires a little bit more knowledge to

be able to uh make this work so I'm

going to say okay so now what I've got

is I have two different ones remember

this one is an ascending order and this

is a descending order so now that we've

got that defined I'm just going to go

over here on my object and I'm going to

do a SQL cury

table and I'm G do that and at this

point I gota give it that data source

name which we defined earlier

and now I'm going to choose an existing

query and I'm going to choose the first

one that I'd already created and I'm

just going to add it and you can go into

the appearance if you want to I would

encourage you for what we're doing you

notice it the FED with this sort being

on always check this because dream

report does this thing where it

remembers previous configurations at

least that's what I've seen with it and

you're going to want to make sure that

they're not going to interfere with what

you're actually trying to do so I don't

want this thing doing any sorting I'm

I'm letting my SQL statement handle my

sorting so I turn that off and then I'm

going to say uh it's it's got in this

instance you need to set it up to where

it use Query directly click okay so

that's the first one right so we should

see the values listed off in an

ascending order so we're going to create

one

more and this one we're going to again

select the DSN name but we're going to

use the second one I

created again I'm going to check this

sure enough to sort was on so I'm going

to check that I'm going to make sure

that when I pull this back it should be

in descending order based off of this

query statement say okay so now we're

basically done and the key to this is uh

when you're building out these reports

using this method you won't be creating

those query statements every time

because they are built within dream

report rep now are stored within dream

report in the SQL manager and what I you

saw me configure earlier so now I want

to do run project yes I want to save

this upload it to the runtime manager

and then we're going to run the report

and see the information come

back okay so I'm going to do a generate

report and I'm going to view

it and sure enough you can see where the

first one did the quantity in ascending

order and the second one did the

quantity in descending order so so

that's one of the ways you can pull back

data directly from the database using

SQL statements okay with the second way

of doing this we don't need two of these

so I'm just going to delete one I'm

delete that one again click on the uh

SQL query

table draw that out and on our data

source again it's the same data source

but this time we're not going to use a

predefined query distored with in train

reports we're going to type it out so

there's this visual query Builder which

is kind of pretty neat to be honest with

you so if you you open it up once you to

find that DSN it'll be able to look at

that table I can click the table I want

and then I can go through pick off the

values I want returned I want you to

sort the uh quantity sort type you know

ascending descending whatever it may be

if I click okay there or just click down

here you'll see it says order msq

data ascending when it says ascending

there I could tell it descending and

push the descending uh so you could do

it like that or you could have just

typed it in by hand we're going to go

ahead and use this easy little Builder

here now I will say this sometimes these

queries can get a little complex

uh kind of your your call on this how

you want to handle it but we're going to

go with this one to where it's just

going to build it in descending order we

just use a visual guey inside dream

report be able to do it that should be

all good and set up so I'm going to say

okay I'm going to go to my appearances

and again I want to turn this sort off

because I want the sort being done over

here I put the sort inside my query

statement you could have done a where

statement and actually done a filter on

this and and we'll do some later videos

about how to do that but again you're

kind of you got a lot of power with this

I can actually tell it to bring the data

back where only the is greater than 100

and then I could have Associated another

field on my report to where I could have

plugged in instead of it being defaulted

at 100 I could have referenced it I

could other words told dream report that

I wanted it where it was greater than

100 or think of a bat report where I

just want to see certain batches brought

back again you

could have that a drop down box on the

report you select the batch you want

then it goes back and pulls all the

associated values based off that batch

so that that's how that stuff would

would work out that's getting a little

bit beyond what we're trying to show

right here just trying to show how we

can actually connect dream report to uh

a relational database I've created this

SQL statement using a uh the guy

configuration so now we should be able

to select okay and now we're ready to

run this report so if I click R for it

yeah do I want to save it yes I do it's

uploading it to the runtime

engine so now I'm going to do a

generate

report and then we're going to look at

PDF and we can see we got it in

descending order so that's the other way

you can build a dream report report um

with connected to SQL Server again two

different ways of doing it I would say

this is probably a little bit more

advanced you got more capabilities with

doing it this way versus the simple way

that I've showed in previous video but

as you can see dream report is fully

capable communicating to databases in at

least three different ways it does

change licensing Model A little bit if

you're going to be using a lot of what I

just showed you here where you're

actually generating SQL statements when

you get your license in or look to

purchase it you're going make sure you

get the mees option because of tag count

uh it will allow unlimited Mees queries

uh the other way is actually going to

bite into your tag count with dream

report if you do it the simple way to

showed in another video so you just kind

of got to weigh that out let us know if

this video was helpful by clicking the

like button below if you want to learn

more about dream report contact your

local es&

[Music]

representative