Data fields in the header

I'm getting this error when I try to preview my report: The value expression
for the textbox 'RepairOrderID' refers to a field. Fields cannot be used in
page headers or footers.
I searched through the news groups and a found suggestion to replace:
Fields!RepairOrderID.Value
with
ReportItems!RepairOrderID.Value
I tried this, it compiles now but when the report renders "#Error" displays
where the RepairOrderID should appear. I found another suggestion to use the
First function this does not work either...
Can someone please help....?
Thanks!!!
DanLooks like your textbox is referring to itself. You need to refer to a
textbox from report body.
More reliable solution is to create read-only parameter with default value
from query and then to use this parameter in the page header textbox.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote
in message news:OMQtyN0eEHA.2560@.TK2MSFTNGP09.phx.gbl...
> I'm getting this error when I try to preview my report: The value
> expression
> for the textbox 'RepairOrderID' refers to a field. Fields cannot be used
> in
> page headers or footers.
> I searched through the news groups and a found suggestion to replace:
> Fields!RepairOrderID.Value
> with
> ReportItems!RepairOrderID.Value
> I tried this, it compiles now but when the report renders "#Error"
> displays
> where the RepairOrderID should appear. I found another suggestion to use
> the
> First function this does not work either...
> Can someone please help....?
> Thanks!!!
> Dan
>|||I don't think my textbox is referring to its self... The name of the textbox
is textbox18, besides I'm explicitly qualifying the Value property with
Fields! or ReportItems!. How do I create a read-only parameter from
query...? I know this does not work.. SELECT @.PARAM = Value FROM Table
Thanks,
Dan
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:%23N$kB50eEHA.3632@.TK2MSFTNGP11.phx.gbl...
> Looks like your textbox is referring to itself. You need to refer to a
> textbox from report body.
> More reliable solution is to create read-only parameter with default value
> from query and then to use this parameter in the page header textbox.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion>
wrote
> in message news:OMQtyN0eEHA.2560@.TK2MSFTNGP09.phx.gbl...
> > I'm getting this error when I try to preview my report: The value
> > expression
> > for the textbox 'RepairOrderID' refers to a field. Fields cannot be used
> > in
> > page headers or footers.
> >
> > I searched through the news groups and a found suggestion to replace:
> >
> > Fields!RepairOrderID.Value
> > with
> > ReportItems!RepairOrderID.Value
> >
> > I tried this, it compiles now but when the report renders "#Error"
> > displays
> > where the RepairOrderID should appear. I found another suggestion to use
> > the
> > First function this does not work either...
> >
> > Can someone please help....?
> >
> > Thanks!!!
> >
> > Dan
> >
> >
>|||http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_interactive_v1_50fn.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rshowto/htm/hrs_designer_v1_38du.asp
Create a parameter without Prompt , set its default value to From Query,
select your dataset and ReportOrderID as value field.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote
in message news:ev1O838eEHA.1100@.TK2MSFTNGP10.phx.gbl...
>I don't think my textbox is referring to its self... The name of the
>textbox
> is textbox18, besides I'm explicitly qualifying the Value property with
> Fields! or ReportItems!. How do I create a read-only parameter from
> query...? I know this does not work.. SELECT @.PARAM = Value FROM Table
> Thanks,
> Dan
> "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> news:%23N$kB50eEHA.3632@.TK2MSFTNGP11.phx.gbl...
>> Looks like your textbox is referring to itself. You need to refer to a
>> textbox from report body.
>> More reliable solution is to create read-only parameter with default
>> value
>> from query and then to use this parameter in the page header textbox.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion>
> wrote
>> in message news:OMQtyN0eEHA.2560@.TK2MSFTNGP09.phx.gbl...
>> > I'm getting this error when I try to preview my report: The value
>> > expression
>> > for the textbox 'RepairOrderID' refers to a field. Fields cannot be
>> > used
>> > in
>> > page headers or footers.
>> >
>> > I searched through the news groups and a found suggestion to replace:
>> >
>> > Fields!RepairOrderID.Value
>> > with
>> > ReportItems!RepairOrderID.Value
>> >
>> > I tried this, it compiles now but when the report renders "#Error"
>> > displays
>> > where the RepairOrderID should appear. I found another suggestion to
>> > use
>> > the
>> > First function this does not work either...
>> >
>> > Can someone please help....?
>> >
>> > Thanks!!!
>> >
>> > Dan
>> >
>> >
>>
>|||Excellent!!... That's what I needed... Thanks..:)
Dan
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:udrYUvEfEHA.4092@.TK2MSFTNGP10.phx.gbl...
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_interactive_v1_50fn.asp
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rshowto/htm/hrs_designer_v1_38du.asp
> Create a parameter without Prompt , set its default value to From Query,
> select your dataset and ReportOrderID as value field.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion>
wrote
> in message news:ev1O838eEHA.1100@.TK2MSFTNGP10.phx.gbl...
> >I don't think my textbox is referring to its self... The name of the
> >textbox
> > is textbox18, besides I'm explicitly qualifying the Value property with
> > Fields! or ReportItems!. How do I create a read-only parameter from
> > query...? I know this does not work.. SELECT @.PARAM = Value FROM Table
> >
> > Thanks,
> >
> > Dan
> >
> > "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> > news:%23N$kB50eEHA.3632@.TK2MSFTNGP11.phx.gbl...
> >> Looks like your textbox is referring to itself. You need to refer to a
> >> textbox from report body.
> >> More reliable solution is to create read-only parameter with default
> >> value
> >> from query and then to use this parameter in the page header textbox.
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >>
> >> "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion>
> > wrote
> >> in message news:OMQtyN0eEHA.2560@.TK2MSFTNGP09.phx.gbl...
> >> > I'm getting this error when I try to preview my report: The value
> >> > expression
> >> > for the textbox 'RepairOrderID' refers to a field. Fields cannot be
> >> > used
> >> > in
> >> > page headers or footers.
> >> >
> >> > I searched through the news groups and a found suggestion to replace:
> >> >
> >> > Fields!RepairOrderID.Value
> >> > with
> >> > ReportItems!RepairOrderID.Value
> >> >
> >> > I tried this, it compiles now but when the report renders "#Error"
> >> > displays
> >> > where the RepairOrderID should appear. I found another suggestion to
> >> > use
> >> > the
> >> > First function this does not work either...
> >> >
> >> > Can someone please help....?
> >> >
> >> > Thanks!!!
> >> >
> >> > Dan
> >> >
> >> >
> >>
> >>
> >
> >
>

