Pivot Table into Report

I'm trying to generate a weekly report for management that summarizes Help 
Desk activity.  I'm using Access DB that links to a CSV, generated from the 
support system (SQL not accessible).

My problem is getting the figures I can generate in the Pivot Table into a 
form I can just pull up without further editing.  I've already separated out 
by type of request (Break/Fix and Service Request), generating separate 
queries.

What is requested are stats for various service queues(rows), by status 
(columns), grouped in specific ways; some queues are not grouped, some need 
to appear separately, others are dropped into a 'misc' group.  status, too, 
is grouped.  This can be accomplished by a PivotTable.  If I could copy and 
pasts, I'd be done, but I can only copy off the numbers. 

*- If I try to report on a Crosstab query, I cannot seem to perform the 
custom grouping I need without further editing.
*- I'm thinking I can requery on the crosstab, and do my groupings there, 
but I don't know how to break up the fields.

**Any ideas on which direction to go are appreciated.  


Here's a genericized sample query:
[sorry if you're not reading this fixed-pitched]
Pivot Table:

Queue                   Status
                        Open (Grouped)  Closed (Grouped)  TOTAL 
                                                                
Queue 1                       3               17           20   
Queue 2                                        2            2   
Queue 3                       1                6            7   
Grouped (3 queues)                            11           11   
Misc. (variable #)            3                7           10   
-------------------------------------------------------------------
GRAND TOTAL                   7               43           50


CrossTab:


Queue                   Status
                        Assigned  In_Prog  Pend  Closed  Resolved  Cancelled 
 TOTAL 
Queue 1                                             1                        
    1  
Queue 2                                             1                        
    1  
Queue 3                                             1                        
    1  
Queue 4                    1                 2      5       12               
   20  
Queue 5                                             2                        
    2  
Queue 6                              1                       6               
    7  
Queue 7                    1                                                 
    1  
Queue 8                                                      1               
    1  
Queue 9                                      2                               
    2  
Queue 10                                            4        7               
   11  
Queue 11                                                     1               
    1  
Queue 12                                            1                        
    1  
Queue 13                                                     1               
    1  

0
Utf
12/8/2009 9:10:01 PM
access 16762 articles. 3 followers. Follow

1 Replies
708 Views

Similar Articles

[PageSpeed] 33

I would model all of this in tables with values that identify which queues 
are grouped together etc. Then create a crosstab for your report.

-- 
Duane Hookom
Microsoft Access MVP


"Scott Kolesar" wrote:

> I'm trying to generate a weekly report for management that summarizes Help 
> Desk activity.  I'm using Access DB that links to a CSV, generated from the 
> support system (SQL not accessible).
> 
> My problem is getting the figures I can generate in the Pivot Table into a 
> form I can just pull up without further editing.  I've already separated out 
> by type of request (Break/Fix and Service Request), generating separate 
> queries.
> 
> What is requested are stats for various service queues(rows), by status 
> (columns), grouped in specific ways; some queues are not grouped, some need 
> to appear separately, others are dropped into a 'misc' group.  status, too, 
> is grouped.  This can be accomplished by a PivotTable.  If I could copy and 
> pasts, I'd be done, but I can only copy off the numbers. 
> 
> *- If I try to report on a Crosstab query, I cannot seem to perform the 
> custom grouping I need without further editing.
> *- I'm thinking I can requery on the crosstab, and do my groupings there, 
> but I don't know how to break up the fields.
> 
> **Any ideas on which direction to go are appreciated.  
> 
> 
> Here's a genericized sample query:
> [sorry if you're not reading this fixed-pitched]
> Pivot Table:
> 
> Queue                   Status
>                         Open (Grouped)  Closed (Grouped)  TOTAL 
>                                                                 
> Queue 1                       3               17           20   
> Queue 2                                        2            2   
> Queue 3                       1                6            7   
> Grouped (3 queues)                            11           11   
> Misc. (variable #)            3                7           10   
> -------------------------------------------------------------------
> GRAND TOTAL                   7               43           50
> 
> 
> CrossTab:
> 
> 
> Queue                   Status
>                         Assigned  In_Prog  Pend  Closed  Resolved  Cancelled 
>  TOTAL 
> Queue 1                                             1                        
>     1  
> Queue 2                                             1                        
>     1  
> Queue 3                                             1                        
>     1  
> Queue 4                    1                 2      5       12               
>    20  
> Queue 5                                             2                        
>     2  
> Queue 6                              1                       6               
>     7  
> Queue 7                    1                                                 
>     1  
> Queue 8                                                      1               
>     1  
> Queue 9                                      2                               
>     2  
> Queue 10                                            4        7               
>    11  
> Queue 11                                                     1               
>     1  
> Queue 12                                            1                        
>     1  
> Queue 13                                                     1               
>     1  
> 
0
Utf
12/9/2009 10:32:11 PM
Reply:

Similar Artilces:

Pivot tables in Excel
Yippee, I am so happy, I finally figured out how to trick excel into not showing the blank cell in a pivot table without limiting my data set! In the Pivot table layout move the field from row to page. Then double click on the field and click on the "blank" to hide it. Move the field back to row and hit finish. It seems like you always have to "trick" MS software to do what you want. ...

Outlook offline reports not available
I have setup most of my CRM 4 users with the Outlook client, which seems to work considerably better than that in CRM 3. However, when the clients are traveling and unable to attach to the Internet, the reports are not available. Is there any way to make the CRM reports available offline. The SQL database is loaded on each of the user PCs and everything else seems to be working just fine. Any assistance will be appreciated. -- Petej-Boston It is working on our end ok. I would try the following: 1. Setup a test client with a CRM Administrator login and install the 4.0 CRM Outlook ...

NDR Report
We get this NDR error when someone sends to a particular address: 550 Only 1 recipients accepted with null sender address Again, the emails are sent using Outlook to a listing in our GAL for the Custom Recipient. I have checked the outgoing messages and they do contain a sender address. Any ideas? Thanks. BSchmidt Have you contacted the recipient's admin folk on this? BSchmidt wrote: > We get this NDR error when someone sends to a particular address: > > 550 Only 1 recipients accepted with null sender address > > Again, the emails are sent using Out...

IWAN & IUSR bei Crystal Reports? *help*
Hi NG, my problem is that i can see the crystal reports, but when i open one i get "more information is needed". But till yesterday i could open reports!! Now after some search i see that i have no IUSR_servane and no IWAM_servername. how could this happen? My system is AD, SQL, CRM and each of them is one a seperate 2003 server. Please let me know if you have any suggestions. Regards Nicolas F�hrs sound strange with the IWAN and IUSR. I offten have this problem. There are a techknowledge article with 13 resoluti...

Pivot Table formatting #6
When I format my pivot tables I have "preserve formatting" checked under "PivotTable Options", and "Autoformat Table" unchecked. Even so, I either lose portions of my formatting, or different formatting is applied when I refresh. Does anyone know what I am doing incorrectly or have any other suggestions? Thanks, Phil Other things to try -- if they don't work, you could record a macro as you refresh and reformat the pivot table. Then, run that when you want to update. --Instead of selecting the cells to format the numbers, right-click the field but...

Count unique values
Hi, I've some problems to count unique items (Invoice #) in a pivot table. There is the default solution "Add-a-new-calculated-column" as mentioned on http://contextures.com/xlPivot07.html#Unique, but in my case, it doesn't works. My problem is, that these values aren't in a Excel worksheet; it's a external data source - a SQL-Select via DAO/ADO. Because of that, I don't have the possibility to add a new column. Another reason is, that the pivot table should always be dynamic: Group over this field, group over another field - and always show the number of unique...

Custom Item Movement Report
Anyone out there have a custom item movement report and item movement history report that includes extended description that they would be willing to share? Thanks! -JIM That was a pretty tricky request but I was able to make the change. Download the reports here: http://www.newestech.com/downloads/itemmovement.zip If you cannot download from there shoort me an email and I will send over. Check out our RMS add-ons website in exchange.. http://www.newestech.com/POS/extensions.htm Casey Hanson New West Technologies chanson@newestech.com Hi, Can you do one with "Transferred To"...

Passing a variable as a parameter value instead of a literal string to a child report?
In the Navigation section (Jump to URL), I have the following: ="javascript:void(window.open('http://localhost/reportserver?/Reports +Folder/My +Report&rs:Command=Render&StartDate=01/01/2009&FinishDate=12/31/2010'))" This works as designed - a new window pops up with the "My Report" report and the StartDate and FinishDate of 01/01/2009 and 12/31/2010 are passed respectively. But what is the syntax for changing the "01/01/2009" and "12/31/2010" to variables that point to the StartDate and FinishDate parameters of the PAR...

Print Report "Bold" Font Category Name Problems
When I print income/expense reports in MS Money 2006 the category names do not print in bold, making them very difficult to read since they end up as the same font as the sub categories. On the screen, category names are bolded. Does anyone know how to fix this print problem? ...

pivot
How to take a copy of data on the web to a pivot tabel? ex. take a copy of ford and fiat cars in bilbasen.dk... HOW!? Import the data onto a worksheet (Data>Import External Data> New Web Query) Then, create a pivot table from the imported data Louise wrote: > How to take a copy of data on the web to a pivot tabel? ex. take a copy of > ford and fiat cars in bilbasen.dk... HOW!? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html Hi. thanks.. but I already tried it. when I click the import button, to my selected "zone&qu...

Z Report shows zeros for an entire shift
How do I correct the problem described above? All receipts are printed correctly, and all transactions seem to be successful. However, the Z report that is run at the end of the shift prints all zeros? Any ideas??? I am having the same problem. All the transactions are successfull and I can see the sale amounts and etc at crstal reports but all the z, zz and x reports print "$0.00" amount. Does anyone know how to solve this problem? "Maakus" wrote: > How do I correct the problem described above? All receipts are printed > correctly, and all transactions seem t...

From temporary table to master table and then join this in junction table
Hello, I have imported two spreadsheets from excel to two temporary tables called "import_tbl_contract" and "import_tbl_products". I do this on daily basis and import many spreadsheets. I end up with these data: [import_tbl_contract] contract_number contract_title start_date end_date [import_tbl_products] product_number product_text price I now append these data to the following two tables: [tbl_contract] contract_id (PK) contract_title start_date end_date e.g. 1; Syringes and needles; 01-01-2010; 31-12-2010 [tbl_products] product_id ...

Form for table
Here's the problem: Access 2003, XP Pro- I have three tables that are related by primary keys. I have a table[Table QC] that has the field [EmployeeID], the data type is number--that field is linked to the autonumber primary key of the Employees Table. So far so good. I am trying to make a form that has a combo box that looks to the Employee Table to get me the drop down of the employees (Last Name, First Name). The SQL statement builds out okay, and I get the correct name config., but because the data type is a number it won't accept the data. I looked at the Northwind example and i...

Can I set my custom views on Pivot table
I want to get Pivot table data on my invoice, is it possible to set custom views so that I can get Pivot table results on invoice format, I've tried views that offer excel but it doesn't offer custom views please help Thanks ...

List Running Horizontally-Pivot Table Possible?
I just had a customer ask this one and I'm not so sure about it. 1) The customer has setup a list of information that is running horizontally instead of the typical list running vertically. 2) I just taught him how to do pivot tables and he wants to generate a pivot table with his "horizontal" list. 3) I know he can copy and paste special and use the Transpose feature to change the list from horizontally oriented to vertically oriented. 4) the question is: can he leave the table in its original orientation and still generate Pivot tables? I can't seem to make it work ...

Z Report Customer Count vs Hourly Sales
My Z report shows customer count of 380 and hourly sales total of 384. Why the difference? This happens all the time. Thanks Your glass is: Half-Empty : Do folks at register forget to ask "Is there anything else I can help you with?" Half-Full: Your impulse items at POS are working -- better late than never! sammy wrote: > My Z report shows customer count of 380 and hourly sales total of > 384. Why the difference? This happens all the time. > Thanks -- ______________________________________________________ Larry Leveen OlyBikes Locally-Owned B...

Reporting Services not installed
Hi all, I have installed crm and reporting services but when I go into a report, display it and then hit print I get an popup box saying.... "Do you want to install this software? Name: Microsoft SQL Server Reporting Services" Can anyone help please Thanks Jools There's an ActiveX control each client machine needs in order to print SQL Reports from CRM. It's a one-time 5-second install. -- Matt Wittemann, CRM MVP http://icu-mscrm.blogspot.com "Jools" wrote: > Hi all, > > I have installed crm and reporting services but when I go into a report, ...

MVP PLESE; Report Manager
I did all required in installation and when the Report Manager comes up I get no options either under "view or scenario", All I see is "none". No selection in the drop down for any of the 6 out of 20 columns I want to print. Is my Report Manager corrupted? I just d/l it today from the MS site. Running Excel 2002. It's so easy in MS Works. ...

Pivot table and organizing data
This one is really making me scratch my head. Here is the story. I have a list of information which I am pulling in via a query from SQL. Data is good and it correctly comes into Excel (03 or 07). I have five columns with data: Date, Time, AccountID , Status. First two are self-explanatory; third is a 3-letter ID, forth is a status (pass/fail). Ok, now that you have an idea, here is what I need: 1. List the account IDs as a column 2. List the dates as rows 3. Place the alert into the location that corresponds to the appropriate data and account I know this 'sounds' like a strai...

Reporting in POS2009
I have installed pos2009 on XP machine and unable to get the standard reports that are included in the product to function properly. For example on the main screen of the managers view it shows me daily sales / top items / sales per cashier / etc. When I go into reports to look at the report for daily sales it comes up totally blank - indicating no data to report. I have checked to make sure there are no filters applied. All the reports behave the same way. Otherwise pos2009 appears to be functioning normally. Any troubleshooting tips would be appreciated. EggHeadCafe - Software ...

"Error on page" and error report on entity close
Greetings, We have CRM 4.0 rollup 6 implemented from a CRM 3.0 upgrade about a month ago. Recently, my users have been reporting an error report being generated when they try to close (or save & close) any entity they have open. The entity is closed (or saved and closed), and I have asked them not to send the error report, but of course this needs to be resolved. I have noticed that once the record is open, "error on page" will appear in the bottom left of the dialog if you will receive the error report dialog, which doesn't happen every time. Also, if I do an IIS...

CRM Crystal Reports Uniqueidentifier
Dear all, I have been customising the paramaterise reports for MS CRM so that site managers can view their child users reports... but I hit the wall as we speak because I cannot get Crystal Reports to query the database with the other users' uniqueidentifier GUID... If I put in the currently logged in user GUID into the report for querying, it seemed to work... other than that it does not... >From the Opportunity List by Account (Specify Owner...) report from Microsoft, I changed the Selection Formula under the Account from (account.ownerid) = (?UserId) to (account.ownerid) = (?Chi...

DCount on Report A2007
I have a TexxBox on a report which returns an event date or, if no date, returns "Pending". I have called the TextBox "txtEvent". =IIf([EventDate] Is Null,"Pending",[EventDate]) No Problem here. Now I want to count the "Pending" =DCount("[txtEvent]","MyReportName","[txtEvent] ='Pending'") This returns "Error" Any suggestions? Thanks in advance. On Wed, 19 Mar 2008 06:16:24 +0200, Tom Ventouris wrote: > I have a TexxBox on a report which returns an event date or, if no date, > returns "...

Bug in Grid Lines in CListCtrl Report View
Hi, This thing is again annoying me! whenever I display the grid lines using LVS_EX_GRIDLINES , there are problems when scrolling the CListCtrl at once , the grid lines seem to loose their position and thus generating a blurred view or sometimes just disappear temporarily. Have anyone else faced the same thing ? Thanks >whenever I display the grid lines using LVS_EX_GRIDLINES , there are >problems when scrolling the CListCtrl at once , the grid lines seem to loose >their position and thus generating a blurred view or sometimes just disappear > temporarily...

Show all months on report
I have a report that shows total shipments by month for specific items by totaling in the Month Footer. Some items have no shipment on some months so that month is not listed. Is there a way to have the report show all months regardless if there were shipments that month or not? Create a table name CountNumber with field CountNUM having integers from 0 (zero) through you maximum spread. Build the query below. SELECT DateAdd("m",[CountNUM],CVDate([Enter start date])) AS [My Dates] FROM CountNumber WHERE (((DateAdd("m",[CountNUM],CVDate([Enter start date])))<=CVDa...