Category Archives: SSRS

Integrating Google Drive

Most people know my favourite challenge is:

‘It can’t be done.’

So when a client wanted to update a dozen different google drive folders with different data every week, this came straight to my desk.

Google offer some great services, but they make integrating it into a business process a little clunky, or expensive.

Take Maps, for instance. If you want to use them for business there’s a $10k charge per annum. Interestingly, they’ve just dropped fusion tables, a quick way to integrate maps and data. That decision is going to bite somebody somewhere, but I digress.

Getting the data ready, good old Sql Server Reporting Services (SSRS) gave the basis of the data. An elegantly presented price list, in colour saved as a pdf. Setting up a subscription to save to a file share, rather than screen or email allowed me to create a folder of appropriate price lists.
A quick ferret around the SSRS subscription table gave me a clue how to run multiple subscriptions in a single pass.
In general I hate using a CURSOR to loop in SQL, I will almost do anything I can to avoid it. However, this really did seem the easiest way to produce a flexible batch of subscriptions.

declare @subscriptionID as NVARCHAR(50)
declare @mycursor as cursor
set @mycursor = CURSOR for
select SubscriptionID from ReportServer.dbo.Subscriptions where Description like ‘%\pricelist%’;
open @mycursor
fetch next from @mycursor into @subscriptionID
while @@FETCH_STATUS = 0
BEGIN
exec [ReportServer].dbo.AddEvent @EventType=’TimedSubscription’, @EventData=@subscriptionID
fetch next from @mycursor into @subscriptionID

END
CLOSE @mycursor
DEALLOCATE @mycursor

What I did encounter was a distinct lack of VB.NET examples of how to use the Google API (V3) Not only did not much exist, but the ones that did, appeared to have errors in them (most of them missed out the ‘Execute’). It’s like any API, some are worse than others and once you have worked with it. It starts to hang together and make sense.
Because Google drive does not actually care if files of the same name exist in the same folder, I created a delete function to make sure all copies of a document were deleted.

Private Sub DeleteFile(inFile As String, infolder As String)
Dim parents As New List(Of String)
Dim flist As New List(Of File)

If Service.ApplicationName <> “Google Drive VB Dot Net” Then CreateService()
Dim listRequest As FilesResource.ListRequest = Service.Files.List()
listRequest.Fields = “*”
Dim files As FileList = listRequest.Execute()
For Each myFile As File In files.Files
If myFile.Name = inFile Then
Dim plist As New List(Of String)
plist = myFile.Parents
If plist IsNot Nothing Then
If plist.Count > 0 Then

For Each par As String In plist
If par = infolder Then
Dim delme As FilesResource.DeleteRequest = Service.Files.Delete(myFile.Id)
delme.Execute()
End If

Next
End If
End If

End If
Next
End Sub

I did find the parent list an odd feature. Google drive will allow you to link a file into multiple folders. Rather like a Unix link. I know Windows has the same, but I have never found any applications that used it.

So, now by combining SSRS and some cloned .NET my client now has pricelists on an easy to access location for their sales folk to pick up.

SSRS Reporting

More time for for honey?

“What are you doing, Pooh?” asked Piglet.

“I’m busy busy busy. I’ve been working since 7am. I have to get this data ready for Christopher Robin’s board meeting.”

“How do you do that?” said Piglet.

“It’s very complicated,” Pooh scratched his head. “I have to run this report and then extract the columns I need into a spreadsheet.

“Then, I run this macro that removes the blank lines and headings. I then copy it into this spreadsheet and add the totals.”

“Wow,” Said Piglet. “Does that ever go wrong?”

“No, as long as nobody distracts me, and I do it exactly the same without missing out a step. Even a bear of very little brain can do it,” said Pooh. “Finally, I print it to a pdf and email it to everybody on a list I have.”

“Oh,” Piglet said. “Why not get somebody to write an SSRS report that does all of that without you touching it?”

“More time for honey?” Pooh said.

“Exactly,” Piglet replied.