Reviewing Management Reporter's "Reporting" Table

(0) Share

Report

Posted on by Community Member

Hello:

One of our database administrators is very often studying ways of downsizing the database for Management Reporter. We are on GP 2013 R2 (12.00.1920) and CU13 of Management Reporter with the Legacy Provider. And, our ManagementReporter database has grown to over 600 GB in size!

We would like to know, then, why does the Management Reporter app "force" financial statements to dump rows where ApplyDate = NULL in the "Reporting" table's ReportLineTransaction object in SQL?

This would, of course, apply to our few statements that are generated to allow for drilling back on the data.

Thank you!

John

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (1)

Sort by

RogerRogerATX 1,515 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi John.

I gave up trying to access the old reports via SQL in the MR database. The encrypted keys where just a little too hard to make it worthwhile.

Anyway:

the DB is huge because it saves every report you've ever run in that DB. There is an official script that can remove the older reports from the db. We had the same problem but we noticed it when MR got to about 250gb. I now try to keep it under 60GB.

Was this reply helpful? Yes No
Community Member on at

Like
a
(0)

Report
Copy link

Link copied!

Actually, I have some updated information, in terms of what one of our database administrators is looking for.

Specifically, according to this administrator, you cannot use the phrase "= NULL" in SQL. You would use "IS NULL", instead. So, why is the app using "= NULL"?

Although we generate financials to run several times per day during month-end close, we do delete the older versions of each report after close. But, why does the database retain copies of these versions in its database?

Thank you!

John

Was this reply helpful? Yes No
RogerRogerATX 1,515 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi John. I know that if you are using a SET or UPDATE command you have to use =NULL.

IS NULL or IS NOT NULL is part of a SELECT statement.

Anyway, the exact programming reasons behind what MR is doing is beyond me. Which is the same answer why it saves a copy. You are deleting them in the Report Library, correct?

Was this reply helpful? Yes No
Community Member on at

Like
a
(0)

Report
Copy link

Link copied!

Correct. We are deleting the versions in the Report Library. That's why I'm surprised that the app's database retains them.

Also, why is the app dumping data to ReportLineTransaction with Null Applydate (i.e. no datetime passed)

Are those rows useful in any way for saved reports?

John

Was this reply helpful? Yes No
Verified answer

serbach 571 on at

Like
a
(0)

Report
Copy link

Link copied!

John,

I think I have the answer: The ApplyDate is NULL for all rows that are NOT Transaction-level rows. That is, FInancial-level and Account-level rows don't have them. Neither do they have Descriptions, which Transaction-level rows do.

Regards,

Steve Erbach

Was this reply helpful? Yes No