Configuration question

I've looked for the answer to this question, but can't find it. How do you
limit the number of report requests that execute at a given time? For
example, if I schedule 100 jobs to execute via a shared schedule at the same
time. How can I tell RS to only run 5 at a time until all 100 have
completed?
ThanksBy default RS will only take on work when it has the resources to do so.
Currently we rely on CPU usage to determine this. We will start working on
at most 2 * number of CPU. If the cpu usage stays low we will continue to
try and do more work. You can limit the total number RS will work on in the
RSReportServer.config file. The MaxQueueThreads tells RS at most how many
threads to use for processing reports. 0 means we will do as described
above, any other number will limit the total work that we can work on at one
time.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"troark" <t.roark@.excite.com.n0sp@.m> wrote in message
news:#R4snWxfEHA.1188@.TK2MSFTNGP11.phx.gbl...
> I've looked for the answer to this question, but can't find it. How do
you
> limit the number of report requests that execute at a given time? For
> example, if I schedule 100 jobs to execute via a shared schedule at the
same
> time. How can I tell RS to only run 5 at a time until all 100 have
> completed?
> Thanks
>|||Great explaination. Thanks!
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:OTHD4byfEHA.1428@.TK2MSFTNGP10.phx.gbl...
> By default RS will only take on work when it has the resources to do so.
> Currently we rely on CPU usage to determine this. We will start working
on
> at most 2 * number of CPU. If the cpu usage stays low we will continue to
> try and do more work. You can limit the total number RS will work on in
the
> RSReportServer.config file. The MaxQueueThreads tells RS at most how many
> threads to use for processing reports. 0 means we will do as described
> above, any other number will limit the total work that we can work on at
one
> time.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "troark" <t.roark@.excite.com.n0sp@.m> wrote in message
> news:#R4snWxfEHA.1188@.TK2MSFTNGP11.phx.gbl...
> > I've looked for the answer to this question, but can't find it. How do
> you
> > limit the number of report requests that execute at a given time? For
> > example, if I schedule 100 jobs to execute via a shared schedule at the
> same
> > time. How can I tell RS to only run 5 at a time until all 100 have
> > completed?
> >
> > Thanks
> >
> >
>sqlsql

conditonal counting

