Has anyone had to do something like this?

I created a master file with a join of two relational Oracle tables. (Otherwise known as Cl-uster Joins) They join based on multiple fields. When this master file is used to create a report using the Report Assistant or Painter, I do not want certain fields available to be used in the report, but unfortunately some of those fields are part of the join condition.
For instance, I join Table A and Table B together. Table B is a lookup table. I only want the grouping name from Table B to be available to report on, not the three fields used in the join. When I remove those fields from the master file, and try to create a report from that master file I get an error message.

P.S. The l-ust in the middle of the word gets censored with "*" so I had to use a '-' dash to get around this.

Hi, Melinda,

I'm not 100% sure but I *think* you can edit the MFD and change the fieldname to blanks - FIELDNAME= , - just make sure the ALIAS still matches the actual column name. And make sure the JOIN syntax or DKU definition in the MFD uses the ALIAS, not the FIELDNAME.

There are others who know this much better than I, but they're all probably at Summit right now. So try the blank fieldname and see if it works.

Also, I fixed it so you can say "cluster" now. Thanks for letting us know about that. The reserved word list was a little too strict.

Regards,

Chris

Hi Chris,

Thanks for fixing the censored word cluster on the website.

I tried your suggestion for blanking out the FIELDNAME= in the MFD. It didn't give an error message but when I create a report in Dev Studio based off this MFD, it still shows those fields which I blanked out the FIELDNAME, like the blanking out didn't change anything.

I have a ticket with Tech Support about this and so far they can't find anything on this issue yet. She was going to check with the Product Division. She stated it might be a New Feature request in the end. With everyone at the Summit this week, it might be sometime before I hear back.

Thanks for your help.

Hi, Again, Melinda,

If you've opened a case with Tech Support then I'm sure they'll take care of you, but I did want to make sure I was clear in my suggestion. I was not suggesting you blank the text "FIELDNAME=" out of the MFD, but blank out the actual field name itself.

In other words:
FIELDNAME=FRED, ALIAS=F1, USAGE=A9, ACTUAL=A9,$

would become:
FIELDNAME=, ALIAS=F1, USAGE=A9, ACTUAL=A9,$

But you would need to use the ALIAS (not the fieldname) in the JOIN or DKU definition.

If the fieldname is blank, then I can't see how it could show up in Report Assistant (unless of course you have the same field name in another table within the JOIN, or the JOINed fieldname is the same in both tables).

Let me know if that's what you tried.

Regards,

-Chris

Chris,

Thanks for the clarification. That is exactly what I tried. In the Dev Studio, the field still showed up in the field list. Weird.

Tech support got back to me and they checked with the Product Division and stated that this would need to be a New Feature Request, as there isn't a way to do this currently. I went ahead and asked them to create a New Feature Request.

I can always go back to the Oracle database level and create a view in Oracle in which I can exclude those fields. Then I can create a MFD on the Oracle View and it should solve the problem. I was kind of hoping I wouldn't have to go to the database level to do this.

Thanks so much for your help Chris!!

Hi, Melinda,

Well the only other thing I can think of is that the old MFD may have been loaded into memory which is why the old field list was still showing up.

In a FOCUS session, for efficiency purposes, a master file description remains loaded into memory for the duration of a session unless you force a new version into memory. One command that forces a read of the MFD from disk and into memory is CHECK FILE.

So, after you edit the .MAS file, if you can execute a CHECK FILE FILENAME (where FILENAME is the name of the .MAS file but without the .MAS extension), THEN load Report Painter or Report Assistant then you may see different results. Or you could do the edit of the MFD before starting your Dev Studio session at all, then start the session, and see if the results are different.

The other option would be to create an alternate copy of the .MAS file and .ACX file using a different filename and make the edits in this new MFD. For example, copy FRED.MAS and FRED.ACX to FRED2.MAS and FRED2.ACX. Edit FRED2.MAS to remove the fieldnames. Also, make sure there is no TITLE associated with the particular fieldname in the master file. A TITLE is used to give the field a different display name in the painter and in the actual report. If there is a title, then delete it - this will have no negative effect on reading the file.

Now load this MFD in the painter and see what shows up. If the fieldnames still show up in the list, then I'll eat my hat. Don't worry - my hat is made of cheese.

-Chris

Hi Chris!

Thanks for the suggestions. But I think you will have to eat your hat. What kind of cheese is it?

I tried your suggestions. I tried doing the CHECK FILE FILENAME command in the Console window in Dev Studio to make sure it has the right version of the MFD in memory. It still comes up with the fields that I set the FIELDNAME=, in the Object Inspector within Painter.

I closed my session, renamed the .mas and .acx files to mel.mas and mel.acx and made sure that the FIELDNAME=, were blank. I also edited the new MFD so that the fields I don't want displayed have no TITLE= or DESCRIPTION= as well. Then I brought up Dev Studio again, did a check file on the new MFD name. Then brought up the painter on that MFD and the fields are still listed in the Object Inspector window. I also brought up the RA and looked at the fields there and they are there as well.

I brought up the new renamed version of the MFD in the Master File Editor within Dev Studio. It is strange but in the tree of the segments and fields listed, it shows some blue "-" marks with no field name after them, but the "-" mark looks like a placeholder for a field in the MFD.

Would you like me to send you my MFD?

Thanks again for all your help.

Hi, Melinda,

Actually, the last thing you noted (with the renamed MAS and ACX files) is exactly what I would expect - the little blue dashes that are placeholders for the blank fieldnames.

I thought that was more or less what you were trying to accomplish - not having the FIELDNAMES themselves show up on the selection list in the painter, so that people would not select them for a report. It's kludgey, but it's probably about the best you'll be able to do without getting that new feature request.

There are a couple of other options. With relational tables, you can simply delete fields from the master file that are not essential for the JOIN if you do not want people to report against them (you can't do this with hierarchical DBs, FOCUS files, or flat files, because they're positional, but you can with relational tables). If the fields are used in the JOIN though, then you're pretty much stuck with them.

Your best bet would probably be to create a different view of the data or just create a data mart or data warehouse extract file that only includes the fields that you want and use this as your reporting table for these reports. If you only need daily extracts then you could schedule a procedure to run daily to do the extract. If you need live up-to-the-minute data, then you could write the extract procedure into the application itself.

Depending on how much data is involved, this might be a viable option.

Regards,

Chris

Hi Chris,

What I was trying to accomplish was to not have the fields show up at all in the Painter even though they are used as part of the join criteria. The problem is that they do show up as a placeholder in the Master File Editor which is what we expected, but in the Painter they still show up like any other field with the ALIAS name as both the FIELDNAME and ALIAS even though in the MFD the FIELDNAME is blank.

This master file does go against a relational table and I have already taken out the fields that we do not want the user to see that are not part of the join. Unfortunately some of the fields I do not want them to see are part of the join condition.

It sounds like at this point this is a new feature request. To get around this, I will just create an Oracle view within the Oracle database and include in the view the fields I want the users to see. Then I can create a MFD off of that Oracle View.

Thanks again for your assistance!!

Gotcha. And by the way, it was limburger. Yummmmm...

-CB