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

[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

[former_member292966 · Accepted solution]

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

[Former Member]

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