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 trigger

Aperently I could not insert a text field to another table from INSERTED in a trigger.
It seems th follwoing is working, do you see any problem joining INSERTED to the mySrcTable
which is the table that has this trigger

INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
SELECT i.myTrID, i.myFirstName, p.myBigText
FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
WHERE (i.myTrType = 'In')

Thanks,

You can set the compatibility level to 80 or higher Or you can choose varchar(max) which has limitation of 8000 characters.

For details seeCREATE TRIGGER.

Hope it helps.

sql

Plain text (fixed length field) report output?

I am desperate to get output from Reporting Services into a text
file. Any plugins, converters or technique that anybody knows of?On Aug 2, 12:20 pm, datam...@.gmail.com wrote:
> I am desperate to get output from Reporting Services into a text
> file. Any plugins, converters or technique that anybody knows of?
Outside of the export option of Reporting Services and the Report Mgr,
you would most likely need to design a custom asp.net application that
could output the results of an ADO.NET dataset. An SSRS report could
be executed programmatically (this link might help:
http://msdn2.microsoft.com/en-us/library/aa964126.aspx#sqldocum_topic7
). If exporting to PDF programmatically is an option, I would suggest
using the open source library iTextSharp (http://sourceforge.net/
projects/itextsharp/ ). Crystal Reports could be setup via a report
viewer control and cr.exe to programmatically export a report to text
via an application. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Placing data field on the report Header

I have the problem palcing the field from my Stored proc on the header.
Depending on the user name, I have to dynamically change the department name
that comes from the stored proc.
But, RS is not allowing me to put any expression (with fields) on header.
Any ideas how to do that ?I think the solution to this is to place the data into a field within the
body of the report, then set the hidden property to true. You can then set
the value of the Header textbox "=ReportItems!Fieldname.Value" This is
because you cannot reference datasets within the header or footer, they are
out of scope.
"Sujay" <Sujay@.discussions.microsoft.com> wrote in message
news:F18343A1-29A9-4BC8-B877-EB74B96495BB@.microsoft.com...
>I have the problem palcing the field from my Stored proc on the header.
> Depending on the user name, I have to dynamically change the department
> name
> that comes from the stored proc.
> But, RS is not allowing me to put any expression (with fields) on header.
> Any ideas how to do that ?|||Sujay,
I am actually doing this. I have a 2 stored procs (header and details).
These are separate datasets. In the table header you can put a dataset value
into it. For example, =First(Fields!Header1.Value, "HeaderDataSet")
HTH
"Sujay" wrote:
> I have the problem palcing the field from my Stored proc on the header.
> Depending on the user name, I have to dynamically change the department name
> that comes from the stored proc.
> But, RS is not allowing me to put any expression (with fields) on header.
> Any ideas how to do that ?|||Rich,
I did exactly as you said. But, since the Body of the field value is being
displayed on the first page only, even the header text box is visible only on
first page.
It displays blank on other pages.
If I place a field value at the bottom of Body section then, I see header
only on the last page.
So, even that doesn't seem to be working.
I haven't tried Neo's solution yet.
"Rich Millman" wrote:
> I think the solution to this is to place the data into a field within the
> body of the report, then set the hidden property to true. You can then set
> the value of the Header textbox "=ReportItems!Fieldname.Value" This is
> because you cannot reference datasets within the header or footer, they are
> out of scope.
> "Sujay" <Sujay@.discussions.microsoft.com> wrote in message
> news:F18343A1-29A9-4BC8-B877-EB74B96495BB@.microsoft.com...
> >I have the problem palcing the field from my Stored proc on the header.
> > Depending on the user name, I have to dynamically change the department
> > name
> > that comes from the stored proc.
> > But, RS is not allowing me to put any expression (with fields) on header.
> > Any ideas how to do that ?
>
>|||Neo,
It is not allowing me to use =First(Fields!Header1.Value, "HeaderDataSet")
as I cannot define any fields in Header section.
What I have done is :
*Created 2 datasets; Header and detail
*Placing a text box in Header section and value of text box is :
First(Fields!Header1.Value, "HeaderDataSet")
*Other dataset fields are witin the Body section placed in the report.
But, when I preview this report it says, cannot place dataset fields in
Header or Footer.
Let me know what am I doing wrong!!!
"Neo" wrote:
> Sujay,
> I am actually doing this. I have a 2 stored procs (header and details).
> These are separate datasets. In the table header you can put a dataset value
> into it. For example, =First(Fields!Header1.Value, "HeaderDataSet")
> HTH
> "Sujay" wrote:
> > I have the problem palcing the field from my Stored proc on the header.
> > Depending on the user name, I have to dynamically change the department name
> > that comes from the stored proc.
> > But, RS is not allowing me to put any expression (with fields) on header.
> > Any ideas how to do that ?

