Crystal Reports and Databinding

Hi,

is there any kind of possibility to bind the fields in a Crystal reports on an self created object or its class description? I can not bind the report fields on a database, so I would like to bind an ArrayList or something like that to the report.

It is possible? If yes, where can I find stuff about how to do it?Can you explain with more details?
Do you want to send array list and want to display them?|||Yes, I have different typed collections and want to show objects inside of it.|||Then use parameter and send that value to it|||Seems that it is what I want. Is there any kind of possiblity to use mapping names like in datasets or do I have to make the mapping by hand?

Any tutorials or samples would be helpful.

Crystal reports across multiple pages

Using crystal reports, I want to display many fields from the database. All the fields does not fit in single page, even with maximum page length. If I have some 30 fields, is it possible to display first 20 fields in page 1 and remaining fields in page 2? And continue the same in page 3/4, 5/6 etc? :confused:Yes. You can format the page layout to do so. Add the 20 fields in page one. Add a New section (i.e. page) then add the remaining 10 fields.

Hope This helps,
Vinny P|||Thanks for the response. :wave:
But, while adding the section, I could not see any option to add the page. There are options to add report header, page header, group header, footers etc, but not for adding the page itself. :confused:
Can you make it more clear?|||I think what shashidharkr had in mind was being able to display very wide reports, one data row on the line, spanning multiple pages horizontally. The answer is "no". You cannot create wider reports than your printer driver supports. This is one of the most annoying limitations of the Crystal Reports. If your printer driver supports A2 landscape, you are lucky, but when displaying and printing this report on another machine or printer, the report will be truncated to whatever other printer driver supports.

The only exception is the crosstab object that can span itself to multiple pages. So there is a tricky workaround to use a hidden crosstab to span a usual report:
http://support.businessobjects.com/library/kbase/articles/c2014205.asp
But it comes with severe limitations (only first page will be spanned, the export to excel will be ruined etc).

For myself, I choose A3-landscape for wide reports. A3 is supported by most printer drivers. For extra-wide reports, I use A2-landscape. On client machine, the report gets truncated on the screen but export to excel will still export all fields.|||wapper,
You got my problem right! ;)

It is really surprising to know that crystal report has such a serious limitation. :o I need to create wide report, which spans across more than one page and thousands of rows of data. Even the workaround suggested by you may not be of much help, as I have many rows of data.

I think it makes no sense to limit the report width based on the paper sizes! If the report is wider, it should span to next page. I understand other reporting tools like cognos has such feature.

Any clue, if crystal report is going to fix this in future releases atleast? I hope this is in Seagate's agends. However, if we want to voice our concern, where can this be raised?|||I think it makes no sense to limit the report width based on the paper sizes! If the report is wider, it should span to next page. I understand other reporting tools like cognos has such feature.

The problem is, Crystal uses windows forms to compose the layout of the report. Forms are related to printer driver. You can experiment with creating custom forms (Control panel-Printers-File-Server properties-Forms) but custom forms are for one computer only, and IIRC there was also a limitation that you cannot create larger forms than some value, can't remember what exactly the limit was.

Any clue, if crystal report is going to fix this in future releases atleast? I hope this is in Seagate's agends. However, if we want to voice our concern, where can this be raised?

If you find such place, please let me know :) I got toooons of stuff to complain about Crystal.|||wapper
Thanks again for the response.

You can experiment with ....
Unfortunately, I can't spend more time on this :) . I have to look for some feasible workarounds. :ehh: I strongly feel that Seagate should resolve this problem. Being widely used report tool, such a major drawback should be addressed immediately. :rolleyes: Still I can't digest that Crystal imposes such a serious limitation on it's users! :(

Just to draw analogy, page width should be independent of paper size as in excel. Irrespective of the width, if paper width is less, excel prints in next page.

I got toooons of stuff to complain about Crystal.
Can the moderators or any others help on this?|||This problem has always been around and it is still not fixed in CR XI. Guess it is some kind of fundamental feature that can't be changed easily. Of course Crystal guys are aware of it because people have been crying for ages but still no solution, only workarounds.

You can try different support options in their web if you want to yell at someone. BTW there is also bunch of forums there. I don't know if those are actually monitored by Crystal.sql

Crystal Reports 8.0 & long strings

Hello,
I need to know how can I display a fields data which has more than 255 chars.
Is there a solution of this problem?[ Moved thread ]|||where was this thread moved to? I am having this same issue.

Thanks.sql

crystal reports

how can i design the crystal reports in windows application using C#.
what code i should write to get all the fields in a report format which should be generated into a crystal report.Visit www.VBCity.com and search in Crystal Reports section|||As you specified on that topic, Doevents would do that. You need to write it twice or thrice

Doevents
Doevents
Doevents

Crystal Report X & Access 2003 Db

I am running CR X and Access 2003 database.

I have a table defined as Employee and one of the fields is called RESDate and is defined as Date/Time.

