Solved

Microsoft Report Builder 3 - Running total of non unique feilds

Posted on 2013-11-03
1
692 Views
Last Modified: 2013-11-18
I have a report from a production system written with MS Report Builder 3 that I need to load into a ERP (with the ability to run a SQL script beforehand).  The report from the production system has some records with identifiers that are not unique and before I load into the ERP I would like to add a trailing digit to make them unique.  The trailing digit is the count of each instance (ie first instance is 1, second is 2 etc)

Data:

Record/Volume
A1/100
A2/50
A2/75
A3/200

Required data
Record/Volume
A11/100
A21/50
A22/75
A31/200

Ideally I would like to do this in MS Report Builder if possible because there are a lot of other moving parts in the current SQL script.

Is it possible to somehow get a count or the like in each row of the Report Builder report?  I have tried but am getting aggregate function errors.

Thanks
0
Comment
Question by:froodox
1 Comment
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 500 total points
Comment Utility
You can use the RowNumber function for this. See http://technet.microsoft.com/en-us/library/ms159225%28v=sql.100%29.aspx.
Because you want to restart the count for every new value of the Record field, you need to create a group for this field, and use the name of that group as the scope for the RowNumber function. See the attached sample report. Assuming the fields are named Record and Volume, and the group for the Record field is named Record, the expression would be:
=Fields!Record.Value & RowNumber("Record") & "/" & Fields!Volume.Value

Open in new window

RowNumber.rdl
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now