is there a way to have conditional counts or conditional running values?
such as counting the number of active customers verses the total number of
customers...
IIF(Fields!CustomerStatus.Value = "Active", Count(Fields!CustomerID.Value),
Nothing)
OR
IIF(Fields!CustomerStatus.Value = "Active",
RunningValue(Fields!CustomerID.Value, Count, Nothing), Nothing)
thanks,
--jimmyI haven't tried this but maybe
Sum(iif(Fields!CustomerStatus.Value="Active",1,0))
Otherwise create a computed column with the IIF,
then SUm( the computed column)
(You might also be able to use Excel's sumif function directly>)
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"jimmy" <jimmy@.discussions.microsoft.com> wrote in message
news:4E42351E-2196-42D8-AB8E-B44B34DDC1E3@.microsoft.com...
> is there a way to have conditional counts or conditional running values?
> such as counting the number of active customers verses the total number
of
> customers...
> IIF(Fields!CustomerStatus.Value = "Active",
Count(Fields!CustomerID.Value),
> Nothing)
> OR
> IIF(Fields!CustomerStatus.Value = "Active",
> RunningValue(Fields!CustomerID.Value, Count, Nothing), Nothing)
>
> thanks,
> --jimmy|||worked perfectly, thanks!
"Wayne Snyder" wrote:
> I haven't tried this but maybe
> Sum(iif(Fields!CustomerStatus.Value="Active",1,0))
> Otherwise create a computed column with the IIF,
> then SUm( the computed column)
> (You might also be able to use Excel's sumif function directly>)
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "jimmy" <jimmy@.discussions.microsoft.com> wrote in message
> news:4E42351E-2196-42D8-AB8E-B44B34DDC1E3@.microsoft.com...
> >
> > is there a way to have conditional counts or conditional running values?
> > such as counting the number of active customers verses the total number
> of
> > customers...
> >
> > IIF(Fields!CustomerStatus.Value = "Active",
> Count(Fields!CustomerID.Value),
> > Nothing)
> >
> > OR
> >
> > IIF(Fields!CustomerStatus.Value = "Active",
> > RunningValue(Fields!CustomerID.Value, Count, Nothing), Nothing)
> >
> >
> > thanks,
> > --jimmy
>
>

Conditionally Expand a Table

I have a form that has a table in the center with text boxes above and below
where the table may contain 1 to n number of rows. When the table has 4 or
more rows the page looks well balanced but anything less makes it look too
compressed.
I would like to conditionally add some blank table rows and have been
attempting this by adding rows and setting the visibility property using
RowCount(). Problem is I have 2 groups in this table so have separate row
counts. I did a test and added RowCount() to a text box in the table header
and it shows the total for the entire table. Is there a syntax for RowCount
that will allow me to reference the header RowCount from each of the group
row visibility properties?
Or is there a better way to set a minimum table size?
Thankstry putting your items inside of a rectangle as a group - I think your table
will expand and contract within the bounds of the rectangle and keep your
text boxed on either side too.
"Mike Harbinger" wrote:
> I have a form that has a table in the center with text boxes above and below
> where the table may contain 1 to n number of rows. When the table has 4 or
> more rows the page looks well balanced but anything less makes it look too
> compressed.
> I would like to conditionally add some blank table rows and have been
> attempting this by adding rows and setting the visibility property using
> RowCount(). Problem is I have 2 groups in this table so have separate row
> counts. I did a test and added RowCount() to a text box in the table header
> and it shows the total for the entire table. Is there a syntax for RowCount
> that will allow me to reference the header RowCount from each of the group
> row visibility properties?
> Or is there a better way to set a minimum table size?
> Thanks
>
>

Conditionally end a report

I am new to CR so this might be a really stupid question. CR version 10.

I want to end the report after X number of records based on a parameter field. Specifically I want to list customers in order of total sales but specify how many customers to print out. Like, Top 100 Customers by Sales.

I know I could print all records to the screen then choose to only print X number of pages but that's not possible if the report goes right to the printer.

Thank you.Type TOP into Crystal's online help (index) and see what you get.l

Conditionally count rows in a table

I am building a Table Report where I need to "count" the number of cells in a column conditionally.

I have a column of data where the values will be "Orange", "Apple", "Banana", NULL
The pseudocode would be something like this:

iCountOfOranges as Integer
iCountOfApples as Integer
iCountOfBananas as Integer

IF Cell.Value = "Orange" THEN

iCountOfOranges = iCountOfOranges + 1

ELSE IF Cell.Value = "Apple" THEN

iCountOfApples = iCountOfApples + 1

ELSE IF Cell.Value = "Banana" THEN

iCountOfBananas = iCountOfBananas + 1

The 3 count values would then be displayed in 3 footer rows at the bottom of the table.

Thanks

There are a couple of ways you could go about this. I generally prefer doing things such as this in SQL. Simply add another data set that returns the counts and display them.

If you can't change your query or just feel compelled to do it all within SSRS, I believe the easiest way to do it involves the RunningValue function. The following statment will accuratly count the number of times "apple" is returned:

Code Snippet

=RunningValue(

IIF(

IIF(

Fields!fruit.Value IS NOTHING,

"",

LCase(Fields!fruit.Value)

).Equals("apple"),

1,

0),

SUM,

"DataSet1"

)

Note that it will not even miss "Apple" because of the LCase function call.

Let me know how this works for you. You could also use custom code, but I will leave that as an exercise for a later date.

Good luck!

Larry Smithmier

|||Thanks Larry - that worked like a charm!

I have isolated the data from the end user (report building) by only exposing Stored Procedures. I'm trying to limit the number of Stored Procs that our data access interface will have.

I do have an additional question for you. Within a Report, Is there a way to run SQL statements against the dataset returned by a stored procedure. I had zero database experience when I started on this project (and hey - out of our entire team, I have the most DB experience so go figure) so I am learning as I go. I'm building a data repository that we will then have a reporting front end to do all kinds of statistical reports for those guys that sit in the Ivory Towers.

Thanks again!

Marty
|||

Hello Marty,

You can't use a stored procedure in a SQL statement, but you can use a Function. Functions look and feel like stored procedures, but can replace tables in SQL statements. For example, the following defines a function:

Code Snippet

CREATE FUNCTION TestFunction
(
)
RETURNS TABLE
AS
RETURN
(
SELECT * from Complex
)
GO

and here is the function used in a select statement:

Code Snippet

SELECT

*

FROM

dbo.TestFunction() AS TestFunction

Good luck!

Larry Smithmier

Conditionally count rows in a table

I am building a Table Report where I need to "count" the number of cells in a column conditionally.

I have a column of data where the values will be "Orange", "Apple", "Banana", NULL
The pseudocode would be something like this:

iCountOfOranges as Integer
iCountOfApples as Integer
iCountOfBananas as Integer

IF Cell.Value = "Orange" THEN

iCountOfOranges = iCountOfOranges + 1

ELSE IF Cell.Value = "Apple" THEN

iCountOfApples = iCountOfApples + 1

ELSE IF Cell.Value = "Banana" THEN

iCountOfBananas = iCountOfBananas + 1

The 3 count values would then be displayed in 3 footer rows at the bottom of the table.

Thanks

There are a couple of ways you could go about this. I generally prefer doing things such as this in SQL. Simply add another data set that returns the counts and display them.

If you can't change your query or just feel compelled to do it all within SSRS, I believe the easiest way to do it involves the RunningValue function. The following statment will accuratly count the number of times "apple" is returned:

Code Snippet

=RunningValue(

IIF(

IIF(

Fields!fruit.Value IS NOTHING,

"",

LCase(Fields!fruit.Value)

).Equals("apple"),

1,

0),

SUM,

"DataSet1"

)

Note that it will not even miss "Apple" because of the LCase function call.

Let me know how this works for you. You could also use custom code, but I will leave that as an exercise for a later date.

Good luck!

Larry Smithmier

|||Thanks Larry - that worked like a charm!

I have isolated the data from the end user (report building) by only exposing Stored Procedures. I'm trying to limit the number of Stored Procs that our data access interface will have.

I do have an additional question for you. Within a Report, Is there a way to run SQL statements against the dataset returned by a stored procedure. I had zero database experience when I started on this project (and hey - out of our entire team, I have the most DB experience so go figure) so I am learning as I go. I'm building a data repository that we will then have a reporting front end to do all kinds of statistical reports for those guys that sit in the Ivory Towers.

Thanks again!

Marty
|||

Hello Marty,

You can't use a stored procedure in a SQL statement, but you can use a Function. Functions look and feel like stored procedures, but can replace tables in SQL statements. For example, the following defines a function:

Code Snippet

CREATE FUNCTION TestFunction
(
)
RETURNS TABLE
AS
RETURN
(
SELECT * from Complex
)
GO

and here is the function used in a select statement:

Code Snippet

SELECT

*

FROM

dbo.TestFunction() AS TestFunction

Good luck!

Larry Smithmier

Conditional Selection

Hi,
Hi,
I'm trying to construct a query (in a stored procedure) which will have a nu
mber of
selection criteria based on input parameters. There are a number of these p
arameters
whose selection conditions they represent which all have to be true for a ro
w to be
returned in the resultset.
The basic query is:
SELECT Store, StoreNumber
FROM Stores
WHERE ...
I'm trying to come up with the WHERE clause.
For example, I want to define a parameter named @.ExcludeSpecialties which if
it has
the value 1, means to return all stores but exclude stores whose StoreNumber
is in
the list (800, 802, 804). If the parameter has the value 0, then it means "
don't
care" and all StoreNumbers should be returned.
One could certainly argue that there probably should have been an column in
the
Stores row to indicate the store is a specialty store, rather than using a h
ard-wired
list of numbers. But the current data schema cannot be easily changed. Bes
ides, the
list never changes.
Indeed, there is a Franchise bit column in the row which is selected by anot
her
parameter called @.ExcludeFranchise whose WHERE predicate could be written as
:
WHERE Franchise = CASE WHEN @.ExcludeFranchise = 1 THEN 0 ELSE Franchise END
and if all the parameters were like this, I wouldn't be posting. Sadly, for
the
Specialties test I'm stuck with a NOT IN list.
This is easy enough to do in an IF/ELSE block, but there are several such si
milar
parameters whose values may be specified in any combination. This, I think,
makes
IF/ELSE impractical as the number of IF/ELSE statements to handle all possib
le
combinations would grow very quickly.
I'm hoping there is a simple solution to this NOT IN list, and it's just tha
t I can't
see it.
Can anyone help?
Thanks,
-- JeffTry this first ( Several popular approaches are details here ):
http://www.sommarskog.se/dyn-search.html
Anith|||try this in your where clause. Let me know if this helps
((@.ExcludeSpecialties = 0) or (storenumber not in (800, 802, 804)))|||You could store the specialties flag in a seperate table, with StoreNumber
as the key, then query against it instead of using the hardcoded list. This
way, when a new specialty store opens, or one of the existing stores
changes, you will just insert a row into the table and not have to touch the
code. It would be better to have it in the original table, but if you can't
change the original, maybe adding a new table is an option...
create table SpecialtyStores
(StoreNumber integer, Specialty bit) -- add PK and FK info here
SELECT Store, StoreNumber
FROM Stores
left outer join SpecialtyStores as spec
on stores.StoreNumber = spec.StoreNumber
WHERE
Specialty = CASE WHEN @.ExcludeFranchise = 1 THEN 0 ELSE 1 END
"Jeff Mason" <je.mason@.comcast.net> wrote in message
news:vvl1525lk0m8baqqpv5vs5q1g36a9c6jpa@.
4ax.com...
> Hi,
> Hi,
> I'm trying to construct a query (in a stored procedure) which will have a
number of
> selection criteria based on input parameters. There are a number of these
parameters
> whose selection conditions they represent which all have to be true for a
row to be
> returned in the resultset.
> The basic query is:
> SELECT Store, StoreNumber
> FROM Stores
> WHERE ...
> I'm trying to come up with the WHERE clause.
> For example, I want to define a parameter named @.ExcludeSpecialties which
if it has
> the value 1, means to return all stores but exclude stores whose
StoreNumber is in
> the list (800, 802, 804). If the parameter has the value 0, then it means
"don't
> care" and all StoreNumbers should be returned.
> One could certainly argue that there probably should have been an column
in the
> Stores row to indicate the store is a specialty store, rather than using a
hard-wired
> list of numbers. But the current data schema cannot be easily changed.
Besides, the
> list never changes.
> Indeed, there is a Franchise bit column in the row which is selected by
another
> parameter called @.ExcludeFranchise whose WHERE predicate could be written
as:
> WHERE Franchise = CASE WHEN @.ExcludeFranchise = 1 THEN 0 ELSE Franchise
END
> and if all the parameters were like this, I wouldn't be posting. Sadly,
for the
> Specialties test I'm stuck with a NOT IN list.
> This is easy enough to do in an IF/ELSE block, but there are several such
similar
> parameters whose values may be specified in any combination. This, I
think, makes
> IF/ELSE impractical as the number of IF/ELSE statements to handle all
possible
> combinations would grow very quickly.
> I'm hoping there is a simple solution to this NOT IN list, and it's just
that I can't
> see it.
> Can anyone help?
> Thanks,
> -- Jeff|||On Thu, 27 Apr 2006 08:13:02 -0700, Omnibuzz <Omnibuzz@.discussions.microsoft
.com>
wrote:

>try this in your where clause. Let me know if this helps
>((@.ExcludeSpecialties = 0) or (storenumber not in (800, 802, 804)))
Duh.
That did it. I knew it was something simple. I was having a Brain Fog, I gu
ess.
Thank you.
-- Jeff

Conditional query on ASPNET page

Dear Friends,

I am working on search customer information page.

I have 5 search options,

Name,

Email,

Order Number,

Product Name,

Order Date

I am using check boxes, I need to allow admin to enter above information and click on search,

How will I make my query and sends to DB server to pull up records which satisfies where clause:

For example,

Select * from orders where Email = #Email#

This is simple, but I can not hardcode all queries, I don’t know in advance what different search option ADMIN may choose.

Any suggestion for logic or query make up,

Thanks,

Fahim.

So here are a couple suggestions that might help you with your task:

1. Look into calling system stored procedure sp_columns (http://msdn2.microsoft.com/en-us/library/ms176077.aspx) on the table to get the column metadata information.

Using this information you can then dynamically generate a query based on the check boxes selected by the user.

2, If you schema is simple and you already know the column information, then all you need to do is have a string that concatenates differect WHERE clauses based on the options selected and then submit the query when the user hits the search button.

HTH,

Conditional Query

Hi,

I'm trying to construct a query (in a stored procedure) which will have a number of
selection criteria based on input parameters. There are a number of these parameters
whose selection conditions they represent which all have to be true for a row to be
returned in the resultset.

The basic query is:

SELECT Store, StoreNumber
FROM Stores
WHERE ...

I'm trying to come up with the WHERE clause.

For example, I want to define a parameter named @.ExcludeSpecialties which if it has
the value 1, means to return all stores but exclude stores whose StoreNumber is in
the list (800, 802, 804). If the parameter has the value 0, then it means "don't
care" and all StoreNumbers should be returned.

One could certainly argue that there probably should have been an column in the
Stores row to indicate the store is a specialty store, rather than using a hard-wired
list of numbers. But the current data schema cannot be easily changed. Besides, the
list never changes.

Indeed, there is a Franchise bit column in the row which is selected by another
parameter called @.ExcludeFranchise whose WHERE predicate could be written as:

WHERE Franchise = CASE WHEN @.ExcludeFranchise = 1 THEN 0 ELSE Franchise END

and if all the parameters were like this, I wouldn't be posting. Sadly, for the
Specialties test I'm stuck with a NOT IN list.

This is easy enough to do in an IF/ELSE block, but there are several such similar
parameters whose values may be specified in any combination. This, I think, makes
IF/ELSE impractical as the number of IF/ELSE statements to handle all possible
combinations would grow very quickly.

I'm hoping there is a simple solution to this NOT IN list, and it's just that I can't
see it.

Can anyone help?

Thanks,

-- JeffJeff Mason (je.mason@.comcast.net) writes:
> I'm trying to construct a query (in a stored procedure) which will have
> a number of selection criteria based on input parameters. There are a
> number of these parameters whose selection conditions they represent
> which all have to be true for a row to be returned in the resultset.

I have an article on by web site that discusses a couple of alternatives,
both with static and dynamic SQL:
http://www.sommarskog.se/dyn-search.html

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Thu, 27 Apr 2006 11:10:01 -0400, Jeff Mason wrote:

(snip)
>For example, I want to define a parameter named @.ExcludeSpecialties which if it has
>the value 1, means to return all stores but exclude stores whose StoreNumber is in
>the list (800, 802, 804). If the parameter has the value 0, then it means "don't
>care" and all StoreNumbers should be returned.

Hi Jeff,

WHERE ( @.ExcludeSpecialties = 0 OR StoreNumber NOT IN (800, 802, 804) )

(snip)
>Indeed, there is a Franchise bit column in the row which is selected by another
>parameter called @.ExcludeFranchise whose WHERE predicate could be written as:
>WHERE Franchise = CASE WHEN @.ExcludeFranchise = 1 THEN 0 ELSE Franchise END
>and if all the parameters were like this, I wouldn't be posting. Sadly, for the
>Specialties test I'm stuck with a NOT IN list.

That is indeed a common method to write such queries.

Do read the article Erland posted a link to - it describes a bunch of
methods to achieve what you need, with all their strengths and
weaknesses. Good stuff!

--
Hugo Kornelis, SQL Server MVP

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

Hi -
I'm trying to generate invoice reports where there are two types of
line items (with a different number of columns). I am basically looking
to have two sets of tables as follows:
Case A - at least one of each item
Table Items 1
<PAGE BREAK>
Table Items 2
Case B - items 2 exist, but no items 1
Table Items 2
Case C - items 1 exist, but no items 2
Table Items 1
I've spent hours trying to figure out how to accomplish this - I have
played with putting Table 1 in an object with conditional visibility,
but that doesn't seem to suppress a page break. I've tried a "hidden
group" but no luck there either. I've also tried having Table items 1
in a subreport.
I'm open to suggestion if you don't think I am going about this the
right way - I would really appreciate any help!
Thanks
BNo conditional page breaks. I've been asking for them since the first
release a couple years ago. It is the main reason we have not migrated. If
you can't control the layout of the report it's pointless. I've tried many
workarounds but it seems like the developers went out of their way to not let
you programatically control them. Be it if you want to remove them as in
HTML reports or insert them manually.
Good luck on your quest. If you find something let us know.
"bigbrorpi@.gmail.com" wrote:
> Hi -
> I'm trying to generate invoice reports where there are two types of
> line items (with a different number of columns). I am basically looking
> to have two sets of tables as follows:
> Case A - at least one of each item
> Table Items 1
> <PAGE BREAK>
> Table Items 2
> Case B - items 2 exist, but no items 1
> Table Items 2
> Case C - items 1 exist, but no items 2
> Table Items 1
>
> I've spent hours trying to figure out how to accomplish this - I have
> played with putting Table 1 in an object with conditional visibility,
> but that doesn't seem to suppress a page break. I've tried a "hidden
> group" but no luck there either. I've also tried having Table items 1
> in a subreport.
> I'm open to suggestion if you don't think I am going about this the
> right way - I would really appreciate any help!
> Thanks
> B
>|||See if this can help :
You can insert a rectangle into a rectangle, and set a page break in the
inner rectangle and visibility in the outer and paste this empty nested
rectangles
in between subreports or tables . If Visible expression is True, the page
break will
work. if Visible expression is False , it will ignore a Page Break. So,
this nested construction will work as a conditional page break ( for RSS
2000) .

Conditional number formatting

How can I use conditional number formatting if one of the formats contain a
comma?
The iif statement conditions are separated by commas. If one of the
conditionals should have the number format:
#,##0,;(#,##0,);0
While the other condition should format to percentage:
#.0%;(#.0%);0.0%
how would I write the iif statement? Or is there another way to format this?I just answered my own question. I needed to put the number format syntax in
double qoutes.
"RJB" wrote:
> How can I use conditional number formatting if one of the formats contain a
> comma?
> The iif statement conditions are separated by commas. If one of the
> conditionals should have the number format:
> #,##0,;(#,##0,);0
> While the other condition should format to percentage:
> #.0%;(#.0%);0.0%
> how would I write the iif statement? Or is there another way to format this?

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 Formatting

Hi
I am trying to format a negative number ie -9999 with the following format
and am getting -(9,999).
=iif(Fields!ActualQty.Value < 0, "(0,000)"," 0,000")
Is there a way to get rid of the negative sign and only show the brackets?
And where can I find are some detailed examples for conditional formatting?
Thanks
FionaThe output is culture dependent. For en-US culture just try this formatcode:
0,000;(0,000)
More details and examples can be found on MSDN:
[1]
http://msdn.microsoft.com/library/en-us/cpguide/html/cpconcustomnumericformatstringsoutputexample.asp
[2]
http://msdn.microsoft.com/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp
[3]
http://msdn.microsoft.com/library/en-us/cpguide/html/cpconstandardnumericformatstringsoutputexample.asp
[4]
http://msdn.microsoft.com/library/en-us/cpguide/html/cpconstandardnumericformatstrings.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Fiona Harrison" <FionaHarrison@.discussions.microsoft.com> wrote in message
news:E9289E77-3BDD-42B3-908F-8918AEAF8904@.microsoft.com...
> Hi
> I am trying to format a negative number ie -9999 with the following format
> and am getting -(9,999).
> =iif(Fields!ActualQty.Value < 0, "(0,000)"," 0,000")
> Is there a way to get rid of the negative sign and only show the brackets?
> And where can I find are some detailed examples for conditional
formatting?
> Thanks
> Fiona|||try
Expression
=iif(Fields!ActualQty.Value < 0, "(" & Fields!ActualQty.Value & ")",
Fields!ActualQty.Value )
And format the field as 0,000
"Fiona Harrison" wrote:
> Hi
> I am trying to format a negative number ie -9999 with the following format
> and am getting -(9,999).
> =iif(Fields!ActualQty.Value < 0, "(0,000)"," 0,000")
> Is there a way to get rid of the negative sign and only show the brackets?
> And where can I find are some detailed examples for conditional formatting?
> Thanks
> Fiona|||Here's what I use.
with two decimals:
$#,#.##;($#,#.##);$0.00
without no decimals:
$#,#;($#,#);$0
one customer likes zeros to be blank, like this:
$#,#;($#,#);
I like zeros to be "zero", like this:
$#,#;($#,#);Zero
BR//Jerry|||Hi people
Thanks for your help.
I put the following in the format place and works really well.
#,##0;(#,##0)
Fiona
"Jerry Nixon" wrote:
> Here's what I use.
> with two decimals:
> $#,#.##;($#,#.##);$0.00
> without no decimals:
> $#,#;($#,#);$0
> one customer likes zeros to be blank, like this:
> $#,#;($#,#);
> I like zeros to be "zero", like this:
> $#,#;($#,#);Zero
> BR//Jerry
>|||Jerry, thanks for your input! You rock! I've emailed several reporting
services "gurus" I know and none of them came as close to getting it as even
I did!
Here's the format I ended up using (after reading your post) to display
negative numbers with 2 decimal points:
#,#.##;(#,#.##);0.00
The negative numbers display like (99.99) with no negative sign.
Many thanks!
"Jerry Nixon" wrote:
> Here's what I use.
> with two decimals:
> $#,#.##;($#,#.##);$0.00
> without no decimals:
> $#,#;($#,#);$0
> one customer likes zeros to be blank, like this:
> $#,#;($#,#);
> I like zeros to be "zero", like this:
> $#,#;($#,#);Zero
> BR//Jerry
>

Conditional Count in Detail Row - Count results across columns

Hi,
I am trying to establish a way to count the number of True/False entries
across a number of columns. That is I have a dataset returning results of
organisations (rows) and whether they have opted for a particular criteria
(columns). I want to know how many of the criteria a particular organisation
has opted for and display that on the end of the detail row for each
organisation.
Org Name Criteria1 Criteria2 Criteria3 Total Selected
ABC Ltd Yes No Yes 2
LMO Ltd No No Yes 1
XYZ Ltd Yes Yes Yes 3
Totals 2 1 3
I have managed the Totals in the footer using a conditional count.
eg =Count(iif(Fields!cf_subindicator111name.Value = "Yes",
Fields!cf_subindicator111name.Value, Nothing))
I am unsure of how to do the same thing at the end of the detail line (Total
Selected) for a conditional count of the discreet fields in the dataset. I
have 89 Criteria in all to count.
I have a feeling that this needs to be done at the SQL query level creating
a running total for each organisation returned where they have opted Yes for
a Criteria but I don't know where to start with this.
Hope this is clear.
Any ideas?
SimonHi,
You were right :). You should do it in your stored procedure. Maybe you
should use PIVOT operator ans aggregation function, like COUNT. Also, you
should try the RunningValue function in SSRS in worst case scenario.
Regards,
Janos
"Simon W3st" <SimonW3st@.discussions.microsoft.com> wrote in message
news:17CF23B8-E99A-4D2F-9947-9AC264D09CCF@.microsoft.com...
> Hi,
> I am trying to establish a way to count the number of True/False entries
> across a number of columns. That is I have a dataset returning results of
> organisations (rows) and whether they have opted for a particular criteria
> (columns). I want to know how many of the criteria a particular
> organisation
> has opted for and display that on the end of the detail row for each
> organisation.
> Org Name Criteria1 Criteria2 Criteria3 Total Selected
> ABC Ltd Yes No Yes 2
> LMO Ltd No No Yes 1
> XYZ Ltd Yes Yes Yes 3
> Totals 2 1 3
> I have managed the Totals in the footer using a conditional count.
> eg =Count(iif(Fields!cf_subindicator111name.Value = "Yes",
> Fields!cf_subindicator111name.Value, Nothing))
> I am unsure of how to do the same thing at the end of the detail line
> (Total
> Selected) for a conditional count of the discreet fields in the dataset. I
> have 89 Criteria in all to count.
> I have a feeling that this needs to be done at the SQL query level
> creating
> a running total for each organisation returned where they have opted Yes
> for
> a Criteria but I don't know where to start with this.
> Hope this is clear.
> Any ideas?
> Simon|||Thanks Janos.
The issue here is I am unclear on the specifics of how to do this, where it
sits in the select statement and what the format would be. I am only just
managing to get the select, from and where statements working at the moment
:) New to Reporting Services and SQL and could do with some pointers. I will
look up the Pivot operator and see if that gets me anywhere. Any pointers
would be appreciated.
Simon
"BERKE Janos" wrote:
> Hi,
> You were right :). You should do it in your stored procedure. Maybe you
> should use PIVOT operator ans aggregation function, like COUNT. Also, you
> should try the RunningValue function in SSRS in worst case scenario.
> Regards,
> Janos
> "Simon W3st" <SimonW3st@.discussions.microsoft.com> wrote in message
> news:17CF23B8-E99A-4D2F-9947-9AC264D09CCF@.microsoft.com...
> > Hi,
> >
> > I am trying to establish a way to count the number of True/False entries
> > across a number of columns. That is I have a dataset returning results of
> > organisations (rows) and whether they have opted for a particular criteria
> > (columns). I want to know how many of the criteria a particular
> > organisation
> > has opted for and display that on the end of the detail row for each
> > organisation.
> >
> > Org Name Criteria1 Criteria2 Criteria3 Total Selected
> >
> > ABC Ltd Yes No Yes 2
> > LMO Ltd No No Yes 1
> > XYZ Ltd Yes Yes Yes 3
> >
> > Totals 2 1 3
> >
> > I have managed the Totals in the footer using a conditional count.
> >
> > eg =Count(iif(Fields!cf_subindicator111name.Value = "Yes",
> > Fields!cf_subindicator111name.Value, Nothing))
> >
> > I am unsure of how to do the same thing at the end of the detail line
> > (Total
> > Selected) for a conditional count of the discreet fields in the dataset. I
> > have 89 Criteria in all to count.
> >
> > I have a feeling that this needs to be done at the SQL query level
> > creating
> > a running total for each organisation returned where they have opted Yes
> > for
> > a Criteria but I don't know where to start with this.
> >
> > Hope this is clear.
> >
> > Any ideas?
> >
> > Simon
>|||Hi Simon,
Post here a similar table structure to me, and I'll write the code for you
;). I will add some comments to my code as well.
Regards,
Janos
"Simon W3st" <SimonW3st@.discussions.microsoft.com> wrote in message
news:0C17ED91-B21D-4805-9B65-A200BBA595B4@.microsoft.com...
> Thanks Janos.
> The issue here is I am unclear on the specifics of how to do this, where
> it
> sits in the select statement and what the format would be. I am only just
> managing to get the select, from and where statements working at the
> moment
> :) New to Reporting Services and SQL and could do with some pointers. I
> will
> look up the Pivot operator and see if that gets me anywhere. Any pointers
> would be appreciated.
> Simon
> "BERKE Janos" wrote:
>> Hi,
>> You were right :). You should do it in your stored procedure. Maybe you
>> should use PIVOT operator ans aggregation function, like COUNT. Also, you
>> should try the RunningValue function in SSRS in worst case scenario.
>> Regards,
>> Janos
>> "Simon W3st" <SimonW3st@.discussions.microsoft.com> wrote in message
>> news:17CF23B8-E99A-4D2F-9947-9AC264D09CCF@.microsoft.com...
>> > Hi,
>> >
>> > I am trying to establish a way to count the number of True/False
>> > entries
>> > across a number of columns. That is I have a dataset returning results
>> > of
>> > organisations (rows) and whether they have opted for a particular
>> > criteria
>> > (columns). I want to know how many of the criteria a particular
>> > organisation
>> > has opted for and display that on the end of the detail row for each
>> > organisation.
>> >
>> > Org Name Criteria1 Criteria2 Criteria3 Total Selected
>> >
>> > ABC Ltd Yes No Yes 2
>> > LMO Ltd No No Yes 1
>> > XYZ Ltd Yes Yes Yes 3
>> >
>> > Totals 2 1 3
>> >
>> > I have managed the Totals in the footer using a conditional count.
>> >
>> > eg =Count(iif(Fields!cf_subindicator111name.Value = "Yes",
>> > Fields!cf_subindicator111name.Value, Nothing))
>> >
>> > I am unsure of how to do the same thing at the end of the detail line
>> > (Total
>> > Selected) for a conditional count of the discreet fields in the
>> > dataset. I
>> > have 89 Criteria in all to count.
>> >
>> > I have a feeling that this needs to be done at the SQL query level
>> > creating
>> > a running total for each organisation returned where they have opted
>> > Yes
>> > for
>> > a Criteria but I don't know where to start with this.
>> >
>> > Hope this is clear.
>> >
>> > Any ideas?
>> >
>> > Simon|||Janos,
Hi, not sure what you mean but my current query is as follows.
SELECT CRMAF_FilteredIncident.customeridname,
CRMAF_FilteredIncident.incidentid,
CRMAF_FilteredCF_Assessment.cf_casenumberid,
CRMAF_FilteredCF_Assessment.cf_applicationsubtypename,
CRMAF_FilteredCF_Assessment.cf_applicationtypename,
CRMAF_FilteredCF_Assessment.cf_subindicator111name,
CRMAF_FilteredCF_Assessment.cf_subindicator112name,
CRMAF_FilteredCF_Assessment.cf_subindicator121name,
CRMAF_FilteredCF_Assessment.cf_subindicator122name,
CRMAF_FilteredCF_Assessment.cf_subindicator123name,
CRMAF_FilteredCF_Assessment.cf_subindicator131name,
CRMAF_FilteredCF_Assessment.cf_subindicator141name,
CRMAF_FilteredCF_Assessment.cf_subindicator211name,
CRMAF_FilteredCF_Assessment.cf_subindicator221name,
CRMAF_FilteredCF_Assessment.cf_subindicator231name,
CRMAF_FilteredCF_Assessment.cf_subindicator232name,
CRMAF_FilteredCF_Assessment.cf_subindicator233name,
CRMAF_FilteredCF_Assessment.cf_subindicator241name,
CRMAF_FilteredCF_Assessment.cf_subindicator242name,
CRMAF_FilteredCF_Assessment.cf_subindicator243name,
CRMAF_FilteredCF_Assessment.cf_subindicator244name,
CRMAF_FilteredCF_Assessment.cf_subindicator245name,
CRMAF_FilteredCF_Assessment.cf_subindicator251name,
CRMAF_FilteredCF_Assessment.cf_subindicator252name,
CRMAF_FilteredCF_Assessment.cf_subindicator311name,
CRMAF_FilteredCF_Assessment.cf_subindicator312name,
CRMAF_FilteredCF_Assessment.cf_subindicator313name,
CRMAF_FilteredCF_Assessment.cf_subindicator321name,
CRMAF_FilteredCF_Assessment.cf_subindicator322name,
CRMAF_FilteredCF_Assessment.cf_subindicator323name,
CRMAF_FilteredCF_Assessment.cf_subindicator331name,
CRMAF_FilteredCF_Assessment.cf_subindicator332name,
CRMAF_FilteredCF_Assessment.cf_subindicator341name,
CRMAF_FilteredCF_Assessment.cf_subindicator342name,
CRMAF_FilteredCF_Assessment.cf_assessmenttypename,
CRMAF_FilteredCF_Assessment.cf_subindicator411name,
CRMAF_FilteredCF_Assessment.cf_subindicator421name,
CRMAF_FilteredCF_Assessment.cf_subindicator422name,
CRMAF_FilteredCF_Assessment.cf_subindicator423name,
CRMAF_FilteredCF_Assessment.cf_subindicator431name,
CRMAF_FilteredCF_Assessment.cf_subindicator441name,
CRMAF_FilteredCF_Assessment.cf_subindicator511name,
CRMAF_FilteredCF_Assessment.cf_subindicator512name,
CRMAF_FilteredCF_Assessment.cf_subindicator513name,
CRMAF_FilteredCF_Assessment.cf_subindicator514name,
CRMAF_FilteredCF_Assessment.cf_subindicator521name,
CRMAF_FilteredCF_Assessment.cf_subindicator522name,
CRMAF_FilteredCF_Assessment.cf_subindicator523name,
CRMAF_FilteredCF_Assessment.cf_subindicator524name,
CRMAF_FilteredCF_Assessment.cf_subindicator525name,
CRMAF_FilteredCF_Assessment.cf_subindicator6110name,
CRMAF_FilteredCF_Assessment.cf_subindicator6111name,
CRMAF_FilteredCF_Assessment.cf_subindicator611name,
CRMAF_FilteredCF_Assessment.cf_subindicator612name,
CRMAF_FilteredCF_Assessment.cf_subindicator613name,
CRMAF_FilteredCF_Assessment.cf_subindicator614name,
CRMAF_FilteredCF_Assessment.cf_subindicator615name,
CRMAF_FilteredCF_Assessment.cf_subindicator616name,
CRMAF_FilteredCF_Assessment.cf_subindicator617name,
CRMAF_FilteredCF_Assessment.cf_subindicator618name,
CRMAF_FilteredCF_Assessment.cf_subindicator619name,
CRMAF_FilteredCF_Assessment.cf_subindicator621name,
CRMAF_FilteredCF_Assessment.cf_subindicator622name,
CRMAF_FilteredCF_Assessment.cf_subindicator623name,
CRMAF_FilteredCF_Assessment.cf_subindicator624name,
CRMAF_FilteredCF_Assessment.cf_subindicator625name,
CRMAF_FilteredCF_Assessment.cf_subindicator626name,
CRMAF_FilteredCF_Assessment.cf_subindicator631name,
CRMAF_FilteredCF_Assessment.cf_subindicator632name,
CRMAF_FilteredCF_Assessment.cf_subindicator633name,
CRMAF_FilteredCF_Assessment.cf_subindicator634name,
CRMAF_FilteredCF_Assessment.cf_subindicator641name,
CRMAF_FilteredCF_Assessment.cf_subindicator642name,
CRMAF_FilteredCF_Assessment.cf_subindicator643name,
CRMAF_FilteredCF_Assessment.cf_subindicator651name,
CRMAF_FilteredCF_Assessment.cf_subindicator652name,
CRMAF_FilteredCF_Assessment.cf_subindicator711name,
CRMAF_FilteredCF_Assessment.cf_subindicator712name,
CRMAF_FilteredCF_Assessment.cf_subindicator713name,
CRMAF_FilteredCF_Assessment.cf_subindicator714name,
CRMAF_FilteredCF_Assessment.cf_subindicator721name,
CRMAF_FilteredCF_Assessment.cf_subindicator731name,
CRMAF_FilteredCF_Assessment.cf_subindicator741name,
CRMAF_FilteredCF_Assessment.cf_subindicator742name,
CRMAF_FilteredCF_Assessment.cf_subindicator743name,
CRMAF_FilteredCF_Assessment.cf_subindicator811name,
CRMAF_FilteredCF_Assessment.cf_subindicator812name,
CRMAF_FilteredCF_Assessment.cf_subindicator821name,
CRMAF_FilteredCF_Assessment.cf_subindicator911name,
CRMAF_FilteredCF_Assessment.cf_subindicator921name,
CRMAF_FilteredCF_Assessment.cf_subindicator931name,
CRMAF_FilteredCF_Assessment.cf_subindicator941name,
CRMAF_FilteredCF_Assessment.cf_subindicator951name,
CRMAF_FilteredCF_Assessment.cf_subindicator961name,
CRMAF_FilteredIncident.cf_casesubtypename
FROM FilteredCF_Assessment AS CRMAF_FilteredCF_Assessment INNER JOIN
FilteredIncident AS CRMAF_FilteredIncident ON
CRMAF_FilteredCF_Assessment.cf_casenumberid =CRMAF_FilteredIncident.incidentid
WHERE (CRMAF_FilteredIncident.cf_casesubtypename = 'Assessment')
What I want to do is a conditional count where the subindicator fields are
equal to yes and be able to total those at the end of each row.
Thanks.
"BERKE Janos" wrote:
> Hi Simon,
> Post here a similar table structure to me, and I'll write the code for you
> ;). I will add some comments to my code as well.
> Regards,
> Janos
>
> "Simon W3st" <SimonW3st@.discussions.microsoft.com> wrote in message
> news:0C17ED91-B21D-4805-9B65-A200BBA595B4@.microsoft.com...
> > Thanks Janos.
> >
> > The issue here is I am unclear on the specifics of how to do this, where
> > it
> > sits in the select statement and what the format would be. I am only just
> > managing to get the select, from and where statements working at the
> > moment
> > :) New to Reporting Services and SQL and could do with some pointers. I
> > will
> > look up the Pivot operator and see if that gets me anywhere. Any pointers
> > would be appreciated.
> >
> > Simon
> >
> > "BERKE Janos" wrote:
> >
> >> Hi,
> >>
> >> You were right :). You should do it in your stored procedure. Maybe you
> >> should use PIVOT operator ans aggregation function, like COUNT. Also, you
> >> should try the RunningValue function in SSRS in worst case scenario.
> >>
> >> Regards,
> >>
> >> Janos
> >>
> >> "Simon W3st" <SimonW3st@.discussions.microsoft.com> wrote in message
> >> news:17CF23B8-E99A-4D2F-9947-9AC264D09CCF@.microsoft.com...
> >> > Hi,
> >> >
> >> > I am trying to establish a way to count the number of True/False
> >> > entries
> >> > across a number of columns. That is I have a dataset returning results
> >> > of
> >> > organisations (rows) and whether they have opted for a particular
> >> > criteria
> >> > (columns). I want to know how many of the criteria a particular
> >> > organisation
> >> > has opted for and display that on the end of the detail row for each
> >> > organisation.
> >> >
> >> > Org Name Criteria1 Criteria2 Criteria3 Total Selected
> >> >
> >> > ABC Ltd Yes No Yes 2
> >> > LMO Ltd No No Yes 1
> >> > XYZ Ltd Yes Yes Yes 3
> >> >
> >> > Totals 2 1 3
> >> >
> >> > I have managed the Totals in the footer using a conditional count.
> >> >
> >> > eg =Count(iif(Fields!cf_subindicator111name.Value = "Yes",
> >> > Fields!cf_subindicator111name.Value, Nothing))
> >> >
> >> > I am unsure of how to do the same thing at the end of the detail line
> >> > (Total
> >> > Selected) for a conditional count of the discreet fields in the
> >> > dataset. I
> >> > have 89 Criteria in all to count.
> >> >
> >> > I have a feeling that this needs to be done at the SQL query level
> >> > creating
> >> > a running total for each organisation returned where they have opted
> >> > Yes
> >> > for
> >> > a Criteria but I don't know where to start with this.
> >> >
> >> > Hope this is clear.
> >> >
> >> > Any ideas?
> >> >
> >> > Simon
> >>
>

