[James Jewell]

Hi Tim –

I am able to make modifications to the XSL template and the modified XSL is working, but how do I get a list of all the fields that one of the STD Stored Procedure (SP) returns so I know what else I can add to the Template display? (ex: STD_0024_NEW_WO)

Also – if the SP does not provide a WO field that you need – how do we retrieve that extra info for a custom template enhanced view?

Thank you for any insight you can provide.

-Jim

[James Jewell]

Hi Mike,

Try a double SELECT with a CASE Statement.

I tried to simulate your situation by returning more than (1) record and if not equal to (1) then provide a “My_Error_Check” result.

Give it a try… I hope the method helps.

-Jim

ps: I have run into the situation of (2) records being returned when the result should be a single record – so by pre-testing for the “multi-record return” scenario using a double SELECT and CASE – it gives you control and can avoid the empty report look.

ps: Another approach would be to return the THEN result with your SELECT Statement (single) result and the ELSE with a text message (or int value of for example -1) that you can then trigger off for display control within Crystal via a Section “Suppress”. I hope this makes sense.

———-

— SAMPLE CODE: Double SELECT with a CASE statement.

SELECT CASE WHEN TT = 1 THEN ‘GOOD’ ELSE ‘ERROR – TOO MANY RECS RETURNED’ END AS MY_ERROR_CHECK

FROM

(

SELECT COUNT(*) AS TT FROM PARTS_MASTER

)

[James Jewell]

Thanks Mike, Tim, Nadim ! (appreciate your posts)

[James Jewell]

Thanks Tim!

For a pull for a report, what would a sample SQL statement look like?

I have tried the following, but I get an error in Interactive SQL when I test it.

SELECT *

FROM RPT_GL_PKG.GET_BALANCE_SHEET_BY_GROUPS (1, ’01-APR-2021′, ’30-APR-2021′, 228, ’01-MAY-2021′, ’31-MAY-2021′, 229)

Appreciate your help.

-Jim

[James Jewell]

Great insight!

Thanks Mike & Jake for the code and technique.

What might be helpful – as a future enhancement to Quantum – would be a PRINT_LOG table – similar to the EMGR_LOG.

Another helpful table might be a CRI_STATUS table – that contains a “current status” (ex: Called, Rendering, Spooling, etc), “cri_auto_key”, and “sysur_auto_key”, and “timestamp”. [similar to a Print Spool Log]

-Jim

[James Jewell]

> so you need to select the report from the list in the first query, right ?

Hi Mike –

Correct.

The only way I could get it to work was by doing the 2-step approach mentioned above.

However… if there were a trigger event (log file) that captured the moment the report was opened, then we could skip the SQL driven parameter altogether (and go with the “hands free” approach) as we would have a way to determine “which” of the User’s CR reports (from their CR Security Approved List) was the one being rendered.

I am familiar with how to write SQL trigger events – is there a way we can setup a trigger event within Quantum? The trigger would simply be an “on click” event then add a record to a UD (side) table. Then we could pull the “Top” record (DESC) from that UD table once the CR report was called.

Does Quantum allow for a UD (User Defined) table? And does Quantum allow for UD triggers / stored procedures?

-Jim

[James Jewell]

Hi Mike –

Outstanding!!

Thank you so much for that code. I tried a dozen angles, but didn’t notice that table. That was exactly what I was looking for. (thank you)

Separate note…

I was thinking about your CRI_Auto_Key retrieval question.

It might be a (2) step approach, but here’s an idea I came up with this morning. I did a quick test – and it seems to work.

Step # 1 – create a parameter in your report for the following SQL.

— This will retrieve a list of CR reports that the Current User has Security rights to. (pick the one from the parameter drop down – using the CRI_AUTO_KEY as the “value” and the NAME field as the “description”).

SELECT *

FROM CRYSTAL_USERS CRU

LEFT OUTER JOIN CR_ITEM CRI ON CRI.CRI_AUTO_KEY = CRU.CRI_AUTO_KEY

WHERE CRU.SYSUR_AUTO_KEY = (SELECT SPS.SYSUR FROM SPS_SYSUR SPS)

ORDER BY CRU.CRI_AUTO_KEY DESC

Step # 2 – create a subroutine in your report for the following SQL.

— This will retrieve a list of THOSE OTHER USERS who have been added to the Security Setting for the report (CRI_AUTO_KEY) that you selected in Step # 1.

SELECT *

FROM CRYSTAL_USERS CRU

LEFT OUTER JOIN CR_ITEM CRI ON CRI.CRI_AUTO_KEY = CRU.CRI_AUTO_KEY

LEFT OUTER JOIN SYS_USERS USR ON USR.SYSUR_AUTO_KEY = CRU.SYSUR_AUTO_KEY

WHERE CRU.CRI_AUTO_KEY = {?PARAMETER_CRI_AK}

I hope the above code (and approach) is helpful.

Thanks again for providing the Current User code. (much appreciated)

-Jim

[James Jewell]

>> I can get the sysur info for currently logged in user

Hi Mike – Mike Carey

Like you – I am trying to back trace running a report to the CRI_Auto_Key – and would like to add the AK as a footnote to the report (for future reference – almost like a Form # reference).

Also – I have a need to capture the Sysur who runs the report, so that I can add the UserName next to Date/Time of print.

Would you mind sharing how you captured the sysur info for currently logged in user?

I know how to pull the list of DB Session Users, but I’d like to know who (specifically) ran the report.

Appreciate any insight you can provide.

Thanks,

-Jim

[James Jewell]

Hi Henrik –

Good morning.

Thank you for the quick response to the (6) test questions I asked earlier.

The (6) test results were helpful – and directly point to a user credential or profile issue and not a workstation issue.

I have (8) more test scenarios for you to test (if you don’t mind). The following tests will help to isolate if it’s “across the board” a data issue for User #1 (the problem account) or is a “specific retrieval issue” (table vs view) dynamic.

The goal with the following (8) tests is to identify the scope of which type of retrieval is the hiccup.

A couple more questions…

7) Using User # 1 (the problem account) – please run the following SQL Statement in Interactive SQL and indicate if you retrieve any records (or not).

SELECT *

FROM VIEW_SPR_AP_AGING

— Does this view return records? YES/NO

8) Using User # 1 (the problem account) – please run the following SQL Statement in Interactive SQL and indicate if you retrieve any records (or not).

SELECT *

FROM VIEW_AP_TRANS

— Does this view return records? YES/NO

9) Using User # 1 (the problem account) – please run the following SQL Statement in Interactive SQL and indicate if you retrieve any records (or not).

SELECT *

FROM SYS_COMPANIES

— Does this table return records? YES/NO

10) Using User # 1 (the problem account) – please run the following SQL Statement in Interactive SQL and indicate if you retrieve any records (or not).

SELECT *

FROM AP_DETAIL

— Does this table return records? YES/NO

11) Using User # 2B (the newly created account you mentioned) – please run the following SQL Statement in Interactive SQL and indicate if you retrieve any records (or not).

SELECT *

FROM VIEW_SPR_AP_AGING

— Does this view return records? YES/NO

12) Using User # 2B (the newly created account you mentioned) – please run the following SQL Statement in Interactive SQL and indicate if you retrieve any records (or not).

SELECT *

FROM VIEW_AP_TRANS

— Does this view return records? YES/NO

13) Using User # 2B (the newly created account you mentioned) – please run the following SQL Statement in Interactive SQL and indicate if you retrieve any records (or not).

SELECT *

FROM SYS_COMPANIES

— Does this table return records? YES/NO

14) Using User # 2B (the newly created account you mentioned) – please run the following SQL Statement in Interactive SQL and indicate if you retrieve any records (or not).

SELECT *

FROM AP_DETAIL

— Does this table return records? YES/NO

Where am I going with these questions?

– I am trying to narrow down (isolate) the specific access point (retrieval method) that blocking the data results for User # 1 (the problem account).

– My gut tells me that Test # 7 and Test # 8 will be the only EMPTY returns.

Goal – to potentially isolate standard Table (from View) retrieval.

I hope this is helpful.

-Jim

[James Jewell]

Hi Henrik –

Good morning.

Thank you for outlining the test you have tried. (provides insight)

A couple quick questions…

1) Am I correct the user is able to login to Quantum and is able see data from within Modules in the Quantum app?

2) And the user (when logged into your PC with his/her credentials) is not able to see the data within Interactive SQL – correct?

3) And yet you (when logged into your PC with your credentials) are able to see the data within Interactive SQL – correct?

4) And if you create a new account for the user (User #2B) (when logged into your PC with the new User #2B account) you are able to see the data within Interactive SQL – correct?

5) And if you (when logged into the USER’s PC with your credentials) are able to see the data within Interactive SQL – correct?

6) And if you (when logged into the USER’s PC with the User #2B credentials) are able to see the data within Interactive SQL – correct?

Where am I going with these questions?

– I am trying to narrow down where the access point is blocking the data results.

– If all (6) of the above questions come back with “Yes”, then I am wondering if this is a Database (credentials) issue, and not a Quantum issue.

– If the user’s original account does not have retrieval credentials on different PC’s for the QC database (it begs the question – is there a table access flag set in Oracle that is somehow different for the user from your account and the new User#2 account). If the above (6) questions are all “Yes”, then it smells of a DB access issue vs a PC/Laptop issue.

– What I am trying to identify with the above (6) questions – is the x-checking the results of each of the 3 credentials (yours, user, new User #2B) against the 2 PC’s (yours, users).

Goal – to potentially separate User (from Device).

I hope this is helpful.

-Jim

[James Jewell]

Nadim –

Thanks for the insight on this code.

Also a great way to handle the “leap year” (Feb 29th) issue when comparing Year-Over-Year results.

last_day(add_months(trunc(SYSDATE), -1))

-Jim

[James Jewell]

Hi Nadim –

Wow!

Thanks for the SQL help.

Much appreciated.

-Jim

[James Jewell]

Nadim –

Thanks for the insight.

For Invoices that were not derived from a WO Quote, how would you (in SQL) connect a non-WO Invoice that an Invoice Deposit was applied to it?

I can pull a list of Deposit Invoice(s) using the INVC_HEADER table and filtering on the field INVC_TYPE = ‘D’, but I am having troubles finding all the future Invoice(s) that each Deposit Invoice was applied to.

Your suggestion of using the WO_QUOTE_DETAIL table is great. Thanks. But how to identify other Invoice(s) that were not quotes where a Deposit Invoice was used as payment?

Thanks again for all your help.

-Jim

ps: What we are ultimately trying to accomplish is identify which Deposit(s) have not been used yet and what the current available balance is for each Deposit Invoice.

[James Jewell]

Hi Lee,

I noticed no response (yet) for your question and thought I’d ask a couple questions to see if we can find you a solution.

1) Please provide 2-3 screen shot examples of the “Search Stock” and what the results would be for each search. “Search Stock” for available inventory, inventory reserved, inventory on order, or other?

2) What is the “context” of how a user will use the report. (sometimes that will refresh my memory to a similar request internally that I’ve addressed – and I can reference some of the code I’ve used to solve it)

The screen shots might help spur some dialogue and bring forward something that sparks a solution.

Regards,

-Jim

[James Jewell]

>> Does anyone know how I can get my subreports to total properly on the main report?

Hi Pam,

I ran into this issue a while back and used the following approach.

Below is a link to a SAP Forum that was very helpful.

Additionally, I have included a Sample report I created for testing.

1) You will need to create “Shared” (global) variables in each Subroutine section.

2) You will then need to have the “Main” formula render AFTER the other Subsection formulas. This is a key issue. SAP Crystal is linear in its rendering of data – so you will need to do (1) Section at a time in sequence. The best way I found to handle this is to create different Sections (a/b/c/d) with the last Section executed (rendered) being the MAIN area display (d). See Sample attached.

3) Create “Subroutine Formulas” in each Subroutine and the MAIN to assign the rendered (calculated) amounts to each Subroutine “Shared” (global) variable, then when section (d) is calculated it will grab all the “Shared” variable amounts.

Subroutine Formula:

// @SubFormula – I called it MyGlobalField01

WhilePrintingRecords;

Shared NumberVar MySubRoutine_01_Amount := 111

//Shared NumberVar MySubRoutine_01_Amount := COUNT ({Command.STM_AUTO_KEY})

Main Formula:

WhilePrintingRecords;

Shared NumberVar MySubroutine_01_Amount;

Shared NumberVar MySubroutine_02_Amount;

Shared NumberVar MySubroutine_03_Amount;

Shared NumberVar MainRoutineTotal := MySubroutine_01_Amount + MySubroutine_02_Amount + MySubroutine_03_Amount;

MainRoutineTotal

I hope this is helpful.

-Jim

ps: I wasn’t able to upload the Sample report I created. I guess the .rpt extension is not permitted as an attachment to this thread. If you can DM me your email, I will try to send the Sample.rpt to you for reference & testing. It is nothing fancy, but will provide you an example of how I was able to get Subroutine amounts to roll up to the Main display. I uploaded a Word doc with a couple screen shots for your review. I hope the screen shots help in the interim.

Reference:

https://apps.support.sap.com/sap/support/knowledge/public/en/1212454

[James Jewell]

Nadim,

Thank you!

That was exactly what I was looking for.

I wasn’t sure what the syntax was for sending parameters thru to the PKG’s. Your sample gave me the syntax format I was looking for.

This is perfect. I so much appreciate your help.

-Jim

[James Jewell]

Thanks Nadim for responding.

I agree we can use a “where” condition in the SQL statement at the reporting level.

Sorry I didn’t clarify – the problem I am running into is the “RPT_GL_PKG” package is using parameters before populating the “View_Income_Statement” table, which is happening before pulling the “View” table into the Crystal report for use.

So the “where” condition at the Interactive SQL level is too late in the game as the summary table “View_Income_Statement” only has the prior standard reports data populated in the table.

What I am hoping for is a way to pass the Crystal parameters thru to the “RPT_GL_PKG”, in effect overriding the prior standard report parameters previously used.

Any insight would be greatly appreciated on how to pass the Crystal parameters thru to the packages, so the “View_Income_Statement” package “RPT_GL_PKG” uses the CR parameters instead of the boiler plate parameters?

Thanks again for responding. Much appreciated.

-Jim