Using different tablix field in an expression for a new field in current tabix
I'm new to SQL Report Writer and need help please. I am trying to use data from a field in a different tablix for a new total. I am able to use the "=ReportItems("ThisFieldName").Value successfully within the same tablix but how do I pull a
field from a different tablix? Any help would be much appreciated!
March 24th, 2011 8:42pm
Hi,
Are you trying to use two different data-sets into one tablix?
KumarKG, MCTS
Free Windows Admin Tool Kit Click here and download it now
March 24th, 2011 9:04pm
same dataset but two different tables.
March 24th, 2011 9:34pm
OK,
What are you trying to achieve? Is it possible for your to elaborate your req?
Thanks
KumarKG, MCTS
Free Windows Admin Tool Kit Click here and download it now
March 24th, 2011 9:54pm
I am trying to take a total from one table (tablix) and use it in a calculation in another table (tablix). For example, the first table is getting a total on a count distinct for "referral ID". I want to use this total as a denominator on a percent
calc in another table, which uses the same dataset. I am new to this so thank you in advance for your patience with my ignorance. Here is an example:
I want Column2 to be an expression which is column1 divided by the total from Tablix2
Tablix1
Rows for Referral to this provider:
Column1
Column2
Provider 1
500
83%
Provider2
200
33%
Provider3
300
50%
Total
1000
Tablix2
Number of distinct referrals
600
March 24th, 2011 11:06pm
Hi,
1. Use the expression for your column2 as:-
= ( CInt ( Fields!Column1.Value ) / CInt ( Fields!NumberOfDistinctReferrals.Value ) ) * 100
2. Right-click on your Column2 (Details) tex-box, go to Text-box properties, go to Number tab, select "custom" from Category and type
P0 as your custom Format.
You should be good to go.
Please let us know your feedback.
Thanks
KumarKG, MCTS
Free Windows Admin Tool Kit Click here and download it now
March 24th, 2011 11:44pm
Hi,
I tried this but got an error message. To be more specific, NumberofDistinctReferrals is not a field. It is in a different tablix and is the total of a calc on a field called referral_id. The calc is countdistinct(referralid). Does
this make sense?
March 24th, 2011 11:57pm
Hi ContinualLearner,
From you description, I noticed that both Tablix refers to the same dataset. So, we can also get the number of distinct referrals in the first Tablix and used it for column. Please
try to specify the following expression to the cell of Column2.
=Fields!Column1.Value/ CountDistinct(Fields!referralid.Value,”DataSetName”)
Please also be aware to correct the field and dataset names based on your report.
Thanks,
Tony ChainTony Chain [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Free Windows Admin Tool Kit Click here and download it now
March 31st, 2011 3:02am
Thank you!!! I didn't think it would be as easy as putting in countdistinct with the data set name. This is the forumla I ended up using based on your comments above and it worked perfectly. I really appreciate your help!
=ReportItems("Referral_ID2").Value/CountDistinct(Fields!Referral_ID.Value,"Referred")
March 31st, 2011 11:50am