Condensing/Modularizing several cumulative calculated measures

In my cube, I have a number of calculated measures - around 30 per date tree and sales type combination (each different sales type has a different fiscal year). I have a Product.Sales Type attribute that can flicker the results between the different sale types, but the issue of working with different hierarchies is still present

Is there a way to condense this statement or somehow hookup some pieces so that one doesn't have to keep coding the same logic over and over? Either reducing the # of calculated members, or simply using some supported functionality to only code the logic in 1 spot, passing in the 'sale type' and 'measure name' and the inner logic would calculate the correct date tree and year level all within itself.

The pieces that change are bolded

(Date Tree hierarchy name,

Date Tree year level,

Calculated measure name

)

CASE

WHEN [Date].[Date Tree].CurrentMember.Level IS

[Date].[Date Tree].[(All)]

THEN [Measures].[Calculated Sales]

ELSE

Sum

(

PeriodsToDate

( [Date].[Date Tree].[Fiscal Year],

[Date].[Date Tree].CurrentMember

),

[Measures].[Calculated Sales]

)

END

One of my thoughts is to do something like this, but can someone feed me some ideas and fill in the syntax holes?

public [return type?] GetCumulativeMeasure( ‘Group 1’, ‘Calculated Sales 1’)

public [return type?] GetCumulativeMeasure(SalesType as string, MeasureName as string)