Data encryption and keys

Hi,
I would like to encrypt data in my database. I want encrypted column value to be viewable only for certain group of users. Users that has access to my database doesn't meant they can access to my encrypted data.

Currently, I am using the following "approach" as my key management.

create master key encryption by password= 'MasterKeyPass'

CREATE ASYMMETRIC KEY MyAsymmKey AUTHORIZATION MyUser
WITH ALGORITHM = RSA_1024
ENCRYPTION BY PASSWORD ='MyAsymmPass'

CREATE SYMMETRIC KEY MySymmKey WITH ALGORITHM = DES
ENCRYPTION BY ASYMMETRIC KEY MyAsymmKey

My data will be encrypted using Symmetric key MySymmKey.

User who want to access my data must have MasterKey and MyAsymmKey password.
Is it OK? Any better way?

Thank you

As long as the user you are trying to protect against is not a dbo or sysadmin, you can also use permissions (i.e. "GRANT CONTROL ON ASYMMETRIC KEY :: MyAsymmKey TO user1") to restrict access rather than through passwords. The advantage is the user then doesn't have to depend on memorizing a password and you don't have to pass any password values in which is safer from a security standpoint.

Sung

|||Fyi, Books online links up a section about BACKUP and RESTORING encryption keys http://msdn2.microsoft.com/en-US/library/ms157275.aspx link.

Data Conversion

This should be ez but I can't get it working;
Anyone knows how to convert a string value of '9/25/2006 4:17:12 PM'
to 'dd/mm/yyyy' and 'dd/mm/yy' format?
Db on SQL 2005.Try
set dateformat mdy
select convert(varchar(10), cast('9/25/2006 4:17:12 PM' as datetime), 103)
select convert(varchar(8), cast('9/25/2006 4:17:12 PM' as datetime), 3)
Linchi
"TBoon" wrote:

> This should be ez but I can't get it working;
> Anyone knows how to convert a string value of '9/25/2006 4:17:12 PM'
> to 'dd/mm/yyyy' and 'dd/mm/yy' format?
> Db on SQL 2005.|||testing
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:E4C66EE1-B961-41BC-B555-818C2EA41599@.microsoft.com...[vbcol=seagreen]
> Try
> set dateformat mdy
> select convert(varchar(10), cast('9/25/2006 4:17:12 PM' as datetime), 103)
> select convert(varchar(8), cast('9/25/2006 4:17:12 PM' as datetime), 3)
> Linchi
> "TBoon" wrote:
>

