Configure "Remote Connections" via Group Policy?

Is it possible to create a group policy that sets the "remote connections"
property that exists in the SQL Server Surface Area Configuration applet?
How? :-)
Thanks,
JoeNo. This is also something you do NOT want to do. If you could create
this, it would mean that every single SQL Server instance, including the
various SQL Server Express instances installed with thousands of different
applications would automatically be able to receive inbound connections from
remote clients. This was a very specific security issue that was addressed
in this version. All you have to do is to look up the Slammer worm and
you'll understand why you only want those instances which need to service
client connections able to do so.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Joe" <jwdaigle@.nospam.nospam> wrote in message
news:Ow0cVf8KGHA.3396@.TK2MSFTNGP10.phx.gbl...
> Is it possible to create a group policy that sets the "remote connections"
> property that exists in the SQL Server Surface Area Configuration applet?
> How? :-)
> Thanks,
> Joe
>|||And if I wanted to ensure that it was never set to allow remote connections?
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:uGopFv$KGHA.3264@.TK2MSFTNGP11.phx.gbl...
> No. This is also something you do NOT want to do. If you could create
> this, it would mean that every single SQL Server instance, including the
> various SQL Server Express instances installed with thousands of different
> applications would automatically be able to receive inbound connections
> from remote clients. This was a very specific security issue that was
> addressed in this version. All you have to do is to look up the Slammer
> worm and you'll understand why you only want those instances which need to
> service client connections able to do so.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Joe" <jwdaigle@.nospam.nospam> wrote in message
> news:Ow0cVf8KGHA.3396@.TK2MSFTNGP10.phx.gbl...
>
>|||Hi Joe,
Welcome to use MSDN Managed Newsgroup Support. And thank Michael's great
help!
As Michael stated, it does not mean that never set to allow remote
connection. You can allow remote connection for those instance you want to.
By default, only Enterprise, Standard and Workgroup editions allow remote
client connection.
If you have any questions or concerns, don't hesitate to let me know. We
are here to be of assistance!
Sincerely yours,
Wei Lu
Microsoft Online Partner 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.|||So then it is not possible to ensure that "allow remote client connections"
is disabled via group policy? Thats too bad, because it requires me to
manually check systems for compliance.
Ok, thank you both for your help.
Joe
"Wei Lu" <t-weilu@.online.microsoft.com> wrote in message
news:Ap4y3JHLGHA.3052@.TK2MSFTNGXA01.phx.gbl...
> Hi Joe,
> Welcome to use MSDN Managed Newsgroup Support. And thank Michael's great
> help!
> As Michael stated, it does not mean that never set to allow remote
> connection. You can allow remote connection for those instance you want
> to.
> By default, only Enterprise, Standard and Workgroup editions allow remote
> client connection.
> If you have any questions or concerns, don't hesitate to let me know. We
> are here to be of assistance!
> Sincerely yours,
> Wei Lu
> Microsoft Online Partner 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.
>
>|||You are welcome, Joe.
Sincerely yours,
Wei Lu
Microsoft Online Partner 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.

Conditionally Formating Subtotal Output

I created a subtotal for my column group by right-clicking on the group and selecting subtotal. I would like to add the condition to the subtotal, that if the value is less than 0 to print zero and not the negative subtotal amount. Is there a way to do this? I haven't been able to find a way to add an expression to the greyed out subtotal field. Thanks.

I would get rid of the autogenerated subtotal, create another matrix that sums the field and conditionally edit the expression of the Sum matrix.

I have also found that the autogenerated subtotal feature has much to be desired.

Conditional visibility in a table group.

