Report Parameters - Decimal vs. Float

My dataset returns data from a range of effective dates for a single
month. In the database, these dates are unfortunately not of type
datetime, but are decimal and are in the YYYYMMDD format.
I'm having trouble getting the report to execute because at the query
level, the datatype has to be decimal. I need to parameterize these
dates, but there is no decimal datatype for parameters.
I have a dataset that generates the necessary dates based on the type
of report the user wants (60/90/120 day) (if they want a 60-day report,
the effective date = 2 months from the first day of the current month,
etc).
SELECT DISTINCT
CONVERT(datetime,LEFT(CAST(effdte AS varchar), 4) + '/' +
SUBSTRING(CAST(effdte AS varchar), 5, 2) + '/' + '01') +
@.period_additive AS effdte_low,
CONVERT(datetime,LEFT(CAST(effdte AS varchar), 4) + '/' +
SUBSTRING(CAST(effdte AS varchar), 5, 2) + '/' + '31') +
@.period_additive AS effdte_high
FROM dbo.t_policy
WHERE YEAR(GETDATE()) - 1 = LEFT(CAST(effdte AS varchar), 4) AND
MONTH(GETDATE()) = SUBSTRING(CAST(effdte AS varchar), 5, 2) AND
LEFT(policy, 3) LIKE @.dept_cd AND LEFT(policy, 3) <> 'LPA'
What would the appropriate code be to do this within the Report
Parameters dialog instead? It seems like I'd have a better chance of
success if it were done that way.
Thanks!
MikeNo matter what, I get this error at runtime (Preview mode):
--
Processing Errors
--
An error has occurred during report processing.
Cannot read the next data row for the data set ds_main.
Arithmetic overflow error converting expression to data type datetime.
--
OK
--|||I think I fixed it...
Even though the database type is decimal, when searching by that range,
I have to put single quotes around it. Therefore, I converted the date
type back to a varchar and changed the parameter data types to string.
Now the report runs.
But there should be a more straightforward way to do this within the
Report Parameters dialog box, shouldn't there?sql

Report only generates 1 page output

I'm fairly new to Reporting Services, so I'm sure this is a dumb question. I have a stored proc that returns 38 rows that I am using in a report. The report is fairly simple, with no page headers or footers. I used the Report Wizard to create the report, but ended up deleting all the generated fields and started with a blank report. Now the report only generates 1 page of output. Any ideas?

Thanks,
MikeDid you get the right data with the report created by the Wizard? After you deleted all the generated fields, what did you replace them with? Does your report contain a repeating data region such as a Table?

Thanks,
Albert

Report only generates 1 page output

I'm fairly new to Reporting Services, so I'm sure this is a dumb question. I have a stored proc that returns 38 rows that I am using in a report. The report is fairly simple, with no page headers or footers. I used the Report Wizard to create the report, but ended up deleting all the generated fields and started with a blank report. Now the report only generates 1 page of output. Any ideas?

Thanks,
MikeDid you get the right data with the report created by the Wizard? After you deleted all the generated fields, what did you replace them with? Does your report contain a repeating data region such as a Table?

Thanks,
Albert

Report model GetUserID() function

Guys,

What is "GetUserID()" function returns when the report server, report builder and everything is using windows authentication?

http://msdn2.microsoft.com/en-us/library/ms156292.aspx

Enkh.

Just use USERID

|||

"Just use USERID" What do you mean? USERID? Is that a global variable, windows authentication username?

Enkh.

|||

More details:

USER!USERID

|||I don't really understand it. Can you please just describe it for me or someone? Is it windows authentication username or some kind of ID?sql

Report is blank, but QA returns results

I've tested a query in Query Analyzer and it returns the desired results.
However, the same query in a SQL report finishes without errors but the
report is completely blank. Similarly formatted reports work fine. Here's the
query:
SELECT CRMAF_Task.modifiedonutc, CRMAF_Task.modifiedbyname,
CRMAF_Task.owneridname, CRMAF_Task.subject, CRMAF_Task.statecodename,
CRMAF_Task.description, CRMAF_Task.regardingobjectid,
CRMAF_Notes.subject AS NotesSubj, CRMAF_Notes.notetext AS NoteText,
CRMAF_Notes.createdonutc
FROM FilteredTask CRMAF_Task LEFT JOIN
FilteredAnnotation CRMAF_Notes ON
CRMAF_Task.activityid = CRMAF_Notes.objectid
WHERE (CRMAF_Task.subject LIKE '%reb%') AND
(CRMAF_Task.regardingobjectid = 'D5C79CEC-D3AF-DB11-86DF-0003FFDAF900') AND
(CRMAF_Task.statecode = 1)
Anyone have any ideas?
--
Matt Wittemann, CRM MVP
http://icu-mscrm.blogspot.comOn Feb 19, 6:12 pm, MattNC <Mat...@.discussions.microsoft.com> wrote:
> I've tested a query in Query Analyzer and it returns the desired results.
> However, the same query in a SQL report finishes without errors but the
> report is completely blank. Similarly formatted reports work fine. Here's the
> query:
> SELECT CRMAF_Task.modifiedonutc, CRMAF_Task.modifiedbyname,
> CRMAF_Task.owneridname, CRMAF_Task.subject, CRMAF_Task.statecodename,
> CRMAF_Task.description, CRMAF_Task.regardingobjectid,
> CRMAF_Notes.subject AS NotesSubj, CRMAF_Notes.notetext AS NoteText,
> CRMAF_Notes.createdonutc
> FROM FilteredTask CRMAF_Task LEFT JOIN
> FilteredAnnotation CRMAF_Notes ON
> CRMAF_Task.activityid = CRMAF_Notes.objectid
> WHERE (CRMAF_Task.subject LIKE '%reb%') AND
> (CRMAF_Task.regardingobjectid = 'D5C79CEC-D3AF-DB11-86DF-0003FFDAF900') AND
> (CRMAF_Task.statecode = 1)
> Anyone have any ideas?
> --
> Matt Wittemann, CRM MVPhttp://icu-mscrm.blogspot.com
That's a tough one. Have you tried refreshing the dataset and making
sure that you are not filtering out anything (if you're grouping in
the report)?
Enrique Martinez
Sr. SQL Server Developer|||In two things it should be empty.
1. If no error, but empty it means it is not satisfying the where
conditions. So just check the where conditions may be spelling etc...
2. you said the same query works in QA, then just check and re-check where
your datasource is connecting, is it pointing to the same database/table
where your QA also connects ' just check this.. I think that must be wrong...
Amarnath
"MattNC" wrote:
> I've tested a query in Query Analyzer and it returns the desired results.
> However, the same query in a SQL report finishes without errors but the
> report is completely blank. Similarly formatted reports work fine. Here's the
> query:
> SELECT CRMAF_Task.modifiedonutc, CRMAF_Task.modifiedbyname,
> CRMAF_Task.owneridname, CRMAF_Task.subject, CRMAF_Task.statecodename,
> CRMAF_Task.description, CRMAF_Task.regardingobjectid,
> CRMAF_Notes.subject AS NotesSubj, CRMAF_Notes.notetext AS NoteText,
> CRMAF_Notes.createdonutc
> FROM FilteredTask CRMAF_Task LEFT JOIN
> FilteredAnnotation CRMAF_Notes ON
> CRMAF_Task.activityid = CRMAF_Notes.objectid
> WHERE (CRMAF_Task.subject LIKE '%reb%') AND
> (CRMAF_Task.regardingobjectid = 'D5C79CEC-D3AF-DB11-86DF-0003FFDAF900') AND
> (CRMAF_Task.statecode = 1)
> Anyone have any ideas?
> --
> Matt Wittemann, CRM MVP
> http://icu-mscrm.blogspot.com|||Who are you running the query as in QA?
Since you are using filtered views, if you are not running as the same
user running the reports, it may be an issue of security.
On Feb 19, 7:12 pm, MattNC <Mat...@.discussions.microsoft.com> wrote:
> I've tested a query in Query Analyzer and it returns the desired results.
> However, the same query in a SQL report finishes without errors but the
> report is completely blank. Similarly formatted reports work fine. Here's the
> query:
> SELECT CRMAF_Task.modifiedonutc, CRMAF_Task.modifiedbyname,
> CRMAF_Task.owneridname, CRMAF_Task.subject, CRMAF_Task.statecodename,
> CRMAF_Task.description, CRMAF_Task.regardingobjectid,
> CRMAF_Notes.subject AS NotesSubj, CRMAF_Notes.notetext AS NoteText,
> CRMAF_Notes.createdonutc
> FROM FilteredTask CRMAF_Task LEFT JOIN
> FilteredAnnotation CRMAF_Notes ON
> CRMAF_Task.activityid = CRMAF_Notes.objectid
> WHERE (CRMAF_Task.subject LIKE '%reb%') AND
> (CRMAF_Task.regardingobjectid = 'D5C79CEC-D3AF-DB11-86DF-0003FFDAF900') AND
> (CRMAF_Task.statecode = 1)
> Anyone have any ideas?
> --
> Matt Wittemann, CRM MVPhttp://icu-mscrm.blogspot.com

Report generation is consuming 100% of CPU client side.

Hi,

I have a problem while rendering a report which returns around 5000 rows. At first the server is busy to process the request, when the server is done the rendering client side takes 100% of CPU and never displays the result ("IE is not responding"). It seems that the ReportViewer has trouble to handle the server response.

Do you have any idea why ?

Sbastien.

It appeared that the problem is a paging issue. The report contains groups and by default the top level is collapsed, when we try to expand a level that contains many rows thoses rows are all displayed on a single page. That's why IE is consuming 100% of the CPU to display all the rows.

How can I change that the paging is working for the sub groups displayed ?

|||

I found the answer in BOL:

"The HTML and Excel rendering extensions are not oriented to physical pages. Furthermore, the HTML rendering extension is interactive, meaning that user actions in a report can trigger additional processing that causes a report to expand horizontally or vertically to accommodate additional content. You cannot precisely control how reports viewed through these rendering extensions will paginate if the report contains interactive features."

Report generation is consuming 100% of CPU client side.

Hi,

I have a problem while rendering a report which returns around 5000 rows. At first the server is busy to process the request, when the server is done the rendering client side takes 100% of CPU and never displays the result ("IE is not responding"). It seems that the ReportViewer has trouble to handle the server response.

Do you have any idea why ?

Sbastien.

It appeared that the problem is a paging issue. The report contains groups and by default the top level is collapsed, when we try to expand a level that contains many rows thoses rows are all displayed on a single page. That's why IE is consuming 100% of the CPU to display all the rows.

How can I change that the paging is working for the sub groups displayed ?

|||

I found the answer in BOL:

"The HTML and Excel rendering extensions are not oriented to physical pages. Furthermore, the HTML rendering extension is interactive, meaning that user actions in a report can trigger additional processing that causes a report to expand horizontally or vertically to accommodate additional content. You cannot precisely control how reports viewed through these rendering extensions will paginate if the report contains interactive features."