| Author |
Message |
TDHall
Newbie
Joined: 07 Jan 2019
Location: United States
Online Status: Offline
Posts: 3
|
   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 Logged |
|
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
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 Logged |
|
TDHall
Newbie
Joined: 07 Jan 2019
Location: United States
Online Status: Offline
Posts: 3
|
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 Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
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 Logged |
|
TDHall
Newbie
Joined: 07 Jan 2019
Location: United States
Online Status: Offline
Posts: 3
|
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 Logged |
|
|
|
