Crystal Reports - Finding Previous Record Values

Hi,

I'm trying to find out the previous value of a record in Crystal 10.

I know I need to use the previous({recordset}) to get it, however if the previous value is = to "something" I need to keep looking at down the recordset until the value does not = "something"

I know it needs to be something along the lines of

If Previous({group}) like "MGS CCT*" then

Else Previous({group})

Just not sure what fits in the middle.

Can anyone advise ?

Many thanks
Steve[ moved ]

Crystal Reports

hai

how to get the textbox value from a form to the crystalreport.I have two textbox

form1-------> TextBox1

crystal report ---->TextBox2

i want to assign the values of the textbox2 in the crystal report by the value of the Textbox1.Any body know the solution plz help me.you try using parameter field in crystal report and you can pass value to it from form.|||hai,

Thank you very much for ur information.If ur having any example code please sent it to me.|||Read about parameters in CR help file

Crystal Report issue - passing text value in vb to crystal

I have a date time picker for begin date and end date to generate my report. I want the begin date and end date selected in my VB App to show on my crystal report as a selected date range. For example, I want my report header to show something like this:

Date Range: 12/14/06 - 12/31/06

This is most likely an easy solution, but I am new to Crystal. What would be the proper way to do this?Figured this one out.....|||How do you resolved this, can you put your code here?

Crystal Report displays wrong value.

Hi,

There is a issue in VB & Crystal report version is 8.5.
I have got a report which displays wrong values though the values returned from the database is correct. It displays all the numbers by dividing 100 to it. I am getting correct values in some of the machines but not in rest. For eg if the value returned from the database is 80 then the report displays the value as 0.8 in some of the machines which is wrong. How do I correct this wierd error? Is there any system based setting where in I could go and change? We have checked the following things and it seems to be correct (DSN settings, Reports Versions)

Crystal report version is 8.5. In users m/c, no crystal report is installed.
Please tell me are there any settings to be set in user machines.

Reply asap.

Regards
Shilpai hope ur problem will be solved if u follow the instructions here...

1) Rt.Click on the Field.
2) choose "Format".
3) check which Style is selected in the "Number" Tab.
NOTE: if it is system default then change it according to ur requirement.

if u r using "Customize" then do following
1) Click on "Customize"
2) Click on "Currency Symbol" Tab.
3) Make sure "Use System Default Format" is Unchecked.
NOTE: if it is checked then make it Unchecked.
Mostly people sets the format according to thier need but
ignore this option which makes such type of troubles.

Hope will be helpfull........Cheer up :D|||Crystal report version is 8.5 .

I checked in the crystal reports on the
first step
1) Rt.Click on the Field.
2) choose "Format".
3) check which Style is selected in the "Number" Tab.
there is no system settings enabled.

Also I couldnt find option specified in the Currency Symbol tab...:

Please reply.

Regards
Shilpa|||Crystal report version is 8.5 .

I checked in the crystal reports on the
first step
1) Rt.Click on the Field.
2) choose "Format".
3) check which Style is selected in the "Number" Tab.
there is no system settings enabled.

Also I couldnt find option specified in the Currency Symbol tab...:

Please reply.

Regards
Shilpa

i think u did not follow the second step..

1) Rt.Click on the Field.
2) choose "Format".(from the pop up window)

you will see "Format Editor" window like in "1-Second Step"
then watch "2-Second Step" and "3-Second Step" respectively.|||i think u did not follow the second step..

1) Rt.Click on the Field.
2) choose "Format".(from the pop up window)

you will see "Format Editor" window like in "1-Second Step"
then watch "2-Second Step" and "3-Second Step" respectively.

I have followed the above steps. But there is no Use System Default format option in step 2 that is in Customize section. You can see images attached for this. I think the version which u have worked is different. Here version is 8.5. Please tell me if you know anything.|||I have followed the above steps. But there is no Use System Default format option in step 2 that is in Customize section. You can see images attached for this. I think the version which u have worked is different. Here version is 8.5. Please tell me if you know anything.

i m using Crystal Report 8.5 and the problem i was expecting is not in your case.

i got rid of the same situation by the steps i told u.|||U can also change the Number format of those systems to that one on which u getting the correct result by

Start->Settings->Control Panel->Regional Settings->Numbers (Tab)

Crystal Report 8.0, Summary function !

Friends,

I'm working with crystal report 8.0 and SQL server.

I need to create a formula in group section, which should return the summary value of next group.

Or is there any function for get summary value if the group value is 'x'. same as SumIF( , , ) in excel.You need to write a formula having the code

If groupvalue='x' then
{field}

and use the summary using this

Crystal Report (using stored procedures - parameter passing)

i have created a crystal report using a stored procedure. in the web form im passing a parameter thru a textbox. but that value is not passing properly.

Dim myCMD As New SqlCommand("select_all_infor", myConn)
myCMD.CommandType = CommandType.StoredProcedure
Dim parr As New SqlClient.SqlParameter("@.Pid", SqlDbType.Int)
parr.Direction = ParameterDirection.Input
parr.Value = TextBox1.Text
myCommand.Parameters.Add(parr)
myCommand.Parameters.Add("@.Pid", TextBox1.Text)

Dim myDA As New SqlDataAdapter
myDA.SelectCommand = myCMD

Dim myDS As New myDS
myDA.Fill(myDS)

Dim myRPT As New fromSP_REPORT

myRPT.SetDataSource(myDS)

CrystalReportViewer1.ReportSource = myRPT

this is what i've written. Im not sure about connecting to a dataset. Pls correct me.

it's giving this error:

Procedure 'select_all_infor' expects parameter '@.Pid', which was not supplied.try to use this.

crReportDocument.SetParameterValue(0,(object) "first parameter");
crReportDocument.SetParameterValue(1,(object) "second parameter");

Cross-tab query definition problem

Hi there,
I have got this problem. I want to know which concrete combination of 7
variables gives the maximum value of some non-linear function. I have
created 7 tables (per 1 column) with all possible values of each
variable (e.g. for the first variable: 0.00,0.01,0.02,0.03,...,1.00)
and then made the cross-tab query based on these 7 tables: Select
Max(...) from dbo.var1 cross join dbo.var2 ... cross join dbo.var7.
This query finds the maximum value of the function but how should I
find out which concrete combination of the variables is connected with
this max.value?
I know that it is not clever to use relational DBS for this problem but
I want to find out how quickly SQL server calculates the result.
Thanks a lot.
MilanHello, Milan
You are talking about cross joins, not crosstabs.
You can get the values using something like this:
SELECT Col1, Col2, ...
FROM Table1, Table2, ...
WHERE YourExpression=(
SELECT MAX(YourExpression)
FROM Table1, Table2, ...
)
If you have 7 tables with 100 values each, that would be a total of
100000000000000 combinations (that is one hundred thousand billions). I
would not dare to think how many years it would take for SQL Server to
return the result of this query.
Razvan|||Hello Razvan,
Thanks for your answer. I am sorry for that mistake, of course I ment
cross joins :).
The problem with your solution is that expression for every
combinations have to be calculated two-times. Since the total number of
combinations is great (the number of rows in each table is: 100, 20,
10, 10, 10, 5, 20 resp., which gives 200 mil. combinations) there is a
serious problem with calculation. Is it somehow possible to obtain the
concrete combination of variables which yield the maximum value of that
function within one single query'
Thank you.
Milan|||Another way would be:
SELECT TOP 1 WITH TIES Col1, Col2, ...
FROM Table1, Table2, ...
ORDER BY YourExpression DESC
Razvan

Crosstab Help in Sql Server

