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.

concatenation

I am trying to concatenate a long string together ad it keeps truncating how
can I extend the length that is allowed to truncate?
TIAHow do you it is being truncated?
Can you post the code?
AMB
"JMNUSS" wrote:

> I am trying to concatenate a long string together ad it keeps truncating h
ow
> can I extend the length that is allowed to truncate?
> TIA|||maybe truncated wasn't the correct term. The string is being cut off at the
end...
What I want to see is something like "this<>is<> a<>long<> string"
What is get is "this<> is<> a<> lon"
and that's it
"Alejandro Mesa" wrote:
> How do you it is being truncated?
> Can you post the code?
>
> AMB
> "JMNUSS" wrote:
>|||How long a string?
Where are you doing the concatenation?
Show us some code.
"JMNUSS" <JMNUSS@.discussions.microsoft.com> wrote in message
news:061DB1FF-1529-45B7-B51E-8627A6162936@.microsoft.com...
> I am trying to concatenate a long string together ad it keeps truncating
how
> can I extend the length that is allowed to truncate?
> TIA|||No, you can not do that. You can insert the result of the sp into a table an
d
then process the table, or you can rewrite the sp as a table-valued user
defined function, or you can call the sp using rowset function.
Example:
use northwind
go
create table #t (
Shippeddate datetime,
OrderID int,
Subtotal money,
col_Year int
)
insert into #t
execute dbo.[Sales by Year] '19960101', '19961231'
select
*
from
#t
where
Subtotal between 1200.00 and 1500.00
drop table #t
go
AMB
"JMNUSS" wrote:

> I am trying to concatenate a long string together ad it keeps truncating h
ow
> can I extend the length that is allowed to truncate?
> TIA|||Are you viewing this in Query Analyser?
If so: Tools/Options/Results and change the "Maximum characters per column"
"JMNUSS" <JMNUSS@.discussions.microsoft.com> wrote in message
news:B1036631-B165-4251-AF5B-A6C3777C0705@.microsoft.com...
> maybe truncated wasn't the correct term. The string is being cut off at
the
> end...
> What I want to see is something like "this<>is<> a<>long<> string"
> What is get is "this<> is<> a<> lon"
> and that's it
> "Alejandro Mesa" wrote:
>
truncating how|||Sorry.
AMB
"Alejandro Mesa" wrote:
> No, you can not do that. You can insert the result of the sp into a table
and
> then process the table, or you can rewrite the sp as a table-valued user
> defined function, or you can call the sp using rowset function.
> Example:
> use northwind
> go
> create table #t (
> Shippeddate datetime,
> OrderID int,
> Subtotal money,
> col_Year int
> )
> insert into #t
> execute dbo.[Sales by Year] '19960101', '19961231'
> select
> *
> from
> #t
> where
> Subtotal between 1200.00 and 1500.00
> drop table #t
> go
>
> AMB
>
> "JMNUSS" wrote:
>|||> What is get is "this<> is<> a<> lon"
Where do you get this, in your client app?
If you are using SQL Query Analyzer and the result is being truncated, go to
tools - Options - Results and change "Maximum characters per column:" (max
number is 8192).
AMB
"JMNUSS" wrote:
> maybe truncated wasn't the correct term. The string is being cut off at t
he
> end...
> What I want to see is something like "this<>is<> a<>long<> string"
> What is get is "this<> is<> a<> lon"
> and that's it
> "Alejandro Mesa" wrote:
>|||Heh Alejandro, get in the right thread...
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:58BD3B7D-A23E-44BC-9008-4276A8B7DE58@.microsoft.com...
> No, you can not do that. You can insert the result of the sp into a table
and
> then process the table, or you can rewrite the sp as a table-valued user
> defined function, or you can call the sp using rowset function.
> Example:
> use northwind
> go
> create table #t (
> Shippeddate datetime,
> OrderID int,
> Subtotal money,
> col_Year int
> )
> insert into #t
> execute dbo.[Sales by Year] '19960101', '19961231'
> select
> *
> from
> #t
> where
> Subtotal between 1200.00 and 1500.00
> drop table #t
> go
>
> AMB
>
> "JMNUSS" wrote:
>
how

Concatenating Numeric Fields

Friends,

I am attempting to concatenate two numeric type fields together with character data and the query is adding them together. I am assuming I need to convert the ints to a string type but would appreciate some info on the best way to do this...I am sure it's something simple but am not finding much on the web about it.

SELECT vehFacID + '-' + vehID AS vehNew FROM Vehicles

Returns the sum of vehFacID & vehID. Doh!

J.H.

I think I found it...Something like this works..

SELECT *, CAST(vehFacID AS VARCHAR(4)) + '-' + CAST(vehID AS VARCHAR(10)) AS vehCombo FROM Vehicles

Is this the right way to do this?

J.H.

|||

If the vehFacId and vehId are numbers, then this is the way to go.

<stuff you can ignore if you want>

A bit nasty with the column names, I hope for your sake you don't have 3 letter abbreviations in every column (but not in your table name.) That must be hard to follow.

</stuff you can ignore if you want>

|||

Are you referring to the "veh" abbreviation? If so, why would you say it would be hard to follow? A small sample of my tables is like:

Vehicles, Departments, Facilities, Customers, etc...I use the 3 (or 4 sometimes) letter abbreviation to determine which table the field came from. I am open to hearing a better suggestion if you have one.

J.H.

|||

You know what else I am curious about is the casting. My numeric columns in this case are smallInt and can hold up to 5 digits. Is the recommendation to cast them to varchar(5) in this case?

J.H.

|||

I don't see any problem even if you cast to varchar(25), that way down the road if you happen to change the datatype from smallint to int, you don't have to worry about T-SQL code like this in various stored procs and functions.

As far as database naming conventions goes there isn't a standard. I wish Microsoft would have suggested something on MSDN.

I kind of agree with a article on aspfaq: http://www.aspfaq.com/show.asp?id=2538

|||

A little bit for the veh abbreviation. I would prefer to see vehicleId, and vehicleFaciltiyId, etc, which is easier to follow for the uninitiated (and in fact good finger exercises :)

The vehFacId was what kind of concerned me. I got this flash of:

select vehId, mak, modYr, numWhl, vehIdNum...etc.

There were a lot of these sorts of naming conventions back when names could only be 30 characters (funny how many times we hit 30, but rarely do I go over it now...) I don't like to see something that might be an issue and not say something. (hence the: <stuff you can ignore if you want> tags) Like the link to aspfaq says, it is a matter of taste, but the more clear it is, the more clear it is.

If a new person or contractor or newsgroup helper can read it and understand it, your job of naming is done right.

Concatenating fields together in Reporting Services

I have several fields in the report that are concatenations of two or more
datafields in the dataset. What we have found is that RS is padding out the
fields to the full size from the database, so if we put together, it doesn't
look right.
For example, if I go to put firstname and lastname together instead of
getting "John Doe" I end up with "John Doe". Does anyone know of
a way to tallow for the default to be not to pad out the field. The only
alternative that I have found is to trim() the fields as needed, but it
really slows down the development process.
Thanks
JeffWhat is the type of Lastname and firstname in the database. Is it nvarchar
or is it a specified number of caracters ?
I don't have this issue with varchar.
Cédric
"Jeffery J. Morgan" <jmorgan@._tranquilitysolutions.com> wrote in message
news:ORsk4POhEHA.1184@.TK2MSFTNGP12.phx.gbl...
>I have several fields in the report that are concatenations of two or more
> datafields in the dataset. What we have found is that RS is padding out
> the
> fields to the full size from the database, so if we put together, it
> doesn't
> look right.
> For example, if I go to put firstname and lastname together instead of
> getting "John Doe" I end up with "John Doe". Does anyone know
> of
> a way to tallow for the default to be not to pad out the field. The only
> alternative that I have found is to trim() the fields as needed, but it
> really slows down the development process.
> Thanks
> Jeff
>