I have a report that presents the detail lines in two groupings. So I
have the detail lines as a subset of group1 which is a subset of group2.
Nothing clever there.
I want now to add a line to the report that is only visible if a
parameter "DisplaySignOff" (which will just show a box which users will
sign) has value = "Y".
If I add a detail group below the existing detail line and enter an
expression to make its visibility conditional on the parameter, the
original detail line becomes invisible too. I tried to change the name
of the added group, but it insists on having the same group name- I can
change it (type some other name), but the change is ignored.
If a add a third group that is a subset of group2 (and just select a
footer) and enter an expression to make its visibility conditional on
the parameter, the detail lines disappear again.
Any pointers gratefully received...Do you want to add the line for each record when DisplaySignOff = "Y"? If so,
right-click on your existing Detail row and choose "Insert Row Below". Now
select this row using its grey selector handle (this selects the entire row).
Now set the Hidden property under Visibility to an expression like:
=iif(ReportItems!DisplaySignOff = "Y", False, True)
Charles Kangai, MCDBA, MCT
"Nick Addison" wrote:
> I have a report that presents the detail lines in two groupings. So I
> have the detail lines as a subset of group1 which is a subset of group2.
> Nothing clever there.
> I want now to add a line to the report that is only visible if a
> parameter "DisplaySignOff" (which will just show a box which users will
> sign) has value = "Y".
> If I add a detail group below the existing detail line and enter an
> expression to make its visibility conditional on the parameter, the
> original detail line becomes invisible too. I tried to change the name
> of the added group, but it insists on having the same group name- I can
> change it (type some other name), but the change is ignored.
> If a add a third group that is a subset of group2 (and just select a
> footer) and enter an expression to make its visibility conditional on
> the parameter, the detail lines disappear again.
> Any pointers gratefully received...
>

Conditional Visability in a table

Hi,
I'm new to RS and want to know how to write an expression which will set
visablility of a table group header to false if group1.value = "Account".
Any ideas?
Thanks
Jonthe basics of using an expression to hide a text box is this:
Type an expression that evaluates to a Boolean: True to hide the item and
False to show the item. Click the expression (fx) button to edit the
expression.
Remember, true = hidden, false = show
I don't know if that will work for your table group header as well, but
thats how it works with text boxes.
Karl
"jonwolds" wrote:
> Hi,
> I'm new to RS and want to know how to write an expression which will set
> visablility of a table group header to false if group1.value = "Account".
> Any ideas?
> Thanks
> Jon

Conditional Sum/Runnining Total

I have a simple table in ssrs where data is returned from a stored procedure.

I have detail data group totals of the detail data.

I want to be able to create a sum of the detail data matching certain criteria.

i.e.

I have the following total field

sum(Fields!hours_m2.Value)

what I also want to be able to do is create a conditional formula like ...

sum(iif(Fields!Sort_Order.Value = "E1",Fields!hours_m2.Value,0))

When I create this on my report and preview it I get the following message in the field #Error.

Can someone please tell me where I've gone wrong and how to fix ... I know I can change the stored proc but I have 12 columns which I want to do the same thing with which would mean adding 12 columns to my stored proc.

Hello Derek,

In Visual Studio, click on the preview tab and run your report. Then look in the Output window (Ctrl+Alt+O), it should have a description as to what the error is. Can you post that error message?

Jarret

|||

One of the things I have found out about summing in ssrs is that it treats values of doubles and integers seperatley.

try changing the the "0" to "0.0" this will then tell ssrs it is a double type and your sum should work.

|||

description of error message

[rsAggregateOfMixedDataTypes] The Value expression for the textbox ‘textbox101’ uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type.

|||

I have found that using the following

sum(iif(Fields!Sort_Order.Value = "E1" or Fields!Sort_Order.Value = "E2" or Fields!Sort_Order.Value = "F1",cdbl(Fields!hours_m2.Value),cdbl(0.0)))

works

Thanks for you responses

|||

The problem is as Mainiac said. Try this:

=sum(iif(Fields!Sort_Order.Value = "E1", cDec(Fields!hours_m2.Value), cDec(0)))

Hope this helps.

Jarret

Conditional Statement in a Group

