Return Different Fields Based On Expression

Hello Out There,
I have a dataset set up to return a set of billing information. I have this
information displayed in a matrix. However I have requirement where;
a) If the account type = ODC then return the vendor_name field
b) If the account type = ODC and venor_name field is null then return the
employee_name field
c) In any other case return the employee_name field.
Is there a way to set up an expression in a text box on the detail line of a
matrix to reflect the info needed?
There's a little more to be desired with my expression syntax skills so any
help would be greatly appreciated.
ThanksYou should do this in the SQL and return it in a single column.
In SQL Server,
NameField = CASE
WHEN accountType = ODC
CASE
WHEN vendor_name is null then employee_name
ELSE vendor_name
END
else
EMPLOYEE_NAME
END
-tIM
"JDArsenault" <JDArsenault@.discussions.microsoft.com> wrote in message
news:2ADF13EE-A421-4F4A-8E5E-A47C1D55D89B@.microsoft.com...
> Hello Out There,
> I have a dataset set up to return a set of billing information. I have
> this
> information displayed in a matrix. However I have requirement where;
> a) If the account type = ODC then return the vendor_name field
> b) If the account type = ODC and venor_name field is null then return the
> employee_name field
> c) In any other case return the employee_name field.
> Is there a way to set up an expression in a text box on the detail line of
> a
> matrix to reflect the info needed?
> There's a little more to be desired with my expression syntax skills so
> any
> help would be greatly appreciated.
> Thanks|||I often don't see the forest through the trees. Thanks for the quick help.
JD
"Tim Dot NoSpam" wrote:
> You should do this in the SQL and return it in a single column.
> In SQL Server,
> NameField = CASE
> WHEN accountType = ODC
> CASE
> WHEN vendor_name is null then employee_name
> ELSE vendor_name
> END
> else
> EMPLOYEE_NAME
> END
> -tIM
> "JDArsenault" <JDArsenault@.discussions.microsoft.com> wrote in message
> news:2ADF13EE-A421-4F4A-8E5E-A47C1D55D89B@.microsoft.com...
> > Hello Out There,
> >
> > I have a dataset set up to return a set of billing information. I have
> > this
> > information displayed in a matrix. However I have requirement where;
> >
> > a) If the account type = ODC then return the vendor_name field
> > b) If the account type = ODC and venor_name field is null then return the
> > employee_name field
> > c) In any other case return the employee_name field.
> >
> > Is there a way to set up an expression in a text box on the detail line of
> > a
> > matrix to reflect the info needed?
> >
> > There's a little more to be desired with my expression syntax skills so
> > any
> > help would be greatly appreciated.
> >
> > Thanks
>
>

Return dataset in one column

Hi there

I have the following two tables

mainprofile (profile varchar(20), description)
accprofile (profile varchar(20), acct_type int)

Sample data could be

mainprofile
------
prof1 | profile one
prof2 | profile two
prof3 | profile three

accprofile
-----

prof1 | 0
prof1 | 1
prof1 | 2
prof2 | 0

Now doing a join between these two tables would return multiple rows,
but I would like to know whether it would be possible to return
acct_type horizontally in a column of the result set, e.g.

prof1 | profile one | [0,1,2]
prof2 | profile two | [0]

I could probably manage this with cursors, but it would be very
resource intensive. Is there a better way?

Regards,
LouisFor a one time data display or if this is used by a single application or a
report, you should consider retrieving the resultset to the client side,
leverage the display/presentation language's string manipulative features
and appropriately format the data there.

If this is more of a general requirement and used by several applications,
in certain cases it may make some sense to do it at the server using t-SQL.
For some options see: http://www.projectdmx.com/tsql/rowconcatenate.aspx
--
Anith|||

Quote:

Originally Posted by

For some options see: http://www.projectdmx.com/tsql/rowconcatenate.aspx

Thanks. In the end I decided to stick with using a CURSOR

Regards,
Louis

Retrieving RowCount from a dataset