{

string Hierarchy = “”;

string HierarchyYearLevel = “”;

switch (SalesType)

{

case ‘Group 1’:Hierarchy = “[Date].[Date Tree]”; HierarchyYearLevel = Hierarchy + “.[Fiscal Year]”;

case ‘Group 2’:Hierarchy = “[Date].[Date Tree B]”; HierarchyYearLevel = Hierarchy + “.[Year Period]”;

}

//build calculated measure string to get a calculated measure for the year period and MeasureName variables

}

One thing you can do is create what's known as a 'time utility' or 'shell' dimension. There's a good writeup of what this means here:

http://www.obs3.com/A%20Different%20Approach%20to%20Time%20Calculations%20in%20SSAS.pdf

Incidentally, your use of the CASE statement to check what level you're at inside your calculation is not the most efficient way of writing the expression. Take a look at Mosha's blog entry on this subject for details on how you can use scoped assignments to do this instead:

http://sqljunkies.com/WebLog/mosha/archive/2006/10/25/time_calculations_parallelperiod.aspx

This might also reduce the amount of code you need to write.

HTH,

Chris

|||

Thank you for the links! It's always nice to know that there's a better way - I had a feeling about that as well, as things were too clunky.

|||

I've read over the material, but I can't picture how to put in mosha's suggestions into the mix.
Some date calculations I'd be doing are L30, L60, and Cumulative based on year.