Placing a sum in a table header column

I have been requested to add the sum of an interger field to the table header. I have the sum in the footer (which is very easy to do), but I cannot get the sum to appear in the table header.

I then set-up the stored procedure to run the sum, and place it into a dummy field. I still cannot add this field to the table header. Instead of printing the data for the dummy field (the correct total), it instead prints the actual field name on the report.

Is there anyway to place a sum in a table header on a SQL Server Report?

Let me know.

Thank you,

T.J.

You should be able to just copy the code that you're using in the sum field in your table footer into a textbox in the table header.

Jarret

|||

Thank you very much. I got busy and didn't get time to get back to this yesterday.

I tested this today, and if I just drop the field into a blank header cell, the number displays correctly.

But when I place this field in with text (such as "# of Boxes" and then add in the summed field, it displays the text of the field name, rather than the value (the number).

Very strange. When mixed with text, it displays the field name rather than the actual value.

Is there anyway to work around this?

Thank you,

T.J.

|||

Can you post your expression you have in your textbox?

You should be able to use something like this:

="# of Boxes - " & count(Fields!boxes.Value)

Jarret

|||

Yes, and thank you...

"# of Boxes - " & Sum(Fields_CountBoxes.Value)

When I preview this, it displays exactly like my formula.

Where if I just drop Sum(Fields_CountBoxes.Value) into the column header, it gives just the summed number I want to display.

|||

You are missing the '='. Try putting this into your textbox:

="# of Boxes - " & Sum(Fields_CountBoxes.Value)

If you don't have the '=' at the front, it takes whatever you have in there as literal. The '=' makes it an expression.

Jarret

|||

Oh, what an over sight.

Thank you very much, that fixed my error!

|||

Glad I could help TJ. Can you mark this one as answered so others can see this solution?

Thanks.

Jarret

placeholder for uniqueidentifier

Hi,
I need help with a SQL SELECT statement. I am using a UNION to merge four
tables together. For the tables that did not include a particular field, I
have used a placeholder of 'N/A' in the SELECT statement as follows:
SELECT organizationid, organization_name, businessunitid,
business_unit_name, subdivision_id, subdivision_name, lot_no, plan_number,
plan_name, current_retail_price, current_expiration_date,
plan_availability_pricing_id, plan_id
FROM hb_view_lot_plan
UNION
SELECT organizationid, organization_name, businessunitid,
business_unit_name, subdivision_id, subdivision_name, 'N/A', plan_number,
plan_name, current_retail_price, current_expiration_date,
plan_availability_pricing_id, plan_id
FROM hb_view_subdivision_plan
UNION
SELECT organizationid, organization_name, businessunitid,
business_unit_name, 'N/A', 'N/A', 'N/A', plan_number, plan_name,
current_retail_price, current_expiration_date, plan_availability_pricing_id,
plan_id
FROM hb_view_business_unit_plan
UNION
SELECT organizationid, organization_name, businessunitid, 'N/A', 'N/A',
'N/A', 'N/A', plan_number, plan_name, current_retail_price,
current_expiration_date, plan_availability_pricing_id, plan_id
FROM hb_view_organization_plan
I am having a problem with using the 'N/A' placeholder in lieu of a
uniqueidentifier. It works for varchar field types but apparently not
uniqueidentifier type fields. I receive the error message "error converting
from character string into uniqueidentifier". Thanks for your help.
DonYou could cast the top uniqueidentifier to a varchar(36) and it will work.
It seems a bit wierd though, because are you going to display the guid to
the user? Pretty hideous if you are.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
<dbj> wrote in message news:uwbfXluPFHA.2136@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I need help with a SQL SELECT statement. I am using a UNION to merge four
> tables together. For the tables that did not include a particular field,
> I have used a placeholder of 'N/A' in the SELECT statement as follows:
>
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, lot_no, plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_lot_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, 'N/A', plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_subdivision_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, 'N/A', 'N/A', 'N/A', plan_number, plan_name,
> current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_business_unit_plan
> UNION
> SELECT organizationid, organization_name, businessunitid, 'N/A',
> 'N/A', 'N/A', 'N/A', plan_number, plan_name, current_retail_price,
> current_expiration_date, plan_availability_pricing_id, plan_id
> FROM hb_view_organization_plan
>
> I am having a problem with using the 'N/A' placeholder in lieu of a
> uniqueidentifier. It works for varchar field types but apparently not
> uniqueidentifier type fields. I receive the error message "error
> converting from character string into uniqueidentifier". Thanks for your
> help.
>
> Don
>
>|||When you use union, first query defines column data types for all other
queries.
So, the problem is (probably, you did not provide DDl, so I cannot tell for
sure) in 3th and/or 4th query, where you set 'N/A' for subdivision_id which
can be of GUID data type.
Solution can be to replace 'N/A' with "empty" guid
'{00000000-0000-0000-0000-000000000000}' or with null.
Regards,
Marko Simic
"dbj" wrote:

> Hi,
> I need help with a SQL SELECT statement. I am using a UNION to merge four
> tables together. For the tables that did not include a particular field,
I
> have used a placeholder of 'N/A' in the SELECT statement as follows:
>
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, lot_no, plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_lot_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, 'N/A', plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_subdivision_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, 'N/A', 'N/A', 'N/A', plan_number, plan_name,
> current_retail_price, current_expiration_date, plan_availability_pricing_i
d,
> plan_id
> FROM hb_view_business_unit_plan
> UNION
> SELECT organizationid, organization_name, businessunitid, 'N/A', 'N/A'
,
> 'N/A', 'N/A', plan_number, plan_name, current_retail_price,
> current_expiration_date, plan_availability_pricing_id, plan_id
> FROM hb_view_organization_plan
>
> I am having a problem with using the 'N/A' placeholder in lieu of a
> uniqueidentifier. It works for varchar field types but apparently not
> uniqueidentifier type fields. I receive the error message "error converti
ng
> from character string into uniqueidentifier". Thanks for your help.
>
> Don
>
>
>|||Sorry I forgot to write the most important part :)
SQL try to convert 'N/A' to uniqueidentifier data type which is not
possible. you may try this to see what will happen:
select newid()
union
select 'N/A'
Regards,
Marko Simic
"dbj" wrote:

