Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Filtering on SQL Views


Filtering on SQL Views

Author
Message
Douglasjbell
Douglasjbell
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 70
Hi All

Looking for a little help.

I have a crystal report that is based on a View on my SQL Server (VwQPPMaterialRptPart3).

This view is based on a union of 2 other views (VwQPPMaterialRptPart1 & VwQPPMaterialRptPart2). After playing with my Crystal Report Select parameters I cannot get it to filter down the way I need the report to be. As a test I filtered some of the information on the underlying Views (Part1 and Part2) and the report worked perfectly. Is there a way to pass parameters from Crystal Reports to my underlying views? Also because of all my views the report does take an age to load.

I am very basic in SQL I can create databases, setup security, add and populate tables and create basic views. So I think my views are very inefficient. Please find below the SQL for my 3 views.

VwQPPMaterialRptPart1 (This combines 2 tables, Shift and SAPMaterials)


SELECT TOP (100) PERCENT dbo.SAPMaterials.Material, dbo.Shift.ShiftDepartment, REPLACE(REPLACE(dbo.Shift.ShiftMachine, CHAR(13), ''), CHAR(10), '') AS QPPMachine,
dbo.Shift.ShiftAmount, CONVERT(datetime, LEFT(dbo.Shift.ShiftStart, 10), 103) AS Date, dbo.SAPMaterials.MaterialButton, dbo.SAPMaterials.Operation
FROM dbo.Shift INNER JOIN
dbo.SAPMaterials ON dbo.Shift.ShiftDepartment = dbo.SAPMaterials.Department AND REPLACE(REPLACE(dbo.Shift.ShiftMachine, CHAR(13), ''), CHAR(10), '')
= dbo.SAPMaterials.Process
ORDER BY dbo.SAPMaterials.Material



VwQPPMaterialRptPart2


SELECT TOP (100) PERCENT ShiftDepartment, REPLACE(REPLACE(ShiftMachine, CHAR(13), ''), CHAR(10), '') AS ShiftMachine, MIN(ShiftAmount) AS ShiftAmounts, SUM(Amount)
AS Defects, CONVERT(datetime, LEFT(ShiftStart, 10), 103) AS Date, ClassType, Material
FROM dbo.VwWeeklyPerformance
GROUP BY ShiftDepartment, REPLACE(REPLACE(ShiftMachine, CHAR(13), ''), CHAR(10), ''), CONVERT(datetime, LEFT(ShiftStart, 10), 103), ClassType, Material
ORDER BY ShiftDepartment



VwQPPMaterialRptPart3 - Final Report View


SELECT dbo.VwQPPMaterialRptPart1.Material, dbo.VwQPPMaterialRptPart1.ShiftDepartment, dbo.VwQPPMaterialRptPart1.QPPMachine,
dbo.VwQPPMaterialRptPart1.ShiftAmount, CASE WHEN dbo.VwQPPMaterialRptPart2.Defects IS NULL
THEN 0 ELSE dbo.VwQPPMaterialRptPart2.Defects END AS Defects, dbo.VwQPPMaterialRptPart1.MaterialButton, dbo.VwQPPMaterialRptPart1.Operation,
dbo.VwQPPMaterialRptPart1.Date
FROM dbo.VwQPPMaterialRptPart1 LEFT OUTER JOIN
dbo.VwQPPMaterialRptPart2 ON dbo.VwQPPMaterialRptPart1.MaterialButton = dbo.VwQPPMaterialRptPart2.Material AND
dbo.VwQPPMaterialRptPart1.QPPMachine = dbo.VwQPPMaterialRptPart2.ShiftMachine
GROUP BY dbo.VwQPPMaterialRptPart1.Material, dbo.VwQPPMaterialRptPart1.ShiftDepartment, dbo.VwQPPMaterialRptPart1.QPPMachine,
dbo.VwQPPMaterialRptPart1.ShiftAmount, dbo.VwQPPMaterialRptPart1.MaterialButton, dbo.VwQPPMaterialRptPart1.Operation,
CASE WHEN dbo.VwQPPMaterialRptPart2.Defects IS NULL THEN 0 ELSE dbo.VwQPPMaterialRptPart2.Defects END, dbo.VwQPPMaterialRptPart1.Date



Now the filtering I was using was by ShiftDepartment and Date on my report but the information is not right. I need to filter by ShiftDepartment and Date on Part1 and also filter by SiftDepartment and Date on Part2.

Hope you can help sort my mess out.

Cheers

Dj
lnardozi 61862
lnardozi 61862
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 615
Personally I prefer an inline TVF for my 'parameterized views', like so


CREATE FUNCTION tvf_report
(
-- Add the parameters for the function here
@ShiftDepartment varchar(100),
@Date1 datetime,
@Date2 datetime
)
RETURNS TABLE
AS
RETURN
(
select VwQPPMaterialRptPart1.Material, VwQPPMaterialRptPart1.ShiftDepartment, VwQPPMaterialRptPart1.QPPMachine,
VwQPPMaterialRptPart1.ShiftAmount, CASE WHEN VwQPPMaterialRptPart2.Defects IS NULL
THEN 0 ELSE VwQPPMaterialRptPart2.Defects END AS Defects, VwQPPMaterialRptPart1.MaterialButton, VwQPPMaterialRptPart1.Operation,
VwQPPMaterialRptPart1.Date from
(
SELECT dbo.SAPMaterials.Material, dbo.Shift.ShiftDepartment, REPLACE(REPLACE(dbo.Shift.ShiftMachine, CHAR(13), ''), CHAR(10), '') AS QPPMachine,
dbo.Shift.ShiftAmount, CONVERT(datetime, LEFT(dbo.Shift.ShiftStart, 10), 103) AS Date, dbo.SAPMaterials.MaterialButton, dbo.SAPMaterials.Operation
FROM dbo.Shift INNER JOIN
dbo.SAPMaterials ON dbo.Shift.ShiftDepartment = dbo.SAPMaterials.Department
AND REPLACE(REPLACE(dbo.Shift.ShiftMachine, CHAR(13), ''), CHAR(10), '') = dbo.SAPMaterials.Process) VwQPPMaterialRptPart1
left join
(SELECT ShiftDepartment, REPLACE(REPLACE(ShiftMachine, CHAR(13), ''), CHAR(10), '') AS ShiftMachine, MIN(ShiftAmount) AS ShiftAmounts, SUM(Amount)
AS Defects, CONVERT(datetime, LEFT(ShiftStart, 10), 103) AS Date, ClassType, Material
FROM dbo.VwWeeklyPerformance
GROUP BY ShiftDepartment, REPLACE(REPLACE(ShiftMachine, CHAR(13), ''), CHAR(10), ''), CONVERT(datetime, LEFT(ShiftStart, 10), 103), ClassType, Material
) VwQPPMaterialRptPart2

ON VwQPPMaterialRptPart1.MaterialButton = VwQPPMaterialRptPart2.Material AND
VwQPPMaterialRptPart1.QPPMachine = VwQPPMaterialRptPart2.ShiftMachine
where VwQPPMaterialRptPart1.Date between @Date1 and @Date2 and VwQPPMaterialRptPart1.ShiftDepartment =@ShiftDepartment and
VwQPPMaterialRptPart2.Date between @Date1 and @Date2 and VwQPPMaterialRptPart2.ShiftDepartment =@ShiftDepartment

)



but you could make another view with what's inside the TVF. All you need to do to be able to filter the second query on shiftdepartment and date is to return those fields in the result set and don't select them in the select statement you use within the report. It's all that TOPing and ORDERing that's slowing you down.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search