I need some assistance, i have this Stored Procedure that will take my table
and create a cross tab. Unfor, I am getting duplicate value from the output,
when checking table1, there are no dups, i dont' know what i am doing wrong.
Please help
below, code that I am using. thaks
=================================
DECLARE @.Month_1_V as varchar(20)
DECLARE @.Create_Date_V as Datetime
DECLARE @.Corp_V as Numeric(13)
DECLARE @.Source_V as Varchar(20)
DECLARE @.Category_V as Varchar(50)
DECLARE @.Description_1_V as Varchar(50)
DECLARE @.Cycle_V as Varchar(1)
DECLARE @.Count_1_V as Numeric(13)
DECLARE @.Month_1_V2 as varchar(20)
DECLARE @.Create_Date_V2 as Datetime
DECLARE @.Corp_V2 as Numeric(13)
DECLARE @.Source_V2 as Varchar(20)
DECLARE @.Category_V2 as Varchar(50)
DECLARE @.Description_1_V2 as Varchar(50)
DECLARE @.Cycle_V2 as Varchar(1)
DECLARE @.Count_1_V2 as Numeric(13)
DECLARE @.V1 as Numeric(13)
DECLARE @.V2 as Numeric(13)
DECLARE @.V3 as Numeric(13)
DECLARE @.V4 as Numeric(13)
DECLARE @.V5 as Numeric(13)
DECLARE @.V6 as Numeric(13)
DECLARE @.V7 as Numeric(13)
DECLARE @.V8 as Numeric(13)
DECLARE @.V9 as Numeric(13)
DECLARE @.V10 as Numeric(13)
DECLARE @.V11 as Numeric(13)
truncate table tbl_Agency
DECLARE t_Noble_Agency CURSOR
FOR
SELECT DISTINCT Month_1, Create_date, Corp, Source, Category, SUM(Count_1)
AS Count_1, Description_1, Cycle
FROM phonecol.temp_tblAgency
WHERE (Create_date BETWEEN CONVERT(DATETIME, '2006-05-12 00:00:00', 102)
AND CONVERT(DATETIME, '2006-05-12 00:00:00', 102))
GROUP BY Month_1, Create_date, Corp, Source, Category, Description_1, Cycle
ORDER BY Category, Corp, Source
OPEN t_Noble_Agency
FETCH NEXT FROM t_Noble_Agency INTO @.Month_1_V, @.Create_Date_V, @.Corp_V,
@.Source_V, @.Category_V, @.Count_1_V, @.Description_1_V, @.Cycle_V
IF @.@.FETCH_STATUS = 0
BEGIN
Set @.Month_1_V2 = @.Month_1_V
SET @.Create_Date_V2 = @.Create_Date_V
SET @.Corp_V2 = @.Corp_V
SET @.Source_V2 = @.Source_V
SET @.Category_V2 = @.Category_V
SET @.Cycle_V2 = @.Cycle_V
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
IF (@.Category_V2 <> @.Category_V)or(@.Corp_V2 <> @.Corp_V) or (@.Source_V2 <>
@.Source_V)
BEGIN
INSERT INTO tbl_Agency(Month_1, Type, Loaddate, Agency, Corp, Cycle,
Total_Accounts,Call_Backs, [Left_Msg(Machine)], [Left_Msg(Live)],
Promise_to_Pay, Full_Pay, Partial_Pay, Past_Due_Pay,
Wrong_Number,TriTones,Skip_Trace,Not_Rep
orted)
VALUES (@.Month_1_V2 , @.Category_V2, @.Create_Date_V2, @.Source_V2, @.Corp_V2,
@.Cycle_V2,
isnull(@.v1,0)+isnull(@.v2,0)+isnull(@.v3,0
)+isnull(@.v4,0)+isnull(@.v5,0)+isnull
(@.v6,0)+isnull(@.v7,0)+isnull(@.v8,0)+
isnull(@.v9,0)+isnull(@.v10,0)+isnull(@.v11
,0), isnull(@.v1,0), isnull(@.v2,0),
isnull(@.v3,0), isnull(@.v4,0), isnull(@.v5,0), isnull(@.v6,0), isnull(@.v7,0),
isnull(@.v8,0), isnull(@.v9,0),isnull(@.v10,0),isnull(@.v11
,0))
Set @.Month_1_V2 = @.Month_1_V
SET @.Create_Date_V2 = @.Create_Date_V
SET @.Corp_V2 = @.Corp_V
SET @.Source_V2 = @.Source_V
SET @.Category_V2 = @.Category_V
SET @.Cycle_V2 = @.Cycle_V
END
--@.V1 = 'Call_Backs'
--@.V2 = '[Left_Msg(Machine)]'
--@.V3 = '[Left_Msg(Live)]'
--@.V4 = 'Promise_to_Pay'
--@.V5 = 'Full_Pay'
--@.V6 = 'Partial_Pay'
--@.V7 = 'Past_Due_Pay'
--@.V8 = 'Wrong_Number'
--@.V9= 'TriTones'
--@.V10 = 'Skip_Trace'
--@.V11 = 'Not_Reported'
IF ltrim(rtrim(@.Description_1_V)) = 'Call Back'
Begin
SET @.V1 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Left Message (Answering Machine)'
Begin
SET @.V2 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Left Message (Live Person)'
Begin
SET @.V3 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Promise To Pay'
Begin
SET @.V4 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Received Payment (Full)'
Begin
SET @.V5 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Received Payment (Partial Pmt)'
Begin
SET @.V6 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Received Payment (Past Due)'
Begin
SET @.V7 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Wrong Number'
Begin
SET @.V8 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Tri-Tones'
Begin
SET @.V9 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Skip Trace Customers Removed Prior to
Contact'
Begin
SET @.V10 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Not Reported'
Begin
SET @.V11 = @.Count_1_V
END
END
FETCH NEXT FROM t_Noble_Agency INTO @.Month_1_V, @.Create_Date_V, @.Corp_V,
@.Source_V, @.Category_V, @.Count_1_V, @.Description_1_V, @.Cycle_V
END
INSERT INTO tbl_Agency(Month_1, Type, Loaddate, Agency, Corp, Cycle,
Total_Accounts,Call_Backs, [Left_Msg(Machine)], [Left_Msg(Live)],
Promise_to_Pay, Full_Pay, Partial_Pay, Past_Due_Pay,
Wrong_Number,TriTones,Skip_Trace,Not_Rep
orted)
VALUES (@.Month_1_V2 , @.Category_V2, @.Create_Date_V2, @.Source_V2, @.Corp_V2,
@.Cycle_V2,
isnull(@.v1,0)+isnull(@.v2,0)+isnull(@.v3,0
)+isnull(@.v4,0)+isnull(@.v5,0)+isnull
(@.v6,0)+isnull(@.v7,0)+isnull(@.v8,0)+
isnull(@.v9,0)+isnull(@.v10,0)+isnull(@.v11
,0), isnull(@.v1,0), isnull(@.v2,0),
isnull(@.v3,0), isnull(@.v4,0), isnull(@.v5,0), isnull(@.v6,0), isnull(@.v7,0),
isnull(@.v8,0), isnull(@.v9,0),isnull(@.v10,0),isnull(@.v11
,0))
END
CLOSE t_Noble_Agency
DEALLOCATE t_Noble_Agency
GOHi Justin,
I didn't look close into the SP. But from a high level I find that you
have two inserts for 1 fetch within the while loop (one inside the if
condition and one outside in the end). Can you tell why you are doing it.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Justin" wrote:

> I need some assistance, i have this Stored Procedure that will take my tab
le
> and create a cross tab. Unfor, I am getting duplicate value from the outpu
t,
> when checking table1, there are no dups, i dont' know what i am doing wron
g.
> Please help
> below, code that I am using. thaks
> =================================
> DECLARE @.Month_1_V as varchar(20)
> DECLARE @.Create_Date_V as Datetime
> DECLARE @.Corp_V as Numeric(13)
> DECLARE @.Source_V as Varchar(20)
> DECLARE @.Category_V as Varchar(50)
> DECLARE @.Description_1_V as Varchar(50)
> DECLARE @.Cycle_V as Varchar(1)
> DECLARE @.Count_1_V as Numeric(13)
> DECLARE @.Month_1_V2 as varchar(20)
> DECLARE @.Create_Date_V2 as Datetime
> DECLARE @.Corp_V2 as Numeric(13)
> DECLARE @.Source_V2 as Varchar(20)
> DECLARE @.Category_V2 as Varchar(50)
> DECLARE @.Description_1_V2 as Varchar(50)
> DECLARE @.Cycle_V2 as Varchar(1)
> DECLARE @.Count_1_V2 as Numeric(13)
> DECLARE @.V1 as Numeric(13)
> DECLARE @.V2 as Numeric(13)
> DECLARE @.V3 as Numeric(13)
> DECLARE @.V4 as Numeric(13)
> DECLARE @.V5 as Numeric(13)
> DECLARE @.V6 as Numeric(13)
> DECLARE @.V7 as Numeric(13)
> DECLARE @.V8 as Numeric(13)
> DECLARE @.V9 as Numeric(13)
> DECLARE @.V10 as Numeric(13)
> DECLARE @.V11 as Numeric(13)
> truncate table tbl_Agency
> DECLARE t_Noble_Agency CURSOR
> FOR
> SELECT DISTINCT Month_1, Create_date, Corp, Source, Category, SUM(Count_1)
> AS Count_1, Description_1, Cycle
> FROM phonecol.temp_tblAgency
> WHERE (Create_date BETWEEN CONVERT(DATETIME, '2006-05-12 00:00:00', 10
2)
> AND CONVERT(DATETIME, '2006-05-12 00:00:00', 102))
> GROUP BY Month_1, Create_date, Corp, Source, Category, Description_1, Cycl
e
> ORDER BY Category, Corp, Source
> OPEN t_Noble_Agency
> FETCH NEXT FROM t_Noble_Agency INTO @.Month_1_V, @.Create_Date_V, @.Corp_V,
> @.Source_V, @.Category_V, @.Count_1_V, @.Description_1_V, @.Cycle_V
> IF @.@.FETCH_STATUS = 0
> BEGIN
> Set @.Month_1_V2 = @.Month_1_V
> SET @.Create_Date_V2 = @.Create_Date_V
> SET @.Corp_V2 = @.Corp_V
> SET @.Source_V2 = @.Source_V
> SET @.Category_V2 = @.Category_V
> SET @.Cycle_V2 = @.Cycle_V
> WHILE (@.@.FETCH_STATUS <> -1)
> BEGIN
> IF (@.@.FETCH_STATUS <> -2)
> BEGIN
> IF (@.Category_V2 <> @.Category_V)or(@.Corp_V2 <> @.Corp_V) or (@.Source_V2 <>
> @.Source_V)
> BEGIN
> INSERT INTO tbl_Agency(Month_1, Type, Loaddate, Agency, Corp, Cycle,
> Total_Accounts,Call_Backs, [Left_Msg(Machine)], [Left_Msg(Live)],
> Promise_to_Pay, Full_Pay, Partial_Pay, Past_Due_Pay,
> Wrong_Number,TriTones,Skip_Trace,Not_Rep
orted)
> VALUES (@.Month_1_V2 , @.Category_V2, @.Create_Date_V2, @.Source_V2, @.Corp_V2,
> @.Cycle_V2,
> isnull(@.v1,0)+isnull(@.v2,0)+isnull(@.v3,0
)+isnull(@.v4,0)+isnull(@.v5,0)+isnu
ll(@.v6,0)+isnull(@.v7,0)+isnull(@.v8,0)+
> isnull(@.v9,0)+isnull(@.v10,0)+isnull(@.v11
,0), isnull(@.v1,0), isnull(@.v2,0),
> isnull(@.v3,0), isnull(@.v4,0), isnull(@.v5,0), isnull(@.v6,0), isnull(@.v7,0),
> isnull(@.v8,0), isnull(@.v9,0),isnull(@.v10,0),isnull(@.v11
,0))
> Set @.Month_1_V2 = @.Month_1_V
> SET @.Create_Date_V2 = @.Create_Date_V
> SET @.Corp_V2 = @.Corp_V
> SET @.Source_V2 = @.Source_V
> SET @.Category_V2 = @.Category_V
> SET @.Cycle_V2 = @.Cycle_V
> END
> --@.V1 = 'Call_Backs'
> --@.V2 = '[Left_Msg(Machine)]'
> --@.V3 = '[Left_Msg(Live)]'
> --@.V4 = 'Promise_to_Pay'
> --@.V5 = 'Full_Pay'
> --@.V6 = 'Partial_Pay'
> --@.V7 = 'Past_Due_Pay'
> --@.V8 = 'Wrong_Number'
> --@.V9= 'TriTones'
> --@.V10 = 'Skip_Trace'
> --@.V11 = 'Not_Reported'
> IF ltrim(rtrim(@.Description_1_V)) = 'Call Back'
> Begin
> SET @.V1 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Left Message (Answering Machine)'
> Begin
> SET @.V2 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Left Message (Live Person)'
> Begin
> SET @.V3 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Promise To Pay'
> Begin
> SET @.V4 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Received Payment (Full)'
> Begin
> SET @.V5 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Received Payment (Partial Pmt)'
> Begin
> SET @.V6 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Received Payment (Past Due)'
> Begin
> SET @.V7 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Wrong Number'
> Begin
> SET @.V8 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Tri-Tones'
> Begin
> SET @.V9 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Skip Trace Customers Removed Prior to
> Contact'
> Begin
> SET @.V10 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Not Reported'
> Begin
> SET @.V11 = @.Count_1_V
> END
> END
> FETCH NEXT FROM t_Noble_Agency INTO @.Month_1_V, @.Create_Date_V, @.Corp_V,
> @.Source_V, @.Category_V, @.Count_1_V, @.Description_1_V, @.Cycle_V
> END
> INSERT INTO tbl_Agency(Month_1, Type, Loaddate, Agency, Corp, Cycle,
> Total_Accounts,Call_Backs, [Left_Msg(Machine)], [Left_Msg(Live)],
> Promise_to_Pay, Full_Pay, Partial_Pay, Past_Due_Pay,
> Wrong_Number,TriTones,Skip_Trace,Not_Rep
orted)
> VALUES (@.Month_1_V2 , @.Category_V2, @.Create_Date_V2, @.Source_V2, @.Corp_V2,
> @.Cycle_V2,
> isnull(@.v1,0)+isnull(@.v2,0)+isnull(@.v3,0
)+isnull(@.v4,0)+isnull(@.v5,0)+isnu
ll(@.v6,0)+isnull(@.v7,0)+isnull(@.v8,0)+
> isnull(@.v9,0)+isnull(@.v10,0)+isnull(@.v11
,0), isnull(@.v1,0), isnull(@.v2,0),
> isnull(@.v3,0), isnull(@.v4,0), isnull(@.v5,0), isnull(@.v6,0), isnull(@.v7,0),
> isnull(@.v8,0), isnull(@.v9,0),isnull(@.v10,0),isnull(@.v11
,0))
> END
> CLOSE t_Noble_Agency
> DEALLOCATE t_Noble_Agency
> GO|||sure,
what happens first is that data will be imported to a temporary table
from there, the SP should take the data from that table and change it to a
crosstab
inserting output to finalize table
unfor, in the finalize table, i am getting duplicated values
"Omnibuzz" wrote:
> Hi Justin,
> I didn't look close into the SP. But from a high level I find that you
> have two inserts for 1 fetch within the while loop (one inside the if
> condition and one outside in the end). Can you tell why you are doing it.
>
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "Justin" wrote:
>|||Where is the insert script for the finalise table?
I assume that tbl_Agency is the temp table you are referring to and it can
have duplicates. Can you post the cross tab query?
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Justin" wrote:
> sure,
> what happens first is that data will be imported to a temporary table
> from there, the SP should take the data from that table and change it to a
> crosstab
> inserting output to finalize table
> unfor, in the finalize table, i am getting duplicated values
>
> "Omnibuzz" wrote:
>

