Here's the situation. I have a custom usage report based on the Resource Analyzer SMQUERY table along with two uploaded spreadsheets (one for user_id to user_name conversion, and one for fex_name to report_name conversion).

This report was working just a couple weeks ago. And it works fine on the test side.

When I create basically the same report without the spreadsheets (only using SMQUERY), I can see all the data.

The problem is when I run this report, it only returns rows for my user_id. I have a feeling it has something to do with the joins and/or spreadsheets, but I have double checked that they are exactly the same was what is in test.

Here is my code. Can anyone see a problem with it?

TABLE FILE WF_USER_TABLE
PRINT
USER_ID
USER_NAME
ROLE
GROUP
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS WF_USER FORMAT FOCUS INDEX 'USER_ID'
ON TABLE SET HTMLCSS ON
END
TABLE FILE FEXNAMEINFO
PRINT
FEX_NAME
REPORT_NAME
FED_INFO
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS WF_FEXINFO FORMAT FOCUS INDEX 'FEX_NAME'
ON TABLE SET HTMLCSS ON
END
JOIN
WF_USER.SEG01.USER_ID IN WF_USER TO MULTIPLE SMQUERY.SMQUERY.SMUSERID
IN SMQUERY AS J0
END
JOIN
SMQUERY.SMQUERY.SMRPCNAME IN WF_USER TO UNIQUE WF_FEXINFO.SEG01.FEX_NAME
IN WF_FEXINFO AS J1
END
TABLE FILE WF_USER
PRINT
USER_ID
SMRPCNAME
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
END

This message has been edited. Last edited by: MO Admin,

Without knowing the layout and contents of your files, I can't see a problem with the code. You might try running it a piece at a time and see if you're getting expected results. Try commenting out the hold in the first TABLE request and throw a -EXIT after the END to see if you're getting all the user_id's or just your own. My first guess is that you're not getting what you expected in one of the first two.

If the code didn't change and it's "just stopped working" then either the data in the tables has changed, or you're not pointing to the data you think you are. Check out the WHENCE command to figure out that part.

I am getting the expected results from the first two queries (using the method you suggested), but when I joing all three tables together, I get limited results (of just my user_id).

What kind of database is the Resource Analyzer data stored in? Is there security on the tables?

Check for user Id's in all the tables individually...

The RA repository is a FOCUS database. No security on it.

I have run reports on each of the three tables individually and get what I expect (all user id's), but not when they are joined.

But like I said, they are joined the same way in test and it works just fine.

I can't explain why it works in one environment and not another. Did you check to see if the Masters are identical?

It appears you're using the GUI to develop the reports, though this is how I would code the first two TABLE commands:

TABLE FILE WF_USER_TABLE
SUM
USER_NAME
ROLE
GROUP

BY USER_ID

ON TABLE HOLD AS WF_USER FORMAT FOCUS INDEX USER_ID
END
-RUN

TABLE FILE FEXNAMEINFO
SUM
REPORT_NAME
FED_INFO

BY FEX_NAME

ON TABLE HOLD AS WF_FEXINFO FORMAT FOCUS INDEX FEX_NAME
END
-RUN

So next step is the third TABLE request. Without doing any joins, read the SMQUERY table to see what SMUSERID values you are getting. Maybe throw in a WHERE SMUSERID NE 'youruserid'; and see if you get any records at all. If not, there's got to be some sort of security set up on that table.

That did it for me.

Turns out I was trying to match an all lower case user_id in my spreadsheet to the all uppercass SMUSERID in SMQUERY. For some reason there are lower-case user id's for me in SMQUERY (I believe from ReportCaster), so those were matching and nothing else was.

Changed my spreadsheet to be all upper-case and I now get the results I expect.

Thanks for everyone's help in debugging this. I really appreciate it!

So was your spreadhsheet different in your test environment or was your SMQUERY data different in prod.? Just wondering why it suddenly stopped working.

Sorry, should have explained more...

I had recently generated a new spreadsheet of users (from the reporting server's mrextract utility) to pull in new users. This is what caused the change.

I tried deleting the master file from Prod and copying it from Test, but I suppose it was still pointing to the same data text file on the server.

To resolve, I had to copy the column into Word, chagne case to UPPER, then copy back into Excel and upload the new master file.

Can I put in code to the join or something to force uppercase so I don't have to manually do the conversion each time a new spreadsheet is generated?

Thanks,

Some databases like DB2 have a setting to remove case-sensitivity, but i don't know how that works with WF. There is a function called UPCASE that will convert your string to uppercase, but in the case of your JOIN, that would then require a DEFINE-based JOIN. Not as efficient, but it works.