Problem with Grouping and Calculation (Network Steve Forum)

Problem with Grouping and Calculation

I'm fairly new with SSRS, so please bear with me. In my data, I have Locations, Centers at those locations, a PD_Amount, and an RB_Amount. The PD_Amount and RB_Amount will be used in a formula to determine an average (RB_Amount/PD_Amount) over GLPeriods. The average needs to be calculated per GLPeriod. The corresponding month of a given GL Period will represent the column names in the report. I have provided a sample of my data in html: <html> <head> <title>Untitled</title> </head> <body> <TABLE BORDER=1> <TR> <TD>CoNo</TD> <TD>Location</TD> <TD>Code</TD> <TD>Center</TD> <TD>GLMonth</TD> <TD>GLMonthText</TD> <TD>GLYear</TD> <TD>GLPeriod</TD> <TD>PD_Amount</TD> <TD>RB_Amount</TD> </TR> <TR> <TD>01</TD> <TD>Alabama</TD> <TD>22001</TD> <TD>Center 1</TD> <TD>1</TD> <TD>January</TD> <TD>2010</TD> <TD>3</TD> <TD>483</TD> <TD>205872.2</TD> </TR> <TR> <TD>01</TD> <TD>Alabama</TD> <TD>22001</TD> <TD>Center 1</TD> <TD>2</TD> <TD>February</TD> <TD>2010</TD> <TD>4</TD> <TD>469</TD> <TD>198313.17</TD> </TR> <TR> <TD>01</TD> <TD>Alabama</TD> <TD>22001</TD> <TD>Center 1</TD> <TD>3</TD> <TD>March</TD> <TD>2010</TD> <TD>5</TD> <TD>461</TD> <TD>187043.64</TD> </TR> <TR> <TD>01</TD> <TD>Alabama</TD> <TD>22001</TD> <TD>Center 1</TD> <TD>4</TD> <TD>April</TD> <TD>2010</TD> <TD>6</TD> <TD>358</TD> <TD>159685.13</TD> </TR> <TR> <TD>01</TD> <TD>Alabama</TD> <TD>22001</TD> <TD>Center 1</TD> <TD>5</TD> <TD>May</TD> <TD>2010</TD> <TD>7</TD> <TD>460</TD> <TD>200804.4</TD> </TR> <TR> <TD>01</TD> <TD>Alabama</TD> <TD>22001</TD> <TD>Center 1</TD> <TD>6</TD> <TD>June</TD> <TD>2010</TD> <TD>8</TD> <TD>580</TD> <TD>241108.75</TD> </TR> <TR> <TD>01</TD> <TD>Alabama</TD> <TD>22001</TD> <TD>Center 1</TD> <TD>7</TD> <TD>July</TD> <TD>2010</TD> <TD>9</TD> <TD>625</TD> <TD>259000.57</TD> </TR> <TR> <TD>01</TD> <TD>Alabama</TD> <TD>22001</TD> <TD>Center 1</TD> <TD>8</TD> <TD>August</TD> <TD>2010</TD> <TD>10</TD> <TD>869</TD> <TD>367046.3</TD> </TR> <TR> <TD>01</TD> <TD>Alabama</TD> <TD>22001</TD> <TD>Center 1</TD> <TD>9</TD> <TD>September</TD> <TD>2010</TD> <TD>11</TD> <TD>706</TD> <TD>293908.12</TD> </TR> <TR> <TD>01</TD> <TD>Alabama</TD> <TD>22001</TD> <TD>Center 1</TD> <TD>10</TD> <TD>October</TD> <TD>2009</TD> <TD>0</TD> <TD>153</TD> <TD>63868.61</TD> </TR> <TR> <TD>01</TD> <TD>Alabama</TD> <TD>22001</TD> <TD>Center 1</TD> <TD>10</TD> <TD>October</TD> <TD>2010</TD> <TD>12</TD> <TD>823</TD> <TD>417685.36</TD> </TR> <TR> <TD>01</TD> <TD>Alabama</TD> <TD>22001</TD> <TD>Center 1</TD> <TD>11</TD> <TD>November</TD> <TD>2009</TD> <TD>1</TD> <TD>166</TD> <TD>66103.03</TD> </TR> <TR> <TD>01</TD> <TD>Alabama</TD> <TD>22001</TD> <TD>Center 1</TD> <TD>12</TD> <TD>December</TD> <TD>2009</TD> <TD>2</TD> <TD>257</TD> <TD>118454.19</TD> </TR> <TR> <TD>12</TD> <TD>Texas</TD> <TD>28012</TD> <TD>Center 2</TD> <TD>1</TD> <TD>January</TD> <TD>2010</TD> <TD>3</TD> <TD>336</TD> <TD>133645.54</TD> </TR> <TR> <TD>12</TD> <TD>Texas</TD> <TD>28012</TD> <TD>Center 2</TD> <TD>2</TD> <TD>February</TD> <TD>2010</TD> <TD>4</TD> <TD>321</TD> <TD>131230.79</TD> </TR> <TR> <TD>12</TD> <TD>Texas</TD> <TD>28012</TD> <TD>Center 2</TD> <TD>3</TD> <TD>March</TD> <TD>2010</TD> <TD>5</TD> <TD>442</TD> <TD>174235.04</TD> </TR> <TR> <TD>12</TD> <TD>Texas</TD> <TD>28012</TD> <TD>Center 2</TD> <TD>4</TD> <TD>April</TD> <TD>2010</TD> <TD>6</TD> <TD>449</TD> <TD>190144.84</TD> </TR> <TR> <TD>12</TD> <TD>Texas</TD> <TD>28012</TD> <TD>Center 2</TD> <TD>5</TD> <TD>May</TD> <TD>2010</TD> <TD>7</TD> <TD>501</TD> <TD>209930.88</TD> </TR> <TR> <TD>12</TD> <TD>Texas</TD> <TD>28012</TD> <TD>Center 2</TD> <TD>6</TD> <TD>June</TD> <TD>2010</TD> <TD>8</TD> <TD>629</TD> <TD>260198.45</TD> </TR> <TR> <TD>12</TD> <TD>Texas</TD> <TD>28012</TD> <TD>Center 2</TD> <TD>7</TD> <TD>July</TD> <TD>2010</TD> <TD>9</TD> <TD>425</TD> <TD>155709.21</TD> </TR> <TR> <TD>12</TD> <TD>Texas</TD> <TD>28012</TD> <TD>Center 2</TD> <TD>8</TD> <TD>August</TD> <TD>2010</TD> <TD>10</TD> <TD>314</TD> <TD>126967.97</TD> </TR> <TR> <TD>12</TD> <TD>Texas</TD> <TD>28012</TD> <TD>Center 2</TD> <TD>9</TD> <TD>September</TD> <TD>2010</TD> <TD>11</TD> <TD>223</TD> <TD>90038.98</TD> </TR> <TR> <TD>12</TD> <TD>Texas</TD> <TD>28012</TD> <TD>Center 2</TD> <TD>10</TD> <TD>October</TD> <TD>2009</TD> <TD>0</TD> <TD>255</TD> <TD>109005.54</TD> </TR> <TR> <TD>12</TD> <TD>Texas</TD> <TD>28012</TD> <TD>Center 2</TD> <TD>10</TD> <TD>October</TD> <TD>2010</TD> <TD>12</TD> <TD>200</TD> <TD>105335.45</TD> </TR> <TR> <TD>12</TD> <TD>Texas</TD> <TD>28012</TD> <TD>Center 2</TD> <TD>11</TD> <TD>November</TD> <TD>2009</TD> <TD>1</TD> <TD>208</TD> <TD>79749.49</TD> </TR> <TR> <TD>12</TD> <TD>Texas</TD> <TD>28012</TD> <TD>Center 2</TD> <TD>12</TD> <TD>December</TD> <TD>2009</TD> <TD>2</TD> <TD>191</TD> <TD>74337.18</TD> </TR> </TABLE> </body> </html> In the report, I need to group by Location and Center. Following that, I need a column for each GLMonthText. GLMonthText is driven by the GLPeriod. First column name: =IIF(Fields!.GLPeriod.value = 0, Fields!GLMonthText.value, " "). Subsequent GLMonthText columns will be driven by the other GLPeriod values the same way. The row data for each of those GLMonthText columns requires the following calculation: =IIF(Fields!GLPeriod.Value = 0, Fields!RB_Amount.Value\Fields!PD_Amount.Value, 0). The calculation is also driven by the GLPeriod. My grouping for Location and Center are correct in the report. The problem that I am having is that the column names are resolving to " ", and the formula results to zero. How do I begin to troubleshoot this? Thank you for your help. cdun2

November 5th, 2010 12:01pm

Never mind on this. I needed a dynamic column group, driven by the GLPeriod. Thanks for your help. cdun2

Free Windows Admin Tool Kit Click here and download it now

November 5th, 2010 2:53pm

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