logo

Warning: The forum is now for consultation only. Please use GitHub Discussions to post any questions or comments.


Welcome Guest ! To enable all features please Login or Register.

Notification

Icon
Error

Options
Go to last post Go to first unread
siobhan  
#1 Posted : Friday, June 6, 2014 8:39:05 AM(UTC)
siobhan

Rank: Member

Groups: Registered
Joined: 6/5/2014(UTC)
Posts: 23
Ireland
Location: Dublin

Thanks: 1 times
Hi

I have a previous report that I want to convert into SEAL Reporting tool, This report has three SELECT statements that make up the data I require for my report.

How do I create a number of Select Statements in one report using SEAL?

Please see the example below

Regards
Siobhan

SELECT CASE WHEN UPPER(parent_source) != "" THEN UPPER(parent_source) ELSE "NO PARENT SOURCE" end as 'Parent Source',
customer_id,
CONCAT(sales_person_first_name , ' ' , P1.Sales_person_surname) as 'Agent',
agent_id as 'Agent ID' ,
(CASE WHEN joint_first_name != '' AND joint_surname != '' THEN CONCAT(customer_first_name , ' ' , customer_surname , ' AND ' , joint_first_name , ' ' , joint_surname )
ELSE CONCAT(customer_first_name , ' ' , customer_surname ) END) as 'Customer Name' ,
customer_address_1 as 'Customer Address 1',
DATE_FORMAT(signup_date , "%d/%m/%Y") as 'Signed up Date' , DATE_FORMAT(installation_date, "%d/%m/%Y") as 'Other Date',
"Installed" as 'Status',
"" as "Notes",
"Installed Customer" as 'Sale Type' ,
sales_team_name as 'Sales Team',
1 as "count",
week(DATE_SUB(NOW(),INTERVAL 1 day),3) as 'Week number',
source AS "Source",
DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 day) , "%d/%m/%Y") as 'Run Date'

FROM P1_SALES
WHERE `status` = 'Active'
and liberty_meter_number != ''
AND installation_date != "0000-00-00 00:00:00"
and customer_first_name != 'Voxpro'
AND DATE(installation_date) = DATE(DATE_SUB(NOW(),INTERVAL 1 day))



UNION

SELECT CASE WHEN UPPER(parent_source) != "" THEN UPPER(parent_source) ELSE "NO PARENT SOURCE" end as 'Parent Source', customer_id,
CONCAT(P1_Sales.sales_person_first_name , ' ' , P1_SALES.sales_person_surname) as 'Agent' ,
agent_id as 'Agent ID',
(CASE WHEN joint_first_name != '' AND joint_surname != '' THEN CONCAT(customer_first_name , ' ' , customer_surname , ' AND ' , joint_first_name , ' ' , joint_surname )
ELSE CONCAT(customer_first_name , ' ' , customer_surname ) END) as 'Customer Name',
customer_address_1 as 'Customer Address 1' ,
DATE_FORMAT(signup_date , "%d/%m/%Y") as 'Signed up Date' ,
DATE_FORMAT(cancellation_date , "%d/%m/%Y") as 'Other Date',
(CASE WHEN `status` = 'Inactive' THEN "Cancelled" ELSE `status` END) as 'Status',
CONCAT(cancellation_reason, ' - ', cancellation_reason_other) as "Notes",
"Cancelled Customer" as 'Sale Type',
sales_team_name as 'Sales Team',
1 as "count",
week(DATE_SUB(NOW(),INTERVAL 1 day),3) as 'Week number',
source AS "Source",
DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 day) , "%d/%m/%Y") as 'Run Date'

FROM P1_SALES
WHERE `status` = 'Inactive'
and customer_first_name != 'Voxpro'
AND DATE(cancellation_date) = DATE(DATE_SUB(NOW(),INTERVAL 1 day))
epf  
#2 Posted : Friday, June 6, 2014 12:59:21 PM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 205 time(s) in 198 post(s)
The simpliest thing to do is to copy and paste your query in the
SQL Statement property of your SealMaster table,
then you can have a direct report based on this query.

Check the quick start guide at http://sealreport.org/QuickStart (Guide 1 in this case).

Edited by user Friday, June 6, 2014 1:01:06 PM(UTC)  | Reason: Not specified

siobhan  
#3 Posted : Sunday, June 8, 2014 8:25:34 PM(UTC)
siobhan

Rank: Member

Groups: Registered
Joined: 6/5/2014(UTC)
Posts: 23
Ireland
Location: Dublin

Thanks: 1 times
Thank you,

I think I have finally seen the light, the SEAL Master table is where I should create all my complex SQL statements and from this table I should run and develop my report.

Thanks,
Siobhan
Users browsing this topic
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.