Darryl Booth

Invoking Report Builder Reports With Passed Parameters

EnvisionConnect, as you probably know, leverages SQL Server's Reporting Services and Report Builder for its ad hoc reporting. This is a fantastic collaboration since it frees up Decade's teams to focus on our domain expertise (software environmental regulation and public safety) AND it provides a superior interface for casual reporting.

This post is for EnvisionConnect Admins (technical folks) who want to do a little bit more to integrate EnvisionConnect and Report Builder.

I must also acknowledge MSSQLTips and Weld County Environmental Health Services Division for inspiring me to write this post.

Let's say you've created a great Report Builder report that summarizes all the activities (e.g., inspections and other services) related to a Complaint. The report prompts for the Complaint ID, but you want that to come over automatically. You also want to invoke this report from within EnvisionConnect for the complaint you're reviewing.

Step 1 - Isolate the Report URL

As you probably know, Report Builder reports can be accessed through a web browser using Report Manager. This is wonderful since you probably have stakeholders across the enterprise that don't really need to be in EnvisionConnect, but DO need to quickly check a record. My simple report looks like this when displayed in a browser:

The intranet URL for the "Standard" view of the report is as follows:

http://envisionconnect/Reports/Pages/Report.aspx?ItemPath=%2fSummary+of+Complaint+Activities (not a real URL)

Server name: envisionconnect (your server name may be different)

Report name: Summary+of+Complaint+Activities (your report name will be different)

This URL can be shared with anybody who has permissions to the report.

The report can also be "served" up at this alternate URL:

http://envisionconnect/ReportServer?%2fSummary+of+Complaint+Activities&rs:Command=Render (not a real URL)

Server name: envisionconnect (your server name may be different)

Report name: Summary+of+Complaint+Activities (your report name will be different)

It's with this second URL that we can start to do some fancy stuff.

Step 2 - Tweak the URL to Pass Parameters and Commands

According to MSSQLTips we can augment this URL to do our bidding by passing one or more report parameters, hiding the parameter panel, or even rendering the report in a certain format.

To Specify a Report Parameter Add to the URL:

&ParameterName=ParameterValue

So, to pass a particular Complaint ID to the report, the URL would look like this:

http://envisionconnect/ReportServer?%2fSummary+of+Complaint+Activities&rs:Command=Render&ComplaintLogRECORDID=CO0034751

So, how do you get the report's Parameter Name? You can guess it... In my experience it's the Entity Name smashed up against the Field Name. But to be sure, you have to look into the report file.

To look into a report file, use these steps:

- Open the report in Report Builder.

- Choose File > Save as File and save the RDL file to your desktop.

- Open the RDL file in Notepad and search for a section titled <ReportParameters>.

You'll see the name of your parameter in this section. For my example, it looked like this:

<ReportParameters>
  <ReportParameter Name="ComplaintLogRECORDID">
    <DataType>String</DataType>
    <Nullable>true</Nullable>
    <DefaultValue>
      <Values>
        <Value>="CO0034751"</Value>
      </Values>
    </DefaultValue>
    <Prompt>Complaint Log RECORD ID</Prompt>
  </ReportParameter>
</ReportParameters>

To Hide the Parameter Panel Add to the URL:

&rc:Parameters=false

To Open in a Specific Format Add to the URL:

&rs:Format=PDF or &rs:Format=Excel

Step 3 - Configure an EnvisionConnect System Tool to Launch Your Report

From within EnvisionConnect, users and administrators can configure "System Tools." This feature adds a link to external resources such as web sites, scripts, and executables. In addition, the System Tools can reference parameters from the EnvisionConnect screen. In our example, we'll scrape the Complaint Record ID off the Enter Complaint Details page to customize the URL and get the report we want.

To configure a System Tool that invokes our custom report:

- Launch Manage System Tools from the System Administration Center.

- Click Add a System Tool to see the Enter System Tools Details page. Using the URL from the example above, my system tool configuration looks like this:

- Don't forget to replace the actual Complaint Record ID ("CO0034751") with the linking placeholder {RecordId} so the system will do the replacement for you on the fly.

After the configuration, your users should be able to see Summary of Complaint Activity under the tools.

In Conclusion

I know this was a rather technical post... but I'm pretty excited about the potential for tying Report Builder Reports and EnvisionConnect System Tools in ways that make your life easier. Enjoy!

Sneaky Sneaky Restaurant

I came across this funny snapshot on the web today (pictured below).

 

 

If you look closely, you'll see that the restaurant dutifully posted their inspection grade placard (a "C" - nothing to be proud of) as required by local ordinance... but they sneakily integrated the "C" into the larger message you see in the picture.

Our team has worked with many health departments who have implemented grading or color-coded placard systems with Envision and EnvisionConnect.

I've heard LOTS of concerns about operators falsifying placards, removing placards, disputing grades, etc. But, I've never once seen this scheme.

The good news is that if they ever do get an "A," they've got a ready-made spot for it on the window... one pane to the right. Don't know what they'll do if they get a "B."