cross tab query

Hi - I have what I think is a "simple problem".

We currently have a database table that stores ItemProperties by
ItemID, PropertyID and Value. (The PropertyID references another table
for property names and types.)

This ItemProperties table is indexed and provides a flexible way of
storing our item metadata. However, I would now like to return
recordsets to the application layer that list these properties in
column fashion, grouped by ItemID

I have seen that most cross-tab queries examples assume numerical data
and are based around using SUM and the GROUP BY phrase. But our data
is a mixture of string and numbers (of various formats) and so GROUP
BY is not an obvious solution. I have tried using CASE in the select
list but this returns one row for each property with one column having
the correct value, and all the other colums are NULL. I cannot think
of how to combine these into one full record!!

I could achieve the desired resultset by using a SELECT sub-statement
for every column, but I was hoping there was a more efficient method.

Can anyone offer advice on this? It would be most appreciated.

Best,

Bill> I have seen that most cross-tab queries examples assume numerical data
> and are based around using SUM and the GROUP BY phrase. But our data
> is a mixture of string and numbers (of various formats) and so GROUP
> BY is not an obvious solution. I have tried using CASE in the select
> list but this returns one row for each property with one column having
> the correct value, and all the other colums are NULL. I cannot think
> of how to combine these into one full record!!

Hi Bill,

