Automate Software Metering Monthly Report.

[rickym61]

Just wondering if anyone has any ideas how to automate a software metering report for all future months, currently setup as an SSRS subscription.

 

example, new rule created for Visio, if you run the built in software metering reports, you have to select the values, so software metering rule name (easy part), year (easy part too) then the month is required, now if I want future months, I have to edit the SSRS subscription parameters  every month as that data/value isn’t available until a user/computer invokes that application.

Invoke-cmreport cmdlet would launch the report but can’t find a way to tie that into the SSRS subscription so I can set it up and let it run monthly without any intervention.

any ideas?

thanks.

 

[GarthMJ]

Ys you can do this but you will need to edit the report and modify the monthly prompt to use getdate and datepart to auto populate the prompt. 

[rickym61]

On 4/26/2018 at 1:36 PM, GarthMJ said:

Ys you can do this but you will need to edit the report and modify the monthly prompt to use getdate and datepart to auto populate the prompt. 

Hi Garth,

I tried this with some success, maybe im missing something obvious, you able to advise any further or one of your famous blog posts

so I've edited the parameter and added this expression =datepart(DateInterval.Month,dateadd(DateInterval.Month,-1,Today))

If I then run the report it will pick up the previous month which is good, tested this in April and today being 1st May, showed month value correctly. Tested hiding the month parameter and not hiding it.

Now the issue is with the subscription, now states "The subscription contains parameters that are not valid" - if I show the parameter then you have to select what is available as values, so there would be nothing for June until we are in June.

[GarthMJ]

check to see if the report is looking for a number or a name. 

[rickym61]

It’s a number, single digit (for now that is)

[GarthMJ]

Why not update the SQL query and not user SSRS itself. 

Something like this. 

Select datepart(mm,getdate()-1) as 'month'

[rickym61]

I did look at the original SQL used but it was overwhelming, made use of rbac fn and other bits, hence my request for help from the skilled :-)

im sure there’s a simple method to get it done but just can’t find it yet.