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 showing a single record

I have a DataSet which is defined as "SELECT * FROM Table". There are MANY
records in Table. However, the report only shows a single record/page. I
placed the fields on the report in the "Body". What am I missing here?Nevermind... Got it. It's the List item.
"Dan" wrote:
> I have a DataSet which is defined as "SELECT * FROM Table". There are MANY
> records in Table. However, the report only shows a single record/page. I
> placed the fields on the report in the "Body". What am I missing here?|||When you place the fields on the blank layout surface you are not using any
of the controls that know about multiple rows. You need to place either a
table control or a list control and then drag and drop onto them.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:7F61EA6A-DB25-43FC-9117-84A2E7E037AA@.microsoft.com...
>I have a DataSet which is defined as "SELECT * FROM Table". There are MANY
> records in Table. However, the report only shows a single record/page. I
> placed the fields on the report in the "Body". What am I missing here?sql

Report Model and Security Filter

I have a single database which will be shared by many customers.
So the idea was to design a "Report Model" and use Security Filters to make
sure that customers only see data belonging to them. Each tabel has a field
called ClientID.
How can one design a single report model which asures that each customer
only sees those records, belonging to him.
br, MichaelMaybe I shoul be more specific:
Is the concept of security filters the right way to go?
Is it better to design a special Report Model for each customer, where the
entities are based on queries?
thx, for any help
"Michael Schwabl" wrote:
> I have a single database which will be shared by many customers.
> So the idea was to design a "Report Model" and use Security Filters to make
> sure that customers only see data belonging to them. Each tabel has a field
> called ClientID.
> How can one design a single report model which asures that each customer
> only sees those records, belonging to him.
> br, Michael
>

Report Model

Can you have more than one data source view in one report model? I have data from multiple sql databases that I want to combine into a single report model for the users.

Currently only one data source per model is supported.

It may be possible however to use Data Source View editor to manually add named queries which reference other databases.

|||

Lev is right. If you want to create a model over a set of databases you need to do some manual work with your DSV.

In model designer start with generating DSV for primary database (the one containing more data). Then manually add named queries to the DSV each selecting data from another database (select * from myotherdb.dbo.tableN). For each named query manually add info about which columns should be treated as PK and which are FKs. This is important for semantic model generation that would omit tables without PKs and will note create roles between entities without FKs.
After you finish with your DSV - generate model in model designer.