Hi,
This probably has a simple answer.
This is what I am trying to do on a report , I am trying to get a count of a
condition where IsUnderInvestigation = "yes" where the report is grouped by
"TEAM"
Dataset:
ID Name Team IsUnderInvestigation
1 John X Yes
2 Michael Y No
3 Peter X Yes
Then the report is grouped by TEAM to produce and then a column of count of
if the field "IsUnderInvestigation" is yes, then it increments the counter
to produce teh following report.
Team How Many Investigations for Team
X 2
Y 0
So as you can see, I need to group the data by TEAM and then have a count of
how many people within each team are under investigation.
Does anyone know how to put this conditional count statement in a cell of
the report?
Thanks in advance for any help.
Kind Regards
WarrenTry this:
=Sum(iif(Fields!UnderInvestigation = "Yes", 1, 0))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Warren Patterson" <des@.newsgroups.nospam> wrote in message
news:%23OKXMCeYFHA.4032@.tk2msftngp13.phx.gbl...
> Hi,
> This probably has a simple answer.
> This is what I am trying to do on a report , I am trying to get a count of
> a
> condition where IsUnderInvestigation = "yes" where the report is grouped
> by
> "TEAM"
> Dataset:
> ID Name Team IsUnderInvestigation
> 1 John X Yes
> 2 Michael Y No
> 3 Peter X Yes
> Then the report is grouped by TEAM to produce and then a column of count
> of
> if the field "IsUnderInvestigation" is yes, then it increments the counter
> to produce teh following report.
> Team How Many Investigations for Team
> X 2
> Y 0
>
> So as you can see, I need to group the data by TEAM and then have a count
> of
> how many people within each team are under investigation.
> Does anyone know how to put this conditional count statement in a cell of
> the report?
> Thanks in advance for any help.
> Kind Regards
> Warren
>
>
>
>
>|||Thank you thank you thank you thank you!
When I saw your solution, I thought....aish, I now look stoopid.
Kind Regards
Warren
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:%23nK5qviYFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Try this:
> =Sum(iif(Fields!UnderInvestigation = "Yes", 1, 0))
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Warren Patterson" <des@.newsgroups.nospam> wrote in message
> news:%23OKXMCeYFHA.4032@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > This probably has a simple answer.
> >
> > This is what I am trying to do on a report , I am trying to get a count
of
> > a
> > condition where IsUnderInvestigation = "yes" where the report is grouped
> > by
> > "TEAM"
> >
> > Dataset:
> > ID Name Team IsUnderInvestigation
> > 1 John X Yes
> > 2 Michael Y No
> > 3 Peter X Yes
> >
> > Then the report is grouped by TEAM to produce and then a column of count
> > of
> > if the field "IsUnderInvestigation" is yes, then it increments the
counter
> > to produce teh following report.
> >
> > Team How Many Investigations for Team
> > X 2
> > Y 0
> >
> >
> > So as you can see, I need to group the data by TEAM and then have a
count
> > of
> > how many people within each team are under investigation.
> >
> > Does anyone know how to put this conditional count statement in a cell
of
> > the report?
> >
> > Thanks in advance for any help.
> >
> > Kind Regards
> > Warren
> >
> >
> >
> >
> >
> >
> >
> >
> >
>

Conditional running total question

Hi there,

I have a problem with trying to get a total of a conditional value. My data output looks as follows:

HEAD OFFICE BRANCH (group1)
(Group 2 below)
Ford Mustang 2001 Blue Excellent
Toyota Raider 2005 Red Good
BMW 5.30 i 2006 Blue Excellent
Mazda MX5 2003 Yellow Good

WESTERN CAPE BRANCH
Ford Fiesta 16i 2002 Blue Good
Renault Clio 2.0d 2005 Red Poor
Nissan Hardbody 2001 Pink Good

I have been trying to find a way to get a value which would be a total number of blue cars for the Branches (group1) as well as the total number of blue cars for the whole report but to no avail with my limited experience. I have read up on people creating extra columns that does counts and sums etc but I am still struggling with aggregate inside aggreagate errors.

Please help me
Mike

Hi,

what about doing a plain SUM(IIF(Fields!autom.Value ="Red",1,0)) etc. in the group footer ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||Hi Jens,

Thanks for the reply. I should have maybe pointed out that I have used a list to do the grouping and can't seem to find a group footer. Is there another thing that I can try?

Sorry if this is a bit of a newbie question.
Mike

Conditional Page Throw

I'm looking for help on how to throw a page so that when printing duplex (both sides) a group will start on an odd page number eg 1, 3, 7, 9 etc. My large report needs to be split up into departments. I don't want a department starting on the reverse side of a sheet of paper.

Your help would be appreciated.

First make sure that the location of your table/list is 0,0.

Then edit the group and check both the properties "page break at start" and "page break at end". Since your table/list is at the top, "page break at start" will not be effective so your first page will have data and from the subsequent groups, the page break will be applied at the beggining and also at the end.

But you cannot stop the beginning page break if your group ends on page 2 (reverse of page 1) which means your second group (new department) will start from page 4 (reverse of page 2). I hope Microssoft provides an expression for page break conditions in future releases.

Shyam

Conditional page breaks

I want to set a page break at the change of a group conditionally based on a
parameter value. Is there any way to do this in Reporting Services?Any help here? Because I would *really* like to figure out how to do this.
Once again, I have a grouped table in a report layout. I would like to have
a page break at the end of a grouping happen conditionally based on a boolean
parameter that I pass to the report.

