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 parameter - First day of last month/last day of month

can somebody pls. help me with the following report parameter defaults?
for a monthly report I need two dates as parameter defaults:
The first day of the last month
and
the last day of last month
If I run a report on - let's say March 1st, I can get the last day of the
last month with
=Today.AddDays(-1)
and the first day of the last month with
=Today.AddMonths(-1)
However, I need these paramers to work every day. For example, I want to be
able to run the report on March 3rd or March 15th, and the report should also
have February 1st and February 28th as start and end dates.
How can I do this?
Thank you for helping,
MartinHi Martin,
This will work everyday. start and end date of last month.
start date - str(year(today)) + "/" + str(month(today)-1) + "/1"
End Date - dateadd("d",-1, (str(year(today)) + "/" + str(month(today)) +
"/1"))
Now the date format ie dd/mm/yy or mm/dd/yy etc... I hope you can change it.
Amarnath
"Martin" wrote:
> can somebody pls. help me with the following report parameter defaults?
> for a monthly report I need two dates as parameter defaults:
> The first day of the last month
> and
> the last day of last month
> If I run a report on - let's say March 1st, I can get the last day of the
> last month with
> =Today.AddDays(-1)
> and the first day of the last month with
> =Today.AddMonths(-1)
> However, I need these paramers to work every day. For example, I want to be
> able to run the report on March 3rd or March 15th, and the report should also
> have February 1st and February 28th as start and end dates.
> How can I do this?
> Thank you for helping,
> Martin
>

Report parameter

I have a stored procedure that works fine in reporting services. It grabs the total of Yes's and No's by dates . But then i went ahead and added 2 more parameters to the proc, and now the totals are all wrong. I dont understand how that can mess everything up. Here is the previous stored proc, that gives the correct sum. I just want to know what im doing wrong, that the totals are completely off now. Did i set up the parameter wrong in reporting services. I have the 3 parameters list in the report parameter section, and even have them cascading off of each other. That seems to work fine. but for the first matrix in my report, but the second matrix with this stored proc, is way off. Please help!!.

Code Snippet

ALTER PROCEDURE [dbo].[Testing_Questions_ALL_YESOrNO]

@.Question char(80)

AS

BEGIN

SELECT

Qry_Questions.Question

, Qry_Questions.Date

, Qry_Questions.response

, B.Total

FROM Qry_Questions

INNER JOIN Qry_Sales_Group

ON dbo.Qry_Questions.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

INNER JOIN

( Select COUNT(qq.response)as Total, Question, Date, response

FROM Qry_Questions qq

Where qq.response in ('Yes','No')

GROUP by qq.[Question] , qq.Date,qq.response ) B

ON Qry_Questions.Date = B.Date AND

Qry_Questions.Question =B.Question and

Qry_Questions.response=B.response

WHERE Qry_Questions.[Response Type]='YesNo' and Qry_Questions.Question=@.Question

GROUP BY Qry_Questions.question,Qry_Questions.Date,Qry_questions.Response,B.Total

ORDER BY Qry_Questions.Question, Qry_Questions.Date

END

SET NOCOUNT OFF

Here is the edited version which only has two new parameters added to the proc. The edits are highlighted.

Code Snippet

ALTER PROCEDURE [dbo].[Testing_Questions_ALL_YESOrNO_Totals]

(@.Region_Key int=null,@.QuestionCode char(5),@.Question char(80))

AS

BEGIN

SELECT

Qry_Questions.Question

, Qry_Questions.Date

, Qry_Questions.response

, B.Total

FROM Qry_Questions

INNER JOIN Qry_Sales_Group

ON dbo.Qry_Questions.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

INNER JOIN

( Select COUNT(qq.response)as Total, Question, Date, response

FROM Qry_Questions qq

Where qq.response in ('Yes','No')

GROUP by qq.[Question] , qq.Date,qq.response ) B

ON Qry_Questions.Date = B.Date AND

Qry_Questions.Question =B.Question and

Qry_Questions.response=B.response

WHERE Qry_Questions.[Response Type]='YesNo'

AND REGION_KEY=@.Region_Key

AND LEFT(Qry_Questions.[Question Code],2)IN (@.QuestionCode)

AND Qry_Questions.Question=@.Question

GROUP BY Qry_Questions.question,Qry_Questions.Date,Qry_questions.Response,B.Total

ORDER BY Qry_Questions.Question, Qry_Questions.Date

END

SET NOCOUNT OFF

Try this:

AND (@.Region_Key is null or REGION_KEY=@.Region_Key)

BobP

Report Manager-Tab Order

I have a mystery. I have a series of reports. Each report has the same
basic parameters all of which except for dates have default values. The
first two are From Date and Through Date. I have couple dozen of these
reports. When I select a report via Report Manager I click my mouse in the
first parameter box. I type date desired and hit tab key. Cursor goes to
Through Date. I type that in and then pressing the enter key activates "view
report". On a small handful of these reports the behavior is different. I
select these from repot manager and put the cursor inthe first parameter box
just like before. Enter the value and then press tab. The cursor does move
to the from date box BUT it doesn't stay there. It immediately leaves.
After this pressing enter does nothing. Using mouse I can put cursor back in
From Date box, but pressing enter still does nothing. I have to actually
click on view report to get it to generate. I'm using Internet Explorer 7.0
and SQL RS 2005. Another strange thing is that these reports are just clones
of each other with some tweaking of the query that returns the data sets.
The same layouts, xml (except query), and properties on all of them. Anyone
have any ideas? Thanks.
--
Thanks, JimOn Aug 9, 2:14 pm, Jim B <J...@.lightning.com> wrote:
> I have a mystery. I have a series of reports. Each report has the same
> basic parameters all of which except for dates have default values. The
> first two are From Date and Through Date. I have couple dozen of these
> reports. When I select a report via Report Manager I click my mouse in the
> first parameter box. I type date desired and hit tab key. Cursor goes to
> Through Date. I type that in and then pressing the enter key activates "view
> report". On a small handful of these reports the behavior is different. I
> select these from repot manager and put the cursor inthe first parameter box
> just like before. Enter the value and then press tab. The cursor does move
> to the from date box BUT it doesn't stay there. It immediately leaves.
> After this pressing enter does nothing. Using mouse I can put cursor back in
> From Date box, but pressing enter still does nothing. I have to actually
> click on view report to get it to generate. I'm using Internet Explorer 7.0
> and SQL RS 2005. Another strange thing is that these reports are just clones
> of each other with some tweaking of the query that returns the data sets.
> The same layouts, xml (except query), and properties on all of them. Anyone
> have any ideas? Thanks.
> --
> Thanks, Jim
>From what I can understand, some of the reports seem to have defaults
for the second parameter and some don't. You may want to check the
default properties of the report parameters to make sure that they are
consistent. Also, you may want to make sure that you have the latest
Microsoft updates (for IE7, SQL Server 2005 SP2, etc). If this does
not resolve the problem, you may want to re-upload the reports. Hope
this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Aug 9, 2:14 pm, Jim B <J...@.lightning.com> wrote:
> I have a mystery. I have a series of reports. Each report has the same
> basic parameters all of which except for dates have default values. The
> first two are From Date and Through Date. I have couple dozen of these
> reports. When I select a report via Report Manager I click my mouse in the
> first parameter box. I type date desired and hit tab key. Cursor goes to
> Through Date. I type that in and then pressing the enter key activates "view
> report". On a small handful of these reports the behavior is different. I
> select these from repot manager and put the cursor inthe first parameter box
> just like before. Enter the value and then press tab. The cursor does move
> to the from date box BUT it doesn't stay there. It immediately leaves.
> After this pressing enter does nothing. Using mouse I can put cursor back in
> From Date box, but pressing enter still does nothing. I have to actually
> click on view report to get it to generate. I'm using Internet Explorer 7.0
> and SQL RS 2005. Another strange thing is that these reports are just clones
> of each other with some tweaking of the query that returns the data sets.
> The same layouts, xml (except query), and properties on all of them. Anyone
> have any ideas? Thanks.
> --
> Thanks, Jim
>From what I can understand, some of the reports seem to have defaults
for the second parameter and some don't. You may want to check the
default properties of the report parameters to make sure that they are
consistent. Also, you may want to make sure that you have the latest
Microsoft updates (for IE7, SQL Server 2005 SP2, etc). If this does
not resolve the problem, you may want to re-upload the reports. Hope
this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Report Heading with dates

I'm trying to create a heading for my report.

I want

sales figures between 09/11/2006 to 09/04/2007.

The date comes from parameters

What I have done is created 4 text boxes

textbox 1 = sales figures between

textbox 2 = =datepart("D", Parameters!param_datef.Value) & "/" & datepart("M", Parameters!param_datef.Value) & "/" & datepart("YYYY", Parameters!param_datef.Value)

textbox 3 = To

textbox 4 = =datepart("D", Parameters!param_datet.Value) & "/" & datepart("M", Parameters!param_datet.Value) & "/" & datepart("YYYY", Parameters!param_datet.Value)

Ok the problem is the text boxes when previewed are all over the place.

In crystal reports you can add a formula into a text box after text

Is this sort of thing possible in Reporting services

Thanks for all your help

You should stick this all in one textbox, not four:

= "Sales Figures between " & datepart("D", Parameters!param_datef.Value) & "/" & datepart("M", Parameters!param_datef.Value) & "/" & datepart("YYYY", Parameters!param_datef.Value) & " to" & =datepart("D", Parameters!param_datet.Value) & "/" & datepart("M", Parameters!param_datet.Value) & "/" & datepart("YYYY", Parameters!param_datet.Value)

It also is probably not necessary for you to "build up" the date - Try the Format() function: Format(Parameters!Param.Value, "FormatMaskGoesHere")

|||

Thanks Russell for you help

For the date how would you use format to convert the date format from yyyy-mm-dd to dd/mm/yyyy

Thanks

|||Format(date, "dd/MM/yyyy")

Report from SSAS with two different date column

Hi all,

I created a SSAS cube with the dimension "date" (including dates from 2006 to 2007) and "service numbers" (including service number where customer can call in) and a measure "number of calls".

The I created a report with the MDX builder showing a date as column (i.e. 02/01/2007), service numbers as rows and number of calls in the middle. Easy :-)

Now I will add a column with a date one year before (i.e. 02/01/2006) and a column showing the difference between the two dates as a percentage value.

How can I create this in the graphical builder?

Or do I have to create the MDX query manualy?

Thanks in advance!

some ASCII art:

2/1/07 2/1/06 diff.

-

service number 1 | 345 | 690 | 50%

service number 1 | 100 | 50 | 200%

...

Hi,

Do you have the data for the previous year's date in the query? For this scenario, it is must to create a calculated measure either in the query builder or in the cube which returns the value for the previous year's same date. You can use the LAG function to get this.

After getting both measures, you can directly give the percentage expression in the column "diff". No need for creating an MDX query for the percentage.