Crystal reports to SQL Server Reports

Hi
Present we are using Crystal reports 9.0 in application.
we like to convert crystal reports to SQL server reports.
Do we need to rewrite all crystal reports into SQl reports again
Would you appreciate if any one give suggestion for hot toconverting
crystal reports to SQL Server Reports .
Regards
VenuI do not know of a conversion tool. We just went through same and re-wrote
our reports. It was fun! RS is a powerful reporting tool and out-classes
crystal.
good luck
--
U. Tokklas
"venu gopal" wrote:
> Hi
> Present we are using Crystal reports 9.0 in application.
> we like to convert crystal reports to SQL server reports.
> Do we need to rewrite all crystal reports into SQl reports again
> Would you appreciate if any one give suggestion for hot toconverting
> crystal reports to SQL Server Reports .
> Regards
> Venu

Crystal reports to Reporting Services

Crystal reports to Reporting Services
Is there any way I can convert Crystal reports to Reporting Services instead
of creating reports again in Reporting Services?JIM. H. wrote:
> Crystal reports to Reporting Services
> Is there any way I can convert Crystal reports to Reporting Services instead
> of creating reports again in Reporting Services?
Aside from using a Migration partner, there is not much else that can
be done outside of maybe designing some sort of custom migration
application. A few of the migration partner links are as follows.
Sorry that I could not be of greater assistance.
http://www.hitachiconsulting.com/downloadPdf.cfm?ID=251
http://www.rpttordl.com/
http://www.microsoft.com/sql/technologies/reporting/partners/crystal-migration.mspx
Regards,
Enrique Martinez
Sr. Software Consultant

Crystal reports to Reporting Services

Crystal reports to Reporting Services

Is there any way I can convert Crystal reports to Reporting Services instead of creating reports again in Reporting Services?

From http://blog.hundhausen.com/ConvertingCrystalReportsRptToReportingServicesReportsRdl.aspx (and I have no idea whether this is true):

There are way too many Crystal Reports out there in the world and I would love to see more of them converted to Reporting Services. Therein lies the problem. I had heard that Crystal (now BusinessObjects) had blocked companies, like Hitachi, from building converters, because they would be reverse engineering (or using the APIs to reverse engineer) the .rpt files, and that would violate the license agreement.

Microsoft only provides migration guidance, but I see that one company, KTL Solutions, has a conversion product available, and another company, Jeff-Net is offering a $25/report conversion service at rpttordl.com.

There do seem to be a lot of "migration partners" (gack!) listed in various places.

>L<

sql

Crystal reports to Reporting Services

Crystal reports to Reporting Services

Is there any way I can convert Crystal reports to Reporting Services instead of creating reports again in Reporting Services?

HI, JIM.H.

You can check out this great article. Hope it helps

Converting Crystal to Reporting Services

http://blogs.msdn.com/gsnowman/archive/2005/10/12/480305.aspx

If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.

I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance

|||

Hi Jim,

Microsoft created one Migration guide for Migrating Your Crystal Reports to SQL Server 2005 Reporting Services -

http://www.microsoft.com/sql/techinfo/whitepapers/migrate-crystal-to-reporting-services.mspx

And the webcast for this -

http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?culture=en-US&EventID=1032298257&CountryCode=US

Got this info from this blog -

http://blogs.msdn.com/bwelcker/archive/2006/05/31/612637.aspx

crystal reports conversion

Hi

Im new to reporting services but experianced in crystal reports

Im trying to convert some crystal reports into sql 2005 reporting services.

I have this formula in crystal reports which I cannot seem to replicate in Reporting services

if {TABLE1.TYPEID} = 1 then (0 - (

if {TABLE2.CURRENCYDESC} <> 'GBP' then {TABLE3.TOTALVALUE} / {TABLE4.EXCHANGERATE} else
({TABLE3.TOTALVALUE})-{@.line total}))
else
if {TABLE2.CURRENCYDESC} <> 'GBP' then
{@.Gross Profit} / {TABLE4.EXCHANGERATE} else {@.Gross Profit}

