Hi,
Am using robvolks crosstab-procedure to generate a crosstab query.
I get this result:
Total A B C
juli 455 1 107 347
okt 83 1 9 73
aug 612 1 113 498
juni 451 1 108 342
So I get a total for each month. But I would also like a total of each
letter
Total A B C
juli 455 1 107 347
okt 83 1 9 73
aug 612 1 113 498
juni 451 1 108 342
Total 1601 4 337 1260
Is that possible?
/jim
---call to procedure
execute crosstab 'select DATENAME(month, (theDate)) as '' '', count(*) as
'MonthsTotal'' from tblData group by
DATENAME(month, (theDate))','co unt(letter)','l etter','tblData '
-----------Robvolks procedure----
CREATE PROCEDURE crosstab
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100),
@where varchar(1000)=' 1=1'
AS
DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET LANGUAGE Danish
EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE
1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table +
' WHERE '
+ @where + ' AND ' + @pivot + ' Is Not Null')
SELECT @sql='', @sumfunc=stuff( @sumfunc, len(@sumfunc), 1, ' END)' )
SELECT @delim=CASE Sign( CharIndex('char ', data_type)+Char Index('date',
data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.informat ion_schema.colu mns
WHERE table_name='##p ivot' AND column_name='pi vot'
SELECT @sql=@sql + '''' + convert(varchar (100), pivot) + ''' = ' +
stuff(@sumfunc, charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar (100), pivot) + @delim + ' THEN ' ) + ', ' FROM
##pivot
DROP TABLE ##pivot
SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@ select, charindex(' FROM ', @select)+1, 0, ', ' + @sql
+ ' ')
EXEC (@select)
SET ANSI_WARNINGS ON
GO 3 1790
Jim Andersen (jb****@politiS LET.dk.invalid) writes:
Am using robvolks crosstab-procedure to generate a crosstab query.
I get this result:
Total A B C
juli 455 1 107 347
okt 83 1 9 73
aug 612 1 113 498
juni 451 1 108 342
So I get a total for each month. But I would also like a total of each
letter
Total A B C
juli 455 1 107 347
okt 83 1 9 73
aug 612 1 113 498
juni 451 1 108 342
Total 1601 4 337 1260
Is that possible?
You would use INSERT EXEC to capture the result from the crosstab
procedure into a temp table, and then compute a total row from the
data in it.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
SELECT month_name,
COUNT(*) AS tot,
SUM(CASE WHEN foo = 'A' THEN 1 ELSE 0 END) AS tot_a,
SUM(CASE WHEN foo = 'B' THEN 1 ELSE 0 END) AS tot_b,
SUM(CASE WHEN foo = 'C THEN 1 ELSE 0 END) AS tot_c
FROM Foobar
GROUP BY month_name
UNION ALL
SELECT 'All months',
COUNT(*) AS tot
SUM(CASE WHEN foo = 'A' THEN 1 ELSE 0 END) AS tot_a,
SUM(CASE WHEN foo = 'B' THEN 1 ELSE 0 END) AS tot_b,
SUM(CASE WHEN foo = 'C THEN 1 ELSE 0 END) AS tot_c
FROM Foobar;
But why not use a report writer in the front end, like you are supposed
to?
--CELKO-- wrote:
SELECT month_name,
COUNT(*) AS tot,
SUM(CASE WHEN foo = 'A' THEN 1 ELSE 0 END) AS tot_a,
SUM(CASE WHEN foo = 'B' THEN 1 ELSE 0 END) AS tot_b,
SUM(CASE WHEN foo = 'C THEN 1 ELSE 0 END) AS tot_c
FROM Foobar
GROUP BY month_name
UNION ALL
SELECT 'All months',
COUNT(*) AS tot
SUM(CASE WHEN foo = 'A' THEN 1 ELSE 0 END) AS tot_a,
SUM(CASE WHEN foo = 'B' THEN 1 ELSE 0 END) AS tot_b,
SUM(CASE WHEN foo = 'C THEN 1 ELSE 0 END) AS tot_c
FROM Foobar;
But why not use a report writer in the front end, like you are
supposed to?
So I don't have to hardcode my A, B and C's ?
Because I am using Visual Studio .NET and that leaves me with Crystal
Reports (yuckk, hark, spit) as a reporting tool.
I think I will try Erlands suggestion.
/jim This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Tom |
last post by:
I have a report based on a crosstab query that shows monthly automobile sales.
The report is grouped on Make so that it shows the models under each Make. I
want to now get the subtotals by Make and then the total for all for each month.
I find that the control source for the textboxes in the detail section have a
date value such as Jan_03 rather than dollars but when I run the report dollars
appears in the report. Obviously I can't sum up...
|
by: Donna Sabol |
last post by:
First, I should start by saying I am creating a database to be used by
some very impatient, non-computer literate people. It needs to be
seameless in it's operation from their point of view. I want them to
do as little as possible when they run their reports.
I have a crosstab query that displays usage of items for each month.
It looks pretty much like this:
ITEM DESC UM 12/02 1/03 2/03 3/03 ...ETC.
1 Solution ...
|
by: Nathan Bloomfield |
last post by:
Does anyone know if there is any documentation which relates to Access2k + ?
or can anyone help adjust the code?
I am having trouble converting the DAO references.
TITLE :INF: How to Create a Dynamic Crosstab Report
PRODUCT :Microsoft Access
PROD/VER:1.00 1.10
OPER/SYS:WINDOWS
|
by: newguy |
last post by:
I am trying to get the totals of a table by client by type of income.
This query will get what I am looking for with each unique combination
as a row:
SELECT Sales.Client, BillCode.Type, Sum(Sales.Amount)
FROM Invoice_Details INNER JOIN BillCode ON Sales.BillCode =
BillCode.id
GROUP BY Client, Type;
|
by: deejayquai |
last post by:
Hi
I'm trying to produce a report based on a dynamic crosstab. Ultimately
i'd like the report to actually become a sub report within a student
end of year record of achievement. The dynamic sub-report will capture
what grades the student has achieved in a list of different subjects
and the reason I need it to be dynamic is that students take different
subjects.
Basically I've been trying to doctor the KB article on dynamic
| |
by: x |
last post by:
hi
i have made a crosstab query in which row heading is "date", colum
heading is "aircraft type" and the value to be summed up is "1-10 row
totals". i want to create a simple query which should just give me the
total value of "1-10 row totals" field by defining a certain criteria.
now when i write the following in the criteria field
Between and
i get an error message saying "The microsoft jet database engine
doesn't recognize as a...
|
by: Tim Hunter |
last post by:
Hi,
I am running Access 2003 on WinXP.
Many of you will think I'm nuts when i explain what I am experiencing. I
have a report that is based on a crosstab query and it works fine. The
user has requested a slight change adding a few different totals at the
end. They should actusall be additional categories but i thought i could
just add some code and pull them out as the report was being poroduced.
I added the code in the ON-PRINT Event and...
|
by: ollyb303 |
last post by:
Hi,
I am trying to create a dynamic crosstab report which will display number of calls handled (I work for a call centre) per day grouped by supervisor.
I have one crosstab query (Query1) which has the following fields:
SPID (supervisor ID), total:group by, as row heading
Date, total:group by, as column heading
Calls handled, total:sum, as value
Date, total:where, criteria between and - this is taken from a form,
|
by: Coxmg |
last post by:
I have several related tables. One table lists orders with due dates for SKUs while another table lists components of a SKU (diamonds). A third related table lists diamond IDs and diamond inventory. One diamond can be included in multiple SKUs and one SKU can contain different types of diamonds. The SKU ID links the SKU component table. The diamond ID links the SKU component table and the diamond type table. The diamond table lists unique types...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |