About SQL reporting services - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

About SQL reporting services

Author

Topic

Joshrinn
Posting Yak Master

118 Posts

Posted - 2012-05-03 : 11:34:45

Im having an issue.
There are two feeds which is suppose to populate a DB table every month.
Feed 1 generates data every month but feed 2 doesn't get data every month as a result of which there is no value on that table. The last time feed 2 had received data and populated the corresponding table was on jan 2012. The problem now is if the table is not populated value wil b Null values in the tables. But instead of that if no value is received I should use the previous month"s value received by feed two as a result of which there will not be huge variance as oppose to putting NULL values. How can I use last moths data if feed 2 doesn't populate the tables. I know a case statement would be great but I just didn't know how to use it. Help would be highly appreciated.

Joshrinn
Posting Yak Master

118 Posts

Posted - 2012-05-03 : 11:41:19

The expression saved here is
=IIF(IsNothing(Fields!End_Det.Value),0,Round(Fields!End_Det.Value))

Can something be done if there is no value present than go to the previous month and if there is no value in the previous month then go prior to another previous month?
Please help

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-05-03 : 12:57:16

Try asking in the ACCESS forum. That is where the ACCESS experts are.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-03 : 14:38:47

quote:
Originally posted by Joshrinn

The expression saved here is
=IIF(IsNothing(Fields!End_Det.Value),0,Round(Fields!End_Det.Value))

Can something be done if there is no value present than go to the previous month and if there is no value in the previous month then go prior to another previous month?
Please help

you cant directly apply this logic at report expression level unless you bring previous month value as a separate column in your dataset. Are you currently having last month values also in dataset as separate columns?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-03 : 14:39:37

quote:
Originally posted by DonAtWork

Try asking in the ACCESS forum. That is where the ACCESS experts are.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

I dont think OP is asking an Access question. It was a SQL reporting services question and expression posted was a SSRS report expressions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Joshrinn
Posting Yak Master

118 Posts

Posted - 2012-05-03 : 15:12:37

Thanks. Also no I haven't brought in previous month value as a separate column in my dataset. Because Im processing feb month report and there is no data from feed 2 and my superior wants me to pull the data from last month for feed 2 which will ultimately result in a close difference rather than a NULL values. How can I put that in a logic so that everytime there is no record from that feed the tables will get automatically populated with the last month's or previous months data.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-03 : 15:17:48

quote:
Originally posted by Joshrinn

Thanks. Also no I haven't brought in previous month value as a separate column in my dataset. Because Im processing feb month report and there is no data from feed 2 and my superior wants me to pull the data from last month for feed 2 which will ultimately result in a close difference rather than a NULL values. How can I put that in a logic so that everytime there is no record from that feed the tables will get automatically populated with the last month's or previous months data.

as suggested you need to include a logic in your query behind or procedure to include last month values

either put values in a temporary table and do an update on it to include last month value as a new column or use apply operator to return them along with same query

see scenario 2

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Joshrinn
Posting Yak Master

118 Posts

Posted - 2012-05-03 : 15:49:18

No actually there is a single SSIS package which populates records received from both these feeds. And this month when I ran that package it just populated results from feed 1 and feed 2 didn't have anything on it. Now the problem is that the data that populated the tables after the execution of this package has only populated tables from feed 1 and it shows no records inserted from feed 2. Now I have to use the tables which was populated by this single SSIS package to get the reports. But when I run this report it just shows me result of feed 1 and there are no records displayed for the other one as both the feeds populate 1 single table.Last month feed 2 when ran had populated tables and now I need to use last month records from feed 2 as this month's records as well.There is no difference in the query present on the dataset because when the record is there it populates it automatically and when there is no record it doesn't show that particular column in the report at all:(. Sorry I was not that clear on explaining my question. Let me know if it is still not clear?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-03 : 16:01:12

quote:
Originally posted by Joshrinn

