Programmatically hiding columns in a report? - GOT IT!

[JBlitzen]

Final update: FIXED! See last post.



ETA: Per this post and my next, I'm now at the point where I have a good crosstab SQL query that returns only the columns I want, it looks great. But I want to show that query's output on a report, and doing so seems to require hardcoding the columns and locations. I see a lot of examples that try to solve this but cheat by using months, which they can always show 12 of. I don't have that option, I want to only show 3 columns if there are 3 in the query, and 4 if there are 4. The query works fine, if I could just dump it out programmatically onto a report or a form I'd be fine. Is there any way to do that?

I have a client whose database tracks, let's say, offices. And each office has ten roughly identical numerical fields associated with it, let's say broken out employee counts of each employee type. This wasn't made by me, and isn't normalized, and can't be at this point. And it actually isn't totally illogical given the real data.

The table looks like this:



They would like a report which selects some of those rows (based on unrelated and nondifficult logic). They would like it if the report only shows columns with non-zero data, as follows:



In this case, the Volunteer column (in this example) is not appearing because it would only contain 0 for NY and 0 for Dallas.

The basic report isn't a problem. What is, is hiding the all-zero columns. Or actually any columns, based on whatever logic applies. Let's say I wanted to show that report, but hide the Part Time column because the sum of 3 and 1 is less than, say 6. I know the sum is probably accessible through the DOM from VBA, or even from a separate query within the VBA. But howtf do I hide that column and move Contract left one?

This comes up occasionally, and I've always scratched my head. I can do it easily in real programming languages where I custom draw the output, but Access is sufficiently retarded as to reject everything I try.

It's not wildly important to the client, but I'm curious now.

Any ideas?

[JBlitzen]

Crosstab sounds useful. I've never done one, and it looks like the data would have to be normalized, but I can do that within a query.

select office_name, 'Full Time' as category, employees_full_time as count from office_employees
union select office_name, 'Part Time' as category, employees_part_time as count from office_employees
union select office_name, 'Volunteer' as category, employees_volunteer as count from office_employees
union select office_name, 'Contract' as category, employees_contract as count from office_employees

(or thereabouts) would give:



ETA: This gets interesting. This SQL:

TRANSFORM sum(employee_count.employee_count) AS total_employee_count
SELECT employee_count.office_name, employee_count.employee_type
FROM employee_count
where office_name in ('Dallas', 'New York City')
GROUP BY employee_count.office_name, employee_count.employee_type
order by employee_count.office_name
PIVOT employee_count.employee_type
produces this output:



ETA: Removing employee_type from the select and the group by produces this output:



Which, honestly, puts me right back where I started. So, that was a nice detour.

ETA: Wait! I still had zero records in that table I was using. I copied it to a new table and pulled out the zero records, so the sql changed to:

TRANSFORM sum(employee_count_no_zeros.employee_count) AS total_employee_count
SELECT employee_count_no_zeros.office_name
FROM employee_count_no_zeros
WHERE office_name In ('Dallas','New York City')
GROUP BY employee_count_no_zeros.office_name
ORDER BY employee_count_no_zeros.office_name
PIVOT employee_count_no_zeros.employee_type;


I feel I have found the correct tree and am proceeding to bark up it.

Now, if I pop that query into a report, I get what I expect, three count columns with no Volunteer one. And I can design appropriately.

BUT, the Volunteer column may appear in later runs of the report, thus rendering the earlier design invalid.

So how do I pull that off? The default might be okay, is there a way to just programmatically throw a crosstab query result out onto a report, without having a hardcoded design in place for it?

[JBlitzen]

GOT IT!



I used this guy's instructions and code:

http://www.blueclaw-db.com/report_dynamic_crosstab_field.htm

And made just a few tiny tweaks to it.

For future reference, I created a new report based on the crosstab query. I then deleted the column bind for each of the data fields on the report, so they showed as unbound. I also renamed them to "data_1", "data_2", etc. I then deleted the captions of the header labels (such as "Full Time", "Part Time", etc.) leaving them blank, and set their id's to "label_1", "label_2", etc. And I made sure there were 4, which was the maximum number of columns in my crosstab query. Beyond the first "office name" column, of course.

I then put this code in the VBA code behind:

Option Compare Database

Private Sub Report_Open(Cancel As Integer)
    '   If this code breaks on "DAO.Recordset" or "DAO.Database",
    '   hit the stop button above to stop debugging, select "Tools" on
    '   the menu right above it, select "References", and make sure that
    '   "Microsoft DAO X.Y Object Library" is checked, where X.Y is some version
    '   number that doesn't particularly matter.

    '   Code courtesy of http://www.blueclaw-db.com/report_dynamic_crosstab_field.htm
    Dim rst As DAO.Recordset
    Dim db As DAO.Database
    Dim i As Integer
    Dim j As Integer
    Set db = CurrentDb
    Set rst = db.OpenRecordset("select * from crosstab_try_no_zeros_1")
    rst.MoveFirst
    j = -1
    i = 0
    For i = 1 To rst.Fields.Count - 1
        j = j + 1
        Select Case j
            Case 0
                Me.data_1.ControlSource = rst.Fields(i).Name
            Case 1
                Me.data_2.ControlSource = rst.Fields(i).Name
            Case 2
                Me.data_3.ControlSource = rst.Fields(i).Name
            Case 3
                Me.data_4.ControlSource = rst.Fields(i).Name
    End Select
skip_it:
    Next i
    rst.Close
    Set rst = Nothing
End Sub

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
    '   If this code breaks on "DAO.Recordset" or "DAO.Database",
    '   hit the stop button above to stop debugging, select "Tools" on
    '   the menu right above it, select "References", and make sure that
    '   "Microsoft DAO X.Y Object Library" is checked, where X.Y is some version
    '   number that doesn't particularly matter.
    
    '   Code courtesy of http://www.blueclaw-db.com/report_dynamic_crosstab_field.htm
    Dim rst As DAO.Recordset
    Dim db As DAO.Database
    Dim i As Integer
    Dim j As Integer
    Set db = CurrentDb
    Set rst = db.OpenRecordset("select * from crosstab_try_no_zeros_1")
    rst.MoveFirst
    j = -1
    i = 0
    For i = 1 To rst.Fields.Count - 1
        If rst.Fields(i).Name = "office_name" Then GoTo skip_it
        j = j + 1
        Select Case j
                Case 0
                    Me.label_1.Caption = rst.Fields(i).Name
                Case 1
                    Me.label_2.Caption = rst.Fields(i).Name
                Case 2
                    Me.label_3.Caption = rst.Fields(i).Name
                Case 3
                    Me.label_4.Caption = rst.Fields(i).Name
        End Select
skip_it:
    Next
    rst.Close
    Set rst = Nothing

End Sub

Here's the report design:



Here it is when I run it against NY, Dallas, and LA:



And here's when it's run against just NY and Dallas:



Pain in the ass, but I'll take it. I can easily adapt that to my own needs now.