Crystal Reports Group By and MySQL mediumtext field

Hello Friends,

I have connected Crystal Reports to MySQL database 4.x.
Now I want to run a query using the group by option on a mediumtext data type field. But as Crystal Reports does not allow to do a Group by on a mediumtext field is there any way i can convert the field to String and then do a group by ?

Thanks for your help in advance.
Shanker.Do you have unique id value for that column?
If so group by itsql

Crystal Report Error

Hi,

After putting the crystalreportviewer into a form in visual studio .Net, I have choosen the DataBindings properties and type the path of the report source inside. The display in the design window of visual studio can see the report output but when I press F5 to run the debug, the browser came out the following error messge. Does anyone help to solve it?
Thank you.

Server Error in '/WebApplication2' Application.
------------------------

Load report failed.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: CrystalDecisions.CrystalReports.Engine.LoadSaveReportException: Load report failed.

Source Error:

Line 12: <body MS_POSITIONING="GridLayout">
Line 13: <form id="Form1" method="post" runat="server">
Line 14: <CR:CrystalReportViewer id=CrystalReportViewer1 style="Z-INDEX: 101; LEFT: 16px; POSITION: absolute; TOP: 8px" runat="server" Width="936px" Height="1044px" ReportSource='<%# "C:\Program Files\Microsoft Visual Studio .NET 2003\Crystal Reports\Samples\Reports\General Business\World Sales Report.rpt" %>' SelectionFormula="{Customer.h~~Z}>0">
Line 15: </CR:CrystalReportViewer>
Line 16: </form>


Source File: c:\inetpub\wwwroot\WebApplication2\WebForm1.aspx Line: 14

Stack Trace:

[LoadSaveReportException: Load report failed.]
CrystalDecisions.Web.ReportAgent.h()
CrystalDecisions.Web.ReportAgentBase.set_ReportSource(Object value)
CrystalDecisions.Web.ReportAgent.set_ReportSource(Object value)
CrystalDecisions.Web.CrystalReportViewerBase.set_ReportSource(Object value)
ASP.WebForm1_aspx.__DataBindCrystalReportViewer1(Object sender, EventArgs e) in c:\inetpub\wwwroot\WebApplication2\WebForm1.aspx:14
System.Web.UI.Control.OnDataBinding(EventArgs e)
System.Web.UI.Control.DataBind()
System.Web.UI.Control.DataBind()
System.Web.UI.Control.DataBind()
WebApplication2.WebForm1.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\WebApplication2\WebForm1.aspx.vb:26
System.Web.UI.Control.OnLoad(EventArgs e)
System.Web.UI.Control.LoadRecursive()
System.Web.UI.Page.ProcessRequestMain()



------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.2032; ASP.NET Version:1.1.4322.2032If your application is on Windows Server 2003, then try giving 'Read & Execute' authority to the "Network Service" account on your 'Windows\Temp' folder.

If you are using Windows 2000/Xp, then browse to your Documents and Settings folder. There you will find a folder by the name same as your machine name. Go into that folder and assign 'Read & Execute' authority to the 'ASPNET\Local Settings\Temp' folder.
If this doesnt work then try assigning full authority to the above specified folders.

Remeber that this solution will work if your rpts build action is 'Embedded Resource'. I am not sure about the other build actions.

Hope this helps!

Regards!|||i have the same problem
i allready try to change the security permission but it didn't effect

Crosstab Query