Data Conversion

This should be ez but I can't get it working;
Anyone knows how to convert a string value of '9/25/2006 4:17:12 PM'
to 'dd/mm/yyyy' and 'dd/mm/yy' format?
Db on SQL 2005.
Try
set dateformat mdy
select convert(varchar(10), cast('9/25/2006 4:17:12 PM' as datetime), 103)
select convert(varchar(8), cast('9/25/2006 4:17:12 PM' as datetime), 3)
Linchi
"TBoon" wrote:

> This should be ez but I can't get it working;
> Anyone knows how to convert a string value of '9/25/2006 4:17:12 PM'
> to 'dd/mm/yyyy' and 'dd/mm/yy' format?
> Db on SQL 2005.
|||testing
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:E4C66EE1-B961-41BC-B555-818C2EA41599@.microsoft.com...[vbcol=seagreen]
> Try
> set dateformat mdy
> select convert(varchar(10), cast('9/25/2006 4:17:12 PM' as datetime), 103)
> select convert(varchar(8), cast('9/25/2006 4:17:12 PM' as datetime), 3)
> Linchi
> "TBoon" wrote:

Data Conversion

This should be ez but I can't get it working;
Anyone knows how to convert a string value of '9/25/2006 4:17:12 PM'
to 'dd/mm/yyyy' and 'dd/mm/yy' format?
Db on SQL 2005.Try
set dateformat mdy
select convert(varchar(10), cast('9/25/2006 4:17:12 PM' as datetime), 103)
select convert(varchar(8), cast('9/25/2006 4:17:12 PM' as datetime), 3)
Linchi
"TBoon" wrote:
> This should be ez but I can't get it working;
> Anyone knows how to convert a string value of '9/25/2006 4:17:12 PM'
> to 'dd/mm/yyyy' and 'dd/mm/yy' format?
> Db on SQL 2005.|||testing
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:E4C66EE1-B961-41BC-B555-818C2EA41599@.microsoft.com...
> Try
> set dateformat mdy
> select convert(varchar(10), cast('9/25/2006 4:17:12 PM' as datetime), 103)
> select convert(varchar(8), cast('9/25/2006 4:17:12 PM' as datetime), 3)
> Linchi
> "TBoon" wrote:
>> This should be ez but I can't get it working;
>> Anyone knows how to convert a string value of '9/25/2006 4:17:12 PM'
>> to 'dd/mm/yyyy' and 'dd/mm/yy' format?
>> Db on SQL 2005.

Data by Row for a chart?

Hi,
I have a dataset which displayes data in a row (no of documents in each month)
So, basically I get 12 columns and one row with value for each month.
I want to create a Bar Chart, but can i supply the data by row?
I have to add 12 data fields to the Bar chart currently and they all show up
in different colors because of that. Is there a way for me to drop the row as
a series (like in excel?)
Thanks,Hi,
Can anyone please let me know if this is possible?
Along with the (unanswered) questions posted before, i also wanted to know
if it is possible to have one series as a line chart and one series as a Bar
chart on the same graph? if yes, can someone PLEASE tell me how? THANKS
"Prashant" wrote:
> Hi,
> I have a dataset which displayes data in a row (no of documents in each month)
> So, basically I get 12 columns and one row with value for each month.
> I want to create a Bar Chart, but can i supply the data by row?
> I have to add 12 data fields to the Bar chart currently and they all show up
> in different colors because of that. Is there a way for me to drop the row as
> a series (like in excel?)
> Thanks,
>|||If you have RS 2000 SP1 or SP2 installed, you could set the color to the
same identical value for all values (under appearance - series styles).
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Prashant" <Prashant@.discussions.microsoft.com> wrote in message
news:1712D9E5-284D-40CF-8E17-B5BBE3476A5C@.microsoft.com...
> Hi,
> I have a dataset which displayes data in a row (no of documents in each
> month)
> So, basically I get 12 columns and one row with value for each month.
> I want to create a Bar Chart, but can i supply the data by row?
> I have to add 12 data fields to the Bar chart currently and they all show
> up
> in different colors because of that. Is there a way for me to drop the row
> as
> a series (like in excel?)
> Thanks,
>|||Yes, you can do that. Create your column chart. Then go to the chart
properties dialog. In the dialog go to the Data tab and edit the properties
of your trend data series. The "Edit chart value" dialog should pop up. In
this dialog go to the Appearance tab and select "Plot data as line".
You can also check this KB article: http://support.microsoft.com/kb/842422
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Prash" <Prash@.discussions.microsoft.com> wrote in message
news:C9D588CF-E695-4E62-9E49-4E4F9FEC0EAD@.microsoft.com...
> Hi,
> Can anyone please let me know if this is possible?
> Along with the (unanswered) questions posted before, i also wanted to know
> if it is possible to have one series as a line chart and one series as a
> Bar
> chart on the same graph? if yes, can someone PLEASE tell me how? THANKS
>
> "Prashant" wrote:
>> Hi,
>> I have a dataset which displayes data in a row (no of documents in each
>> month)
>> So, basically I get 12 columns and one row with value for each month.
>> I want to create a Bar Chart, but can i supply the data by row?
>> I have to add 12 data fields to the Bar chart currently and they all show
>> up
>> in different colors because of that. Is there a way for me to drop the
>> row as
>> a series (like in excel?)
>> Thanks,