Annual Conference Season

Summer is nearly over and, to me, the coming fall season represents change. The 2008 Decade User Group and Conference is right around the corner. At this all-user event I get to meet new people and renew old friendships. This is where I get to see how the work done inside our office impacts those who use the system outside the office. The enthusiasm is engaging. It recharges my batteries and resets my reality.

This is my twelfth Decade User Group and Conference meeting.

"For the first time ever in North America, Envision for Windows!"

At that first all-user conference at the Piccadilly Shaw in 1997 I had the honor of showing Envision for Windows to our users for the very first time.

In my best radio voice I exclaimed, "For the first time ever in North America, Envision for Windows!" With that, I launched Envision and started the presentation. Folks were ready for a Windows version of Envision.

Even change for the better (replacing Envision for DOS for Envision for Windows) is still change and can still be disruptive.

EnvisionConnect Upgrades

Now, flash ahead more than a decade (no pun intended), we're deep into EnvisionConnect upgrades and it's quite fulfilling to point out features that were prompted by that same user community. You guys are awesome!

I anticipate that this month's conference will be characterized by the same energy and enthusiasm we enjoy each year. I know the Decade staff have been working diligently to prepare meaningful presentations.

So, mark your calendars and be sure to seek me out. I'll be the one presenting the class on Report Builder.

Decade Software Company's Microsoft Partnership

Decade Software Company is a Microsoft Certified Partner and it's nearly time to renew our membership for the coming year. This is among my "Must Do" tasks this week.

We first achieved this milestone in 2003 and it has been both beneficial and fascinating experience.

The benefits are substantial, but what fascinates me is how a multi-billion dollar corporation courts its small partners and makes them feel important. It's good fun to bash Microsoft... but let me point out a couple bright points.

• Microsoft initially sought out us... that is, they contacted Decade Software Company and asked us to consider partnership.
• Microsoft lowered the barriers to program enrollment to make it an easy and attractive proposition.
• A Microsoft rep calls me monthly to gauge our needs, tell me about the latest MS events, and to make sure they know about our efforts (and challenges).
• The partner program provides us with easy access to virtually all MS software products which bolsters our capacity to test our product and support our customers.
• They called me to remind me that it was time to renew.

It's not all wine and roses. The whole interaction can be kind of sterile, as if each person is just reading off what the last person wrote. Also, we've found their Technical Advisory Services to be only marginally better than just using Google.

But... we're also ESRI partners and we never hear from those guys...

Fringe Benefits in Training

I really enjoy meeting with our clients face-to-face. I don't get a chance to do that as much as I'd like.

This last week, I took a two-day road trip with our Crystal Reports expert to train with Kern County Environmental Health staff at their office.

The stated purpose of the visit was Crystal Reports training. Since EnvisionConnect uses Crystal Reports (along with SQL Server Reporting Services) to get data out of the system, we really want our clients to know these tools.

The training went really well, but the added benefit, I believe, came from putting two Decade team members in front of twelve Environmental Health and Data Management experts.

Here's what happened:

• We were able to brief the client on two key features of our product:
  • Valid Code Combinations - A feature that prevents users from classifying data in obviously incorrect combinations (e.g., time for a Bereavement Leave allocated to a Complaint Investigation).
  • Surcharging - A financial system feature in which certain facilities are assessed an additional charge (or charges) just for being involved certain programs. This technique is being used by some programs to generate revenue for targeted projects in that program area.
• We collaborated on and validated a plan to re-align the agency's inspection frequency and fees based on risk (and not just for the Food programs either).
• We shared some tips on reducing the total number off inspection reports by collapsing and combining similar reports using a few Crystal tricks & tips.

Then, we learned a few things about what's going on in Kern:

• We learned of the agency's Tobacco Retailer Program in which retailers maintain a permit and are subject to inspection.
• We learned of the agency's effort to publish public service announcements on important issues.

We enjoyed a couple great debates over lunch and... oh yeah... trained their team on using Crystal Reports. Thanks guys!

Who Controls Your Data?

Here at Decade Software Company, we have a strong tradition in providing data management tools (emphasis on tools) for Environmental Health and Regulatory Agencies.

Under this model, our customers own their data and their processes and we impose no barriers to its access.

Impeding Data Access?

We're working with several new clients this summer who are abandoning a competing system in favor of EnvisionConnect. That's the good news.

Getting the historical, permit, and financial detail out of the old system and into EnvisionConnect is the first order of business. We call this process data conversion.

This particular competitor imposes a model in which it is nearly impossible to extract the data from the legacy system.

The vendor prevents native access to the database. Then, they provide only crippled export tools. In short, they impede your access and essentially control your data. That's the bad news.

This is wrong... unethical in my mind.

Embracing Data Access?

All versions of EnvisionConnect encourage you to access to your data, even in its most raw form (assuming you have the security credential to do so). Here's how:

