Comparing Financial values across random dates

I submitted this issue to the reporting services thread, but I'm beginning to think I may have to do something in SSAS:

I want to enable my users to select 2 random month end dates in a report parameter. After selecting these dates, I want to be able to take the corresponding financial values, be able to line them up next to each other in a report, and calculate the difference. Obviously in a matrix I can line the values up, but I am then not able to calc a difference. I do have a time hierarchy that allows me to do this across dates at the same level of granularity, i.e. year to year, qtr to qtr, etc., but if my users want to compare February month end to November month end, it's a problem. I am more than willing to add calculated members to the cube to get this done, I just haven't figured out the MDX for it.

Thanks in Advance...

I think this can be done without any calculated members, simply by using the following query:

SELECT

{Measures.ImportantFinancialValue1, Measures.ImportantFinancialValue2, ...} ON COLUMNS

, {Time.Date1, Time.Date2} ON ROWS

FROM Cube

HTH,

Mosha (http://www.mosha.com/msolap)

|||

Mosha-

Thanks for your quick response. I am using SSAS on top of SSRS, and am trying to avoid writing MDX as these reports may get very involved. Can I replicate this in a calc'd member, can it be dynamic enough to accept values at runtime?

|||I am supposed to find out the total number of open account between February - April and i am having difficulties. Below is a working statement for SQL which i am supposed to do in MDX for my Cube:

SELECT COUNT(*) AS Expr1

FROM ConsumerAccount

WHERE (DATEPART(yyyy, AccountOpenedDate) = '2007')

AND (DATEPART(mm, AccountOpenedDate) IN (02, 03, 04))

I have a cube called Batch process, column AccountOpenedDate and CrearedOnDate, how do i do it

Comments

Does anyone know how to add a comment in a sql statement in reporting
services, all the normal types e.g
/*comment*/
and
-- comment
Dont work, it just removes then as soon as you move away from the sql
window.
could maybe be a menu choice but i cant find it!
Help please
Steve DActually, it seems that they don't go away properly. If you add a comment,
it will be visible in the code view. It might be visible if you open the
data set box where you change the data source etc.
Still, it's quite silly. It's not very usable.
Kaisa M. Lindahl Lervik
"Steve Dearman" <steve.dearman@.grant.co.uk> wrote in message
news:uL4pLQttGHA.4852@.TK2MSFTNGP02.phx.gbl...
> Does anyone know how to add a comment in a sql statement in reporting
> services, all the normal types e.g
> /*comment*/
> and
> -- comment
> Dont work, it just removes then as soon as you move away from the sql
> window.
> could maybe be a menu choice but i cant find it!
> Help please
> Steve D
>

CommandText issue - SQL Reporting Services

I'm having a ton of trouble with a dataset. It builds at design time,
but fails at runtime, saying:

--------
Processing Errors
--------
An error has occurred during report processing.
Cannot set the command text for data set 'ds_Legal_Entity'.
Error during processing of the CommandText expression of dataset
'ds_Legal_Entity'.
--------
OK
--------

Below is the CommandText for ds_Legal_Entity that gives me the error:

="SELECT DISTINCT dbo.t_d_legal_entity.legal_entity_desc FROM
dbo.t_d_legal_entity INNER JOIN dbo.t_f_month_summary ON
dbo.t_d_legal_entity.legal_entity_key =
dbo.t_f_month_summary.legal_entity_key WHERE
(dbo.t_f_month_summary.acctg_mth_key = " &
Parameters!acctg_mth_key.Value & ")" &
IIF(Parameters!BusUnitKey.Value = 0,""," AND
(dbo.t_f_month_summary.bus_unit_key = " & Parameters!BusUnitKey.Value &
")") & " ORDER BY dbo.t_d_business_unit.legal_entity_desc"

If I delete everything after " & Parameters!acctg_mth_key.Value & ")",
I won't get the error, so I assume that's where the problem lies. I
just need another pair of eyes to see it.

Thanks!
Mike(Michael.EJ.Reynolds@.gmail.com) writes:
> Below is the CommandText for ds_Legal_Entity that gives me the error:
>="SELECT DISTINCT dbo.t_d_legal_entity.legal_entity_desc FROM
> dbo.t_d_legal_entity INNER JOIN dbo.t_f_month_summary ON
> dbo.t_d_legal_entity.legal_entity_key =
> dbo.t_f_month_summary.legal_entity_key WHERE
> (dbo.t_f_month_summary.acctg_mth_key = " &
> Parameters!acctg_mth_key.Value & ")" &
> IIF(Parameters!BusUnitKey.Value = 0,""," AND
> (dbo.t_f_month_summary.bus_unit_key = " & Parameters!BusUnitKey.Value &
> ")") & " ORDER BY dbo.t_d_business_unit.legal_entity_desc"
>
> If I delete everything after " & Parameters!acctg_mth_key.Value & ")",
> I won't get the error, so I assume that's where the problem lies. I
> just need another pair of eyes to see it.

The best way to sort this out, is to grab the generated SQL and then
copy and paste into Query Analyzer to run it there for hopefully a
better error message.

The one error I can see is that the ORDER BY clause includes a column
which is not in the SELECT list, which is required when you have DISTINCT.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

command type = Stored procedure

Hi,
does anyone know an URL with an example of using "StoredProcedure" as
"Command type" in reporting services. Normally it defaults to "Text" instead
of "StoredProcedure"
thanks,
Ludowell its the same as the text only u write the name of the sp(no need exec..).
if the sp has input parameters the RS will add them by default as Report
parameters.
I think it's the best way 2 use RS...
"Ludo Van Dun" wrote:
> Hi,
> does anyone know an URL with an example of using "StoredProcedure" as
> "Command type" in reporting services. Normally it defaults to "Text" instead
> of "StoredProcedure"
> thanks,
> Ludo
>
>

Command line to take one cluster Group offline

We have an issue in our environment that requires me to bounce a cluster
group and associated services ever other day and Im getting tired of it (out
of control developers).
I would like to create a script that I can schedule to do this and would
like to know if there is a command line that will take just one cluster
group offline and then bring it back online. I have under groups in my
cluster admin: Cluster information, DataMirror, SQL Backup, SQL Data and SQL
Logs. I need to bounce the DataMirror one.
Thanks!!
Derrick
I figured it out after tinkering.. it is:
for my example
cluster /cluster:sqlcluster group datamirror /offline
thx..
derrick
"Turbo [GEEK]" <riveradl@.hotmail.com> wrote in message
news:u7PIk4I4FHA.3292@.tk2msftngp13.phx.gbl...
> We have an issue in our environment that requires me to bounce a cluster
> group and associated services ever other day and Im getting tired of it
> (out of control developers).
> I would like to create a script that I can schedule to do this and would
> like to know if there is a command line that will take just one cluster
> group offline and then bring it back online. I have under groups in my
> cluster admin: Cluster information, DataMirror, SQL Backup, SQL Data and
> SQL Logs. I need to bounce the DataMirror one.
> Thanks!!
> Derrick
>

Command line qeury of service

I have been looking at SC.exe from the resource kit to query sql server
services from command line. Anyone have any recommendations other than this
to do so.
I was looking to query all my sql servers to check services and output to a
file.Russ,
How about VBScript and WMI? Or VBScript and SQL-DMO?
HTH
Jerry
"RussN" <RussN@.discussions.microsoft.com> wrote in message
news:EEA1A919-672C-441F-8F4D-9838F67650FE@.microsoft.com...
>I have been looking at SC.exe from the resource kit to query sql server
> services from command line. Anyone have any recommendations other than
> this
> to do so.
> I was looking to query all my sql servers to check services and output to
> a
> file.

Command line installation of SQL Server 2005 Express with Advanced Features

Hi,

I'm trying to install SQL Express 2005 with Reporting Services using Install Shield 11.5. The command line parameters which i am using is:

/qn ADDLOCAL=SQL_Engine,SQL_Data_Files,RS_Server,RS_Web_Interface,Client_Components SECURITYMODE=SQL SAPWD=<password> DISABLENETWORKPROTOCOLS=0 RSCONFIGURATION=Default RSSQLLOCAL=1 AUTOSTART=1

It installs the SQL Server and Reporting Services. It configures (creates Virtual Directories for report server and database) but it is not initializing the Report Server.

Having done the installation when i check the Report Server Status it was running and everything seems to be OK except it wasn't initialized.

Any idea?

Thanks,

Moby.

It's been a long time since I've played around with this, but I think it is the RSCONFIGURATION command.

Here's the snippet from template.ini. It looks like the default is FilesOnly, which does not configure RS. Can you try it with the Default value and let us know if it works?

;--
; RSCONFIGURATION specifies the Reporting Services installation option.

; The installation options can be either “FilesOnly” or “Default”.
; The “FilesOnly” option will only install the files without configuring the Reporting Services.
; The “Default” option will install the files and configure the Reporting Services.
; If you specify RSCONFIGURATION=Default, you must set RSSQLLOCAL=1
; NOTE: If no option is specified the default is “FilesOnly”.
; For example:
; RSCONFIGURATION=Default
; RSCONFIGURATION=FilesOnly

Thanks,
Sam Lester (MSFT)

|||Hi,

Well i am using the Default configuration option as if you look at the parameters in my previous post. As i said before it does all configuration except initializing the server.

But now i have figured it out. Thanks for your efforts. Having installed it with the parameters used in my previous message, you need to restart the PC. Once it is restarted the server will be initialized.

Thanks,
Mubashir