Crystal Report Design
I have a request to create a report with data from 4 tables for a specific month grouping the data by the user that is logged in EITHER the created_by or modified_by fields for each record. This is supposed to be an audit report that can be run and then tested to ensure that the user had the authority to make the transactions.
The table data can not be linked and so therefore I am assuming that I have to create 4 subreports.
The data in the first table is a Users table that we are ensuring that additional records have not been created by users already in that table.
The data in the second table is a Contractors table that we are ensuring fictitious contractors were not created.
The data in the third table is a list of Projects that we are ensuring fictitious projects were not created.
The final table is the actual financial transaction that we are ensuring fictitious financial transactions were not created.
All 4 tables have the same 4 data fields...
Created_by
Creation_date
Modified_by
Modified_date
I wanted the report to run a separate section of the transactions for the 4 tables for each user that is identified from the Users table and initially I need to hard code the month date range to run this for prior months, but moving forward have the auditor be able to run the report at the beginning of the month to get the prior month's transactions.
Do I need to put these tables in subreports or is there a better way and how do I ensure that if there were no transactions for a particular user for a particular table that it specifically says..."No transactions"?
Any help with this would be greatly appreciated. I am really not sure where to begin.
Patrick
The table data can not be linked and so therefore I am assuming that I have to create 4 subreports.
The data in the first table is a Users table that we are ensuring that additional records have not been created by users already in that table.
The data in the second table is a Contractors table that we are ensuring fictitious contractors were not created.
The data in the third table is a list of Projects that we are ensuring fictitious projects were not created.
The final table is the actual financial transaction that we are ensuring fictitious financial transactions were not created.
All 4 tables have the same 4 data fields...
Created_by
Creation_date
Modified_by
Modified_date
I wanted the report to run a separate section of the transactions for the 4 tables for each user that is identified from the Users table and initially I need to hard code the month date range to run this for prior months, but moving forward have the auditor be able to run the report at the beginning of the month to get the prior month's transactions.
Do I need to put these tables in subreports or is there a better way and how do I ensure that if there were no transactions for a particular user for a particular table that it specifically says..."No transactions"?
Any help with this would be greatly appreciated. I am really not sure where to begin.
Patrick
ASKER
MLMCC,
You have helped me with reports before. I would not say that I am an expert, but an intermediate user but haven't done a lot of report writing lately and have lost touch with my Crystal skills.
I am using 2011.
The data in the three tables are really from 4 very different processes and cannot be linked. Two of the tables are administrative types of tables where you are adding users so that they can use the application and adding contractors as new contractors are identified. The other two tables are transactional, but can't be linked because as you said there would be a many to many relationship.
What all 4 tables have in common are that the system tags the record with Created_by, Creation_date, and then if that particular record was modified, a Modified_by.
I have contemplated creating a view in SQL to pull this data, but I am not sure what that will gain me. I am struggling to get the data to group for each user and do not have the links correct because I am getting data attributable to another user listed on a particular user. Also the select expert of wanting records where Created_by OR Modified_by seems to throw the joins off.
You have helped me with reports before. I would not say that I am an expert, but an intermediate user but haven't done a lot of report writing lately and have lost touch with my Crystal skills.
I am using 2011.
The data in the three tables are really from 4 very different processes and cannot be linked. Two of the tables are administrative types of tables where you are adding users so that they can use the application and adding contractors as new contractors are identified. The other two tables are transactional, but can't be linked because as you said there would be a many to many relationship.
What all 4 tables have in common are that the system tags the record with Created_by, Creation_date, and then if that particular record was modified, a Modified_by.
I have contemplated creating a view in SQL to pull this data, but I am not sure what that will gain me. I am struggling to get the data to group for each user and do not have the links correct because I am getting data attributable to another user listed on a particular user. Also the select expert of wanting records where Created_by OR Modified_by seems to throw the joins off.
I don't think there is any reason to try and combine the tables.
Can you upload the RPT file so I can look at the links
mlmcc
Can you upload the RPT file so I can look at the links
mlmcc
ASKER
MLCC,
I am sorry it has taken me a while to get back around to this. Being a one man IT department, this problem has dropped off of my radar but is back...Hopefully the details of the report are visible even without the data. Let me know if there is something else I need to provide. This is the report before I started tinkering with it. I had thought that maybe instead of each of the reports being in separate details sections, that maybe at the bottom of the report would work and so I have a new iteration, but I don't think it is even right.
To refresh, I am trying to return the records from 4 separate tables for the various users of an application. If the users did not commit any records, then the report returns that there were no transactions for that table for that user. If there were transactions that the user either created or modified in one of the tables then I want those records to be returned.
Any help would be greatly appreciated.
Patrick
CFAS-Reports-of-Transactions.rpt
I am sorry it has taken me a while to get back around to this. Being a one man IT department, this problem has dropped off of my radar but is back...Hopefully the details of the report are visible even without the data. Let me know if there is something else I need to provide. This is the report before I started tinkering with it. I had thought that maybe instead of each of the reports being in separate details sections, that maybe at the bottom of the report would work and so I have a new iteration, but I don't think it is even right.
To refresh, I am trying to return the records from 4 separate tables for the various users of an application. If the users did not commit any records, then the report returns that there were no transactions for that table for that user. If there were transactions that the user either created or modified in one of the tables then I want those records to be returned.
Any help would be greatly appreciated.
Patrick
CFAS-Reports-of-Transactions.rpt
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
What version of Crystal?
Why can't the tables be linked?
I assume there tables aren't really related and there would be a many to many relationship.
If you want to use subreports you at least need a common field, I assume this will be the user.
Without more knowledge of the data I would use subreports.
Link the subreports to the main report on the user name field
You can have a parameter that determines whether to use the Created By or Modified By field.
mlmcc