> Hi,
> I need help with a SQL SELECT statement. I am using a UNION to merge four
> tables together. For the tables that did not include a particular field,
I
> have used a placeholder of 'N/A' in the SELECT statement as follows:
>
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, lot_no, plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_lot_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, 'N/A', plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_subdivision_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, 'N/A', 'N/A', 'N/A', plan_number, plan_name,
> current_retail_price, current_expiration_date, plan_availability_pricing_i
d,
> plan_id
> FROM hb_view_business_unit_plan
> UNION
> SELECT organizationid, organization_name, businessunitid, 'N/A', 'N/A'
,
> 'N/A', 'N/A', plan_number, plan_name, current_retail_price,
> current_expiration_date, plan_availability_pricing_id, plan_id
> FROM hb_view_organization_plan
>
> I am having a problem with using the 'N/A' placeholder in lieu of a
> uniqueidentifier. It works for varchar field types but apparently not
> uniqueidentifier type fields. I receive the error message "error converti
ng
> from character string into uniqueidentifier". Thanks for your help.
>
> Don
>
>
>|||Oops, no it won't (d'oh!) you will have to cast them all to char:
select cast( 'na' as varchar(36))
union
select cast(newId() as varchar(36))
Sorry,
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:u%238on1uPFHA.532@.TK2MSFTNGP09.phx.gbl...
> You could cast the top uniqueidentifier to a varchar(36) and it will work.
> It seems a bit wierd though, because are you going to display the guid to
> the user? Pretty hideous if you are.
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> <dbj> wrote in message news:uwbfXluPFHA.2136@.TK2MSFTNGP14.phx.gbl...
>|||Marko,
Thank you very much. Your answer worked on the first try.
Don
"Simic Marko" <SimicMarko@.discussions.microsoft.com> wrote in message
news:CA618990-5DFD-4753-B3CC-13FA859F136C@.microsoft.com...
> When you use union, first query defines column data types for all other
> queries.
> So, the problem is (probably, you did not provide DDl, so I cannot tell
> for
> sure) in 3th and/or 4th query, where you set 'N/A' for subdivision_id
> which
> can be of GUID data type.
> Solution can be to replace 'N/A' with "empty" guid
> '{00000000-0000-0000-0000-000000000000}' or with null.
> Regards,
> Marko Simic
> "dbj" wrote:
>

PK/UQ dumb question?