The RESDate has a default value Date () - 1. Bascially, I am deriving yesterday's date. When I view the table in Access, I see yesterday's date in the field.

Now in CR X:
I made a new connection and brought in this Access database and this one table. I moved the RESDate to detail line.

I do not see yesterday's date in CR. I did browse of the field in CR X and it shows blanks also. Any ideas ?

Thanks for your help in advance.

Regards
LB1That sounds odd, in CR right click on the field name and select show type,
does the field show up as a string ? or as date / time or ? Also can you create a query, or a view and try reporting off the query / view ?

crystal report -sorting issue

I am new to crystal reporting
Ii have an existing report with Job Numbers, Job Description and Total fields, The report works fine with the right sorting and groping in the Crystal report viewer.
But once loaded in the ASP page it stops sorting and grouping in the asp page - companys brower page.
Any suggestions on what must have gone wrong.
Thanks in advance

SandyTry checking in the
Menu File / Options / Database tab / Perrform Grouping On Server|||Hi Sandy, saw a very similar post somewhere else, this sounds odd, if it sorts correctly in CR, it should sort in ASP the same, you may want to have a web person check to make sure something is not sorting your report once it gets to the page. Sorry I am not much help...|||Try checking in the
Menu File / Options / Database tab / Perrform Grouping On Server

hi

done the changes-- but how would it reflect in my asp page

thanks

sandy|||are you playing with SQLQueryString property?
or modifying 'Order By' statement in asp.

what amount sub reports there're in your main report?
what amount records show your report?|||are you playing with SQLQueryString property?
or modifying 'Order By' statement in asp.

what amount sub reports there're in your main report?
what amount records show your report?

NO there is no subreports in my report - its a simple report - with fields like job, description and amount. - with a total and a grand total in the end. the report is coming to from the viewer though an asp page. Its sorted in the
report but when it has sort in the asp page ... it refuses to do the same. but when i click on the job field it sorts by descending and with one more click to goes to ascending. Now i want a default ascending as in the report.
I am a newbie in asp..
If you can please help

sandy

Crystal Report ****

Alright Boys and Girls,

I am in ****. I have a report, and in this report I use multipe formula fields to count 4 different occurences. So for each record, and each month from 1 year ago to today, i count how many people were housed, how many people moved in, how many people moved out, and what my baseline of occupants is. I use another formula field to get each month from this month, back 12 months obviously, which is used to determine housed, ect. ect..
I have no problem getting my totals with my "formulated months". But when I go to graph it, im screwed. It wants one column for the x axis, say a month column with 12 values. Well I have 12 formulated fields giving me the 12 months. For each of those months i have 4 calculations letting me know housed, moved out, ect. So for each record I have 48 calculations happening. Can anyone give me any sort of idea on how to graph this or atleast compress it down so i can graph it.

Thanks,
RyanHi,

How you are calculating the counts (housed, moved in).

I had a situation like this. For each and every month, I had to calculate multiple counts. I'd created a group for date field and for every month. Created running totals for each group.

In this way, we can easily add fields in our chart.

Just generate the chart on change of Date and add the running total fields in the next list. This is for Bar chart.

Hope this will work!|||Well here is my pickle. I don't have a single column for the date. For instance, say i have a record, for that single record, i get todays date, and i calculate 12 formulated fields for each month from todays date back 12 months. so, if you look at the record, you see 12 formulated months, beneath that record, you are going to calculate whether or not someone moved in, moved out, was housed, and your base line. So underneath these 12 months, i have 4 parameters, that are working off the parameter month fields, which in turn are calculated from todays date. Thats the tuff part, I don't have a single date column to work from, I wish to god I did. Im stuck, if anyone knows of any great crystal resource sites I would be happy to hear it!

Crystal Mandatory Fields

Hiya,

I'm writing a crystal report linking with an Access Database.

basically I need to say If Address line1 = "" then display section A if not, do nothing,

but I have to do this for about 30 fields, How would i go about doing this?

Basically, it picks up data from the database,If everything is filled in when the report is run, the report orints out fine, if something is blank or a null value then it will pick up details section B which will say "The following fields need to be filled in" and then it will list the fields?

Any ideas

Thanksif you want to suppress whole section A if addressline ="" then right click on section A -> format section -> there is a button beside suppress text click on it and write down:
if addressline ="" then true else false

It will suppress that section and if you want to suppress some fields only then you have write this on each and every field in design view and for that you have to right click on that field -> formate field -> and here also same you have to write "if addressline ="" then true else false" in suppress button script.

Best of Luck|||better use

if isnull(addressline) then
true
else
false

Cross-Tab - How to add logic in summarized fields

Hi, I'm using Crystal Report Developper XI with Eclipse and I'd like to know if it's possible to put some logic in a Cross-table

Here's my problem:
I made a simple cross-table to track orders delivery

Example:
---| 20/06/07 21/06/07 22/06/07
-------------------
Order 1 | 44 55 66
Order 2 | 66 55 33
Order 3 | DELI 22 66

