SSAS: Custom Reports in SP2

Chris Webb blogged about the new Custom Reports feature that was added to SSMS in SP2 here http://cwebbbi.spaces.live.com...cns!7B84B0F2C239489A!946.entry, while SP2 was still in CTP. These reports work great for the relational engine. However, while there are a number of standard reports that have been added for various SSAS objects, if you go to add your own custom reports you will find the following:

 

Notice how the "Custom Reports..." option is greyed out? Apparently Custom reports are only supported for the relational engine and there are not any plans at the moment to extend support to the other servers.

I have a theory about why this is the case. One of the dev team made the RDL files for the standard reports available here (http://blogs.msdn.com/sqlrem/archive/2006/08/30/SSMS-Reports-3.aspx) If you have a look at the RDL for the standard SSAS reports you will notice something strange about the queries for the data sets. They are completely empty. What does this mean? My theory is that they are providing the data to these reports by building a dataset which they push into the report (this is a feature that is available with client-side Reporting Services reports). This makes the concept of custom reports for SSAS redundant as in the current implementation there is no way to specify an alternative query.

I must admit, I was really curious to see how they had implemented queries against the metadata of SSAS as I could not see how they would be implemented. The only option I could think of was to fire off an XMLA discover command, but reporting services will only let you use an xml datasource over the HTTP protocol. So the only way I could think of to implement these sort of queries was to either...

  • implement a custom dataprovider, but I don't think these will work with client side SSRS reports.
  • implement a "reporting" webservice proxy, but the deployment of such a service would be extremely problematic to say the least.

...both of which I did not really think would work. I do have another idea about how I might be able to get meta data reports working in Reporting Services. If I get anything interesting working I will post some samples.

Print | posted on Monday, March 5, 2007 8:43 PM

Comments on this post

# re: SSAS: Custom Reports in SP2

Requesting Gravatar...
Hi Darren,

I´ve just faced this type of constraint when trying to provide customer a standard way of controlling cubes with "lots" of partitions. They need to know for example, if cube data is consistent against sql data or get an enumeration of those partition that have not been procesed correctly and so on.
For those type of info I only need to access via AMO or ADOMD.
Left by LeandroTubia on Dec 12, 2007 12:41 AM

# re: SSAS: Custom Reports in SP2

Requesting Gravatar...
I don't know if you have looked at PowerShell at all, but combined with powerSSAS you could extract this sort of information pretty easily.
Left by Darren Gosbell on Dec 12, 2007 7:56 AM

# re: SSAS: Custom Reports in SP2

Requesting Gravatar...
Hi Chris

You can connect to the SSAS Metadata repository thru SQL queries, which relate to a linked server to SSAS.
In 2008 you should be able to directly read the DMVs, whereas in 2005 you can create a workaround by creating views, which execute openquery calls against the ASSP assembly(codeplex).

cheers
Michael
Left by Michael Brönnimann on Jul 31, 2009 5:12 PM

# re: SSAS: Custom Reports in SP2

Requesting Gravatar...
Hi Michael,

This is my blog, not Chris's. I am aware of the linked server technique and the functions in ASSP (which I wrote). And while you can write reports using these techniques, neither of these will currently work with the custom reports feature in SSMS.

Regards
Darren
Left by Darren Gosbell on Aug 01, 2009 4:46 PM

# re: SSAS: Custom Reports in SP2

Requesting Gravatar...
Humm... interesting,

powershell is easier to use to get this sort of information...

Thanks for writing about it
Left by Web developers on Oct 16, 2009 8:05 PM

Your comment:

 (will show your gravatar)