July 2, 2013 at 2:34 am
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
July 2, 2013 at 3:08 am
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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply