Please help me solve this problem

Hello All,
I am new to crystalreport am using crystalreport 8.5. I have to provide the link for
a column whose value is > 1000. i don't want to suppress the values other than1000.
My Problem is the link should be enabled only when the value is > 1000. Please share your experience to solve this problem.
Thanks in advance
Vasuformat the field of the column using a formula..

if(value>100, "link text" & value, value)

?|||Thanks a lot cjard. it works well. the link is enabled for value > 1000
thanks and regards
Vasu

Please Help me 'DrillthroughSourceQuery' parameter is missing a value in report builder

Can u help to to solve my problem . Many time this question arised in the forum no one answer to me to find out the solution for my problem. So please help

I am using report builder thru LocalHost\Report . I want a drill through report by using Report Builder and cube . I created the report but unfortunatly I cannot create a drill through report using parameters .How can I pass parameter in report1 to jump into report2 .

When I running the report after giving drill through properties in report the following error will occure.

'The 'DrillthroughSourceQuery' parameter is missing a value

How can I create a report using parameter for drill down in report builder

I am expecting one answer from u expertise

-Create a report with your needed entities and save it in Report builder as RDL file.

-Import it in a Reporting project

-Design your report as needed

-Deploy it on the Report Server

-Use SSMS to connect to the Reporting Server and navigate to the model

-Connect the Report with the single / multi instance of your entity within the model.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens,

Could you please elaborate on the steps.

In my scenario the report rdl file is on some other server and the folder is not shared, how do i impport the rdl file in reporting project. Could you please elaborate on "Use SSMS to connect to the Reporting Server and navigate to the model"

Regards

Neeraj

Please Help me ''DrillthroughSourceQuery'' parameter is missing a value in report builder

Can u help to to solve my problem . Many time this question arised in the forum no one answer to me to find out the solution for my problem. So please help

I am using report builder thru LocalHost\Report . I want a drill through report by using Report Builder and cube . I created the report but unfortunatly I cannot create a drill through report using parameters .How can I pass parameter in report1 to jump into report2 .

When I running the report after giving drill through properties in report the following error will occure.

'The 'DrillthroughSourceQuery' parameter is missing a value

How can I create a report using parameter for drill down in report builder

I am expecting one answer from u expertise

-Create a report with your needed entities and save it in Report builder as RDL file.

-Import it in a Reporting project

-Design your report as needed

-Deploy it on the Report Server

-Use SSMS to connect to the Reporting Server and navigate to the model

-Connect the Report with the single / multi instance of your entity within the model.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens,

Could you please elaborate on the steps.

In my scenario the report rdl file is on some other server and the folder is not shared, how do i impport the rdl file in reporting project. Could you please elaborate on "Use SSMS to connect to the Reporting Server and navigate to the model"

Regards

Neeraj

sql

Please help deciphering error message

I just changed my dataset syntaxes from the typical
SELECT col FROM table WHERE col3 = @.value
to
="SELECT col FROM table WHERE col3 = '" & Parameters!Code.Value & "'
For some reason I'm getting the following pop-up:
--
Processing Errors
--
An error has occurred during report processing.
Cannot set the command text for data set 'ds_Legal_Entity'.
Error during processing of the CommandText expression of dataset
â'ds_Legal_Entityâ'.
--
OK
--
I am not familiar with the CommandText syntax to understand where the error
might be. My query is below, could someone tell me what the problem might be?
Thanks!
Mike
="SELECT DISTINCT dbo.t_d_legal.legal_desc
FROM dbo.t_d_legal
INNER JOIN dbo.t_pms ON dbo.t_d_legal.legal_key = dbo.t_pms.legal_key
WHERE (dbo.t_pms.mth_key = " & Parameters!mth_key.Value & ") " &
IIF(Parameters!BusKey.Value = 0,"","
AND
(dbo.t_pms.bus_key = ") & Parameters!BusKey.Value & ")" &
" ORDER BY dbo.t_d_legal.legal_desc"think you had extra single quote:
="SELECT col FROM table WHERE col3 = " & Parameters!Code.Value & " rest of
code"
otherwise if you need quotes due to your parm value being character you'll
need to double up on the quotes. read BOL.
"Bassist695" wrote:
> I just changed my dataset syntaxes from the typical
> SELECT col FROM table WHERE col3 = @.value
> to
> ="SELECT col FROM table WHERE col3 = '" & Parameters!Code.Value & "'
> For some reason I'm getting the following pop-up:
> --
> Processing Errors
> --
> An error has occurred during report processing.
> Cannot set the command text for data set 'ds_Legal_Entity'.
> Error during processing of the CommandText expression of dataset
> â'ds_Legal_Entityâ'.
> --
> OK
> --
> I am not familiar with the CommandText syntax to understand where the error
> might be. My query is below, could someone tell me what the problem might be?
> Thanks!
> Mike
> ="SELECT DISTINCT dbo.t_d_legal.legal_desc
> FROM dbo.t_d_legal
> INNER JOIN dbo.t_pms ON dbo.t_d_legal.legal_key = dbo.t_pms.legal_key
> WHERE (dbo.t_pms.mth_key = " & Parameters!mth_key.Value & ") " &
> IIF(Parameters!BusKey.Value = 0,"","
> AND
> (dbo.t_pms.bus_key = ") & Parameters!BusKey.Value & ")" &
> " ORDER BY dbo.t_d_legal.legal_desc"

Please Help !

Hi Experts,

I'm very new in ASP.NET 2.0. Got a simpe question. After I defined the 'SqlDataSource', how can I get the value (content) of a column to a control (whatever TextBox) ? I can Update a data row by using a TextBox conent to a column but don't know how to do it reversely.

Thanks in advance !

Stephen

Hey,

This is the syntax: http://book.itzero.com/read/microsoft/0602/Addison.Wesley.Data.Binding.with.Windows.Forms.2.0.Programming.Smart.Client.Data.Applications.with.dot.NET.Jan.2006_html/032126892X/app01lev1sec5.html

But you may need to put it in a form view or something like that.

|||

Let me check it first. Thanks !

stephen

Please correct syntax

hi there

RS2005 - Layout tab

I have a field that says

txtTrackerTrue=Sum(Iif ((Fields!Tracker.Value = Fields!RecvdBy.Value), 1,0 ))

I want another field that says

txtAllTrue=Sum(Iif ((Fields!Tracker.Value, ClosedBy.Value = Fields!RecvdBy, 1,0 ))

obviously this is going to error

how would I do this please

thanks

jewelfire,

I am not 100% what you are trying to get help with here.

I assume you have a field (txtTrackerTrue) that has the expression (starting with an equal sign)

I think you need a double equals sign like this...

=Sum(Iif (Fields!Tracker.Value == Fields!RecvdBy.Value , 1,0 ))

In your second example you were missing closing paren. and I am not sure what you are trying to do,

Can you explain in words what you are wanting to do in your second example and I will try to help you out!

Thanks

|||

Hi,

you are missing a ")" before the 1,0.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

hey there thanks for jumping in, Reading back I didn't explain myself very well at all - sorry about that

RS2005 - layout tab

Name of txt box = txtTrackerTrue

Value of this txt box = Sum(Iif ((Fields!Tracker.Value = Fields!RecvdBy.Value), 1,0 ))

This is correct - this is just for guidelines.

I need another field

Lets call the txt box = txtAllTrue

Value of this txt box to be

Sum If Tracker/RecvdBy/ClosedBy all the same then true.

I thought if I could reference the 1st txt box as part of the sum is already done and then just add the closedby into the equasion?

But not sure how to reference a txt box.

thanks

Jewel

|||

Hi,

You will have to use the prefix ReportItems!SomeItem to use the value of that field, I am not sure if that works for expressions, but It hink the processor will take care of the informationen and the precendence that the expression has to be evaluated.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

thanks Jens

I tried that but it came up with

ReportItem is a type and cannot be used as an expression.

any other suggestions?

cheers

|||You need to use the Value property on the ReportItem. For example, ReportItems!txtTrackerTrue.Value|||

thanks Ian

I thought I could as well but as mentioned when Jens suggested same I am getting an error (Report Item is a type and cannot be used as an expression)

this is what I need to do

=Sum(Iif ((ReportItem!txtTrackerTrue.Value = Fields!ClosedBy.Value), 1,0 ))

with ReportItem!txtTrackerTrue - already a combination of two fields being equal.

cheers

Jewel

|||Jens suggested that you do the same thing, but Jens did not mention that you need to use the .Value property. Make sure that the .Value property is used on the ReportItem, otherwise you will get the this error.

Ian|||

thanks but still get the error with using the .Value

cheers

J

|||

I noticed that in the expression "ReportItem" is used, try using "ReportItems" instead.

Ian

|||

thanks Ian

yes I tried that as well - I noticed that and thought I would give it a go - error is:

Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope.

so am just trying to see if there is anything else I can do.

cheers for your help though

jewel

|||

hey there

thanks all for participating but I still have not solved this problem. Is this something I cannot do?

so I have a text box called txttrackertrue

the expression in this box is

=Sum(Iif ((Fields!Tracker.Value = Fields!RecvdBy.Value), 1,0 ))

I have another text box called txtClosedtrue

the expression needed for this box is

=Sum(Iif ((ReportItems!txtTrackerTrue.Value = Fields!ClosedBy.Value), 1,0 ))

error is

[rsAggregateReportItemInBody] The Value expression for the textbox 'txtClosedTrue' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers.

[rsReportItemReference] The Value expression for the textbox ‘txtClosedTrue’ refers to the report item ‘txtTrackerTrue’. Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope.

any suggestions please.

thanks

jewel

|||
Where is the textbox 'txtClosedtrue' in relation to the textbox 'txtTrackerTrue'? The textbox 'txtClosedtrue' needs to be in the same scope or a child scope of the scope that contains the textbox 'txtTrackerTrue'. In other words, textboxes can only refer to other text boxes above them in the grouping hierarchy--Only one-to-one or many-to-one relationships can exist.

Also, you cannot refer to textboxes in an Aggregate function in the body of the report. This is only allowed in the page header and footer.

Ian|||

thanks Ian for your input

both text boxes are on a details line of a table - there is no group in this table

txtTrackerTrue comes before txtClosedTrue

I am presuming by Aggregate you mean the (sum) part.

so if this is so is there another way I should do this then?

cheers

Jewel

sql

please check this not null SQL String

the SQL string below worked, and then started bringing up every record.
it should only select records with a value in at least one of the columns, but it apears to be suggesting that all records have some data in one of the columns. if I check the database or the output on the web page there apears to be no data. ?? confused.

"SELECT id, make, model FROM vehicles WHERE workToBeDone1 IS NOT NULL OR workToBeDone2 IS NOT NULL OR workToBeDone3 IS NOT NULL OR workToBeDone4 IS NOT NULL OR workToBeDone5 IS NOT NULL"

Any ideas how I could implement this more robustly?
cheers
MSorry, doesn't work that way.

You need a condition for each column|||Cheat. Execute:
"SELECT id, make, model
, CAST(workToBeDone1 AS VARBINARY(10)) AS w1
, CAST(workToBeDone2 AS VARBINARY(10)) AS w2
, CAST(workToBeDone3 AS VARBINARY(10)) AS w3
, CAST(workToBeDone4 AS VARBINARY(10)) AS w4
, CAST(workToBeDone5 AS VARBINARY(10)) AS w5
FROM vehicles
WHERE workToBeDone1 IS NOT NULL
OR workToBeDone2 IS NOT NULL
OR workToBeDone3 IS NOT NULL
OR workToBeDone4 IS NOT NULL
OR workToBeDone5 IS NOT NULL"If the Cast() columns do not ALL show NULL as their value, then you have data in the offending column(s). Empty strings, and sometimes even the constant "NULL" have been known to sneak into tables when you do not expect them!

-PatP|||thanks guys.
I'm sure my version was working fine until the database seemed to put something invisible into the columns.
I tried your code Pat but it returns "ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal."

What does the 'as w1' part do?

my code looks like this:
"SELECT id, make, model, CAST(workToBeDone1 AS VARBINARY(10)) AS w1, CAST(workToBeDone2 AS VARBINARY(10)) AS w2, CAST(workToBeDone3 AS VARBINARY(10)) AS w3, CAST(workToBeDone4 AS VARBINARY(10)) AS w4, CAST(workToBeDone5 AS VARBINARY(10)) AS w5 FROM vehicles WHERE workToBeDone1 IS NOT NULL OR workToBeDone2 IS NOT NULL OR workToBeDone3 IS NOT NULL OR workToBeDone4 IS NOT NULL OR workToBeDone5 IS NOT NULL"|||Drop the quotes from around the SQL statement for starters ;)

For the 'As w1' try running this

SELECT id As 'Example'
FROM vehicles|||thanks georgev
sorry, I missed a crucial bit re the quotes: SQLstring="Select..."
I'll have a play with your example and see if I get it.|||nope, sorry, couldn't figure out what I am supposed to do with your example George.|||Run the thing in QA and see if you notice something.
Basically it's giving the column an alias http://doc.ddart.net/mssql/sql70/sa-ses_3.htm - scroll down to columns_alias :p|||can't use QA on this, I have to run scripts on pages on the server.
Not sure why I need aliases.
My database columns seem to contain invisible data, is there a way to discover if the columns have any meaningful data in them? NULL seems to be a bit flakey

I need to find cars that need work done - i.e. someone has inputted something like: 'replace tyres' in one of the workToBeDone fields for a Volvo. but my search is returning every car in the database because it is seeing something in the columns. (I think!).

I tried casting as varchar(255) - made no difference|||The "as W1" simply assigns an alias to the column as GeorgeV observed. It appears that your ADO implementation doesn't like the aliases.

If Query Anylyzer (or its equivalent) is available, then I'd use it instead of writing/changing code to support your ADO implementation. Operative word being "should", you should be able to simply drop the column names and move on without them.

-PatP|||And by drop the column names we don't mean physically dropping the columns... Just remove the "As ..." from your SQL statement.

The reason the aliases were applied in the first place because as soon as you perform any function on a column it loses the reference to the column name (because it's not the same as the column data any more!). The Aliases allow us to access the columns by referenec in ADO (or so I believe).|||I dropped the aliases, but it made no difference, I'm still getting:
'Item cannot be found in the collection corresponding to the requested name or ordinal',|||Ok, let's try to solve the problem from a different vector and execute:"SELECT id, make, model
, CASE WHEN workToBeDone1 IS NULL THEN 0 WHEN 0 = Len(workToBeDone1) THEN 1 ELSE 2 END
, CASE WHEN workToBeDone2 IS NULL THEN 0 WHEN 0 = Len(workToBeDone2) THEN 1 ELSE 2 END
, CASE WHEN workToBeDone3 IS NULL THEN 0 WHEN 0 = Len(workToBeDone3) THEN 1 ELSE 2 END
, CASE WHEN workToBeDone4 IS NULL THEN 0 WHEN 0 = Len(workToBeDone4) THEN 1 ELSE 2 END
, CASE WHEN workToBeDone5 IS NULL THEN 0 WHEN 0 = Len(workToBeDone5) THEN 1 ELSE 2 END
FROM vehicles
WHERE workToBeDone1 IS NOT NULL
OR workToBeDone2 IS NOT NULL
OR workToBeDone3 IS NOT NULL
OR workToBeDone4 IS NOT NULL
OR workToBeDone5 IS NOT NULL"-PatP|||Thanks Pat,
still getting the same error. here's more of the code (inc. your bit) to give you a bigger picture:

Set linkRS = Server.CreateObject("ADODB.Recordset")

salePrice = request.Form("salePrice")
make=request.Form("make")
model2show=request.Form("model2show")
salePrice=request.Form("salePrice")
fuel=request.Form("fuel")
sold=request.Form("sold")
workOutstanding=request.Form("workOutstanding")
notOnWebsite=request.Form("notOnWebsite")

strSQL="SELECT id, make, model, model2show, registration, price FROM vehicles WHERE price BETWEEN "& salePrice &""
if make <> "" then strSQL = strSQL & " AND make = '" & make & "'"

if fuel <> "" then strSQL = strSQL & " AND fuel = '" & fuel & "'"

if model2show <> "" then strSQL = strSQL & " AND model2show = '" & model2show & "'"

if sold = "yes" then strSQL = strSQL & " AND sold = 'yes'"

if workOutstanding = "yes" then strSQL = "SELECT id, make, model, CASE WHEN workToBeDone1 IS NULL THEN 0 WHEN 0 = Len(workToBeDone1) THEN 1 ELSE 2 END, CASE WHEN workToBeDone2 IS NULL THEN 0 WHEN 0 = Len(workToBeDone2) THEN 1 ELSE 2 END, CASE WHEN workToBeDone3 IS NULL THEN 0 WHEN 0 = Len(workToBeDone3) THEN 1 ELSE 2 END, CASE WHEN workToBeDone4 IS NULL THEN 0 WHEN 0 = Len(workToBeDone4) THEN 1 ELSE 2 END, CASE WHEN workToBeDone5 IS NULL THEN 0 WHEN 0 = Len(workToBeDone5) THEN 1 ELSE 2 END FROM vehicles WHERE workToBeDone1 IS NOT NULL OR workToBeDone2 IS NOT NULL OR workToBeDone3 IS NOT NULL OR workToBeDone4 IS NOT NULL OR workToBeDone5 IS NOT NULL"

if notOnWebsite = "yes" then strSQL = strSQL & " AND active = 'no'"

strSQL = strSQL & " ORDER BY make"
'response.Write(strSQL)
linkRS.Open strSQL, oConn, 2, 3
if (linkRS.BOF and linkRS.EOF) then
response.Write("<p class=""inputRed"">No vehicles to display - try selecting fewer parameters</p>")
else
linkRS.moveFirst
Do while not linkRS.eof
make = linkRS("make")
'etc.
'etc.

most of this works fine, but the error message is odd because those fields do exist.|||Uncomment your 'response.Write(strSQL) and post the result.
First glance suggests you have a problem with your BETWEEN statement|||here you go:
SELECT id, make, model, CASE WHEN workToBeDone1 IS NULL THEN 0 WHEN 0 = Len(workToBeDone1) THEN 1 ELSE 2 END, CASE WHEN workToBeDone2 IS NULL THEN 0 WHEN 0 = Len(workToBeDone2) THEN 1 ELSE 2 END, CASE WHEN workToBeDone3 IS NULL THEN 0 WHEN 0 = Len(workToBeDone3) THEN 1 ELSE 2 END, CASE WHEN workToBeDone4 IS NULL THEN 0 WHEN 0 = Len(workToBeDone4) THEN 1 ELSE 2 END, CASE WHEN workToBeDone5 IS NULL THEN 0 WHEN 0 = Len(workToBeDone5) THEN 1 ELSE 2 END FROM vehicles WHERE workToBeDone1 IS NOT NULL OR workToBeDone2 IS NOT NULL OR workToBeDone3 IS NOT NULL OR workToBeDone4 IS NOT NULL OR workToBeDone5 IS NOT NULL ORDER BY make|||Maybe it contain spaces, try this
where coalesce(workToBeDone1,workToBeDone2,workToBeDone3 ,workToBeDone4,workToBeDone5,'') != ''|||thanks,
same error msg tho'

Plan Guide

Is it possible to do something like this with a query plan?
I want to get
Query1
select * from tableA
where col1 like '%value%'
to run like
Query2
select * from tableA
where contains(col1, "value")
using a plan_guide.
Basically what's happening is a 3rd party product is executing a query that
looks like query1, which is searching on a text field (col1).
I wanted to try a full text index, but I don't think that I can get the
vendor to change their code to use a contains instead.
Any ideas?
Tia
--
MGEgads, why isn't the vendor using stored procedures?
"Hurme" <michael.geles@.thomson.com> wrote in message
news:FD780483-2D13-4284-8911-EF08FA332560@.microsoft.com...
> Is it possible to do something like this with a query plan?
> I want to get
> Query1
> select * from tableA
> where col1 like '%value%'
> to run like
> Query2
> select * from tableA
> where contains(col1, "value")
> using a plan_guide.
> Basically what's happening is a 3rd party product is executing a query
> that
> looks like query1, which is searching on a text field (col1).
> I wanted to try a full text index, but I don't think that I can get the
> vendor to change their code to use a contains instead.
> Any ideas?
> Tia
> --
> MG|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eXbLZijfIHA.5560@.TK2MSFTNGP04.phx.gbl...
> Egads, why isn't the vendor using stored procedures?
LOL with SQL statements hardwired into the client-side code the customer
can't go in and make changes like this to the code

PK

I have seen some look-up tables have no primary key because there is a null
or " " value in them. Since every table should have a Primary Key, can
anyone educate me how can I handle null or " " value, in the code level? I
am avoiding using Surrogate key.

Thanks,

J

P.S. Null or " " value is "Unknown".The primary key can not be null,or it can not be a primary key.
Add anther Column to this table to create a primary of this column.|||J wrote:
> I have seen some look-up tables have no primary key because there is a null
> or " " value in them. Since every table should have a Primary Key, can
> anyone educate me how can I handle null or " " value, in the code level? I
> am avoiding using Surrogate key.
>
> Thanks,
> J
>
> P.S. Null or " " value is "Unknown".

Nothing technically wrong with ' ' as a key if that makes sense to your
business. If you have nulls then eliminate them either by decomposition
or by encoding the unknown value as something else. There is no excuse
not to have a candidate key.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||J, I suspect that if you have NULL in the colum or a column of what
should be the natural key to a table that you have bad data or a
non-normalized table.

Fixing bad data is usually easier than fixing bad design.

Adding a surrogate key will do nothing to protect the integrity of the
business data in the columns in question.

Good luck
-- Mark D Powell --

Pivot Query - Assigning Row Value Based on Column Name

Hey all,

i have a question for all the SQL Gurus out there. I have tried to think of a way around, it, but i cannot work it out.

I have a set of data: Samples Below:

Item Warehouse FOR1 FOR2 FOR3 FOR4 FOR5 FOR6 FOR7 FOR8 FOR9 FOR10 FOR11 FOR12 FOR13 FOR14

01-0001 010 329 329 335 343 317 331 328 331 31

I have written a Query to Pivot this data like below:

SELECT WAREHOUSE,ITEM, QTY

FROM

(SELECT ITEM,WAREHOUSE,FOR1,FOR2,for3,for4,for5,for6,for7,for8,for9,for10,
for11,for12,for13,for14,for15,for16,for17,for18,for19,for20,for21,
for22,for23,for24 FROM mvxreport.tbldmsForecasttoMovex) p

UNPIVOT

(QTY FOR tbldmsForecasttoMovex IN (FOR1,FOR2,for3,for4,for5,for6,for7,
for8,for9,for10,for11,for12,for13,for14,for15,for16,for17,for18,for19,
for20,for21,for22,for23,for24))AS unpvt

Warehouse Item Qty
010 01-0001 329
010 01-0001 329
010 01-0001 335
010 01-0001 343
010 01-0001 317
010 01-0001 331
010 01-0001 328
010 01-0001 331
010 01-0001 315
010 01-0001 344
010 01-0001 334
010 01-0001 321
010 01-0001 327
010 01-0001 328
010 01-0001 332
010 01-0001 342
010 01-0001 316
010 01-0001 330
010 01-0001 330
010 01-0001 331
010 01-0001 315
010 01-0001 343
010 01-0001 333
010 01-0001 322

I would like to add some more code to the query, so for each FOR% column,
i can put a numeric value in it. The value will be the numbers ,1 - 24 . One for each line as this represents Months Forward.

Example:

Warehouse Item Qty Month
010 01-0001 329 1
010 01-0001 329 2
010 01-0001 335 3
010 01-0001 343 4
010 01-0001 317 5
010 01-0001 331 6
010 01-0001 328 7
010 01-0001 331 8
010 01-0001 315 9
010 01-0001 344 10
010 01-0001 334 11
010 01-0001 321 12
010 01-0001 327 13
010 01-0001 328 14
010 01-0001 332 15
010 01-0001 342 16
010 01-0001 316 17
010 01-0001 330 18
010 01-0001 330 19
010 01-0001 331 20
010 01-0001 315 21
010 01-0001 343 22
010 01-0001 333 23
010 01-0001 322 24

Does anyone know how i can do this?

Many Thnank

Scotty

Use the below query,

Code Block

SELECT WAREHOUSE,ITEM, QTY, replace(monthfor,'For','') as [Month]

FROM

(SELECT ITEM,WAREHOUSE,FOR1,FOR2,for3,for4,for5,for6,for7,for8,for9,for10,

for11,for12,for13,for14,for15,for16,for17,for18,for19,for20,for21,

for22,for23,for24 FROM mvxreport.tbldmsForecasttoMovex) p

UNPIVOT

(QTY FOR monthfor IN (FOR1,FOR2,for3,for4,for5,for6,for7,

for8,for9,for10,for11,for12,for13,for14,for15,for16,for17,for18,for19,

for20,for21,for22,for23,for24))AS unpvt

|||

I think you can use ROW_NUMBER() function, something like that:

select Warehouse,Item, ROW_NUMBER() OVER (ORDER BY item) as Aqty

from

(

SELECT WAREHOUSE,ITEM, QTY

FROM

(SELECT ITEM,WAREHOUSE,FOR1,FOR2,for3,for4,for5,for6,for7,for8,for9,for10,
for11,for12,for13,for14,for15,for16,for17,for18,for19,for20,for21,
for22,for23,for24 FROM mvxreport.tbldmsForecasttoMovex) p

UNPIVOT

(QTY FOR tbldmsForecasttoMovex IN (FOR1,FOR2,for3,for4,for5,for6,for7,
for8,for9,for10,for11,for12,for13,for14,for15,for16,for17,for18,for19,
for20,for21,for22,for23,for24))AS unpvt

)

|||

thats awesome manivannan, once again you come to the rescue!!

thanks mate

Scotty

|||

recome,

select Warehouse,Item, ROW_NUMBER() OVER (ORDER BY item) as Month

from ...

Pivot Key Values

Is it possible to use a expression as a pivot key value?

I need to take data from one table and pivot it into another but what pivots to where is conditional, so I'm wondering if it's possible to say something like

"where Column1 = 'A' and Column2 = 'B' " in the pivot key value.

I'm trying to get it to work but can't, just keep getting the 'No pivot Key found' error.

Thanks

bobbins,

How many conditions are there? You could just use a conditional split to grab the records with the correct values per type and use a seperate chain for each, doing a union all to get them back together after the chain.

--

Looking at it more closely you are probably trying to unpivot (un-normalize). You could possibly use an expression to do the trick. The expression is located on the data flow task and is referenced in a manner similar to Unpivot.Unpivot Input.ColumnName.PivotKeyValue . (NOTE: both pivot and unpivot expose the pivotkeyvalue as data flow property expressions) Although, to be perfectly honest, I'm not sure if you will have access to the record information going through the pipe at the moment or not, so that might not work either...

|||You could pivot the values with a script. Using the script allows you a lot more flexibility in defining the pivoting rules.|||

Thanks for the replies, I am trying to de-normalize the data, here is a better explanation of what I'm trying to do:

My source data is an Ingres db on Unix:

MemberID Date Value Code Type 5029348 01/12/2006 79 A 1 5029348 01/12/2006 8 B 2 5029348 01/12/2006 4 C 3

I want to put the data in a de-normalized table so it looks like this:

MemberID Date ValueA1 ValueB2 ValueC3 5029348 01/12/2006 79 8 4

So my data mapping rules are:

Where Code = A and Type = 1 then map to ValueA1

Where Code = B and Type = 2 then map to ValueB2

Where Code = C and Type = 3 then map to ValueC3

I would like to do this as the data comes through the pipe instead of creating a staging table at either the source end or destination and then just bumping the data straight in from the staging table. I have limited experience with SSIS and want to learn but I'm struggling to work out what to do in the time I've been given to do this, hence my question about what you can actually put in the Pivot Key Values. A conditional split will split the values out but how do I put them all back together again as one row per MemberID to go into the destination? Or is there another way to do this?

Thanks again

|||

Take a look at this post. http://agilebi.com/cs/blogs/jwelch/archive/2007/05/18/dynamically-pivoting-columns-to-rows.aspx

You'll have to alter the script to not use the Split function, and apply your mapping rules, but it should provide a good starting point. If you are still having problems, post back here and we'll help.

Pivot Error

i am fallowing this link

http://sqljunkies.com/Article/705F07C3-69FE-4CAF-8CF8-CADBF145F372.scuk

last 15 th i am unable to understatnd the value to set tht pivotkey value for the output columns

INPUT COLUMNS LEGEND ID

CUSTOMER 861

PRODUCT 864

ID

OUPUT COLUMNS SOUREC COLUMN PIVOT KEY VALUE

CUSTOMER 861 ?

HamQty 864 ?

MILKQTY 864 ?

BEERQTY 864 ?

BREADQTY 864 ?

CHESSQTY 864 ?

HAMQTY CANNOT MAPPED WITH PIVOTKEY VALUE ERROR I AM GETTING

PLEASE HELP ME

You have run into a special degenerate case for the Pivot transform.

You do not have a pivot value. You simply have customers that purchase products, but you have no measurement such as “quantity” associated with the purchase. The existence of the row acts as a measure.

So what is the pivot value? There is not one.

You can set the lineage ID for the output columns to “-1” this is a special case use. When you do so, the value “TRUE” will be put into columns that have matching existing rows in the source.

This also means that your output columns need to be BOOLEAN typed so they can hold the value TRUE. (or NULL if not matched)

to use your example:

Input data

Customer(861) Product (864)

(the set key) (the pivot key)

-

JohnCheese

JohnBread

BillHam

Turns into

Customer CheeseExists BreadExists HamExists BeerExists

-- -- -

JohnTRUETRUENULL NULL

BillNULLNULLTRUENULL

Your output columns would be:

NameType SourceColumn PivotKeyValue

-- -

Customerchar861

CheeseExists VT_BOOL-1Cheese

BreadExistsVT_BOOL-1Bread

HamExistsVT_BOOL-1Ham

BeerExistsVT_BOOL-1Beer