Data Connectivity
 Crystal Reports Forum : Crystal Reports 9 through 2016 : Data Connectivity
Message Icon Topic: Get CR to run different supplied SQL queries Post Reply Post New Topic
Author Message
TDHall
Newbie
Newbie
Avatar

Joined: 07 Jan 2019
Location: United States
Online Status: Offline
Posts: 3
Quote TDHall Replybullet Topic: Get CR to run different supplied SQL queries
    Posted: 11 Jan 2019 at 9:32am
I have an app that builds a sql query that works with a stored procedure to filter data as specified by the user selecting from over a dozen variable and categories. The process works perfectly for displaying the data in a grid.
Now I would like provide CR with the same queries to call the stored procedure to filter the data for the production of a CR report. Is there some way to do this?
Retired after 40 years in IT. Worked in 13 countries besides the US. Now write Windows WPF software for non-profits and churches.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3668
Quote hilfy Replybullet Posted: 18 Jan 2019 at 3:55am
The problem with dynamic queries like this is that Crystal expects to have the exact data/query/table structure a report is designed with when the report is run. If you're selecting different fields with every run of the stored proc, you would need to use the RAS SDK that is included with both the .NET and the Java SDKs to build the report on the fly based on what data is returned. There is sample .NET code for this process here: http://www.sdn.sap.com/irj/boc/go/portal/prtroot/docs/library/uuid/70834815-d166-2b10-a2a2-e7c8c9d3c668. If you're using Java, it should be fairly easy to translate between the two - the main difference being that the Java classes are probably interfaces with "I" at the beginning of their names.

-Dell
IP IP Logged
TDHall
Newbie
Newbie
Avatar

Joined: 07 Jan 2019
Location: United States
Online Status: Offline
Posts: 3
Quote TDHall Replybullet Posted: 18 Jan 2019 at 4:28am
Thanks for the reply.
My stored procedure returns the same data fields every time. I use the user selected input values to build an sql statement that tells the stored procedure how to filter the data selection ie. by date, by county, by assessed value, by zip code, defendant, plaintiff, etc. or any combination of the selection criteria.
My question is would the following work:
My thought now is that I can build a temporary table containing the results of my query and have CR always go to that table to build the report. The table would be truncated every time before a new query, so that the data would be unique as dictated by the query and stored procedure, but the tables name and structure would remain static. I could just call CR from my C# app to run the report.
Does that seem like an approach that would work?
Tom
Retired after 40 years in IT. Worked in 13 countries besides the US. Now write Windows WPF software for non-profits and churches.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3668
Quote hilfy Replybullet Posted: 22 Jan 2019 at 6:57am
That should work as long as you don't have two people trying to run the report at the same time. A better approach might be to add a date/time or user based identifier to id a specific run of the report. You would then include that id in your parameters so that only the data for that run is included. You could have a "cleanup" step included in the stored proc that would delete any old data for a specific user or any data that's more than 24 hours old so that the table doesn't grow too large.

-Dell
IP IP Logged
TDHall
Newbie
Newbie
Avatar

Joined: 07 Jan 2019
Location: United States
Online Status: Offline
Posts: 3
Quote TDHall Replybullet Posted: 22 Jan 2019 at 9:44am
Thanks, you have really helped by confirming my approach. I have already thought of the issues you brought up and addressed them. I think it has been over five years since I posted a question else where and received a reply.
Happy New Year

Edited by TDHall - 22 Jan 2019 at 9:45am
Retired after 40 years in IT. Worked in 13 countries besides the US. Now write Windows WPF software for non-profits and churches.
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.031 seconds.