March 12, 2008 at 5:40 am
I need help finding a solution in Reporting Services for this scenario.
Consider this result set:
Person Letter
1 A
1 B
1 C
2 E
2 F
Here’s what I want to print on the report
Person 1 A B C
Person 2 E F
In Crystal, I’d do the following:
Group Header: insert a formula to declare global variable @TheString = ‘ ‘
Detail: Insert a formula that does doesn’t print anything, but does concatenate the string: @TheString = @TheString + Letter value
Group Footer: insert a formula to print variable @TheString
With the result set as described, how would I get the desired report content using Reporting Services?
If the only option is to rewrite the stored proc so that the result set comes back with the Letters already appended, I need to know that because it has to become a consideration when estimating the time needed to migrate a report from Crystal to RS.
Thanks,
March 13, 2008 at 6:23 pm
Interesting problem, I used Crystal for a long time (15+ years) and there are a number of things that are a little easier in Crystal, but I'm a fan of SSRS also. Anyway, onto your problem......
The obvious first answer on this one is to try to achieve your end result of details in the SQL Query that returns the dataset to the report, this is where I find I can have much greater control over the dataset because you can get into some fairly complex querying before returning anything to the report for printing. You should be able to develop something that returns just the item and one string, therefore meaning you only have to create a detail section and print the data.
Second, it's possible I am sure to develop something in the expressions in SSRS to achieve the same thing as you would in CR but having never needed to do it I am guessing a little here and you'll need to try a few things out.
1. Group on Item
2. Expression in detail area something like:
=IIF(Previous(Fields!Item.Value)=Fields!Item.Value,ReportingItems!ItemLetters.Value+Fields!Letter.Value,Fields!Letter.Value)
You should note that the textbox that this expression is stored in should have a name of ItemLetters (or whatever you like as long as you change the reference above accordingly.
3. In the group footer, use the expression: =ReportItems!ItemLetters.Value
As I say, I'm guessing a little, but something like this should work.
Best of luck,
Nigel.
Nigel West
UK
March 14, 2008 at 12:44 am
Thanks for your reply, let me try today , lets see if it works!
Cheers
Meghna
March 19, 2008 at 1:25 am
hi Nigel
i did the same as per you and added the expression mentioned below
IIF(Previous( Fields!Person.Value )=Fields!Person.Value, ReportItems!ItemLetters.Value +Fields!Letter.Value,Fields!Letter.Value)
but u know whats happening
output is like
A
BB
CC
E
FF
for thsi data as given below
Person Letter
1 A
1 B
1 C
2 E
2 F
ya , its comparing with the person whose value is same as the previous one, then its adding the same string again n again, but i need teh output as
Person 1 A B C
Person 2 E F
can u please guide me out , how to acheive it?
March 20, 2008 at 9:29 am
Sorry to leave this thread hanging so long.
Go to Report / Report Properties.
Click on the Code tab.
Add some VBA code like:
Public myString
Public myKey
Function StringGrouping( aKey,aString )
If MyKey <> aKey Then
myString = ""
myKey =aKey
End if
myString = myString + aString
Return myString
End Function
In your table, on the correct field use the expression
=Code.StringGrouping( Fields!Person.Value, Fields!Letter.Value )
This'll build list as you go. If you want just the summary, insert a Group and reference =Code.myString on the header. Watch recordset state since the headers / footers don't fire until the current record has changed. (I'll let you figure it out.)
Cheers.
March 20, 2008 at 10:52 pm
It worked ,thanks a lot
Meghna
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply