Multivalue parameter textbox size

Hi,

In my report,I have multivalue parameter for this parameter the Available values are from the Dataset.Suppose the dataset contains Names like this:

'AL - Alabama Center (Tuscaloosa)',

'AL - Regional Control Center (Birmingham)',

'AR - Arkansas & Rock)',

'Arizona Department of Health'

But my problem is the Multivalue parameter TextBox is of fixed size.Now i want the size of the text box to the size of the name which is of length long in the Name.

Whether it is possible or not.

How to achieve this.

The size of the parameter area is not configurable. You can modify some of the style properties of the report viewer by following the instructions here: http://msdn2.microsoft.com/en-us/library/ms345247.aspx, there is just no way to set the size of the input boxes.|||

Sorry for high jacking this post but I can’t find any other reference to this issue.

I heard a rumour that this would be fixer in SQL Server 2005 SP2, is this the case?This issue is a real thorn in my side with constant user moaning.

Multivalue parameter textbox size

Hi,

In my report,I have multivalue parameter for this parameter the Available values are from the Dataset.Suppose the dataset contains Names like this:

'AL - Alabama Center (Tuscaloosa)',

'AL - Regional Control Center (Birmingham)',

'AR - Arkansas & Rock)',

'Arizona Department of Health'

But my problem is the Multivalue parameter TextBox is of fixed size.Now i want the size of the text box to the size of the name which is of length long in the Name.

Whether it is possible or not.

How to achieve this.

The size of the parameter area is not configurable. You can modify some of the style properties of the report viewer by following the instructions here: http://msdn2.microsoft.com/en-us/library/ms345247.aspx, there is just no way to set the size of the input boxes.|||

Sorry for high jacking this post but I can’t find any other reference to this issue.

I heard a rumour that this would be fixer in SQL Server 2005 SP2, is this the case?This issue is a real thorn in my side with constant user moaning.

Multi-value parameter default values not working

I have a parameter that gets it's available values from a dataset.

I use this exact same data to populate the default values.

When I run the report, the available values get populated; however the default values are not being selected.

This works on other parameters on the same report. However, on this parameter, it is not working.

I have tried ltrim/rtrim (Been burned with that before when the field type is a char)

I change the data field in the query, without changing the parameter setup, and it works...

Here is my query:

Code Snippet

Select distinct
CityName

from dimHotel dH (NOLOCK)
Inner join factHotel fH (NOLOCK)
on dh.HotelKey = fH.HotelKey

Where dH.CityName <> ''
and dH.CityName is not null
and fH.ClientKey in (@.ClientID)

Order by CityName

The parameter is setup correctly, and matches the setup of another parameter on the same report that is working fine.

I have tried deleting the parameter and re-adding it. This did not work.

I also deleted and re-added the query. No luck.

Any ideas?

Thanks!!

Thanks

Ok, this is weird...

If I change the query to:

Code Snippet

Select distinct
Replace(CityName,'','') as CityName

It works fine.

If I do a lower or upper, it works fine.

Convert or l/rtrim do not work.

Anyone have any ideas?

At this point I am thinking that there is a character in one of the fields that shouldnt be in there... I am looking at that now.

BobP

|||

Ok, it's an intermittent problem.

Sometimes the replace works, sometimes it doesn't.

Still trying to figure out why...

Does anyone know if there are any characters that could be in the data field, and prevent the default parameters from populating?

Thanks

|||

This same query populates a multi value drop down on a totally different report and it does not work there, either.

I am thinking it is data related.

Can anyone think of a reason why the multi select drop down would be populated, but the defaults not be set?

There are only 360 rows, so it's not a row number issue.

Any ideas would be very welcomed.

Thanks

BobP

Multi-value parameter default values not working

I have a parameter that gets it's available values from a dataset.

I use this exact same data to populate the default values.

When I run the report, the available values get populated; however the default values are not being selected.

This works on other parameters on the same report. However, on this parameter, it is not working.

I have tried ltrim/rtrim (Been burned with that before when the field type is a char)

I change the data field in the query, without changing the parameter setup, and it works...

Here is my query:

Code Snippet

Select distinct
CityName

from dimHotel dH (NOLOCK)
Inner join factHotel fH (NOLOCK)
on dh.HotelKey = fH.HotelKey

Where dH.CityName <> ''
and dH.CityName is not null
and fH.ClientKey in (@.ClientID)

Order by CityName

The parameter is setup correctly, and matches the setup of another parameter on the same report that is working fine.

I have tried deleting the parameter and re-adding it. This did not work.

I also deleted and re-added the query. No luck.

Any ideas?

Thanks!!

Thanks

Ok, this is weird...

If I change the query to:

Code Snippet

Select distinct
Replace(CityName,'','') as CityName

It works fine.

If I do a lower or upper, it works fine.

Convert or l/rtrim do not work.

Anyone have any ideas?

At this point I am thinking that there is a character in one of the fields that shouldnt be in there... I am looking at that now.

BobP

|||

Ok, it's an intermittent problem.

Sometimes the replace works, sometimes it doesn't.

Still trying to figure out why...

Does anyone know if there are any characters that could be in the data field, and prevent the default parameters from populating?

Thanks

|||

This same query populates a multi value drop down on a totally different report and it does not work there, either.

I am thinking it is data related.

Can anyone think of a reason why the multi select drop down would be populated, but the defaults not be set?

There are only 360 rows, so it's not a row number issue.

Any ideas would be very welcomed.

Thanks

BobP

sql

MultiValue Parameter

Hello,

I have in my report a text box that lists the user selection. When the user select a few of the values I display the selection but when the user select "All" I would like the text box to say "Parameter: All".

Is there anyway to know if "All" was selected, I don't want to list all the values because sometime there are too many of them.

Thank you,

Itzhak

The closest you can get is to compare the number of rows in the dataset used for the valid values list (e.g. =CountRows("ParameterDataSetName")) with the number of selected parameter values (e.g. =Parameters!P1.Count)

-- Robert

|||

Thank you, this will solve my issue.

I put the following expression and it works well !!

=iif(CountRows("UsersDataSet")=Parameters!UserID.Count,"ALL",join(Parameters!UserID.Label,","))

Thanks,

Itzhak

Multiselect problem

hi ,
following parameter @.a has multi values. but when i select the multi value it gives some error.
is this the correct method i use? and is there any differrent way that i should use in the report designer when i use the value of that parameter ... Field!a.value etc.?\

SET [FilteredBUList] AS descendants(strtoset(@.a),[Account—BillingCodeDsc].[Billing Code Description],leaves)

The descendants function expects a single member for the first parameter, to get this to work with mulitple parameters you would need to use the generate function. I'm not exactly sure what your structures look like so you will need to fill in the dimension and hierarchy of the members in @.a in the code below

eg.

SET [FilteredBUList] AS generate(strtoset(@.a), descendants(<dimension>.<hierarchy>.CurrentMember),[Account—BillingCodeDsc].[Billing Code Description],leaves)

|||cant use generate function like that. giving syntax error.|||Sorry, it's missing a closing bracket.|||that is fine. but how can i get only the selected value.

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SET [FilteredBUList] AS generate(strtoset(@.BU),descendants([Account—BillingCodeDsc].CURRENTMEMBER,[Account—BillingCodeDsc].[Billing Code Description],leaves))

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[FilteredBUList] on rows

FROM Profitability

this is my mdx. so once i trying to get only selected values there is some error. i mean some times it will appear all the BU . sometime it will display only the first BU. how can i get only selected value to the report design.
|||hey darren
u knw how to solve following problem?

HI ,
how can i access the different dataset parameter in th SSRS. i have created one dataset and define Bu as a parameter. then i created separate dataset called dataset2 and need to access that BU parameter from dataset2. i cant access it using normal way. ex strtoset(@.Bu)..... ?

i need to access it thru the mdx query like...
set as strtoset(@.BU) this bu is from some other dataset
|||

lk_wick wrote:

that is fine. but how can i get only the selected value.

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SET [FilteredBUList] AS generate(strtoset(@.BU),descendants([Account—BillingCodeDsc].CURRENTMEMBER,[Account—BillingCodeDsc].[Billing Code Description],leaves))

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[FilteredBUList] on rows

FROM Profitability

this is my mdx. so once i trying to get only selected values there is some error. i mean some times it will appear all the BU . sometime it will display only the first BU. how can i get only selected value to the report design.

If you want only the selected member(s) on the rows, then you would just put the parameter directly in the row axis.

Code Snippet

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
strtoset(@.BU) on rows

FROM Profitability

Also I would not do the "if empty return 0" logic in the MDX, if you are using Reporting Services, you can use a format string in the report to return 0 for null values. I often use format strings like "$0;($0);-;-" which returns negative values in brackets and 0 and null as dashes.|||

lk_wick wrote:

hey darren
u knw how to solve following problem?

HI ,
how can i access the different dataset parameter in th SSRS. i have created one dataset and define Bu as a parameter. then i created separate dataset called dataset2 and need to access that BU parameter from dataset2. i cant access it using normal way. ex strtoset(@.Bu)..... ?

i need to access it thru the mdx query like...
set as strtoset(@.BU) this bu is from some other dataset

Sorry, I don't really understand what you are trying to do.

|||i have created dataset1 and define the query parameter named bu. i have created another dataset and i need do access that bu parameter like following.

set [test ] as strtoset(@.BU) bu query parameter is in differrent datset.
|||Query parameters are fed from Report parameters, so you should be able to map both datasets to using the same report parameter.

Multiselect problem

hi ,
following parameter @.a has multi values. but when i select the multi value it gives some error.
is this the correct method i use? and is there any differrent way that i should use in the report designer when i use the value of that parameter ... Field!a.value etc.?\

SET [FilteredBUList] AS descendants(strtoset(@.a),[Account—BillingCodeDsc].[Billing Code Description],leaves)

The descendants function expects a single member for the first parameter, to get this to work with mulitple parameters you would need to use the generate function. I'm not exactly sure what your structures look like so you will need to fill in the dimension and hierarchy of the members in @.a in the code below

eg.

SET [FilteredBUList] AS generate(strtoset(@.a), descendants(<dimension>.<hierarchy>.CurrentMember),[Account—BillingCodeDsc].[Billing Code Description],leaves)

|||cant use generate function like that. giving syntax error.|||Sorry, it's missing a closing bracket.|||that is fine. but how can i get only the selected value.

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SET [FilteredBUList] AS generate(strtoset(@.BU),descendants([Account—BillingCodeDsc].CURRENTMEMBER,[Account—BillingCodeDsc].[Billing Code Description],leaves))

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[FilteredBUList] on rows

FROM Profitability

this is my mdx. so once i trying to get only selected values there is some error. i mean some times it will appear all the BU . sometime it will display only the first BU. how can i get only selected value to the report design.
|||hey darren
u knw how to solve following problem?

HI ,
how can i access the different dataset parameter in th SSRS. i have created one dataset and define Bu as a parameter. then i created separate dataset called dataset2 and need to access that BU parameter from dataset2. i cant access it using normal way. ex strtoset(@.Bu)..... ?

i need to access it thru the mdx query like...
set as strtoset(@.BU) this bu is from some other dataset
|||

lk_wick wrote:

that is fine. but how can i get only the selected value.

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SET [FilteredBUList] AS generate(strtoset(@.BU),descendants([Account—BillingCodeDsc].CURRENTMEMBER,[Account—BillingCodeDsc].[Billing Code Description],leaves))

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[FilteredBUList] on rows

FROM Profitability

this is my mdx. so once i trying to get only selected values there is some error. i mean some times it will appear all the BU . sometime it will display only the first BU. how can i get only selected value to the report design.

If you want only the selected member(s) on the rows, then you would just put the parameter directly in the row axis.

Code Snippet

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
strtoset(@.BU) on rows

FROM Profitability

Also I would not do the "if empty return 0" logic in the MDX, if you are using Reporting Services, you can use a format string in the report to return 0 for null values. I often use format strings like "$0;($0);-;-" which returns negative values in brackets and 0 and null as dashes.|||

lk_wick wrote:

hey darren
u knw how to solve following problem?

HI ,
how can i access the different dataset parameter in th SSRS. i have created one dataset and define Bu as a parameter. then i created separate dataset called dataset2 and need to access that BU parameter from dataset2. i cant access it using normal way. ex strtoset(@.Bu)..... ?

i need to access it thru the mdx query like...
set as strtoset(@.BU) this bu is from some other dataset

Sorry, I don't really understand what you are trying to do.

|||i have created dataset1 and define the query parameter named bu. i have created another dataset and i need do access that bu parameter like following.

set [test ] as strtoset(@.BU) bu query parameter is in differrent datset.
|||Query parameters are fed from Report parameters, so you should be able to map both datasets to using the same report parameter.

Multirow insert statement,... how?

In MySQL I was able to insert multiple rows into the db like this:

REPLACE INTO Products (productid, price) VALUES (1, 55), (2, 88), (3, 99);

In transact-Sql (Sql Server 2000), this "REPLACE" keyword means something different. What it meant in MySQL is that if there is an existing row that has the same primary key value as one of the new rows being inserted, it will replace that old row with the new row.

How do I do this in SQL Server 2000?

Also, I can do this in MySQL:

INSERT INGORE INTO Products (productid, price) VALUES (1, 55), (2, 88), (3, 99);

This meant in MySQL is that if there is an existing row that has the same primary key value as one of the new rows being inserted, it will ignore that new row's insert and keep the values of the old row.

How do I do that also in SQL Server 2000?

ThanksYou can't easily. MySQL is CISC to MS SQLs RISC.
There is no equiv' REPLACE and no IGNORE. You have do everything the hard way.

You can use insert with a not exists (yuk) or populate a temp table var with the rows you want and load the same keyed values from the database. That way you can see the gaps (inserts) and the ones with data (updates). Still not gr8 but what you gonna do!|||Darn,... I have experience developing aps with both MySQL and SQL server and I think this is the first time ever that it's harder to do something with SQL SERVER. Usually, it's MySql that makes things harder.

No wonder why I couldn't find any documentation for the life of me.
Oh well!|||You could use, and I'm trying not to be sick when saying it, a datagram. I think that's got some of the features you want <shudder>. Might be worth a look though.|||How do I use a datagram? Can you give me some examples?|||try
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/dotnet_8704.asp|||You can insert multiple rows into a table by doing the following (works for SQL 7 & 2000, not sure about MySQL):

insert into yourtable(field1, field2, field3)
select
customerid,
firstname,
lastname
from
customers
where
customerid between 1 and 10

This would insert several rows from the customers table into yourtable.|||youre kiddin me right tingent? That is absolutely off the subject of what we are talking about|||"What it meant in MySQL is that if there is an existing row that has the same primary key value as one of the new rows being inserted, it will replace that old row with the new row."

Surely, though, by definition you will only ever have ONE row that matches the primary key so isn't this in effect an UPDATE statement? Or does the replace statement insert a new row if a match isn't found (you didn't mention it did!) ?|||Ouch...ok, I admit I didn't completly read the post so I didn't know what REPLACE was doing in MySQL. My bad...|||But the SQL i wrote does exactly what the the thread title says, Multi-row insert statement.|||>> But the SQL i wrote does exactly what the the thread title says, Multi-row insert statement

True but you do need to read the question. I think you caused a tiny little bit of offense 'cause judging by the question I think they know how do a standard insert. Don't worry though, no ones perfect - and don't let some of the posters tell you otherwise ;)|||I apologize, I didn't mean it at all the way it sounded. Sometimes things don't come off the right way when you type them over the internet. If we were in person and I would've said that to you, you would've took it the right way because I would've said it in a joking way.

Sorry|||Tingent, again, I apologize for the way that sounded.

The Mult-Row insert statement that you have extracts rows from another area and inserts them into the table. I need to insert brand new rows.|||hat's off to you javan15, what I jolly good egg you are.

Multiple Y-Axis values in report

I will try to explain this best I can. I have a report where it shows total number of pieces over time. This range is from 0 to like 100 pieces. I have done a UNION query to add average accumulation (in inches).

If adding average accumulation to the "Data fields" section of the report in report designer, It shows the average accumulation on the bottom in releation to the number of pieces. (which is what I want.. But.. ) The numbers are not proportional obviously there wont be 60 inches of snow.. more like 1 or two inches.. So the line is very close to the bottom almost invisible..

Now that ive dont a muck up job of explaining.. This is what im looking for.. Some type of chart that will overlay the average accumulation but somehow be smart enough to show the different scale of precipitation vs pieces of equipment.

VS 2003 SQL Server 2000

Multiple y-axis are currently not supported through the built-in charts in Reporting Services. However, with RS 2005 there is a new feature called CustomReportItem which third party ISVs (Dundas, ChartFX, etc.) take advantage of to integrate their own charting solutions directly into RS 2005. These charts do support multiple y-axis within RS 2005.

-- Robert

|||It close to 1 year since the previous reply. Is multiple y-axis currently supported in reporting service now?

Multiple Y-Axis values in report

I will try to explain this best I can. I have a report where it shows total number of pieces over time. This range is from 0 to like 100 pieces. I have done a UNION query to add average accumulation (in inches).

If adding average accumulation to the "Data fields" section of the report in report designer, It shows the average accumulation on the bottom in releation to the number of pieces. (which is what I want.. But.. ) The numbers are not proportional obviously there wont be 60 inches of snow.. more like 1 or two inches.. So the line is very close to the bottom almost invisible..

Now that ive dont a muck up job of explaining.. This is what im looking for.. Some type of chart that will overlay the average accumulation but somehow be smart enough to show the different scale of precipitation vs pieces of equipment.

VS 2003 SQL Server 2000

Multiple y-axis are currently not supported through the built-in charts in Reporting Services. However, with RS 2005 there is a new feature called CustomReportItem which third party ISVs (Dundas, ChartFX, etc.) take advantage of to integrate their own charting solutions directly into RS 2005. These charts do support multiple y-axis within RS 2005.

-- Robert

|||It close to 1 year since the previous reply. Is multiple y-axis currently supported in reporting service now?

Multiple variables to an IN sstatement

How can I pass a list of comma seperated values to an IN statememnt?
ie
@.parameter = '1,2,3,4'
select * from table_name where column_name in (@.parameter)
This can be executed dynamically, but is there any other method?In your stored procedure declare a table to hold the data.
Parse the input paramater into the table.
DECLARE @.InList table
( parm int)
)
parse the parameter
Select * from table_name where column_name in (select parm from @.InList)|||No, you cannot do that
1)
Dejan Sarka has posted this script
IF OBJECT_ID('dbo.TsqlSplit') IS NOT NULL
DROP FUNCTION dbo.TsqlSplit
GO
CREATE FUNCTION dbo.TsqlSplit
(@.List As varchar(8000))
RETURNS @.Items table (Item varchar(8000) Not Null)
AS
BEGIN
DECLARE @.Item As varchar(8000), @.Pos As int
WHILE DATALENGTH(@.List)>0
BEGIN
SET @.Pos=CHARINDEX(',',@.List)
IF @.Pos=0 SET @.Pos=DATALENGTH(@.List)+1
SET @.Item = LTRIM(RTRIM(LEFT(@.List,@.Pos-1)))
IF @.Item<>'' INSERT INTO @.Items SELECT @.Item
SET @.List=SUBSTRING(@.List,@.Pos+DATALENGTH(','),8000)
END
RETURN
END
GO
/* Usage example */
SELECT t1.*
FROM TsqlSplit('10428,10429') AS t1
declare @.inList varchar(50)
set @.inList='10428,10429'
select od.* from [order details] od
INNER JOIN
(SELECT Item
FROM dbo.TsqlSplit(@.InList)) As t
ON od.orderid = t.Item
2)
declare @.sql varchar(50)
set @.sql='1,2,4'
select * from table where charindex(',' + id+ ',',','+ @.sql+',')>0
<newscorrespondent@.charter.net> wrote in message
news:vXEEg.27$Lo3.3@.newsfe07.lga...
> In your stored procedure declare a table to hold the data.
> Parse the input paramater into the table.
> DECLARE @.InList table
> ( parm int)
> )
> parse the parameter
> Select * from table_name where column_name in (select parm from @.InList)|||pravin.nagarajan@.gmail.com wrote:
> How can I pass a list of comma seperated values to an IN statememnt?
> ie
> @.parameter = '1,2,3,4'
> select * from table_name where column_name in (@.parameter)
> This can be executed dynamically, but is there any other method?
>
I have a short post on my web site describing how to do this, but the
site is currently being reconstructed. You can find the original post
in Google's cache by searching for
"www.realsqlguy.com/twiki/bin/view/RealSQLGuy/FindingTheLatestValue"
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks I found this solution very helpful.It works
Uri Dimant wrote:
> No, you cannot do that
> 1)
> Dejan Sarka has posted this script
> IF OBJECT_ID('dbo.TsqlSplit') IS NOT NULL
> DROP FUNCTION dbo.TsqlSplit
> GO
> CREATE FUNCTION dbo.TsqlSplit
> (@.List As varchar(8000))
> RETURNS @.Items table (Item varchar(8000) Not Null)
> AS
> BEGIN
> DECLARE @.Item As varchar(8000), @.Pos As int
> WHILE DATALENGTH(@.List)>0
> BEGIN
> SET @.Pos=CHARINDEX(',',@.List)
> IF @.Pos=0 SET @.Pos=DATALENGTH(@.List)+1
> SET @.Item = LTRIM(RTRIM(LEFT(@.List,@.Pos-1)))
> IF @.Item<>'' INSERT INTO @.Items SELECT @.Item
> SET @.List=SUBSTRING(@.List,@.Pos+DATALENGTH(','),8000)
> END
> RETURN
> END
> GO
> /* Usage example */
> SELECT t1.*
> FROM TsqlSplit('10428,10429') AS t1
>
> declare @.inList varchar(50)
> set @.inList='10428,10429'
> select od.* from [order details] od
> INNER JOIN
> (SELECT Item
> FROM dbo.TsqlSplit(@.InList)) As t
> ON od.orderid = t.Item
> 2)
> declare @.sql varchar(50)
> set @.sql='1,2,4'
> select * from table where charindex(',' + id+ ',',','+ @.sql+',')>0
>
> <newscorrespondent@.charter.net> wrote in message
> news:vXEEg.27$Lo3.3@.newsfe07.lga...
> > In your stored procedure declare a table to hold the data.
> > Parse the input paramater into the table.
> >
> > DECLARE @.InList table
> > ( parm int)
> > )
> >
> > parse the parameter
> >
> > Select * from table_name where column_name in (select parm from @.InList)sql

Multiple variables to an IN sstatement

How can I pass a list of comma seperated values to an IN statememnt?
ie
@.parameter = '1,2,3,4'
select * from table_name where column_name in (@.parameter)
This can be executed dynamically, but is there any other method?In your stored procedure declare a table to hold the data.
Parse the input paramater into the table.
DECLARE @.InList table
( parm int)
)
parse the parameter
Select * from table_name where column_name in (select parm from @.InList)|||No, you cannot do that
1)
Dejan Sarka has posted this script
IF OBJECT_ID('dbo.TsqlSplit') IS NOT NULL
DROP FUNCTION dbo.TsqlSplit
GO
CREATE FUNCTION dbo.TsqlSplit
(@.List As varchar(8000))
RETURNS @.Items table (Item varchar(8000) Not Null)
AS
BEGIN
DECLARE @.Item As varchar(8000), @.Pos As int
WHILE DATALENGTH(@.List)>0
BEGIN
SET @.Pos=CHARINDEX(',',@.List)
IF @.Pos=0 SET @.Pos=DATALENGTH(@.List)+1
SET @.Item = LTRIM(RTRIM(LEFT(@.List,@.Pos-1)))
IF @.Item<>'' INSERT INTO @.Items SELECT @.Item
SET @.List=SUBSTRING(@.List,@.Pos+DATALENGTH(',
'),8000)
END
RETURN
END
GO
/* Usage example */
SELECT t1.*
FROM TsqlSplit('10428,10429') AS t1
declare @.inList varchar(50)
set @.inList='10428,10429'
select od.* from [order details] od
INNER JOIN
(SELECT Item
FROM dbo.TsqlSplit(@.InList)) As t
ON od.orderid = t.Item
2)
declare @.sql varchar(50)
set @.sql='1,2,4'
select * from table where charindex(',' + id+ ',',','+ @.sql+',')>0
<newscorrespondent@.charter.net> wrote in message
news:vXEEg.27$Lo3.3@.newsfe07.lga...
> In your stored procedure declare a table to hold the data.
> Parse the input paramater into the table.
> DECLARE @.InList table
> ( parm int)
> )
> parse the parameter
> Select * from table_name where column_name in (select parm from @.InList)|||pravin.nagarajan@.gmail.com wrote:
> How can I pass a list of comma seperated values to an IN statememnt?
> ie
> @.parameter = '1,2,3,4'
> select * from table_name where column_name in (@.parameter)
> This can be executed dynamically, but is there any other method?
>
I have a short post on my web site describing how to do this, but the
site is currently being reconstructed. You can find the original post
in Google's cache by searching for
"www.realsqlguy.com/twiki/bin/view/RealSQLGuy/FindingTheLatestValue"
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks I found this solution very helpful.It works
Uri Dimant wrote:[vbcol=seagreen]
> No, you cannot do that
> 1)
> Dejan Sarka has posted this script
> IF OBJECT_ID('dbo.TsqlSplit') IS NOT NULL
> DROP FUNCTION dbo.TsqlSplit
> GO
> CREATE FUNCTION dbo.TsqlSplit
> (@.List As varchar(8000))
> RETURNS @.Items table (Item varchar(8000) Not Null)
> AS
> BEGIN
> DECLARE @.Item As varchar(8000), @.Pos As int
> WHILE DATALENGTH(@.List)>0
> BEGIN
> SET @.Pos=CHARINDEX(',',@.List)
> IF @.Pos=0 SET @.Pos=DATALENGTH(@.List)+1
> SET @.Item = LTRIM(RTRIM(LEFT(@.List,@.Pos-1)))
> IF @.Item<>'' INSERT INTO @.Items SELECT @.Item
> SET @.List=SUBSTRING(@.List,@.Pos+DATALENGTH(',
'),8000)
> END
> RETURN
> END
> GO
> /* Usage example */
> SELECT t1.*
> FROM TsqlSplit('10428,10429') AS t1
>
> declare @.inList varchar(50)
> set @.inList='10428,10429'
> select od.* from [order details] od
> INNER JOIN
> (SELECT Item
> FROM dbo.TsqlSplit(@.InList)) As t
> ON od.orderid = t.Item
> 2)
> declare @.sql varchar(50)
> set @.sql='1,2,4'
> select * from table where charindex(',' + id+ ',',','+ @.sql+',')>0
>
> <newscorrespondent@.charter.net> wrote in message
> news:vXEEg.27$Lo3.3@.newsfe07.lga...

multiple variables from query

Hi,
I want to assign multiple values from a query to local variables.
I can do it with a single one like:
SET @.Myvar = (SELECT FirstNameFROM People)
But if I want to select more then one field, I don't know how to do this.
SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't work.
I could do two queries, or write a cursor, but that sounds inefficient to
me,
Does anyone have a solution?
Thanks,
Leo
SELECT @.myVar = FirstName, @.MyVar2 = LastName FROM People
Jacco Schalkwijk
SQL Server MVP
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>
|||Leo
Declare @.var1 INT, @.var2 INT
SELECT @.var1=col1,@.var2=col2 FROM Table
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>
|||Hi,
You can write the query like the below,
Select @.Myvar = FirstName, @.Myvar2 = LastName FROM People
Thanks
Hari
MCDBA
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>
|||Thank you for all the replies.
It works now!
Leo
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>

multiple variables from query

Hi,
I want to assign multiple values from a query to local variables.
I can do it with a single one like:
SET @.Myvar = (SELECT FirstNameFROM People)
But if I want to select more then one field, I don't know how to do this.
SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't work.
I could do two queries, or write a cursor, but that sounds inefficient to
me,
Does anyone have a solution?
Thanks,
LeoSELECT @.myVar = FirstName, @.MyVar2 = LastName FROM People
Jacco Schalkwijk
SQL Server MVP
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>|||Leo
Declare @.var1 INT, @.var2 INT
SELECT @.var1=col1,@.var2=col2 FROM Table
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>|||Hi,
You can write the query like the below,
Select @.Myvar = FirstName, @.Myvar2 = LastName FROM People
Thanks
Hari
MCDBA
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>|||Thank you for all the replies.
It works now!
Leo
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>sql

multiple variables from query

Hi,
I want to assign multiple values from a query to local variables.
I can do it with a single one like:
SET @.Myvar = (SELECT FirstNameFROM People)
But if I want to select more then one field, I don't know how to do this.
SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't work.
I could do two queries, or write a cursor, but that sounds inefficient to
me,
Does anyone have a solution?
Thanks,
LeoSELECT @.myVar = FirstName, @.MyVar2 = LastName FROM People
--
Jacco Schalkwijk
SQL Server MVP
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>|||Leo
Declare @.var1 INT, @.var2 INT
SELECT @.var1=col1,@.var2=col2 FROM Table
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>|||Hi,
You can write the query like the below,
Select @.Myvar = FirstName, @.Myvar2 = LastName FROM People
Thanks
Hari
MCDBA
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>|||Thank you for all the replies.
It works now!
Leo
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>

Multiple Values to single parameter

Hi,
I have a situation where in I got to pass more than one value to a single parameter like I want to see all the employees of department 5,6 & 7 & so on, my query goes like this "Select firstname,lastname, title from employees where departmentid=@.departmentid". In this variable @.deparmentid I need to pass more than one value. is it possible? if it can, dude's pull it fast to me.

Hi adonis

It is possible I use SQL to check for a comma seperated list. Create this function and pass it a comma seperated list, it will return a table with the values. Change your SQL to something like this:
"Select firstname,lastname, title from employees where departmentid IN ( SELECT [ENTRY] FROM ListToTable(@.departmentid))". It should return the desired result. However you should add maybe an IF to check if the user want all employees and not just a few

IF EXISTS (SELECT * FROM sysobjects WHERE name = N'ListToTable') DROP FUNCTION ListToTable GO

CREATE FUNCTION ListToTable
(
/*
** Usage: select entry from listtotable('abc,def,ghi') order by entry desc
*/
@.mylist varchar(8000) )
RETURNS @.ListTable TABLE
(
seqid int not null,
entry varchar(255) not null
)

AS

BEGIN
DECLARE @.this varchar(255),
@.rest varchar(8000),
@.pos int,
@.seqid int

SET @.this = ' ' SET @.seqid = 1 SET @.rest = @.mylist SET @.pos = PATINDEX('%,%', @.rest) WHILE (@.pos > 0) BEGIN set @.this=substring(@.rest,1,@.pos-1) set @.rest=substring(@.rest,@.pos+1,len(@.rest)-@.pos) INSERT INTO @.ListTable (seqid,entry) VALUES (@.seqid,@.this) SET @.pos= PATINDEX('%,%', @.rest) SET @.seqid=@.seqid+1 END set @.this=@.rest INSERT INTO @.ListTable (seqid,entry) VALUES (@.seqid,@.this)
RETURN
END
, Hope this helps
l0n3i200n

|||

I really appriciate your effort. but dont you think it all a work around we are fiddling the query here, Insted I was looking for any option in the reporting services thru wich we can send multiple values. Your solution is feasible when you have a small query but you Iam playing with arount more that 25 tables the queries are so complex that if I fiddle those than Its gona suck me.

Any ways thanks and if U have any thing coming up please update me.

Multiple Values to select all values or deselect all values

I have 3 multi select report parameters on my report. Let's say a
user selects only a few of the values in each of the report parameter
drop downs but know they want to select all of the values with a click
of option button.
Then what I would like to do is have an option button that will allow
the user to select all of the option values or deselect all of the
values for all pararmeters by just selecting True or False from the
option button. Is there a way of doing this in Reporting Services?
Thanks for any help proved!On Jul 20, 11:01 am, trevorfuller1...@.gmail.com wrote:
> I have 3 multi select report parameters on my report. Let's say a
> user selects only a few of the values in each of the report parameter
> drop downs but know they want to select all of the values with a click
> of option button.
> Then what I would like to do is have an option button that will allow
> the user to select all of the option values or deselect all of the
> values for all pararmeters by just selecting True or False from the
> option button. Is there a way of doing this in Reporting Services?
> Thanks for any help proved!
There is not something built in to SSRS already; however, you could
create the functionality. You could have a parameter that if its value
is selected as True, you could send an 'All' type value back to the
stored procedure/query that is sourcing the report and then the stored
procedure could determine what 'All' means and select everything. Hope
this helps.
Regards,
Enrique Martinez
Sr. Software Consultantsql

Multiple Values Returned from Stored Proc

I need to return 2 values from a stroed proc. Can you have more than one
output parameters in a stored proc? If not, would bringing back a dataset b
e
better than two round trips to the database to get the 2 values I need?
--
Robert HillRobert wrote:
> I need to return 2 values from a stroed proc. Can you have more than
> one output parameters in a stored proc? If not, would bringing back
> a dataset be better than two round trips to the database to get the 2
> values I need?
Output variables are generally faster to return than generating a 1 row
result set. You can have more than one output parameter in a procedure.
I would tell you to determine if, in fact, the results should be
returned as a resultset or as output parameters. If you think that
additional values may need to be returned in the future or if you think
there might be a time when more than one row needs to be returned, it's
better to use a resultset so you don't have to mess with the interface
of the procedure.
David Gugick
Imceda Software
www.imceda.com|||I am using the Microsoft Application Block for DataAccess and I cannot find
a
suitable procedure to call to bring back two output parameters. If this is
correct, I will need to extend the Application Block to include a procedure
to do what it is I need. Is this correct?
"David Gugick" wrote:

> Robert wrote:
> Output variables are generally faster to return than generating a 1 row
> result set. You can have more than one output parameter in a procedure.
> I would tell you to determine if, in fact, the results should be
> returned as a resultset or as output parameters. If you think that
> additional values may need to be returned in the future or if you think
> there might be a time when more than one row needs to be returned, it's
> better to use a resultset so you don't have to mess with the interface
> of the procedure.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Robert wrote:
> I am using the Microsoft Application Block for DataAccess and I
> cannot find a suitable procedure to call to bring back two output
> parameters. If this is correct, I will need to extend the
> Application Block to include a procedure to do what it is I need. Is
> this correct?
>
I plead ignorance. I have never used the Microsoft Application Block for
DataAccess. I don't know how the object model looks. I assume it's a
high-level view of ADO.Net. Where do you define parameters for the
stored procedures. Make sure you're using the latest release, now called
the "Enterprise Library Patterns and Practices Library"
http://msdn.microsoft.com/library/d...li
b.asp
David Gugick
Imceda Software
www.imceda.com

multiple values per table cell?

I am converting a site from asp to .net and the previus writer did something I never saw.(I am somewhat new to programming all together). He put multiple values in a single cell of the database. I have always learned to use a seperate column for each value.
Is this a classic asp thing and there are better ways now? Or is this something I should do myself? The columns type is text and the contents look like this:

<Details Expertise="bla bla" Description="We are a three-divisional company ...," WebSite="www.blabla.com" AccountLevel="Free" AccountStatus="Active" WorkHomeZIP="22222" ContractStartDate="01/01/2003" ContractEndDate="12/31/2003"><Address Location="Work" State="Oh" ZIP="22222"/><Email Location="Work" Value=""/><Phone Location="Fax" Code="9801" Ext=""/></Details>

Putting aside the question of the style being used for a moment, the contents of the column is not *really* multiple values, but a single XML string. This allows it to be easily read into a XmlDocument object and parsed.

As for the reason behind this approach, the only one I can thing of would be to store dynamic categories of information that is not known at design time. While none come to mind immediately, I'm sure that there might be times when this approach is quite valid. It is *not*, however, a classic ASP thing, but a database design decision. As to whether you should change the approach, I would if the attributes in the XML string were always the same. Better to use a database to do what databases were intended to do ;)

HTH

Multiple Values on Point Labels

Hellow all,
Is there a way to show multiple point labels on a pie chart? I have the pie
showing the percentage using the following format :
=Sum(Fields!YTD.Value)/Sum(Fields!YTD.Value, "chart1")
format code: %.0
I want to add the YTD value on a separate line so I will need to add a
return then add some code like Sum(Fields!YTD.Value).
I can get the two values to join by using a + sign but the number run
together with the same format code. I need the first line to show as a
percent and the second on a new line as a whole number.
Has anyone done this or know how to do it? In Excel this was easy but I am
unsure how to do it in SRS.
Thanks,
AnthonyUse the Format function to apply certain format codes to parts of your label
string. E.g.:
=Format(Sum(Fields!YTD.Value)/Sum(Fields!YTD.Value, "chart1"), "P1") &
vbcrlf & Format(Sum(Fields!YTD.Value), "C0")
See also:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctFormat.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
news:08E789F2-6DA2-4A6F-ADF4-98BF617C52DE@.microsoft.com...
> Hellow all,
> Is there a way to show multiple point labels on a pie chart? I have the
> pie
> showing the percentage using the following format :
> =Sum(Fields!YTD.Value)/Sum(Fields!YTD.Value, "chart1")
> format code: %.0
> I want to add the YTD value on a separate line so I will need to add a
> return then add some code like Sum(Fields!YTD.Value).
> I can get the two values to join by using a + sign but the number run
> together with the same format code. I need the first line to show as a
> percent and the second on a new line as a whole number.
> Has anyone done this or know how to do it? In Excel this was easy but I
> am
> unsure how to do it in SRS.
> Thanks,
> Anthony|||Robert,
Thanks for all the help!!! I won't get to try this until tomorrow but it
makes sense. I think my biggest problem is I am not a "programmer" by trade.
I am getting pretty good with SQL but I know exactly where I need to look by
using the Transact SQL help that comes with SQL server 2000. Is there a
specific place that I can look that gives me examples of how to code for SRS?
I need to be able to look up functions and such then be able to translate
them. Also what language are you using for most of the examples that you
provide? I think I have the option to use any of the .net languages but I am
not sure. If I had to pick I think Visual Basic would be the best...I know
a little from Access.
Thanks again for all the help!!!
"Robert Bruckner [MSFT]" wrote:
> Use the Format function to apply certain format codes to parts of your label
> string. E.g.:
> =Format(Sum(Fields!YTD.Value)/Sum(Fields!YTD.Value, "chart1"), "P1") &
> vbcrlf & Format(Sum(Fields!YTD.Value), "C0")
> See also:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctFormat.asp
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
> news:08E789F2-6DA2-4A6F-ADF4-98BF617C52DE@.microsoft.com...
> > Hellow all,
> >
> > Is there a way to show multiple point labels on a pie chart? I have the
> > pie
> > showing the percentage using the following format :
> > =Sum(Fields!YTD.Value)/Sum(Fields!YTD.Value, "chart1")
> > format code: %.0
> >
> > I want to add the YTD value on a separate line so I will need to add a
> > return then add some code like Sum(Fields!YTD.Value).
> >
> > I can get the two values to join by using a + sign but the number run
> > together with the same format code. I need the first line to show as a
> > percent and the second on a new line as a whole number.
> >
> > Has anyone done this or know how to do it? In Excel this was easy but I
> > am
> > unsure how to do it in SRS.
> >
> > Thanks,
> > Anthony
>
>|||The RDL expression language is based on VB.NET and has additional built-in
RS specific functions.
Use this as a starting point - it will lead to built-in RS aggregate
functions, various object collections in the ReportObjectModel, etc.:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_expressions_v1_6fhv.asp
Besides that, you can use ALL functions available in the VB.NET run-time
library:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vaoriVBRuntimeLibraryKeywords.asp
And by default, you can use everything that is contained within the
following .NET framework namespaces:
* System
* System.Math
* System.Convert
* Microsoft.VisualBasic
Furthermore, you can add custom code or reference custom assemblies (written
in any .NET language).
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
news:A328F9B7-3070-4D9A-8D86-51EA26E906E9@.microsoft.com...
> Robert,
> Thanks for all the help!!! I won't get to try this until tomorrow but it
> makes sense. I think my biggest problem is I am not a "programmer" by
> trade.
> I am getting pretty good with SQL but I know exactly where I need to look
> by
> using the Transact SQL help that comes with SQL server 2000. Is there a
> specific place that I can look that gives me examples of how to code for
> SRS?
> I need to be able to look up functions and such then be able to translate
> them. Also what language are you using for most of the examples that you
> provide? I think I have the option to use any of the .net languages but I
> am
> not sure. If I had to pick I think Visual Basic would be the best...I
> know
> a little from Access.
> Thanks again for all the help!!!
> "Robert Bruckner [MSFT]" wrote:
>> Use the Format function to apply certain format codes to parts of your
>> label
>> string. E.g.:
>> =Format(Sum(Fields!YTD.Value)/Sum(Fields!YTD.Value, "chart1"), "P1") &
>> vbcrlf & Format(Sum(Fields!YTD.Value), "C0")
>> See also:
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctFormat.asp
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
>> news:08E789F2-6DA2-4A6F-ADF4-98BF617C52DE@.microsoft.com...
>> > Hellow all,
>> >
>> > Is there a way to show multiple point labels on a pie chart? I have
>> > the
>> > pie
>> > showing the percentage using the following format :
>> > =Sum(Fields!YTD.Value)/Sum(Fields!YTD.Value, "chart1")
>> > format code: %.0
>> >
>> > I want to add the YTD value on a separate line so I will need to add a
>> > return then add some code like Sum(Fields!YTD.Value).
>> >
>> > I can get the two values to join by using a + sign but the number run
>> > together with the same format code. I need the first line to show as a
>> > percent and the second on a new line as a whole number.
>> >
>> > Has anyone done this or know how to do it? In Excel this was easy but
>> > I
>> > am
>> > unsure how to do it in SRS.
>> >
>> > Thanks,
>> > Anthony
>>|||Thanks again this information is very helpful!!!
"Robert Bruckner [MSFT]" wrote:
> The RDL expression language is based on VB.NET and has additional built-in
> RS specific functions.
> Use this as a starting point - it will lead to built-in RS aggregate
> functions, various object collections in the ReportObjectModel, etc.:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_expressions_v1_6fhv.asp
> Besides that, you can use ALL functions available in the VB.NET run-time
> library:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vaoriVBRuntimeLibraryKeywords.asp
> And by default, you can use everything that is contained within the
> following .NET framework namespaces:
> * System
> * System.Math
> * System.Convert
> * Microsoft.VisualBasic
> Furthermore, you can add custom code or reference custom assemblies (written
> in any .NET language).
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
> news:A328F9B7-3070-4D9A-8D86-51EA26E906E9@.microsoft.com...
> > Robert,
> >
> > Thanks for all the help!!! I won't get to try this until tomorrow but it
> > makes sense. I think my biggest problem is I am not a "programmer" by
> > trade.
> > I am getting pretty good with SQL but I know exactly where I need to look
> > by
> > using the Transact SQL help that comes with SQL server 2000. Is there a
> > specific place that I can look that gives me examples of how to code for
> > SRS?
> > I need to be able to look up functions and such then be able to translate
> > them. Also what language are you using for most of the examples that you
> > provide? I think I have the option to use any of the .net languages but I
> > am
> > not sure. If I had to pick I think Visual Basic would be the best...I
> > know
> > a little from Access.
> >
> > Thanks again for all the help!!!
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> >> Use the Format function to apply certain format codes to parts of your
> >> label
> >> string. E.g.:
> >> =Format(Sum(Fields!YTD.Value)/Sum(Fields!YTD.Value, "chart1"), "P1") &
> >> vbcrlf & Format(Sum(Fields!YTD.Value), "C0")
> >>
> >> See also:
> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctFormat.asp
> >>
> >> -- Robert
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
> >> news:08E789F2-6DA2-4A6F-ADF4-98BF617C52DE@.microsoft.com...
> >> > Hellow all,
> >> >
> >> > Is there a way to show multiple point labels on a pie chart? I have
> >> > the
> >> > pie
> >> > showing the percentage using the following format :
> >> > =Sum(Fields!YTD.Value)/Sum(Fields!YTD.Value, "chart1")
> >> > format code: %.0
> >> >
> >> > I want to add the YTD value on a separate line so I will need to add a
> >> > return then add some code like Sum(Fields!YTD.Value).
> >> >
> >> > I can get the two values to join by using a + sign but the number run
> >> > together with the same format code. I need the first line to show as a
> >> > percent and the second on a new line as a whole number.
> >> >
> >> > Has anyone done this or know how to do it? In Excel this was easy but
> >> > I
> >> > am
> >> > unsure how to do it in SRS.
> >> >
> >> > Thanks,
> >> > Anthony
> >>
> >>
> >>
>
>