Making groups available

I'd like to get the sum of a row in a reporting services table. Unlike
summing columns which are easy, I'm told I need to create a group.
I create the group with the fields I want to sum. Then I right click on the
table cell I want to hold the sum value, and select expression. My table
column names are there, but not my group (which I need to sum my row).
When I create the group, I give it a name and add the fields I want to sum.
What else do I have to do to make the group available when I edit expressions?
Of course, if there's some aggregate functions that let's me sum a row
without a group, all the better.
Thanks
--
RandyHi Randy,
Welcome to the MSDN newsgroup.
Regarding on the "sum of a row in a reporting services table", do you mean
perform sum on all the columns in a single data row or still sum a certain
column , but restricted in a certain group?
If you're wantting to perform the sum on all the columns in a single row,
I'm afraid, the group or other filter can not help on this. You may need to
manually write the sum code in the expression of the textbox or certain
table cell. Also, since you'll perform sum on the columns, we need to make
sure all the columns are of number type.
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Many thanks for the reply.
Yes, I would like to sum all the columns in the row, and they are all type
int.
I have found the code window, and written a VB function to sum all the
columns. When I say Code.MySum(...) in the Edit Expression dialog box, MySum
is underlined in red and "not defined".
I've tried to just sum the columns in the edit expression dialog box. The
sum function provided only takes one parameter. I'd need an example or BOM
reference to see how to do this. At one point, someone suggested the Subtotal
function, but I couldn't find that in the available aggregate functions list.
If you could point me toward an article on writing functions for reporting
services under the Code tab or in the Edit Expression dialog box, that would
help a lot.
Thanks again,
--
Randy
"Steven Cheng[MSFT]" wrote:
> Hi Randy,
> Welcome to the MSDN newsgroup.
> Regarding on the "sum of a row in a reporting services table", do you mean
> perform sum on all the columns in a single data row or still sum a certain
> column , but restricted in a certain group?
> If you're wantting to perform the sum on all the columns in a single row,
> I'm afraid, the group or other filter can not help on this. You may need to
> manually write the sum code in the expression of the textbox or certain
> table cell. Also, since you'll perform sum on the columns, we need to make
> sure all the columns are of number type.
> Regards,
> Steven Cheng
> Microsoft Online Community Support
>
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>
>
>
>|||Thanks for your followup Randy,
IMO, if you just want to do sum on all the columns in a single row, you can
just put the sum code in the expression of the textbox( use + operator).
For example:
=Fields("col1").Value + Fields("col2").Value + Fields("col3").Value
For custom code in SSRS, you can have a look at the following web reference:
#Using Custom Code References in Expressions [Reporting Services]
http://msdn2.microsoft.com/en-US/library/ms155798(SQL.90).aspx
#Chapter 5: Using Custom .Net Code with Reports
http://www.yukonxml.com/chapters/apress/reportingservices/dotnet/
Hope this helps.
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Making a table viewable or not on specific page

Hi
I want to make a table or even a row to be visible on the first and the
last pages only. Any suggestions on how to do it will be helpful.
thanxIf you want a table or row to be visible only when on the last page you could
try the following expression in the table's/row's Hidden property:
=iif(Globals!PageNumber = Globals!TotalPages, False, True)
If you want it to appear only on the first page try:
=iif(Globals!PageNumber = 1 , False, True)
HTH,
Magendo_man
"Aquariun25" wrote:
> Hi
> I want to make a table or even a row to be visible on the first and the
> last pages only. Any suggestions on how to do it will be helpful.
> thanx
>|||Sorry, ignore previous suggestion. These Global values can only be used in
page headers and footers!
"magendo_man" wrote:
> If you want a table or row to be visible only when on the last page you could
> try the following expression in the table's/row's Hidden property:
> =iif(Globals!PageNumber = Globals!TotalPages, False, True)
> If you want it to appear only on the first page try:
> =iif(Globals!PageNumber = 1 , False, True)
> HTH,
> Magendo_man
> "Aquariun25" wrote:
> > Hi
> > I want to make a table or even a row to be visible on the first and the
> > last pages only. Any suggestions on how to do it will be helpful.
> > thanx
> >
> >sql

Making a row not show