The problem is that when I display this cross-tab, I must checked whether or not this order has been delivered (it's a simple boolean flag), if yes, I want to display DELI, otherwise I display the quantity

I could I do that given that the quantities displayed are the result of a SUM({ORDER.QUANTITY}) ??

Thanks in advance !
Philgo to cross tab expert, you should see new formula near bottom right corner, create your formula and add to your cross tab.|||It seems like I don't have access to that cross-tab expert, i'm using Crystal Report XI Developper (plugin for eclipse) and i'm not using the Crystal report server.

Is there another of doing it... without using cross-tab expert ?!|||The problem is that when I display this cross-tab, I must checked whether or not this order has been delivered (it's a simple boolean flag), if yes, I want to display DELI, otherwise I display the quantity

I could I do that given that the quantities displayed are the result of a SUM({ORDER.QUANTITY}) ??
Phil

ig you have access to your query into DB, I suggest you create a view and you could write there the logic.

bit litle sample

create or replace view nameview as
select................
case when myflag=1 then
ORDER.QUANTITY
else
0 'in your report takes 0 when run (Sum)
end as ForCrossTabData
..
.....

cross tab type query in sql

I have three tables:
tblBook has the fields bookID, bookRangeID, bookSubjectID, bookCode
tblBookRange has the fields bookRangeID, bookRangeDescription
tblBookSubject has the fields bookSubjectID, bookSubjectDescription
so some typical data in tblBook might be:
1, 1, 1, B1HBSCI
2, 1, 2, B2HBFRE1
3, 1, 3, B3HBGER
4, 2, 1, B4PBSCI
5, 2, 2, B5PBFRE
6, 2, 3, B6PBGER
7, 3, 1, B7CDSCI
8, 3, 2, B8CDFRE
9, 3, 3, B9CDGER1
10, 3, 3, B10CDGER2
11, 1, 2, B11HBFRE2
tblBookRange would be:
1, HardBack
2, PaperBack
3, CD Rom
tblBookSubject would be:
1, Science
2, French
3, German
I'd like to create a query which will return me the subjects along the
top, the book range down the side, and the bookcodes in the cells, a
bit like this:
BookRange , Science, French, German
HardBack , B1HBSCI, B2HBFRE1 B11HBFRE2, B3HBGER
PaperBack , B4PBSCI, B5PBFRE, B6PBGER
CD Rom , B7CDSCI, B8CDFRE, B9CDGER1 B10CDGER2
Does that make any sense? So basically I'd like to get some kind of
dynamic SQL working which will do this kind of thing. I don't want to
hard code the subjects in or the book ranges. I get the feeling that
dynamic SQL is the way forward with this and possibly using a cursor or
two too, but it got quite nasty and convoluted when I tried various
attempts to get it working. (one of the ways I tried included working
out each result in a dynamic script, but it ran out of characters as
there were too many "subjects".)
If anyone has any nice but quite dynamic solutions, I'd be delighted to
hear.
(and I know some of you have already told me you don't like tables
beginnig with tbl, but I'm not hear for a lecture on naming
conventions, I'm hear to learn and share ideas :o) )The RAC utility could do this for you quite easily (similiar in
concept to Access crosstab but much more powerful).
But then you wouldn't have the opportunity to write your
own dynamic code and maintain it:)
www.rac4sql.net|||The RAC utility is well worth the relatively inexpensive cost. I think it i
s
$60 per database or $300+ for a site license. If you are doing many crossta
b
queries, you will save this much in time in the first couple of days.
Another thing to note though, the not-yet-officially-released SQL Server 200
5
has a pivot function which will easliy perform crosstab queries.
Archer
"Pike" wrote:

> The RAC utility could do this for you quite easily (similiar in
> concept to Access crosstab but much more powerful).
> But then you wouldn't have the opportunity to write your
> own dynamic code and maintain it:)
> www.rac4sql.net
>
>|||If you are using SQL 2000/SQL 7.0, then yes you have to use dynamic SQL (or
a
report writer that uses dynamic SQL) and all the evil that goes with it or h
ave
your report writer create your crosstab type result. You could also use
something like Excel (or other report writer) to call to a stored proc to ge
t
the data you want and have Excel do the pivoting/crosstabbing.
Thomas
"Neil" <neildog_remove*@._remove_majiccarpet.fsnet.co.uk> wrote in message
news:d753v9$11b$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
>I have three tables:
>
> tblBook has the fields bookID, bookRangeID, bookSubjectID, bookCode
>
> tblBookRange has the fields bookRangeID, bookRangeDescription
>
> tblBookSubject has the fields bookSubjectID, bookSubjectDescription
>
> so some typical data in tblBook might be:
>
> 1, 1, 1, B1HBSCI
> 2, 1, 2, B2HBFRE1
> 3, 1, 3, B3HBGER
> 4, 2, 1, B4PBSCI
> 5, 2, 2, B5PBFRE
> 6, 2, 3, B6PBGER
> 7, 3, 1, B7CDSCI
> 8, 3, 2, B8CDFRE
> 9, 3, 3, B9CDGER1
> 10, 3, 3, B10CDGER2
> 11, 1, 2, B11HBFRE2
> tblBookRange would be:
>
> 1, HardBack
> 2, PaperBack
> 3, CD Rom
>
> tblBookSubject would be:
>
> 1, Science
> 2, French
> 3, German
>
> I'd like to create a query which will return me the subjects along the
> top, the book range down the side, and the bookcodes in the cells, a
> bit like this:
>
> BookRange , Science, French, German
> HardBack , B1HBSCI, B2HBFRE1 B11HBFRE2, B3HBGER
> PaperBack , B4PBSCI, B5PBFRE, B6PBGER
> CD Rom , B7CDSCI, B8CDFRE, B9CDGER1 B10CDGER2
>
> Does that make any sense? So basically I'd like to get some kind of
> dynamic SQL working which will do this kind of thing. I don't want to
> hard code the subjects in or the book ranges. I get the feeling that
> dynamic SQL is the way forward with this and possibly using a cursor or
> two too, but it got quite nasty and convoluted when I tried various
> attempts to get it working. (one of the ways I tried included working
> out each result in a dynamic script, but it ran out of characters as
> there were too many "subjects".)
>
> If anyone has any nice but quite dynamic solutions, I'd be delighted to
> hear.
>
> (and I know some of you have already told me you don't like tables
> beginnig with tbl, but I'm not hear for a lecture on naming
> conventions, I'm hear to learn and share ideas :o) )
>
>|||Does RAC simply do the dynamic SQL for you (i.e. build a dynamic SQL stateme
nt
and return the results)?
Thomas
"bagman3rd" <bagman3rd@.discussions.microsoft.com> wrote in message
news:10B2ABB7-0B0B-4AC3-9CDA-BC7721E62EAC@.microsoft.com...
> The RAC utility is well worth the relatively inexpensive cost. I think it
is
> $60 per database or $300+ for a site license. If you are doing many cross
tab
> queries, you will save this much in time in the first couple of days.
> Another thing to note though, the not-yet-officially-released SQL Server 2
005
> has a pivot function which will easliy perform crosstab queries.
> Archer
> "Pike" wrote:
>|||You could do something like this:
create table tblBook (
bookID int, bookRangeID int, bookSubjectID int, bookCode varchar(50))
insert into tblBook values(1, 1, 1, 'B1HBSCI')
insert into tblBook values(2, 1, 2, 'B2HBFRE1')
insert into tblBook values(3, 1, 3, 'B3HBGER')
insert into tblBook values(4, 2, 1, 'B4PBSCI')
insert into tblBook values(5, 2, 2, 'B5PBFRE')
insert into tblBook values(6, 2, 3, 'B6PBGER')
insert into tblBook values(7, 3, 1, 'B7CDSCI')
insert into tblBook values(8, 3, 2, 'B8CDFRE')
insert into tblBook values(9, 3, 3, 'B9CDGER1')
insert into tblBook values(10, 3, 3, 'B10CDGER2')
insert into tblBook values(11, 1, 2, 'B11HBFRE2')
create table tblBookRange (bookRangeID int, bookRangeDescription
varchar(50))
insert into tblBookRange values (1, 'HardBack')
insert into tblBookRange values (2, 'PaperBack')
insert into tblBookRange values (3, 'CD Rom')
create table tblBookSubject(bookSubjectID int, bookSubjectDescription
varchar(50))
insert into tblBookSubject values(1, 'Science')
insert into tblBookSubject values(2, 'French')
insert into tblBookSubject values(3, 'German')
BookRange , Science, French, German
HardBack , B1HBSCI, B2HBFRE1 B11HBFRE2, B3HBGER
PaperBack , B4PBSCI, B5PBFRE, B6PBGER
CD Rom , B7CDSCI, B8CDFRE, B9CDGER1 B10CDGER2
declare variables
declare @.p char(1000)
declare @.i char(12)
declare @.cntm int
declare @.cntn int
declare @.m int
declare @.n int
set @.p = ''
select @.cntm=count(distinct bookRangeID) from tblBookRange
set @.m = 1
select @.cntn=count(distinct bookSubjectID) from tblBookSubject
set @.n = 1
Process until no more items
while @.m <= @.cntm
begin
while @.n <= @.cntn
begin
string together all items with a comma between
select @.i = bookRangeDescription, @.p = rtrim(@.p) + ' ' + bookCode
from tblBook a join tblbookRange b on a.bookRangeID = b.bookRangeID
where a.bookRangeID = @.m and a.bookSubjectID = @.n
set @.n = @.n + 1
set @.p = rtrim(@.p) + ','
end
print detail row
print @.i + ' ' + rtrim(substring(@.p,1,len(@.p)))
set @.n = 1
set @.m = @.m + 1
set @.p = ''
end
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"Neil" <neildog_remove*@._remove_majiccarpet.fsnet.co.uk> wrote in message
news:d753v9$11b$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
> I have three tables:
>
> tblBook has the fields bookID, bookRangeID, bookSubjectID, bookCode
>
> tblBookRange has the fields bookRangeID, bookRangeDescription
>
> tblBookSubject has the fields bookSubjectID, bookSubjectDescription
>
> so some typical data in tblBook might be:
>
> 1, 1, 1, B1HBSCI
> 2, 1, 2, B2HBFRE1
> 3, 1, 3, B3HBGER
> 4, 2, 1, B4PBSCI
> 5, 2, 2, B5PBFRE
> 6, 2, 3, B6PBGER
> 7, 3, 1, B7CDSCI
> 8, 3, 2, B8CDFRE
> 9, 3, 3, B9CDGER1
> 10, 3, 3, B10CDGER2
> 11, 1, 2, B11HBFRE2
> tblBookRange would be:
>
> 1, HardBack
> 2, PaperBack
> 3, CD Rom
>
> tblBookSubject would be:
>
> 1, Science
> 2, French
> 3, German
>
> I'd like to create a query which will return me the subjects along the
> top, the book range down the side, and the bookcodes in the cells, a
> bit like this:
>
> BookRange , Science, French, German
> HardBack , B1HBSCI, B2HBFRE1 B11HBFRE2, B3HBGER
> PaperBack , B4PBSCI, B5PBFRE, B6PBGER
> CD Rom , B7CDSCI, B8CDFRE, B9CDGER1 B10CDGER2
>
> Does that make any sense? So basically I'd like to get some kind of
> dynamic SQL working which will do this kind of thing. I don't want to
> hard code the subjects in or the book ranges. I get the feeling that
> dynamic SQL is the way forward with this and possibly using a cursor or
> two too, but it got quite nasty and convoluted when I tried various
> attempts to get it working. (one of the ways I tried included working
> out each result in a dynamic script, but it ran out of characters as
> there were too many "subjects".)
>
> If anyone has any nice but quite dynamic solutions, I'd be delighted to
> hear.
>
> (and I know some of you have already told me you don't like tables
> beginnig with tbl, but I'm not hear for a lecture on naming
> conventions, I'm hear to learn and share ideas :o) )
>
>|||Yes. Here is the sql to execute a RAC stored procedure. The tool that
comes with RAC is not very straightforward, but manipulating the SQL to get
what you want is.
Execute rac
@.transform = 'max(ltrim(rtrim([qyRac_hits].[x]))) as result))',
@.rows = '[qyRac_hits].[test_name] & [qyRac_hits].[Analyte]',
@.pvtcol = '[qyRac_hits].[ENVIRON_sample_id_mod]',
@.racheck = 'y',
@.from = '[qyRac_hits]',
@.where = 'ENVIRON_sample_id_mod like ~%emw1-%~
and collection_date < ~4/1/2005~',
@.grand_totals='n',
@.defaultexceptions='Funct & Totals'
Archer
"Thomas Coleman" wrote:

> Does RAC simply do the dynamic SQL for you (i.e. build a dynamic SQL state
ment
> and return the results)?
>
> Thomas
>
> "bagman3rd" <bagman3rd@.discussions.microsoft.com> wrote in message
> news:10B2ABB7-0B0B-4AC3-9CDA-BC7721E62EAC@.microsoft.com...
>
>|||Hello Thomas,
For what it's worth:
Most novice and even experienced sql programmers assume RAC
builds a classic dynamic SELECT statement consisting of gobs
of classic CASE statements as seen in the plethora of sql books
illustrating an sql crosstab.This is NOT what RAC does.Given all
the functionality/features available, this method would be next to
impossible
and even if it could be done it probably would bring the server to a hault.
The basic dynamic query built is a SELECT/GROUP BY query based
on the transform (aggregate(s)), row(s) and the column to be pivoted.
This is the building block from which xtabs,various ranking functionality
and all other basic questions/options start with.The default method that
RAC uses to build the actual result utilitized bcp.So anything you see
in a text book or on a site that shows how to develope a dynamic
crosstab in t-sql bears absolutely no resemblence to RAC:)
The real goal of RAC is to unburdon the user/developer of the messy
details of creating a myraid of dynamic results for various data
manipulation problems without stressing out the query optimizer.
Another example of *What* not *How*:)
Best from,
www.rac4sql.net
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:%23kC1sSiYFHA.228@.TK2MSFTNGP12.phx.gbl...
> Does RAC simply do the dynamic SQL for you (i.e. build a dynamic SQL
> statement and return the results)?|||Understand, but quality of the code was not the reason I asked. My question
related to the permission issues with dynamic SQL. From what I understand, R
AC
would require providing direct table access to the users.
Thomas
"Pike" <stevenospam@.rac4sql.net> wrote in message
news:uCv1hqkYFHA.2796@.TK2MSFTNGP09.phx.gbl...
> Hello Thomas,
> For what it's worth:
> Most novice and even experienced sql programmers assume RAC
> builds a classic dynamic SELECT statement consisting of gobs
> of classic CASE statements as seen in the plethora of sql books
> illustrating an sql crosstab.This is NOT what RAC does.Given all
> the functionality/features available, this method would be next to impossi
ble
> and even if it could be done it probably would bring the server to a hault
.
> The basic dynamic query built is a SELECT/GROUP BY query based
> on the transform (aggregate(s)), row(s) and the column to be pivoted.
> This is the building block from which xtabs,various ranking functionality
> and all other basic questions/options start with.The default method that
> RAC uses to build the actual result utilitized bcp.So anything you see
> in a text book or on a site that shows how to develope a dynamic
> crosstab in t-sql bears absolutely no resemblence to RAC:)
> The real goal of RAC is to unburdon the user/developer of the messy
> details of creating a myraid of dynamic results for various data
> manipulation problems without stressing out the query optimizer.
> Another example of *What* not *How*:)
> Best from,
> www.rac4sql.net
> "Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
> news:%23kC1sSiYFHA.228@.TK2MSFTNGP12.phx.gbl...
>|||"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:%23g5cFSiYFHA.228@.TK2MSFTNGP12.phx.gbl...
> If you are using SQL 2000/SQL 7.0, then yes you have to use dynamic SQL
> (or a report writer that uses dynamic SQL) and all the evil that goes with
> it....
*Evil*...one would think that a professional might pick a more
appropriate term. But since its drummed into the head of every
user so often, it must be so:) Beware of piped pipers with long noses:)