• Microsoft SQL Server 2005 - By using an enterprise-class database on the back-end, you gain access a huge library of Microsoft and third-party tools to extract, catalog, optimize, and mine your data.
• Crystal Reports - EnvisionConnect includes a library of 200 pre-defined Crystal Reports including a variety of permits, invoices, inspection reports, and financial statements. Use one of our canned reports, customize it, or build your own.
• SQL Server Reporting Services - With SQL Server 2005 comes an incredible tool for enterprise and end-user reporting. We're especially high on the end-user reporting tool, Report Builder (PDF).

Even though Report Builder is in its infancy, this tool blows me away with its basic user interface and report subscription model in which reports can be e-mailed or posted online based a schedule you set. It is very flexibility while remaining incredibly simple. If you haven't seen it, be sure to check it out. It's awesome!

A Bug's Life

Decade Software Company makes big investments in testing prior to each release.

However, when a bug is uncovered in EnvisionConnect, I'm often asked by clients and staff to predict when that bug will be fixed.

I'm please to say that bugs are addressed very rapidly and with good predictability.

Below is a countdown of how a long a documented bug can live in EnvisionConnect.

Bugs, Critical Bugs, Normal Releases, and Emergency Fixes

Our developers work in 30-work-day sprints.

All known bugs on the first day of a sprint will be fixed by the end of that sprint.

Therefore, a non-critical bug can live between 30 and 60 days. In the worst case (bug discovered on the first day of a new sprint) it lives 60 work days. In the best case (bug discovered on the day before a new sprint) it lives 30 work days.

Obviously, it’s in our interest to document bugs as soon as possible.

A truly critical defect that impacts data integrity or prevents basic operations may be escalated for an emergency release.

A body of work (e.g., bug fixes) may be released to the user community earlier if the Product Owner (me in this case) prioritizes a mid-sprint release. This is not the norm.

Our normal release schedule is every 30 (business) days.

I Miss My Office

I've really been missing my office this last week and I have just thirty minutes before I have to hit the road.

Last week, my wife was called to jury duty and I stayed at home with our two young children.

This week, I'm traveling for business.

Working from home or on the road is never as satisfying for me as just being at my desk.

Although we have the normal "road warrior" VPN access to everything at the office, I just feel as if all my efforts move at a slightly slower pace than if I were at my desk. I get to e-mail later. I respond to phone calls later. I converse with my team less often. It's like working under water.

To offset my time away during the week, I worked some over the weekend. By contrast, working at my desk after hours accelerates my pace. It's like working with that funny "hurry up" soundtrack going on in my head.

The Good News

The good news is that our team reliably moves forward on our commitments. I'm really grateful to have such a focused set of professionals working with me... whether I'm in the office to say it or not.

An Ounce of Prevention...

I twisted my ankle good last weekend playing tennis with my five-year-old son. We were just hitting balls against the wall at the local high school, Andrew learning how to use his new Spongebob racket, and me showing him a proper forehand.

During one of those, "See how I swing level and always watch the ba.....OUCH!" I stepped on a loose ball and fell to the ground.

OK, bad form on my part allowing loose balls on the court. I know better.

A Systems Perspective?

Later in the day with ice, swelling, and elevation, I started thinking about the pain I was enduring and how it could have been prevented. The answer is obvious... I should have not proceeded with the lesson while the court was littered with loose tennis balls. I should have checked then stopped to clean up the balls before proceeding. User error.... and I had all the tools to prevent it.

EnvisionConnect Financial Wizards?

EnvisionConnect's billing and aging wizards has a check-before-you-proceed page for just this reason.

The system shows you the anticipated outcome of the wizard before the wizard posts the results. For example, when billing for annual permits or hourly services, the system displays each of all the charges it thinks should be posted to the financial system based on your configuration and how you used the wizard.

This wizard page is designed to encourage the user to stop, print reports, and validate the anticip0ated outcome before proceeding with the "Post and Continue" button.

Taking these few extra moments certainly will prevent the pain of an incorrect billing run.

Coffee-Gate?

I don't know where this blog post is going to go, but this local controversy got me giggling last Sunday... and there's even an Environmental Health angle.

Here in Fresno, there is a local politician (a current City Councilman and candidate for County Board of Supervisors) who routinely sets up a card table, a sign, and a carafe of coffee on street corners of his district. I often see this gentleman waving at me in my car during my commute.

The idea, as I understand it, is that he uses this time to mingle with the people. That's cool and I'm sure he's a good guy... nice to his dog... calls his mother... the whole bit.

The Controversy

Recently our councilman got headlines by proposing a city ordinance that would prevent panhandling from the traffic medians. Well, it took about minute for a local columnist to start picking this plan apart. This by itself is kind of funny... a councilman who spends his mornings on street corners arguing that panhandling from the medians is unsafe.

The Punch Line

It turns out that the good councilman needs a permit from the Fresno County Health Department to dispense coffee to the public. He's been "found out" and is now being encouraged to adhere to to our local food code.

Don't you think that's funny?