Conditional iif statement

Ok I have a sp that returns a number of records, each identfied as being part
of a group based on the Type field.
Type Name Amount
1 Test1 1.00
1 Test2 1.00
2 Test3 2.00
3 Test4 3.00
What I am using is iff(Type.value = 1, Price.value, 0) When I do this it
works fine but when I use them middle records iff(Type.value = 2,
Price.value, 0) it displays 1.00 as the price for type 2, and it should
display 2.00 for type 2.
Any help is greatly apprciated. Also, due to the formatting specifics I am
using single textboxes instead of a table or list.
DigivixTry this...
=iif(Fields!Type.Value=1, Fields!Price.Value,0)
the same with Value =2
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"DigitalVixen" <DigitalVixen@.discussions.microsoft.com> wrote in message
news:2B698793-0708-4538-9DCD-595819A758D8@.microsoft.com...
> Ok I have a sp that returns a number of records, each identfied as being
> part
> of a group based on the Type field.
> Type Name Amount
> 1 Test1 1.00
> 1 Test2 1.00
> 2 Test3 2.00
> 3 Test4 3.00
> What I am using is iff(Type.value = 1, Price.value, 0) When I do this it
> works fine but when I use them middle records iff(Type.value = 2,
> Price.value, 0) it displays 1.00 as the price for type 2, and it should
> display 2.00 for type 2.
> Any help is greatly apprciated. Also, due to the formatting specifics I
> am
> using single textboxes instead of a table or list.
> Digivix|||Sorry for the typo's but that is exactly what i am using and it is giving me
the first record's price only.
"Wayne Snyder" wrote:
> Try this...
> =iif(Fields!Type.Value=1, Fields!Price.Value,0)
> the same with Value =2
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "DigitalVixen" <DigitalVixen@.discussions.microsoft.com> wrote in message
> news:2B698793-0708-4538-9DCD-595819A758D8@.microsoft.com...
> > Ok I have a sp that returns a number of records, each identfied as being
> > part
> > of a group based on the Type field.
> >
> > Type Name Amount
> > 1 Test1 1.00
> > 1 Test2 1.00
> > 2 Test3 2.00
> > 3 Test4 3.00
> >
> > What I am using is iff(Type.value = 1, Price.value, 0) When I do this it
> > works fine but when I use them middle records iff(Type.value = 2,
> > Price.value, 0) it displays 1.00 as the price for type 2, and it should
> > display 2.00 for type 2.
> >
> > Any help is greatly apprciated. Also, due to the formatting specifics I
> > am
> > using single textboxes instead of a table or list.
> >
> > Digivix
>
>|||Can't you go back to a table and simulate the look & feel? I think if you
did it in the detail row it would pull the right values.
--
"Everyone knows something you don't know"
"DigitalVixen" wrote:
> Sorry for the typo's but that is exactly what i am using and it is giving me
> the first record's price only.
> "Wayne Snyder" wrote:
> > Try this...
> >
> > =iif(Fields!Type.Value=1, Fields!Price.Value,0)
> > the same with Value =2
> >
> >
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "DigitalVixen" <DigitalVixen@.discussions.microsoft.com> wrote in message
> > news:2B698793-0708-4538-9DCD-595819A758D8@.microsoft.com...
> > > Ok I have a sp that returns a number of records, each identfied as being
> > > part
> > > of a group based on the Type field.
> > >
> > > Type Name Amount
> > > 1 Test1 1.00
> > > 1 Test2 1.00
> > > 2 Test3 2.00
> > > 3 Test4 3.00
> > >
> > > What I am using is iff(Type.value = 1, Price.value, 0) When I do this it
> > > works fine but when I use them middle records iff(Type.value = 2,
> > > Price.value, 0) it displays 1.00 as the price for type 2, and it should
> > > display 2.00 for type 2.
> > >
> > > Any help is greatly apprciated. Also, due to the formatting specifics I
> > > am
> > > using single textboxes instead of a table or list.
> > >
> > > Digivix
> >
> >
> >|||Hi David,
Thank you for the reply, however I don't understand what is meant by
"simulate the look & feel", can you please be a little more specific?
Thanks
"David Bienstock" wrote:
> Can't you go back to a table and simulate the look & feel? I think if you
> did it in the detail row it would pull the right values.
> --
> "Everyone knows something you don't know"
>
> "DigitalVixen" wrote:
> > Sorry for the typo's but that is exactly what i am using and it is giving me
> > the first record's price only.
> >
> > "Wayne Snyder" wrote:
> >
> > > Try this...
> > >
> > > =iif(Fields!Type.Value=1, Fields!Price.Value,0)
> > > the same with Value =2
> > >
> > >
> > >
> > > --
> > > Wayne Snyder, MCDBA, SQL Server MVP
> > > Mariner, Charlotte, NC
> > > www.mariner-usa.com
> > > (Please respond only to the newsgroups.)
> > >
> > > I support the Professional Association of SQL Server (PASS) and it's
> > > community of SQL Server professionals.
> > > www.sqlpass.org
> > >
> > > "DigitalVixen" <DigitalVixen@.discussions.microsoft.com> wrote in message
> > > news:2B698793-0708-4538-9DCD-595819A758D8@.microsoft.com...
> > > > Ok I have a sp that returns a number of records, each identfied as being
> > > > part
> > > > of a group based on the Type field.
> > > >
> > > > Type Name Amount
> > > > 1 Test1 1.00
> > > > 1 Test2 1.00
> > > > 2 Test3 2.00
> > > > 3 Test4 3.00
> > > >
> > > > What I am using is iff(Type.value = 1, Price.value, 0) When I do this it
> > > > works fine but when I use them middle records iff(Type.value = 2,
> > > > Price.value, 0) it displays 1.00 as the price for type 2, and it should
> > > > display 2.00 for type 2.
> > > >
> > > > Any help is greatly apprciated. Also, due to the formatting specifics I
> > > > am
> > > > using single textboxes instead of a table or list.
> > > >
> > > > Digivix
> > >
> > >
> > >