Hi,
I need to calculate a percentage of population based on the number of rows
returned on a dataset. Within a stored procedure, I could use @.@.ROWCOUNT but
there is no way for SRS to receive multiple result sets in a single data set.
I could execute the query twice, once for the count and second with the
count included as a column. I hate to run the query twice. What I could use
is a rowcount from the dataset in SRS, but I don't this property exists.
Does anyone have any ideas?
Thank you,
BobBob,
As you are using a SProc to return the recordset, just add the @.@.rowcount to
the end of the SELECT statement and it will be returned with each row. This
will allow a single call only, and the amount of 'extra' data returned (2
bytes per row maybe?) will be mnore efficient than recalling the SProc a
second time.
Tony
"Bob" wrote:
> Hi,
> I need to calculate a percentage of population based on the number of rows
> returned on a dataset. Within a stored procedure, I could use @.@.ROWCOUNT but
> there is no way for SRS to receive multiple result sets in a single data set.
> I could execute the query twice, once for the count and second with the
> count included as a column. I hate to run the query twice. What I could use
> is a rowcount from the dataset in SRS, but I don't this property exists.
> Does anyone have any ideas?
> Thank you,
> Bob|||Hi Tony,
I have already tried that idea. For each row, @.@.ROWCOUNT returns 1. Its
only valid after the select has completed. I was hoping that a rowcount
property would be available within SRS.
Thank you for the suggestion.
Bob
"Logicalman" wrote:
> Bob,
> As you are using a SProc to return the recordset, just add the @.@.rowcount to
> the end of the SELECT statement and it will be returned with each row. This
> will allow a single call only, and the amount of 'extra' data returned (2
> bytes per row maybe?) will be mnore efficient than recalling the SProc a
> second time.
> Tony
> "Bob" wrote:
> > Hi,
> >
> > I need to calculate a percentage of population based on the number of rows
> > returned on a dataset. Within a stored procedure, I could use @.@.ROWCOUNT but
> > there is no way for SRS to receive multiple result sets in a single data set.
> > I could execute the query twice, once for the count and second with the
> > count included as a column. I hate to run the query twice. What I could use
> > is a rowcount from the dataset in SRS, but I don't this property exists.
> > Does anyone have any ideas?
> >
> > Thank you,
> > Bob|||Bob,
Have you tried the CountRows function? (Syntax should be:
=CountRows("yourDataset")) Just a suggestion. I have never used this
technique.
Ernie
Bob wrote:
> Hi Tony,
> I have already tried that idea. For each row, @.@.ROWCOUNT returns 1. Its
> only valid after the select has completed. I was hoping that a rowcount
> property would be available within SRS.
> Thank you for the suggestion.
> Bob
> "Logicalman" wrote:
> > Bob,
> >
> > As you are using a SProc to return the recordset, just add the @.@.rowcount to
> > the end of the SELECT statement and it will be returned with each row. This
> > will allow a single call only, and the amount of 'extra' data returned (2
> > bytes per row maybe?) will be mnore efficient than recalling the SProc a
> > second time.
> >
> > Tony
> >
> > "Bob" wrote:
> >
> > > Hi,
> > >
> > > I need to calculate a percentage of population based on the number of rows
> > > returned on a dataset. Within a stored procedure, I could use @.@.ROWCOUNT but
> > > there is no way for SRS to receive multiple result sets in a single data set.
> > > I could execute the query twice, once for the count and second with the
> > > count included as a column. I hate to run the query twice. What I could use
> > > is a rowcount from the dataset in SRS, but I don't this property exists.
> > > Does anyone have any ideas?
> > >
> > > Thank you,
> > > Bob|||I figured out how to do this.
In the table HEADER section, which is the entire scope, I placed a
CountDistinct(fld!name.value) for the unique item in the dataset. Then I
made the column invisible. Lastly, I referenced this value within the table
via ReportItems!textboxname.value. This gave me the count I was looking for.
"Bob" wrote:
> Hi Tony,
> I have already tried that idea. For each row, @.@.ROWCOUNT returns 1. Its
> only valid after the select has completed. I was hoping that a rowcount
> property would be available within SRS.
> Thank you for the suggestion.
> Bob
> "Logicalman" wrote:
> > Bob,
> >
> > As you are using a SProc to return the recordset, just add the @.@.rowcount to
> > the end of the SELECT statement and it will be returned with each row. This
> > will allow a single call only, and the amount of 'extra' data returned (2
> > bytes per row maybe?) will be mnore efficient than recalling the SProc a
> > second time.
> >
> > Tony
> >
> > "Bob" wrote:
> >
> > > Hi,
> > >
> > > I need to calculate a percentage of population based on the number of rows
> > > returned on a dataset. Within a stored procedure, I could use @.@.ROWCOUNT but
> > > there is no way for SRS to receive multiple result sets in a single data set.
> > > I could execute the query twice, once for the count and second with the
> > > count included as a column. I hate to run the query twice. What I could use
> > > is a rowcount from the dataset in SRS, but I don't this property exists.
> > > Does anyone have any ideas?
> > >
> > > Thank you,
> > > Bob|||Bob,
Excellent result. We are all still on the learning curve, I'll have to
remember that one.
Regarding using the @.@.RowCount feature, you could also have used a simple
variable in the sproc and set it to Select Count(*) FROm tblename and then
passed that as an extra column in the final select statement.
Tony
"Ernie Gutierrez" wrote:
> Bob,
> Have you tried the CountRows function? (Syntax should be:
> =CountRows("yourDataset")) Just a suggestion. I have never used this
> technique.
> Ernie
> Bob wrote:
> > Hi Tony,
> >
> > I have already tried that idea. For each row, @.@.ROWCOUNT returns 1. Its
> > only valid after the select has completed. I was hoping that a rowcount
> > property would be available within SRS.
> >
> > Thank you for the suggestion.
> >
> > Bob
> >
> > "Logicalman" wrote:
> >
> > > Bob,
> > >
> > > As you are using a SProc to return the recordset, just add the @.@.rowcount to
> > > the end of the SELECT statement and it will be returned with each row. This
> > > will allow a single call only, and the amount of 'extra' data returned (2
> > > bytes per row maybe?) will be mnore efficient than recalling the SProc a
> > > second time.
> > >
> > > Tony
> > >
> > > "Bob" wrote:
> > >
> > > > Hi,
> > > >
> > > > I need to calculate a percentage of population based on the number of rows
> > > > returned on a dataset. Within a stored procedure, I could use @.@.ROWCOUNT but
> > > > there is no way for SRS to receive multiple result sets in a single data set.
> > > > I could execute the query twice, once for the count and second with the
> > > > count included as a column. I hate to run the query twice. What I could use
> > > > is a rowcount from the dataset in SRS, but I don't this property exists.
> > > > Does anyone have any ideas?
> > > >
> > > > Thank you,
> > > > Bob
>

Retrieve Dataset using RS Web Service?

I am using the RS web service to generate reports. I know it is possible to
generate lists of available reports, together with a list of required
parameters for each. But is it possible to get a list of valid values for
each parameter via the web service? i.e. retrieve the dataset results that
are used to poulate the filter when the report is viewed using Report
Manager. This would save me writing a query to populate a list in my custom
front end.Dave Morrow wrote:
<snip>
> But is it possible to get a list of valid values for
> each parameter via the web service? i.e. retrieve the dataset results that
> are used to poulate the filter when the report is viewed using Report
> Manager. This would save me writing a query to populate a list in my custom
> front end.
You can check to see if a parameter has ValidValuesQueryBased specified,
and then build a combobox from the ValidValues collection of the
ReportParameter object. I've written a small report launcher in C#
which does this and it works well.
-BA|||<snip>
> and then build a combobox from the ValidValues collection of the
> ReportParameter object.
<snip>
Edit: ValidValues is a property of the ReportParameter class, and is of
type ValidValue[].
-BA|||Thanks for your help, Brian. Much appreciated.
"Brian Almond" wrote:
> <snip>
> > and then build a combobox from the ValidValues collection of the
> > ReportParameter object.
> <snip>
> Edit: ValidValues is a property of the ReportParameter class, and is of
> type ValidValue[].
> -BA
>

Retrieve data from second dataset

The stored procedure returns 2 datasets. Is there any way to use the second dataset in SSRS2005?

I believe only the first dataset gets retrieved. You would have to create a wrapper stored procedure that would toggle between datasets based on a parameter, and then create 2 separate calls in Reporting Services.

cheers,

Andrew

Retrieve data from a SQLDatasource object.

I'm an "old" programmer but new to ASP.NET.

I want to get a value from the SQL Dataset.

What I would normally do in other environments is iterate through the dataset to get the value I would be intrested in, but I can't figure out how to do this without using a visual data display object like a Grid view.

Typically I want to get a value from the database that I then after manipulating it, like multply by 5, use to format something on the page.

thanks in advance,

Thommie

if you already have created the dataset then its easy to retrieve and iterate through rows.

for each row as datarow in dataset.tables(table index or name, 0 if its only table).rows

dim str as string=row(column name or index).tostring

Next

or

for i as integer=0 to dataset.tables(0).rows.count

var=dataset.tables(0).rows(i)(columnname or index)

next

hope this helps.

Eric

|||

Do you have a compelling reason to use a DataSet? If you are needing to return a single value, I'd look into using ExecuteScalar() off of the SqlCommand object or using an output parameter and getting your value like that. If you need some more explicit examples, let me know.

|||

Hi,

You can go through the DataSet using foreach, like

foreach(DataRow dr in DataSet.Table[0].Rows)
{
//use dr[0] to get the first column data.
}

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!