Dynamic Crosstab Report

bdb04 · #1 (**permalink**) 08-28-08, 14:16

I have a crosstab query which feeds a report that shows productivity for employees for the week. The way I have been asked to set it up is that on Monday, only Monday's results will show but the dates for every day of that week show on the report with no results displayed. I have worked that part out and don't mind changing my column headers each week for the Monday-Friday dates.

Where I am stuck is how to pass those column headings to the reports so I am not basically redoing all of the calculated fields in the group footers every week. I really would be OK with manually changing the column headers in the report each week if no way around that, but there are about 15 calculated fields in the footers which I would have to reenter formulas in and I would prefer not to do that.

Any ideas on how to do that? I have searched long and hard to no avail.

Thanks,
Ben

StarTrekker · #2 (**permalink**) 08-28-08, 21:25

Quote:

I have searched long and hard to no avail.

Really?

Google "dynamic crosstab report"

The top TEN (at least) links explain how to create reports based on crosstab queries!

Here's just one:
http://support.microsoft.com/kb/328320

Have a poke around and see what you can figure out. The first time I did this I was kinda discouraged by the seeming amount of work to do, but once you have done one, it doesn't seem all that bad.

bdb04 · #3 (**permalink**) 08-29-08, 10:41

OMG, I read that exact article twice yesterday trying to figure it out and didn't look at it deeply enough. Now that I have actually had some sleep, it seems like that should work with some tinkering.

FYI, that was the exact google search I did, but I got mostly SQL server stuff that on quick glance didn't seem to be applicable...maybe I should reread those as well.

Thanks, and I will post back if I still am stuck.

StarTrekker · #4 (**permalink**) 08-30-08, 00:31

Good luck!

bdb04 · #5 (**permalink**) 09-02-08, 16:30

Well, I am getting closer! I have modified the code in the link you mentioned to get the report very close to what I need. What I have still not been able to figure out is how to create a group level by what is column 1 in the query.

If I add a bound control to the group header, it displays correctly, but it won't sum columns 5-9 in my group footer. I have =sum([Col5]) in an unbound text box in the group footer, but it is not recognizing the values in Col5 in the detail section.

I have looked at the CreateGroupLevel method but I can't seem to grasp how to reference the unbound control, Col1, in order to group it.

Any further thoughts are greatly appreciated!

pkstormy · #6 (**permalink**) 09-02-08, 19:12

Not sure if this will help but here is a crosstab report example I posted in the code bank:

DBForums Code Bank

bdb04 · #7 (**permalink**) 09-02-08, 19:34

Thanks for the reply. I had actually looked at that post before I ever did my first post, but it really didn't solve what I am trying to accomplish here, namely create a crosstab report that uses parameters to build the report crosstab data and column headings.

I now have that part worked out, but I am still trying to figure a way to get the grouping worked out. I guess to give a better example:

Group Header: Manager
Detail: Employees and their stats by day
Group Footer: Summary data for the manager's group

The code provided here has been modified so that it works with my data other than getting the grouping figured out.

Thanks and am always willing to look at other ideas on this!

Ben

pkstormy · #8 (**permalink**) 09-02-08, 19:45