I have one column that is an expression that either returns a value or
returns zero. I would like if it is zero to not show at all. How is this
done?
Thanks,
RyanGo to properties for that column, click on Visibility tab. Click on
Expression radio button and enter this formula:
=iif (Fields!<expression>.Value = 0, True, False)
you may have to map the expression to a data set row before this will work.
I have made it work with parameter and field values.
"Ryan Mcbee" wrote:
> I have one column that is an expression that either returns a value or
> returns zero. I would like if it is zero to not show at all. How is this
> done?
> Thanks,
> Ryan|||Carl,
I tried this and it didn't work. I think because I am using a matrix
report. Would have to put this formula in every column since it is a matrix
report?
Thanks,
Ryan
"Carl Henthorn" wrote:
> Go to properties for that column, click on Visibility tab. Click on
> Expression radio button and enter this formula:
> =iif (Fields!<expression>.Value = 0, True, False)
> you may have to map the expression to a data set row before this will work.
> I have made it work with parameter and field values.
> "Ryan Mcbee" wrote:
> > I have one column that is an expression that either returns a value or
> > returns zero. I would like if it is zero to not show at all. How is this
> > done?
> >
> > Thanks,
> > Ryan

Making a row delete itself at a certain time?

Is there any possible way to make a row delete itself at a certain date and time? I am tring to make an "On_Sale" table and perticular items(rows) must expire at a certain Date and time. I have an idea to delete a row when it expires but it will cause an extra burden on my application. So is it possible to have SQL responsible for deleting a row at a certain time and date?

Also, I have another question:
when I asign a PK Identity to a certain column is there a way I can enforce consecutive order of PK values? For example if I delete a row and the PK value was 5 it will reoder the whole table so all PK values are in consecutive order:

PK----Item----price
1----XYZ1----$$$$
2----XYZ2----$$$$
3----XYZ3----$$$$

when I delete PK 2 this is what happens

PK----Item----price
1----XYZ1----$$$$
3----XYZ3----$$$$

the PKs are not in consecutive order

What I want is this to happen: when PK 2 is deleted I need it to show as follows

PK----Item----price
1----XYZ1----$$$$
2----XYZ3----$$$$

The PKs stay in consecutive order.

See how the PK are still in consecutive order?I am tring to make an "On_Sale" table and perticular items(rows) must expire at a certain Date and time. I have an idea to delete a row when it expires but it will cause an extra burden on my application.Why delete it at all? Save the expiration date within your row, and then create a view on that table with something like WHERE expiration_date <= current_date. Then make your application use the view to retrieve the data (or simply add the condition to your application logic), thus it will never see expired rows. If you put an index on that column the retrieval should be quite fast.

If you are concerned about space, than run a batch job every night that deletes the expired rows.|||you could create an agent job that would delete the rows also.

Make row in table shrink or collaps

How can I make a row in a table shrink or collapse when there's no data for
that row? I have a table with several detail rows in it. the bottom row
has 2 cells. the first one if for a label and the second one is for the
data.
cell 1: =iif(len((Fields!Id_Assignee.Value))>0,"Assignees","")
cell 2: =(Fields!Id_Assignee.Value)
The iff statement in cell 1 works good so when there's no data it doesn't
show the label. Now when there's no data in cell 2, how can I make this row
go away completely? I set the CanShrink property for both cells to true.
Thanks.
--
moondaddy@.nospam.nospamPut the hight of the row = 0 (you have to do this in the properties panel,
not in the window you get by right clicking) and enable the hight to grow
"moondaddy" wrote:
> How can I make a row in a table shrink or collapse when there's no data for
> that row? I have a table with several detail rows in it. the bottom row
> has 2 cells. the first one if for a label and the second one is for the
> data.
> cell 1: =iif(len((Fields!Id_Assignee.Value))>0,"Assignees","")
> cell 2: =(Fields!Id_Assignee.Value)
> The iff statement in cell 1 works good so when there's no data it doesn't
> show the label. Now when there's no data in cell 2, how can I make this row
> go away completely? I set the CanShrink property for both cells to true.
> Thanks.
> --
> moondaddy@.nospam.nospam
>
>|||Thanks that is exactly what I was looking for.
Does this mean Can Shrink doesn't really work?
Dave
"Antoon" wrote:
> Put the hight of the row = 0 (you have to do this in the properties panel,
> not in the window you get by right clicking) and enable the hight to grow
> "moondaddy" wrote:
> > How can I make a row in a table shrink or collapse when there's no data for
> > that row? I have a table with several detail rows in it. the bottom row
> > has 2 cells. the first one if for a label and the second one is for the
> > data.
> >
> > cell 1: =iif(len((Fields!Id_Assignee.Value))>0,"Assignees","")
> > cell 2: =(Fields!Id_Assignee.Value)
> >
> > The iff statement in cell 1 works good so when there's no data it doesn't
> > show the label. Now when there's no data in cell 2, how can I make this row
> > go away completely? I set the CanShrink property for both cells to true.
> >
> > Thanks.
> >
> > --
> > moondaddy@.nospam.nospam
> >
> >
> >

Make row data column data??

I need to make a report that looks like...
Statistic A Statistic B Statistic C Statistic D
----
99 07 102 91
It would be easy but the values are all in one column in the table, like...
KeyValue | StatisticCode | StatisticValue
001| A| 99
002| B| 07
003| D| 91
004| C| 102
What's the best way to do this?, we have several reports that we need to
build like this.
Thanks,
ShawnHave you tried using the matrix control?
"sysdesigner" wrote:
> I need to make a report that looks like...
> Statistic A Statistic B Statistic C Statistic D
> ----
> 99 07 102 91
>
> It would be easy but the values are all in one column in the table, like...
> KeyValue | StatisticCode | StatisticValue
> 001| A| 99
> 002| B| 07
> 003| D| 91
> 004| C| 102
>
> What's the best way to do this?, we have several reports that we need to
> build like this.
>
> Thanks,
> Shawn
>

Make a textbox invisible based on another value

I cannot figure out how to make a textbox or a row in a table visible based
on another boolean variable. I don't want it to be based on the user
toggling or collapsing something - I simply want it to display or hide based
on a field in the dataset. Thanks!Use the ReportItems collection (e.g. =ReportItems!textbox1.Value) to refer
to the value of another textbox.
Note: expressions that refer to report items can only refer to values of
peer report items (those in the same grouping scope) or report items in any
containing (ancestor) grouping scope.
See also:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Richard Wrench" <rwrench@.icsecurity.com> wrote in message
news:%23eQA%23DXXEHA.556@.tk2msftngp13.phx.gbl...
> I cannot figure out how to make a textbox or a row in a table visible
based
> on another boolean variable. I don't want it to be based on the user
> toggling or collapsing something - I simply want it to display or hide
based
> on a field in the dataset. Thanks!
>|||Thanks for pointing out the ReportItems collection (that answered another
question) however, I'm still confused about Textbox visibility.
I have a boolean field in the database named OtherCharges. I want a textbox
to display only if this value is true - How can I do this? - To me, there
should be a Visible property, instead, there is a Hidden and ToggleItem
property which are used so that the user can collapse / expand data - a la
Crystal - I don't want to do that.
Regards, Richard
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:eKefMYXXEHA.3012@.tk2msftngp13.phx.gbl...
> Use the ReportItems collection (e.g. =ReportItems!textbox1.Value) to refer
> to the value of another textbox.
> Note: expressions that refer to report items can only refer to values of
> peer report items (those in the same grouping scope) or report items in
any
> containing (ancestor) grouping scope.
> See also:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Richard Wrench" <rwrench@.icsecurity.com> wrote in message
> news:%23eQA%23DXXEHA.556@.tk2msftngp13.phx.gbl...
> > I cannot figure out how to make a textbox or a row in a table visible
> based
> > on another boolean variable. I don't want it to be based on the user
> > toggling or collapsing something - I simply want it to display or hide
> based
> > on a field in the dataset. Thanks!
> >
> >
>|||The textbox has a Visibility.Hidden property in the VS properties window
(hit F4 if it is not visible). For the Hidden property you would write this
expression: =(Fields!OtherCharges.Value = False)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Richard Wrench" <rwrench@.icsecurity.com> wrote in message
news:uzMqAJsXEHA.3716@.TK2MSFTNGP11.phx.gbl...
> Thanks for pointing out the ReportItems collection (that answered another
> question) however, I'm still confused about Textbox visibility.
> I have a boolean field in the database named OtherCharges. I want a
textbox
> to display only if this value is true - How can I do this? - To me, there
> should be a Visible property, instead, there is a Hidden and ToggleItem
> property which are used so that the user can collapse / expand data - a la
> Crystal - I don't want to do that.
> Regards, Richard
>
>
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:eKefMYXXEHA.3012@.tk2msftngp13.phx.gbl...
> > Use the ReportItems collection (e.g. =ReportItems!textbox1.Value) to
refer
> > to the value of another textbox.
> > Note: expressions that refer to report items can only refer to values of
> > peer report items (those in the same grouping scope) or report items in
> any
> > containing (ancestor) grouping scope.
> > See also:
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "Richard Wrench" <rwrench@.icsecurity.com> wrote in message
> > news:%23eQA%23DXXEHA.556@.tk2msftngp13.phx.gbl...
> > > I cannot figure out how to make a textbox or a row in a table visible
> > based
> > > on another boolean variable. I don't want it to be based on the user
> > > toggling or collapsing something - I simply want it to display or hide
> > based
> > > on a field in the dataset. Thanks!
> > >
> > >
> >
> >
>|||Perfect ! Thanks ...
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:OBvdeGtXEHA.2500@.TK2MSFTNGP09.phx.gbl...
> The textbox has a Visibility.Hidden property in the VS properties window
> (hit F4 if it is not visible). For the Hidden property you would write
this
> expression: =(Fields!OtherCharges.Value = False)
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Richard Wrench" <rwrench@.icsecurity.com> wrote in message
> news:uzMqAJsXEHA.3716@.TK2MSFTNGP11.phx.gbl...
> > Thanks for pointing out the ReportItems collection (that answered
another
> > question) however, I'm still confused about Textbox visibility.
> >
> > I have a boolean field in the database named OtherCharges. I want a
> textbox
> > to display only if this value is true - How can I do this? - To me,
there
> > should be a Visible property, instead, there is a Hidden and ToggleItem
> > property which are used so that the user can collapse / expand data - a
la
> > Crystal - I don't want to do that.
> >
> > Regards, Richard
> >
> >
> >
> >
> >
> >
> > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> > news:eKefMYXXEHA.3012@.tk2msftngp13.phx.gbl...
> > > Use the ReportItems collection (e.g. =ReportItems!textbox1.Value) to
> refer
> > > to the value of another textbox.
> > > Note: expressions that refer to report items can only refer to values
of
> > > peer report items (those in the same grouping scope) or report items
in
> > any
> > > containing (ancestor) grouping scope.
> > > See also:
> > >
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > >
> > > "Richard Wrench" <rwrench@.icsecurity.com> wrote in message
> > > news:%23eQA%23DXXEHA.556@.tk2msftngp13.phx.gbl...
> > > > I cannot figure out how to make a textbox or a row in a table
visible
> > > based
> > > > on another boolean variable. I don't want it to be based on the
user
> > > > toggling or collapsing something - I simply want it to display or
hide
> > > based
> > > > on a field in the dataset. Thanks!
> > > >
> > > >
> > >
> > >
> >
> >
>

