More things in heaven and earth

I can’t stop thinking about Zachy.  It’s kind of horrible, it’s dispiriting, and it’s exhausting, frankly, because I’m not sleeping well.

But life goes on, and people ask questions.  This one came in two parts.

Part Un: How do I use a backend stored procedure with a VFP report?

I really couldn’t remember how, but I worked it out. I’m going to show you how I decided to do it.  Not the only way. I’m sure it’s possible to do with a cursor adaptor but I hates ’em.  And my way seems pretty simple.

The backend I’ll be using here is a SQL Server with an ODBC connection, although I don’t suppose it matters what the backend is, except potentially for the exact execution syntax.

1. (Duh) Write the stored procedure.

The only significant requirement for the stored procedure to fit reporting needs, I imagine, is that it has one or more parameters — otherwise what’s the point?  What we’re trying to do is allow the user to make some choices and then execute the procedure with those choices, right?  The users are going to make those choices in a VFP application UI, presumably.

To model this scenario, I wrote a very simple stored procedure, which you could adapt to any datasource.  It looks like this:

create procedure p_test (@TopN as int)
as
begin
   select top (@TopN) * from Tests.dbo.DateDimension ;
end
go

… the only significance of this particular test procedure, if you’re trying this exercise at home, is that it would work with any database table, and very easily allows you to confirm that the parameter instructions have been respected when you view the resulting report.

2. Test the stored procedure in VFP, and get a little added benefit from your test.

Start by stipulating that one variable, or potentially one field in a table if you prefer, for each parameter is going to be in scope at runtime and available to your code. (In your UI, whether it’s a field or a variable, your user choices for each parameter will be bound to this item.)

In our case, there’s just one such variable, for the single parameter. For your test you can just set it up in the command window if you like, which is what I did, all the way through the code you see in this section:

m.piLimit = 15


You’ll also need your connection to the external data source:

m.lcODBCName = "ODBC-Tests"
m.lhConnection = SQLCONNECT(m.lcODBCName)

And then you need to execute the procedure with this connection/handle.

Honestly, folks, it’s been so long since I’ve done this that I forgot the syntax and had to look it up in one of C’s articles. So, bear with me. I’m pretty sure there is a syntax that I’ve forgotten that lets you use ? or some other argument for the parameters, rather than writing out the SQL as a string, but this is what I did:

m.lcStatement = ;
   "exec Tests.dbo.p_test @TopN = " + ;
   CAST(m.piLimit as varchar(10)) + ";"
*&* contents of m.lcStatement is now:
*&* exec Tests.dbo.p_tests @TopN = 15 ;

? SQLEXEC(m.lhConnection, m.lcStatement,"myCursor")

*&* verify result:
SELECT * FROM myCursor

I said there would be some added benefit, and here it is: You now have the structure of the result, and you can put it into a table, if you like, which can be empty:

COPY TO MyCursorTable FOR 1= 2


3. Report Design Time!

Now you have a perfectly good table that presumably has the structure of some denormalized rows you want for your report, which you can use throughout your report design process. Just open this table with the same alias that you will give to your cursor when you run the report, whatever that happens to be.

If you like, after designing the report, you can delete this table. Or you can keep it around in a special directory, since it and all tables like this will be tiny. Either way, no steenkin’ cursor adapters.

What if you’re not totally denormalizing your report source data? You can certainly run multiple stored procedures and have multiple cursors in scope.

It is perfectly possible for a stored procedure to return more than one table-shaped result — so you might have only one procedure call, and multiple cursors. But what does VFP do with those results? If VFP doesn’t handle this scenario well natively (and I honestly do not remember), and if you need help to use a multi-result-set stored procedure on the VFP side, I’ll have to think harder. I suspect my answer, although maybe not somebody else’s, would require XML. I’m not going to test this or worry about it unless somebody needs me to.

If you do have multiple cursors, no matter how retrieved, don’t forget that you can index them, to optimize your report relations. You’ll want to clean these temporary index files up at the end of your process, of course, at the same processing step in which you eventually close your connection handle.

4. Production strategy, first iteration

So, now your reporting PRG might look something like this:

m.piLimit =  … && the variables that you need for your statement are in scope
m.lcODBCName = … && comes from somewhere in your app
m.lhConnection =     SQLCONNECT(lcODBCName)

IF m.lhConnection > 0

    *&* setup:
    m.lcStatement =  … && construct your statement
    ? SQLEXEC(m.lhConnection,m.lcStatement, "myCursor")
    *&* more cursors created here if needed, 
    *&* plus indexing, set relations, etc

    REPORT FORM …  && whatever

    *&* cleanup: 
    USE IN mycursor  && and more as needed
    *&* delete index files if you need to, here … 
   =SQLDisconnect( m.lhConnection )

