how to generate report with title in sql server
-
need help to generate report with title
i have table Products like this
-----------------------------------------
ProductName | CategoryID | amount_SUM
a | 1 | 999
b 2 546
c 3 1345
a 1 888
a 1 777
b 2 999
---------------------------------
need to generate report like this
----------------------------------------------
title of the report = ProductName
A
999
888
777
----------------------------------
title of the report = ProductName
B
77
888
777
thnks ilan
-
That is a presentation issue that should be done on the report designer of your choise. On SQL it will be just a matter of ordering by product name
* Noel -
Noel,
Sometimes one wants to run quick ad hoc reports straight out of Query Analyzer.
Try this:
Print 'My Report Title"
Print 'for My Big Boss'
Print getdate()
Select whatever, columns, you, need
from YourTable
-
Sara,
your query, even if you try to do it from QA, does not represents the output that the poster requested
* Noel -
Noel is absolutely right - as usual. I was too quick on the draw.
OK.
First, in QA tools - options - results, turn off print column headers
print 'title of the report = ProductName'
print ' A'
select amount_SUM
from Products
where ProductName = 'A'
print 'title of the report = ProductName'
print ' B'
select amount_SUM
from Products
where ProductName = 'B'
-
how to do it from Stord procedures
thet generate report
with the title in heder=ProductName
and after the amount_SUM like in table
like this
ProductName-11
----------------------
aaa 55
aaa 99
aaa 76
ProductName-22
----------------------
bbb 888
bbb 999
bbb 444
----------------------
-
What is the -11, -22? how are those numbers generated?
-
A
----------------------
aaa 55
aaa 99
aaa 76
B
----------------------
bbb 888
bbb 999
bbb 444
----------------------
OK NOW
WHAT IS THE answer ??
-
midan - you would just have to use what sara gave you and put it in a stored procedure - almost verbatim...the nocount has been set so that you don't get a display of how many rows have been affected by your select - also make sure that in tools - options - results you select "results to text"....to run your procedure you would "Exec ProcTestPrint" in QA!
CREATE PROCEDURE ProcTestPrint
AS
SET NOCOUNT ON
PRINT 'Title of the report = ProductName'
PRINT ' A'
SELECT ProductName, amount_SUM
FROM Products
WHERE ProductName = 'A'
PRINT 'Title of the report = ProductName'
PRINT ' B'
SELECT ProductName, amount_SUM
FROM Products
WHERE ProductName = 'B'
SET NOCOUNT OFF
GO
**ASCII stupid question, get a stupid ANSI !!!** -
thnks
but how to use ONLY IF exist
i need to see in the report only if the ProductName exist
thnks again
ilan
-
so you have to check the existence of both product names (a & b) before you print them ?!
just out of curiosity....may I ask why you need to do this report?!
**ASCII stupid question, get a stupid ANSI !!!** -
here goes...
CREATE PROCEDURE ProcTestPrint
AS
SET NOCOUNT ON
IF EXISTS(SELECT * FROM Products WHERE ProductName = 'A')
BEGIN
PRINT 'Title of the report = ProductName'
PRINT ' A'
SELECT ProductName, amount_SUM
FROM Products
WHERE ProductName = 'A'
END
IF EXISTS(SELECT * FROM Products WHERE ProductName = 'B')
BEGIN
PRINT 'Title of the report = ProductName'
PRINT ' B'
SELECT ProductName, amount_SUM
FROM Products
WHERE ProductName = 'B'
END
SET NOCOUNT OFF
GO
**ASCII stupid question, get a stupid ANSI !!!** -
is it possible to create a smart PROCEDURE
thet count the Rows in Table Products
and generate report thet know how many "ProductName" i have
for example let's assume that I add new "ProductName" in Table Products
i wont to generate report with the new ProductName without to fix the
PROCEDURE ProcTestPrint evry time ???
thnks for all
ilan
-
ilan - I'm not very clear on your question...
however, you can count the number of rows with a given ProductName by doing a :
select count(*) from Products where ProductName = 'NewProductName'...
one way of writing conditional code is by declaring a variable & storing the number of rows in that ...
eg:
declare @numOfRows int
select @numOfRows = count(*) from Products where ProductName = 'NewProductName'
and then do a ...
if @numOfRows = 0
...
else
....
is this what you're looking for ?!
**ASCII stupid question, get a stupid ANSI !!!** -
ok
this PROCEDURE is good to generate report for
ProductName = 'A'
AND FOR
ProductName = 'B'
AND I HAVE another table name Table_Product_id
----------------------------
ProductName | CategoryID
a 1
b 2
c 3
d 4
e 5
f 6
....... ......
z 99
---------------------------------
that my users select from list and insert into TABLE Products
my question is if i add more 100 new Products into Table_Product_id
how to generate a report from table Products thet automatic know thet i add more 100 new Products to Table_Product_id
thnks ilan
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply