Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
znarimani
Starting Member
2 Posts |
 Posted - 2011-09-27 : 03:39:09
|
| Hi I'm creating a report for a company on its sales data. They need a report which is dynamically customized by user.For example user can determine she needs measures A, B and C in dates D1, D2 and D3. Since the maximum of selected dates is fixed (10) and measures are fixed too, I have created a report with 10 dates as input parameters, the report columns are all measures in all selected dates. Now the user can check if she wants a date/measure or not. This selection is done using another parameter which is set to true/false for each measure/date. If a measure/date is requested by user, I make the column for this measure visible, and get the value from the dataset, otherwise I set the value to zero and set the column invisible. This checking is done in report interface (SSRS table).Is it a good solution? can I have dynamic columns in a better way?Now my main question:The user wants to type a formula in an input field (like excel formulas), and get the result in the last column of this table. for example if the user types (c1+c2)/c3 she wants to get the value of first column data plus second over the third. Now I don't know how to implement this :( I cant save selected user columns in variables and use them to calculate formula. Instead I think I should check all parameters to see which one is selected by user, and find c1, c2 and c3 due to user selection, and finally write a long code in the last column in order to have the right output for the user. Since in SSRS I don't have powerful programming utilities, I should write static codes to check lots of conditions in order to calculate a simple formula... I tried to convince the owner to save the report in excel first and do it in excel but she didn't accept and insists on having a field to write formulas in SSRS... Excuse me for this long post :">I'd appreciate your help :)Zahra |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-09-27 : 07:08:26
|
| Being able to input working formulas in an SSRS parameter would have serious security implications so it is FAR from recomended practice. But the only way I can think of that you _might_ be able to make work is to have the report source as a stored procedure, then pass the formula to the stored procedure and then execute some dynamic sql inside the procedure that uses the formula. This is however a TERRIBLE way to do it and will leave a huge security hole in your application. You better do some more convincing instead...- LumbagoMy blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/ |
 |
|
|
znarimani
Starting Member
2 Posts |
Posted - 2011-09-28 : 01:42:03
|
| Thanks a lot Lumbago.Stored procedure is a good solution - if I can't convince her :) |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-09-28 : 01:52:33
|
| It would be better to tell her it's not possible. Then you don't need to convince her ;)- LumbagoMy blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/ |
|
|
|
|
|
|
|
|
dynamic reports in ssrs