Problem with LEFT JOIN... please help!

Please help,

below is my problem. Let's say I have 2 tables, a Products table and a
Colors table that go as follow:

Table Products

prodID Name
1 shirt
2 tshirt
3 pants
4 jeans

and

Table Colors

prodID Colors
1 Blue
1 Red
2 Blue
3 Black
3 White
4 Blue

I want to find out all the products that come in Blue, and if not I
want to have the color empty. The result I want from my Query / SQL
Statement is:

prodID Colors Name...

1 Blue
2 Blue
3
4 Blue

What should my SQL statement/Query be like?

I tried:

Select Product.ProdID, Colors.Colors
From
Products
Left Join Colors
on Product.ProdID = Colors.ProdID
where Colors.Colors = "blue"

and this is what I get:

prodID Colors

1 Blue
2 Blue
4 Blue

Notice that prodID 2 doesn't show up but I want to return all prodIDs
whether or not they have a color Blue.

Please help...

Thanks[posted and mailed, please reply in news]

Allan (proflicker@.hotmail.com) writes:
> Select Product.ProdID, Colors.Colors
> From
> Products
> Left Join Colors
> on Product.ProdID = Colors.ProdID
> where Colors.Colors = "blue"

When you say:

FROM a LEFT JOIN b on ...

You are, concpetually, constructing a table. Then you apply a WHERE
clause to filter out rows from that table.

Thus for

Products Left Join Colors on Product.ProdID = Colors.ProdID

You get a table with data in all columns for Products, but where
there is no matching row in Colors, you get NULL.

Then you apply a WHERE clause to this, but then you filter all those
NULL rows, because NULL is not equal to "blue".

The remedy is to move the condition to the ON clause:

Products Left Join Colors
on Product.ProdID = Colors.ProdID
and Colors.Color = "blue"

Now the condition on Colors becomes part of that conceptual table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 7 Jun 2004 15:26:39 -0700, Allan wrote:

>Please help,
>below is my problem. Let's say I have 2 tables, a Products table and a
>Colors table that go as follow:
>Table Products
>prodID Name
>1 shirt
>2 tshirt
>3 pants
>4 jeans
>and
>Table Colors
>prodID Colors
>1 Blue
>1 Red
>2 Blue
>3 Black
>3 White
>4 Blue
>
>I want to find out all the products that come in Blue, and if not I
>want to have the color empty. The result I want from my Query / SQL
>Statement is:
>prodID Colors Name...
>1 Blue
>2 Blue
>3
>4 Blue
>What should my SQL statement/Query be like?
>I tried:
>Select Product.ProdID, Colors.Colors
>From
>Products
>Left Join Colors
>on Product.ProdID = Colors.ProdID
>where Colors.Colors = "blue"
>and this is what I get:
>prodID Colors
>1 Blue
>2 Blue
>4 Blue
>
>Notice that prodID 2 doesn't show up but I want to return all prodIDs
>whether or not they have a color Blue.
>Please help...
>Thanks

Hi Allan,

Try:

Select Product.ProdID, Colors.Colors
From
Products
Left Join Colors
on Product.ProdID = Colors.ProdID
and Colors.Colors = 'blue'

(untested)

Note: Changed "where" to "and" and also changed double-quotes to
single-quotes (single quotes are the standard string delimiter for SQL, as
defined by the ANSI standard. Double quotes are, depending on the setting
of some option, still supported in SQL Server 2000 for backward
compatibility)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks for the quick reply...

I had already tried that and I get an error message saying:

JOIN expression not supported

Any other suggestions?

Gad

Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<94r9c0lovk9hshhb5monp75s24mg1abnd2@.4ax.com>...
> On 7 Jun 2004 15:26:39 -0700, Allan wrote:
> >Please help,
> >below is my problem. Let's say I have 2 tables, a Products table and a
> >Colors table that go as follow:
> >Table Products
> >prodID Name
> >1 shirt
> >2 tshirt
> >3 pants
> >4 jeans
> >and
> >Table Colors
> >prodID Colors
> >1 Blue
> >1 Red
> >2 Blue
> >3 Black
> >3 White
> >4 Blue
> >I want to find out all the products that come in Blue, and if not I
> >want to have the color empty. The result I want from my Query / SQL
> >Statement is:
> >prodID Colors Name...
> >1 Blue
> >2 Blue
> >3
> >4 Blue
> >What should my SQL statement/Query be like?
> >I tried:
> >Select Product.ProdID, Colors.Colors
> >From
> >Products
> >Left Join Colors
> >on Product.ProdID = Colors.ProdID
> >where Colors.Colors = "blue"
> >and this is what I get:
> >prodID Colors
> >1 Blue
> >2 Blue
> >4 Blue
> >Notice that prodID 2 doesn't show up but I want to return all prodIDs
> >whether or not they have a color Blue.
> >Please help...
> >Thanks
> Hi Allan,
> Try:
> Select Product.ProdID, Colors.Colors
> From
> Products
> Left Join Colors
> on Product.ProdID = Colors.ProdID
> and Colors.Colors = 'blue'
> (untested)
> Note: Changed "where" to "and" and also changed double-quotes to
> single-quotes (single quotes are the standard string delimiter for SQL, as
> defined by the ANSI standard. Double quotes are, depending on the setting
> of some option, still supported in SQL Server 2000 for backward
> compatibility)
> Best, Hugo|||"Allan" <proflicker@.hotmail.com> wrote in message
news:7b5b0602.0406071919.319fa873@.posting.google.c om...
> Thanks for the quick reply...
> I had already tried that and I get an error message saying:
> JOIN expression not supported
> Any other suggestions?

The name of your products table is "Products", right?
You have "Product" in the join condition of your query.

SELECT P.prodID, C.colors
FROM Products AS P
LEFT OUTER JOIN
Colors AS C
P.prodID = C.prodID AND
C.colors = 'blue'

--
JAG

> Gad
>
> Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:<94r9c0lovk9hshhb5monp75s24mg1abnd2@.4ax.com>...
> > On 7 Jun 2004 15:26:39 -0700, Allan wrote:
> > >Please help,
> > > >below is my problem. Let's say I have 2 tables, a Products table and a
> > >Colors table that go as follow:
> > > >Table Products
> > > >prodID Name
> > >1 shirt
> > >2 tshirt
> > >3 pants
> > >4 jeans
> > > >and
> > > >Table Colors
> > > >prodID Colors
> > >1 Blue
> > >1 Red
> > >2 Blue
> > >3 Black
> > >3 White
> > >4 Blue
> > > > >I want to find out all the products that come in Blue, and if not I
> > >want to have the color empty. The result I want from my Query / SQL
> > >Statement is:
> > > >prodID Colors Name...
> > > >1 Blue
> > >2 Blue
> > >3
> > >4 Blue
> > > >What should my SQL statement/Query be like?
> > > >I tried:
> > > >Select Product.ProdID, Colors.Colors
> > >From
> > >Products
> > >Left Join Colors
> > >on Product.ProdID = Colors.ProdID
> > >where Colors.Colors = "blue"
> > > >and this is what I get:
> > > >prodID Colors
> > > >1 Blue
> > >2 Blue
> > >4 Blue
> > > > >Notice that prodID 2 doesn't show up but I want to return all prodIDs
> > >whether or not they have a color Blue.
> > > >Please help...
> > > >Thanks
> > Hi Allan,
> > Try:
> > Select Product.ProdID, Colors.Colors
> > From
> > Products
> > Left Join Colors
> > on Product.ProdID = Colors.ProdID
> > and Colors.Colors = 'blue'
> > (untested)
> > Note: Changed "where" to "and" and also changed double-quotes to
> > single-quotes (single quotes are the standard string delimiter for SQL, as
> > defined by the ANSI standard. Double quotes are, depending on the setting
> > of some option, still supported in SQL Server 2000 for backward
> > compatibility)
> > Best, Hugo|||Allan (proflicker@.hotmail.com) writes:
> Thanks for the quick reply...
> I had already tried that and I get an error message saying:
> JOIN expression not supported

It is at this time I find it appropriate to ask which version of SQL
Server you are using. Or rather which DBMS you are using. That message
does not sound familliar at all. But it could also be that you are issueing
the query through some unknown tool which has its own quirks. Did you
try running in Query Analyzer?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>>Let's say I have 2 tables, a Products table and a Colors table that
go as follow: <<

1) Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

2) Next, can you explain why color is NOT AN ATTRIBUTE of a product??
In your reality and therefore, your data model, it floats around with
weight, height, or whatever physcial attributes that go with being a
color?

If you had done this right, wouldn't it look more like this?

CREATE Table Products
(sku INTEGER NOT NULL PRIMARY KEY,
description VARCHAR(25) NOT NULL,
color CHAR(5) NOT NULL
CHECK(color IN (..)),
..);

>> I want to find out all the products that come in Blue, and if not I
want to have the color empty. <<

Weird, if your inventory is of any size at all; How many non-blue
things do you think that J.C. Penney's has, as compared to blue
things? But you can try this:

SELECT I1.sku, 'Blue'
FROM Inventory AS I1
WHERE color = 'Blue'
UNION ALL
SELECT I1.sku, 'Not Blue'
FROM Inventory AS I1
WHERE color <> 'Blue';

If the Colors table were actually not an attribute in a properly
designed schema, then you'd use an outer join.

Problem with Group by and having using alias

Hi
My below query is not working
SELECT
'EstReqStd' As Type,
WO.WorkOrderTypeCd + '-' + WO.SequenceNo as OFSType
FROM
FROM WORK_ORDER WO (NOLOCK)
Group By
'EstReqStd' --Not Working
WO.WorkOrderTypeCd + '-' + WO.SequenceNo
Having
(WO.WorkOrderTypeCd + '-' + WO.SequenceNo) like 'DHG*' --Not Working
Can anyone help me with this.
Regards,
RajeevTry this.
SELECT Type, OFSType
FROM
(SELECT
'EstReqStd' As Type,
WO.WorkOrderTypeCd + '-' + WO.SequenceNo as OFSType
FROM WORK_ORDER WO (NOLOCK))WO
GROUP BY Type, OFSType
HAVING OFSType LIKE 'DHG*'
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Rajeev" <rajeev.rajput@.gmail.com> wrote in message
news:1146631177.404550.146230@.j73g2000cwa.googlegroups.com...
> Hi
> My below query is not working
> SELECT
> 'EstReqStd' As Type,
> WO.WorkOrderTypeCd + '-' + WO.SequenceNo as OFSType
> FROM
> FROM WORK_ORDER WO (NOLOCK)
> Group By
> 'EstReqStd' --Not Working
> WO.WorkOrderTypeCd + '-' + WO.SequenceNo
> Having
> (WO.WorkOrderTypeCd + '-' + WO.SequenceNo) like 'DHG*' --Not Working
> Can anyone help me with this.
> Regards,
> Rajeev
>|||Hello, Rajeev
Try something like this:
SELECT
'EstReqStd' As Type,
WO.WorkOrderTypeCd + '-' + WO.SequenceNo as OFSType
FROM WORK_ORDER WO
GROUP BY WO.WorkOrderTypeCd, WO.SequenceNo
HAVING (WO.WorkOrderTypeCd + '-' + WO.SequenceNo) LIKE 'DHG%'
However, I think the following query works better and provides the same
results:
SELECT
'EstReqStd' As Type,
WO.WorkOrderTypeCd + '-' + WO.SequenceNo as OFSType
FROM WORK_ORDER WO
WHERE WO.WorkOrderTypeCd LIKE 'DHG%'
GROUP BY WO.WorkOrderTypeCd, WO.SequenceNo
Razvan|||You dont need group by or having, because you dont have any agregate
functions (i.e. max, min, count, sum).
Remove both and repace with a where clause.
Also, use % not * as a wild card.
SELECT
'EstReqStd' As Type,
WO.WorkOrderTypeCd + '-' + WO.SequenceNo as OFSType
FROM
FROM WORK_ORDER WO (NOLOCK)
where
(WO.WorkOrderTypeCd + '-' + WO.SequenceNo) like 'DHG%'
"Rajeev" <rajeev.rajput@.gmail.com> wrote in message
news:1146631177.404550.146230@.j73g2000cwa.googlegroups.com...
> Hi
> My below query is not working
> SELECT
> 'EstReqStd' As Type,
> WO.WorkOrderTypeCd + '-' + WO.SequenceNo as OFSType
> FROM
> FROM WORK_ORDER WO (NOLOCK)
> Group By
> 'EstReqStd' --Not Working
> WO.WorkOrderTypeCd + '-' + WO.SequenceNo
> Having
> (WO.WorkOrderTypeCd + '-' + WO.SequenceNo) like 'DHG*' --Not Working
> Can anyone help me with this.
> Regards,
> Rajeev
>|||>>Can anyone help me with this. <<
Sopmeone else will give you a quick fix, but your REAL problem is that
you do not know how a SELECT works in SQL. Real products will
optimize things, but the code has to produce the same results.
a) Start in the FROM clause and build a working table from all of the
joins, unions, intersections, and whatever other table constructors are
there. The <table expression> AS <correlation name> option allows you
give a name to this working table which you then have to use for the
rest of the containing query.
b) Go to the WHERE clause and remove rows that do not pass criteria;
that is, that do not test to TRUE (i.e. reject UNKNOWN and FALSE). The
WHERE clause is applied to the working set in the FROM clause.
c) Go to the optional GROUP BY clause, make groups and reduce each
group to a single row, replacing the original working table with the
new grouped table. The rows of a grouped table must be group
characteristics: (1) a grouping column (2) a statistic about the group
(i.e. aggregate functions) (3) a function or (4) an expression made up
those three items. The original table no longer exists.
d) Go to the optional HAVING clause and apply it against the grouped
working table; if there was no GROUP BY clause, treat the entire table
as one group.
e) Go to the SELECT clause and construct the expressions in the list.
This means that the scalar subqueries, function calls and expressions
in the SELECT are done after all the other clauses are done. The AS
operator can also give names to expressions in the SELECT list. These
new names come into existence all at once, but after the WHERE clause,
GROUP BY clause and HAVING clause have been executed; you cannot use
them in the SELECT list or the WHERE clause for that reason.
If there is a SELECT DISTINCT, then redundant duplicate rows are
removed. For purposes of defining a duplicate row, NULLs are treated
as matching (just like in the GROUP BY).
f) Nested query expressions follow the usual scoping rules you would
expect from a block structured language like C, Pascal, Algol, etc.
Namely, the innermost queries can reference columns and tables in the
queries in which they are contained.
g) The ORDER BY clause is part of a cursor, not a query. The result
set is passed to the cursor, which can only see the names in the SELECT
clause list, and the sorting is done there. The ORDER BY clause cannot
have expression in it, or references to other columns because the
result set has been converted into a sequential file structure and that
is what is being sorted.
As you can see, things happen "all at once" in SQL, not "from left to
right" as they would in a sequential file/procedural language model. In
those languages, these two statements produce different results:
READ (a, b, c) FROM File_X;
READ (c, a, b) FROM File_X;
while these two statements return the same data:
SELECT a, b, c FROM Table_X;
SELECT c, a, b FROM Table_X;
Think about what a mess this statement is in the SQL model.
SELECT f(c2) AS c1, f(c1) AS c2 FROM Foobar;
That is why such nonsense is illegal syntax.
Your next problem is that you do not know how to do a data model or how
to name data elements. Something can be a "<something>_type" or a
"<something>_code", but not both. Likewise, a sequence is a
"<something>_sequence"; you have vague geneal things floating in your
schema and probably no data dictionary at all.|||Hi,
I tried the below approach. Seems to be good. The problem is my query
contains multiple table which belongs to different databases... so how
do i use them.
Roji. P. Thomas wrote:
> Try this.
>
> SELECT Type, OFSType
> FROM
> (SELECT
> 'EstReqStd' As Type,
> WO.WorkOrderTypeCd + '-' + WO.SequenceNo as OFSType
> FROM WORK_ORDER WO (NOLOCK))WO
> GROUP BY Type, OFSType
> HAVING OFSType LIKE 'DHG*'
>
> --
> Regards
> Roji. P. Thomas
> http://toponewithties.blogspot.com
> "Rajeev" <rajeev.rajput@.gmail.com> wrote in message
> news:1146631177.404550.146230@.j73g2000cwa.googlegroups.com...

Problem With Excel Import in Execute Phase

Hi All

I have a SSIS package running and tested fine on my desktop. However when I deploy the package to my server I get the error given below.

SSIS package "CR Sec Watch List 2.dtsx" starting.

Information: 0x4001100A at Transform Master Files: Starting distributed transaction for this container.

Information: 0x4004300A at Transform Master Files, DTS.Pipeline: Validation phase is beginning.

Information: 0x40043006 at Transform Master Files, DTS.Pipeline: Prepare for Execute phase is beginning.

Information: 0x40043007 at Transform Master Files, DTS.Pipeline: Pre-Execute phase is beginning.

Information: 0x4004300C at Transform Master Files, DTS.Pipeline: Execute phase is beginning.

Error: 0xC0202009 at Transform Master Files, Excel Source [1]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.

Error: 0xC0208265 at Transform Master Files, Excel Source [1]: Failed to retrieve long data for column "NKoreaPoi".

Error: 0xC020901C at Transform Master Files, Excel Source [1]: There was an error with output "Excel Source Output" (9) on component "Excel Source" (1). The column status returned was: "DBSTATUS_UNAVAILABLE".

Error: 0xC0209029 at Transform Master Files, Excel Source [1]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output "Excel Source Output" (9)" failed because error code 0xC0209071 occurred, and the error row disposition on "component "Excel Source" (1)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

Error: 0xC0047038 at Transform Master Files, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Excel Source" (1) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

Error: 0xC0047021 at Transform Master Files, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.

Error: 0xC0047039 at Transform Master Files, DTS.Pipeline: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.

Error: 0xC0047021 at Transform Master Files, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.

Information: 0x40043008 at Transform Master Files, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x40043009 at Transform Master Files, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at Transform Master Files, DTS.Pipeline: "component "SQL Server Destination" (20)" wrote 0 rows.

Task failed: Transform Master Files

Information: 0x4001100C at Transform Master Files: Aborting the current distributed transaction.

Warning: 0x80019002 at Process Master Files: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (8) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "CR Sec Watch List 2.dtsx" finished: Success

For some reason it tells me that it not able to retrive the long data for a column. The column mentioned in the error contains special characters.

Once again, there seems to be no problem with the package as the package runs fine on my desktop.

Any help or clues as to why this is happening on my server is greatly appreciated.

Regards

Ryan

Jet Engine for Excel guesses data type of a column. Please see threads on TypeGuessRows and IMEX to understand how Jet works, and its limitations, and how to tune it to work for you.

Setting TypeGuessRows to 0 would force Jet to scan all rows in the Excel to guess the type. You may also need to set the Defalut Type to Text.

Using Imex=1 in your Connection string is essential to get mixed types to default to Text.

Other Potential Problems:

Maybe the excel file on your PC contains Long Data, and the one you used on the server does not contain even a single row with Long Data in your NorthKorea column.

Maybe the Jet Engine Settings have different values on your PC and your server.

If the same file is giving errors, even when Jet Engine settings are same, then I would speculate if it is a Locale problem?

HTH

Kar

|||

Thanks Kar.

You recommendation worked. Setting typeguessrows to 0 did the trick.

Regards

Ryan

Problem with Drill Through Linking for Implementing Tree view Structure

Hai Iam new to SSRS 2005, please help me regarding below Drill Through Report Problem. Assum my problem with below example.Iam tried Hard iam not able to find the solution, Any body please help me.my real time problem is same as below functionality

If Suppose iam dispalying two columns like Country,Department,iam taking these two columns in a table, assume country column having America,south africa, individual america column having Florida state, south africa column contains capetown state, in the preview of the report iam applying drilldown to America column like + America, i need when clicking the America column i want to display Florida state under the column of Country like tree view structure

i need output like this and the same time i want to display other columns those also contain tree view structure assume other one is department column, with contains computers and sales, individualy computers contains HP, Sales contains Bikes

Country Department

+ America + Computers

--Florida -- HP

+ SothAfrica + Sales

--Capetown __Bikes

How to implement above output using drill through functionality, i tried with subreports and used all grouping formats, is possible for display output like above explain the procedure .

Thanks In advance

Jacks

Hi,

If Suppose iam dispalying two columns like Country,Department,iam taking these two columns in a table, assume country column having America,south africa, individual america column having Florida state, south africa column contains capetown state

From your description, you want to display other columns those also contain tree view structure assume other one is department column, right?

Actually you can build a tabular table by using Report Builder Wizard, and make your table structure to be a parent-child format, in that way, you can use inner join in your select statement, and put corresponding fields to Groups, Fields, Details. Thanks.

Thanks.

|||

Hai, iam new to reporting services please help me,

where can i see reportbuilder wizard in reporting services, can you please explain step by step procedure for creating parent- child Tabular format using report bulider wizard.

give repaly as early as possible

Thank you so much

Jack vs

|||

Hi,

Start your Visual Studio, and create a new project, choose the Business Intelligence Projects type, and click on "Reporting Service Project Wizard template".

In this way, you can modify the database connections, select queries, and table structures in an ease way.

Thanks.

problem with Display databound images in page header

Hi all,
I have a problem in displaying databound images in page header.
I have done as is stated in the below article.
-X-
http://msdn2.microsoft.com/en-us/library/ms159677(en-us,VS.90).aspx
Adding a Databound Image to a Header or Footer
You can use image data stored in a database in a header or footer. However, you cannot reference database fields from the Image control directly. Instead, you must add a text box in the body of the report and then set the text box to the data field that contains the image (note that the value must be base64 encoded). You can hide the text box in the body of the report to avoid showing the base64 encoded image. Then, you can reference the value of the hidden text box from the Image control in the page header or footer.
For example, suppose you have a report that consists of product information pages. In the header of each page, you want to display a photograph of the product. To print a stored image in the report header, define a hidden text box named TXT_Photo in the body of the report that retrieves the image from the database and use an expression to give it a value:
=System.Convert.ToBase64String(Fields!Photo.Value)
In the header, add an Image control which uses the TXT_Photo text box, decoded to show the image:
=System.Convert.FromBase64String(ReportItems!TXT_Photo.Value)

-X-
but I am not getting the image.
The error message displayed is like this
[rsInvalidExpressionDataType] The Value expression used in image ‘image2’ returned a data type that is not valid.
Can any one help me in this regard.
Thanks in advance.
Ramesh

What's the image source type of the image reportitem? Is it set to "Database"?

-- Robert

|||

If the image was saved to the database via MS Access, the solution is here: http://forums.devarticles.com/microsoft-sql-server-5/displaying-image-fields-in-reporting-services-11844.html . In brief, as it states, instead of utilizing the hidden text box and Base64 strings, simply use this piece of code to set the Value property of your RS image control:

=System.Text.Encoding.Default.GetBytes(Mid(System.Text.Encoding.Default.GetString(Fields!Picture.Value),XXX))

For English use XXX = 79
For Spanish use XXX = 89

(The image control should still have its Source property set to Database.)

|||

u can do that with the help of parameters

1) add a calculated field in dataset named imagebase64 in that add the following expression

Convert.ToBase64String(Fields!Photo.value)

2)Add report Parameter named image

set the datatype as string.

problem with Display databound images in page header

Hi all,
I have a problem in displaying databound images in page header.
I have done as is stated in the below article.
-X-
http://msdn2.microsoft.com/en-us/library/ms159677(en-us,VS.90).aspx
Adding a Databound Image to a Header or Footer
You can use image data stored in a database in a header or footer. However, you cannot reference database fields from the Image control directly. Instead, you must add a text box in the body of the report and then set the text box to the data field that contains the image (note that the value must be base64 encoded). You can hide the text box in the body of the report to avoid showing the base64 encoded image. Then, you can reference the value of the hidden text box from the Image control in the page header or footer.
For example, suppose you have a report that consists of product information pages. In the header of each page, you want to display a photograph of the product. To print a stored image in the report header, define a hidden text box named TXT_Photo in the body of the report that retrieves the image from the database and use an expression to give it a value:
=System.Convert.ToBase64String(Fields!Photo.Value)
In the header, add an Image control which uses the TXT_Photo text box, decoded to show the image:
=System.Convert.FromBase64String(ReportItems!TXT_Photo.Value)

-X-
but I am not getting the image.
The error message displayed is like this
[rsInvalidExpressionDataType] The Value expression used in image ‘image2’ returned a data type that is not valid.
Can any one help me in this regard.
Thanks in advance.
Ramesh

What's the image source type of the image reportitem? Is it set to "Database"?

-- Robert

|||

If the image was saved to the database via MS Access, the solution is here: http://forums.devarticles.com/microsoft-sql-server-5/displaying-image-fields-in-reporting-services-11844.html . In brief, as it states, instead of utilizing the hidden text box and Base64 strings, simply use this piece of code to set the Value property of your RS image control:

=System.Text.Encoding.Default.GetBytes(Mid(System.Text.Encoding.Default.GetString(Fields!Picture.Value),XXX))

For English use XXX = 79
For Spanish use XXX = 89

(The image control should still have its Source property set to Database.)

|||

u can do that with the help of parameters

1) add a calculated field in dataset named imagebase64 in that add the following expression

Convert.ToBase64String(Fields!Photo.value)

2)Add report Parameter named image

set the datatype as string.

problem with Display databound images in page header

Hi all,
I have a problem in displaying databound images in page header.
I have done as is stated in the below article.
-X-
http://msdn2.microsoft.com/en-us/library/ms159677(en-us,VS.90).aspx
Adding a Databound Image to a Header or Footer
You can use image data stored in a database in a header or footer. However, you cannot reference database fields from the Image control directly. Instead, you must add a text box in the body of the report and then set the text box to the data field that contains the image (note that the value must be base64 encoded). You can hide the text box in the body of the report to avoid showing the base64 encoded image. Then, you can reference the value of the hidden text box from the Image control in the page header or footer.
For example, suppose you have a report that consists of product information pages. In the header of each page, you want to display a photograph of the product. To print a stored image in the report header, define a hidden text box named TXT_Photo in the body of the report that retrieves the image from the database and use an expression to give it a value:
=System.Convert.ToBase64String(Fields!Photo.Value)
In the header, add an Image control which uses the TXT_Photo text box, decoded to show the image:
=System.Convert.FromBase64String(ReportItems!TXT_Photo.Value)

-X-
but I am not getting the image.
The error message displayed is like this
[rsInvalidExpressionDataType] The Value expression used in image ‘image2’ returned a data type that is not valid.
Can any one help me in this regard.
Thanks in advance.
Ramesh

What's the image source type of the image reportitem? Is it set to "Database"?

-- Robert

|||

If the image was saved to the database via MS Access, the solution is here: http://forums.devarticles.com/microsoft-sql-server-5/displaying-image-fields-in-reporting-services-11844.html . In brief, as it states, instead of utilizing the hidden text box and Base64 strings, simply use this piece of code to set the Value property of your RS image control:

=System.Text.Encoding.Default.GetBytes(Mid(System.Text.Encoding.Default.GetString(Fields!Picture.Value),XXX))

For English use XXX = 79
For Spanish use XXX = 89

(The image control should still have its Source property set to Database.)

|||

u can do that with the help of parameters

1) add a calculated field in dataset named imagebase64 in that add the following expression

Convert.ToBase64String(Fields!Photo.value)

2)Add report Parameter named image

set the datatype as string.

Problem with Dependencies and Default Values

Dear Anyone,

Im having trouble with RS2005 with regards to dependencies of report parameters and the default value. Below is my scenario:

1st parameter:

List of Letters from A to Z

Multiple Select

2nd Parameter:

List of Names starting with the last name

Uses 1 data set for list of values and default values

Multiple Select

Steps:

Select 2 letters from the 1st parameter. This action would refresh the list of values of the 2nd parameter.

Inspect the 2nd parameter. You will notice that all values are selected.

Select another letter from the first parameter. This action would refresh the list of values of the 2nd parameter.

Inspect the 2nd parameter. You will notice that not all is selected despite the fact that the data set being used to populate the report parameter is the same as with the default values

Is the behavior of the report parameters really like this? If so, how can I make it in a way that whenever I select a new letter from the first parameter and when the 2nd parameter gets refreshed because of the depencies, all values will still be selected despite the number of times I add more values to the first parameter?

Thanks,

Joseph

Is there a resolution to this issue? How do we overcome this behavior? I am running into the same issue in my reports.

apex|||Bumping up this thread. Any resolution to this problem will be very helpful.

Problem with Dependencies and Default Values

Dear Anyone,

Im having trouble with RS2005 with regards to dependencies of report parameters and the default value. Below is my scenario:

1st parameter:

List of Letters from A to Z

Multiple Select

2nd Parameter:

List of Names starting with the last name

Uses 1 data set for list of values and default values

Multiple Select

Steps:

Select 2 letters from the 1st parameter. This action would refresh the list of values of the 2nd parameter.

Inspect the 2nd parameter. You will notice that all values are selected.

Select another letter from the first parameter. This action would refresh the list of values of the 2nd parameter.

Inspect the 2nd parameter. You will notice that not all is selected despite the fact that the data set being used to populate the report parameter is the same as with the default values

Is the behavior of the report parameters really like this? If so, how can I make it in a way that whenever I select a new letter from the first parameter and when the 2nd parameter gets refreshed because of the depencies, all values will still be selected despite the number of times I add more values to the first parameter?

Thanks,

Joseph

Is there a resolution to this issue? How do we overcome this behavior? I am running into the same issue in my reports.

apex|||Bumping up this thread. Any resolution to this problem will be very helpful.

Problem with DATEDIFF

I currently have a problem with DATEDIFF(). When I run the below code, it works the way I expect it should. However, running this logic in a trigger produces a different result. I can consistenly reproduce this and have not figured out the cause.

DECLARE

@.Hours INT,

@.LastPlayDate DATETIME,

@.CardOutTime DATETIME

set @.LastPlayDate ='1900-01-01 00:00:00.000'

set @.CardOutTime ='2005-04-22 18:33:51.000'

SET @.hOURS =DATEDIFF(hh, @.LastPlayDate, @.CardOutTime)

ifDATEDIFF(hh, @.LastPlayDate, @.CardOutTime)> 12

BEGIN

print'greater'

SELECT @.HOURS

END

else

BEGIN

print'lessor'

SELECT @.HOURS

END

Trigger Code

IFDATEDIFF(hh, @.LastPlayDate, @.CardOutTime)> 12--@.TripLength

-- @.Hours > @.TripLength

SET @.NewTrip = 1;

ELSE

SET @.NewTrip = 0;

I know that the last play date is less than the card out time and that the hours between these is greater than 12 in this csae and this shows correctly in the results pane. When this logic is implemented in a trigger, instead of the results being "greater", I get "lesser". When I set the results to another variable so I can see what the value is, it always returns a negative number. Any insight into this is appreaciated.

Hi MAGrimsley,

I can not reproduce the problem. I am using SS2005 DE SP2.

createtable dbo.t1 (

c1 int

)

go

createtrigger tr_t1_ins on dbo.t1

forinsert

as

DECLARE @.Hours INT, @.LastPlayDate DATETIME, @.CardOutTime DATETIME

declare @.NewTrip int

set @.LastPlayDate ='1900-01-01 00:00:00.000'

set @.CardOutTime ='2005-04-22 18:33:51.000'

IFDATEDIFF(hh, @.LastPlayDate, @.CardOutTime)> 12

SET @.NewTrip = 1;

ELSE

SET @.NewTrip = 0;

select @.NewTrip

go

insertinto dbo.t1 defaultvalues

go

droptable dbo.t1

go

Result:

1

AMB

|||

AMB

That is the mystifying part. I copy the code into another window and it runs the way I expect it to. The fact it is in a trigger should not make a difference, but the result I get is. I have even rewritten the trigger, but the result is the same.

MAGrimsley

|||Version--SQL Server 2005 Enterprise (IA64) 9.00.3054.00