Dynamic Crosstab query report

[JoeCruse]

Good day, folks!

I have a question regarding dynamic crosstab query reports. I've successfully put together one of these to report some lab data we regularly generate. I used the example in the Solutions application to do this, and it outputs exactly what I want it to.

My question is, should the speed of these reports be a good deal slower than a regular crosstab query report (fixed column headings)? This thing takes about 30 seconds to run. The underlying query has been set to pull only the top 39 records, and the query runs in a couple of seconds.

The query throws raw screen test data into the dynamic report, and the report performs calculations to show a total of the raw data in each record. This raw data is a series of masses retained on multiple sieves. The report totals up all retained masses in each record, calculates the mass out to a test weight (LBS), and then shows the % mass retained on each sieve. It's pretty simple, but the report is VERY slow. I pulled out all the calculations and ran the report with just what the query was giving it, but gained only a few seconds in output time.

Are these type reports this slow normally? I just changed this report , and the underlying query and tables, to a more normailized structure recently. Before, I had a table with all the test information, plus 8 fields for data from a possible 8 sieves. These 8 fields are repeating and the setup is not normalized. I changed this to a 2 table setup, with a test table and a related results table, in one-to-many fashion. Thus the need for a crosstab query report. I can't set fixed columns, because we use over 50 sieves for all our testing, and have way over 100 possible sieve combinations. The dynamic report fills the bill for this, but I can't believe the report should take 30 seconds to run, given the speed of the underlying query. The old report was set to return all records from the parameters given, not just the top 39, and would churn out a report as long as 70 pages in just a few seconds.

Anyone of you folks have a take on this?

Thank you in advance.

 

[JoeCruse]

More on this issue.

I've played around wih the report more, and made an interesting discovery today. I made a blank database and imported all the modules, reports, forms, queries, and tables associated with this one lab testing method. Of the 19K + records in the main table, I deleted and left only 3k. I ran the report and it spit it out in about 4 seconds, versus the 30+ seconds it took before. I then imported the tables again and ran the report on the full dataset, and report time was 7 seconds, versus the 30+ secinds it took before. I was completely baffled by this. I then deleted the tables and linked them from the backend, which is on a network drive. I ran the report and it took 30+ seconds this time. I unlikned the tables and imported them again and ran the report; 7 seconds to spit it out.

I don't have any experience with these dynamic reports, so this is confusing to me. The underlying crosstab query runs in just a few seconds, whether the tables are part of the database, on my desktop, or are linked from the network drive. I see little difference in speed in the query. It's only in the report.

To cut down on the amount of data for the report, I built the crosstab query using an underlying query on the main table that queries only the top 39 records, in response to query criteria. The crosstab runs off this query, and the report runs off the crosstab. The report is based on the query, and it is also called in the On Open event of the report, just like the example report in the Solutions application does.

Could it be something in the way the On Open event is calling the underlying query? I can't understand why the slowness is caused by the data being called from a back end on a network drive. I do run other crosstab reports based on data in back ends on this same network drive, and they run nice and quick, but they are not dynamic reports.

Here is the code from the On Open event:

Private Sub Report_Open(cancel As Integer)
    ' Create underlying recordset for report by using criteria entered in
    ' fdlgScreenTestReport form.

    Dim intX As Integer
    Dim qdf As QueryDef
    
    
    
       DoCmd.OpenForm "fdlgScreenTestReport", acNormal, "", "", acEdit, acDialog
       If (Not IsLoaded("fdlgScreenTestReport")) Then
            ' user chooses cancel, no report to print
           DoCmd.CancelEvent
           Exit Sub
           
       End If
       
       

    ' Set database variable to current database.
    Set dbsReport = CurrentDb

    ' Open QueryDef object.
    Set qdf = dbsReport.QueryDefs("qryScreenTestReport")

    ' Set parameters for query based on values entered
    ' in fdlgScreenTestReport form.
    qdf.Parameters("Forms!fdlgScreenTestReport!txtproduct") _
        = [Forms]![fdlgScreenTestReport]![txtproduct]
    qdf.Parameters("Forms!fdlgScreenTestReport!txtsize") _
        = [Forms]![fdlgScreenTestReport]![txtsize]

' Open Recordset object.
    Set rstReport = qdf.OpenRecordset()

    ' Set a variable to hold number of columns in crosstab query.
    intColumnCount = rstReport.Fields.count


End Sub

Thanks for any help in advance.

 

[JoeCruse]

Another note on this: I pulled the tables used for this into a new database on my desktop, and then linked those tables to my front end and tried running the report. It took almost 20 seconds to run the report this time. So it runs fastest when the tables are actually part of the front end (7 seconds), then about 20 seconds when linked to a back end on my desk top, and finally 30+ seconds when linked to a back end on a network drive.

It's like the code is having the entire dataset of 19,000+ records (and thousands more sub records in related table) run through.

 

[JoeCruse]

I have gone a different route with this problem, after seeing another person's solution. I have done away with the Solution's dynamic crosstab report and put in a workaround to this. I'll try to find the link where I got the sample app from to do my report and post it here. I got it from Duane Hookum over at the Tek Tips forums.

It involves a little work, but once one figures out how to apply the workaround to one's own situation, it vastly improves the performance of the dynamic crosstab report.

 

[JoeCruse]

This is a link to the site with the example app that I used for a workaround on the dynamic crosstab query report issue:

http://www.invisibleinc.com/divFiles.cfm?divDivID=4

It's the crosstab report download.

Full credit to Duane-dhookum over at the Tek-Tips Forums for this, as it is his solution, and he regularly points folks to it if they have an issue like I had.

It may not be easy to implement, but if you can figure a way to use it, it works well.

Here was my situation: in making a sieve analysis report, the underlying query had 2 tables: a main test table (One) and the actual raw test data table (Many). We have over 100 different test stack combinations for the sieves, and the test stacks can have from 3 to 8 sieves used. If you output all fields in the query, that's a TON of columns! If you make fixed columns, that's over 50 columns because of the number of sieves available for use in testing. No thanks! So this is a GREAT place for a dynamic crosstab query report. After a lot of work, I got the report worked out from the example in MS's Solutions app. Unfortunately, it was slower than Moses. When I was pointed to Duane's workaround, I went to work applying it to my app.

I knew I'd never have more than 8 data columns (from the raw test data) for the report, so following Duane's solution, I assigned a test stack order to each sieve. I did this in a table which is already set up that has the test stack and the corresponding sieves for that test stack in it. For each stack, I assigned the numerical order of the individual sieve in the stack, 1-8. In the crosstab query, I then assigned a set of column headers, 1-8. This returns the query results I needed, and gives me column headers for the report. I did something similar for another query, and used it to build a subreport to be the main report's columns, which show the sieve names.

Like I said, it is a workaround to the problem, but it is adaptable to many situations, and you could make as many columns for data as you need. It's a LOT faster than what I had from my first dynamic report solution.

Good luck to anyone trying to make a dynamic report. I hope this helps you.

 

[Mohsin Malik]

Hi everyone, please help me in making a dynamic query with yearwise column heading, i want to show the column years in sequence, if there is no data for any year ms-access is skipping that year from the query column heading, i want ms-access to dont disturb the sequence and show the column head with blank data (if there is no data). Please review the attachment of query required. Waiting for your kind response, any idea or help? Thanks