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 |
|
Xaviervp
Starting Member
2 Posts |
 Posted - 2011-07-18 : 16:15:32
|
| Bare with me this is new to me.Is there a quick and easy way to create a report using my existing SP'sthe only way I find is after creating my data source I have to use the query builder that comes up, or i cant use the grouping tool,i have found a way to trick it by typing Execute "SP Name '@parameter name' then it lets me continue.What im trying to accomplish in as few steps as possible.select my data source "my database"start dropping fields in reportgroup as i see fitreport is finishedpublishI have been using Crystal and MS Access for years and SSRS has not made it easier any report i need takes me 4 or 5 times the time to create than it would in crystal or access even when they were new to me..also the fact that data fields can not be placed in header and footerblew my mind I can create complex reports in Crystal or Access and satisfy my users but ssrs makes it very hard to do.SSRS says you can import reports from access well I have not found one that would work without having to revamp it by first eliminating everything that is in the headder or footer WHY?so back to my question is there a short and quick way to accomplish what i want, if i bypass the query builder why can I not group my results?Thank youXavier |
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2011-07-21 : 10:27:20
|
| Not sure what the hangup would be. We took stored procs behind Crystal reports and moved them into SSRS with little issue using the same steps you mentioned. In fact, I found that SSRS was more powerful than Crystal in the control it gives on individual cells. The one huge advantage Crystal has (and the only one I can find so far) is that in CR you can be in preview mode and still move fields around and widen/shorten fields as needed. With SSRS you have to go back to design mode.We did not try any import tools, just took the stored proc and started building the new report layout so I can't comment on the claim that it is easy to import from Access. When you add a detail row you should be able to right click and select from the Grouping options. I never had problems other than just learning when and where to put stuff. In fact, the grouping capabilities are more powerful in SSRS than CR. You have more formatting options than I could ever figure out in CR. I am sure that none of this helps much. Maybe give us some specifics and we can try to help you through it.Hey, it compiles. |
 |
|
|
Xaviervp
Starting Member
2 Posts |
Posted - 2011-07-21 : 11:49:08
|
| Thank you, I guess i'm not too clear on the steps but once you create your data source the next step is the query builderif I bypass it then the form for table grouping does not come up and when I insert a row and select it i have no mention of groupingin the drop down.You say SSRS is more powerfull I can't dispute it as im still learing it but the first drawback i found was like you said in CR you can adjust fields, change fonts, formatting etc in preview mode which is very convenient in SSRS you have to you switch from preview to design and back and enter your parameter everytime.Also I have many CR reports as well as Access that have data fields in the header and footer SSRS does not allow data fields in header or footer.Except what I have read is you place them in the details section as hidden fields and place a label in the header or footer and refer to the hidden field I have not tried this yet but i see it as very cumbersom and time consuming.Question, when you took your SP's how did you use them, did you just copy and paste them into your query window? in CR it was very easyas you know.the only way I found to use them was by coping to the query editor,is there another way? it would be nice just to be able to open a blank report and be able to pick what SP you want to use and start dropping fields in the body of the report..also another thing I have not figured out yet is:how to link sub reports or an inserted table to the main body so i filter the contents based on a specific field on the main report, this parameter is not the same that is used to call the main report.how do i change my data source to my production server without affecting any reports?how to move all my reports, do I have to open one by one and change the datasource and deploy again?In crystal it was just copy all reports then yes i had to open one by one and poit it to the new DB and appropriate SP.all I know is a report no matter how complex that I can prepare in crystal and deploy ittakes me over twice the time in SSRS.Guess ill keep on trying :) |
|
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2011-07-21 : 14:08:37
|
quote:
Thank you, I guess i'm not too clear on the steps but once you create your data source the next step is the query builderif I bypass it then the form for table grouping does not come up and when I insert a row and select it i have no mention of groupingin the drop down.
I used to set up my data source. Then add a data set. Within the data set screen you can enter query text or you can select a stored procedure from a dropdown box. Ironically I am back to Crystal in my new position here so I don't have the SSRS screens up in front of me to give you exact specifics.After you set the data set (I always named it the same name as my SP so I knew what I was working with) you have your fields listed out. As with CR, you can have multiple datasets.quote:
You say SSRS is more powerfull I can't dispute it as im still learing it but the first drawback i found was like you said in CR you can adjust fields, change fonts, formatting etc in preview mode which is very convenient in SSRS you have to you switch from preview to design and back and enter your parameter everytime.
If Microsoft can fix that, they would have the ultimate reporting product!quote:
Also I have many CR reports as well as Access that have data fields in the header and footer SSRS does not allow data fields in header or footer.Except what I have read is you place them in the details section as hidden fields and place a label in the header or footer and refer to the hidden field I have not tried this yet but i see it as very cumbersom and time consuming.
I seem to recall being able to put a text field in the header and then going into the expression builder and doing this. I think that the trick was you needed to put the "First()" function around the field you were returning. Many times the report requester would want some summary details in the header, such as region or date ranges. I could do this by putting the field up there but in order to keep the field from repeating a hundred times for each row in my data set, I put the "First()" function around the field name.quote:
Question, when you took your SP's how did you use them, did you just copy and paste them into your query window? in CR it was very easyas you know.the only way I found to use them was by coping to the query editor,is there another way? it would be nice just to be able to open a blank report and be able to pick what SP you want to use and start dropping fields in the body of the report..
Add a Data Set to the report after you have added the Data Source and you will see options to select the Data Source, and then there is a Refresh button. Click that and select the option to use a stored procedure. In the dropdown is your proc.quote:
also another thing I have not figured out yet is:how to link sub reports or an inserted table to the main body so i filter the contents based on a specific field on the main report, this parameter is not the same that is used to call the main report.
It can be done as I made many reports with sub reports. A more powerful feature though is to simply put a table in your report and put your fields in it as usual. Then there is an option to hide the table unless a plus sign is checked. In essence, you have your sub report in the body of the report but you are just hiding it. Go online and research how to hide the table and add a clickable field. It is very powerful and an awesome way to present your sub report info.quote:
how do i change my data source to my production server without affecting any reports?how to move all my reports, do I have to open one by one and change the datasource and deploy again?
In development you have a dev data source that hopefully points to a database where screwups won't affect real production values. After you deploy your report to your testing or production SSRS machine you have to go into the properties of the report (on the hosted machine, not your local machine) and switch data sources. So it really isn't much different than CR in that you still have to switch data sources. After you upload your report, click on it, it should fail. Near the top of the screen is a set of tabs. Again, I don't have it in front of me but one of them lets you go in and set the new data source to production or testing or whatever your next phase is. After you do that, your report should work just fine.quote:
Guess ill keep on trying :)
Can't ask for more than that! Good luck!Hey, it compiles. |
|
|
|
|
|
|
|
|
2005 Reporting Services