conditional grouping (to group or not to group)

I have 3 groups: region, district, facility.
Depending upon user input, I want to either display the data in a drilldown
such as:
region 1
district 1
facility A data data data
facility C data data data
facility F data data data
district 2
facility B
facility D
...etc
or I want to only display the facility data without the drilldown and
without displaying which region and district those facilities belong to.
Obviously, this could be done by using 2 separate reports, but I need to have
this functionality for all of my reports. So if there is a simple way to
allow for it in the same report, I would like to do that.
ThanksThe closest you can get is a conditional grouping expression like this:
=iif(Parameters!GroupOnRegion.Value = True, Fields!Region.Value, 1)
Note: Grouping on a constant value will just generate 1 group that contains
all values.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Stads" <Stads@.discussions.microsoft.com> wrote in message
news:CC17E391-B188-492F-980E-C8F3B4298F97@.microsoft.com...
> I have 3 groups: region, district, facility.
> Depending upon user input, I want to either display the data in a
drilldown
> such as:
> region 1
> district 1
> facility A data data data
> facility C data data data
> facility F data data data
> district 2
> facility B
> facility D
> ...etc
> or I want to only display the facility data without the drilldown and
> without displaying which region and district those facilities belong to.
> Obviously, this could be done by using 2 separate reports, but I need to
have
> this functionality for all of my reports. So if there is a simple way to
> allow for it in the same report, I would like to do that.
> Thanks
>

conditional group summing problem

I am having a little difficulty with aggregate functions in a group. I have two columns, one for the current year and one for the previous year. the functions are as follows:

=Sum(iif(Fields!YEAR_DESIGNATION.Value="C",Fields!AMOUNT.Value,0)) // Current year sales

and

=Sum(iif(Fields!YEAR_DESIGNATION.Value="P",Fields!AMOUNT.Value,0)) // Previous year sales

The YEAR_DESIGNATION field is based on a sql server case statement that returns a "P" or a "C" depending on the year (invoice date). Anyway, the data looks perfectly normal, each row has a "P" or a "C" along with a value in the amount field. For some reason if the grouping contains both P" and "C" rows, I get #error where the data should be. If a row only contains all P's or all C's the totals work. I have done this before but for some reason I can't get this to work.

P.S. All fields have data (there are no nulls)

It sounds like the AMOUNT field value is not of type System.Int32, but either Int16 or Double or Decimal. Assuming you are conditionally aggregating double values, you have to ensure that the datatypes are always the same:

=Sum(iif(Fields!YEAR_DESIGNATION.Value="C", CDbl(Fields!AMOUNT.Value), 0.0))