If there's a primary key constraint on a field, is it redundant to have a
unique key constraint on that same field?You may still want a unique constraint on the same table, for example
primary key on Customer_ID and unique key on SocialSecurityNumber, but there
is no need for a unique key constraint on the primary key column.
"Paul Pedersen" <no-reply@.swen.com> wrote in message
news:erqfdQXXFHA.1044@.TK2MSFTNGP10.phx.gbl...
> If there's a primary key constraint on a field, is it redundant to have a
> unique key constraint on that same field?
>
>|||Hi,
SQL Server will not allow to keep Primary key and Unique constraint on the
same field.
Thanks
Hari
SQL Server MVP
"Paul Pedersen" <no-reply@.swen.com> wrote in message
news:erqfdQXXFHA.1044@.TK2MSFTNGP10.phx.gbl...
> If there's a primary key constraint on a field, is it redundant to have a
> unique key constraint on that same field?
>
>|||Thanks!
"JT" <someone@.microsoft.com> wrote in message
news:uQpn1TXXFHA.3840@.tk2msftngp13.phx.gbl...
> You may still want a unique constraint on the same table, for example
> primary key on Customer_ID and unique key on SocialSecurityNumber, but
> there
> is no need for a unique key constraint on the primary key column.
> "Paul Pedersen" <no-reply@.swen.com> wrote in message
> news:erqfdQXXFHA.1044@.TK2MSFTNGP10.phx.gbl...
>|||Thanks for your reponse. It seems like it can be done, though.
create table test (testfld int not null)
alter table test add constraint pk_test primary key (testfld)
alter table test add constraint uq_test unique (testfld)
"Hari Pra" <hari_pra_k@.hotmail.com> wrote in message
news:OyZaPWXXFHA.3488@.tk2msftngp13.phx.gbl...
> Hi,
> SQL Server will not allow to keep Primary key and Unique constraint on the
> same field.
> Thanks
> Hari
> SQL Server MVP
>
> "Paul Pedersen" <no-reply@.swen.com> wrote in message
> news:erqfdQXXFHA.1044@.TK2MSFTNGP10.phx.gbl...
>|||Yes, it is redundant because a pk by definition have to be unique and not
null. When sql server check for integrity, it will give preference to pk.
use northwind
go
create table t1 (
c1 int not null,
constraint pk_t1 primary key (c1),
constraint u_t1_c1 unique (c1)
)
go
select
*
from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where
table_name = 't1'
go
insert into t1 values(1)
go
-- this will give an error because of the pk constraint
insert into t1 values(1)
go
drop table t1
go
AMB
"Paul Pedersen" wrote:

> If there's a primary key constraint on a field, is it redundant to have a
> unique key constraint on that same field?
>
>
>|||I accepted. If there is primary key then the Unique constraint is redundant.
Thanks
Hari
"Paul Pedersen" <no-reply@.swen.com> wrote in message
news:OBibncXXFHA.2796@.TK2MSFTNGP09.phx.gbl...
> Thanks for your reponse. It seems like it can be done, though.
>
> create table test (testfld int not null)
> alter table test add constraint pk_test primary key (testfld)
> alter table test add constraint uq_test unique (testfld)
>
>
> "Hari Pra" <hari_pra_k@.hotmail.com> wrote in message
> news:OyZaPWXXFHA.3488@.tk2msftngp13.phx.gbl...
>|||Thank you for your help.
"Hari Pra" <hari_pra_k@.hotmail.com> wrote in message
news:eyTSShXXFHA.796@.TK2MSFTNGP09.phx.gbl...
>I accepted. If there is primary key then the Unique constraint is
>redundant.
> Thanks
> Hari
> "Paul Pedersen" <no-reply@.swen.com> wrote in message
> news:OBibncXXFHA.2796@.TK2MSFTNGP09.phx.gbl...
>|||Thank you.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:7EAC399A-D99C-4578-B34B-82967C169247@.microsoft.com...
> Yes, it is redundant because a pk by definition have to be unique and not
> null. When sql server check for integrity, it will give preference to pk.
> use northwind
> go
> create table t1 (
> c1 int not null,
> constraint pk_t1 primary key (c1),
> constraint u_t1_c1 unique (c1)
> )
> go
> select
> *
> from
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> where
> table_name = 't1'
> go
> insert into t1 values(1)
> go
> -- this will give an error because of the pk constraint
> insert into t1 values(1)
> go
> drop table t1
> go
>
> AMB
>
> "Paul Pedersen" wrote:
>

PK field generate by SQL server using Bulkload

Hi, I'm a bit of Bulkload newbie.
I would like to import XML files into my SQL2k table,
but PK field (identity-yes field) in this table has to be
generated by SQL server.
What have I to write in my XSD file for this PK field?
Thanks for guru :-)Just make sure that bulkload has KeepIdentity=False set on the API, that
should do it.
Irwina
"Gen" <anonymous@.discussions.microsoft.com> wrote in message
news:061301c52a47$36077470$a501280a@.phx.gbl...
> Hi, I'm a bit of Bulkload newbie.
> I would like to import XML files into my SQL2k table,
> but PK field (identity-yes field) in this table has to be
> generated by SQL server.
> What have I to write in my XSD file for this PK field?
> Thanks for guru :-)|||It works!
Thank you so much

