Unable to see detail reports in SPM

[Former Member]

Hi

We are unable to run any detail reports in SPM UI. When we try to run the query "0ASA_MP04/0ASA_MP04_Q0001" in RSRT it goes in debug mode and gives the following error:

[Microsoft][SQL Server Native Client 10.0][SQL Server]Arithmetic overflow error converting expression to data type int. row 1, col -1

Error reading the data of InfoProvider 0ASA_DS09

Error while reading data; navigation is possible

>> Row: 32 Inc: EXECUTE_QUERY Prog: CL_SQL_STATEMENT

Please help us resolve this

Regards

Neel

[Former Member · Accepted solution]

Hi Rohit

Our basic concern is, we are not able to run the Standard Detail Reports i.e Invoice, PO and Contract detail reports.

We cannot use the restriction on the backend as the users need to see all the data through the front end.

Regards

Neel

[Former Member · Accepted solution]

Hi Neel,

Yes I understand that you need to be able to run the detail reports without any filter. I request you to apply the filters to test this functionality only and not for productive use.

Secondly like I mentioned above, the detail reports cannot be run by themselves directly, they are always triggered from the summary report and all the dimension values are passed down.

Regards,

Rohit

[Former Member · Accepted solution]

Hi Rohit

When we apply filters, the query works in backend, but not in UI even after we refresh the data sources.

Also could you please confirm, should the out of box detail reports like Contract detail PO detail, invoice detail work? because they are not working in our case.

Regards

Neel

[Former Member · Accepted solution]

Hi Neel,

Yes the details reports do work, they are being productively used by customers. As mentioned they cannot be executed on their own, but can be called from a summary report.

If you are still not able to pin point the issue, I would recommend you to open up a customer message on this issue.

Regards,

Rohit

[Former Member]

Hi Rohit

Thanks for the prompt reply. Really appreciate it.

As suggested we have tried the following:

1) In the BEx Query designer we restricted the values of the source systems and then tried to run the query in RSRT and it is working fine

2) Further to the above issue, since we want to analyze data from all source systems, we tried to apply filters through UI so we set the preferred data source to ASA Detail query, but when we try to go to NEW--> Detail report it gives us 1009 error.

Awaiting your further inputs

Regards

Neel

[Former Member]

Hi Neel,

Its good to know that the detail reports runs in RSRT without any problems (with filter). Next step is to 'Refresh Datasource' in the UI (with the filter applied) to ensure that this detail query can be access in the UI.

What you are trying 'changing the datasource to detail query' and trying to create a new report is not possible. Any query that is registered as a detail datasource canno be executed directly. Reports against these detail datasources can only be access via a summary report. Which means create a summary report apply as many dimension filters as possible (dimension which exist in the detail datasource) and then launch the detail report from the measure value context menu (Invoice detail, PO detail, etc...) This will execute a report that is against a detail datasource.

Inspite of this if you are running into issues, please provide more details or open up a customer message with details.

Regards,

Rohit

[Former Member]

Hi Neel,

Detail reports in SPM is based on query "0ASA_MP04/0ASA_MP04_Q0001" like you have already identified. But in SPM this query is passed multiple filters from the summary reports before it is executed. This query has be filter before its execution because it contains very detail line item level information like multiple Document Item numbers, which depending on how much data you have could take very long time to execute and possibly will fail with memory related issue at most customers.

In order to check to see if the query is functioning you can modify the query (in Query designer) and apply some filters on multiple characteristics to ensure you will receive very little data. This way during execution of this query in RSRT you will be able to narrow down the true issue.

OR

You can click 'Generate Report' button for this query and that should also tell you if there is something wrong with the query.

By the looks of the error message you are receiving "Error reading the data of InfoProvider 0ASA_DS09", either there is some mapping missing in the detail MP or there is some other issue. This is too little information to narrow down the cause of the issue. Hopefully the explanation above will help you get to the true cause of the issue.

Regards,

Rohit