Hi Dr. Goms, I was able to create my first ever report watching your videos. Thank you for sharing all these. Right now, I need more help on complex report requirement specifically creating columns for values of a field in the table. For example, in the Table A, there is a field X and under field X, there are values such B, C, D, E. In simple reporting, the field X is a column. But how to make B, C, D, E columns as well?
Hello Ruel, I'm thrilled to hear that my videos have been helpful to you in creating your first report! Congratulations on your achievement, and thank you for sharing your feedback. I'm here to assist you further with your complex report. To address your specific question about creating columns for values of a field in the table, such as B, C, D, and E under field X, you can achieve this by utilizing a technique called "matrix grouping" in RDLC reports. Here's a general outline of the steps you can follow: 1. Start by designing your report layout in RDLC as usual, including the necessary dataset and data fields. 2. Insert a Matrix control onto your report layout, and bind it to the dataset containing the data for field X. 3. Group the rows of the matrix by field X. This will ensure that each unique value of field X will become a separate column in the matrix. 4. Within the matrix, you can add the necessary data fields or calculations that you want to display for each value of field X (B, C, D, E). 5. Format and customize the appearance of the matrix columns and rows as needed. By following these steps, you should be able to achieve the desired result of having columns for the values of field X (B, C, D, E) in your report. If you need more specific guidance or have any further questions, please feel free to provide additional details, and I'll be happy to assist you in the best way I can. Keep up the great work, and best of luck. Warm regards, Dr. Gomathi S
@@gomstechtalks Dear Dr. Goms, the matrix grouping worked. Big big thanks. I was able to have columns for the values B, C, etc... If it is still okay to ask guidance from you, since I am a beginner in development, and following your videos made me a sort of a "developer" but still very beginner. The report I need to create will come from 3 tables. These 3 tables have common link via the field "Transaction No.". The first table, called "Transaction Header" has the fields, Transaction No., Date, Time, Gross Amount, Net Amount, Payment, and Transaction Type. The second table, called Trans Sales Entry, has the fields Transaction No, Trans. Date, Trans Time, Line Discount, Line Discount Code. And the third table, called Trans Payment Entry has the fields Transaction No, Trans Date, Trans Time, Tender Type, Amount Tendered. I was able to do matrix group to both Line Discount Code and Tender Type (I really thank you for this). However, the required format of the report is that on a per date and per transaction no in one line, I will be able to show the following: Day 1: Total Gross Amount/day (1st column), Total Net Amount/day (2nd column), Payment (3rd column) Line Discount (matrix group - next columns), Tender Type(matrix group - next columns) Day 2: Total Gross Amount/day (1st column), Total Net Amount/day (2nd column), Payment (3rd column) Line Discount (matrix group - next columns), Tender Type(matrix group - next columns) Day 3: Total Gross Amount/day (1st column), Total Net Amount/day (2nd column), Payment (3rd column) Line Discount (matrix group - next columns), Tender Type(matrix group - next columns) Day 4... Day 5 same as above Thank you in advance for all the help. In fact, I emailed you today about this. Regards, Ruel
Hi mam I'm very addicted to your lectures. Could you please make a playlist on Xrec, SetSelectionFilter , SetCurrentKey , RecordRef , Settablefilter , Gettable filter , SetAutoCalcFields , SetRecFilter , SetPosition , SetLoadFields . These things are not explained by anyone anywhere and I'm sure this will be really very very helpful for many persons who are learning AL . I've learnt lot of things from you please help me to understand the above provided list . Please mam I'm very interested in learning AL after watching your lectures please don't avoid my comment.
Thank you so much for recommending these topics. Glad to know that you are learning something from the videos. Sure will upload all the topics which you have recommended soon. This week is scheduled for Action types. Will upload these topics from the next week.
Dear Goms, this was very good. Slower and much more calm than the other Tutorials before in this playlist and very good for me to understand. Thank you very much :-) I wonder how to make a subtotal at the end of each page if more than one page is created by the report. I tried it with =Sum(Fields!CustomerBalance.Value, "DataSet_Result") in the footer but then the total is shown on each page. Any hint? Regards Carsten
Hi Carsten, Thank you for your kind words about the tutorial, I'm glad it was helpful for you! To create subtotals at the end of each page in a report using Microsoft Report Builder, you can use the same approach as in Business Central. First, you need to group your data by a common field, such as the customer name or order date. Then, you can add a page break after each group to ensure that each group appears on a separate page. In the footer section of the report, you can add a textbox to display the subtotal for that group. To calculate the subtotal, you can use an expression like "=Sum(Fields!CustomerBalance.Value)". To ensure that the subtotal is only displayed at the end of each group page and not on every page, you can set the "PageName" property of the textbox to a unique value, such as the group name. I hope this helps! Let me know if you have any further questions or need additional assistance. Best regards, Dr. Gomathi
@@gomstechtalks Thank you very much, but thats not what I meant. From your example "Report based on Query in Business Central" there's customer data. Each customer exists only once with its own Balance. If I do not limit it with TopN to 5 and say e.g. 100, the customers are listed and page breaked after a report page is full. And before page will be broken a sum of all balances of the current report page should be summed at the end of the page. E.g. Report Page 1: CustomerNo1 | CustomerName1 | Balance1 . . . CustomerNo20 | CustomerName20 | Balance20 Sum of Balance1 to Balance20 --- Page break Report Page 2: CustomerNo21 | CustomerName21 | Balance21 . . . CustomerNo40 | CustomerName40 | Balance40 Sum of Balance21 to Balance40 --- Page break and so forth...
Thanks for this great video! I have a question regarding line 16: In minute 15:12 you talk about the sorting and formulate the line like this: DataItemTableView = sorting(number) But at the end of the video and in your GitHub page there's nothing inside the brackets: DataItemTableView = sorting(); Can you please explain the difference between the sorting with "number" inside the brackets and the sorting with nothing inside the brackets?
Thank you for your support and feedback. DataItemTableView property is used to specify how the data in a report or query should be sorted or filtered before being processed. The syntax you've mentioned relates to how this sorting is defined within the AL language (the programming language used for developing in Dynamics 365 Business Central). The key difference between these two is that the first explicitly defines a field to sort by, making your intentions clear and directly influencing the data's order. The second form does not specify sorting fields, which means its behavior can be more context-dependent and might require additional setup or assumptions about the default sorting behavior of the data item.
![YoungBoy Never Broke Again - Missing Everything [Official Video]](http://i.ytimg.com/vi/dTYeMZGzOzw/mqdefault.jpg)
Your videos have been hugely beneficial for me. Thank you for churning out easy-to-understand content.
Glad that it is useful to you. Thank you for taking the time to share your feedback
Hi Dr. Goms, I was able to create my first ever report watching your videos. Thank you for sharing all these. Right now, I need more help on complex report requirement specifically creating columns for values of a field in the table. For example, in the Table A, there is a field X and under field X, there are values such B, C, D, E. In simple reporting, the field X is a column. But how to make B, C, D, E columns as well?
Hello Ruel,
I'm thrilled to hear that my videos have been helpful to you in creating your first report! Congratulations on your achievement, and thank you for sharing your feedback. I'm here to assist you further with your complex report.
To address your specific question about creating columns for values of a field in the table, such as B, C, D, and E under field X, you can achieve this by utilizing a technique called "matrix grouping" in RDLC reports.
Here's a general outline of the steps you can follow:
1. Start by designing your report layout in RDLC as usual, including the necessary dataset and data fields.
2. Insert a Matrix control onto your report layout, and bind it to the dataset containing the data for field X.
3. Group the rows of the matrix by field X. This will ensure that each unique value of field X will become a separate column in the matrix.
4. Within the matrix, you can add the necessary data fields or calculations that you want to display for each value of field X (B, C, D, E).
5. Format and customize the appearance of the matrix columns and rows as needed.
By following these steps, you should be able to achieve the desired result of having columns for the values of field X (B, C, D, E) in your report.
If you need more specific guidance or have any further questions, please feel free to provide additional details, and I'll be happy to assist you in the best way I can.
Keep up the great work, and best of luck.
Warm regards,
Dr. Gomathi S
@@gomstechtalks Dear Dr. Goms, the matrix grouping worked. Big big thanks. I was able to have columns for the values B, C, etc... If it is still okay to ask guidance from you, since I am a beginner in development, and following your videos made me a sort of a "developer" but still very beginner. The report I need to create will come from 3 tables. These 3 tables have common link via the field "Transaction No.". The first table, called "Transaction Header" has the fields, Transaction No., Date, Time, Gross Amount, Net Amount, Payment, and Transaction Type. The second table, called Trans Sales Entry, has the fields Transaction No, Trans. Date, Trans Time, Line Discount, Line Discount Code. And the third table, called Trans Payment Entry has the fields Transaction No, Trans Date, Trans Time, Tender Type, Amount Tendered. I was able to do matrix group to both Line Discount Code and Tender Type (I really thank you for this).
However, the required format of the report is that on a per date and per transaction no in one line, I will be able to show the following:
Day 1: Total Gross Amount/day (1st column), Total Net Amount/day (2nd column), Payment (3rd column) Line Discount (matrix group - next columns), Tender Type(matrix group - next columns)
Day 2: Total Gross Amount/day (1st column), Total Net Amount/day (2nd column), Payment (3rd column) Line Discount (matrix group - next columns), Tender Type(matrix group - next columns)
Day 3: Total Gross Amount/day (1st column), Total Net Amount/day (2nd column), Payment (3rd column) Line Discount (matrix group - next columns), Tender Type(matrix group - next columns)
Day 4... Day 5 same as above
Thank you in advance for all the help. In fact, I emailed you today about this.
Regards,
Ruel
Hi mam I'm very addicted to your lectures. Could you please make a playlist on Xrec, SetSelectionFilter , SetCurrentKey , RecordRef , Settablefilter , Gettable filter , SetAutoCalcFields , SetRecFilter , SetPosition , SetLoadFields . These things are not explained by anyone anywhere and I'm sure this will be really very very helpful for many persons who are learning AL . I've learnt lot of things from you please help me to understand the above provided list . Please mam I'm very interested in learning AL after watching your lectures please don't avoid my comment.
Thank you so much for recommending these topics.
Glad to know that you are learning something from the videos.
Sure will upload all the topics which you have recommended soon.
This week is scheduled for Action types. Will upload these topics from the next week.
@@gomstechtalks hi mam can you please look into this
Yes, I have already uploaded videos on xrec, and filter. Please check
@@gomstechtalks yeah I'm watching them please do for rest of the things also thanks a lot ❤️
Please check here for setautocalcfield
ruclips.net/video/tJy7uZAm4AA/видео.html
Dear Goms, this was very good. Slower and much more calm than the other Tutorials before in this playlist and very good for me to understand. Thank you very much :-)
I wonder how to make a subtotal at the end of each page if more than one page is created by the report.
I tried it with
=Sum(Fields!CustomerBalance.Value, "DataSet_Result") in the footer but then the total is shown on each page.
Any hint?
Regards
Carsten
Hi Carsten,
Thank you for your kind words about the tutorial, I'm glad it was helpful for you!
To create subtotals at the end of each page in a report using Microsoft Report Builder, you can use the same approach as in Business Central.
First, you need to group your data by a common field, such as the customer name or order date. Then, you can add a page break after each group to ensure that each group appears on a separate page.
In the footer section of the report, you can add a textbox to display the subtotal for that group. To calculate the subtotal, you can use an expression like "=Sum(Fields!CustomerBalance.Value)".
To ensure that the subtotal is only displayed at the end of each group page and not on every page, you can set the "PageName" property of the textbox to a unique value, such as the group name.
I hope this helps! Let me know if you have any further questions or need additional assistance.
Best regards,
Dr. Gomathi
@@gomstechtalks
Thank you very much, but thats not what I meant.
From your example "Report based on Query in Business Central" there's customer data. Each customer exists only once with its own Balance.
If I do not limit it with TopN to 5 and say e.g. 100, the customers are listed and page breaked after a report page is full. And before page will be broken a sum of all balances of the current report page should be summed at the end of the page.
E.g.
Report Page 1:
CustomerNo1 | CustomerName1 | Balance1
.
.
.
CustomerNo20 | CustomerName20 | Balance20
Sum of Balance1 to Balance20
--- Page break
Report Page 2:
CustomerNo21 | CustomerName21 | Balance21
.
.
.
CustomerNo40 | CustomerName40 | Balance40
Sum of Balance21 to Balance40
--- Page break
and so forth...
Thanks for this great video! I have a question regarding line 16:
In minute 15:12 you talk about the sorting and formulate the line like this:
DataItemTableView = sorting(number)
But at the end of the video and in your GitHub page there's nothing inside the brackets:
DataItemTableView = sorting();
Can you please explain the difference between the sorting with "number" inside the brackets and the sorting with nothing inside the brackets?
Thank you for your support and feedback.
DataItemTableView property is used to specify how the data in a report or query should be sorted or filtered before being processed. The syntax you've mentioned relates to how this sorting is defined within the AL language (the programming language used for developing in Dynamics 365 Business Central).
The key difference between these two is that the first explicitly defines a field to sort by, making your intentions clear and directly influencing the data's order. The second form does not specify sorting fields, which means its behavior can be more context-dependent and might require additional setup or assumptions about the default sorting behavior of the data item.