One thing you may consider (although I can't say if this will work with your situation but it's something I've done in the past)...

1. Do a make-table query from the crosstab query making a "temp" table and then base the report's recordsource on that "temp" table (although with "new" fields in the crosstab, this might be a bit difficult.) But I've also been somewhat successful have a "temp" table with certain fields established and then using an append crosstab type query, append to that "temp" table. The "temp" table for example might have "Column1", "Column2", etc...for field names versus actually fields. It get's a little complex but I've had a "temp" table configured this way (and also had fields in the "temp" table which would be the "header" type names (ie. "Column1HeaderName", "Column2HeaderName", etc..) for the report.) It's difficult to explain though and I'll try to find the example on how I did this.

Without seeing what exactly you're trying to do though, it's a little difficult. If you post the mdb, it might be a little easier to come up with a few other ideas.

bdb04 · #9 (**permalink**) 09-02-08, 20:20

I thought about the temp table route, but then I started to think that I would have to change all of the code that I got working so far. Here is what I have. The old, hard-coded variables are in the query/report without the _New at the end. It is a copy of the actual production data, but all names have been changed to protect the innocent.

You can see the grouping I am trying to get from the old reports and you can see where I am stuck in the _New reports. Hopefully, you can also see why the first option is a maintenance nightmare which is why I want to move to the second.

Thanks again for your help!

pkstormy · #10 (**permalink**) 09-02-08, 22:17

I downloaded your post but you'll need to give me a day or two to look at it. Since I just started a new job, I've got to familiarize myself with about 3 dozen new mdb's rather quickly but I'll try to come up with some suggestions for you soon.

bdb04 · #11 (**permalink**) 09-03-08, 10:10

Ugh, been there, but not with that many new DBs to wade through! Any help you can provide, whenever you can provide it is appreciated. I will continue to play with it and will let you know if I get any closer or actually figure it out.

Thanks!
Ben

StarTrekker · #12 (**permalink**) 09-03-08, 11:22

Just looking quickly at this....

Col5 to Col10 can't be summed by direct references because they are unbound. I think you should bind by modifying the control source of them rather than stick the values in them.

BTW, your code:

Code:

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
    
   Dim intX As Integer
    
   '  Put column headings into text boxes in page header.
   For intX = 1 To intColumnCount
      Me("Head" + Format(intX)) = rstReport(intX - 1).Name
   Next intX

   '  Make next available text box Totals heading.
   Me.Controls("Head" + Format(intColumnCount + 1)).ControlSource = "=""Totals"""

   '  Hide unused text boxes in page header.
   For intX = (intColumnCount + 2) To conTotalColumns
      Me("Head" + Format(intX)).Visible = False
   Next intX

End Sub

Has a flaw... if the board is full there is no room for the total column.

I think the For intX = 1 To intColumnCount part should read For intX = 1 To intColumnCount - 1. This would make sure there is room to make a column for totals.

bdb04 · #13 (**permalink**) 09-03-08, 11:45

Quote:

Col5 to Col10 can't be summed by direct references because they are unbound. I think you should bind by modifying the control source of them rather than stick the values in them.

I messed around with that idea for a couple of hours yesterday, and I couldn't figure out how to get the bound reference into the group footer. I like that idea though and I will play with it again to try that.

As for the other part, I don't need row totals, only column totals. However, the code was working as it was and I was afraid to revise it too much at this point so I just hid the row total field in the detail so it wouldn't blow up. I figured that once I got everything working, I would go back and tidy things up.

Thanks for confirming my suspicions about summing the unbound controls. I thought that was what was happening, but confirmation that I am not going completely nuts is always welcome!

bdb04 · #14 (**permalink**) 09-11-08, 15:52

Well, I've tidied up the data side of things hoping that would help, but I still can't figure out how to get the summary data into the group footer. If I go totally bound, I am stuck with changing all of the field names in the footers each week, and I guess I am just missing the boat on how to get it "semi-bound".

Any ideas greatly appreciated! I am attaching an updated version.

Thanks again for any input.

Ben

Goreto · #15 (**permalink**) 02-20-13, 02:58

Quote:

Originally Posted by pkstormy

Not sure if this will help but here is a crosstab report example I posted in the code bank:

dBforums - View Single Post - DBForums Code Bank

I downloaded your Example Database, and It run perfectly on my computer. I made an exact same report on my database, I created a Crosstab query just like yours, but when I try to open the report I get the following message: "You can't use pass-through query on a non-fixed-column crosstab query as a record source for a subform or subreport. Before you bind the subform or subreport to a crosstab query, set the query's ColumnHeadings property."

I tried doing this, but my columns are not static, they change depending on the range of dates I select. So my columns are dynamic.

Any ideas how to fix this problem?

Thanks.

Internet Services

Web Development

Digital Marketing

Consumer Tech