Skip to Content
0

How to return data on report that is not included in a group

Oct 17, 2016 at 10:16 PM

32

avatar image
Former Member

Hi All,

I am currently using Crystal reports 2013 SP5 to build my report. Here is how the design of the report goes:

There is a SQL Server database in the backend with tables such as a Groups, Activities, Users and Main.

Each user has access to a front-end MS Access database to enter their data on a daily basis. A user enters data by selecting an activity and entering the Quantity against it.

Activities are tied to Groups but not all of them have an associated Group. On my crosstab report, I have Activities in columns while the username, Quantities and a field called as "Today's Productive Hours" in rows.

The users make only 1 entry for "Today's Productive Hours" that is recorded as a single transaction corresponding to an activity and quantity.

The problem is that sometimes the users record their "Today's Productive Hours" against an activity that is not assigned to any Group on the report.

Hence, I cannot display their productive hours on the cross tab report due to this issue.

Is there any way to display their "Today's Productive Hours" field on the same row where I am displaying the Sum of Quantities for each Group even though the data entered for this field is not attached to any activity that falls within a specific group on the displayed columns?

Any help will be greatly appreciated.

Thanks!

Nick

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Brian Dong Oct 17, 2016 at 10:33 PM
0

Hi Nick,

Depending on how complicated the report is, they first place we can look at is how your tables are linked in the report. If there are Activities with no matching Groups, try linking your Activities table to Groups with a Left Outer Join. That should return all the Activities, regardless if they have a Group or not.

Try this in a small simple report with just those two tables and see what happens. Remember, if you add a Record Selection formula, that can force an Equal join and only return Activities with matching Groups.

If the report is very complicated, try creating a view or Stored Procedure in SQL Server that will return the records for you and use that in the report instead of the tables.

Good luck,

Brian

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Oct 20, 2016 at 02:17 PM
0

Hi Brian,

Thank you for your reply. I am currently in the process of creating a SQL stored procedure that will return the records that i want and then later use this to create my report.

Thanks,

Nick

Share
10 |10000 characters needed characters left characters exceeded