04-04-2018 04:01 PM
I have a one-page store metrics report built in VA, with a drop-down filter for store, that I distribute weekly. I think I'd get much better usage if I could send as a PDF, preferably with one page per store (approx 50). I could make it work by duplicating the sheets but that will quickly become a nightmare if / when changes are needed.
Thanks in advance
04-06-2018 12:42 AM
We had a similar issue; we needed to create a report to our customers, with the great look of VA.
This seemed impossible to do. We asked some of the best SAS-people in our area to tell us what to do, but it was really hard to get answers that were useful.
So... Here is my idea for you:
Create the report you want in your favourite Microsoft Office-product using the SAS Add-in for Microsoft Office.
Combining this with row level security should get you where you want to be, except for the PDF-part.
The users do need access to VA to be able to refresh the report.
Let the users log in to VA and open up the report.
At our place, we have created a sort of a "html-wrapper", so that linking to a VA-Report becomes somewhat simpler, and almost completely unbrands VA (no weird SAS-loading-logos) - this is because we have created an entirely new portal with links to VA-reports, and needed our own brading.
What our solution is (this could also be an idea for you, but may seem like a really tough task):
Ok, we also still needed the PDF-reports at our place, so we needed to figure out how to get PDF out of VA.
1) Create the VA-report - we used 1 filter to set a parameter, which is then used in the different calculations in the report (it has lots and lots of pages, so the filter needs to filter every page). The looks of the report is not that important.
2) Create a duplicate of that very same report (we prepended the name with "output").
3) Create a PowerPoint, and using SAS Add-in for Microsoft Office add elements from step 2 (not step 1!)
4) In a SAS-program connect to SASVA WebDav share (2 times filename webdav). A user with high priveledges is needed.
Our MetaData path to the reports looks like this:
/DLRbi/Standard Reports/Report Name Duplicated report:
/DLRbi/Standard Reports/Report Name Output
Using WebDav the path looks like this:
http://sasva/SASContentServer/repository/default/sasfolders/DLRbi/Standard Reports/Report Name(Report) Duplicated report:
http://sasva/SASContentServer/repository/default/sasfolders/DLRbi/Standard Reports/Report Name Output(Report)
(our server is named SASVA)
What we do is read the XML of the original report (named "Report Name" in this example) and overwrite the "Report Name Output" with the changes we made.
5) Step 4 gives us access to the BIRD XML that is the report.
Changing this could ruin the report, but it could also change stuff you can't normally do dynamically.
We had a report prompt (the filter that is used in VA) that we needed to change and a parameter, so looking through the XML we needed to change something like this (this is and example of what it could look like):
<PromptDefinition data="dd193" label="Dynamic parameter" labelFormatted="false" isParameter="true" labelVariable="bi217" valueVariable="bi217" name="pr229"> <DefaultValue> <String>Dynamic Value that needs to change</String> </DefaultValue> <StringConstraint userValuesAllowed="false" required="true" minInclusive="true" maxInclusive="true"/> </PromptDefinition>
We then go through the XML and change/replace what is needed (remember not to overwrite the original XML-file).
You need to know what values you can put in there, as there is no validation.
We loaded the original dataset and then did a distinct on the value we needed to put in there, so that the SAS-program would work even if data got a new receiver.
6) The SAS-program then runs a PowerShell-script, which opens up PowerPoint, wait for the SAS-Addin for Microsoft Office to update the PowerPoint slides (this takes a couple of seconds, give and take) and then exports the PowerPoint to a PDF and then closes PowerPoint again (this can be run in the background without PowerPoint showing up when updating, but then it just looks like you are taking a nap rather than doing your job).
7) Loops through steps 5 - 6 changing the report prompt (the filter) and exporting a PDF over and over again....
The first 2 ideas you could probably figure out easily, or someone on the community could give more insights to this. The last idea is a bit more complex, but I can share more on this if you take this route.
04-06-2018 03:06 PM
Wow! Thank you for such a detailed reply! It's good to know that others need this functionality. I think option 3 is probably the one we'd have to take, although with less than 50 stores I'm tempted to create a section each one....
I'm heading to Global Forum so I'll also ask people there - maybe it's on the roadmap!
04-09-2018 01:06 AM
We have like 70 reports to make, so the setup might seem quite big for such a small number of reports, but to make sure every report is correct and "mistakes is not an option", we needed to make sure, that it works every time.
If you decide not to wait for SAS to come up with the solution (we have already asked them, but "SAS Visual Analytics is an online tool" is always the answer), and want to try out the "hard" way, let me know, and I'll be happy to supply some SAS-code to make it work as written.
Have a nice #SASGF