Data access by creating functions

a number of time I have come accross developers using functions like

GetProduct()
GetProductTitle()
GetProductCategory()

to get the value of the data instead of just using queries / stored procedures

I have not understood why

can you please point to some good forum messages / blogposts / articles on this ?

Whats your take on this ?Dealing with functions that give direct results is easier programmatically than dealing with OUTPUT parameters in stored procedures.|||The concept of user defined functions is a great way to centralize code and they can come in very handy.

But in my experience they have one major drawback: performance.

When a user defined function needs to be performed on over a thousend rows (arbitrary boundery) the performance seems to decrease dramaticly. My guess it is because SQL Server has to switch from set-processing to row-by-row prossing.

AmitGeorge: look in the BOL under CREATE FUNCTION. That doesn't say anything about performance though.

Lex|||The use of UDFs will not cause the engine to stop using set-based processing. It's more likely due to the fact that UDF's tend to complex application-specific algorithms, otherwise they would be included in the standard stock of functions, right? And of course, there is no guarantee that the code in the UDF is GOOD code. MS hasn't yet come up with an error message that goes "You want me to run that? You can't be serious. Click OK to continue, or click Jeez What Was I Thinking to cancel."|||Hi Blindman,

A few months back I had to tune a stp which did a select which returned about 50 columns and about 25000 rows. Of these 50 columns 10 called a udf which only did the following:
CREATE FUNCTION convdate (@.dtMyDate DATETIME)
RETURNS VARCHAR(8)
AS
BEGIN
RETURN CONVERT(VARCHAR(8), @.dtMyDate, 112)
END
After replacing the functions calls with the CONVERT-statement in the select the procedure was about 10 times as fast!

If SQL is still using set-based processing I can't explain why! Got any ideas?

Lex|||i was not talking about using UDFs

I am talking about functions in the client language like VB, PHP, Asp, Java etc|||After replacing the functions calls with the CONVERT-statement in the select the procedure was about 10 times as fast!

If SQL is still using set-based processing I can't explain why! Got any ideas?Sorry. I just don't get the same results. I used these objects:set nocount on
go

CREATE FUNCTION dbo.convdate (@.dtMyDate DATETIME)
RETURNS VARCHAR(8)
AS
BEGIN
RETURN CONVERT(VARCHAR(8), @.dtMyDate, 112)
END

GO

CREATE TABLE TestDates (DateID int NOT NULL IDENTITY (1, 1) PRIMARY KEY CLUSTERED, DateValue datetime NOT NULL)

Insert into TestDates (DateValue)
select dateadd(Hour, Ones + Tens + Hundreds + Thousands + TenThousands, '2000-01-01')
from (select 0 as Ones
Union select 1
Union select 2
Union select 3
Union select 4
Union select 5
Union select 6
Union select 7
Union select 8
Union select 9) Ones,
(select 0 as Tens
Union select 10
Union select 20
Union select 30
Union select 40
Union select 50
Union select 60
Union select 70
Union select 80
Union select 90) Tens,
(select 0 as Hundreds
Union select 100
Union select 200
Union select 300
Union select 400
Union select 500
Union select 600
Union select 700
Union select 800
Union select 900) Hundreds,
(select 0 as Thousands
Union select 1000
Union select 2000
Union select 3000
Union select 4000
Union select 5000
Union select 6000
Union select 7000
Union select 8000
Union select 9000) Thousands,
(select 0 as TenThousands
Union select 10000
Union select 20000
Union select 30000
Union select 40000
Union select 50000
Union select 60000
Union select 70000
Union select 80000
Union select 90000) TenThousands
order by Ones + Tens + Hundreds + Thousands + TenThousands

Running these statements, I get virtually no difference in execution times:declare @.StartTime datetime
set @.StartTime = getdate()

-- select CONVERT(VARCHAR(8), DateValue, 112)
-- from TestDates

select dbo.convdate(DateValue)
from TestDates

select datediff(ms, @.StartTime, GetDate())
Regardless, you can check the execution plans for each statement and see that they are identical.|||i was not talking about using UDFs

I am talking about functions in the client language like VB, PHP, Asp, Java etc

you got the wrong forum then.|||MS hasn't yet come up with an error message that goes "You want me to run that? You can't be serious. Click OK to continue, or click Jeez What Was I Thinking to cancel."

it appears 2005 does this by making sucky code suck more after the "upgrade".

Data access by creating functions

A number of time I have come accross developers using functions like

GetProduct()
GetProductTitle()
GetProductCategory()

to get the value of the data instead of just using queries / stored procedures

Not UDFs but functions in VB and in DLLs

I have not understood why

can you please point to some good forum messages / blogposts / articles on this ?

Whats your take on this ?The main reason for this is to separate your code into layers. When you create this function, then you could have different database implementations inside of the function, like data from SQL Server or Oracle, but to outside world, that uses this function, it is still the same value. This design simplifies switching between databases and does not affect the code that calls these functions

Dashboard Custom Report Error : The 'version_string' parameter ismissing a value

I get errors like this " Report Parameter 'sql_handle' was not found.
I can not find anything in newsgroups or web. Anyone else get this or
have an idea. Here are my install details:
Downloaded and installed Performance Dash Board
Installed setup.sql in MSDB on server to be monitored
@.@.VERSION = Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
All standard reports work just fine. These custom reports a AWESOME
and I can't wait to see them.
Thank you!
Erik
Reading instructions is always good..DUH... You have to go through the
main dash board RDL and then its all cool.
This is SOO COOL!!!!
Are there any other custom RDL files out there on websites to
download?
Erik

CustomRollupColumn and aggregates

I've a parent-child relation where I want the cell value will be the sum of children but if the cell value of the parent is given don't operate this calculation.

So I've defined a CustomRollupColumn on the parent with the forumla:

IIF(ISEMPTY(([TypeHierarchy].[Type].[3],[Measures].[Value])),([TypeHierarchy].[Type].[1],[Measures].[Value])+([TypeHierarchy].[Type].[2],[Measures].[Value]),([TypeHierarchy].[Type].[3],[Measures].[Value]))

Where [TypeHierarchy].[Type].[3] is the parent of [1] and [2].

It works really well but When I want to aggregate on another dimension I've a big issues.

Just having the following table: (ID, Type, Date, Value)

1113/12/2006 0:00:001012212/12/2006 0:00:002013113/12/2006 0:00:001024113/12/2006 0:00:001036314/12/2006 0:00:00877114/12/2006 0:00:0098213/12/2006 0:00:0010009115/12/2006 0:00:0040110215/12/2006 0:00:0040211315/12/2006 0:00:00403

When I browse the cube I've the right results except for the total over dates wher for Type 3 I've 490 where I hoped to have 1997 (201+1306+87+430). But unfortunatelly SASS doesn't sum the two first columns which are calculated by the CustomRollupFormula.

Any idea to solve this issue is really welcome...

Seddryck wrote:

I've a parent-child relation where I want the cell value will be the sum of children but if the cell value of the parent is given don't operate this calculation.

Using the above logic I would expect your example to add up to 490 and I can't follow where the values you expect to add up to 1997 are coming from. Maybe you could try re wording your issue. Is it that you want the values from the children added to the value in the parent if it is present?

|||This is the table I've with "Time" on columns and "Type" on rows

12/12 13/12 14/12 15/12 Total
#1 - 306 9 401 716
#2 201 1000 - 402 1603
#3(1+2) 201 1306 87 403 490

As

you see the row #3 is calculated (sum of rows#1 and #2) only in the

case the value is not in my fact table (Case for columns 12/12 and

13/12). If the value is provided by my fact table I use it. To

implement this I've used the CustomRollup and the formula displayed in

first post.

Everything is working as I hoped except the Total of

row #3. The Total of row #3 only used value of columns 14/12 and 15/12

but I want to use also calculated values of columns 12/12 and 13/12 so

I wanted to have a total of 201+1306+87+403 and not a total of 87+403

Thx for your reply