Hello,
THis is my first time using crystal report and VB6.
Can someone give me some examples on how should I do to preview the report built in crystal report in VB6? After preview, I want to print it out too, using VB.
I found something in the internet that I should use the crystal report control, but I still don't know how to display it...
Cheers!
ClaudiHi,
You can very well use the crystal report control. kindly follow the steps to view crystal report using VB 6.0
1. Open a vb application (Standard EXE).
2. go to Project Menu->components
3. in components list box, get the crystal32.ocx control to add that.
4. design a report using crystal report.
5. then follow the code to view the report from VB.
public sub cmdViewReport_Click()
With
.reportfile=app.path & "\MyReport.rpt"
.WindowTitle = "Report Output"
.WindowState = crptMaximized
.WindowShowGroupTree = True
.WindowShowPrintSetupBtn = True
.WindowShowSearchBtn = True
.Action = 1
End With
End Sub
Hope, this will help....
Regards
Deepak
Showing posts with label examples. Show all posts
Showing posts with label examples. Show all posts
Sunday, March 11, 2012
Friday, February 24, 2012
Crosstab question for a "properties" table
Most of the examples for crosstabs use pivot. It looks like aggregations are what they are used for. What about if I have a dynamic properties table. So there is a table Person and a table Property and a table PropertyPersonValue. Is there any way to create a crosstab table of Person with Properties listed in the column heading and the Values in the cells?
Sure, can you post some DDL and sample data?
AMB
|||
CREATE TABLE Person
(
PersonID INT IDENTITY(1,1) NOT NULL,
[Name] VARCHAR(200),
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED (PersonID)
)
GO
CREATE TABLE PersonProperty
(
PersonPropertyID INT IDENTITY(1,1) NOT NULL,
[Name] VARCHAR(200),
CONSTRAINT [PK_PersonProperty] PRIMARY KEY CLUSTERED (PersonPropertyID)
)
GO
CREATE TABLE PersonPropertyValue
(
PersonID INT NOT NULL,
PersonPropertyID INT NOT NULL,
[Value] VARCHAR(1000),
CONSTRAINT [PK_PersonPropertyValue] PRIMARY KEY CLUSTERED (PersonID,PersonPropertyID),
CONSTRAINT [FK_PersonPropertyValue_PersonID] FOREIGN KEY (PersonID)
REFERENCES [Person],
CONSTRAINT [FK_PersonPropertyValue_PersonPropertyID] FOREIGN KEY (PersonPropertyID)
REFERENCES [PersonProperty],
)
GO
INSERT INTO PersonProperty ([Name]) VALUES ('Eye Color')
INSERT INTO PersonProperty ([Name]) VALUES ('Title')
INSERT INTO Person ([Name]) VALUES ('Joe Smith')
INSERT INTO Person ([Name]) VALUES ('Fred Thompson')
INSERT INTO Person ([Name]) VALUES ('Sally Hamilton')
INSERT INTO PersonPropertyValue (PersonID,PersonPropertyID,[Value])
SELECT p.PersonID,pp.PersonPropertyID,'Brown'
FROM Person p,PersonProperty pp
WHERE p.Name='Joe Smith'
AND pp.Name='Eye Color'
INSERT INTO PersonPropertyValue (PersonID,PersonPropertyID,[Value])
SELECT p.PersonID,pp.PersonPropertyID,'Blue'
FROM Person p,PersonProperty pp
WHERE p.Name='Fred Thompson'
AND pp.Name='Eye Color'
INSERT INTO PersonPropertyValue (PersonID,PersonPropertyID,[Value])
SELECT p.PersonID,pp.PersonPropertyID,'Green'
FROM Person p,PersonProperty pp
WHERE p.Name='Sally Hamilton'
AND pp.Name='Eye Color'
INSERT INTO PersonPropertyValue (PersonID,PersonPropertyID,[Value])
SELECT p.PersonID,pp.PersonPropertyID,'CFO'
FROM Person p,PersonProperty pp
WHERE p.Name='Joe Smith'
AND pp.Name='Title'
INSERT INTO PersonPropertyValue (PersonID,PersonPropertyID,[Value])
SELECT p.PersonID,pp.PersonPropertyID,'Vice Presient'
FROM Person p,PersonProperty pp
WHERE p.Name='Fred Thompson'
AND pp.Name='Title'
INSERT INTO PersonPropertyValue (PersonID,PersonPropertyID,[Value])
SELECT p.PersonID,pp.PersonPropertyID,'Sr. Partner'
FROM Person p,PersonProperty pp
WHERE p.Name='Sally Hamilton'
AND pp.Name='Title'
SELECT p.Name,pp.Name AS PropertyName, ppv.Value
FROM Person p
LEFT JOIN PersonPropertyValue ppv
ON p.PersonID=ppv.PersonID
LEFT JOIN PersonProperty pp
ON pp.PersonPropertyID=ppv.PersonPropertyID
|||Try:
declare @.columns nvarchar(max)
declare @.sql nvarchar(max)
set @.columns = stuff(
(
select ',' + cast(quotename([Name]) as nvarchar(max))
from dbo.PersonProperty
order by [Name]
for xml path('')
), 1, 1, '')
set @.sql = N'
select
*
from
(
SELECT
p.Name,pp.Name AS PropertyName, ppv.Value
FROM
dbo.Person p
LEFT JOIN
dbo.PersonPropertyValue ppv
ON p.PersonID=ppv.PersonID
LEFT JOIN
dbo.PersonProperty pp
ON pp.PersonPropertyID=ppv.PersonPropertyID
) as src
pivot
(
min(Value)
for PropertyName in (' + @.columns + N')
) as pvt
order by
[Name]
'
exec sp_executesql @.sql
go
Result:
Be careful with sql injection.
PIVOT on Steroids
http://www.sqlmag.com/Articles/ArticleID/94268/pg/3/3.html
AMB
|||Thanks. So I guess pivot is not really used for this sort of thing.|||
Nope, you are wrong.
He used the PIVOT operator, but he listed or collected all the property values on one variable and given those stored values to your PIVOT operator query...
Print @.SQL
You will get the query which is executed here..
Crosstab in TSQL
Does anyone know of any code to produce a crosstab table or
temp table? I found several examples of stored procs to do this,
however they just produce results. I need to produce a table
to use to update another table.
Thanks much,
Marc MillerA simple one
USE pubs
GO
SELECT
stor_id,
SUM(CASE YEAR(ord_date)
WHEN 1992 THEN qty
ELSE 0
END) AS c1992,
SUM(CASE YEAR(ord_date)
WHEN 1993 THEN qty
ELSE 0
END) AS c1993,
SUM(CASE YEAR(ord_date)
WHEN 1994 THEN qty
ELSE 0
END) AS c1994
FROM Sales
GROUP BY stor_id
ORDER BY stor_id
"Marc Miller" <mm1284@.hotmail.com> wrote in message
news:ObOs2SzJGHA.1544@.TK2MSFTNGP11.phx.gbl...
> Does anyone know of any code to produce a crosstab table or
> temp table? I found several examples of stored procs to do this,
> however they just produce results. I need to produce a table
> to use to update another table.
> Thanks much,
> Marc Miller
>|||This article describes various techniques for implementing a crosstab type
query. Basically, it's just a group by query.
http://www.aspfaq.com/show.asp?id=2462
The result of any query can be output to a table or temporary table:
the SELECT.. INTO.. syntax will create a new table with the same column and
data type structure as the query result.
select
a,
b,
sum(cnt) as totcnt,
avg(cnt) as avgcnt
into
mycrosstab
from
mytable
group by
a,
b
the INSERT INTO.. SELECT.. syntax will insert the query result into an
existing table:
insert into mycrosstab
select
a,
b,
sum(cnt) as totcnt,
avg(cnt) as avgcnt
from
mytable
group by
a,
b
For temporary tables, just prefix the insert table with a # symbol.
insert into #mycrosstab . . .
"Marc Miller" <mm1284@.hotmail.com> wrote in message
news:ObOs2SzJGHA.1544@.TK2MSFTNGP11.phx.gbl...
> Does anyone know of any code to produce a crosstab table or
> temp table? I found several examples of stored procs to do this,
> however they just produce results. I need to produce a table
> to use to update another table.
> Thanks much,
> Marc Miller
>|||Uri,
Thanks! I took your suggestion a step further, becuase I the field names
can be variable. Here's what I did
(just for posterity I suppose, and anyone else trying to do the same.)
Temp table ##c_bt:
CUST_ACCT COLUMN_NAME AMOUNT
0123456 January 500.00
5464646 March 53.00
5161616 January 333.33
0123456 May 500.00
5464646 June 53.00
5161616 June 333.33
etc.
CODE:
DECLARE @.col varchar(20)
DECLARE @.DDL varchar(1000)
DECLARE curcols CURSOR
FOR select distinct column_name from ##c_bt order by column_name
OPEN curcols
SET @.DDL = 'Select cust_acct, '
FETCH NEXT FROM curcols INTO @.col
WHILE (@.@.fetch_status <> -1)
BEGIN
set @.DDL = @.DDL + ' SUM(CASE column_name WHEN ''' + rtrim(@.col) +''' THEN
amount ELSE 0 END) AS ' + rtrim(@.col) + ','
FETCH NEXT FROM curcols INTO @.col
END
CLOSE curcols
DEALLOCATE curcols
SET @.DDL = SUBSTRING(@.DDL,0,LEN(@.DDL)-1)
SET @.DDL = @.DDL + ' FROM ##c_bt GROUP BY cust_acct ORDER BY cust_acct'
EXEC(@.DDL)
Thanks again,
Marc
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23MG2$WzJGHA.1728@.TK2MSFTNGP14.phx.gbl...
>A simple one
> USE pubs
> GO
> SELECT
> stor_id,
> SUM(CASE YEAR(ord_date)
> WHEN 1992 THEN qty
> ELSE 0
> END) AS c1992,
> SUM(CASE YEAR(ord_date)
> WHEN 1993 THEN qty
> ELSE 0
> END) AS c1993,
> SUM(CASE YEAR(ord_date)
> WHEN 1994 THEN qty
> ELSE 0
> END) AS c1994
> FROM Sales
> GROUP BY stor_id
> ORDER BY stor_id
>
>
> "Marc Miller" <mm1284@.hotmail.com> wrote in message
> news:ObOs2SzJGHA.1544@.TK2MSFTNGP11.phx.gbl...
>|||Marc
Two questions I'd liek to ask you.
1) Why do you need to use a global temporary table?
2) Why do you need to use a cursor?
"Marc Miller" <mm1284@.hotmail.com> wrote in message
news:uLCuj6zJGHA.720@.TK2MSFTNGP14.phx.gbl...
> Uri,
> Thanks! I took your suggestion a step further, becuase I the field names
> can be variable. Here's what I did
> (just for posterity I suppose, and anyone else trying to do the same.)
> Temp table ##c_bt:
> CUST_ACCT COLUMN_NAME AMOUNT
> 0123456 January 500.00
> 5464646 March 53.00
> 5161616 January 333.33
> 0123456 May 500.00
> 5464646 June
> 53.00
> 5161616 June 333.33
> etc.
> CODE:
> DECLARE @.col varchar(20)
> DECLARE @.DDL varchar(1000)
> DECLARE curcols CURSOR
> FOR select distinct column_name from ##c_bt order by column_name
> OPEN curcols
> SET @.DDL = 'Select cust_acct, '
> FETCH NEXT FROM curcols INTO @.col
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> set @.DDL = @.DDL + ' SUM(CASE column_name WHEN ''' + rtrim(@.col) +''' THEN
> amount ELSE 0 END) AS ' + rtrim(@.col) + ','
> FETCH NEXT FROM curcols INTO @.col
> END
> CLOSE curcols
> DEALLOCATE curcols
> SET @.DDL = SUBSTRING(@.DDL,0,LEN(@.DDL)-1)
> SET @.DDL = @.DDL + ' FROM ##c_bt GROUP BY cust_acct ORDER BY cust_acct'
> EXEC(@.DDL)
> Thanks again,
> Marc
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23MG2$WzJGHA.1728@.TK2MSFTNGP14.phx.gbl...
>|||Uri,
1) The temp table need not be global, it was just the way I was
experimenting, but I do need a table
to join and update another table.
2) I'm using the cursor to loop through its results to discover the 'column
names' that I need to build
my SQL. The column names can differ from month to month for me,
depending on the source data.
Marc
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e7c5bC0JGHA.1032@.TK2MSFTNGP10.phx.gbl...
> Marc
> Two questions I'd liek to ask you.
> 1) Why do you need to use a global temporary table?
> 2) Why do you need to use a cursor?
>
> "Marc Miller" <mm1284@.hotmail.com> wrote in message
> news:uLCuj6zJGHA.720@.TK2MSFTNGP14.phx.gbl...
>|||Marc
> 1) The temp table need not be global, it was just the way I was
> experimenting, but I do need a table
> to join and update another table.
I don't see any reasons to use a global temrorary table. Pls refer to the
BOL for more details
> 2) I'm using the cursor to loop through its results to discover the
> 'column names' that I need to build
> my SQL. The column names can differ from month to month for me,
> depending on the source data.
>
Search on internet "dynamic crosstab" written by Itzik Ben-Gan
"Marc Miller" <mm1284@.hotmail.com> wrote in message
news:uvNydJ0JGHA.2864@.TK2MSFTNGP10.phx.gbl...
> Uri,
> 1) The temp table need not be global, it was just the way I was
> experimenting, but I do need a table
> to join and update another table.
> 2) I'm using the cursor to loop through its results to discover the
> 'column names' that I need to build
> my SQL. The column names can differ from month to month for me,
> depending on the source data.
> Marc
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e7c5bC0JGHA.1032@.TK2MSFTNGP10.phx.gbl...
>|||Check out the RAC utility for pivoting/xtabs,
no coding necessary
www.rac4sql.net|||Is there an easy way, in outlook express, to stop this guy's spam from
showing up based on their alias?
I am sure that others use noname@.noname.com just to avoid being put on
mailing lists, and I don't want to filter out useful messages inadvertantly.
Thanks,
Jim
"05ponyGT" <noname@.noname.com> wrote in message
news:uN0pYl1JGHA.596@.TK2MSFTNGP10.phx.gbl...
> Check out the RAC utility for pivoting/xtabs,
> no coding necessary
> www.rac4sql.net
>|||Is that better:)
Have a nice day.
www.rac4sql.net
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:epx%23Ir1JGHA.1832@.TK2MSFTNGP11.phx.gbl...
> Is there an easy way, in outlook express, to stop this guy's spam from
> showing up based on their alias?
> I am sure that others use noname@.noname.com just to avoid being put on
> mailing lists, and I don't want to filter out useful messages
> inadvertantly.
> Thanks,
> Jim
> "05ponyGT" <noname@.noname.com> wrote in message
> news:uN0pYl1JGHA.596@.TK2MSFTNGP10.phx.gbl...
>
temp table? I found several examples of stored procs to do this,
however they just produce results. I need to produce a table
to use to update another table.
Thanks much,
Marc MillerA simple one
USE pubs
GO
SELECT
stor_id,
SUM(CASE YEAR(ord_date)
WHEN 1992 THEN qty
ELSE 0
END) AS c1992,
SUM(CASE YEAR(ord_date)
WHEN 1993 THEN qty
ELSE 0
END) AS c1993,
SUM(CASE YEAR(ord_date)
WHEN 1994 THEN qty
ELSE 0
END) AS c1994
FROM Sales
GROUP BY stor_id
ORDER BY stor_id
"Marc Miller" <mm1284@.hotmail.com> wrote in message
news:ObOs2SzJGHA.1544@.TK2MSFTNGP11.phx.gbl...
> Does anyone know of any code to produce a crosstab table or
> temp table? I found several examples of stored procs to do this,
> however they just produce results. I need to produce a table
> to use to update another table.
> Thanks much,
> Marc Miller
>|||This article describes various techniques for implementing a crosstab type
query. Basically, it's just a group by query.
http://www.aspfaq.com/show.asp?id=2462
The result of any query can be output to a table or temporary table:
the SELECT.. INTO.. syntax will create a new table with the same column and
data type structure as the query result.
select
a,
b,
sum(cnt) as totcnt,
avg(cnt) as avgcnt
into
mycrosstab
from
mytable
group by
a,
b
the INSERT INTO.. SELECT.. syntax will insert the query result into an
existing table:
insert into mycrosstab
select
a,
b,
sum(cnt) as totcnt,
avg(cnt) as avgcnt
from
mytable
group by
a,
b
For temporary tables, just prefix the insert table with a # symbol.
insert into #mycrosstab . . .
"Marc Miller" <mm1284@.hotmail.com> wrote in message
news:ObOs2SzJGHA.1544@.TK2MSFTNGP11.phx.gbl...
> Does anyone know of any code to produce a crosstab table or
> temp table? I found several examples of stored procs to do this,
> however they just produce results. I need to produce a table
> to use to update another table.
> Thanks much,
> Marc Miller
>|||Uri,
Thanks! I took your suggestion a step further, becuase I the field names
can be variable. Here's what I did
(just for posterity I suppose, and anyone else trying to do the same.)
Temp table ##c_bt:
CUST_ACCT COLUMN_NAME AMOUNT
0123456 January 500.00
5464646 March 53.00
5161616 January 333.33
0123456 May 500.00
5464646 June 53.00
5161616 June 333.33
etc.
CODE:
DECLARE @.col varchar(20)
DECLARE @.DDL varchar(1000)
DECLARE curcols CURSOR
FOR select distinct column_name from ##c_bt order by column_name
OPEN curcols
SET @.DDL = 'Select cust_acct, '
FETCH NEXT FROM curcols INTO @.col
WHILE (@.@.fetch_status <> -1)
BEGIN
set @.DDL = @.DDL + ' SUM(CASE column_name WHEN ''' + rtrim(@.col) +''' THEN
amount ELSE 0 END) AS ' + rtrim(@.col) + ','
FETCH NEXT FROM curcols INTO @.col
END
CLOSE curcols
DEALLOCATE curcols
SET @.DDL = SUBSTRING(@.DDL,0,LEN(@.DDL)-1)
SET @.DDL = @.DDL + ' FROM ##c_bt GROUP BY cust_acct ORDER BY cust_acct'
EXEC(@.DDL)
Thanks again,
Marc
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23MG2$WzJGHA.1728@.TK2MSFTNGP14.phx.gbl...
>A simple one
> USE pubs
> GO
> SELECT
> stor_id,
> SUM(CASE YEAR(ord_date)
> WHEN 1992 THEN qty
> ELSE 0
> END) AS c1992,
> SUM(CASE YEAR(ord_date)
> WHEN 1993 THEN qty
> ELSE 0
> END) AS c1993,
> SUM(CASE YEAR(ord_date)
> WHEN 1994 THEN qty
> ELSE 0
> END) AS c1994
> FROM Sales
> GROUP BY stor_id
> ORDER BY stor_id
>
>
> "Marc Miller" <mm1284@.hotmail.com> wrote in message
> news:ObOs2SzJGHA.1544@.TK2MSFTNGP11.phx.gbl...
>|||Marc
Two questions I'd liek to ask you.
1) Why do you need to use a global temporary table?
2) Why do you need to use a cursor?
"Marc Miller" <mm1284@.hotmail.com> wrote in message
news:uLCuj6zJGHA.720@.TK2MSFTNGP14.phx.gbl...
> Uri,
> Thanks! I took your suggestion a step further, becuase I the field names
> can be variable. Here's what I did
> (just for posterity I suppose, and anyone else trying to do the same.)
> Temp table ##c_bt:
> CUST_ACCT COLUMN_NAME AMOUNT
> 0123456 January 500.00
> 5464646 March 53.00
> 5161616 January 333.33
> 0123456 May 500.00
> 5464646 June
> 53.00
> 5161616 June 333.33
> etc.
> CODE:
> DECLARE @.col varchar(20)
> DECLARE @.DDL varchar(1000)
> DECLARE curcols CURSOR
> FOR select distinct column_name from ##c_bt order by column_name
> OPEN curcols
> SET @.DDL = 'Select cust_acct, '
> FETCH NEXT FROM curcols INTO @.col
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> set @.DDL = @.DDL + ' SUM(CASE column_name WHEN ''' + rtrim(@.col) +''' THEN
> amount ELSE 0 END) AS ' + rtrim(@.col) + ','
> FETCH NEXT FROM curcols INTO @.col
> END
> CLOSE curcols
> DEALLOCATE curcols
> SET @.DDL = SUBSTRING(@.DDL,0,LEN(@.DDL)-1)
> SET @.DDL = @.DDL + ' FROM ##c_bt GROUP BY cust_acct ORDER BY cust_acct'
> EXEC(@.DDL)
> Thanks again,
> Marc
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23MG2$WzJGHA.1728@.TK2MSFTNGP14.phx.gbl...
>|||Uri,
1) The temp table need not be global, it was just the way I was
experimenting, but I do need a table
to join and update another table.
2) I'm using the cursor to loop through its results to discover the 'column
names' that I need to build
my SQL. The column names can differ from month to month for me,
depending on the source data.
Marc
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e7c5bC0JGHA.1032@.TK2MSFTNGP10.phx.gbl...
> Marc
> Two questions I'd liek to ask you.
> 1) Why do you need to use a global temporary table?
> 2) Why do you need to use a cursor?
>
> "Marc Miller" <mm1284@.hotmail.com> wrote in message
> news:uLCuj6zJGHA.720@.TK2MSFTNGP14.phx.gbl...
>|||Marc
> 1) The temp table need not be global, it was just the way I was
> experimenting, but I do need a table
> to join and update another table.
I don't see any reasons to use a global temrorary table. Pls refer to the
BOL for more details
> 2) I'm using the cursor to loop through its results to discover the
> 'column names' that I need to build
> my SQL. The column names can differ from month to month for me,
> depending on the source data.
>
Search on internet "dynamic crosstab" written by Itzik Ben-Gan
"Marc Miller" <mm1284@.hotmail.com> wrote in message
news:uvNydJ0JGHA.2864@.TK2MSFTNGP10.phx.gbl...
> Uri,
> 1) The temp table need not be global, it was just the way I was
> experimenting, but I do need a table
> to join and update another table.
> 2) I'm using the cursor to loop through its results to discover the
> 'column names' that I need to build
> my SQL. The column names can differ from month to month for me,
> depending on the source data.
> Marc
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e7c5bC0JGHA.1032@.TK2MSFTNGP10.phx.gbl...
>|||Check out the RAC utility for pivoting/xtabs,
no coding necessary
www.rac4sql.net|||Is there an easy way, in outlook express, to stop this guy's spam from
showing up based on their alias?
I am sure that others use noname@.noname.com just to avoid being put on
mailing lists, and I don't want to filter out useful messages inadvertantly.
Thanks,
Jim
"05ponyGT" <noname@.noname.com> wrote in message
news:uN0pYl1JGHA.596@.TK2MSFTNGP10.phx.gbl...
> Check out the RAC utility for pivoting/xtabs,
> no coding necessary
> www.rac4sql.net
>|||Is that better:)
Have a nice day.
www.rac4sql.net
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:epx%23Ir1JGHA.1832@.TK2MSFTNGP11.phx.gbl...
> Is there an easy way, in outlook express, to stop this guy's spam from
> showing up based on their alias?
> I am sure that others use noname@.noname.com just to avoid being put on
> mailing lists, and I don't want to filter out useful messages
> inadvertantly.
> Thanks,
> Jim
> "05ponyGT" <noname@.noname.com> wrote in message
> news:uN0pYl1JGHA.596@.TK2MSFTNGP10.phx.gbl...
>
Subscribe to:
Posts (Atom)