Crystal Reports Can we query on a dataset?

Data Connectivity
Crystal Reports Forum : Crystal Reports 9 through 2022 : Data Connectivity

Topic: Can we query on a dataset?

Author	Message
karenros Newbie Joined: 28 Jul 2008 Location: United States Online Status: Offline Posts: 14	Topic: Can we query on a dataset? Posted: 09 Sep 2008 at 7:29am
	Is there a way i can query on a dataset?? Like the Stored procedure??? and can I find the no of rows my query returns?
	IP Logged

hilfy Admin Group Joined: 20 Nov 2006 Online Status: Offline Posts: 3702	Posted: 09 Sep 2008 at 3:17pm
	What type of "dataset" do you mean? Yes, you can connect to a stored procedure that returns a dataset. If you set up filters in your report, they will be handled one of two ways: 1. If the filters are parameters to the stored procedure which are handled inside the stored proc, they will be run on the database and the dataset that's returned is already filtered. You will need to set up report parameters where the user can enter these values before running the report and then pass the parameters to the stored procedure. 2. If the filters are only in the report, the stored procedure will return all of its data to the report and the filter will be applied in the report. The only way to get the number of rows that were returned is to count them in the report. There is no way to get that value before processing the data in the report. -Dell
	Proviti, Data & Analytics Practice www.protiviti.com/US-en/data-management-advanced-analytics
	IP Logged

karenros Newbie Joined: 28 Jul 2008 Location: United States Online Status: Offline Posts: 14	Posted: 10 Sep 2008 at 8:21am
	Thanks Hilfy for your answer.. suppose if i use the record selection to get the RowNumbers and use the Count(fieldName) I get an error that says it cannot be evaluated.. so what should i do.. I am trying to give this query in my forumla RowNumber = (IIf(Count({rpt_SelectInvestments;1.PlanId})\2 = 1,(Count({rpt_SelectInvestments;1.PlanId})/2)+1,Count({rpt_SelectInvestments;1.PlanId})/2)) Edited by karenros - 10 Sep 2008 at 8:22am
	IP Logged

hilfy Admin Group Joined: 20 Nov 2006 Online Status: Offline Posts: 3702	Posted: 10 Sep 2008 at 10:23am
	Crystal won't let you use Count like this. The only way to do something like this is to use SQL. If you're working from a stored procedure, update it to include the calculation for the row number. If you're not, you may be able to use SQL in a CommandText to do this calculation. -Dell
	Proviti, Data & Analytics Practice www.protiviti.com/US-en/data-management-advanced-analytics
	IP Logged

karenros Newbie Joined: 28 Jul 2008 Location: United States Online Status: Offline Posts: 14	Posted: 10 Sep 2008 at 11:48am
	Can u please elaborate as to how can i use command text in crystal...i am new to crystal and was working on Sql server reporting services before.. Thanks Karen
	IP Logged

hilfy Admin Group Joined: 20 Nov 2006 Online Status: Offline Posts: 3702	Posted: 10 Sep 2008 at 8:57pm
	Basically, you would create a SQL Select statement that would do the counts you need in the report. Go to the Database Expert, open the connection. Just under the connection name you'll see "Add Command" if Commands are available for your database type. Enter your SQL. If you're going to link the query to data in the other tables in your report, be sure to include the fields that you need to link on in the query. The one limitation I've found to using Commands is that Crystal won't let you also use SQL expressions in the same report. But that's not usually a big deal. -Dell
	Proviti, Data & Analytics Practice www.protiviti.com/US-en/data-management-advanced-analytics
	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.023 seconds.