VICKRAM
asked on
Seagate Crystal Report 7
im using MS Access and Seagaet Crystal report 7 for report generation. i have 2 queries..please help
1)When my package is deployed, the user has the choice of setting the location to store his database file(.mdb file). While designing the report im forced to set the absolute path of the database. This means that if im going to design the report with the database at D:\Projects\Library\Data.m
file will try to look for the .mdb file at that location.
Suppose the user chooses C:\Library\Data.mdb to store the data then how to do i set the location of the database in my report to this path at runtime?
How do i solve this ?
2)Suppose the data for a report comes from executing a sql statement at run time something similar to this sql statement..its a join of 3 tables...
"SELECT tblMemMast.MEM_NAME, tblFines_H.MEM_ID, tblFines_D.ACC_NO, tblIssues.ISSUE_DATE,
[tblReturns].[RET_DATE]-[t
tblFines_D.AMOUNT, tblFines_H.FINE_ID, tblFines_H.FINE_DATE FROM (((tblFines_H INNER JOIN tblFines_D ON tblFines_H.FINE_ID = tblFines_D.FINE_ID) INNER JOIN tblReturns ON tblFines_D.RET_ID = tblReturns.RET_ID) INNER JOIN tblIssues ON tblReturns.ISSUE_ID = tblIssues.ISSUE_ID) INNER JOIN tblMemMast ON tblIssues.MEM_ID = tblMemMast.MEM_ID
WHERE (((tblFines_H.FINE_ID) = 101))"
how do i set the report data at run time in such a case? How do i design such a report first!!
vickram
1)When my package is deployed, the user has the choice of setting the location to store his database file(.mdb file). While designing the report im forced to set the absolute path of the database. This means that if im going to design the report with the database at D:\Projects\Library\Data.m
file will try to look for the .mdb file at that location.
Suppose the user chooses C:\Library\Data.mdb to store the data then how to do i set the location of the database in my report to this path at runtime?
How do i solve this ?
2)Suppose the data for a report comes from executing a sql statement at run time something similar to this sql statement..its a join of 3 tables...
"SELECT tblMemMast.MEM_NAME, tblFines_H.MEM_ID, tblFines_D.ACC_NO, tblIssues.ISSUE_DATE,
[tblReturns].[RET_DATE]-[t
tblFines_D.AMOUNT, tblFines_H.FINE_ID, tblFines_H.FINE_DATE FROM (((tblFines_H INNER JOIN tblFines_D ON tblFines_H.FINE_ID = tblFines_D.FINE_ID) INNER JOIN tblReturns ON tblFines_D.RET_ID = tblReturns.RET_ID) INNER JOIN tblIssues ON tblReturns.ISSUE_ID = tblIssues.ISSUE_ID) INNER JOIN tblMemMast ON tblIssues.MEM_ID = tblMemMast.MEM_ID
WHERE (((tblFines_H.FINE_ID) = 101))"
how do i set the report data at run time in such a case? How do i design such a report first!!
vickram
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I have only used the method I described of changing the DSN at runtime in a scenario where the application was closed and then re-opened after the change. Perhaps if you close your connections to the DB through that DSN and then re-open them after the change this will solve the problem.
Insofar as your 2nd question, I was not aware that your field and table names were going to change, I incorrectly assumed it was only your 'WHERE' clause that would change. In this case you could have connected to the database at design time to place your fields and labels and then pass an ADO recordset (with matching field names and data-types) at runtime. If indeed the 'SELECT' statement in your query is dynamic, then I am unfortunately not able to help. Notice that Expert's-Exchange does have a topic dedicated to Crystal.
Insofar as your 2nd question, I was not aware that your field and table names were going to change, I incorrectly assumed it was only your 'WHERE' clause that would change. In this case you could have connected to the database at design time to place your fields and labels and then pass an ADO recordset (with matching field names and data-types) at runtime. If indeed the 'SELECT' statement in your query is dynamic, then I am unfortunately not able to help. Notice that Expert's-Exchange does have a topic dedicated to Crystal.
TO generate a report at runtime you might look at
https://www.experts-exchange.com/questions/20323320/Generating-Crystal-Report-at-runtime.html
If that helps, you should give the points to dbirdman
mlmcc
https://www.experts-exchange.com/questions/20323320/Generating-Crystal-Report-at-runtime.html
If that helps, you should give the points to dbirdman
mlmcc
Hi VICKRAM,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:
Accept RobDillon's comment(s) as an answer.
VICKRAM, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you. DO NOT accept this comment as an answer.
EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:
Accept RobDillon's comment(s) as an answer.
VICKRAM, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you. DO NOT accept this comment as an answer.
EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
Comment from expert accepted as answer
Computer101
E-E Admin
Computer101
E-E Admin
ASKER
following your advice i created a DSN, myDSN which pointed to D:\Library\Store.mdb
Then i launched Crystal Report then chose the Standard report from the report gallery..
Then i chose the SQL/ODBC type..and i was shown a list of tables and queries from my
access database. I chose a table and selected some fields and previewed the report..
everything ran fine :-) and i saved the report.
now i changed the database myDSN pointed to as D:\Store.mdb through the
Control Panel->ODBC Data Sources. Now i tried previewing the report and i got the
"Could not find the D:\Library\Store.mdb" error message.
how do i rectify this.
can u also help me with my second question..how can i design a report based on a query that
will be generated only at runtime?
thanks for ur reply