ENDIF 
     

At this point, seasoned VFP developers (who really didn’t need my help with all of this and are usually smarter than me about generalizing this sort of thing) are thinking about how they might load their existing reporting procedure with intelligence about doing it without constructing the statement in-line, using a variable to represent the cursor name, etc. Why should their generic reporting procedure have to know what parameters and what procedures go with each reporting call? More etc.

5. Production strategy, second iteration

There’s a good reason to bind the creation of the data environment tightly into the report run: it’s because each report needs its own data setup. That’s why VFP (and FoxPro before it) allows you to do this, using a Data Environment.

So far, it hasn’t mattered how you set REPORTBEHAVIOR. What we’ve done will work whether you’re going for 80 or 90. Even the “greenbar” effect in my sample VFP report, above, is just a Print When with a colored rectangle.

I even think that what I’m about to suggest could be done using the DataEnvironment events, even with REPORTBEHAVIOR set to 80, although I can’t quite remember how.

Suppose, though, we add a ReportListener class to this mix, or (better) a data-friendly Decorator class for ReportListeners.

You could subclass, either way, for the specific data setup and cleanup that you needed for one or more reports, and attach your subclass to your report run. I haven’t sat down and done this, but it’s straightforward ReportListener practice. I imagine the setup would be very careful with DataSession handling, and always end with a SELECT of the appropriate driver workarea/cursor for the report.

Wouldn’t that be nice?

6. Now about those parameters and user choices…

I keep saying that you’ve got variables, or field contents in a parameter-driving table, in scope. Presumably you’ll want this information (the report parameters) to be gathered from the user in a VFP form.

Let’s bring the process along a little further:

Put together what you have read so far, with what C explains in his article about Report Preview techniques,
and you can see that there is definitely a way to put the controls requesting the user to set values directly on the preview form, and then pressing a button to view the report, simply by building a custom preview form. Along with C’s article, of course you have his shipping Preview code in XSOURCE.ZIP, and you can adapt it.

Now let’s go further still:

7. Suppose you have 700 reports in your application.

Analyze your 700 reports and you will probably find out that 95% of them can be covered by a suite of parameters that looks something like this:

• – two integers
• – maybe two decimals
• – two dates
• – two strings, free form, or maybe only one
• – two dropdowns

I’m saying that, although not every report needs that many parameters, 95% of your reports don’t need more than the set I’ve just listed.

This analysis process is very similar to what I described, a long time ago, in a post about generalizing date parameters.

It remains a significant part of my approach to reporting, of any type, to this day. I recently built something called a “Survey Panel Generator” for Dominican, in fact, that dynamically builds SQL out of a data context (employees, students, or both), a set of time factors, like the ones in the post about generalizing date parameters. It adds the ability to select a set of desired output columns, and an extensive set of filters.
Typically, I’ll do something like this as a multi-step wizard and guide the user through the choices a bit; for example, in this case, their data context governs what output columns and filters the user can select, since they’re not all suitable for both employees and students.

What can you do with your analysis of the parameters?

Well, you can generalize those in-scope report-deriving variables (or fields in a table) to a set of 10 standard variables. You can then apply whichever ones you happen to need, as the arguments to each stored procedure you’ll use to get data for a report. You can have your custom report preview form set up the common superset of 10 possible parameters. For my example test procedure, the only difference is that I would be using some variable name such as m.piStandardIntegerParam1, rather than m.piLimit, to set up my procedure call.

Now, suppose you build a table of report names and flag columns that represent which parameters you need
FOR EACH of your reports. You’re on your way to data-driving the whole system.

Some of this is non trivial — for example how is your generic code going to fill the two dropdown options when you need them? How will you keep track of what value type they will return? (Hint: You might think about making them all strings underneath, considering that you’re going to put them together in a string to execute in SQLEXECUTE anyway. The two that are integers have to be validated as integers, the two that are dates have to be validated as dates.)

Well, I suppose each of the flag columns in the table can have an associated “code” column that says how, and is dynamically EVAL’d and executed at runtime. Non-trivial, but this is the kind of thing that VFP is really good at.

What about the captions or prompts for each parameter that a report needs? In my example, m.piStandardIntegerParam1 needs a caption that says Number of Rows, while the next report that uses the same integer parameter will need a caption that says Employee ID, right? Again, each flag column for each parameter can have an associated column with the needed caption text. What about whether you want to fill the value yourself, behind the scenes, or allow the user to do it? An associated “hide” column.

At this point you might stop and say “Lisa, we need to normalize this rather than put it in one table!” And C would definitely agree with you. Heck, some days I would agree with you, too.
But, basically, I’m just doing what the FRX format itself does, and all the other FoxPro system table formats do, and it doesn’t really matter very much here that we’re doing it in one, wide, table.
Why not? keep reading.

