Vista - reporting services and powershell results

I'm looking for ideas on how best to consume Powershell result sets from
Reporting Services 2005.

One method I was trying was a combination of powershell, and XML output. I
found an XML function (see function below) on a blog, which I've modified to
output an XML string that RS can read as an embedded XML datasource. I
create an XML datasource in Reporting Services, and embed the XML string as a
hardcoded query. (see step 9 from:
http://msdn2.microsoft.com/en-us/library/aa337458.aspx) Works great, but
it's still static.

I haven't figured out how to send dynamic results. I've tried experimenting
with custom code in Reporting Services, but it's starting to involve a lot
more "massaging" before I can get a viable dataset that Reporting Services
can recognize.

I'm hoping the newsgroup can drop some ideas whether there's a more
efficient way of leverage Reporting Services against powershell.

Powershell Reporting Services XML export function (I apologize to the
author...I forget where):

# ========================================
function New-RSEmbeddedXml($ChildItems="*")
{
Begin {
$xml = "<Query><XmlData><Root>"
}

Process {
$xml += "<ITEM>"
foreach ($child in $_ | Get-Member -Type *Property $childItems){
$Name = $child.Name
$xml += " <$Name>$($_.$Name)</$Name>"
}
$xml += "</ITEM>"
}

End {
$xml += "</Root></XmlData></Query>"
$xml
}
}
# ========================================

Thuan wrote:Reporting Services isn't available with SQL Express, so I need to go out
and get a VM setup. I tried to get a SQL 2005 VHD on Friday, but seemed
to have authentication problems with my Live account.

It will be Sunday night before I can get back to restarting the
download, and depending how big it is, it could be Monday before I have
it running (and even longer if the VHD image doesn't have Reporting
Services installed by default).

Still need help and can you wait?



--
Microsoft MVP - Windows PowerShell
http://www.microsoft.com/mvp

PowerGadgets MVP
http://www.powergadgets.com/mvp

Blog:
http://marcoshaw.blogspot.com

"Marco Shaw [MVP]"
Live Search: "Reporting Services 2005" +express
http://search.msn.com/results.aspx?q...%22+%2Bexpress

Reporting Services in SQL Server 2005 Express Edition with Advanced
Services
http://msdn2.microsoft.com/en-us/library/ms365166.aspx

On Oct 13, 1:26 pm, "Marco Shaw [MVP]" <marco.shaw@_NO_SPAM_gmail.com>
wrote:SQLExpress/Advanced Services is free, and includes Reporting Services.

- Oisin

Right now, I have a clunky solution involving running a powershell script,
and exporting its results to a tempoary CSV file. I then import that file
into SQL server via a BULK IMPORT statement into a temporary SQL table from a
stored procedure. The same stored procedure ends by doing a SELECT on that
temp table. From Reporting Services, call that sproc and consume the data
like any other sproc.

This would've worked out ok, but I plan to report on some data that include
comma's...so exporting it to a CSV file doesn't leave a clean dataset.
Another option is to export directly to a real SQL table (instead of the temp
table), but I'm trying to avoid needing to create more objects that are
required. In this case, this method would only need the powershell script,
the sproc, and the report.

Hope someone comes up with more elegent solution.

Thanks!


"Marco Shaw [MVP]" wrote:

> Hope someone comes up with more elegent solution.

I saw this today:
http://www.paulstovell.net/blog/inde...es-automation/

I don't know if this help.

Honestly, I don't know Reporting Services at all. I mentioned I would
look into this earlier this week, but just haven't found the time.

Marco