Make a copy of parts of a ROW, but with a new Key.

Hi there,

I want to insert a new record into my database table, giving new field
values for all but one (an image field), which I can copied from an existing
record. The background to this is that I am allowing a user to create a new
"template" from an existing one, thus I want a new record, but also to copy
the existing template data blob into the new record.

My code looks like this, but obviously doesn't work (I don't supply a field
for the first field "ID" in my SELECT statement which should be a unique ID.
Obviously, I want SQL to automatically generate one for me!). Is there any
way to do this with SQL?

IF @.Error = 0
BEGIN
SELECT @.DateCreated,
@.Title,
@.Description,
@.Thumbnail,
ReportTemplate.Report

INTO

ReportTemplate

FROM

ReportTemplate

SET @.Error = @.@.ERROR
ENDI think I got it. Like this:

IF @.Error = 0
BEGIN
INSERT INTO
ReportTemplate
(
DateCreated,
Title,
Description,
IsStatic,
Thumbnail,
Report
)

SELECT

@.DateCreated,
@.Title,
@.Description,
@.IsStatic,
@.Thumbnail,
ReportTemplate.Report

FROM

ReportTemplate

WHERE

ReportTemplate.ID = @.ID

SET @.Error = @.@.ERROR
END

"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:c635gr$483$1$8302bc10@.news.demon.co.uk...
> Hi there,
> I want to insert a new record into my database table, giving new field
> values for all but one (an image field), which I can copied from an
existing
> record. The background to this is that I am allowing a user to create a
new
> "template" from an existing one, thus I want a new record, but also to
copy
> the existing template data blob into the new record.
> My code looks like this, but obviously doesn't work (I don't supply a
field
> for the first field "ID" in my SELECT statement which should be a unique
ID.
> Obviously, I want SQL to automatically generate one for me!). Is there
any
> way to do this with SQL?
> IF @.Error = 0
> BEGIN
> SELECT @.DateCreated,
> @.Title,
> @.Description,
> @.Thumbnail,
> ReportTemplate.Report
> INTO
> ReportTemplate
> FROM
> ReportTemplate
> SET @.Error = @.@.ERROR
> END