Last period as initial value but still have period as parameter?

Hi all,

I'm using an olap cube in a report (dash board).
The last "actual" period should be viewed when the user opens the report. I'm using the MDX tail function for that.
Once the report is opened the users want to select another period. Then I want to put period as a parameter.
But that is not possible since the parameter check box is no longer available when using a MDX filter expression.

Is there any solution to this problem?

Any help will be appreciated.

If you know MDX, then don't use the query designer and just write your own queries. You'll need to have 2 maybe 3 datasets:

Main query dataset

Last Day of the Month

Hi,
is there a way to find the last day of the month ?
the input parameter is just the month. year will be the current year, start
date will always be 1st of the month. I need to find the last day of the
month
Thankshttp://www.aspfaq.com/show.asp?id=2444
Andrew J. Kelly SQL MVP
"Vishal" <vrajput77@.hotmail.com> wrote in message
news:ems8Wj$BGHA.3812@.TK2MSFTNGP15.phx.gbl...
> Hi,
> is there a way to find the last day of the month ?
> the input parameter is just the month. year will be the current year,
> start date will always be 1st of the month. I need to find the last day of
> the month
> Thanks
>|||Maybe not as elegate as other solutions, but
SELECT DATEADD(dd, -1, CAST(YEAR(DATEADD(mm, 1, GETDATE())) AS char(4)) +
RIGHT('0' + CAST(MONTH(DATEADD(mm, 1, GETDATE())) AS varchar(2)), 2) + '01')
will get you the last day of the current month, in datetime format. If you
want only the numeric portion for the day, then
SELECT DAY(DATEADD(dd, -1, CAST(YEAR(DATEADD(mm, 1, GETDATE())) AS char(4))
+ RIGHT('0' + CAST(MONTH(DATEADD(mm, 1, GETDATE())) AS varchar(2)), 2) +
'01'))
DISCLAIMER: This will not work 7,995 years from now. ;-)
"Vishal" wrote:

> Hi,
> is there a way to find the last day of the month ?
> the input parameter is just the month. year will be the current year, star
t
> date will always be 1st of the month. I need to find the last day of the
> month
> Thanks
>
>|||Speaking of more elegant solutions:
SELECT DATEADD(dd, -1, LEFT(CONVERT(char(8), DATEADD(mm, 1, GETDATE()),
112),6) + '01')
for the last day of the current month in datetime format
SELECT DAY(DATEADD(dd, -1, LEFT(CONVERT(char(8), DATEADD(mm, 1, GETDATE()),
112),6) + '01'))
for just the numeric date portion.
"Mark Williams" wrote:
> Maybe not as elegate as other solutions, but
> SELECT DATEADD(dd, -1, CAST(YEAR(DATEADD(mm, 1, GETDATE())) AS char(4)) +
> RIGHT('0' + CAST(MONTH(DATEADD(mm, 1, GETDATE())) AS varchar(2)), 2) + '01
')
> will get you the last day of the current month, in datetime format. If you
> want only the numeric portion for the day, then
> SELECT DAY(DATEADD(dd, -1, CAST(YEAR(DATEADD(mm, 1, GETDATE())) AS char(4)
)
> + RIGHT('0' + CAST(MONTH(DATEADD(mm, 1, GETDATE())) AS varchar(2)), 2) +
> '01'))
> DISCLAIMER: This will not work 7,995 years from now. ;-)
> "Vishal" wrote:
>|||Speaking of more elegant solutions:
SELECT DATEADD(dd, -1, LEFT(CONVERT(char(8), DATEADD(mm, 1, GETDATE()),
112),6) + '01')
for the last day of the current month in datetime format
SELECT DAY(DATEADD(dd, -1, LEFT(CONVERT(char(8), DATEADD(mm, 1, GETDATE()),
112),6) + '01'))
for just the numeric day of the month.
"Mark Williams" wrote:
> Maybe not as elegate as other solutions, but
> SELECT DATEADD(dd, -1, CAST(YEAR(DATEADD(mm, 1, GETDATE())) AS char(4)) +
> RIGHT('0' + CAST(MONTH(DATEADD(mm, 1, GETDATE())) AS varchar(2)), 2) + '01
')
> will get you the last day of the current month, in datetime format. If you
> want only the numeric portion for the day, then
> SELECT DAY(DATEADD(dd, -1, CAST(YEAR(DATEADD(mm, 1, GETDATE())) AS char(4)
)
> + RIGHT('0' + CAST(MONTH(DATEADD(mm, 1, GETDATE())) AS varchar(2)), 2) +
> '01'))
> DISCLAIMER: This will not work 7,995 years from now. ;-)
> "Vishal" wrote:
>|||OK, I promise this is my last post in this thread. I finally got my head on
straight
SELECT DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE())+1, 0))
SELECT DAY(DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE())+1, 0)))
"Vishal" wrote:

> Hi,
> is there a way to find the last day of the month ?
> the input parameter is just the month. year will be the current year, star
t
> date will always be 1st of the month. I need to find the last day of the
> month
> Thanks
>
>|||Hi,
This is what I want to do:
User enters parameters StoreNo. Month(11), Year(2005), Open Months(8).
Store Table :
Store No. INT (IDENTITY)
OpenDate (DATETIME)
When the user calls a S Proc with the above parameters, I want to check
whether the Store was opened for 8 Months from the OpenDate on the store, if
the OpenDate is 22/02/2004 then the number of months should be calculated
from the next month, not including the month the store opened.
I would like to write this in a Function, please help.
Thanks
"Vishal" <vrajput77@.hotmail.com> wrote in message
news:ems8Wj$BGHA.3812@.TK2MSFTNGP15.phx.gbl...
> Hi,
> is there a way to find the last day of the month ?
> the input parameter is just the month. year will be the current year,
> start date will always be 1st of the month. I need to find the last day of
> the month
> Thanks
>

Large String Parameters Causing Performance Issues

Hi All,
Bit of strange one this. I have a key field in one of my databases that is a
Char(18). I use this as a parameter field in one of my reports.
When I run the report and specify the key field parameter manually the
report runs instantly. If I attempt to set this as a parameter variable and
enter the criteria in the parameter box, the report takes over a minute to
run. The 2 statements are as follows.
1) select sum(sales_value) as sales from production.dbo.epos_transactions
where transaction_ref = '085505010500074995'
When run as above, the report runs instantly.
2) select sum(sales_value) as sales from production.dbo.epos_transactions
where transaction_ref = @.TREF
When I enter the parameter and run the report it takes over a minute to run.
Running the query through query analyser causes no issues on any of the
servers involved and the response time on all servers is instant.
I'd be grateful if anyone can shed any light on this.
Many thanks for any help.
--
Nick Colebourn
DBA
United Coop LtdHi Nick,
Could you supply a generic repro for this issue. An RDL for Northwind or a
simple DB. I would love to take a look at it.
| Thread-Topic: Large String Parameters Causing Performance Issues
| thread-index: AcUC1GzdZAw/tJ+RRciPnzP3TypLNA==| X-WBNR-Posting-Host: 213.146.135.115
| From: "=?Utf-8?B?TmljayBDb2xlYm91cm4=?="
<NickColebourn@.discussions.microsoft.com>
| Subject: Large String Parameters Causing Performance Issues
| Date: Tue, 25 Jan 2005 03:53:03 -0800
| Lines: 29
| Message-ID: <EFE8F146-E9CC-49C8-A5B8-4E29E3F57EC2@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:40737
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hi All,
| Bit of strange one this. I have a key field in one of my databases that
is a
| Char(18). I use this as a parameter field in one of my reports.
| When I run the report and specify the key field parameter manually the
| report runs instantly. If I attempt to set this as a parameter variable
and
| enter the criteria in the parameter box, the report takes over a minute
to
| run. The 2 statements are as follows.
|
| 1) select sum(sales_value) as sales from production.dbo.epos_transactions
| where transaction_ref = '085505010500074995'
|
| When run as above, the report runs instantly.
|
| 2) select sum(sales_value) as sales from production.dbo.epos_transactions
| where transaction_ref = @.TREF
|
| When I enter the parameter and run the report it takes over a minute to
run.
|
| Running the query through query analyser causes no issues on any of the
| servers involved and the response time on all servers is instant.
|
| I'd be grateful if anyone can shed any light on this.
|
| Many thanks for any help.
|
| --
| Nick Colebourn
| DBA
| United Coop Ltd
||||Hi Brad,
Many thanks for your reply. I'll see if I can get it to do the same
with a northwind query and post back asap. For your info, I've created an
in-line table valued function containing the sql with the parameter and
called this as the dataset instead of the sql text itself. This works ok
either with static criteria or using a parameter passed across to the
function. The performance issue only seems to happen when you pass the
parameter value directly across to the sql text.
Many thanks
Nick
""Brad Syputa - MS"" wrote:
> Hi Nick,
> Could you supply a generic repro for this issue. An RDL for Northwind or a
> simple DB. I would love to take a look at it.
>
> --
> | Thread-Topic: Large String Parameters Causing Performance Issues
> | thread-index: AcUC1GzdZAw/tJ+RRciPnzP3TypLNA==> | X-WBNR-Posting-Host: 213.146.135.115
> | From: "=?Utf-8?B?TmljayBDb2xlYm91cm4=?="
> <NickColebourn@.discussions.microsoft.com>
> | Subject: Large String Parameters Causing Performance Issues
> | Date: Tue, 25 Jan 2005 03:53:03 -0800
> | Lines: 29
> | Message-ID: <EFE8F146-E9CC-49C8-A5B8-4E29E3F57EC2@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:40737
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Hi All,
> | Bit of strange one this. I have a key field in one of my databases that
> is a
> | Char(18). I use this as a parameter field in one of my reports.
> | When I run the report and specify the key field parameter manually the
> | report runs instantly. If I attempt to set this as a parameter variable
> and
> | enter the criteria in the parameter box, the report takes over a minute
> to
> | run. The 2 statements are as follows.
> |
> | 1) select sum(sales_value) as sales from production.dbo.epos_transactions
> | where transaction_ref = '085505010500074995'
> |
> | When run as above, the report runs instantly.
> |
> | 2) select sum(sales_value) as sales from production.dbo.epos_transactions
> | where transaction_ref = @.TREF
> |
> | When I enter the parameter and run the report it takes over a minute to
> run.
> |
> | Running the query through query analyser causes no issues on any of the
> | servers involved and the response time on all servers is instant.
> |
> | I'd be grateful if anyone can shed any light on this.
> |
> | Many thanks for any help.
> |
> | --
> | Nick Colebourn
> | DBA
> | United Coop Ltd
> |
>

Large Parameter Sring

I am using a string for the querystring of a report, and I am passing in parameters to substitute values. However, one of the parameters is over 1200 characters long and when I run the report from the designer it executes, but when I run it from report manager it doesn't run the report, and I don't receive any errors, and nothing shows up in the error logs. Please let me know if there are some kind of limitations on the amount of data a parameter can pass in a string data typeYou are probably hitting this URL limitation:
http://support.microsoft.com/support/kb/articles/Q208/4/27.ASP
Currently, there is no workarond.
--
This posting is provided "AS IS" with no warranties, and confers no rights
"pietrzbm" <pietrzbm@.discussions.microsoft.com> wrote in message
news:62AB9382-CCD8-466C-A36A-71462D174638@.microsoft.com...
> I am using a string for the querystring of a report, and I am passing in
parameters to substitute values. However, one of the parameters is over
1200 characters long and when I run the report from the designer it
executes, but when I run it from report manager it doesn't run the report,
and I don't receive any errors, and nothing shows up in the error logs.
Please let me know if there are some kind of limitations on the amount of
data a parameter can pass in a string data type