Data Connectivity
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Data Connectivity
Message Icon Topic: Data Selection Post Reply Post New Topic
Author Message
tress
Newbie
Newbie
Avatar

Joined: 14 Aug 2009
Location: United Kingdom
Online Status: Offline
Posts: 2
Quote tress Replybullet Topic: Data Selection
    Posted: 14 Sep 2009 at 3:29am
Hi,
 
I am wondering if someone can help with a problem I am having while selecting data from SQL 2000 table using Crystal as part of the record selection in Crystal, I say to get all data from a specific data where the dates are between 2 dates, these were originally set as parameters that the users could select, I have not tried to change this in a number of ways so that the parameters are picked up from a view, I created, 2 sql expressions, one to pick a start date and then one to pick and end date, then created 2 formula fields that reference the results of the sql expression.  I have inserted the formula fields in to my record so then to limit the data returns, but rather than running the select statement for all the data limited between the 2 dates, it returns all data from the table and then only runs the filter at the end, this means that the report takes about 4 times as long to run, please find below what I have done
 
SQL Expression Field
sql_FirstDate = (select ay from perioddates)
sql_LastDate = (select ed from perioddates)
 
Formula Field
FirstDate = {%sql_FirstDate}
LastDate = {%sql_LastDate}
 
Selection Formulas - Record Selection
{TableName.Date} in {@FirstDate} to {@LastDate}
 
I have checked in the SQL code that CR shows and it shows no use of the parameters, when I revert it back to using Parameters entered by the user it all works fine, can anyone see where I am going wrong, still a bit of a novice at this.
 
Thanks PD
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 14 Sep 2009 at 7:25am

You will want to use  a stored procedure here instead of a view.

Define the params in the SP and they will become available as params in the crystal report. Do not mannually create crystal params.
Primary issue here is that you have to define the params as datetime instead of date as you can in crystal. one work around is to define them as varchar and cast that into a datetime. You lose the formating and crystal date validation so it dies in the SP when a bad date is entered. There are several posts on this if you look around.
IP IP Logged
tress
Newbie
Newbie
Avatar

Joined: 14 Aug 2009
Location: United Kingdom
Online Status: Offline
Posts: 2
Quote tress Replybullet Posted: 14 Sep 2009 at 8:07am
Hi DBlank,
 
Thanks for the response, I did have an inkling that might have been the problem, had tried every other way of getting the parameters to be passed through.  Thanks for the confirmation its much appreciated and saves me having to waste any more time.
 
Thanks PD Smile
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

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



This page was generated in 0.015 seconds.