Hi All
I have a table as illistarted beloe that cotains three
mod types (x, y and z). How to I write a query that
will return the count of each mod type like a cross tab
ie.
x y z
4 2 3
CREATE TABLE ##Mods
(
ModType CHAR(1),
ModName VARCHAR(20)
)
INSERT INTO ##Mods VALUES ('X', 'CCC')
INSERT INTO ##Mods VALUES ('X', 'CCC')
INSERT INTO ##Mods VALUES ('X', 'CCC')
INSERT INTO ##Mods VALUES ('X', 'AAA')
INSERT INTO ##Mods VALUES ('Y', 'AAA')
INSERT INTO ##Mods VALUES ('Y', 'AAA')
INSERT INTO ##Mods VALUES ('Z', 'BBB')
INSERT INTO ##Mods VALUES ('Z', 'BBB')
INSERT INTO ##Mods VALUES ('Z', 'BBB')
Thanks in Advance.Hi
SELECT COUNT(CASE WHEN ModType='X' THEN 1 END)'X',
COUNT(CASE WHEN ModType='Y' THEN 1 END)'Y',
COUNT(CASE WHEN ModType='Z' THEN 1 END)'Z'
FROM ##Mods
<anonymous@.discussions.microsoft.com> wrote in message
news:14d5601c4156c$4b978910$a301280a@.phx
.gbl...
> Hi All
> I have a table as illistarted beloe that cotains three
> mod types (x, y and z). How to I write a query that
> will return the count of each mod type like a cross tab
> ie.
> x y z
> 4 2 3
>
> CREATE TABLE ##Mods
> (
> ModType CHAR(1),
> ModName VARCHAR(20)
> )
> INSERT INTO ##Mods VALUES ('X', 'CCC')
> INSERT INTO ##Mods VALUES ('X', 'CCC')
> INSERT INTO ##Mods VALUES ('X', 'CCC')
> INSERT INTO ##Mods VALUES ('X', 'AAA')
> INSERT INTO ##Mods VALUES ('Y', 'AAA')
> INSERT INTO ##Mods VALUES ('Y', 'AAA')
> INSERT INTO ##Mods VALUES ('Z', 'BBB')
> INSERT INTO ##Mods VALUES ('Z', 'BBB')
> INSERT INTO ##Mods VALUES ('Z', 'BBB')
> Thanks in Advance.

Cross table constraint -- possible?

Consider these four tables:
TableA:
Columns: ID (PK), Type (FK-TableA_Types), etc.
TableA_Types:
Columns: ID (PK), Name
Table B:
Columns: ID (PK), Type (FK-TableB_Types), etc.
TableB_Types:
Columns: ID (PK), Name

TableA has a FK column to the TableA_Types table (TableA.Type->TableA_Types.ID), and TableB has a FK column to the TableB_Types table (TableB.Type->TableB_Types.ID).

The problem is that TableA_Types and TableB_Types overlap, and should really be in the same table. The problem is, they're not, and the given the large amount of work that it would involve in consolidating the tables, it would preferable to simply add a constraint to TableA_Types and TableB_Types such that their IDs don't overlap.

In other words, I want to avoid this situation:
TableA_Types:
1 name1
2 name2

TableB_Types:
1 name3
2 name4

Can I add a constraint on TableA_Types such that it prevents new records from having the same IDs as any ID for records in the TableB_Types table, and vice versa?

If so, how? If not, any other suggestions?

Thanks!Are these identity fields? If you can get away with changing the identities for these tables, just re create the identitiy columns so that one table only uses odd numbers and another table only uses even numbers.|||

This is not possible with a constraint.

You could achieve this with a trigger if you really want this situation to be blocked.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

To directly answer you questions -No, not a table constraint; and yes, you have alternatives.

You can use the UNIQUEIDENTIFIER datatype for the primary key in each table -there will never be any overlap. But I don't think that that really makes much sense in this situation. The keys are too large and unwieldy.

Cam's suggestion is interesting, and just 'might' work for you. Set the IDENTITY seed = 1 in one table, and the seed = 2 in the other table, and the increment value = 2 in both tables. As long as the row counts in each table don't approach 1 billion, you 'should' be OK. Set a table constraint in each table that maintains the odd or even scheme.

And Wesley's suggestion to use a TRIGGER is the most 'bullet proof' method -all the while requiring the most server resources.

But the real issue is this: Why do you not take the time and effort and correct a underlaying defect that you know exists in the database?

Leaving it is like having dryrot in your house and just painting over it.

Once I know that dryrot has been painted over, I'm instantly on alert for other signifianct defects waiting like time bombs ...

|||You can "sort of" do it. A constraint can actually access other tables through a function, but it can get messy to do this. So like several other folks have said, a trigger is the thing to do|||

I agree with Arnie though, correcting the database design is actually the best solution.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Thanks for the posts, guys. I've implemented a check using triggers. Not the most elegant or best solution, I agree, but it will have to do for now.

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:)