The Last _ Days should only work with the Date level and always go back 30 days from the current membe,
while cumulative would be limited to the year that's being used.

I envision a shell dimension attribute with the following members:
L30 Days
L60 Days
MTD
LY MTD
YTD
LYTD
Yearly Cumulative

Could someone provide an example with a few of the calculations or let me know what other info would be helpful to post? Thank you for working with me through this process!!

|||

I think the logic you've already got in your calculations will stay pretty much the same; it's scoping the calculations that will be tricky. We'd need to know some details about your Date dimension structure.

Chris

|||

The date dimension hierarchy structure's right now are as follows. What more info is needed?

[Date].[Date Tree A]
Year Type A (spans from jan-dec)
Month
Day

[Date].[Date Tree B]
Year Type B (spans from march-july this year, then next year goes from Aug-July)
Month
Day

[Date].[Date Tree C]
Year Type C (spans from oct-sept)
Month
Day

Calculated measures include:

hierarchy based:
(Cumulative based on year and hard coded to a specific date tree)
Cumulative Type A Sales
Cumulative Type A Internet Sales
Cumulative Type B Sales
Cumulative Type B Internet Sales
Cumulative Type C Sales
Cumulative Type C Internet Sales

(hopefully obtainable in both hierarchy and non hierarchy form (from the Month or Day level):

LY MTD Type A Sales
LY MTD Type B Sales
LY MTD Type C Sales

MTD Type A Sales
MTD Type B Sales
MTD Type C Sales

Not hierarchy based:
L30 Days Sales
L30 Days Internet Sales
L60 Days Sales
L60 Days Internet Sales
(for these, I'm hoping to somehow just have a reusable 'L30' and 'L60' scoping, but that would allow you to see measures in both time periods in the same axis. If it's a good route to use a time shell dimension, then that's what I would do, but I'm looking for a little bit more instruction if possible.

Condensing/Modularizing several cumulative calculated measures

In my cube, I have a number of calculated measures - around 30 per date tree and sales type combination (each different sales type has a different fiscal year). I have a Product.Sales Type attribute that can flicker the results between the different sale types, but the issue of working with different hierarchies is still present

Is there a way to condense this statement or somehow hookup some pieces so that one doesn't have to keep coding the same logic over and over? Either reducing the # of calculated members, or simply using some supported functionality to only code the logic in 1 spot, passing in the 'sale type' and 'measure name' and the inner logic would calculate the correct date tree and year level all within itself.

The pieces that change are bolded

(Date Tree hierarchy name,

Date Tree year level,

Calculated measure name

)

CASE

WHEN [Date].[Date Tree].CurrentMember.Level IS

[Date].[Date Tree].[(All)]

THEN [Measures].[Calculated Sales]

ELSE

Sum

(

PeriodsToDate

( [Date].[Date Tree].[Fiscal Year],

[Date].[Date Tree].CurrentMember

),

[Measures].[Calculated Sales]

)

END

One of my thoughts is to do something like this, but can someone feed me some ideas and fill in the syntax holes?

public [return type?] GetCumulativeMeasure( ‘Group 1’, ‘Calculated Sales 1’)

public [return type?] GetCumulativeMeasure(SalesType as string, MeasureName as string)

{

string Hierarchy = “”;

string HierarchyYearLevel = “”;

switch (SalesType)

{

case ‘Group 1’:Hierarchy = “[Date].[Date Tree]”; HierarchyYearLevel = Hierarchy + “.[Fiscal Year]”;

case ‘Group 2’:Hierarchy = “[Date].[Date Tree B]”; HierarchyYearLevel = Hierarchy + “.[Year Period]”;

}

//build calculated measure string to get a calculated measure for the year period and MeasureName variables

}

One thing you can do is create what's known as a 'time utility' or 'shell' dimension. There's a good writeup of what this means here:

http://www.obs3.com/A%20Different%20Approach%20to%20Time%20Calculations%20in%20SSAS.pdf

Incidentally, your use of the CASE statement to check what level you're at inside your calculation is not the most efficient way of writing the expression. Take a look at Mosha's blog entry on this subject for details on how you can use scoped assignments to do this instead:

http://sqljunkies.com/WebLog/mosha/archive/2006/10/25/time_calculations_parallelperiod.aspx

This might also reduce the amount of code you need to write.

HTH,

Chris

|||

Thank you for the links! It's always nice to know that there's a better way - I had a feeling about that as well, as things were too clunky.

|||

I've read over the material, but I can't picture how to put in mosha's suggestions into the mix.
Some date calculations I'd be doing are L30, L60, and Cumulative based on year.

The Last _ Days should only work with the Date level and always go back 30 days from the current membe,
while cumulative would be limited to the year that's being used.

I envision a shell dimension attribute with the following members:
L30 Days
L60 Days
MTD
LY MTD
YTD
LYTD
Yearly Cumulative

Could someone provide an example with a few of the calculations or let me know what other info would be helpful to post? Thank you for working with me through this process!!

|||

I think the logic you've already got in your calculations will stay pretty much the same; it's scoping the calculations that will be tricky. We'd need to know some details about your Date dimension structure.

Chris

|||

The date dimension hierarchy structure's right now are as follows. What more info is needed?

[Date].[Date Tree A]
Year Type A (spans from jan-dec)
Month
Day

[Date].[Date Tree B]
Year Type B (spans from march-july this year, then next year goes from Aug-July)
Month
Day

[Date].[Date Tree C]
Year Type C (spans from oct-sept)
Month
Day

Calculated measures include:

hierarchy based:
(Cumulative based on year and hard coded to a specific date tree)
Cumulative Type A Sales
Cumulative Type A Internet Sales
Cumulative Type B Sales
Cumulative Type B Internet Sales
Cumulative Type C Sales
Cumulative Type C Internet Sales

(hopefully obtainable in both hierarchy and non hierarchy form (from the Month or Day level):

LY MTD Type A Sales
LY MTD Type B Sales
LY MTD Type C Sales

MTD Type A Sales
MTD Type B Sales
MTD Type C Sales

Not hierarchy based:
L30 Days Sales
L30 Days Internet Sales
L60 Days Sales
L60 Days Internet Sales
(for these, I'm hoping to somehow just have a reusable 'L30' and 'L60' scoping, but that would allow you to see measures in both time periods in the same axis. If it's a good route to use a time shell dimension, then that's what I would do, but I'm looking for a little bit more instruction if possible.

Condensing/Modularizing several cumulative calculated measures

In my cube, I have a number of calculated measures - around 30 per date tree and sales type combination (each different sales type has a different fiscal year). I have a Product.Sales Type attribute that can flicker the results between the different sale types, but the issue of working with different hierarchies is still present

Is there a way to condense this statement or somehow hookup some pieces so that one doesn't have to keep coding the same logic over and over? Either reducing the # of calculated members, or simply using some supported functionality to only code the logic in 1 spot, passing in the 'sale type' and 'measure name' and the inner logic would calculate the correct date tree and year level all within itself.

The pieces that change are bolded

(Date Tree hierarchy name,

Date Tree year level,

Calculated measure name

)

CASE

WHEN [Date].[Date Tree].CurrentMember.Level IS

[Date].[Date Tree].[(All)]

THEN [Measures].[Calculated Sales]

ELSE

Sum

(

PeriodsToDate

( [Date].[Date Tree].[Fiscal Year],

[Date].[Date Tree].CurrentMember

),

[Measures].[Calculated Sales]

)

END

One of my thoughts is to do something like this, but can someone feed me some ideas and fill in the syntax holes?

public [return type?] GetCumulativeMeasure( ‘Group 1’, ‘Calculated Sales 1’)

public [return type?] GetCumulativeMeasure(SalesType as string, MeasureName as string)

{

string Hierarchy = “”;

string HierarchyYearLevel = “”;

switch (SalesType)

{

case ‘Group 1’:Hierarchy = “[Date].[Date Tree]”; HierarchyYearLevel = Hierarchy + “.[Fiscal Year]”;

case ‘Group 2’:Hierarchy = “[Date].[Date Tree B]”; HierarchyYearLevel = Hierarchy + “.[Year Period]”;

}

//build calculated measure string to get a calculated measure for the year period and MeasureName variables

}

One thing you can do is create what's known as a 'time utility' or 'shell' dimension. There's a good writeup of what this means here:

http://www.obs3.com/A%20Different%20Approach%20to%20Time%20Calculations%20in%20SSAS.pdf

Incidentally, your use of the CASE statement to check what level you're at inside your calculation is not the most efficient way of writing the expression. Take a look at Mosha's blog entry on this subject for details on how you can use scoped assignments to do this instead:

http://sqljunkies.com/WebLog/mosha/archive/2006/10/25/time_calculations_parallelperiod.aspx

This might also reduce the amount of code you need to write.

HTH,

Chris

|||

Thank you for the links! It's always nice to know that there's a better way - I had a feeling about that as well, as things were too clunky.

|||

I've read over the material, but I can't picture how to put in mosha's suggestions into the mix.
Some date calculations I'd be doing are L30, L60, and Cumulative based on year.

The Last _ Days should only work with the Date level and always go back 30 days from the current membe,
while cumulative would be limited to the year that's being used.

I envision a shell dimension attribute with the following members:
L30 Days
L60 Days
MTD
LY MTD
YTD
LYTD
Yearly Cumulative

Could someone provide an example with a few of the calculations or let me know what other info would be helpful to post? Thank you for working with me through this process!!

|||

I think the logic you've already got in your calculations will stay pretty much the same; it's scoping the calculations that will be tricky. We'd need to know some details about your Date dimension structure.

Chris

|||

The date dimension hierarchy structure's right now are as follows. What more info is needed?

[Date].[Date Tree A]
Year Type A (spans from jan-dec)
Month
Day

[Date].[Date Tree B]
Year Type B (spans from march-july this year, then next year goes from Aug-July)
Month
Day

[Date].[Date Tree C]
Year Type C (spans from oct-sept)
Month
Day

Calculated measures include:

hierarchy based:
(Cumulative based on year and hard coded to a specific date tree)
Cumulative Type A Sales
Cumulative Type A Internet Sales
Cumulative Type B Sales
Cumulative Type B Internet Sales
Cumulative Type C Sales
Cumulative Type C Internet Sales

(hopefully obtainable in both hierarchy and non hierarchy form (from the Month or Day level):

LY MTD Type A Sales
LY MTD Type B Sales
LY MTD Type C Sales

MTD Type A Sales
MTD Type B Sales
MTD Type C Sales

Not hierarchy based:
L30 Days Sales
L30 Days Internet Sales
L60 Days Sales
L60 Days Internet Sales
(for these, I'm hoping to somehow just have a reusable 'L30' and 'L60' scoping, but that would allow you to see measures in both time periods in the same axis. If it's a good route to use a time shell dimension, then that's what I would do, but I'm looking for a little bit more instruction if possible.

Concurrent stored procs?

Hi all

I have a stored proc that runs every 4 hours - as a job. The stored proc takes about 3-5 minutes to comple, depending on number of records.
To do testing we run that stored proc manually, also.
Sometimes 2 or more people may run the same stored proc without knowing that the other person is running. This may create duplicates entries once processed.

What I want to know is, Is there a way to check if that stored procedure is currently running. If so it wont run second time concurrently.
(May be semapohres,mutex or something like that?)

(I am trying not to use a table to store whether the stored proc is running or not)

Thanks in advance.

RochanaOne can use sp_getapplock and sp_releaseapplock when invoking a sp.

Hans.|||Thanks Hans for quick reply.

I tried it but found some problems.
sp_getapplock needs an active transaction, without which it fails.
I am trying not to use Transactions because it slows down the system so badly.

Originally posted by HansVE
One can use sp_getapplock and sp_releaseapplock when invoking a sp.

Hans.|||Could use global temporary table as semaphore.

CREATE TABLE ##proc_running(x int)
IF @.@.error <> 0
PRINT 'Proc already running'
ELSE BEGIN
...
DROP TABLE ##proc_running
END

Hans.|||It works to some extent Hans.
The error checking never happens. It quits the process without displaying the error.
..
IF @.@.error <> 0
PRINT 'Proc already running'
...

Originally posted by HansVE
Could use global temporary table as semaphore.

CREATE TABLE ##proc_running(x int)
IF @.@.error <> 0
PRINT 'Proc already running'
ELSE BEGIN
...
DROP TABLE ##proc_running
END

Hans.|||Yes, the error is too serious to continue. If you cannot trap that error at the client, you could instead check for existence of the table object.

IF OBJECT_ID('tempdb.dbo.##proc_running') > 0|||sysprocesses holds info about all processes currently running on the server (master.sysprocesses)

dbcc inputbuffer spid tells you what a specific process is doing

The two combined (in some way ;)) should tell you if the proc is running already..

Hope it helps a little bit..|||I am a little confused .. but isnt the job scheduled ... then why is it being run manually ... and even if it is being run manually ... why two people have been given the access ??|||Originally posted by Jonte
sysprocesses holds info about all processes currently running on the server (master.sysprocesses)

dbcc inputbuffer spid tells you what a specific process is doing

The two combined (in some way ;)) should tell you if the proc is running already..

Hope it helps a little bit..

One problem with this is that two people could still start the proc simultaneously.

Hans.|||Enigma here is the scenario:
Say, the job is scheduled at 12noon everyday.
One of the bosses come and asks us to run that particular job becos they need to see the data on their screen. So one of the programmers of the team runs that job, or invoke the particular sp.
The sp takes about 10-15 mins to complete.
If it reaches 12noon while that particular sp is running, job kicks in and invoke that same sp again. (our sp is still running)
Thats why I want to check if the particular sp is already running or not, so that the sp wont run again.

Originally posted by Enigma
I am a little confused .. but isnt the job scheduled ... then why is it being run manually ... and even if it is being run manually ... why two people have been given the access ??|||Thanks Jonte..
Your suggestion worked !! ;)
I have to check the Event Info for my stored proc, thats running in the server.

I have pasted code to test what processes are currently running on the server on a db. May be you can run and see the results too

Thanks

declare @.spid bigint
declare crsr cursor read_only
for
select spid from master..sysprocesses where dbid>1 and kpid>1

open crsr
fetch next from crsr
into @.spid

while @.@.fetch_status<>-1
begin
dbcc inputbuffer(@.spid)
fetch next from crsr
into @.spid

end

close crsr
deallocate crsr

Originally posted by Jonte
sysprocesses holds info about all processes currently running on the server (master.sysprocesses)

dbcc inputbuffer spid tells you what a specific process is doing

The two combined (in some way ;)) should tell you if the proc is running already..

Hope it helps a little bit..

Concurrent Connections

HI All
Could help me out to find the following information from the Query
Analyzer?
Number of Concurrent Connections/Users
Average Transaction Per/Sec
Database Growth Rate
RegardsHi,
You can querry system table master..sysprocesses and get required counters.
Amol Lembhe
"Praveen" wrote:
> HI All
> Could help me out to find the following information from the Query
> Analyzer?
> Number of Concurrent Connections/Users
> Average Transaction Per/Sec
> Database Growth Rate
> Regards
>