-- Robert

|||That was it! Thanks again. Your always a very big help Robert.

conditional group summing problem

I am having a little difficulty with aggregate functions in a group. I have two columns, one for the current year and one for the previous year. the functions are as follows:

=Sum(iif(Fields!YEAR_DESIGNATION.Value="C",Fields!AMOUNT.Value,0)) // Current year sales

and

=Sum(iif(Fields!YEAR_DESIGNATION.Value="P",Fields!AMOUNT.Value,0)) // Previous year sales

The YEAR_DESIGNATION field is based on a sql server case statement that returns a "P" or a "C" depending on the year (invoice date). Anyway, the data looks perfectly normal, each row has a "P" or a "C" along with a value in the amount field. For some reason if the grouping contains both P" and "C" rows, I get #error where the data should be. If a row only contains all P's or all C's the totals work. I have done this before but for some reason I can't get this to work.

P.S. All fields have data (there are no nulls)

It sounds like the AMOUNT field value is not of type System.Int32, but either Int16 or Double or Decimal. Assuming you are conditionally aggregating double values, you have to ensure that the datatypes are always the same:

=Sum(iif(Fields!YEAR_DESIGNATION.Value="C", CDbl(Fields!AMOUNT.Value), 0.0))

-- Robert

|||That was it! Thanks again. Your always a very big help Robert.

Conditional Group Header

Here is my scenario. I have 3 Groupings, lets say Group A, B, and C.

Group A
Group B
Group C
Detail
Group Footer C
Group Footer B
Group Footer A

Group A and Group B will be starting from new page, but not group C. I am having problems when Group C change values on same page. Like on one page I have

Group A -Header
Group B -Header
Group C (value 1) header
Detail
Group C (value 1) footer
Group C (value 2) header
Page footer
and then on next page, same Group C (value 2) header and then detail. I am not sure how to check like, if Group C (value 2) header does not have detail for it or Group C (value 2) header is followed by page footer then skip that header and put Group C (value 2) header on new page. Could someone please guide me how to code it please.

Please let me know if I could not explain my scanrio properly and I will try to explain it in different way.

Thank you in advance.I think I already answered this on another forum. Please check.

Conditional group footer sum