I have already created the formulas for @.line total and @.gross profit

Can any one please put me into the right direction

Thanks for your help

Use this expression assuming that have created 2 calculated database fields called "LineTotal" and "GrossProfit" and have the TABLE fields (as in crystal expression) as dataset fields:

IIf(Fields!TYPEID.Value=1,(0-(IIf(Fields!CURRENCYDESC.Value<>"GBP", Fields!TOTALVALUE.Value/Fields!EXCHANGERATE.Value, Fields!TOTALVALUE.Value-Fields!LineTotal.Value))), IIf(Fields!CURRENCYDESC.Value<>"GBP", Fields!GrossProfit.Value/Fields!EXCHANGERATE.Value, Fields!GrossProfit.Value))

Shyam

|||

Top man Shyam

Works like a dream

Thanks a lot for your help

|||Hi, I am currently trying to convert a crystal report over to a sql 2005 reporting services. In my crystal report I have a parameter called @.Option which has the following formula associated to it:

Local NumberVar Opt := 0;

select {@.ReportStyle}
case 1: Opt := Opt + 2
default: Opt;

select {@.ReportType}
case 1: Opt := Opt + 1
case 3: Opt := Opt + 1
default: Opt;

select {@.EnterpriseOption}
case 2: Opt := Opt + 4
case 4: Opt := Opt + 4
default: Opt;

Opt;

where @.ReportStyle, @.ReportType and @.EnterpriseOption are all parameters from the same report.

I am having some trouble trying to convert this over. Can any body give me any suggestions or point me in the right direction on how this can be achieved.

Thanks in advance.

|||I have solved the issue.
sql

crystal reports conversion

Hi

Im new to reporting services but experianced in crystal reports

Im trying to convert some crystal reports into sql 2005 reporting services.

I have this formula in crystal reports which I cannot seem to replicate in Reporting services

if {TABLE1.TYPEID} = 1 then (0 - (

if {TABLE2.CURRENCYDESC} <> 'GBP' then {TABLE3.TOTALVALUE} / {TABLE4.EXCHANGERATE} else
({TABLE3.TOTALVALUE})-{@.line total}))
else
if {TABLE2.CURRENCYDESC} <> 'GBP' then
{@.Gross Profit} / {TABLE4.EXCHANGERATE} else {@.Gross Profit}

I have already created the formulas for @.line total and @.gross profit

Can any one please put me into the right direction

Thanks for your help

Use this expression assuming that have created 2 calculated database fields called "LineTotal" and "GrossProfit" and have the TABLE fields (as in crystal expression) as dataset fields:

IIf(Fields!TYPEID.Value=1,(0-(IIf(Fields!CURRENCYDESC.Value<>"GBP", Fields!TOTALVALUE.Value/Fields!EXCHANGERATE.Value, Fields!TOTALVALUE.Value-Fields!LineTotal.Value))), IIf(Fields!CURRENCYDESC.Value<>"GBP", Fields!GrossProfit.Value/Fields!EXCHANGERATE.Value, Fields!GrossProfit.Value))

Shyam

|||

Top man Shyam

Works like a dream

Thanks a lot for your help

|||Hi, I am currently trying to convert a crystal report over to a sql 2005 reporting services. In my crystal report I have a parameter called @.Option which has the following formula associated to it:

Local NumberVar Opt := 0;

select {@.ReportStyle}
case 1: Opt := Opt + 2
default: Opt;

select {@.ReportType}
case 1: Opt := Opt + 1
case 3: Opt := Opt + 1
default: Opt;

select {@.EnterpriseOption}
case 2: Opt := Opt + 4
case 4: Opt := Opt + 4
default: Opt;

Opt;

where @.ReportStyle, @.ReportType and @.EnterpriseOption are all parameters from the same report.

I am having some trouble trying to convert this over. Can any body give me any suggestions or point me in the right direction on how this can be achieved.

Thanks in advance.

|||I have solved the issue.

crystal reports - help