The first choice the user makes on your custom preview form would of course be the report to run. I’d say this would look nice as a drop down. (Obviously, the selection text would be in your metadata table, too.) Based on this selection, and all the other information in the table, your preview form can hide and show, and if necessary dynamically move, the parameters needed for each report the user selects. The user presses a “Run” or “View” or “Print” button when satisfied and these values are available to send to the associated stored procedure.

As described so far, this technique is not infinitely extensible; you have a set of controls which your form knows about, and which are bound to the appropriate variables that the code invoking your stored procedure expects to see. You can’t just add more such controls and associated variables any time you want; you have to think about how to display them, telling your stored procedures about the new variables, etc. That’s why it’s not really necessary to normalize the metadata. You’ve got your 10 possible variables and associated controls, each of which needs some columns that represent its attributes, and you’re not likely to add more variables without tons of thought and re-jiggering your preview form design.

Well, maybe. There’s always more room for exciting display of VFP prowess. Yes, I suppose you could add controls dynamically to the form, include a sequence value in the table, and present the parameters for each report in a kind of grid you set up dynamically, but it wouldn’t look great and I personally wouldn’t bother.

There is a lot more stuff I wouldn’t bother with.

IF you think about it, all we’ve really done here is painfully recreate the SSRS ReportViewer’s parameter panel in a VFP form.

We’ve described exactly what the SSRS parameter panel is doing, under the covers. And BTW, yes it is completely dynamic, you are not limited to 10 variables or anything, and as a result it doesn’t look fabulous. But it’s no work at all, for us, that’s the difference!

Would all this preview form and metadata work really be worth it, to keep the reports in FoxPro, especially when the reports are being driven by SQL stored procedures, and all the data originates in a backend datasource, in the first place?

Maybe not.

But how would you integrate SSRS reports, instead, into your VFP application?

Part Deux: How would I integrate SSRS reports in a VFP application?

This was the second part of the question I was asked. And, as you can see, I don’t think the the two parts are unrelated.

As it turned out, this was very easy to do. You only need two controls on your form:

1. a combobox or other method for the user to select reports. (This is the same as you were going to have to do on the custom preview form, right?)
   In my simple example, I just have two reports and I gave the combobox the values Report1 and Report2 as a datasource, but you would data-drive this, adding new rows to a table for each report you set up as an RDL instead of an FRX.
2. an olecontrol to serve as a “home” for the report. This is a Web Browser instance, and on my system I had to browse to find it (it’s in C:\Windows\SysWOW64\ieframe.dll).

The combobox gets some very simple code in its Interactive Change event. In my simple demo example, with two reports and not data-driven, it looks like this:

PROCEDURE InteractiveChange
 IF this.Value = "Report1"
   thisform.olecontrol1.Navigate( ;
    "http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fPoseidon%2fEventNotification&rs:Command=Render")
 ELSE
   thisform.olecontrol1.Navigate( ;
    "http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fPoseidon%2fRawMeasurementGraph&rs:Command=Render")
 ENDIF
ENDPROC

Notice that the URLs in the calls to Navigate() are addressing ReportServer — not the Report Manager — and also that I’ve tacked on a parameter to tell the report to render, using the SSRS built-in “URL Access” API. (That’s pretty much my favorite thing in the world.) If I needed to — perhaps on a per-user basis — I could pass more parameters in the URL that were specific to a report.

The form gets some equally simple code in the Resize event, to handle the cosmetics, so that the two controls use all available real estate on the form’s surface:

PROCEDURE Resize
   this.combo1.Width = this.width
   this.olecontrol1.Width = this.width
   this.olecontrol1.Height = this.height - this.olecontrol1.top
ENDPROC

Finally, the form gets a Show event that leverages both the above methods, to “kick start” the form on open:

PROCEDURE Show
LPARAMETERS nStyle

   *&* this.BorderStyle= 3
   this.Resize()


   *&* don't do the following
   *&* if you don't want the user
   *&* to have a report open until
   *&* they make a selection:
   this.combo1.Value = "Report1"
   this.combo1.InteractiveChange()


ENDPROC

And that’s it! (No code in the olecontrol at all.)

Suddenly, your reports have all the dynamic report capabilities — including parameters. In the example Report1 and Report2 I used, notice (screenshot below, click to open in a separate window), that one report happens to have a boolean parameter and a freeform text box parameter, while the other has two datetime parameters and two dropdowns, (one of which has been dynamically filled out, because parameters can cascade). You also get drilldown, you have pretty good export capabilities out-of-the-box, etc. Even better, you’re not even moving any data from the server to the client side.

And that’s all she wrote.

I think Hamlet and Horatio would be happy with this hybrid approach. How about you?