HI,
I am working with a developer who has an interesting problem. They have
data field in a sql table that is of a numeric value a second field that
tells whether or not the previous value is a debit or credit for a general
ledger.
They want to show on a report a sum of the numeric field in a group footer.
We have tried writing a conditional formula(=Iif(FieldB="dr",Sum(FieldA),o))
for a hidden field on the report in the detail row of a table and then using
the Reportitems Syntax to display that fields value in the group footer, but
we get an out of scope error.
Looking for possible suggestions, code sample, or alternatives. Changing
the data in the SQL table is not a possibility.
Thanks!!I recently tried doing something similar to this but I was getting a data
type error. I resolved it by making the following change:
Orig: sum(iif(Fields!Type.Value = 'Dr', Fields!Amount.Value, 0) --Got errors
New: sum(iif(Fields!Type.Value = 'Dr', Fields!Amount.Value,
Fields!Amount.Value*0)
I don't know why mulitplying by 0 gives the correct data type, but simply
putting a 0 in does not, but that is only variation (0, 0.0, 0.00, etc...) I
could find that worked.
"Mark" wrote:
> HI,
> I am working with a developer who has an interesting problem. They have
> data field in a sql table that is of a numeric value a second field that
> tells whether or not the previous value is a debit or credit for a general
> ledger.
> They want to show on a report a sum of the numeric field in a group footer.
> We have tried writing a conditional formula(=Iif(FieldB="dr",Sum(FieldA),o))
> for a hidden field on the report in the detail row of a table and then using
> the Reportitems Syntax to display that fields value in the group footer, but
> we get an out of scope error.
> Looking for possible suggestions, code sample, or alternatives. Changing
> the data in the SQL table is not a possibility.
> Thanks!!|||The reason why multiplying with 0 works is that it preserves the original
datatype of the numeric field (which could be anything like UInt16, Decimal,
etc.).
This should work (the constant value 0.0 is a System.Double at runtime):
=sum(iif(Fields!Type.Value = 'Dr', CDbl(Fields!Amount.Value), 0.0)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
news:9EAB31A6-E7CA-4912-82BC-A678EAE9CA0C@.microsoft.com...
> I recently tried doing something similar to this but I was getting a data
> type error. I resolved it by making the following change:
> Orig: sum(iif(Fields!Type.Value = 'Dr', Fields!Amount.Value, 0) --Got
errors
> New: sum(iif(Fields!Type.Value = 'Dr', Fields!Amount.Value,
> Fields!Amount.Value*0)
> I don't know why mulitplying by 0 gives the correct data type, but simply
> putting a 0 in does not, but that is only variation (0, 0.0, 0.00, etc...)
I
> could find that worked.
> "Mark" wrote:
> > HI,
> >
> > I am working with a developer who has an interesting problem. They have
> > data field in a sql table that is of a numeric value a second field that
> > tells whether or not the previous value is a debit or credit for a
general
> > ledger.
> >
> > They want to show on a report a sum of the numeric field in a group
footer.
> > We have tried writing a conditional
formula(=Iif(FieldB="dr",Sum(FieldA),o))
> > for a hidden field on the report in the detail row of a table and then
using
> > the Reportitems Syntax to display that fields value in the group
footer, but
> > we get an out of scope error.
> >
> > Looking for possible suggestions, code sample, or alternatives.
Changing
> > the data in the SQL table is not a possibility.
> >
> > Thanks!!

Conditional group by

Hi,

Can anyone help me in writing this sql query, i want to group my select statement depending on the parameter user is passing.

Say when @.group='Cell' I want to group by CellID otherwise different conditions, something like below query but it is not working. I know we can't use case directly in where but please let me know if there is any other work around.

I don't want to use dynamic query and also this is big SP so i dont want to break sp in four conditions.

declare @.group varchar(10)

set @.group='Cell'

select cellid,sum(count)

FROM CellImpressionFact

WHERE ImpressionTypeLevelId = 2

AND ImpressionTypeId = 4

group by

case when group='Cell' then GROUP BY CellId

else group by activityID

end

This is not a good idea really. I would use dynamic SQL to provide this kind of capability if you really need to. It is possible (see code) but I would be very concerned about performance.

create table test
(
grouper int,
grouper2 int,
value decimal(10,5)
)
go
insert into test
select 1,1,10
union all
select 1,2,10
union all
select 1,3,10
union all
select 2,1,10
go
declare @.groupby varchar(10)
set @.groupBy = 'grouper2'

select max(grouper) as grouper,
max(grouper2) as grouper2,
sum(value) as valueSum
from test
group by case when @.groupBy = 'grouper' then grouper else grouper2 end

Note that the grouper2 column is of any value when you group by grouper, and vice versa (say it five times fast.)

Conditional formatting when visability = True or False

I have groupings in which the first column of each new group hides duplicates
(all in one report). I would like the first listing (row) of each new group
to be highlighted (i.e. the one without the hidden field).
Is there any way to accomplish this task?you might test to see if a value in the table is = the min of a column's values (if your data is ordered on one of your columns)
you could also include a rowtype column in your datasource to examine
Your hidden expression would be like:
=iif(Fields!fieldsortedby.value = min(Fields!fieldsortedby.value),false,true)
--
Message posted via http://www.sqlmonster.com

Conditional DistinctCount

I want a distinctCount in a group for a criteria. How can i do it
I am doing something like this
=CountDistinct(IIF(Fields!CATE.Value='N'),Fields!ERNR.Value,nothing),
"grpResCat")
Is this right?Try this:
=CountDistinct(IIF(Trim(CStr(Fields!CATE.Value)) = "N", Fields!ERNR.Value,
Nothing), "grpResCat")
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Anand Prabhu" <AnandPrabhu@.discussions.microsoft.com> wrote in message
news:594F0D45-8C6E-4988-A41D-DA03E858EC86@.microsoft.com...
> I want a distinctCount in a group for a criteria. How can i do it
> I am doing something like this
> =CountDistinct(IIF(Fields!CATE.Value='N'),Fields!ERNR.Value,nothing),
> "grpResCat")
> Is this right?
>

Conditional Count for Row Group together with Column Group

Division =Fields!Status.Value
Total
=Fields!Division.Value
=Count(iif("Status"=Fields!Status.Value,1,0),"Division")
Total:
the above is the matrix that i had done to count some values. The
"=Fields!Status.Value" is a column group call Status and the
"=Fields!Division.Value" is a row group call Division. My column goup
contains 3 fields which are "Accepted", "Rejected" and "Pending" and my row
group contais all my division fields. My database had a record of all the
submissions that had been submitted by the different divisions and what is
their submission current status which is either Accepted, Rejected or Pending.
Now what i want to achieved is that i want to display the Number of
Accepted, Rejected and Pending submissions for each Division by using the
Count function. But the expression that i had did does not give me the actual
result. i need some help on what correct expression should i use.
Thansk in advance for all the help and time consumed.ok, managed to solve it with a simple expression =Count(Fields!Status.Value)
Thanks anyway. =)
"JiaN" wrote:
> Division =Fields!Status.Value
> Total
> =Fields!Division.Value
> =Count(iif("Status"=Fields!Status.Value,1,0),"Division")
> Total:
> the above is the matrix that i had done to count some values. The
> "=Fields!Status.Value" is a column group call Status and the
> "=Fields!Division.Value" is a row group call Division. My column goup
> contains 3 fields which are "Accepted", "Rejected" and "Pending" and my row
> group contais all my division fields. My database had a record of all the
> submissions that had been submitted by the different divisions and what is
> their submission current status which is either Accepted, Rejected or Pending.
> Now what i want to achieved is that i want to display the Number of
> Accepted, Rejected and Pending submissions for each Division by using the
> Count function. But the expression that i had did does not give me the actual
> result. i need some help on what correct expression should i use.
> Thansk in advance for all the help and time consumed.

Conditional column select

Is there a statement to change the column in a select clause?

For example:

select Groups, sum ((if group like '%total%' then select TotalHours else if group like '%Direct%' then select DirectHours endif endif)) as Hours, count(*) from tblGroups. group by Groups.

Examples

A. Use a SELECT statement with a simple CASE function

Within a SELECT statement, a simple CASE function allows only an equality check; no other comparisons are made. This example uses the CASE function to alter the display of book categories to make them more understandable.

USE pubs
GO
SELECT Category = 
 CASE type
 WHEN 'popular_comp' THEN 'Popular Computing'
 WHEN 'mod_cook' THEN 'Modern Cooking'
 WHEN 'business' THEN 'Business'
 WHEN 'psychology' THEN 'Psychology'
 WHEN 'trad_cook' THEN 'Traditional Cooking'
 ELSE 'Not yet categorized'
 END,
 CAST(title AS varchar(25)) AS 'Shortened Title',
 price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price
COMPUTE AVG(price) BY type
GO

B. Use a SELECT statement with simple and searched CASE function
Within a SELECT statement, the searched CASE function allows values to be replaced in the result set based on comparison values. This example displays the price (a money column) as a text comment based on the price range for a book.
USE pubs
GO
SELECT 'Price Category' = 
 CASE 
 WHEN price IS NULL THEN 'Not yet priced'
 WHEN price < 10 THEN 'Very Reasonable Title'
 WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
 ELSE 'Expensive book!'
 END,
 CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price
GO

C. Use CASE with SUBSTRING and SELECT
This example uses CASE and THEN to produce a list of authors, the book identification numbers, and the book types each author has written. 
USE pubs
SELECT SUBSTRING((RTRIM(a.au_fname) + ' '+ 
 RTRIM(a.au_lname) + ' '), 1, 25) AS Name, a.au_id, ta.title_id,
 Type = 
 CASE 
 WHEN SUBSTRING(ta.title_id, 1, 2) = 'BU' THEN 'Business'
 WHEN SUBSTRING(ta.title_id, 1, 2) = 'MC' THEN 'Modern Cooking'
 WHEN SUBSTRING(ta.title_id, 1, 2) = 'PC' THEN 'Popular Computing'
 WHEN SUBSTRING(ta.title_id, 1, 2) = 'PS' THEN 'Psychology'
 WHEN SUBSTRING(ta.title_id, 1, 2) = 'TC' THEN 'Traditional Cooking'
 END
FROM titleauthor ta JOIN authors a ON ta.au_id = a.au_id

|||

How would I change the static text into the column that I want?

If I used this for example:

SELECT Category =
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN Price
END,
CAST(title AS varchar(25)) AS 'Shortened Title',
price AS Price
FROM titles

I would like my result to be like:

Category Shortened Title Price

2.9900 The Gourmet Microwave 2.9900
19.9900 Silicon Valley Gastronomi 19.9900
Popular Computing Secrets of Silicon Valley 20.0000
Popular Computing But Is It User Friendly? 22.9500