Hi there is am new with this porgram. I porblem is that in my report the is a automatic convert from US $ to EU € now i need to stop the convert, the actual vaule from being shown. I can not find the program and i am not sure where to look please help.
kingyJust go to your crystal report design view and right click on the field which shows money and click "edit formula field" and you can see logic behind that formula and to put $ sign instead of other currency sign right click on money field again and go to "formet field" and change format

hope it will work..BEST OF LUCK..sql

Crystal Report In Vb.net 2005

HELLO FRIENDS

i want to know how to convert the numbers to letters

ex :

5462 = five thousands four hundered and sixty two.

is there any formula can covert numbers to letters.

because i have a Recipts report and i want the total to be in both numbers and letters .

thanks
husseinSee help on ToWords function

Crystal report Impossible to convert to RS?

I am trying to re-create a report in RS which we currently have in Crystal.
The report does a grouping of data based on client. There should be an
"address" page in-between each of the client's data pages which displays the
client name and address (for an envelope). This page should not have a page
footer. Also, the client's data pages should have a footer with page numbers
but the numbering for the pages should be re-set for each client. For example:
First printed page: CLIENT A Address page (no footer)
Second printed page: CLIENT A Data page (displays "page 1 of 1" in footer)
Third printed page: CLIENT B Address page (no footer)
Fourth printed page: CLIENT B Data page (displays "page 1 of 2" in footer)
Fifth printed page: CLIENT B Data page (displays "page 2 of 2" in footer)
Sixth printed page: CLIENT C Address page (no footer)
Seventh printed page: CLIENT C Data page (displays "page 1 of 1" in footer)
I can do the "address" page by creating a group header at the top of my
table which is the same size as a regular page. The problem for me is:
How do I have a footer displaying the page number only on the data pages?
How do I reset the page numberign for each client (or grouping)?You won't be able to get the "of X" part of the page report, but you can
reset the page number using a little custom code. Basically you define a
static variable for the report and reset it every time the grouping item
changes.
See https://blogs.msdn.com/bwelcker/archive/2005/05/19/420046.aspx
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:973069DA-28B4-40A3-A075-EBC1C3E216D5@.microsoft.com...
>I am trying to re-create a report in RS which we currently have in Crystal.
> The report does a grouping of data based on client. There should be an
> "address" page in-between each of the client's data pages which displays
> the
> client name and address (for an envelope). This page should not have a
> page
> footer. Also, the client's data pages should have a footer with page
> numbers
> but the numbering for the pages should be re-set for each client. For
> example:
> First printed page: CLIENT A Address page (no footer)
> Second printed page: CLIENT A Data page (displays "page 1 of 1" in footer)
> Third printed page: CLIENT B Address page (no footer)
> Fourth printed page: CLIENT B Data page (displays "page 1 of 2" in footer)
> Fifth printed page: CLIENT B Data page (displays "page 2 of 2" in footer)
> Sixth printed page: CLIENT C Address page (no footer)
> Seventh printed page: CLIENT C Data page (displays "page 1 of 1" in
> footer)
> I can do the "address" page by creating a group header at the top of my
> table which is the same size as a regular page. The problem for me is:
> How do I have a footer displaying the page number only on the data pages?
> How do I reset the page numberign for each client (or grouping)?

Crystal 2 ReportingServices

