How to Add Fields to a Canned SSRS Report

(1) Share

Report

Posted on by Community Member

Hi:

I simply want to add a couple of fields to the canned GP SSRS Historical Inventory Trial Balance (HITB), such as the Item Class Code.

In Report Builder, I was able to create a Data Set called "ITEMS" based on the simple "select * from IV00101" query.

But, when I tried to add the Item Class Code field as a column in the report from this new Data Set in the HITB, I received an error saying that only fields from the HITB's original Data Set can be added to the report.

I realize that it is easy to create a new HITB report in some form simply by basing a new report on the SEE30303 table, but I want to modify the canned HITB report as requested by the end user.

And, yes, I know that I can duplicate the stored procedure that the canned HITB report is based on and create a similar SSRS report based on the revised duplicate stored procedure. This, however, seems like a lot of "trouble" just to add one or two fields.

Not only that, if I were to change the original HITB's Data Set to look at a "revised" duplicate stored procedure, how would that affect potentially other canned GP SSRS reports that use this same Data Set?

Why is the canned report not letting me add fields from a newly created Data Set? Is there another means of modifying such a canned report?

Thanks!

John

Categories:

Microsoft Dynamics GP

I have the same question (0)

All responses (15)

Answers (0)

bgajjar 150 on at

Like (1)

Report

Hi

First of I would recommend not modify out of the box SSRS Report or Stored Procedure, so try below steps.

Duplicate seeHITB SP as [seeHITB_Custom], change last line of code as below

SET @acctsegt = 'select a.*, b.ACTNUMST, c.ITMCLSCD from ' + RTRIM(@temptable) + ' a, GL00105 b, IV00101 c where a.ACTINDX = b.ACTINDX and a.ITEMNMBR = c.ITEMNMBR'
EXEC (@acctsegt)
GO

Duplicate GP's "Historical Inventory Trial Balance Detail" SSRS Report as ""Historical Inventory Trial Balance Detail - Custom" Edit this custom report and change DataSet command seeHITB to eeHITB_Custom, and click refresh, refresh will refresh field list with ITMCLSCD new field, now you can this field to the report.

I hope this is what you are looking for.

Thanks

Bharat

Was this reply helpful? Yes No
Alaa Ramadan 4,130 on at

Like (0)

Report

This is message more related to your SSRS report. typically it pops up when you attempt to add a field from different data set other than the one linked to SSRS table.

in SSRS report design, right click on the table that you want to add the new field to and check the name of the dataset.

Make sure the new field belong to the same dataset.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Bharat:

The report is not finding the new stored procedure. What are your thoughts?

Thanks!

John

Was this reply helpful? Yes No
bgajjar 150 on at

Like (0)

Report

Hi John,

As I explain in my last reply, you need to change SP name to your custom new SP name in the SSRS Report, make sure you create your custom SP first and setup required permissions, see attached screen shot for how to change SP name in report. i hope this will help.

Good Luck.

Bharat

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Yes, and I had done all of that already. The stored proc is not appearing.

You mention "permissions". How do I check for this?

John

Was this reply helpful? Yes No
bgajjar 150 on at

Like (0)

Report

when you say "stored proc is not appearing" do you get any error when press Refresh Fields Button ? and if you send you stored proc script.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

The stored proc is not in the drop-down list.

Was this reply helpful? Yes No
bgajjar 150 on at

Like (0)

Report

Just type your new SP name manually, and click "Refresh Fields" button, if you don't get any error then just press OK button otherwise check your Data Source in the report, pointing to correct GP database where you have created custom SP.

-Bharat

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Bharat:

I had already tried typing the name of the stored proc manually and the Data Source is pointing to the correct company. Still, it's a "no-go".

John

Was this reply helpful? Yes No
bgajjar 150 on at

Like (0)

Report

and you you dont get any error, when you press Refresh Fields ?

Was this reply helpful? Yes No