Comparing Reporting Programs

I am developing an ASP.Net 2.0 application that will make heavy use of reports. The VisualStudio.Net 2005 Professional edition contains two reporting programs -- Crystal Reports and SQL Server 2005 Reporting Services. Can anyone refer me to a white paper, technical article, book chapter, blog, etc. that compares the strengths and weaknesses of these two programs? Thanks for any suggestions.

Ken McLean

In my opinion SSRS, though far from perfect beats Crystal Reports. I will look up some references for you.

|||

Have a look at:

SQL Server Reporting Services 2005 XML extensions http://www.codeproject.com/KB/reporting-services/SSRS2005XML.aspx|||

Thanks a lot. That's exactly what I was looking for.

Ken McLean

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 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

comboboxes input parameter

Is it possible to configure combo box with values depending on another
combobox selection in Reporting Parameters
e.g.
Input1: a combobox with country
Input2: should dynamically display states in the selected country, which
user can select.
appreciate any pointers . thxOn Apr 19, 12:06 pm, "nkg" <x...@.yahoo.com> wrote:
> Is it possible to configure combo box with values depending on another
> combobox selection in Reporting Parameters
> e.g.
> Input1: a combobox with country
> Input2: should dynamically display states in the selected country, which
> user can select.
> appreciate any pointers . thx
Sure. What you want to do is create a dataset to use for the second
report parameter (in the Data tab, select the <New Dataset...> option
from the drop-down list to the right of Dataset) and include the usual
query as well as the place holder parameter (i.e., select States from
StateTable where Country = @.Country). Then select the 'Edit Selected
Dataset' button [...] and on the Parameters tab enter in the Name of
the parameter (in this case, @.Country) [it is case sensitive by the
way] and select the Parameter that @.Country should be dependent on
(most likely, something like: Parameters!Country.Value). hope this
helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||thx. a lot that worked.
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1177009678.611966.300310@.b58g2000hsg.googlegroups.com...
> On Apr 19, 12:06 pm, "nkg" <x...@.yahoo.com> wrote:
>> Is it possible to configure combo box with values depending on another
>> combobox selection in Reporting Parameters
>> e.g.
>> Input1: a combobox with country
>> Input2: should dynamically display states in the selected country, which
>> user can select.
>> appreciate any pointers . thx
>
> Sure. What you want to do is create a dataset to use for the second
> report parameter (in the Data tab, select the <New Dataset...> option
> from the drop-down list to the right of Dataset) and include the usual
> query as well as the place holder parameter (i.e., select States from
> StateTable where Country = @.Country). Then select the 'Edit Selected
> Dataset' button [...] and on the Parameters tab enter in the Name of
> the parameter (in this case, @.Country) [it is case sensitive by the
> way] and select the Parameter that @.Country should be dependent on
> (most likely, something like: Parameters!Country.Value). hope this
> helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Apr 20, 2:45 pm, "nkg" <x...@.yahoo.com> wrote:
> thx. a lot that worked."EMartinez" <emartinez...@.gmail.com> wrote in message
> news:1177009678.611966.300310@.b58g2000hsg.googlegroups.com...
> > On Apr 19, 12:06 pm, "nkg" <x...@.yahoo.com> wrote:
> >> Is it possible to configure combo box with values depending on another
> >> combobox selection in Reporting Parameters
> >> e.g.
> >> Input1: a combobox with country
> >> Input2: should dynamically display states in the selected country, which
> >> user can select.
> >> appreciate any pointers . thx
> > Sure. What you want to do is create a dataset to use for the second
> > report parameter (in the Data tab, select the <New Dataset...> option
> > from the drop-down list to the right of Dataset) and include the usual
> > query as well as the place holder parameter (i.e., select States from
> > StateTable where Country = @.Country). Then select the 'Edit Selected
> > Dataset' button [...] and on the Parameters tab enter in the Name of
> > the parameter (in this case, @.Country) [it is case sensitive by the
> > way] and select the Parameter that @.Country should be dependent on
> > (most likely, something like: Parameters!Country.Value). hope this
> > helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Glad I could be of assistance.
Regards,
Enrique Martinez
Sr. Software Consultant