Put your table into a hashish table, implement the IUnpronounceable
interface to convert it to XML, overload it with a semi-private method
name foo, call it from another method with a meaningless name that is
also a swear word, drag and drop into the trash can (sorry I meant
recycle bin), and do start->run->format c:. Seriously, I think this
might give you an idea.

SELECT
itemID,
name=MAX(case when propertyID='name' then value else null end),
color=MAX(case when propertyID='color' then value else null end),
size=MAX(case when propertyID='size' then value else null end)
FROM Property
GROUP BY itemID

cross reference of database

Hi all gurus
From Pubs database I want to get some ObjectProperty value from NorthWind
ObjectProperty(<object id of Northwind DB> , <property> )
Is it possible?
Thanksuse pubs
go
select objectproperty(object_id('northwind.dbo.orders'), 'IsUserTable')
go
AMB
"AM" wrote:

> Hi all gurus
> From Pubs database I want to get some ObjectProperty value from NorthWind
> ObjectProperty(<object id of Northwind DB> , <property> )
> Is it possible?
> Thanks
>
>|||Sorry boss
Its not working
Thanks
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:CA3BF8A4-8AB9-4E9C-BC71-E3C85670551E@.microsoft.com...
> use pubs
> go
> select objectproperty(object_id('northwind.dbo.orders'), 'IsUserTable')
> go
>
> AMB
> "AM" wrote:
>
NorthWind|||AM,
You are right. Function objectproerty expects an object id from current
database. Try,
use pubs
go
declare @.i int
exec sp_executesql N'use northwind; select @.i =
objectproperty(object_id(''dbo.orders''), ''IsUserTable'')', N'@.i int
output', @.i output
print @.i
go
AMB
"AM" wrote:

