SBX - Search With Button

SBX - Forum Post Title

SSRS Report Format

Microsoft Dynamics AX Forum

Jhon Edmar Tarlac asked a question on 10 Aug 2018 9:36 AM

Question Status

Verified

Hi Guys,

Sorry for the subject i don't know how to name my question.

Let me explain. I want to print Product serial numbers Dynamically based on quantity when posting sales order.

Honestly this report is already done in Morphx Report in AOT, we planing to transfer this report to SSRS report but with new requirements which to put the serial number.

I create Customizte Report (RDP) with three temporary table and in SSRS three dataset.

a. HeaderFooterTmp
b. BodyTmp
c. SerialTmp

Now, the format requirements is quite hard, So that i try to ask here for a help.

Please see below format needed.

Any help or suggestion would be greatly appreciated.

Should have any question please let me know.

TIA

Reply
Crispin John Augustine responded on 10 Aug 2018 9:56 AM
My Badges

It is possible to nest a tablix Control inside a tablix.. That is one Option

It will help greatly if you merge the BodyTmp and SerialTmp in one table, (although this might result in a de-normalized structure, this makes the design much more simple and readable)

All you Need in this case is proper Group bys and Aggregates, which is quite a Standard way of designing AX Ssrs Reports.

Reply
Jhon Edmar Tarlac responded on 12 Aug 2018 11:01 PM

Thanks for the response Crispin,

I understand what you said and i think you right, i do combine the bodytmp and serialtmp.

But, i don't know how get the proper group bys  and aggregate.

Since the the SerialTmp before has 4 fields and move it to Bodytmp.

  • InventTransId
  • SerialNumber_1
  • SerialNumber_2
  • SerialNumber_3

how i can do the format/image above?

Thank you and regards,

Reply
Crispin John Augustine responded on 13 Aug 2018 3:30 AM
My Badges

If you get your merged table to hold data in this Format, that is a great first step.

After this, you can just create one tablix in the Report design, sort by item id, and Display the item Information only if it changes. This will get you the design in your post.

Reply
Jhon Edmar Tarlac responded on 13 Aug 2018 3:59 AM

Yes it returns like that.

I try the next one you said and add the serial. Below it shows the result.

ITEMNAME-Head
SerialABCD1 SerialABCD2 SerialABCD3
ITEMNAME-Head
SerialABCD4 SerialABCD5 SerialABCD6
ITEMNAME-Head
SerialABCD7 SerialABCD8 SerialABCD9

And to share with the tablix looks like.

Can you help me on how to group this?

Thank you very much

Reply
Crispin John Augustine responded on 13 Aug 2018 4:19 AM
My Badges
Verified Answer

Just one more easy step.

On the ItemName row, right click on the row Icon (left) and select it's hidden property.

In the Expression, use something like this:

=iif(Fields!ItemName.Value=previous(Fields!ItemName.Value))

(no Group by required, I got that wrong in my first comment)

Reply
Jhon Edmar Tarlac responded on 13 Aug 2018 4:46 AM

Thank you Crispin .

It helps me a lot.

Reply
Crispin John Augustine responded on 13 Aug 2018 4:19 AM
My Badges
Verified Answer

Just one more easy step.

On the ItemName row, right click on the row Icon (left) and select it's hidden property.

In the Expression, use something like this:

=iif(Fields!ItemName.Value=previous(Fields!ItemName.Value))

(no Group by required, I got that wrong in my first comment)

Reply

SBX - Two Col Forum

SBX - Migrated JS