Does anybody know a free tool to convert Crystal Reports to Reporting
Services ?
Thanks ins advance,
Tito.On Jun 5, 1:10 pm, "Marcos Tito" <mrct...@.hotmail.com> wrote:
> Does anybody know a free tool to convert Crystal Reports to Reporting
> Services ?
> Thanks ins advance,
> Tito.
Unfortunately, I have not found any free conversion options. Here are
a few that can do the conversion for a price though:
http://www.microsoft.com/sql/technologies/reporting/partners/crystal-migration.mspx
http://www.rpttordl.com/
Sorry I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thank you !
"EMartinez" <emartinez.pr1@.gmail.com> escreveu na mensagem
news:1181100921.907554.304710@.p77g2000hsh.googlegroups.com...
> On Jun 5, 1:10 pm, "Marcos Tito" <mrct...@.hotmail.com> wrote:
>> Does anybody know a free tool to convert Crystal Reports to Reporting
>> Services ?
>> Thanks ins advance,
>> Tito.
>
> Unfortunately, I have not found any free conversion options. Here are
> a few that can do the conversion for a price though:
> http://www.microsoft.com/sql/technologies/reporting/partners/crystal-migration.mspx
> http://www.rpttordl.com/
> Sorry I could not be of greater assistance.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Jun 6, 8:22 am, "Marcos Tito" <mrct...@.hotmail.com> wrote:
> Thank you !
> "EMartinez" <emartinez...@.gmail.com> escreveu na mensagemnews:1181100921.907554.304710@.p77g2000hsh.googlegroups.com...
> > On Jun 5, 1:10 pm, "Marcos Tito" <mrct...@.hotmail.com> wrote:
> >> Does anybody know a free tool to convert Crystal Reports to Reporting
> >> Services ?
> >> Thanks ins advance,
> >> Tito.
> > Unfortunately, I have not found any free conversion options. Here are
> > a few that can do the conversion for a price though:
> >http://www.microsoft.com/sql/technologies/reporting/partners/crystal-...
> >http://www.rpttordl.com/
> > Sorry I could not be of greater assistance.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Let me know if I can be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Crosstab result

Hi there,

I'm using sql2005. I've got a table in tabular form and I need to convert it to crosstab format. I've tried to use pivot operator but i can't get the result i want. Because pivot operator needs to aggregate a field but what i need is Dim values as column names and DimCode as values within those columns as it is, no aggregations.

For example : This is tabular table.

Dim DimCode JrnlEntry GLDistId TrxDate GLPostDT TrxType AccountIndex DebitAmt CreditAmt Amount 4 2 54222 1 25/05/2007 25/05/2007 1 44256 617 0 617 4 11991 54222 1 25/05/2007 25/05/2007 1 44256 617 0 617 5 7 54222 1 25/05/2007 25/05/2007 1 44256 617 0 617 5 5 54222 1 25/05/2007 25/05/2007 1 44256 617 0 617 4 1 54225 1 31/05/2007 31/05/2007 1 44256 600 0 600 5 5 54225 1 31/05/2007 31/05/2007 1 44256 600 0 600 4 12821 54225 2 31/05/2007 31/05/2007 1 44256 400 0 400 5 4 54225 2 31/05/2007 31/05/2007 1 44256 400 0 400 4 3 54227 3 31/05/2007 31/05/2007 1 44256 0 500 -500 5 8 54227 3 31/05/2007 31/05/2007 1 44256 0 500 -500

And i want the end result as under..

jrnentry aaGLDistID TRXDATE GLPOSTDT aaTRXType Dim1 Dim2 Dim3 Dim4 Dim5 Dim6 Dim7 Dim8 Dim9 Dim10 ACTINDX DEBITAMT CRDTAMNT Amount 54222 1 25/05/2007 25/05/2007 1 0 0 0 2 7 0 0 0 0 0 44256 617 0 617 54222 1 25/05/2007 25/05/2007 1 0 0 0 11991 5 0 0 0 0 0 44256 617 0 617 54225 1 31/05/2007 31/05/2007 1 0 0 0 1 5 0 0 0 0 0 44256 600 0 600 54225 2 31/05/2007 31/05/2007 1 0 0 0 12821 4 0 0 0 0 0 44256 400 0 400 54227 3 31/05/2007 31/05/2007 1 0 0 0 3 8 0 0 0 0 0 44256 0 500 -500

Your help will much appreciated.

There is an excellent article about PIVOT by Peter Larsson.

Follow the link :

Pivot table for Microsoft SQL Server

Thanks

Naras.

|||

For quite a few examples of using PIVOT, do a search on this Forum for the keyword 'Pivot'. Amazing how often this question is asked, and how often folks don't seem to bother looking in the archives to find their answer.

|||

Thanks Naras,

