Data tab works great but i can't get it work in Preview tab in reporting services

This code works great in data tab but when i go to preview it i get a len function error.
I am passing possible BLANK values in all the strings not NULL. Do you think its an parameter expression issue? If so, how do I write a expression for each parameter that will reflect this logic. NOTE: As you can see states and program are multi param, and I don't know ahead of time what values will be entered. So if some could please help.
SELECT DISTINCT A.RECIPIENT_STATE, B.FISCAL_YEAR, B.FEDERAL_SHARE, B.NON_FEDERAL_SHARE
FROM Tab_Awards AS A JOIN TAB_Amendments AS B ON A.AWARD_NUMBER = B.AWARD_NUMBER JOIN Tab_Program AS C
ON A.FPO_ID = C.FPO_ID
WHERE (LEN(@.keywords)<1 OR (FREETEXT(A.*,@.keywords)))
AND (LEN(@.states)<1 OR A.RECIPIENT_STATE IN (@.states))
AND (LEN(@.program) <1 OR C.PROGRAM IN (@.program))
AND (LEN(@.fromYear)<1 OR B.FISCAL_YEAR >= @.fromYear)
AND (LEN(@.toYear)<1 OR B.FISCAL_YEAR <= @.toYear)
AND (LEN(@.year)<1 OR LEN(@.year)>=1)
AND (LEN(@.organization)<1 OR LEN(@.organization)>=1
From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comWhat exactly is the error ? Why I am asking is that len must be evaluating
some numeric value e.g year parameter must be having numeric and Len will
take string expression only. So Just have a look at all the parameters again
and see their data type.
Amarnath
"Dre" wrote:
> This code works great in data tab but when i go to preview it i get a len function error.
> I am passing possible BLANK values in all the strings not NULL. Do you think its an parameter expression issue? If so, how do I write a expression for each parameter that will reflect this logic. NOTE: As you can see states and program are multi param, and I don't know ahead of time what values will be entered. So if some could please help.
> SELECT DISTINCT A.RECIPIENT_STATE, B.FISCAL_YEAR, B.FEDERAL_SHARE, B.NON_FEDERAL_SHARE
> FROM Tab_Awards AS A JOIN TAB_Amendments AS B ON A.AWARD_NUMBER = B.AWARD_NUMBER JOIN Tab_Program AS C
> ON A.FPO_ID = C.FPO_ID
> WHERE (LEN(@.keywords)<1 OR (FREETEXT(A.*,@.keywords)))
> AND (LEN(@.states)<1 OR A.RECIPIENT_STATE IN (@.states))
> AND (LEN(@.program) <1 OR C.PROGRAM IN (@.program))
> AND (LEN(@.fromYear)<1 OR B.FISCAL_YEAR >= @.fromYear)
> AND (LEN(@.toYear)<1 OR B.FISCAL_YEAR <= @.toYear)
> AND (LEN(@.year)<1 OR LEN(@.year)>=1)
> AND (LEN(@.organization)<1 OR LEN(@.organization)>=1)
> From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
>

Data Output Tab

Does anyone know what the data output tabs are used for on all the
Properties dialogs?When you're outputting to xml, you can define xml options there.
"mjohnson1122@.hotmail.com" wrote:
> Does anyone know what the data output tabs are used for on all the
> Properties dialogs?
>

Data on report different than what is in result set from stored pr

I am running my report in the IDE. When I execute the stored proc from the
data tab, the result set has the correct data. When I Preview the report,
the data on the report is different from what is in the result set. I have
made sure I used the same data in the report parameters that I used in the
query parameters. What causes this problem?
Thanks.GO to the properties of the field... It is probably set as a "general',
change the property to date, and set the format... that should fix your
problem
--
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
"Deb" <Deb@.discussions.microsoft.com> wrote in message
news:BACEB8DD-D958-479D-A5B8-F2FDA24C085E@.microsoft.com...
>I am running my report in the IDE. When I execute the stored proc from the
> data tab, the result set has the correct data. When I Preview the report,
> the data on the report is different from what is in the result set. I
> have
> made sure I used the same data in the report parameters that I used in the
> query parameters. What causes this problem?
> Thanks.|||It is not the date but the actual data. For example, the stored proc returns
a number that is 42984438 but when on the report I see 45711664. I have
deleted all .data files and I have changed the RSReportDesigner.config file
to have
<Add Key="CacheDataForPreview" Value="false" />. Still I am getting wrong
data from somewhere. I do no manipulation of the data in the report. All my
fields are just dragged and dropped into a table. Where is it getting the
data from?
"Wayne Snyder" wrote:
> GO to the properties of the field... It is probably set as a "general',
> change the property to date, and set the format... that should fix your
> problem
> --
> 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
> "Deb" <Deb@.discussions.microsoft.com> wrote in message
> news:BACEB8DD-D958-479D-A5B8-F2FDA24C085E@.microsoft.com...
> >I am running my report in the IDE. When I execute the stored proc from the
> > data tab, the result set has the correct data. When I Preview the report,
> > the data on the report is different from what is in the result set. I
> > have
> > made sure I used the same data in the report parameters that I used in the
> > query parameters. What causes this problem?
> >
> > Thanks.
>
>|||I am experiencing the same problem. I have posted a question titled "Columns
Not Consistent With Query". I am also using a SP with no manipulation of the
data. I found that the "incorrect" data in the column is always the same
value of the column in the row right above it.
If anyone responds to your question with a solution, would you please let me
know at danhaynes42@.hotmail.com and I will do the same for you. You could
send me your email to make this easier. Thanks. Dan Haynes.
"Deb" wrote:
> It is not the date but the actual data. For example, the stored proc returns
> a number that is 42984438 but when on the report I see 45711664. I have
> deleted all .data files and I have changed the RSReportDesigner.config file
> to have
> <Add Key="CacheDataForPreview" Value="false" />. Still I am getting wrong
> data from somewhere. I do no manipulation of the data in the report. All my
> fields are just dragged and dropped into a table. Where is it getting the
> data from?
> "Wayne Snyder" wrote:
> > GO to the properties of the field... It is probably set as a "general',
> > change the property to date, and set the format... that should fix your
> > problem
> >
> > --
> > 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
> >
> > "Deb" <Deb@.discussions.microsoft.com> wrote in message
> > news:BACEB8DD-D958-479D-A5B8-F2FDA24C085E@.microsoft.com...
> > >I am running my report in the IDE. When I execute the stored proc from the
> > > data tab, the result set has the correct data. When I Preview the report,
> > > the data on the report is different from what is in the result set. I
> > > have
> > > made sure I used the same data in the report parameters that I used in the
> > > query parameters. What causes this problem?
> > >
> > > Thanks.
> >
> >
> >|||I am having the same problem with the pile of crap (Reporting
Services). Any one figured this gem out ?|||I am having the same problem with the pile of crap (Reporting
Services). Any one figured this gem out ?|||I have got to tell you that this is a little embarassing. My problem ended up
being that somehow the aggregate "first()" was in effect on the data in the
column that appeared to be wrong. This is why the result directly from the SP
was always correct and the result in the report was only wrong when a sort
order was selected that had a different value in the first row. From Deb's
description, this could also fit her description of the problem, but she is
probably more observant than I.
I will say that I have no idea how the aggregate "first()" got on the
column. There were 9 detail columns in the report and this is the only column
that had this done.
Dave, I understand your frustration because I was feeling it when it looked
like I had a really bizarre internal problem and no one was responding to my
postings. Not to sound like a cheerleader, but I have been extreemly
impressed with V1 of RS, even if it is a real bitch to get to run on a domain
controller.
I doubt this was any help, but I sincerely hope so.
"Dave" wrote:
> I am having the same problem with the pile of crap (Reporting
> Services). Any one figured this gem out ?
>

Data not showing

Hi,
I am new to crystal reporting. I created a report using 2 tables. when i go to preview tab data is not showing up. and in the preview tab on the left hand side there is no drilldown button(+ sign) next to my report name. Please help me. Thanks in advanceHi, Did you make proper joins between two tables?

Madhivanan|||Thank you you are right

Data not displayed in Preview Tab, but is displayed in Data Tab

This has happened on more than one occasion. I have defined a stored
procedure type datasource with 12 parameters. When I execute the SP in the
Data tab, the resulting rows are correctly displayed. When I use the same
parameters when previewing the report, no data is displayed. I have found
that empty string parameters need to be <blank> on the data tab, but need to
be '' on the preview tab, so I know about this little gotcha.
Has anyone else had this problem and found a solution. I have check the rdl
file to make sure that the datasource and fields are correct. I have a table
and a pie chart on the report and both are empty when I preview it.
===================== Thanks!
Keith
p.s. Get off the couch and exercise!I have answered my own question.
This might come in handy for a few people. If one of your parameters is a
string and the SP allows an empty string, then make sure to check the
Parameter definition box "Allow Blank Value". otherwise if you try to send
it a blank or '' (empty string), then SQL profiler shows the parameter as
N'''. This solved my problem and I am now getting data.
=====================Thanks!
Keith
p.s. Get off the couch and exercise!
"Keith" wrote:
> This has happened on more than one occasion. I have defined a stored
> procedure type datasource with 12 parameters. When I execute the SP in the
> Data tab, the resulting rows are correctly displayed. When I use the same
> parameters when previewing the report, no data is displayed. I have found
> that empty string parameters need to be <blank> on the data tab, but need to
> be '' on the preview tab, so I know about this little gotcha.
> Has anyone else had this problem and found a solution. I have check the rdl
> file to make sure that the datasource and fields are correct. I have a table
> and a pie chart on the report and both are empty when I preview it.
>
> =====================> Thanks!
> Keith
> p.s. Get off the couch and exercise!