> Sorry boss
> Its not working
> Thanks
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:CA3BF8A4-8AB9-4E9C-BC71-E3C85670551E@.microsoft.com...
> NorthWind
>
>|||Thanks
It works
But one more question
How can I use for database on other server
Thanks
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:9AA0B83B-509B-4D2F-87A1-A4913B5A898D@.microsoft.com...
> AM,
> You are right. Function objectproerty expects an object id from current
> database. Try,
> use pubs
> go
> declare @.i int
> exec sp_executesql N'use northwind; select @.i =
> objectproperty(object_id(''dbo.orders''), ''IsUserTable'')', N'@.i int
> output', @.i output
> print @.i
> go
>
> AMB
> "AM" wrote:
>
message
'IsUserTable')|||AM,
You have to add the server as a linked server. See sp_addlinkedserver in BOL
.
select
*
from
openquery(linked_server, 'exec sp_executesql N''set nocount on; use
northwind; select objectproperty(object_id(''''dbo.orders''''),
''''IsUserTable'''')''')
go
AMB
"AM" wrote:

> Thanks
> It works
> But one more question
> How can I use for database on other server
> Thanks
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:9AA0B83B-509B-4D2F-87A1-A4913B5A898D@.microsoft.com...
> message
> 'IsUserTable')
>
>|||Thanks for the answer
I have found one more solution
USE northwind
GO
declare @.val varchar(100)
set @.val = (SELECT a.*
FROM OPENROWSET('SQLOLEDB',
'DRIVER={SQL Server};SERVER=amish;UID=sa;database=pub
s',
'select objectproperty(object_id(''pubs.dbo.authors''),
''IsUserTable'')') AS a)
print @.val
GO
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:39FBD04B-066B-4AFD-BBA3-6C309ED41F44@.microsoft.com...
> AM,
> You have to add the server as a linked server. See sp_addlinkedserver in
BOL.
> select
> *
> from
> openquery(linked_server, 'exec sp_executesql N''set nocount on; use
> northwind; select objectproperty(object_id(''''dbo.orders''''),
> ''''IsUserTable'''')''')
> go
>
> AMB
> "AM" wrote:
>
message
current