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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics