Results 1 to 14 of 14
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Dynamic CrossTab Report Calculation (A2K SR1)

    I have created a dynamic crosstab report using the code below and it works great. I am having a problem with generating a percentage in the footer section of the report. The report details hours worked by employee and project over time and produces a total of these hours. (This works.) Now, however, users want to generate a calculated field, Percent Overhead, which is the Overhead hours worked for a given month divided by the total hours worked for that month. Seems easy enough but I can't get it to work. (See attached document for an example report output and report design.)

    I tried creating a separate qdf and bringing that into the report but that fails when there is no OH data for the crosstab. I thought about using recordsetclone to point to the OH data but have not gone this route. How would you reference only the OH hours in the code - can you use a where clause - so you can generate the calculation?

    BTW - How could you set null values to zero in the crosstab?

    Any suggestions:

    Code is below:


    <pre> ' Create underlying recordset for report

    Dim intX As Integer
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    Dim intColCount As Integer 'get count of fields selected
    Dim strName As String
    Dim strName1 As String

    On Error Resume Next

    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("QUERY_DataAsSelected_CrosstabMonthl y")

    ' Open Recordset object.
    Set rst = qdf.OpenRecordset()
    intColCount = rst.Fields.Count ' get count of fields selected

    'Get Data for Selected Controls
    For intX = 1 To intColCount - 1
    strName = rst.Fields(intX).Name
    Me.Controls("Col" & intX).ControlSource = strName
    Me.Controls("Sum" & intX).ControlSource = "=Sum([" & strName & "])"
    Me.Controls("Tot" & intX).ControlSource = "=Sum([" & strName & "])"

    If intX > 3 Then
    Me.Controls("Hed" & intX).Caption = strName
    End If

    Next intX

    'Hide Data for Extra Controls not selected
    For intX = intColCount To 15
    Me.Controls("Hed" & intX).Visible = False
    Me.Controls("Col" & intX).Visible = False
    Me.Controls("Sum" & intX).Visible = False
    Me.Controls("Tot" & intX).Visible = False
    Next intX

    rst.Close

    Set rst = Nothing
    Set qdf = Nothing
    Set dbs = Nothing
    Exit Sub
    </pre>

    Regards,

    Gary
    (It's been a while!)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic CrossTab Report Calculation (A2K SR1)

    Your attachement didn't make it yet. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    You can use the Nz function to replace nulls by zero, for example:

    Me.Controls("Col" & intX).ControlSource = "=Nz([" & strName & "],0)"
    Me.Controls("Sum" & intX).ControlSource = "=Nz(Sum([" & strName & "]),0)"
    Me.Controls("Tot" & intX).ControlSource = "=Nz(Sum([" & strName & "]),0)"

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Dynamic CrossTab Report Calculation (A2K SR1)

    Thanks Hans. I tried the Nz function as you suggested but it didn't do anything (Perhaps I dont have any nulls.)

    Here is the attachment again.
    Attached Files Attached Files
    Regards,

    Gary
    (It's been a while!)

  4. #4
    Lounger
    Join Date
    Jan 2003
    Location
    Herndon, Virginia, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic CrossTab Report Calculation (A2K SR1)

    Hi Gary, when I create dynamic crosstab reports, I typically use multiple queries to get all of the information I need into the reports. You might be able to do a query (probably a simple select query, not crosstab) on the original query (possibly) that just pulls out the overhead hours. Then the challenge is mapping the values to where they need to go (since you may not have the same columns for those values). It's tough for me to tell without having a copy of the database.

    The "null" values in the crosstab are harder to deal with because they're not really "null" values from the table, they're places where there isn't anything in the table at all. If you really want to have a zero value show up in the crosstab, you'll need to put zero value placeholders in the table for where there could be a value (I'd recommend using a temporary table for this so you don't mess up your real data). You can do this by putting the data that will go into the crosstab query into a temporary table and then appending zero hour rows for each of the time periods (months) and CLINs. That way, when you create the crosstab, you'll have a value in each place in the crosstab (no nulls) and the totals won't be affected (because you're summing zero into everything.

    Hope this makes sense,

    Brent

    P.S. If you'd like me to take a look at your DB (or a subset of it just containing what's needed for the report), I'd be happy to do that.

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Dynamic CrossTab Report Calculation (A2K SR1)

    Brent,

    Thanks for the response, and yes, it did make sense. I haven't decided on which way to go as of yet.

    Any ideas on the calculation part of the problem?
    Regards,

    Gary
    (It's been a while!)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic CrossTab Report Calculation (A2K SR1)

    Below the line

    <code>Me.Controls("Tot" & intX).ControlSource = "=Sum([" & strName & "])"</code>

    add this line:

    <code>Me.Controls("Perc" & intX).ControlSource = "=Sum(Abs([TYPE]='Overhead')*[" & strName & "])/[Tot" & intX & "]"</code>

    where Perc1, Perc2 etc. are the text boxes that should display the overhead percentage. Don't forget to set the Format property of the Perc1 etc. text boxes to Percent, and the Decimal Places property to the appropriate value.

  7. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Dynamic CrossTab Report Calculation (A2K SR1)

    Hans,

    This is just what I was looking for, but I get the following error when adding the line as suggested:

    The Microsoft Jet Database engine does not recognize '[TYPE]' as a valid field name or expression. <img src=/S/help.gif border=0 alt=help width=23 height=15>

    How do you refer to the data field of "overhead" when using a crosstab? I have been trying to figure just this issue out.

    Thanks for the help.
    Regards,

    Gary
    (It's been a while!)

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic CrossTab Report Calculation (A2K SR1)

    The screenshots you posted (in the Word doc) show a column labeled TYPE that has values "Direct" and "Overhead". I had assumed that TYPE was the field name, apparently it isn't. Replace it by the field in your crosstab query that displays "Direct" and "Overhead". This should be a "fixed" column, so it shouldn't cause problems if you refer to this field by its name.

  9. #9
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Dynamic CrossTab Report Calculation (A2K SR1)

    Hans,

    I had already tried that as I assumed that is what you meant. Let me go back and do it again. ... I am having another one of these brain dead days.
    Regards,

    Gary
    (It's been a while!)

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic CrossTab Report Calculation (A2K SR1)

    It should work - it does on a comparable crosstab report I have. But you need to be very careful, since you're creating expressions in code - the slightest misplacement of a quote or bracket will make the expression fail...

  11. #11
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Dynamic CrossTab Report Calculation (A2K SR1)

    I don't know if this makes a difference, but Direct, Overhead, and other fields are not a fixed column but are also populated when the data is running through the loop. Only the header data for the first three columns of the first row is fixed. Everything else populates and can change based on the data set being processed. Thus, Overhead could be the fifth row in one report, and change to another row in any other reports.
    Regards,

    Gary
    (It's been a while!)

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic CrossTab Report Calculation (A2K SR1)

    It doesn't matter whether Overhead is in the first, fifth or thirty-seventh row, as long as it is in the same column. What are the first three columns?

  13. #13
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Dynamic CrossTab Report Calculation (A2K SR1)

    Hans,

    I got it. Sorry for being a pain. As you said, I checked the names of everything and I had changed the name of the data from the query in code. Once I reverted back to the original name as fed from the data source (in this case DirectLvl2 instead of Type) the percentage worked as shown.

    Thanks for your help and patience.
    Regards,

    Gary
    (It's been a while!)

  14. #14
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Dynamic CrossTab Report Calculation (A2K SR1)

    Hans,

    I finished the report this morning and it works like a charm. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

    Thanks again for all of your help.
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •