on 09-26-2013 4:10 PM
Hi,
I am using following case statement as a perdefined filter in Webi Report . The Webi report takes more then 40 minutes to complete.
Where as same Webi report with manual date prompt completes less then a minute.
I want to use below perdefined filter so I can schedule the report and user will not have to enter date prompt every month to run this reports.
Question is why it takes so much time to complete the webi report with perdefined filter below.
Please advice.
MP
Current Year:
DM_PAYMENTDATE.CALENDAR_DATE
Between
Case @Prompt('Enter Payment Begin Date','A',,MONO,FREE) When
'01/01/1900' THEN DATEADD(yyyy, DATEDIFF(yyyy,0,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -1)), 0)
ELSE @Prompt('Enter Payment Begin Date','A',,MONO,FREE)
END
AND
CASE @Prompt('Enter Payment End Date','A',,MONO,FREE) When
'12/31/1900' THEN DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -1)
ELSE @Prompt('Enter Payment End Date','A',,MONO,FREE)
END
---------------
Pervious Year:
DM_PAYMENTDATE.CALENDAR_DATE
Between
Case @Prompt('Enter Payment Begin Date','A',,MONO,FREE)When
'01/01/1900' THEN DATEADD(yyyy,0,DATEADD(yyyy, DATEDIFF(yyyy,0,DATEADD(yyyy,-1,(DATEADD(d,-1,CAST(CONVERT(char(7),GETDATE(),23)+'-01' as datetime))))), 0))
ELSE DateAdd(yy,-1,@Prompt('Enter Payment Begin Date','A',,MONO,FREE))
END
AND
CASE @Prompt('Enter Payment End Date','A',,MONO,FREE) When
'12/31/1900' THEN DATEADD(yyyy,-1,(DATEADD(d,-1,CAST(CONVERT(char(7),GETDATE(),23)+'-01' as datetime))))
ELSE DateAdd(yy,-1,@Prompt('Enter Payment End Date','A',,MONO,FREE))
END
Hi,
For your 2 webi documents, what's the SQL query being sent to the database when you refresh ? (you'll need to trace to capture this)
regards,
H
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
I tried it.. I captured the SQL from database and run it directly on database.
The query with the case statement takes 5 mins and 38 secs to complete at database level. But the webi report takes more then 40 minutes.
Where as the query with dates prompt on database takes 25 secs on database and 51 secs for webi report.
Megha
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.