>--Original Message--
>Just make sure that bulkload has KeepIdentity=False set
on the API, that
>should do it.
>Irwina
>"Gen" <anonymous@.discussions.microsoft.com> wrote in
message
>news:061301c52a47$36077470$a501280a@.phx.gbl...
be
>
>.
>

PK field generate by SQL server using Bulkload

Hi, I'm a bit of Bulkload newbie.
I would like to import XML files into my SQL2k table,
but PK field (identity-yes field) in this table has to be
generated by SQL server.
What have I to write in my XSD file for this PK field?
Thanks for guru :-)
Just make sure that bulkload has KeepIdentity=False set on the API, that
should do it.
Irwina
"Gen" <anonymous@.discussions.microsoft.com> wrote in message
news:061301c52a47$36077470$a501280a@.phx.gbl...
> Hi, I'm a bit of Bulkload newbie.
> I would like to import XML files into my SQL2k table,
> but PK field (identity-yes field) in this table has to be
> generated by SQL server.
> What have I to write in my XSD file for this PK field?
> Thanks for guru :-)
|||It works!
Thank you so much

>--Original Message--
>Just make sure that bulkload has KeepIdentity=False set
on the API, that
>should do it.
>Irwina
>"Gen" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:061301c52a47$36077470$a501280a@.phx.gbl...
be
>
>.
>

PK column

I need to make is to my users can not accidentally delete or replace the pK field, how do I do that anyone got any suggestions??Ummmm..huh :confused:

USE Northwind
GO

CREATE TABLE myTable99(Col1 int PRIMARY KEY)
GO

INSERT INTO myTable99(Col1) SELECT 1 UNION ALL SELECT 2
GO

SELECT * FROM myTable99
GO

DELETE FROM myTable99 WHERE Col1 = 1
GO

SELECT * FROM myTable99
GO

UPDATE myTable99 SET Col1 = 1 WHERE Col1 = 2
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||Ummmm..huh :confused:Yeah, what Brett said.

-PatP|||use pubs
go
deny update, delete on authors (au_id) to public
go|||thank you so much guys I appreciate it :). How do you donate to this website anyways??|||I explained that incorrectly what I am trying to say is I dont want my users to delete, or replace the data in the primary key field. Does that Make sense?|||Then this will suffice:

deny update on <your_table>(<your_primary_key>) to <your_users_or_public>

PivotTable Total from Database

hi,
i'm trying to create a PivotTable in which I will be able to see totals coming from the database, not a calculated total field performed by the PivotTable. The data are structured with a hierarchical dimension for wich values are existing for each low level detail item and also for the high level detail item.
Is it possible to create a PivotTable based on a ROLAP(with SQL query) access that provide this kind of result (with total from the database).
For example, with the following table of data, I want to produced a PivotTable with 2 columns (E1Dim2 and E2Dim2 values) and 2 rows (E1Dim1 and E2Dim1 values). The PivotTable values are 100 for each dimension elements combinaison (the totals from database). Sure, I need to be able to drill one values on the row axis to see E11, E12 and E13 detail values.

id_meas1 val1_meas1 val2_meas1 des_dim1 des_dim11 des_dim2 1 10.23445 12.23445 E1Dim1 E11 E1Dim2 2 100 100 E1Dim1 E1Dim2 3 12.43356 13.34424 E1Dim1 E12 E1Dim2 4 14.34455 12.34234 E1Dim1 E13 E1Dim2 5 9.23423 8.53464 E1Dim1 E11 E2Dim2 6 100 100 E1Dim1 E2Dim2 7 10.23452 9.43234 E1Dim1 E12 E2Dim2 8 14.4532 12.3423 E1Dim1 E13 E2Dim2 9 8.23423 12.53464 E2Dim1 E11 E1Dim2 10 100 100 E2Dim1 E1Dim2 11 9.23452 14.43234 E2Dim1 E12 E1Dim2 12 12.4532 11.3423 E2Dim1 E13 E1Dim2 13 12.23423 13.53464 E2Dim1 E11 E2Dim2 14 100 100 E2Dim1 E2Dim2 15 10.23452 10.43234 E2Dim1 E12 E2Dim2 16 11.4532 12.3423 E2Dim1 E13 E2Dim2

Is it possible to get this behavior with ROLAP access?
Jeff

ROLAP is only storage mode you choose for your dimensions and partitions.

You should be able to change storage mode to ROLAP and continue to work as you were in MOLAP. The data you reciveing and the way you receive it should be the same.

