Management Studio Reports – Part 3 – Standard Reports


Sorry for the delay for part 3.  I have not been able to take our existing standard reports and make them work properly in a Reporting Services environment.  I’m going to release the files anyway, in case one of you can figure out what is wrong.  I’m not a Reporting Services guru, but I hope that somebody out there can find the bug and let us all know.  (I think the problem is in the extraction tool that we used to dynamically localize them into US – English.)


Please see the attached zip file containing all of our standard reports.  You can view them in Business Intelligence Development Studio and look the queries behind them… but for some reason, some (original Disk Usage.rdl for example) will not run out of the box.


Here are some descriptions of parameters for the reports:





































Parameter Name


CLR data type


Comments


ObjectTypeName


String


The type of object. For example, “Database”, “Login”, “Functions”.


ObjectName


String


The name of the object. For example, “Foo”, “AdventureWorksDW”, “GetUserIDFromName”, etc.


ErrorText


String


Used in the Default report to show error information.


Filtered


Boolean


This was used to indicate whether the dataset being passed from OE is filtered or not. We will respect the filters the user has in place in OE and this parameter allows us to indicate on the list reports whether the list is filtered.


ServerName


String


Name of the server and instance currently connected. In the form of “server\instance” for a named instance and “server” for a default instance.


Prompt Name: ServerName


Allow Null:    checked


Allow Blank:  checked


Avail Values: none


Defaults:       none


FontName


String


Name of the font to be used to display the report.


Defaults:       Non-Queried – “Tahoma”


DatabaseName


String


Name of the database containing the current object. If the object is not database scoped, this value will be an empty string.


Please post a comment if you have any questions/suggestions.


Paul A. Mestemaker II
Program Manager
Microsoft SQL Server

SSMS Standard Reports.zip

Comments (12)

  1. Have you wanted to get your hands on the Reports that are available in Management Studio. Well you now…

  2. If you’ve downloaded the SSMS reports and want to get the Disk Usage report working. Follow these…

  3. If you’ve downloaded the SSMS reports and want to get the Disk Usage report working. Follow these…

  4. Charl Reid says:

    Hi Paul
    Fantastic work on making these reports available! As SimonS says it is easy to get the Disk Usage report working if you remove (or modify /* */ for –) the comments and add an ; after each last declare; however there is one issue I can’t seem to track down.

    The section labeled Disk Space Used By Partitions has an error against the column Partition Defined On which is this code:

    =IIF(Fields!index_id.Value = 0,”Table”
           ,”Index ({0})” Fields!name_1.Value — This portion has a syntax error
           )

    Will keep trying to resolve this, has anyone else got an idea?

    Best regards,

    Charl Reid

  5. antxxxx says:

    One of the things I really like in sql 2005 is the management reports available from within management…

  6. Keith says:

    The section labeled Disk Space Used By Partitions has an error against the column Partition Defined On which is this code:

    =IIF(Fields!index_id.Value = 0,”Table”

          ,”Index ({0})” Fields!name_1.Value

          )

    If you replace it with the following it fixes the problem.

    =IIF(Fields!index_id.Value = 0,”Table”

           ,”Index (“&(Fields!name_1.Value)&”)”

           )

  7. Sander Stuurwold says:

    Hello,

    when I try open the "AS_Database" report after I change the name of the database in the datasource (datasource2) I get an eror (I copied and named the report SAS_Database).

    Error 1 [rsCompilerErrorInExpression] The Value expression for the textbox ‘textbox8’ contains an error: [BC30456] ‘SqlServer’ is not a member of ‘Microsoft’. c:documents and settingsadministrator.mitros-netdesktopssms standard reportsssms standard reportsSAS_Database.rdl 0 0

    Please help me because I do not know what I do wrong

  8. Dan's Blog says:

    Did you know that Management Studio (SSMS) has a rich set of built-in reports? Furthermore, did you know

  9. bean says:

    where’s the zip with standard reports???

  10. Randy In Marin says:

    I think the IIF evaluates the last argument even if it is null and not used.  I got around this by

    1) Uncomment the line in one of the select statements which returns the "partition_defined_on" field.  

    select a1.object_id

    ,       a2.name

    ,       Case when a1.index_id = 0 then ‘Table’ else ( ‘ Index (‘ + a3.name + ‘)’) end as partition_defined_on

    ,       a1.index_id

    2) Then a little below that code, replace name_1 and index_name with partition_defined_on

    Before

           <Field Name="name_1">

             <DataField>index_name</DataField>

           </Field>

    After

           <Field Name="partition_defined_on">

             <DataField>partition_defined_on</DataField>

           </Field>

    3) Then remove the IIF

    Before

                             <Value>=IIF(Fields!index_id.Value = 0,"Table"

           ,"Index ({0})"(Fields!name_1.Value)

           )</Value>

    After

                             <Value>=Fields!partition_defined_on.Value</Value>

    It runs.  I won’t say it runs correctly, however.  

  11. narayanann says:

    can i able to copy the entire structure of the tables in a database

  12. vikasdharia says:

    Is there a repository similarm to this for MDW reports, if so please share, I need to web deploy them