The new Crosstabs feature is very nice, but it appears that ‘out of the box’ it lacks one option Pan6 had included: outline crosstabs, those which on Pan6’s cross tab preview showed two, or more, fields along the side. Neither the documentation nor the video mention mention them and when I tried first summarizing a database then choosing cross tabs the summary records weren’t shown.
However, it seems to me that outline crosstabs matching Pan6’s could be produced with the new tools, made into a separate composite crosstab database. They could be assembled programmatically from separate crosstab pieces using crosstab queries to parse a summarized database. Set up the basic crosstab and summarize the original database. Parsing how it’s summarized, a text array of Crosstab Queries would be built then used to generate a series of individual databases as crosstab pieces, which would be combined into a composite crosstab database. Extra fields would be added along the way, providing enough information to summarize the composite crosstab like the original, to allow selecting the original data and to allow refreshing the composite. However the later probably would take as long as its original creation, which might be awhile. They would have all the data and subtotals Pan6 crosstabs provided. They couldn’t wouldn’t be able to use PanX’s new, cool, charting features, but could export data for Excel, etc. to chart.
That’s correct, Panorama X does not have that ability. Still, I think the new crosstab feature is much nicer than the old crosstab feature (and I use crosstabs a lot).
I would suggest an alternative technique. Rather than try to cram all this into a single table, I would embed either two crosstab tables or a crosstab table and a summary table into a form. One would be the master, and the other would recalculate based on whatever row was clicked in the master table. I’ve been meaning to pull together a demo of this idea … ok, that took about 10 minutes, and it came out even better than I hoped! Here’s this idea in action. As I click each row in the top crosstab, the bottom crosstab updates to show the corresponding detail. Pretty cool, eh?
(If this movie looks a bit fuzzy, click to expand it and it will be sharp.)
Both of these crosstabs were created using the summary workshop, then pasted into a single form. I replaced the standard code in the top crosstab with:
fileglobal vendorSelectFormula
local targetDatabase
let clickedRow = info("matrixrow")
crosstabselectoriginalformula crosstabInfo, clickedRow, 1, vendorSelectFormula, targetDatabase
showvariables vendorSelectFormula
Then in the bottom crosstab, the standard formula was replaced with this:
The key is the second line, the Query option. This uses the vendorSelectFormula calculated by the click code in the top crosstab. As each row on top is clicked, the formula changes. (I used the catcherror( function to provide a default before anything is clicked, but you could leave that out, or make sure the vendorSelectFormula is set up in your .Initialize procedure.
That’s all it took to set up this two level crosstab! Five lines of code and a slight change to a formula.
Of course, you could also set up a chart corresponding to the top crosstab, or the bottom one, or both. Or you could use a summary table for one or both of these tables. You could also set up a text list or matrix object to display the raw data associated with the clicked row right inside this form window, without even needing the data sheet.
One last point, as an aside. The Summary Workshop and Crosstab Workshop are just Panorama databases with a Panorama form. There were no secret tools used to create this. Anything done in those workshops can also be done in your forms. For example, you could add a pop-up menu to my multi-level crosstab to select months, quarters or years. For now I’ll leave that as an exercise for the reader.
That and the vision to conceive of the simpler solution… and of the program to make it possible! It’s easy to conceive of big, clunky, do it all solutions; although not so easy to make them work. Elegant solutions may be simple in implementation, but are often not so simple in conception. I now see how to tweak this for whatever variations I may want.
However I’ve spotted and localized one glich. Both Crosstab and Summary Tables don’t work with all my fields listed in the Group popups. The problem turns out to be my Fieldnames. The database I tried was an old (Panorama II originally!) one and I’ve not updated its fieldnames to take advantage of the Field Title property. Multiple fieldnames produced an assortment of errors or problems. The basic Summary form allowed display of error messages resulting from its group selection, unlike the side group selection on the Crosstab so I’m reporting on that. Crosstabs gave similar lack of success. I’ve quoted the fieldnames below for clarity. None were actually quoted in the database. I could change my fieldnames to purely alphabetic ones and display the old ones via Titles, but suspect you’d rather tweak your code to accomodate broader naming.
Expression contains an operand when an operator was expected error:
“Chart#”
“10s”
“T#”
“Medi#”
Field or Variable does not exist error:
“f/u?”
“√chart”
“∑ Mine”
Missing operand error:
“-”
Expression parsing error:
“#”
Their 2nd (numerical) character is falsely given as the value for every field,
but they work ok when chosen for Count in Summary Table & Chart
“∑1”
“∑2”
“∑3”
“∑4”
“∑5”
“∑6”
“∑7”
“∑8”
although named as line item fields Mine weren’t used that way. You’ll want to verify line item field names work with summaries and cross tabs.
None of my problem filenames were ‘legal Panorama token;’ chevronquoted( added chevrons to all of them. Plugging them into crosstab( with the added chevrons worked.
However, I’d had date fields with spaces in their filenames, thus not ‘legal Panorama tokens,’ which had worked as was. Manually inserting chevrons around them within crosstab( broke them. Dumping the crosstabInfo dictionary showed chevrons were already being added to date filenames, needed or not, within the groupdatepattern( function, explaining their disparate behavior. So only text and numeric fields now need chevronquoted( added. I now understand how to work around this until you get around to a better fix. I haven’t checked, but presume binary fields are irrelevant here.
I haven’t had a chance to look at this yet but what you are saying makes sense. Date fields need special handling because they have to be embedded in the groupdatepattern( function. So it sounds like the code that does that correctly adds the chevrons. For other types of fields, they are just plopped into the formula, so no chevrons. Most likely this will be a trivial fix. And yes, binary fields are irrelevant.