Word of caution here:
If you are operating with large amounts of data you might get slower performance for your ROLAP dimensions and partitions.
Also during processing of MOLAP dimensions and partitions Analysis Server hides some of referential integrity problems you might have in relational database. Switching to ROLAP might surface these problems.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||my questionning is about how can I display TOTAL VALUES comming from database, not totals calculated by the pivottable?
I'm trying to create a pivottable in wich the detail and total values providing from database and not calculated by the pivottable. And I have a technological constraint, is to using MS-Access database, thus using SQLQuery and not MDX.

Is it possible to present detail values associate with the lower items level dimension and the total values associate with the higher items level dimension, without using total in the pivottable?

|||

Using Excel you can acomplish a lot. I am sure you should be able to find a solution for your problem.

This is probably a wrong forum for you question. Try and post it on the microsoft.public.excel newsgroup.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Pivot Table Woes

I am very confused here. I really hope someone can help.

I have a table that contains "virtual fields" (ie. a column for field name and a column for field value). What I'd like is a pivot table that has the field names across the top and the field values as the row. I found the PivotTable service, but that seems like quite a bit of work for something that you can do in Access in a few clicks. I also know the SUM/CASE method, but unfortunately the virtual fields are dynamic, and I don't know what they could be named, nor how many of them exist. Does anybody have any ideas of what I can do? I'll include an example below to clear up any confusion.

Thanks!

What I have:

field_name | field_value

----------------

car_manufacturer | Jaguar

car_model | XJR

car_horsepower | 390

car_manufactuer | Ford

car_model | Mustang GT

car_horsepower | 400

What I want:

car_manufacturer | car_model | car_horsepower

------------------------------

Jaguar | XJR | 390

Ford | Mustang GT | 400Complete a primary key

car_manufacturer | Jaguar
car_manufactuer | Ford
car_model | XJR
car_model | Mustang GT
car_horsepower | 400
car_horsepower | 390

1 | car_manufacturer | Jaguar
1 | car_model | XJR
1 | car_horsepower | 390
2 | car_manufactuer | Ford
2 | car_model | Mustang GT
2 | car_horsepower | 400

and add an ordering table of value names

1 | car_manufacturer
2 | car_model
3 | car_horsepower|||create table dbo.ValuesTable(GroupId int,ValueName varchar(50),Value varchar(100) not null,primary key (GroupID,ValueName))
GO
insert dbo.ValuesTable (GroupId,ValueName,Value) values (1,'car_manufacturer','Jaguar')
insert dbo.ValuesTable (GroupId,ValueName,Value) values (1,'car_model','XJR')
insert dbo.ValuesTable (GroupId,ValueName,Value) values (1,'car_horsepower','390')
insert dbo.ValuesTable (GroupId,ValueName,Value) values (2,'car_manufacturer','Ford')
insert dbo.ValuesTable (GroupId,ValueName,Value) values (2,'car_model','Mustang GT')
insert dbo.ValuesTable (GroupId,ValueName,Value) values (2,'car_horsepower','400')
GO

--without ordering
create function dbo.ufn_ConcatOrderingDistinct()
returns varchar(8000)
with schemabinding
as
begin
declare @.Res varchar(8000)
select @.Res = isnull(@.Res+',','')+'"'+ValueName+'"=max(case when d.ValueName = '''+ValueName+''''+' then d.Value end)'
from
(
select distinct ValueName
from dbo.ValuesTable
) x
return @.Res
end
GO
declare @.Exec varchar(8000)
set @.Exec='select '+dbo.ufn_ConcatOrderingDistinct()+'from dbo.ValuesTable d group by d.GroupId'
exec(@.Exec)
GO

--with ordering
create table dbo.OrderingTable(OrderId int primary key,ValueName varchar(50) not null)
GO
insert dbo.OrderingTable(OrderId,ValueName) values (1,'car_manufacturer')
insert dbo.OrderingTable(OrderId,ValueName) values (2,'car_model')
insert dbo.OrderingTable(OrderId,ValueName) values (3,'car_horsepower' )
GO
create function dbo.ufn_ConcatOrderingTable()
returns varchar(8000)
with schemabinding
as
begin
declare @.Res varchar(8000)
select @.Res = isnull(@.Res+',','')+'"'+ValueName+'"=max(case when d.ValueName = '''+ValueName+''''+' then d.Value end)'
from dbo.OrderingTable
order by OrderId
return @.Res
end
GO
declare @.Exec varchar(8000)
set @.Exec='select '+dbo.ufn_ConcatOrderingTable()+'from dbo.ValuesTable d group by d.GroupId'
exec(@.Exec)
GO|||create table #a (id int, fld varchar(40), val varchar(20))
insert #a select 1, 'car_manufacturer', 'Jaguar'
insert #a select 1, 'car_model', 'XJR'
insert #a select 1, 'car_horsepower', '390'
insert #a select 2, 'car_manufacturer', 'Ford'
insert #a select 2, 'car_model', 'Mustang GT'
insert #a select 2, 'car_horsepower', '400'
insert #a select 2, 'Doors', '4'

declare @.sql varchar(8000)

select @.sql = coalesce(@.sql+',','') + fld + ' = (select val from #a a1 where a1.fld = ''' + fld + ''' and a1.id = #a.id)'
from (select distinct fld from #a) as a
exec ('select id,' + @.sql+ ' from #a group by id')

drop table #a

gives
id car_horsepower car_manufacturer car_model Doors
---- ------- ------- ------- -------
1 390 Jaguar XJR NULL
2 400 Ford Mustang GT 4

You will have to split up the string if you have too many fields to fit in 8000 chars.

Pivot table Conditional calculation field

Hello All

I am using owc pivot table as a control to display the data in cubes.

Now i have a requirement to apply the formula (calculated field) for all records except few.

How to solve this scenario.

Please Help

Thanks

I have written this query and this query returns me the same results as i expected except the Grand Total at bottom which show the same value as of old grand total.

"WITH MEMBER [Measures].[Q1] AS '[Measures].[BY] * IIf([Center].CurrentMember.Name = ""Apple Research Center"", 1.2, 1) ' SELECT {[Measures].[Q1],[Measures].[Prior],[Measures].[CY]} ON COLUMNS, [Center].[Center].Members ON ROWS FROM Demo"

Please Help

Anil

|||

I am still in need of a solution for this question

please help!!!

|||Does any body come accross this situation?|||

Hi Anil,

Do you mean, "all records", or "all members"? If it's the latter, then an MDX formula is best, using the IF function. If it's the former, then use a function in the fact table.

Could you tell me the structure of your cube, in terms of measures and dimensions, and then maybe I can help you with a MDX formula.

Cheers,

Jerome

|||

Jerome,

Thanks for your response.

I meant all records.

I am new to this analysis service.

Could you please help me out.

Thanks

|||

Hi Anil,

Sorry I took so long. If you need to apply a function to some of the records of the fact table, you can create a function on the SQL Server which applies the formula according to a condition:

CREATE FUNCTION <Function Name>

RETURNS <Data Type> AS

IF <Condition>

RETURN <X>

ELSE

RETURN <Y>

Then use this function in a view based on the fact table, and either use the view to populate a new version of the fact table, or else base the cube on the view.

I hope this helps. Now, I'll be able to help you better if you give me more specific information:

1. Structure of the Fact Table

2. Measures of the Cube

3. Dimensions

4. Condition for which you want to display rows from the fact table.

I look forward to hearing from you.

Cheers,

Jerome

|||

Jerome,

Thank you for your response.

I implemented this but the total is displaying the old value only.

Pivot table Conditional calculation field

Hello All

I am using owc pivot table as a control to display the data in cubes.

Now i have a requirement to apply the formula (calculated field) for all records except few.

How to solve this scenario.

Please Help

Thanks

I have written this query and this query returns me the same results as i expected except the Grand Total at bottom which show the same value as of old grand total.

"WITH MEMBER [Measures].[Q1] AS '[Measures].[BY] * IIf([Center].CurrentMember.Name = ""Apple Research Center"", 1.2, 1) ' SELECT {[Measures].[Q1],[Measures].[Prior],[Measures].[CY]} ON COLUMNS, [Center].[Center].Members ON ROWS FROM Demo"

Please Help

Anil

|||

I am still in need of a solution for this question

please help!!!

|||Does any body come accross this situation?|||

Hi Anil,

Do you mean, "all records", or "all members"? If it's the latter, then an MDX formula is best, using the IF function. If it's the former, then use a function in the fact table.

Could you tell me the structure of your cube, in terms of measures and dimensions, and then maybe I can help you with a MDX formula.

Cheers,

Jerome

|||

Jerome,

Thanks for your response.

I meant all records.

I am new to this analysis service.

Could you please help me out.

Thanks

|||

Hi Anil,

Sorry I took so long. If you need to apply a function to some of the records of the fact table, you can create a function on the SQL Server which applies the formula according to a condition:

CREATE FUNCTION <Function Name>

RETURNS <Data Type> AS

IF <Condition>

RETURN <X>

ELSE

RETURN <Y>

Then use this function in a view based on the fact table, and either use the view to populate a new version of the fact table, or else base the cube on the view.

I hope this helps. Now, I'll be able to help you better if you give me more specific information:

1. Structure of the Fact Table

2. Measures of the Cube

3. Dimensions

4. Condition for which you want to display rows from the fact table.

I look forward to hearing from you.

Cheers,

Jerome

|||

Jerome,

Thank you for your response.

I implemented this but the total is displaying the old value only.

Pivot Example when you don't know the exact values to Pivot on

Say, I have the following temporary table (@.tbl) where the QuestionID field will change values over time

Survey QuestionID Answer
1 1 1
1 2 0
2 1 1
2 2 2

I'd like to perform a pivot on it like this: select * from @.tbl Pivot (min(Answer) for QuestionID in ([1], [2])) as PivotTable

...however, I can't just name the [1], [2] values because they're going to change.

Instead of naming the values like this:
for QuestionID in ([1], [2], [3], [4])

I tried something like this:
for QuestionID in (select distinct QuestionID from @.tbl)

but am getting a syntax error. Is it possible to set up a pivot like this:
select * from @.tbl Pivot (min(Answer) for Question_CID in (select distinct @.QuestionID from @.tbl)) as PivotTable

or does anyone know another way to do it?

You cannot use a subquery in the IN for your pivot. I wish we could. Here is a dynamic solution:

Code Snippet

Create Table #tb1 (

Survey int,

QuestionID int,

Answer int

);

--Survey QuestionID Answer

--1 1 1

--1 2 0

--2 1 1

--2 2 2

--Sample Data

Insert Into #tb1 Values('1','1', '1');

Insert Into #tb1 Values('1','2', '0');

Insert Into #tb1 Values('2','1', '1');

Insert Into #tb1 Values('2','2', '2');

Insert Into #tb1 Values('3','3', '1');

--SELECT DISTINCT QuestionID FROM #tb1

SET

NOCOUNT ON

DECLARE

@.T AS TABLE(y nvarchar(20) NOT NULL PRIMARY KEY)

INSERT

INTO @.T SELECT DISTINCT QuestionID FROM #tb1

DECLARE

@.T1 AS TABLE(num int NOT NULL PRIMARY KEY)

DECLARE @.i AS int

SET @.i=1

WHILE @.i <20

BEGIN

INSERT INTO @.T1 SELECT @.i

SET @.i=@.i+1

END

DECLARE @.cols AS nvarchar(MAX), @.cols2 AS nvarchar(MAX),@.y AS nvarchar(20)

SET @.y = (SELECT MIN(y) FROM @.T)

SET @.cols = N''

SET @.cols2 = N''

WHILE @.y IS NOT NULL

BEGIN

SET @.cols = @.cols + N',['+CAST(@.y AS nvarchar(20))+N']'

SET @.cols2 = @.cols2 + N'+ coalesce(['+CAST(@.y AS nvarchar(20))+N'],'''')'

