Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Filtering on SQL Views Expand / Collapse
Author
Message
Posted Tuesday, July 2, 2013 2:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 3, 2013 11:50 AM
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












Post #1469342
Posted Tuesday, July 2, 2013 3:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 1:16 PM
Points: 107, Visits: 511
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.
Post #1469358
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse