Software Metering Console View – Part 7

Today I was reading the newsgroups and I was telling people that you should not run report against SQL tables but instead follow best practice and run reports against the views. To proved some examples on how this person can do what he wanted I send the link to Software Metering Console View – Part 6.

 

However in this series no where do I going into creating a view for the table that was created in Software Metering Console View – Part 5! Now is the time to remedy that!

 

MICROSOFT WILL NOT SUPPORT THESE UPDATES!
Have said the prerequisite warning, keep in mind that MS might during service package or version upgrade might delete all objects from the db, therefore I recommend having a backup of all your custom queries, function, tables, etc.

 

This will create the view.

Create view v_ES_Language

as

select    Langid as ‘LanguageID’,

            LangDes as ‘LanguageDescription’

from     Language

Go

 

This will add the appropriate security to the view so that it can be used within web reports.

 

GRANT Select ON v_ES_Language TO [webreport_approle]

go

 

And now finally our query looks like this.

 

SELECT

 MPR.ProductName AS ‘Name’,

 MPR.OriginalFileName AS ‘Original File Name’,

 MPR.FileName AS ‘File Name’,

 MPR.FileVersion AS ‘Version’,

 Lang.LanguageDescription as ‘Language’,

 MPR.SourceSite AS ‘Site’,

 case MPR.ApplyToChildSites

 when ‘1’ then ‘True’

 else ‘False’

 end as ‘Apply to Child Site’,

 case MPR.Enabled

 when ‘1’ then ‘True’

 else ‘False’

 end as ‘Enabled’,

 MPR.SecurityKey as ‘Rule ID’

FROM

 v_MeteredProductRule MPR,

 v_ES_Language Lang

Where

 MPR.LanguageID = Lang.LanguageID

Leave a Reply

Your email address will not be published. Required fields are marked *