No actually there is a single SSIS package which populates records received from both these feeds. And this month when I ran that package it just populated results from feed 1 and feed 2 didn't have anything on it. Now the problem is that the data that populated the tables after the execution of this package has only populated tables from feed 1 and it shows no records inserted from feed 2. Now I have to use the tables which was populated by this single SSIS package to get the reports. But when I run this report it just shows me result of feed 1 and there are no records displayed for the other one as both the feeds populate 1 single table.Last month feed 2 when ran had populated tables and now I need to use last month records from feed 2 as this month's records as well.There is no difference in the query present on the dataset because when the record is there it populates it automatically and when there is no record it doesn't show that particular column in the report at all:(. Sorry I was not that clear on explaining my question. Let me know if it is still not clear?

You're missing my point here
regardless of whether you get values from feed 2 or not you've to bring values from last month also for feed 2. then in report you need to add an expression to check for nulls in current month values and in case its null take value from last month fields instead. In case where you have values coming from feed2 it will take it and will not look for last month values. But in any case the resultset has to have both values as separate column like

ValueCM ValueLM

where Cm,LM are Current Month,Last Month

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Joshrinn
Posting Yak Master

118 Posts

Posted - 2012-05-03 : 16:03:51

Thankyou sir its much clear now

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-03 : 16:05:00

welcome
lemme knw how you got on!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Joshrinn
Posting Yak Master

118 Posts

Posted - 2012-05-03 : 16:09:24

Sure I will keep u posted and if I need any help I'll let u know

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-03 : 16:10:52

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Joshrinn
Posting Yak Master

118 Posts

Posted - 2012-05-06 : 23:05:45

Thankyou I got it fixed

Joshrinn
Posting Yak Master

118 Posts

Posted - 2012-05-06 : 23:08:25

I have one more issue. Could you please tell me a step by step guide of how I can deploy the report? I mean is there any way I can just add the reports I need in a separate folder? I mean I have bunch of rdl files that gets displayed on the solution explorer. Out of those I just need 5 reports and idont know the steps of how I can deploy these reports. Please this would be really helpful.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-06 : 23:17:37

quote:
Originally posted by Joshrinn

I have one more issue. Could you please tell me a step by step guide of how I can deploy the report? I mean is there any way I can just add the reports I need in a separate folder? I mean I have bunch of rdl files that gets displayed on the solution explorer. Out of those I just need 5 reports and idont know the steps of how I can deploy these reports. Please this would be really helpful.

you've property called TargetServerURL and TargetReportFolder available from Report Project properties. You need to set former to your report server url and latter to your required report folder. then right click your reports from solution explorer and select deploy. the reports will get deployed to required folder in reportserver.
Then you can access the reports from report server using url

http://machinename/reports
and going to relevant folder which you gave above. You should also be deploying datasources along with reports

Also you can do this only if your login is mapped to MyReports role in report server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Joshrinn
Posting Yak Master

118 Posts

Posted - 2012-05-07 : 08:40:21

No actually I have one rdl file which consists of 5 reports inside it. How do I just deploy that particular file?

Joshrinn
Posting Yak Master

118 Posts

Posted - 2012-05-09 : 13:30:39

Do you have a personal email address which I can email to ? Sorry if that is too much to ask for

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-09 : 15:52:04

quote:
Originally posted by Joshrinn

No actually I have one rdl file which consists of 5 reports inside it. How do I just deploy that particular file?

You need to deploy the included reports first and then deploy the master report

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-09 : 15:52:58

quote:
Originally posted by Joshrinn

Do you have a personal email address which I can email to ? Sorry if that is too much to ask for

you can post it here itself so that others can also chip in and give suggestions which might provide you with better and quicker solutions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Joshrinn
Posting Yak Master

118 Posts

Posted - 2012-05-09 : 21:32:17

I tried a lot but could not come with a query for the following.
I have two tables. I would like you to please help me with a logic here.
The first table dbo.Main_ax has records from the month of March and so does the second table dbo.subort_aw where the company prefixes the values for a year and currently its prefixed until Dec 2012. Now I would need to write a logic for dbo.subort such that the value remains the same for that quarter. Meaning if my quarter starts from March 2011, I would like to have the values of March, April and May 2011 same as the value of March as they are all different right now. So everything has to be unchanging for that quarter.No new employee number will be added for that period even if they join around April and all the new addition will be done in the next quarter.If dbo.main_ax will update accordingly.

Please help me with this one. I tried to explain to my best knowledge but let me know if i need to add any other info.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources