Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Group by Month in Pivot-table
Hi All,
I understand how to group data when it's transferred to Excel as pivot- table. However I was wondering if it's possible to group before the data is returned to excel (i.e. from the wizard directly up-front). The problem is that I'm trying to query, externally via ODBC, in a pivot-table to simply group the number of cases in our incident system by Month/Year. I know I could do this in Access as well, however this isn't standard on our client build so need a way to do this in Excel. However the number of records is now exceeding 70,000. As I only want month/year and a count of the cases by 'Date Created' field I was wondering if you can do this directly within the wizard so Excel can return/display this successfully within the pivot-table? If not, does anyone know how to only return this 'summed' view of data within a MS Query so automatically I'd only get the Month/Year and count of Date Created field? Thanks in advance, Al. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Group by Month in Pivot-table
I am a little lost here. If your pivot table is hooked directly back to your
ODBC data source then you are no longer bound to 65k records. The pivot cache is only bound by memory. I have done pivot talbles with more than a million records. -- HTH... Jim Thomlinson " wrote: Hi All, I understand how to group data when it's transferred to Excel as pivot- table. However I was wondering if it's possible to group before the data is returned to excel (i.e. from the wizard directly up-front). The problem is that I'm trying to query, externally via ODBC, in a pivot-table to simply group the number of cases in our incident system by Month/Year. I know I could do this in Access as well, however this isn't standard on our client build so need a way to do this in Excel. However the number of records is now exceeding 70,000. As I only want month/year and a count of the cases by 'Date Created' field I was wondering if you can do this directly within the wizard so Excel can return/display this successfully within the pivot-table? If not, does anyone know how to only return this 'summed' view of data within a MS Query so automatically I'd only get the Month/Year and count of Date Created field? Thanks in advance, Al. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Group by Month in Pivot-table
Al,
Just to clarify, you are returning rows from an external data source to XL, and then you have a pivot table based on those rows? What is your external data source? MS Access? SQL Server? Other? I don't think it is possible to do what you are asking by using the Query Wizard, but if you can open MS Query, copy your SQL statement, and paste it here, I *MIGHT* be able to come up with a SQL statement that will do what you are asking. Just let me know what fields/columns you want to group by, which ones you want to count, which ones to sum, etc... HTH, Conan wrote in message ... Hi All, I understand how to group data when it's transferred to Excel as pivot- table. However I was wondering if it's possible to group before the data is returned to excel (i.e. from the wizard directly up-front). The problem is that I'm trying to query, externally via ODBC, in a pivot-table to simply group the number of cases in our incident system by Month/Year. I know I could do this in Access as well, however this isn't standard on our client build so need a way to do this in Excel. However the number of records is now exceeding 70,000. As I only want month/year and a count of the cases by 'Date Created' field I was wondering if you can do this directly within the wizard so Excel can return/display this successfully within the pivot-table? If not, does anyone know how to only return this 'summed' view of data within a MS Query so automatically I'd only get the Month/Year and count of Date Created field? Thanks in advance, Al. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Group by Month in Pivot-table
Hi Conan,
Appreciate your help. Query is below: SELECT "Request"."Case ID", "Request"."Create Date" FROM "Request" "Request" This query is 'auto' created based on manually just selecting the fields within MS Query. Thanks for any help you can give. Al. On 19 Feb, 20:43, "Conan Kelly" wrote: Al, Just to clarify, you are returning rows from an external data source to XL, and then you have a pivot table based on those rows? What is your external data source? *MS Access? *SQL Server? *Other? I don't think it is possible to do what you are asking by using the Query Wizard, but if you can open MS Query, copy your SQL statement, and paste it here, I *MIGHT* be able to come up with a SQL statement that will do what you are asking. Just let me know what fields/columns you want to group by, which ones you want to count, which ones to sum, etc... HTH, Conan wrote in message ... Hi All, I understand how to group data when it's transferred to Excel as pivot- table. *However I was wondering if it's possible to group before the data is returned to excel (i.e. from the wizard directly up-front). The problem is that I'm trying to query, externally via ODBC, in a pivot-table to simply group the number of cases in our incident system by Month/Year. *I know I could do this in Access as well, however this isn't standard on our client build so need a way to do this in Excel. However the number of records is now exceeding 70,000. As I only want month/year and a count of the cases by 'Date Created' field I was wondering if you can do this directly within the wizard so Excel can return/display this successfully within the pivot-table? If not, does anyone know how to only return this 'summed' view of data within a MS Query so automatically I'd only get the Month/Year and count of Date Created field? Thanks in advance, Al.- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Group by Month in Pivot-table
Al,
In your original post you mentioned "the number of records is now exceeding 70,000". Are you returning records to XL and then creating a pivot table on those records? If that is the case, you have an unnecessary step in there. You do not need to return records to XL in this case. Cut out the middle-man. Create your pivot table on an external source. When you do that, it will take you through the exact same process as if you were creating an external query. But I will adjust your SQL statement so that it MIGHT work with MS Access (you didn't tell me which DB system you are working with). Adjusted SQL Statements are below. In the following query, a new field is calculated. This new field is 'MBDate' (Month Begin Date). The MBDate is set to the first day of the month of "Create Date" for each record. Then this query is "Grouped By" this MBDate and counts the "Case ID" field...in other words, one record is returned for each MBDate with a count of Case ID's that have the same MBDate (counts all records in the same month): SELECT dateserial(year("Request"."Create Date"),month("Request"."Create Date"),1) AS 'MBDate', Count("Request"."Case ID") FROM "Request" "Request" GROUP BY dateserial(year("Request"."Create Date"),month("Request"."Create Date"),1) The following query is the exact same as the previous one except, I used the last day of the month (MEDate--Month End Date) instead of MBDate: SELECT dateserial(year("Request"."Create Date"),month("Request"."Create Date")+1,0) AS 'MEDate', Count("Request"."Case ID") FROM "Request" "Request" GROUP BY dateserial(year("Request"."Create Date"),month("Request"."Create Date")+1,0) In the results, you can change the format of the date cells to "mmm yyyy" (or "mm-yyyy", "yyyy-mm", "mmm-yyyy", "mmmm yyyy", etc....what ever suits your needs) so that it will display just the month and year. I did test these in an external DB query to Access and it did work, although my syntax was slightly different. I hope these will work for you. HTH, Conan wrote in message ... Hi Conan, Appreciate your help. Query is below: SELECT "Request"."Case ID", "Request"."Create Date" FROM "Request" "Request" This query is 'auto' created based on manually just selecting the fields within MS Query. Thanks for any help you can give. Al. On 19 Feb, 20:43, "Conan Kelly" wrote: Al, Just to clarify, you are returning rows from an external data source to XL, and then you have a pivot table based on those rows? What is your external data source? MS Access? SQL Server? Other? I don't think it is possible to do what you are asking by using the Query Wizard, but if you can open MS Query, copy your SQL statement, and paste it here, I *MIGHT* be able to come up with a SQL statement that will do what you are asking. Just let me know what fields/columns you want to group by, which ones you want to count, which ones to sum, etc... HTH, Conan wrote in message ... Hi All, I understand how to group data when it's transferred to Excel as pivot- table. However I was wondering if it's possible to group before the data is returned to excel (i.e. from the wizard directly up-front). The problem is that I'm trying to query, externally via ODBC, in a pivot-table to simply group the number of cases in our incident system by Month/Year. I know I could do this in Access as well, however this isn't standard on our client build so need a way to do this in Excel. However the number of records is now exceeding 70,000. As I only want month/year and a count of the cases by 'Date Created' field I was wondering if you can do this directly within the wizard so Excel can return/display this successfully within the pivot-table? If not, does anyone know how to only return this 'summed' view of data within a MS Query so automatically I'd only get the Month/Year and count of Date Created field? Thanks in advance, Al.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Group in Pivot Table | Excel Discussion (Misc queries) | |||
Pivot table - group dates per week or month | Excel Discussion (Misc queries) | |||
Can't group pivot table items by month in Excel | Excel Discussion (Misc queries) | |||
Pivot Table - no group on web | Excel Discussion (Misc queries) | |||
Pivot Table - Group by Month - Show Items with no data | Excel Discussion (Misc queries) |