Solved

how to retreive data according to date constrain

Posted on 2014-09-03
5
116 Views
Last Modified: 2014-09-05
I am connecting Excel to SQL Server to create some reports by using the data from SQL.. Example of data in SQL is Table name: Campaign that include (AccountID, date, impressions, clicks, cost). I would like to create a report in Excel that keep historical record for each week just like the one in the picture how i want my report to look like.. is there any way that allow me to pull data from SQL in weekly bases ?

Thank you!
0
Comment
Question by:Rawan Jambi
  • 2
  • 2
5 Comments
 
LVL 11

Expert Comment

by:jkpieterse
ID: 40301185
Click the Data tab of the ribbon, click "From other sources", "from SQL server" and off you go!
0
 

Author Comment

by:Rawan Jambi
ID: 40301220
yes I know I am doing this already.. my question is when getting the data how can I make them come as blocks for weeks? for example (1 july - 7 july ) with their data (accounted, impressions, clicks and cost) then (8 july - 15 july) then the next week ... etc
0
 
LVL 11

Expert Comment

by:jkpieterse
ID: 40301257
You would typically create a pivottable off of the data, drag the date field in the row area and then group that date field.
0
 
LVL 16

Accepted Solution

by:
Jerry Paladino earned 500 total points
ID: 40303597
Use your detail data to generate a pivot table.   Insert / Pivot Table...   Group the Date field... right mouse click on that field and select GROUP.   For Weeks, select Day and choose 7 in the number of days box.   Use "Calculated Fields" for Cost per Click and Click Through Rate.  Pivot Table / Analyze / Calculations group / Fields, Items, & Sets.  See graphics below.   A sample excel file is attached. GroupingGroup by WeekCalculated Field Dialog selectionCalculated Field - Cost per ClickCalculated Field - Click Through RatePivot-Table--Group-by-Week.xlsx
0
 

Author Comment

by:Rawan Jambi
ID: 40305603
Thank you so much that was very helpful.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now