sql server reporting services: printing a single row of data from a dataset -- sql server reporting services

You're in the

sql server reporting services

group:

printing a single row of data from a dataset

printing a single row of data from a dataset

William Byrd
8/2/2005 12:34:13 PM

sql server reporting services: I know that this has to be possible, but my google searches have not been
effective...so here goes

I have create a report that runs a stored procedure that accepts parameters
beginDate, endDate and LocationID and returns a dataset with LocationID,
totalPeople, peopleMeetingCriteria, PCT and showPCT, the ShowPCT is 0 unless
LocationID at that row matches your location ID, so that when I graph it, I
can set showPCt as the series field and it overlays your locationID in a
different color. (yes, this is necessary as I can show how your location
compares to others, but I can not label the others and make them
identifiable, so I can not label the locations)

The problem is that I would like to also print the totalPeople,
peopleMeetingCriteria and PCT values as text. Currently, I execute an almost
identical stored procedure that returns only a single row of data (where the
locationID is you location) but this is inefficient (as the stored pro has
to run a second time to return a row that is already returned once.) Is
there a way to print in a text field the value of a single field
(Fields!PCt.Value) from a dataset only from the row where
Field!locationID.value = Parameters!LocationID.value

I tried something like putting =Fields!PCT.Value where
Field!locationID.value = Parameters!locationID.value and of course this
returns an error when I attempt this. I know there has to be a better way.
If the stored procedure only took a second or so to return data, I would not
care so much, but the Stored Proc is aggregating data on millions of rows
from hundreds of locations and takes about 35 seconds to return data.... I
would prefer to be able to get my data by only running the procedure once.
35 seconds is better than 70 or more.

Thanks in advance for any help anyone may have.
Bill

Re: printing a single row of data from a dataset

Wayne Snyder
8/2/2005 1:12:34 PM

Try adding a FILTER expression, to remove all of the rows except the one you
wish to print...

--
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.
[quoted text, click to view]

Re: printing a single row of data from a dataset

William Byrd
8/2/2005 1:22:33 PM

I want to have that dataset used in its entirety for a graph in the same
report, but just print that one locations absolute numbers as well... If I
filter, it would filter the results for the graph as well woudl it not, and
if I create a new dataset that does have a filter, it would run the stored
procedure again, or am I getting something wrong?

Thanks for the suggestion and any help

Bill

[quoted text, click to view]

Re: printing a single row of data from a dataset

William Byrd
8/2/2005 4:06:39 PM

okay, piece of info
if I put the data in a table on the page, and I add a filter to the table of
=Fields!LocationID.Value = 13
this will work and show a table with a single row of data, that for location
13, but the locationID is
parameterized and when I try to use a filter of =Fields!LocationID.Value =
Parameters!LocationID.Value (both are INTs)
it bombs with the following error

"An error has occured during report processing.
The processing of filter expression for 'table1' cannot be performed. The
comparison failed. Please check the data type returned by filter expression"

Any clues?

Bill

[quoted text, click to view]

Re: printing a single row of data from a dataset

Gastón Pírez
8/2/2005 8:36:44 PM

Try with this:

CINT(Fields!LocationID.Value) = CINT(Parameters!LocationID.Value)

I've had the same problem few days ago....

Gastón.-

[quoted text, click to view]

Re: printing a single row of data from a dataset

William Byrd
8/3/2005 11:26:11 AM

Gastón, my friend, you are a genius. This was exactly what was wrong. Even
though I specified the parameter as an INT, and even though the field beign
returned from the database is an INT, apparently, someplace, somehow, it
became a non-int (does, RS just consider these things variants until cast?)
and adding a CINT inline makes the magic happen.

My reports thank you, I thank you, and my client thanks you

Bill

[quoted text, click to view]

Re: printing a single row of data from a dataset

allanrocha
8/14/2006 7:53:30 AM

Gastn Prez,

RE: printing a single row of data from a dataset

Ravi
8/14/2006 9:12:02 AM

Try grouping
--
THANKS & PLEASE RATE THE POSTING.
--RAVI--

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.

home · blog · groups

Questions? Comments? Contact the dn