Crystal Report Design Question

I need to develop a report that uses a Users table to compare against the Created_by and Modified_by fields in another table to report a list of the records that were Created_by or Modified_by each user in the Users table.  

In other words, the Users table has a userid that an application inserts into the Created_by field or the Modified_by field for every transaction inserted into the Transaction table.

I am not sure how to cycle thru every user to either show the transactions that the user created or modified or state "No transactions entered for this user."

Any guidance on how to set this up would be greatly appreciated.
mounty95Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raghavendra HullurSoftware DeveloperCommented:
Hi,

Which is the database you are using?

Some brief logic would be to build a join on user id table with created by and modified by columns as you say the User ID is stored in those 2 columns.

So something like below should work.

select required columns
from Users a, another_table b
where (a.userID = b.createdBy or a.userID = b.modifiedBy)
and your additional join conditions.

I hope this helps. Revert back if you are looking for something else, may be report with some saved data with required result may help further.
0
mounty95Author Commented:
I guess my question is though....how do I design the report to cycle thru every user (there are only 5-10 users) and return either the transactions that the user has created or modified or return "No transactions for this year" for each user that did not insert any records.

Users Table
userid, password, email, userlevel
x,123456,x_email,1
y,abcxyz,y_email,1
z,abcdef,z_email, 1
a,987654,a_email,2

Transaction Table
ID, $Amount,Description, Created_by, Creation_date,Modified_by,Modfied_date
1,100,x,3/28/18,x,3/28/18
2,200,x,3/1/18,y,3/17/18
3,300,x,3/5/18,x,3/5/18
4,100,a,3/1/18,a,3/1/19
5,50,z,2/14/18,z,2/14/18

So the report would cycle thru all of the users (x, y, z, and a).  It would return the transactions that X inserted or updated.  It would state that there were no transactions for y.  It would list the transactions z inserted or updated.  It would list the transactions a inserted or updated.

Hope this helps to clarify.
0
Raghavendra HullurSoftware DeveloperCommented:
Ok,

Additional info helps.
Having said that, if you try to pick the records based on modifed_by or created_by = userId, then even the records with y will also be included as userID = modifed_by in that case.

So, if you just need to pick the records as you explained, then you need to have a join on created_by column.

Here are the results for each:
1) userID = created_by:

x      100      x      x
x      200      x      y
x      300      x      x
y      NULL      NULL      NULL
z      50      z      z
a      100      a      a

2) userID = created_by or userID = modified_by:

userid      amount      created_by      modified_by
x      100      x      x
x      200      x      y
x      300      x      x
y      200      x      y
z      50      z      z
a      100      a      a

Note that I have used an left join.
If this get clarified, then report design would be to insert required columns and include a group on userID to display related records, and if you want to display the message "No Transactions", then create a formula with a condition like

if isNul(createdBy) then "No Transactions" and then use that to display the message when no transactions.
0
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

mlmccCommented:
You need to use a UNION query
The first query will return the users and the files they created
The second query will return the users and the files they modified.

SELECT Users.UserID, Transactions.ID, "Insert" as UserAction
FROM Users LEFT OUTER JOIN Transactions on Users.UserID = Transactions.Created_By

UNION ALL
SELECT Users.UserID, Transactions.ID, "Update"
FROM Users LEFT OUTER JOIN Transactions on Users.UserID = Transactions.Modified_By

In the report add a group on the USER ID
In the Group header add a formula
If IsNull({Table.ID}) then
    "No inserts or updates"

Open in new window



Put the Action and Transaction ID field in the details

mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mounty95Author Commented:
MLMCC,

I was able to generate the SQL code for the report.  The formula:

if isnull ({Command.contractor_id}) then "No inserts or updates" returns the error "A Boolean is required here."

I think I am almost here with this.

Thanks for your help.
0
mlmccCommented:
Where did you put that formula?

mlmcc
0
mounty95Author Commented:
I placed the formula in the "Keep Together" Group Header formula.  I couldn't decide where else it should go.
0
mlmccCommented:
Just put it in the group header

mlmcc
0
mounty95Author Commented:
So I created a formula called it NoData and inserted it into the group header.

My report is only returning the one user and not listing the other users with "No data inserted"

There are 19 users in the users table and so in this one case with the join, I would expect the one user with the transactions entered by that user and 18 rows with the user listed as it is in the group header and "No data inserted" next to their name.

Sounds like a join issue, but I am not sure how to modify:

SELECT project_users.UserID, contractors.contractor_id, contractor_name,'Insert' as UserAction,CONTRACTORS.CREATION_DATE
FROM PROJECT_USERS LEFT OUTER JOIN contractors on project_users.UserID = contractors.Created_By
where CONTRACTORS.CREATION_DATE >'1/1/16'

UNION ALL
SELECT project_users.UserID, contractors.contractor_id, contractor_name,'Update',CONTRACTORS.MODIFIED_DATE
FROM project_users LEFT OUTER JOIN contractors on project_users.UserID = contractors.Modified_By
where CONTRACTORS.MODIFIED_DATE>'1/1/16'
0
James0628Commented:
I don't know if this will help, but you have a Left Outer Join to Contractors, which implies that some users may not have any records in Contractors.  If not, the columns in Contractors will be null, but you check for CONTRACTORS.CREATION_DATE >'1/1/16' in the Where, so you'll be excluding the users that aren't in Contractors.  If you want to include those users, try changing the Where to:

where CONTRACTORS.CREATION_DATE IS NULL OR CONTRACTORS.CREATION_DATE >'1/1/16'

 And if you don't want to include the users that aren't in Contractors (or every user will always be in Contractors), then why the Left Outer Join?

 James
0
mlmccCommented:
James is correct.  When Crystal tries to evaluate the DATE comparison, NULL dates cause the comparison to terminate and the result is usually not what you want.  In this case it eliminates all contractors with no jobs.

His fix may work.  If not you will probably have to sue a command and include the date comparison in the join rather than the where clause.

mlmcc
0
mounty95Author Commented:
Gentlemen,

Thank you very much!!!!!  I got just the result I was hoping for.  Each user is listed and "No inserts or updates" appears by each user that did not enter any transactions in the contractors table.
0
James0628Commented:
You're welcome.  Glad I could help.

 James
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.