Need help with cross-tab report (1 Viewer)

[Lkwdmntr]

Hello,

I have run into a snag with my reporting. I have a complicated report that I can't seem to get to work the way I would like.

I have a Daily Progress by Standard Action report that is made out of several subreports using the query qlkpStandardActionPoints query to pull data for each day.

I couldn't do a cross-tab due to the 6 fields that I need data for each day of the week. I ended up using 7 different sub-reports to get the data, but now I have to do a Total of each row of data, and can't seem to figure it out.

If all the data was in one report, I could probably get the totals, but with them all being in sub-reports, I can't

This is how the report is looking right now, and I need to add another column for the totals.

Any advice on how to turn this into one report with totals for each row?

 

[plog]

It depends on the data. Can you provide a sample database?

In general my recommendation would be not to do 7 subreports (one per day I assume), but to use calculated fields to get every days total:

SundayValue: Iif(Weekday([YourDateField]) = 1, 1, 0)
MondayValue: Iif(Weekday([YourDateField]) = 2, 1, 0)
TuesdayValue: Iif(Weekday([YourDateField]) = 3, 1, 0)
etc.

It's a sort of manual crosstab. It will eliminate the subreports and gets all your day values into one query. Again, though a sample database would help.

 

[Lkwdmntr]

I can send the database. You have to open the MainMenu form, use Ann Smith, and hit Reports. It will default to week 12. Just hit Output. The report will pop up. From there, you can go into the design view and trace through the logic.

My goal is to do some kind of cross-tab, but I need the data for all six fields and the weekly total for each.

 

[plog]

Its too complex for me to breakdown and rebuild into one query. But looking at it, my method should work.

Instead of 7 queries built off qlkpSTandardActionPoints (and 7 subreports) you can accomplish the same thing with just one using the method I described above.

 

[Lkwdmntr]

I'm not sure that would work because sometimes the value in the field is blank, and other times it's 1, and other times it's X.

If anyone else has any input, it would be greatly appreciated.

 

[Lkwdmntr]

Another option would be to add the values from each field in the subreports to get a total in another subreport.

Do you have any ideas on how I could do that? I tried adding the fields together, but I got a type error.

I tried this

=DLookUp("[StartTimeXValue]","[qlkpStandardActionPoints]","[WeelyPlanID]=[TempVars]![TempPlanID] And [DOW]=1 And [Action] = Reports![rptDailyProgressByStandardAction]![subDailyProgressbyStandardAction_1_Mon].Report![Action]")

Just to see if I could get a value. If I could get this to work, I could just add the Dlookups together.

 

[Pat Hartman]

I couldn't do a cross-tab due to the 6 fields that I need data for each day of the week.

The wizard limits you to three but if you open the query in design view after the wizard finishes, you can add whatever you need or join the crosstab to another query with the details.

I would go back to the crosstab. That will allow you to use sorting and grouping for the breaks and allow you to do totals as well.