Seal Report Forum
»
Report Edition
»
Reports
»
Selecting from Joined Sub-Selects
Rank: Member
Groups: Registered
Joined: 9/4/2015(UTC) Posts: 23  Location: ottawa Thanks: 7 times
Was thanked: 2 time(s) in 2 post(s)
|
I am interested in trying to work a solution into Seal Report that will compare different results form the same table. Say for example I am recording execution times for various transactions in multiple test runs and storing the results in the same table. Each test run will have a test run ID and each test run will have the same set of test transactions like this
t.TestRun t.ActionId t.Action t.Time
1 21 open 31
1 25 close 29
2 21 open 32
2 25 close 45
I am interested in comparing times for each transaction between test runs like this
Action FirstRun SecondRun
open 31 32
close 29 45
I can do this in SQL selecting from a join of sub-select statements... something like this
Select
FirstSet.Action
FirstSet.Time as FirstRun
SecondSet.Time as SecondRun
(SecondSet.Time - FirstSet.Time) as Difference
From
(Select t.Action, t.Time
From t
Where t.TestRun = 1) as FirstSet
INNER JOIN
(Select t.Action, t.Time
From t
Where t.TestRun = 2) as SecondSet
ON FirstSet.ActionId = SecondSet.ActionId
Which would give me the result set I want, from which I could do any compare calculation shown in the Difference column.
Action FirstRun SecondRun Difference
open 31 32 1
close 29 45 16
I was attempting to implement this solution in Seal Report but was running into a few issues. The first issue was that I was not sure how to put in this custom sql. Looking at the examples I was under the impression that we could use our own sql but it's not as simple as putting it into the sql field (which always errors out on the 'AS' statements). So I was trying to examine different solutions for this. I do not have the authority to change the DB, stuck with reporting on it the way it is.
What are your thoughts?
|
|
|
|
|
|
Rank: Administration
Groups: Administrators
Joined: 12/20/2013(UTC) Posts: 1,209  Thanks: 14 times
Was thanked: 206 time(s) in 199 post(s)
|
First, you can try to paste your SQL in the SealMasterTable (check the quick start in http://www.sealreport.org/quickstart.cshtml). This may not work depending on your database type. I know also that there are other function in SQLServer (like PIVOT) or in Oracle to create additional columns from a simple table, you may check this function to re-write your query. Perhaps you can create a view in your database for this select ? Then just use SELECT * from YourView and add it to your Data Source... These are the solutions if you want to use your database to perform the query and generate the result set... Note that from the 1.8, you can build up your own result set (by performing here 2 queries) by using the following features: Model Scripts: For SQL Data Source, a 'Pre Load Script' can be defined in the model to modify the model before the database load and a 'Post Load Script' can be defined in the model to modify the result table after the database load. Finally, a 'Final Script' is available to modify the model after the complete load. Check samples: 43-SQL Source with XML Google API Cell Script: Custom scripts can be defined on a report element to process values after the Summary, Page and Data tables have been built in the model. These scripts can be used to customize cell display and values or to perform complex calculations. Check sample: 50-Cell Script - Progression and running totals I don't know if it fits your requirements and your skills (as you have to write C#) but at least you are sure to be able to generate the result set you want... Good luck.
|
|
|
|
|
|
Rank: Member
Groups: Registered
Joined: 9/4/2015(UTC) Posts: 23 Location: ottawa Thanks: 7 times
Was thanked: 2 time(s) in 2 post(s)
|
Thx for the suggestions. I did get this to work by pasting the from clause (that had the joined subselects) directly into the FROM clause of the model, then dragged the columns I wanted to see (including 2 for the Time column) into the report. Then I changed the SQL for each report item manually to reference FirstSet and SecondSet etc. Seemed to work great.
I do appreciate your suggestions. Also have some more questions regarding the filtering but will direct that to a new post.
|
|
|
|
|
|
Seal Report Forum
»
Report Edition
»
Reports
»
Selecting from Joined Sub-Selects
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.