Cross Tab query in SqlServer

Hi,
I have three tables with there fields given in brackets:
User: [userId as int] (PK), [userName as navarchar]
Divisions: [divisionId as int] (PK), [divisionName as nvarchar]
DivisionsOfUsers: [userId as int],[divisionId as int]
the "DivisionsOfUsers" tables has many-to-many relationships between
userid and divisionId.
I would like to generate a result something like this:
Division1 Division2 Division3
User1 1 0 0
User2 0 0 1
User3 1 1 0
User4 0 0 0
and so on...
where "1" indicates that the given User-Division combination exists and
"0" denotes that it doesnt in the "DivisionOfUsers" table.
I have tried all sorts of joins to get this data. But was unable to do
this.
I have been told that this is possible by a cross-tab query. I dont
know how to generate this query.
Can anybody give me a solution for this to be used in Sqlserver 2000 as
well as Sqlserver 2005.
Thanks in advance
Often, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Darsin" <darsin@.gmail.com> wrote in message
news:1164563679.582175.136260@.h54g2000cwb.googlegr oups.com...
> Hi,
> I have three tables with there fields given in brackets:
> User: [userId as int] (PK), [userName as navarchar]
> Divisions: [divisionId as int] (PK), [divisionName as nvarchar]
> DivisionsOfUsers: [userId as int],[divisionId as int]
> the "DivisionsOfUsers" tables has many-to-many relationships between
> userid and divisionId.
> I would like to generate a result something like this:
> Division1 Division2 Division3
> User1 1 0 0
> User2 0 0 1
> User3 1 1 0
> User4 0 0 0
> and so on...
> where "1" indicates that the given User-Division combination exists and
> "0" denotes that it doesnt in the "DivisionOfUsers" table.
> I have tried all sorts of joins to get this data. But was unable to do
> this.
> I have been told that this is possible by a cross-tab query. I dont
> know how to generate this query.
> Can anybody give me a solution for this to be used in Sqlserver 2000 as
> well as Sqlserver 2005.
> Thanks in advance
>
|||Please post DDL, Sample Data, Expected Results (based on your Sample Data).
In the meantime, you might look up the PIVOT operator in SQL 2005.
"Darsin" <darsin@.gmail.com> wrote in message
news:1164563679.582175.136260@.h54g2000cwb.googlegr oups.com...
> Hi,
> I have three tables with there fields given in brackets:
> User: [userId as int] (PK), [userName as navarchar]
> Divisions: [divisionId as int] (PK), [divisionName as nvarchar]
> DivisionsOfUsers: [userId as int],[divisionId as int]
> the "DivisionsOfUsers" tables has many-to-many relationships between
> userid and divisionId.
> I would like to generate a result something like this:
> Division1 Division2 Division3
> User1 1 0 0
> User2 0 0 1
> User3 1 1 0
> User4 0 0 0
> and so on...
> where "1" indicates that the given User-Division combination exists and
> "0" denotes that it doesnt in the "DivisionOfUsers" table.
> I have tried all sorts of joins to get this data. But was unable to do
> this.
> I have been told that this is possible by a cross-tab query. I dont
> know how to generate this query.
> Can anybody give me a solution for this to be used in Sqlserver 2000 as
> well as Sqlserver 2005.
> Thanks in advance
>