SET @.y = (SELECT MIN(y) FROM @.T WHERE y > @.y)

END

SET @.cols = SUBSTRING(@.cols, 2, LEN(@.cols))

SET @.cols2 = SUBSTRING(@.cols2, 2, LEN(@.cols2)-1)

DECLARE @.sql AS nvarchar(MAX)

SET @.sql = N'SELECT Survey, ' +@.cols + N' FROM (SELECT Survey, QuestionID, Answer FROM #tb1) as t

PIVOT (Min(Answer) FOR QuestionID IN(' + @.cols + N')) AS pvt'

EXEC sp_executesql @.sql

print @.sql

drop table #tb1

|||

Try:

Code Snippet

use tempdb

go

create table #t1 (

survey int,

questionid int,

answer int

)

insert into #t1 values(1,1, 1)

insert into #t1 values(1,2, 0)

insert into #t1 values(2,1, 1)

insert into #t1 values(2,2, 2)

insert into #t1 values(0,0, 1)

declare @.cols nvarchar(max)

declare @.sql nvarchar(max)

set @.cols = stuff(

(

select ',' + quotename(QuestionID)

from (select distinct QuestionID from #t1) as t

order by QuestionID

for xml path('')

), 1, 1, '')

set @.sql = N'

select

Survey, ' + @.cols + N'

from

#t1

pivot

(

min(Answer)

for QuestionID in (' + @.cols + N')

) as pvt

'

exec sp_executesql @.sql

drop table #t1

go

In case you do not want to see the NULL values, take a look to this post. There I posted a similar solution.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2123214&SiteID=1

Be careful with SQL Injection.

PIVOT on Steroids

http://www.sqlmag.com/Article/ArticleID/94268/sql_server_94268.html

AMB