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 ?

PIVOT problems

All of the examples I've seen using PIVOT have the pivoted columns known
(hard coded). I want to PIVOT dynamically... here's my problem.
Here's my example:
Script that captures all data (without group by clause)
select
p.procID,
p.procName,
i.itemName
from
tblProcs p
left outer join tblTailoredItems ti on p.procID = ti.ProcID
left outer join tblItems i on ti.itemID = i.itemID
this will in a sense get all data i need but I want to have all itemNames in
tblItems as columns. I need to use PIVOT but I can't figure out how to do
this dynamically.
Please let me know if you need more information. For each itemName (column)
I want the count of how many times the item is tailored under it (for each
procedure)... which should be trivial.
Thanks
- jvHello Jeffrey,

> All of the examples I've seen using PIVOT have the pivoted columns
> known (hard coded). I want to PIVOT dynamically... here's my problem.
Nope, not going to happen in this version of SQL Server, maybe the next.
You can build the query (the select and the pivot) up dynamically use sp_exe
cutesql
to execute it.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Finally, a Mentor that's a dynamic sql advocate:)
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad74152348c810f11c91c920@.news.microsoft.com...
> Hello Jeffrey,
>
> Nope, not going to happen in this version of SQL Server, maybe the next.
> You can build the query (the select and the pivot) up dynamically use
sp_executesql
> to execute it.
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>|||Hello 05ponyGT,

> Finally, a Mentor that's a dynamic sql advocate:)
Well yes and no. In this case, its the only suitable solution to the problem
.
In general, no, I don't like dynamic SQL very much for all of the well-worn
reasons.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||You haven't checked out RAC?:)
www.rac4sql.net
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad74154fa8c8111dd31eb4d0@.news.microsoft.com...
> Hello 05ponyGT,
>
> Well yes and no. In this case, its the only suitable solution to the
problem.
> In general, no, I don't like dynamic SQL very much for all of the
well-worn
> reasons.
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>|||That's kind of a bad answer don't you think? I mean they've had this
functionality in Access a long time ago and your telling me that SQL can't
handle this at all'
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad74152348c810f11c91c920@.news.microsoft.com...
> Hello Jeffrey,
>
> Nope, not going to happen in this version of SQL Server, maybe the next.
> You can build the query (the select and the pivot) up dynamically use
> sp_executesql to execute it.
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>|||There are no native T-SQL commands to handle this situation. Access
does it, but also consider the fact all data from Access is manipulated
completely on the client - you won't be killing your server performance
as much if you pull all the data to the client in one fell swoop and
pivot/crosstab it there.
Another solution is to attach a SQL table to an Access front end and
use Access to create your xtab.|||Whatsamatter, can't handle the truth?
I heard someone ask where are the server keywords for a factor analysis.
There's lots of good software out there for xtabs and what not (and I don't
mean Access).
Discover the world beyond MS:)
"Jeffrey A. Voigt" <jvelite@.gmail.com> wrote in message
news:%23tXs2J8QGHA.5552@.TK2MSFTNGP14.phx.gbl...
> That's kind of a bad answer don't you think? I mean they've had this
> functionality in Access a long time ago and your telling me that SQL can't
> handle this at all'
>
> "Kent Tegels" <ktegels@.develop.com> wrote in message
> news:b87ad74152348c810f11c91c920@.news.microsoft.com...
>|||Hello Jeffrey,

> That's kind of a bad answer don't you think? I mean they've had this
> functionality in Access a long time ago and your telling me that SQL
> can't handle this at all'
Um, SQL can do it, its T-SQL that's the problem. Anyway, yes, I totally agre
e
that its a sucky answer.
That said, SQL Server has had transactions for ages, still don't see that
in Access either. The best way to look at this is "its MS's initial introduc
tion
of PIVOT into T-SQL. It works. It will be improved as T-SQL improves."
Glass half full, in other words.
I don't have the SQL 1999 standard specs in front of me, but does that allow
for a dynamic list in a pivot?
Cheers,
Kent
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||PIVOT is not in ANSI SQL.
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad7415e2a8c811e00b0b4300@.news.microsoft.com...
> Hello Jeffrey,
>
> Um, SQL can do it, its T-SQL that's the problem. Anyway, yes, I totally
> agree that its a sucky answer.
> That said, SQL Server has had transactions for ages, still don't see that
> in Access either. The best way to look at this is "its MS's initial
> introduction of PIVOT into T-SQL. It works. It will be improved as T-SQL
> improves."
> Glass half full, in other words.
> I don't have the SQL 1999 standard specs in front of me, but does that
> allow for a dynamic list in a pivot?
> Cheers,
> Kent
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>