Cross Tab query in SqlServer

Hi,
I have three tables with there fields given in brackets:
User: [userId as int] (PK), [userName as navarchar]
Divisions: [divisionId as int] (PK), [divisionName as nvarchar]
DivisionsOfUsers: [userId as int],[divisionId as int]
the "DivisionsOfUsers" tables has many-to-many relationships between
userid and divisionId.
I would like to generate a result something like this:
Division1 Division2 Division3
User1 1 0 0
User2 0 0 1
User3 1 1 0
User4 0 0 0
and so on...
where "1" indicates that the given User-Division combination exists and
"0" denotes that it doesnt in the "DivisionOfUsers" table.
I have tried all sorts of joins to get this data. But was unable to do
this.
I have been told that this is possible by a cross-tab query. I dont
know how to generate this query.
Can anybody give me a solution for this to be used in Sqlserver 2000 as
well as Sqlserver 2005.
Thanks in advanceOften, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Darsin" <darsin@.gmail.com> wrote in message
news:1164563679.582175.136260@.h54g2000cwb.googlegroups.com...
> Hi,
> I have three tables with there fields given in brackets:
> User: [userId as int] (PK), [userName as navarchar]
> Divisions: [divisionId as int] (PK), [divisionName as nvarchar]
> DivisionsOfUsers: [userId as int],[divisionId as int]
> the "DivisionsOfUsers" tables has many-to-many relationships between
> userid and divisionId.
> I would like to generate a result something like this:
> Division1 Division2 Division3
> User1 1 0 0
> User2 0 0 1
> User3 1 1 0
> User4 0 0 0
> and so on...
> where "1" indicates that the given User-Division combination exists and
> "0" denotes that it doesnt in the "DivisionOfUsers" table.
> I have tried all sorts of joins to get this data. But was unable to do
> this.
> I have been told that this is possible by a cross-tab query. I dont
> know how to generate this query.
> Can anybody give me a solution for this to be used in Sqlserver 2000 as
> well as Sqlserver 2005.
> Thanks in advance
>|||Please post DDL, Sample Data, Expected Results (based on your Sample Data).
In the meantime, you might look up the PIVOT operator in SQL 2005.
"Darsin" <darsin@.gmail.com> wrote in message
news:1164563679.582175.136260@.h54g2000cwb.googlegroups.com...
> Hi,
> I have three tables with there fields given in brackets:
> User: [userId as int] (PK), [userName as navarchar]
> Divisions: [divisionId as int] (PK), [divisionName as nvarchar]
> DivisionsOfUsers: [userId as int],[divisionId as int]
> the "DivisionsOfUsers" tables has many-to-many relationships between
> userid and divisionId.
> I would like to generate a result something like this:
> Division1 Division2 Division3
> User1 1 0 0
> User2 0 0 1
> User3 1 1 0
> User4 0 0 0
> and so on...
> where "1" indicates that the given User-Division combination exists and
> "0" denotes that it doesnt in the "DivisionOfUsers" table.
> I have tried all sorts of joins to get this data. But was unable to do
> this.
> I have been told that this is possible by a cross-tab query. I dont
> know how to generate this query.
> Can anybody give me a solution for this to be used in Sqlserver 2000 as
> well as Sqlserver 2005.
> Thanks in advance
>

Cross Tab query in SqlServer

Hi,
I have three tables with there fields given in brackets:
User: [userId as int] (PK), [userName as navarchar]
Divisions: [divisionId as int] (PK), [divisionName as nvarchar]
DivisionsOfUsers: [userId as int],[divisionId as int]

the "DivisionsOfUsers" tables has many-to-many relationships between
userid and divisionId.
I would like to generate a result something like this:
Division1 Division2 Division3
User1 1 0 0
User2 0 0 1
User3 1 1 0
User4 0 0 0

and so on...
where "1" indicates that the given User-Division combination exists and
"0" denotes that it doesnt in the "DivisionOfUsers" table.
I have tried all sorts of joins to get this data. But was unable to do
this.
I have been told that this is possible by a cross-tab query. I dont
know how to generate this query.
Can anybody give me a solution for this to be used in Sqlserver 2000 as
well as Sqlserver 2005.

Thanks in advanceDarsin (darsin@.gmail.com) writes:

Quote:

Originally Posted by

I have three tables with there fields given in brackets:
User: [userId as int] (PK), [userName as navarchar]
Divisions: [divisionId as int] (PK), [divisionName as nvarchar]
DivisionsOfUsers: [userId as int],[divisionId as int]
>
the "DivisionsOfUsers" tables has many-to-many relationships between
userid and divisionId.
I would like to generate a result something like this:
Division1 Division2 Division3
User1 1 0 0
User2 0 0 1
User3 1 1 0
User4 0 0 0
>
and so on...
where "1" indicates that the given User-Division combination exists and
"0" denotes that it doesnt in the "DivisionOfUsers" table.
I have tried all sorts of joins to get this data. But was unable to do
this.
I have been told that this is possible by a cross-tab query. I dont
know how to generate this query.
Can anybody give me a solution for this to be used in Sqlserver 2000 as
well as Sqlserver 2005.

SELECT U.userName,
Division1 = coalesce(MAX(CASE do.divisionID WHEN 1 THEN 1 END), 0),
Division2 = coalesce(MAX(CASE do.divisionID WHEN 2 THEN 1 END), 0),
Division3 = coalesce(MAX(CASE do.divisionID WHEN 3 THEN 1 END), 0)
FROM Users U
LEFT JOIN DivisionOfUsers do ON U.userId = do.userId
GROUP BY U.userName

The MAX in this query is somewhat of a trick. Each CASE expression returns
a non-NULL value for at most one row. So whether we use MIN - or even AVG -
does not matter. But by using MAX and GROUP BY, we don't need to left-join
for each division.

As you might understand from the query, it only handles a known set of
divisions. There is no way to write a query that handles an unknown
number of divisions. That would be a fundamental breach of the relational
foundations: a query returns a table, and a table has a finite number of
columns.

The only way to get an output if the possible columns are not known
beforehand is to use dynamic SQL to build a query like the one above.
Rather than endulging in dynamic SQL yourself, you may want to take a
look at the third-party tool RAC, http://www.rac4sql.net/.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Cross Tab query in SqlServer

Hi,
I have three tables with there fields given in brackets:
User: [userId as int] (PK), [userName as navarchar]
Divisions: [divisionId as int] (PK), [divisionName as nvarchar]
DivisionsOfUsers: [userId as int],[divisionId as int]
the "DivisionsOfUsers" tables has many-to-many relationships between
userid and divisionId.
I would like to generate a result something like this:
Division1 Division2 Division3
User1 1 0 0
User2 0 0 1
User3 1 1 0
User4 0 0 0
and so on...
where "1" indicates that the given User-Division combination exists and
"0" denotes that it doesnt in the "DivisionOfUsers" table.
I have tried all sorts of joins to get this data. But was unable to do
this.
I have been told that this is possible by a cross-tab query. I dont
know how to generate this query.
Can anybody give me a solution for this to be used in Sqlserver 2000 as
well as Sqlserver 2005.
Thanks in advanceOften, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Darsin" <darsin@.gmail.com> wrote in message
news:1164563679.582175.136260@.h54g2000cwb.googlegroups.com...
> Hi,
> I have three tables with there fields given in brackets:
> User: [userId as int] (PK), [userName as navarchar]
> Divisions: [divisionId as int] (PK), [divisionName as nvarchar]
> DivisionsOfUsers: [userId as int],[divisionId as int]
> the "DivisionsOfUsers" tables has many-to-many relationships between
> userid and divisionId.
> I would like to generate a result something like this:
> Division1 Division2 Division3
> User1 1 0 0
> User2 0 0 1
> User3 1 1 0
> User4 0 0 0
> and so on...
> where "1" indicates that the given User-Division combination exists and
> "0" denotes that it doesnt in the "DivisionOfUsers" table.
> I have tried all sorts of joins to get this data. But was unable to do
> this.
> I have been told that this is possible by a cross-tab query. I dont
> know how to generate this query.
> Can anybody give me a solution for this to be used in Sqlserver 2000 as
> well as Sqlserver 2005.
> Thanks in advance
>|||Please post DDL, Sample Data, Expected Results (based on your Sample Data).
In the meantime, you might look up the PIVOT operator in SQL 2005.
"Darsin" <darsin@.gmail.com> wrote in message
news:1164563679.582175.136260@.h54g2000cwb.googlegroups.com...
> Hi,
> I have three tables with there fields given in brackets:
> User: [userId as int] (PK), [userName as navarchar]
> Divisions: [divisionId as int] (PK), [divisionName as nvarchar]
> DivisionsOfUsers: [userId as int],[divisionId as int]
> the "DivisionsOfUsers" tables has many-to-many relationships between
> userid and divisionId.
> I would like to generate a result something like this:
> Division1 Division2 Division3
> User1 1 0 0
> User2 0 0 1
> User3 1 1 0
> User4 0 0 0
> and so on...
> where "1" indicates that the given User-Division combination exists and
> "0" denotes that it doesnt in the "DivisionOfUsers" table.
> I have tried all sorts of joins to get this data. But was unable to do
> this.
> I have been told that this is possible by a cross-tab query. I dont
> know how to generate this query.
> Can anybody give me a solution for this to be used in Sqlserver 2000 as
> well as Sqlserver 2005.
> Thanks in advance
>

Cross tab formula

Hi guys,

I am a Crystal beginner *trying* to make a cross tab formula work.

The db has 2 fields which hold the similar data.
I want to do something like:

If {field1} like "A*" or if {field2} like "A*"
then etc etc

But it is not letting me - it only recognises the first part of the if statement
So, any ideas why thats happening?
Cant you compare or pull from two fields in the same formula?
....and if you can't do it with an if/else then any ideas as to how I would do this?You don't want / need the 2nd "if"

If {field1} like "A*" or {field2} like "A*"
then etc etc