I'm not looking for help in Pivot functionality of sql2005 as it won't be usefull for me. If you look at the first table, i want the first column Dim to be used for the new columns i.e. Dim1, Dim2, Dim3 will be columns in the crosstab result but then i want DimCode to appear under respective columns without any aggregations. In pivot you've to use aggregate function. Also in crosstab result if look at the record with jrnentry 54222, there are two lines with the same aaGLDistId i.e. i don't want to aggregate values within dim1, dim2 etc.

Thanks

Vivek

|||

Vivek,

You are wrong. You can achive it using the PIVOT Operator. See the below query..

Code Snippet

Create Table #data (

[Dim] int ,

[DimCode] int ,

[JrnlEntry] int ,

[GLDistId] int ,

[TrxDate] datetime ,

[GLPostDT] datetime ,

[TrxType] int ,

[AccountIndex] int ,

[DebitAmt] int ,

[CreditAmt] int ,

[Amount] int

);

Set Dateformat DMY

Insert Into #data Values('4','2','54222','1','25/05/2007','25/05/2007','1','44256','617','0','617');

Insert Into #data Values('4','11991','54222','1','25/05/2007','25/05/2007','1','44256','617','0','617');

Insert Into #data Values('5','7','54222','1','25/05/2007','25/05/2007','1','44256','617','0','617');

Insert Into #data Values('5','5','54222','1','25/05/2007','25/05/2007','1','44256','617','0','617');

Insert Into #data Values('4','1','54225','1','31/05/2007','31/05/2007','1','44256','600','0','600');

Insert Into #data Values('5','5','54225','1','31/05/2007','31/05/2007','1','44256','600','0','600');

Insert Into #data Values('4','12821','54225','2','31/05/2007','31/05/2007','1','44256','400','0','400');

Insert Into #data Values('5','4','54225','2','31/05/2007','31/05/2007','1','44256','400','0','400');

Insert Into #data Values('4','3','54227','3','31/05/2007','31/05/2007','1','44256','0','500','-500');

Insert Into #data Values('5','8','54227','3','31/05/2007','31/05/2007','1','44256','0','500','-500');

Select

[JrnlEntry]

,[GLDistId]

,[TrxDate]

,[GLPostDT]

,[TrxType]

,Isnull([1],0) Dim1

,Isnull([2],0) Dim2

,Isnull([3],0) Dim3

,Isnull([4],0) Dim4

,Isnull([5],0) Dim5

,Isnull([6],0) Dim6

,Isnull([7],0) Dim7

,Isnull([8],0) Dim8

,Isnull([9],0) Dim9

,Isnull([10],0) Dim10

,[AccountIndex]

,[DebitAmt]

,[CreditAmt]

,[Amount] From

(

Select

[Dim]

,[DimCode]

,[GLDistId]

,[JrnlEntry]

,[TrxDate]

,[GLPostDT]

,[TrxType]

,[AccountIndex]

,[DebitAmt]

,[CreditAmt]

,[Amount]

,Row_Number() Over (Partition By [Dim],[GLDistId],[JrnlEntry] Order By [Dim],[GLDistId],[JrnlEntry]) RowId

From

#data

) as Data

Pivot

(

Max([DimCode])

For [Dim] in

(

[1],[2],[3],[4],[5],[6],[7],[8],[9],[10]

)

) As PVT

|||

Thanks Manivannan, much appreciated. Its returning correct result. I almost spent the whole day on this yesterday with frustration.

Regards,

Vivek

cross-tab query

Hi all.

we have a department that has an access database with a bunch of queries in it. They want us to convert it to sql server. One of the queries is a cross-tab query. Is there an easy way to create this in sql? the column headings are the value of column from a table. This could change each month that they run it. How do I make the column heading a variable? I'm guessing a stored procedure would be best. Does anyone have any suggestions?

Thanks so much.
ODanielsWhat version of SQL Server?

If 2000, then no, there is no "easy" way. This is one of the more irritating omissions from SQL 2k.

If you have 2k5, then you're good to go.|||it's not too hard in sql 2k. there is even a section in BOL if you need a how to.|||The section in BOL requires you to know all of the "column" values in advance...|||It's sql2k.
That's what I saw also - need to know all the column values in advance.
I can build the table with a bunch of statements, I was just hopeing there was an easier way.|||I have a crosstab sproc tucked away somewhere. I'll dig it up in a bit here.

... and there she is:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO



CREATE PROCEDURE crosstab @.XField varChar(50), @.XTable varChar(50),
@.XWhereString varChar(250), @.XFunction varChar(10), @.XFunctionField varChar(20), @.XRow varchar(40)
AS
Declare @.SqlStr nvarchar(4000)
Declare @.tempsql nvarchar(4000)
Declare @.SqlStrCur nvarchar(4000)
Declare @.col nvarchar(100)

set @.SqlStrCur = N'Select [' + @.XField + '] into ##temptbl_Cursor from [' + @.XTable + '] ' + @.XWhereString + ' Group By [' + @.XField + ']'

exec sp_executesql @.sqlstrcur

declare xcursor Cursor for Select * from ##temptbl_Cursor
open xcursor

Fetch next from xcursor
into @.Col

While @.@.Fetch_Status = 0
Begin
set @.Sqlstr = @.Sqlstr + ", "
set @.tempsql = isnull(@.sqlstr,'') + isnull(@.XFunction + '( Case When ' + @.XField + " = '" +@.Col +
"' then [" + @.XFunctionField + "] Else 0 End) As [" + @.Col + "]" ,'')
set @.Sqlstr = @.tempsql
Fetch next from xcursor into @.Col

End

set @.tempsql = 'Select ' + @.XRow + ', ' + @.Sqlstr + ' From ' + @.XTable +
@.XWhereString + ' Group by ' + @.XRow + ' Order By ' + @.XRow
set @.Sqlstr = @.tempsql
PRINT @.tempsql
Close xcursor
Deallocate xcursor

set @.tempsql = N'Drop Table ##temptbl_Cursor'
exec sp_executesql @.tempsql

exec sp_executesql @.Sqlstr


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

I should add that I shameless stole this from some random website, though I can't remember where it was.|||You are awsome Teddy.
I will give that a try. Thank you.

Crosstab query

Can someone show me how to write a query to convert this:

Year, Account, Qtr1, Qtr2, Qtr3, Qtr4
2004, 12345, 100, 200, 300, 400
2005, 23456, 200, 300, 400, 500

to this:

Year, Account, Quarters, Amount
2004, 12345, Qtr1, 100
2004, 12345, Qtr2, 200
2004, 12345, Qtr3, 300
2004, 12345, Qtr4, 400
2005, 23456, Qtr1, 200
2005, 23456, Qtr2, 300
2005, 23456, Qtr3, 400
2005, 23456, Qtr4, 500

So far I've only found examples which convert values to columns but not
for columns to values. TIA... ALHi

You are looking to unpivot your table such as
http://www.umachandar.com/technical...ipts/Main25.htm

Although you will probably doing this one the client.

John

<albundy1998@.hotmail.com> wrote in message
news:1115058028.710708.183670@.f14g2000cwb.googlegr oups.com...
> Can someone show me how to write a query to convert this:
> Year, Account, Qtr1, Qtr2, Qtr3, Qtr4
> 2004, 12345, 100, 200, 300, 400
> 2005, 23456, 200, 300, 400, 500
> to this:
> Year, Account, Quarters, Amount
> 2004, 12345, Qtr1, 100
> 2004, 12345, Qtr2, 200
> 2004, 12345, Qtr3, 300
> 2004, 12345, Qtr4, 400
> 2005, 23456, Qtr1, 200
> 2005, 23456, Qtr2, 300
> 2005, 23456, Qtr3, 400
> 2005, 23456, Qtr4, 500
> So far I've only found examples which convert values to columns but not
> for columns to values. TIA... AL|||albundy1...@.hotmail.com wrote:
> Can someone show me how to write a query to convert this:
> Year, Account, Qtr1, Qtr2, Qtr3, Qtr4
> 2004, 12345, 100, 200, 300, 400
> 2005, 23456, 200, 300, 400, 500
> to this:
> Year, Account, Quarters, Amount
> 2004, 12345, Qtr1, 100
> 2004, 12345, Qtr2, 200
> 2004, 12345, Qtr3, 300
> 2004, 12345, Qtr4, 400
> 2005, 23456, Qtr1, 200
> 2005, 23456, Qtr2, 300
> 2005, 23456, Qtr3, 400
> 2005, 23456, Qtr4, 500
> So far I've only found examples which convert values to columns but
not
> for columns to values. TIA... AL

One simple way is via unions:
select Year, Account, 'Qty1' AS Quarters, Qtr1 AS Amount
from acctqtrs
union
select Year, Account, 'Qty2' AS Quarters, Qtr2 AS Amount
from acctqtrs
...

--
David Rowland
How well is your server performing
http://dbmonitor.tripod.com
-- New release of DBMonitor is almost ready with new functionality

Crosstab Convert

TRANSFORM IIf(Sum(IIf([blockinyield]=True,[SIZE],0))>0,Sum([Y_TOTAL_ton])/Sum(IIf([blockinyield]=True,[SIZE],0)),0) AS Yield_THA
SELECT OILPALM.NAME, OILPALM.YEAR, formatyear([year]) AS yearDisplay, Count(OILPALM.BLOCK) AS CountOfBLOCK
FROM OILPALM
GROUP BY OILPALM.NAME, OILPALM.YEAR
PIVOT Year([D_PLANTED]);

how to convert those query to either sql 2000 or 2005

please help me.....!!

I am really wondering about this Question...

I am seeing the same question for 3rd time in this forum...

Any how if you use SQL Server 2000, you have to use the following dynamic query...

Code Snippet

Declare @.JoinQuery as Varchar(1000);
Declare @.SelectQuery as Varchar(1000);
Declare @.PreparedJoinQuery as Varchar(1000);
Declare @.PreparedSelectQuery as Varchar(1000);
Select @.JoinQuery = '', @.SelectQuery = ''
Select @.PreparedJoinQuery = 'Left Outer Join OILPALM as [?] On [?].Year=? and [?].NAME=[Main].NAME '
Select @.PreparedSelectQuery =',Count([?].BLOCK) as [?]'
Select
@.JoinQuery = @.JoinQuery + Replace(@.PreparedJoinQuery,'?',Cast(year as Varchar))
,@.SelectQuery = @.SelectQuery + Replace(@.PreparedSelectQuery,'?',Cast(year as Varchar)) From OILPALM Group By Year

Exec ('Select [Main].NAME' + @.SelectQuery + ' From (Select Distinct NAME From OILPALM) as [Main]' + @.JoinQuery + ' Group By [Main].NAME')

If you use SQL Server 2005,

Code Snippet

select * from oilpalm PIVOT( SUM(Block) for Year In ([2006], [2005])) as Pvt

Or --Dynamic Values will fetch all the data need not to hardcode the year values

Declare @.PreparedStatement as varchar(1000);
Declare @.Query as varchar(1000);
Select @.PreparedStatement = ',[?]', @.Query ='';
Select @.Query = @.Query + Replace(@.PreparedStatement,'?',Year) From oilpalm Group By Year;
Select @.Query = Substring(@.Query,2,Len(@.Query))

Exec ('Select * from oilpalm PIVOT( SUM(Block) for Year In (' + @.Query + ')) as Pvt')

|||is there any code which is more simple then you posted|||

For SQL Server 2000.

Code Snippet

Select
[Main].NAME,
Count([2005].BLOCK) as [2005],
Count([2006].BLOCK) as [2006]
From
(Select Distinct NAME From OILPALM) as [Main]
Left Outer Join OILPALM as [2005] On [2005].Year=2005 and [2005].NAME=[Main].NAME
Left Outer Join OILPALM as [2006] On [2006].Year=2006 and [2006].NAME=[Main].NAME
Group By
[Main].NAME

For SQL Server 2005

Code Snippet

select

*

from

oilpalm

PIVOT( SUM(Block) for Year In ([2006], [2005])) as Pvt

|||

I suggest that you take the time to work through the suggestions that have been provided to your previous posts until you understand the process.

When you work through the process and begin to understand the process, you will be able to work these out for yourself. (Otherwise, we might think that you are just trying to get folks to do your work for you. And that probably wouldn't be fair to you ...)

cross table

Sir,

My query is

select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/11/2006'
group by task_date
union
select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/12/2006'
group by task_date
union
select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/13/2006'
group by task_date
union
select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/14/2006'
group by task_date
union
select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/15/2006'
group by task_date
union
select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/16/2006'
group by task_date

My out put is

Date Program Design Upload Testing Meeting Others

2006-09-11 00:00:00.000 42 0 0 8 2 1

2006-09-12 00:00:00.000 77 0 0 4 0 0

2006-09-13 00:00:00.000 56 0 0 8 0 1

2006-09-14 00:00:00.000 63 0 0 6 0 1

2006-09-15 00:00:00.000 63 0 0 6 0 1

Now i want in below format

2006-09-11 2006-09-11 etc

Program 42 77

Design 0 0

Upload 0 0

Testing 8 4

Meeting 2 0

Others 1 0

Total 53 81

How to convert in this format .

--From your output as table:transposetable$:

SELECT ISNULL(cat, 'Total') ,SUM([9/11/2006]) AS '9/11/2006',SUM([9/12/2006]) AS '9/12/2006',SUM([9/13/2006]) AS '9/13/2006',SUM([9/14/2006]) AS '9/14/2006',

SUM([9/15/2006]) AS '9/15/2006'

FROM (SELECT cat, MIN(CASE WHEN t.tDate = '9/11/2006' THEN Program END) AS '9/11/2006',

MIN(CASE WHEN t.tDate = '9/12/2006' THEN Program END) AS '9/12/2006',

MIN(CASE WHEN t.tDate = '9/13/2006' THEN Program END) AS '9/13/2006',

MIN(CASE WHEN t.tDate = '9/14/2006' THEN Program END) AS '9/14/2006',

MIN(CASE WHEN t.tDate= '9/15/2006' THEN Program END) AS '9/15/2006'

FROM

(SELECT 'Program' as cat, tDate, Program FROM transposetable$

UNION ALL

SELECT 'Design', tDate, Design FROM transposetable$

UNION ALL

SELECT 'Upload',tDate, Upload FROM transposetable$

UNION ALL

SELECT 'Testing', tDate, Testing FROM transposetable$

UNION ALL

SELECT 'Meeting', tDate, Meeting FROM transposetable$

UNION ALL

SELECT 'Others', tDate, Others FROM transposetable$

) t

GROUP BY cat ) x

GROUP BY cat

WITH ROLLUP

|||

--SQL Server 2005, Louis's idea. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326847&SiteID=1

WITH myCTE as(

select tDate, cat, value

from ( select tDate, Program, Design, Upload, Testing, Meeting,Others

from transposetable$) p

UNPIVOT

(value for cat in ([Program], [Design], [Upload],[Testing],[Meeting],[Others])) as unpvt)

SELECT ISNULL(cat, 'Total'),SUM([9/11/2006]) AS '9/11/2006',SUM([9/12/2006]) AS '9/12/2006',SUM([9/13/2006]) AS '9/13/2006',SUM([9/14/2006]) AS '9/14/2006',SUM([9/15/2006]) AS '9/15/2006'

FROM

(select cat, tDate,value

from myCTE) as rotated

PIVOT

(SUM(value) FOR tDate in ([9/11/2006],[9/12/2006],[9/13/2006],[9/14/2006],[9/15/2006])) as pvt

GROUP BY cat

WITH ROLLUP

|||

Hi,

have you looked at the UNPIVOT function?

Sten-Gunnar